-- MySQL dump 10.13  Distrib 8.0.40, for macos14 (x86_64)
--
-- Host: localhost    Database: zagi_warehouse
-- ------------------------------------------------------
-- Server version	8.0.30

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `calendar`
--

DROP TABLE IF EXISTS `calendar`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `calendar` (
  `calendar_key` int NOT NULL AUTO_INCREMENT,
  `full_date` date DEFAULT NULL,
  `day_of_week` varchar(9) DEFAULT NULL,
  `day_of_month` int DEFAULT NULL,
  `month` int DEFAULT NULL,
  `qtr` int DEFAULT NULL,
  `year` int DEFAULT NULL,
  PRIMARY KEY (`calendar_key`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `calendar`
--

LOCK TABLES `calendar` WRITE;
/*!40000 ALTER TABLE `calendar` DISABLE KEYS */;
INSERT INTO `calendar` VALUES (1,'2023-02-01','Wednesday',1,2,1,2023),(2,'2023-02-02','Thursday',2,2,1,2023),(3,'2023-05-24','Wednesday',24,5,2,2023);
/*!40000 ALTER TABLE `calendar` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `customer`
--

DROP TABLE IF EXISTS `customer`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `customer` (
  `customer_key` int NOT NULL AUTO_INCREMENT,
  `customer_id` varchar(7) DEFAULT NULL,
  `customer_name` varchar(32) DEFAULT NULL,
  `customer_zip` int DEFAULT NULL,
  `customer_gender` varchar(6) DEFAULT NULL,
  `customer_marital_status` varchar(7) DEFAULT NULL,
  `customer_education_level` varchar(16) DEFAULT NULL,
  `customer_credit_score` int DEFAULT NULL,
  PRIMARY KEY (`customer_key`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `customer`
--

LOCK TABLES `customer` WRITE;
/*!40000 ALTER TABLE `customer` DISABLE KEYS */;
INSERT INTO `customer` VALUES (1,'1-2-333','Tina',60137,'Female','Single','College',700),(2,'2-3-444','Tony',60611,'Male','Single','High School',650),(3,'3-4-555','Pam ',35401,'Female','Marred','College',623);
/*!40000 ALTER TABLE `customer` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `product`
--

DROP TABLE IF EXISTS `product`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `product` (
  `product_key` int NOT NULL AUTO_INCREMENT,
  `product_id` varchar(3) DEFAULT NULL,
  `product_name` varchar(32) DEFAULT NULL,
  `product_price` double DEFAULT NULL,
  `product_vendor_name` varchar(32) DEFAULT NULL,
  `product_category_name` varchar(16) DEFAULT NULL,
  PRIMARY KEY (`product_key`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `product`
--

LOCK TABLES `product` WRITE;
/*!40000 ALTER TABLE `product` DISABLE KEYS */;
INSERT INTO `product` VALUES (1,'1X1','Zzz Bag',100,'Pacifica Gear','Camping'),(2,'2X2','Easy Boot',70,'Mountain King','Footwear'),(3,'3X3','Cosy Sock',15,'Mountain King','Footwear'),(4,'4X4','Dura Boot',90,'Pacifica Gear','Footwear'),(5,'5X5','Tiny Tent',150,'Mountain King','Camping'),(6,'6X6','Biggy Tent',250,'Mountain King','Camping');
/*!40000 ALTER TABLE `product` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `sales`
--

DROP TABLE IF EXISTS `sales`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `sales` (
  `calendar_key` int NOT NULL,
  `store_key` int NOT NULL,
  `product_key` int NOT NULL,
  `customer_key` int NOT NULL,
  `tid` varchar(8) DEFAULT NULL,
  `time_of_day` time DEFAULT NULL,
  `dollars_sold` double DEFAULT NULL,
  `units_sold` int DEFAULT NULL,
  PRIMARY KEY (`calendar_key`,`store_key`,`product_key`,`customer_key`),
  KEY `store_key` (`store_key`),
  KEY `product_key` (`product_key`),
  KEY `customer_key` (`customer_key`),
  CONSTRAINT `sales_ibfk_1` FOREIGN KEY (`calendar_key`) REFERENCES `calendar` (`calendar_key`),
  CONSTRAINT `sales_ibfk_2` FOREIGN KEY (`store_key`) REFERENCES `store` (`store_key`),
  CONSTRAINT `sales_ibfk_3` FOREIGN KEY (`product_key`) REFERENCES `product` (`product_key`),
  CONSTRAINT `sales_ibfk_4` FOREIGN KEY (`customer_key`) REFERENCES `customer` (`customer_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `sales`
--

LOCK TABLES `sales` WRITE;
/*!40000 ALTER TABLE `sales` DISABLE KEYS */;
INSERT INTO `sales` VALUES (1,1,1,1,'T111','08:23:59',100,1),(1,2,2,2,'T222','08:24:30',70,1),(2,3,1,1,'T333','08:15:08',100,1),(2,3,2,3,'T444','08:20:33',140,2),(2,3,3,1,'T333','08:15:08',75,5),(2,3,4,2,'T555','08:30:00',360,4),(2,3,4,3,'T444','08:20:33',90,1),(2,3,5,2,'T555','08:30:00',300,2),(2,3,6,2,'T555','08:30:00',250,1),(3,1,1,3,'T666','10:45:09',200,2);
/*!40000 ALTER TABLE `sales` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `sales_per_dcs`
--

DROP TABLE IF EXISTS `sales_per_dcs`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `sales_per_dcs` (
  `calendar_key` int NOT NULL,
  `store_key` int NOT NULL,
  `customer_key` int NOT NULL,
  `dollars_sold` double DEFAULT NULL,
  `units_sold` int DEFAULT NULL,
  `line_count` int DEFAULT NULL,
  PRIMARY KEY (`calendar_key`,`store_key`,`customer_key`),
  KEY `store_key` (`store_key`),
  KEY `customer_key` (`customer_key`),
  CONSTRAINT `sales_per_dcs_ibfk_1` FOREIGN KEY (`calendar_key`) REFERENCES `calendar` (`calendar_key`),
  CONSTRAINT `sales_per_dcs_ibfk_2` FOREIGN KEY (`store_key`) REFERENCES `store` (`store_key`),
  CONSTRAINT `sales_per_dcs_ibfk_3` FOREIGN KEY (`customer_key`) REFERENCES `customer` (`customer_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `sales_per_dcs`
--

LOCK TABLES `sales_per_dcs` WRITE;
/*!40000 ALTER TABLE `sales_per_dcs` DISABLE KEYS */;
INSERT INTO `sales_per_dcs` VALUES (1,1,1,100,1,1),(1,2,2,70,1,1),(2,3,1,175,6,2),(2,3,2,910,7,3),(2,3,3,230,3,2),(3,1,3,200,2,1);
/*!40000 ALTER TABLE `sales_per_dcs` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `sales_per_dpcs`
--

DROP TABLE IF EXISTS `sales_per_dpcs`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `sales_per_dpcs` (
  `calendar_key` int NOT NULL,
  `store_key` int NOT NULL,
  `product_key` int NOT NULL,
  `customer_key` int NOT NULL,
  `dollars_sold` double DEFAULT NULL,
  `units_sold` int DEFAULT NULL,
  `line_count` int DEFAULT NULL,
  PRIMARY KEY (`calendar_key`,`store_key`,`product_key`,`customer_key`),
  KEY `store_key` (`store_key`),
  KEY `product_key` (`product_key`),
  KEY `customer_key` (`customer_key`),
  CONSTRAINT `sales_per_dpcs_ibfk_1` FOREIGN KEY (`calendar_key`) REFERENCES `calendar` (`calendar_key`),
  CONSTRAINT `sales_per_dpcs_ibfk_2` FOREIGN KEY (`store_key`) REFERENCES `store` (`store_key`),
  CONSTRAINT `sales_per_dpcs_ibfk_3` FOREIGN KEY (`product_key`) REFERENCES `product` (`product_key`),
  CONSTRAINT `sales_per_dpcs_ibfk_4` FOREIGN KEY (`customer_key`) REFERENCES `customer` (`customer_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `sales_per_dpcs`
--

LOCK TABLES `sales_per_dpcs` WRITE;
/*!40000 ALTER TABLE `sales_per_dpcs` DISABLE KEYS */;
INSERT INTO `sales_per_dpcs` VALUES (1,1,1,1,100,1,1),(1,2,2,2,70,1,1),(2,3,1,1,100,1,1),(2,3,2,3,140,2,1),(2,3,3,1,75,5,1),(2,3,4,2,360,4,1),(2,3,4,3,90,1,1),(2,3,5,2,300,2,1),(2,3,6,2,250,1,1),(3,1,1,3,200,2,1);
/*!40000 ALTER TABLE `sales_per_dpcs` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `store`
--

DROP TABLE IF EXISTS `store`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `store` (
  `store_key` int NOT NULL AUTO_INCREMENT,
  `store_id` varchar(2) DEFAULT NULL,
  `store_zip` int DEFAULT NULL,
  `store_region_name` varchar(16) DEFAULT NULL,
  `store_size` int DEFAULT NULL,
  `store_c_system` varchar(16) DEFAULT NULL,
  `store_layout` varchar(16) DEFAULT NULL,
  PRIMARY KEY (`store_key`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `store`
--

LOCK TABLES `store` WRITE;
/*!40000 ALTER TABLE `store` DISABLE KEYS */;
INSERT INTO `store` VALUES (1,'S1',60600,'Chicagoland',51000,'Cashiers','Modern'),(2,'S2',60605,'Chicagoland',35000,'Self Service','Traditional'),(3,'S3',35400,'Tristate',55000,'Mixed','Traditional');
/*!40000 ALTER TABLE `store` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2025-05-01  9:41:11
