-- MySQL dump 10.13 Distrib 5.7.7-rc, for linux-glibc2.5 (x86_64) -- -- Host: localhost Database: qestar -- ------------------------------------------------------ -- Server version 5.7.7-rc-log /*!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 */; /*!40101 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 `customer` -- DROP TABLE IF EXISTS `customer`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `customer` ( `CUSTOMER_KEY` int(11) NOT NULL, `CUSTOMER_NAME` varchar(50) NOT NULL, `ACCOUNT_NUMBER` varchar(50) NOT NULL, `BILLING_ADDRESS` varchar(50) NOT NULL, `BILLING_CITY` varchar(50) NOT NULL, `BILLING_STATE` varchar(50) NOT NULL, `BILLING_ZIP` varchar(50) NOT NULL, PRIMARY KEY (`CUSTOMER_KEY`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!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,'Fargo Products','98729874','1876 Palm Ave','San Francisco','CA','94567'),(2,'Kingston Outlets','98723948','2341 Telegraph Hill','San Fernando','CA','95964'),(3,'Marcus CRS','98734556','4768 Palm Street','Los Angeles','CA','78647'),(4,'ServiceLand','83756263','9459 Tenesse Lane','Sacramento','CA','98784'),(5,'Electronic Warehouse','23452345','2727 Fortune Ave','San Francisco','CA','94225'),(6,'SuperClub','48591030','8656 Ocean Beach','Santa Cruz','CA','94558'),(7,'SuperClub','02937958','2829 Grant Lane','Marin County','CA','95689'),(8,'SuperClub','29384656','5784 Turnpike','Los Angeles','CA','93478'),(9,'Jenkins','29834733','4589 Eisenhower','Alexandria','VA','36732'),(10,'Circuit Branches','30495283','3689 Gower Ave','San Francisco','CA','94112'); /*!40000 ALTER TABLE `customer` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `dates` -- DROP TABLE IF EXISTS `dates`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `dates` ( `DAY_KEY` int(11) NOT NULL, `DATES` datetime NOT NULL, `DAY_OF_WEEK_NUMBER` varchar(50) NOT NULL, `DAY_OF_WEEK_NAME` varchar(50) NOT NULL, `DAY_OF_MONTH` int(11) NOT NULL, `MONTH_NUMBER` int(11) NOT NULL, `MONTH_NAME` varchar(50) NOT NULL, `FISCAL_PERIOD` varchar(50) NOT NULL, `YEAR` int(11) NOT NULL, PRIMARY KEY (`DAY_KEY`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `dates` -- LOCK TABLES `dates` WRITE; /*!40000 ALTER TABLE `dates` DISABLE KEYS */; INSERT INTO `dates` VALUES (1,'1997-01-01 12:00:00','1','Monday',1,1,'January','Q1',1997),(2,'1997-01-02 12:00:00','2','Tuesday',2,1,'January','Q1',1997),(3,'1997-01-03 12:00:00','3','Wednesday',3,1,'January','Q1',1997),(4,'1997-01-04 12:00:00','4','Thursday',4,1,'January','Q1',1997),(5,'1997-01-05 12:00:00','5','Friday',5,1,'January','Q1',1997),(6,'1997-01-06 12:00:00','6','Saturday',6,1,'January','Q1',1997),(7,'1997-01-07 12:00:00','7','Sunday',7,1,'January','Q1',1997),(8,'1997-01-08 12:00:00','1','Monday',8,1,'January','Q1',1997),(9,'1997-01-09 12:00:00','2','Tuesday',9,1,'January','Q1',1997),(10,'1997-01-10 12:00:00','3','Wednesday',10,1,'January','Q1',1997),(11,'1997-01-11 12:00:00','4','Thursday',11,1,'January','Q1',1997),(12,'1997-01-12 12:00:00','5','Friday',12,1,'January','Q1',1997),(13,'1997-01-13 12:00:00','6','Saturday',13,1,'January','Q1',1997),(14,'1997-01-14 12:00:00','7','Sunday',14,1,'January','Q1',1997),(15,'1997-01-15 12:00:00','1','Monday',15,1,'January','Q1',1997),(16,'1997-01-16 12:00:00','2','Tuesday',16,1,'January','Q1',1997),(17,'1997-01-17 12:00:00','3','Wednesday',17,1,'January','Q1',1997),(18,'1997-01-18 12:00:00','4','Thursday',18,1,'January','Q1',1997),(19,'1997-01-19 12:00:00','5','Friday',19,1,'January','Q1',1997),(20,'1997-01-20 12:00:00','6','Saturday',20,1,'January','Q1',1997),(21,'1997-01-21 12:00:00','7','Sunday',21,1,'January','Q1',1997),(22,'1997-01-22 12:00:00','1','Monday',22,1,'January','Q1',1997),(23,'1997-01-23 12:00:00','2','Tuesday',23,1,'January','Q1',1997),(24,'1997-01-24 12:00:00','3','Wednesday',24,1,'January','Q1',1997),(25,'1997-01-25 12:00:00','4','Thursday',25,1,'January','Q1',1997),(26,'1997-01-26 12:00:00','5','Friday',26,1,'January','Q1',1997),(27,'1997-01-27 12:00:00','6','Saturday',27,1,'January','Q1',1997),(28,'1997-01-28 12:00:00','7','Sunday',28,1,'January','Q1',1997),(29,'1997-01-29 12:00:00','1','Monday',29,1,'January','Q1',1997),(30,'1997-01-30 12:00:00','2','Tuesday',30,1,'January','Q1',1997),(31,'1997-01-31 12:00:00','3','Wednesday',31,1,'January','Q1',1997),(32,'1997-02-01 12:00:00','1','Monday',1,2,'February','Q1',1997),(33,'1997-02-02 12:00:00','2','Tuesday',2,2,'February','Q1',1997),(34,'1997-02-03 12:00:00','3','Wednesday',3,2,'February','Q1',1997),(35,'1997-02-04 12:00:00','4','Thursday',4,2,'February','Q1',1997),(36,'1997-02-05 12:00:00','5','Friday',5,2,'February','Q1',1997),(37,'1997-02-06 12:00:00','6','Saturday',6,2,'February','Q1',1997),(38,'1997-02-07 12:00:00','7','Sunday',7,2,'February','Q1',1997),(39,'1997-02-08 12:00:00','1','Monday',8,2,'February','Q1',1997),(40,'1997-02-09 12:00:00','2','Tuesday',9,2,'February','Q1',1997),(41,'1997-02-10 12:00:00','3','Wednesday',10,2,'February','Q1',1997),(42,'1997-02-11 12:00:00','4','Thursday',11,2,'February','Q1',1997),(43,'1997-02-12 12:00:00','5','Friday',12,2,'February','Q1',1997),(44,'1997-02-13 12:00:00','6','Saturday',13,2,'February','Q1',1997),(45,'1997-02-14 12:00:00','7','Sunday',14,2,'February','Q1',1997),(46,'1997-02-15 12:00:00','1','Monday',15,2,'February','Q1',1997),(47,'1997-02-16 12:00:00','2','Tuesday',16,2,'February','Q1',1997),(48,'1997-02-17 12:00:00','3','Wednesday',17,2,'February','Q1',1997),(49,'1997-02-18 12:00:00','4','Thursday',18,2,'February','Q1',1997),(50,'1997-02-19 12:00:00','5','Friday',19,2,'February','Q1',1997),(51,'1997-02-20 12:00:00','6','Saturday',20,2,'February','Q1',1997),(52,'1997-02-21 12:00:00','7','Sunday',21,2,'February','Q1',1997),(53,'1997-02-22 12:00:00','1','Monday',22,2,'February','Q1',1997),(54,'1997-02-23 12:00:00','2','Tuesday',23,2,'February','Q1',1997),(55,'1997-02-24 12:00:00','3','Wednesday',24,2,'February','Q1',1997),(56,'1997-02-25 12:00:00','4','Thursday',25,2,'February','Q1',1997),(57,'1997-02-26 12:00:00','5','Friday',26,2,'February','Q1',1997),(58,'1997-02-27 12:00:00','6','Saturday',27,2,'February','Q1',1997),(59,'1997-02-28 12:00:00','7','Sunday',28,2,'February','Q1',1997),(60,'1997-04-01 12:00:00','3','Wednesday',1,4,'April','Q2',1997),(61,'1997-04-02 12:00:00','4','Thursday',2,4,'April','Q2',1997),(62,'1997-04-03 12:00:00','5','Friday',3,4,'April','Q2',1997),(63,'1997-04-04 12:00:00','6','Saturday',4,4,'April','Q2',1997),(64,'1997-04-05 12:00:00','7','Sunday',5,4,'April','Q2',1997),(65,'1997-04-06 12:00:00','1','Monday',6,4,'April','Q2',1997),(66,'1997-04-07 12:00:00','2','Tuesday',7,4,'April','Q2',1997),(67,'1997-04-08 12:00:00','3','Wednesday',8,4,'April','Q2',1997),(68,'1997-04-09 12:00:00','4','Thursday',9,4,'April','Q2',1997),(69,'1997-04-10 12:00:00','5','Friday',10,4,'April','Q2',1997),(70,'1997-04-11 12:00:00','6','Saturday',11,4,'April','Q2',1997),(71,'1997-04-12 12:00:00','7','Sunday',12,4,'April','Q2',1997),(72,'1997-04-13 12:00:00','1','Monday',13,4,'April','Q2',1997),(73,'1997-04-14 12:00:00','2','Tuesday',14,4,'April','Q2',1997),(74,'1997-04-15 12:00:00','3','Wednesday',15,4,'April','Q2',1997),(75,'1997-04-16 12:00:00','4','Thursday',16,4,'April','Q2',1997),(76,'1997-04-17 12:00:00','5','Friday',17,4,'April','Q2',1997),(77,'1997-04-18 12:00:00','6','Saturday',18,4,'April','Q2',1997),(78,'1997-04-19 12:00:00','7','Sunday',19,4,'April','Q2',1997),(79,'1997-04-20 12:00:00','1','Monday',20,4,'April','Q2',1997),(80,'1997-04-21 12:00:00','2','Tuesday',21,4,'April','Q2',1997),(81,'1997-04-22 12:00:00','3','Wednesday',22,4,'April','Q2',1997),(82,'1997-04-23 12:00:00','4','Thursday',23,4,'April','Q2',1997),(83,'1997-04-24 12:00:00','5','Friday',24,4,'April','Q2',1997),(84,'1997-04-25 12:00:00','6','Saturday',25,4,'April','Q2',1997),(85,'1997-04-26 12:00:00','7','Sunday',26,4,'April','Q2',1997),(86,'1997-04-27 12:00:00','1','Monday',27,4,'April','Q2',1997),(87,'1997-04-28 12:00:00','2','Tuesday',28,4,'April','Q2',1997),(88,'1997-04-29 12:00:00','3','Wednesday',29,4,'April','Q2',1997),(89,'1997-04-30 12:00:00','4','Thursday',30,4,'April','Q2',1997); /*!40000 ALTER TABLE `dates` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `order_facts` -- DROP TABLE IF EXISTS `order_facts`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `order_facts` ( `ORDER_DATE_KEY` int(11) NOT NULL, `PRODUCT_KEY` int(11) NOT NULL, `CUSTOMER_KEY` int(11) NOT NULL, `SALESPERSON_KEY` int(11) NOT NULL, `ORDER_DOLLARS` float NOT NULL, `EXTENDED_COST` float NOT NULL, `MARGIN_DOLLARS` float NOT NULL, `QUANTITY_ORDERED` float NOT NULL, KEY `IDX1_ORDER_FACTS` (`ORDER_DATE_KEY`), KEY `IDX2_ORDER_FACTS` (`PRODUCT_KEY`), KEY `IDX3_ORDER_FACTS` (`CUSTOMER_KEY`), KEY `IDX4_ORDER_FACTS` (`SALESPERSON_KEY`), CONSTRAINT `FK_ORDDACT_TO_CUST` FOREIGN KEY (`CUSTOMER_KEY`) REFERENCES `sndcustomer` (`CUSTOMER_KEY`), CONSTRAINT `FK_ORDFACT_TO_CUST` FOREIGN KEY (`CUSTOMER_KEY`) REFERENCES `customer` (`CUSTOMER_KEY`), CONSTRAINT `FK_ORDFACT_TO_DATE` FOREIGN KEY (`ORDER_DATE_KEY`) REFERENCES `dates` (`DAY_KEY`), CONSTRAINT `FK_ORDFACT_TO_PROD` FOREIGN KEY (`PRODUCT_KEY`) REFERENCES `product` (`PRODUCT_KEY`), CONSTRAINT `FK_ORDFACT_TO_SLPS` FOREIGN KEY (`SALESPERSON_KEY`) REFERENCES `salesperson` (`SALESPERSON_KEY`), CONSTRAINT `FK_ORDFACT_TO_SNPR` FOREIGN KEY (`PRODUCT_KEY`) REFERENCES `sndproduct` (`PRODUCT_KEY`), CONSTRAINT `FK_ORDFACT_TO_SNSP` FOREIGN KEY (`SALESPERSON_KEY`) REFERENCES `sndsalesperson` (`SALESPERSON_KEY`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `order_facts` -- LOCK TABLES `order_facts` WRITE; /*!40000 ALTER TABLE `order_facts` DISABLE KEYS */; INSERT INTO `order_facts` VALUES (15,1,4,6,200000,2000,180000,1000),(38,2,3,2,200000,50000,100000,200000),(1,2,3,3,50000,10000,35000,20000),(20,3,5,1,8000000,400000,7400000,20000),(10,4,2,7,3000,500,2500,25000),(30,5,1,3,2500,100,2400,50),(22,5,1,7,1000,200,800,10),(20,6,1,4,40000,20000,20000,40),(50,7,5,9,4000,200,3800,80),(35,8,7,8,48000,4000,44000,40000),(25,9,4,1,25000,5000,20000,100),(3,10,6,2,100000,20000,80000,1000000); /*!40000 ALTER TABLE `order_facts` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `product` -- DROP TABLE IF EXISTS `product`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `product` ( `PRODUCT_KEY` int(11) NOT NULL, `CATEGORY` varchar(50) NOT NULL, `BRAND` varchar(50) NOT NULL, `PRODUCT` varchar(50) NOT NULL, `SKU` varchar(50) NOT NULL, `PACKAGETYPE` varchar(50) NOT NULL, PRIMARY KEY (`PRODUCT_KEY`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!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,'Kitchen Appliances','Captain Coffee','Standard Coffee Maker','90820','Normal'),(2,'Kitchen Appliances','Captain Coffee','Thermal Coffee Maker','987987','Fragile'),(3,'Kitchen Appliances','Captain Coffee','Deluxe Coffee Maker','590159','Fragile'),(4,'Kitchen Appliances','Kitchen Master','Can Opener','023452','Normal'),(5,'Kitchen Appliances','Kitchen Master','Blender','098209','Normal'),(6,'Kitchen Appliances','Kitchen Master','Toaster','345634','Normal'),(7,'Kitchen Appliances','Kitchen Master','Toaster Oven','234523','Normal'),(8,'Home Appliances','Reliant','Refrigerator','456345','Fragile'),(9,'Home Appliances','Reliant','Washer','345634','Normal'),(10,'Home Appliances','Reliant','Dryer','34734','Normal'),(11,'Home Appliances','CRS','Television 12\"','928474','Fragile'),(12,'Home Appliances','CRS','Television 20\"','928257','Fragile'),(13,'Home Appliances','CRS','Television 30\"','8593023','Fragile'); /*!40000 ALTER TABLE `product` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `return_facts` -- DROP TABLE IF EXISTS `return_facts`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `return_facts` ( `RETURN_KEY` int(11) NOT NULL, `SHIPPER_KEY` int(11) NOT NULL, `RETURN_DOLLARS` float NOT NULL, `EXTENDED_COST` float NOT NULL, `MARGIN_DOLLARS` float NOT NULL, `QUANTITY_RETURNED` float NOT NULL, KEY `IDX1_RETURN_FACTS` (`RETURN_KEY`), KEY `FK_RTFACTS_TO_SHIP` (`SHIPPER_KEY`), CONSTRAINT `FK_RFACTS_TO_RTRSN` FOREIGN KEY (`RETURN_KEY`) REFERENCES `returnreason` (`RETURN_KEY`), CONSTRAINT `FK_RTFACTS_TO_SHIP` FOREIGN KEY (`SHIPPER_KEY`) REFERENCES `shipper` (`SHIPPER_KEY`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `return_facts` -- LOCK TABLES `return_facts` WRITE; /*!40000 ALTER TABLE `return_facts` DISABLE KEYS */; INSERT INTO `return_facts` VALUES (1,4,12500,2500,10000,5000),(1,4,37500,7500,25000,15000),(1,2,50000,10000,40000,500000),(3,1,3000,500,2500,25000),(2,3,50000,500,45000,250),(2,2,5000,1000,4000,2),(1,2,1000,50,950,20),(1,2,3000,150,2850,60),(2,3,24000,2000,22000,20000),(3,2,25000,5000,20000,250000); /*!40000 ALTER TABLE `return_facts` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `returnreason` -- DROP TABLE IF EXISTS `returnreason`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `returnreason` ( `RETURN_KEY` int(11) NOT NULL, `REASONDESCRIPTION` varchar(254) NOT NULL, PRIMARY KEY (`RETURN_KEY`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `returnreason` -- LOCK TABLES `returnreason` WRITE; /*!40000 ALTER TABLE `returnreason` DISABLE KEYS */; INSERT INTO `returnreason` VALUES (1,'malfunction'),(2,'missing parts'),(3,'wrong order'); /*!40000 ALTER TABLE `returnreason` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `salesperson` -- DROP TABLE IF EXISTS `salesperson`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `salesperson` ( `SALESPERSON_KEY` int(11) NOT NULL, `SALESPERSON_NAME` varchar(50) NOT NULL, `SALESPERSON_NUMBER` varchar(50) NOT NULL, `PHONE_NUMBER` varchar(50) NOT NULL, `TERRITORY_NAME` varchar(50) NOT NULL, `SALES_REGION` varchar(50) NOT NULL, PRIMARY KEY (`SALESPERSON_KEY`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `salesperson` -- LOCK TABLES `salesperson` WRITE; /*!40000 ALTER TABLE `salesperson` DISABLE KEYS */; INSERT INTO `salesperson` VALUES (1,'Jeff Archer','AF5','987 555 9493','North','Zone 1'),(2,'Mary Sales','DF3','876 555 2938','East','Zone 2'),(3,'Jim Loot','UK7','389 555 1245','North','Zone 1'),(4,'John Cross','EI8','967 555 3849','South','Zone 1'),(5,'David Fern','BH6','892 555 9087','East','Zone 2'),(6,'Donald Campbell','ML5','987 555 5831','West','Zone 2'),(7,'Jesse Smith','JH2','567 555 2897','West','Zone 2'),(8,'Tony Mendelson','LK9','986 555 3847','West','Zone 2'),(9,'Adam Leer','PJ2','879 555 1425','East','Zone 2'),(10,'Caroline Springer','JK9','980 555 1840','South','Zone 1'); /*!40000 ALTER TABLE `salesperson` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `shipment_facts` -- DROP TABLE IF EXISTS `shipment_facts`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `shipment_facts` ( `SHIPMENT_DATE_KEY` int(11) NOT NULL, `ORDER_DATE_KEY` int(11) NOT NULL, `PRODUCT_KEY` int(11) NOT NULL, `CUSTOMER_KEY` int(11) NOT NULL, `SALESPERSON_KEY` int(11) NOT NULL, `SHIPPER_KEY` int(11) NOT NULL, `SALES_DOLLARS` float NOT NULL, `EXTENDED_COST` float NOT NULL, `MARGIN_DOLLARS` float NOT NULL, `QUANTITY_SHIPPED` float NOT NULL, KEY `IDX1_SHIPMNT_FACTS` (`SHIPMENT_DATE_KEY`), KEY `IDX2_SHIPMNT_FACTS` (`ORDER_DATE_KEY`), KEY `IDX3_SHIPMNT_FACTS` (`PRODUCT_KEY`), KEY `IDX4_SHIPMNT_FACTS` (`CUSTOMER_KEY`), KEY `IDX5_SHIPMNT_FACTS` (`SALESPERSON_KEY`), KEY `IDX6_SHIPMNT_FACTS` (`SHIPPER_KEY`), CONSTRAINT `FK_SHIPFACT_PROD` FOREIGN KEY (`PRODUCT_KEY`) REFERENCES `product` (`PRODUCT_KEY`), CONSTRAINT `FK_SHIPFACT_TO_CST` FOREIGN KEY (`CUSTOMER_KEY`) REFERENCES `customer` (`CUSTOMER_KEY`), CONSTRAINT `FK_SHIPFACT_TO_DTE` FOREIGN KEY (`SHIPMENT_DATE_KEY`) REFERENCES `dates` (`DAY_KEY`), CONSTRAINT `FK_SHIPFACT_TO_SCS` FOREIGN KEY (`CUSTOMER_KEY`) REFERENCES `sndcustomer` (`CUSTOMER_KEY`), CONSTRAINT `FK_SHIPFACT_TO_SHP` FOREIGN KEY (`SHIPPER_KEY`) REFERENCES `shipper` (`SHIPPER_KEY`), CONSTRAINT `FK_SHIPFACT_TO_SLP` FOREIGN KEY (`SALESPERSON_KEY`) REFERENCES `salesperson` (`SALESPERSON_KEY`), CONSTRAINT `FK_SHIPFACT_TO_SNP` FOREIGN KEY (`PRODUCT_KEY`) REFERENCES `sndproduct` (`PRODUCT_KEY`), CONSTRAINT `FK_SHIPFACT_TO_SNS` FOREIGN KEY (`SALESPERSON_KEY`) REFERENCES `sndsalesperson` (`SALESPERSON_KEY`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `shipment_facts` -- LOCK TABLES `shipment_facts` WRITE; /*!40000 ALTER TABLE `shipment_facts` DISABLE KEYS */; INSERT INTO `shipment_facts` VALUES (1,1,2,3,3,4,12500,2500,10000,5000),(10,1,2,3,3,4,37500,7500,25000,15000),(10,3,10,6,2,2,50000,10000,40000,500000),(10,10,4,2,7,1,3000,500,2500,25000),(18,15,1,4,6,3,50000,500,45000,250),(20,15,1,4,6,3,50000,500,45000,250),(20,20,3,5,1,1,2000000,100000,1900000,5000),(26,22,5,1,7,2,1000,200,800,10),(27,20,6,1,4,2,30000,15000,15000,20),(30,20,6,1,4,2,30000,15000,15000,20),(30,25,9,4,1,2,20000,4000,16000,8),(34,30,5,1,3,4,2500,100,2400,50),(39,20,3,5,1,1,2000000,100000,1900000,5000),(40,3,10,6,2,2,25000,5000,20000,250000),(40,15,1,4,6,3,50000,500,4500,250),(40,35,8,7,8,3,24000,2000,22000,20000),(41,15,1,4,6,3,50000,500,4500,250),(43,20,3,5,1,1,4000000,200000,3800000,10000),(45,38,2,3,9,1,100000,25000,75000,100000),(50,25,9,4,1,2,5000,1000,4000,2),(60,50,7,5,9,2,1000,50,950,20),(65,50,7,5,9,2,3000,150,2850,60),(69,35,8,7,8,3,24000,2000,22000,20000),(70,3,10,6,2,2,25000,5000,20000,250000),(70,38,2,3,9,1,100000,25000,75000,100000); /*!40000 ALTER TABLE `shipment_facts` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `shipper` -- DROP TABLE IF EXISTS `shipper`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `shipper` ( `SHIPPER_KEY` int(11) NOT NULL, `SHIPPER_NAME` varchar(50) NOT NULL, `SHIPPER_ADDRESS` varchar(50) NOT NULL, `SHIPPER_CITY` varchar(50) NOT NULL, `SHIPPER_STATE` varchar(50) NOT NULL, `SHIPPER_TYPE` varchar(50) NOT NULL, PRIMARY KEY (`SHIPPER_KEY`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `shipper` -- LOCK TABLES `shipper` WRITE; /*!40000 ALTER TABLE `shipper` DISABLE KEYS */; INSERT INTO `shipper` VALUES (1,'Global Shipper','234 Market St.','San Francisco','CA','Sea'),(2,'Multi-world Trans','1827 Columbus','San Francisco','CA','Land'),(3,'Shippers Ltd','2836 Ocean View','Miami','FL','Air'),(4,'On-the-move','2345 3rd St.','San Francisco','CA','Sea, Land & Air'); /*!40000 ALTER TABLE `shipper` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `sndcustbilling_city` -- DROP TABLE IF EXISTS `sndcustbilling_city`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `sndcustbilling_city` ( `BILLING_CITY_CODE` int(11) NOT NULL, `BILLING_CITY` varchar(254) NOT NULL, `BILLING_STATE_CODE` int(11) NOT NULL, PRIMARY KEY (`BILLING_CITY_CODE`), KEY `FK_CBQTY_TO_CSTBST` (`BILLING_STATE_CODE`), CONSTRAINT `FK_CBQTY_TO_CSTBST` FOREIGN KEY (`BILLING_STATE_CODE`) REFERENCES `sndcustbilling_state` (`BILLING_STATE_CODE`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `sndcustbilling_city` -- LOCK TABLES `sndcustbilling_city` WRITE; /*!40000 ALTER TABLE `sndcustbilling_city` DISABLE KEYS */; INSERT INTO `sndcustbilling_city` VALUES (1,'Alexandria',2),(2,'Los Angeles',1),(3,'Marin County',1),(4,'Sacramento',1),(5,'San Fernando',1),(6,'San Francisco',1),(7,'Santa Cruz',1); /*!40000 ALTER TABLE `sndcustbilling_city` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `sndcustbilling_state` -- DROP TABLE IF EXISTS `sndcustbilling_state`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `sndcustbilling_state` ( `BILLING_STATE_CODE` int(11) NOT NULL, `BILLING_STATE` varchar(50) NOT NULL, PRIMARY KEY (`BILLING_STATE_CODE`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `sndcustbilling_state` -- LOCK TABLES `sndcustbilling_state` WRITE; /*!40000 ALTER TABLE `sndcustbilling_state` DISABLE KEYS */; INSERT INTO `sndcustbilling_state` VALUES (1,'CA'),(2,'VA'); /*!40000 ALTER TABLE `sndcustbilling_state` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `sndcustomer` -- DROP TABLE IF EXISTS `sndcustomer`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `sndcustomer` ( `CUSTOMER_KEY` int(11) NOT NULL, `CUSTOMER_NAME` varchar(254) NOT NULL, `ACCOUNT_NUMBER` int(11) NOT NULL, `BILLING_ADDRESS` varchar(254) NOT NULL, `BILLING_CITY_CODE` int(11) NOT NULL, PRIMARY KEY (`CUSTOMER_KEY`), KEY `FK_SCST_CUSTBILLNG` (`BILLING_CITY_CODE`), CONSTRAINT `FK_SCST_CUSTBILLNG` FOREIGN KEY (`BILLING_CITY_CODE`) REFERENCES `sndcustbilling_city` (`BILLING_CITY_CODE`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `sndcustomer` -- LOCK TABLES `sndcustomer` WRITE; /*!40000 ALTER TABLE `sndcustomer` DISABLE KEYS */; INSERT INTO `sndcustomer` VALUES (1,'Fargo Products',98729874,'1876 Palm Ave',6),(2,'Kingston Outlets',98723948,'2341 Telegraph Hill',5),(3,'Marcus CRS',98734556,'4768 Palm Street',2),(4,'ServiceLand',83756263,'9459 Tenesse Lane',4),(5,'Electronic Warehouse',23452345,'2727 Fortune Ave',6),(6,'SuperClub',48591030,'8656 Ocean Beach',7),(7,'SuperClub',2937958,'2829 Grant Lane',3),(8,'SuperClub',29384656,'5784 Turnpike',2),(9,'Jenkins',29834733,'4589 Eisenhower',1),(10,'Circuit Branches',30495283,'3689 Gower Ave',6); /*!40000 ALTER TABLE `sndcustomer` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `sndproduct` -- DROP TABLE IF EXISTS `sndproduct`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `sndproduct` ( `PRODUCT_KEY` int(11) NOT NULL, `PRODUCT` varchar(254) NOT NULL, `BRANDCODE` int(11) NOT NULL, `PACKAGETYPECODE` int(11) NOT NULL, `SKU` varchar(254) NOT NULL, PRIMARY KEY (`PRODUCT_KEY`), KEY `FK_PROD_TO_PRDBRND` (`BRANDCODE`), KEY `FK_SNDPROD_TO_PROD` (`PACKAGETYPECODE`), CONSTRAINT `FK_PROD_TO_PRDBRND` FOREIGN KEY (`BRANDCODE`) REFERENCES `sndproductbrand` (`BRANDCODE`), CONSTRAINT `FK_SNDPROD_TO_PROD` FOREIGN KEY (`PACKAGETYPECODE`) REFERENCES `sndproductpackagetype` (`PACKAGETYPECODE`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `sndproduct` -- LOCK TABLES `sndproduct` WRITE; /*!40000 ALTER TABLE `sndproduct` DISABLE KEYS */; INSERT INTO `sndproduct` VALUES (1,'Standard Coffee Maker',1,2,'90820'),(2,'Thermal Coffee Maker',1,1,'987987'),(3,'Deluxe Coffee Maker',1,1,'590159'),(4,'Can Opener',3,2,'023452'),(5,'Blender',3,2,'098209'),(6,'Toaster',3,2,'345634'),(7,'Toaster Oven',3,2,'234523'),(8,'Refrigerator',4,1,'456345'),(9,'Washer',4,2,'345634'),(10,'Dryer',4,2,'34734'),(11,'Television 12\"',2,1,'928474'),(12,'Television 20\"',2,1,'928257'),(13,'Television 30\"',2,1,'8593023'); /*!40000 ALTER TABLE `sndproduct` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `sndproductbrand` -- DROP TABLE IF EXISTS `sndproductbrand`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `sndproductbrand` ( `BRANDCODE` int(11) NOT NULL, `BRAND` varchar(254) NOT NULL, `CATEGORYCODE` int(11) NOT NULL, PRIMARY KEY (`BRANDCODE`), KEY `FK_PRBRND_TO_PRCAT` (`CATEGORYCODE`), CONSTRAINT `FK_PRBRND_TO_PRCAT` FOREIGN KEY (`CATEGORYCODE`) REFERENCES `sndproductcategory` (`CATEGORYCODE`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `sndproductbrand` -- LOCK TABLES `sndproductbrand` WRITE; /*!40000 ALTER TABLE `sndproductbrand` DISABLE KEYS */; INSERT INTO `sndproductbrand` VALUES (1,'Captain Coffee',2),(2,'CRS',1),(3,'Kitchen Master',2),(4,'Reliant',1); /*!40000 ALTER TABLE `sndproductbrand` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `sndproductcategory` -- DROP TABLE IF EXISTS `sndproductcategory`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `sndproductcategory` ( `CATEGORYCODE` int(11) NOT NULL, `CATEGORY` varchar(254) NOT NULL, PRIMARY KEY (`CATEGORYCODE`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `sndproductcategory` -- LOCK TABLES `sndproductcategory` WRITE; /*!40000 ALTER TABLE `sndproductcategory` DISABLE KEYS */; INSERT INTO `sndproductcategory` VALUES (1,'Home Appliances'),(2,'Kitchen Appliances'); /*!40000 ALTER TABLE `sndproductcategory` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `sndproductpackagetype` -- DROP TABLE IF EXISTS `sndproductpackagetype`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `sndproductpackagetype` ( `PACKAGETYPECODE` int(11) NOT NULL, `PACKAGETYPE` varchar(50) NOT NULL, PRIMARY KEY (`PACKAGETYPECODE`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `sndproductpackagetype` -- LOCK TABLES `sndproductpackagetype` WRITE; /*!40000 ALTER TABLE `sndproductpackagetype` DISABLE KEYS */; INSERT INTO `sndproductpackagetype` VALUES (1,'Fragile'),(2,'Normal'); /*!40000 ALTER TABLE `sndproductpackagetype` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `sndsalesperson` -- DROP TABLE IF EXISTS `sndsalesperson`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `sndsalesperson` ( `SALESPERSON_KEY` int(11) NOT NULL, `SALESPERSON_NAME` varchar(254) NOT NULL, `SALESPERSON_NUMBER` varchar(254) NOT NULL, `PHONE_NUMBER` varchar(254) NOT NULL, `SALESTERRITORYCODE` int(11) NOT NULL, PRIMARY KEY (`SALESPERSON_KEY`), KEY `FK_SLSP_TO_SLSTERR` (`SALESTERRITORYCODE`), CONSTRAINT `FK_SLSP_TO_SLSTERR` FOREIGN KEY (`SALESTERRITORYCODE`) REFERENCES `sndsalesterritory` (`SALESTERRITORYCODE`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `sndsalesperson` -- LOCK TABLES `sndsalesperson` WRITE; /*!40000 ALTER TABLE `sndsalesperson` DISABLE KEYS */; INSERT INTO `sndsalesperson` VALUES (1,'Jeff Archer','AF5','987 555 9493',2),(2,'Mary Sales','DF3','876 555 2938',1),(3,'Jim Loot','UK7','389 555 1245',2),(4,'John Cross','EI8','967 555 3849',3),(5,'David Fern','BH6','892 555 9087',1),(6,'Donald Campbell','ML5','987 555 5831',4),(7,'Jesse Smith','JH2','567 555 2897',4),(8,'Tony Mendelson','LK9','986 555 3847',4),(9,'Adam Leer','PJ2','879 555 1425',1),(10,'Caroline Springer','JK9','980 555 1840',3); /*!40000 ALTER TABLE `sndsalesperson` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `sndsalesregion` -- DROP TABLE IF EXISTS `sndsalesregion`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `sndsalesregion` ( `SALESREGIONCODE` int(11) NOT NULL, `SALES_REGION` varchar(254) NOT NULL, PRIMARY KEY (`SALESREGIONCODE`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `sndsalesregion` -- LOCK TABLES `sndsalesregion` WRITE; /*!40000 ALTER TABLE `sndsalesregion` DISABLE KEYS */; INSERT INTO `sndsalesregion` VALUES (1,'Zone 1'),(2,'Zone 2'); /*!40000 ALTER TABLE `sndsalesregion` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `sndsalesterritory` -- DROP TABLE IF EXISTS `sndsalesterritory`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `sndsalesterritory` ( `SALESTERRITORYCODE` int(11) NOT NULL, `TERRITORY_NAME` varchar(254) NOT NULL, `SALESREGIONCODE` int(11) NOT NULL, PRIMARY KEY (`SALESTERRITORYCODE`), KEY `FK_SLTERR_TO_SLRGN` (`SALESREGIONCODE`), CONSTRAINT `FK_SLTERR_TO_SLRGN` FOREIGN KEY (`SALESREGIONCODE`) REFERENCES `sndsalesregion` (`SALESREGIONCODE`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `sndsalesterritory` -- LOCK TABLES `sndsalesterritory` WRITE; /*!40000 ALTER TABLE `sndsalesterritory` DISABLE KEYS */; INSERT INTO `sndsalesterritory` VALUES (1,'East',2),(2,'North',1),(3,'South',1),(4,'West',2); /*!40000 ALTER TABLE `sndsalesterritory` 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 2015-08-01 2:30:58