TDV615d0

1CREATE DATABASE  IF NOT EXISTS `pharmacy` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */;
2USE `pharmacy`;
3-- MySQL dump 10.13  Distrib 8.0.12, for macos10.13 (x86_64)
4--
5-- Host: localhost    Database: pharmacy
6-- ------------------------------------------------------
7-- Server version	8.0.12
8
9/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
10/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
11/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
12 SET NAMES utf8 ;
13/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
14/*!40103 SET TIME_ZONE='+00:00' */;
15/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
16/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
17/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
18/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
19
20--
21-- Table structure for table `cities`
22--
23
24DROP TABLE IF EXISTS `cities`;
25/*!40101 SET @saved_cs_client     = @@character_set_client */;
26 SET character_set_client = utf8mb4 ;
27CREATE TABLE `cities` (
28  `id` int(11) NOT NULL,
29  `name` varchar(50) DEFAULT NULL,
30  `zip` int(11) NOT NULL,
31  PRIMARY KEY (`id`)
32) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
33/*!40101 SET character_set_client = @saved_cs_client */;
34
35--
36-- Dumping data for table `cities`
37--
38
39LOCK TABLES `cities` WRITE;
40/*!40000 ALTER TABLE `cities` DISABLE KEYS */;
41INSERT INTO `cities` VALUES (1,'Long Beach',90815),(2,'Los Angeles',90001);
42/*!40000 ALTER TABLE `cities` ENABLE KEYS */;
43UNLOCK TABLES;
44
45--
46-- Table structure for table `customer_contacts`
47--
48
49DROP TABLE IF EXISTS `customer_contacts`;
50/*!40101 SET @saved_cs_client     = @@character_set_client */;
51 SET character_set_client = utf8mb4 ;
52CREATE TABLE `customer_contacts` (
53  `id` int(11) NOT NULL,
54  `customer_id` int(11) NOT NULL,
55  `contact` varchar(30) NOT NULL,
56  PRIMARY KEY (`id`),
57  KEY `customer_id` (`customer_id`),
58  CONSTRAINT `customer_contacts_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`)
59) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
60/*!40101 SET character_set_client = @saved_cs_client */;
61
62--
63-- Dumping data for table `customer_contacts`
64--
65
66LOCK TABLES `customer_contacts` WRITE;
67/*!40000 ALTER TABLE `customer_contacts` DISABLE KEYS */;
68INSERT INTO `customer_contacts` VALUES (1,1,'3105634565'),(2,1,'jeremy@csulb.edu'),(3,2,'9876783732');
69/*!40000 ALTER TABLE `customer_contacts` ENABLE KEYS */;
70UNLOCK TABLES;
71
72--
73-- Table structure for table `customers`
74--
75
76DROP TABLE IF EXISTS `customers`;
77/*!40101 SET @saved_cs_client     = @@character_set_client */;
78 SET character_set_client = utf8mb4 ;
79CREATE TABLE `customers` (
80  `id` int(11) NOT NULL,
81  `name` varchar(50) DEFAULT NULL,
82  `city_id` int(11) NOT NULL,
83  PRIMARY KEY (`id`),
84  KEY `city_id` (`city_id`),
85  CONSTRAINT `customers_ibfk_1` FOREIGN KEY (`city_id`) REFERENCES `cities` (`id`)
86) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
87/*!40101 SET character_set_client = @saved_cs_client */;
88
89--
90-- Dumping data for table `customers`
91--
92
93LOCK TABLES `customers` WRITE;
94/*!40000 ALTER TABLE `customers` DISABLE KEYS */;
95INSERT INTO `customers` VALUES (1,'Jim Lee',1),(2,'John Sage',1);
96/*!40000 ALTER TABLE `customers` ENABLE KEYS */;
97UNLOCK TABLES;
98
99--
100-- Table structure for table `orders`
101--
102
103DROP TABLE IF EXISTS `orders`;
104/*!40101 SET @saved_cs_client     = @@character_set_client */;
105 SET character_set_client = utf8mb4 ;
106CREATE TABLE `orders` (
107  `id` int(11) NOT NULL,
108  `total_price` int(11) NOT NULL,
109  `date` date NOT NULL,
110  `store_code` int(11) NOT NULL,
111  PRIMARY KEY (`id`),
112  KEY `store_code` (`store_code`),
113  CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`store_code`) REFERENCES `stores` (`code`)
114) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
115/*!40101 SET character_set_client = @saved_cs_client */;
116
117--
118-- Dumping data for table `orders`
119--
120
121LOCK TABLES `orders` WRITE;
122/*!40000 ALTER TABLE `orders` DISABLE KEYS */;
123INSERT INTO `orders` VALUES (401,400,'2015-04-15',1311001),(402,600,'2015-03-15',1311002),(403,50,'2025-03-15',1311003),(404,440,'2025-03-15',1311003);
124/*!40000 ALTER TABLE `orders` ENABLE KEYS */;
125UNLOCK TABLES;
126
127--
128-- Table structure for table `orders_details`
129--
130
131DROP TABLE IF EXISTS `orders_details`;
132/*!40101 SET @saved_cs_client     = @@character_set_client */;
133 SET character_set_client = utf8mb4 ;
134CREATE TABLE `orders_details` (
135  `id` int(11) NOT NULL,
136  `product_id` int(11) NOT NULL,
137  `amount` int(11) NOT NULL,
138  `order_id` int(11) DEFAULT NULL,
139  PRIMARY KEY (`id`),
140  KEY `product_id` (`product_id`),
141  KEY `order_id` (`order_id`),
142  CONSTRAINT `orders_details_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`),
143  CONSTRAINT `orders_details_ibfk_2` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`)
144) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
145/*!40101 SET character_set_client = @saved_cs_client */;
146
147--
148-- Dumping data for table `orders_details`
149--
150
151LOCK TABLES `orders_details` WRITE;
152/*!40000 ALTER TABLE `orders_details` DISABLE KEYS */;
153INSERT INTO `orders_details` VALUES (1,1002,2,401),(2,1002,2,402),(3,1003,3,402);
154/*!40000 ALTER TABLE `orders_details` ENABLE KEYS */;
155UNLOCK TABLES;
156
157--
158-- Table structure for table `products`
159--
160
161DROP TABLE IF EXISTS `products`;
162/*!40101 SET @saved_cs_client     = @@character_set_client */;
163 SET character_set_client = utf8mb4 ;
164CREATE TABLE `products` (
165  `id` int(11) NOT NULL,
166  `name` varchar(20) NOT NULL,
167  `qty_stk` int(11) NOT NULL,
168  `cost` int(11) NOT NULL,
169  PRIMARY KEY (`id`)
170) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
171/*!40101 SET character_set_client = @saved_cs_client */;
172
173--
174-- Dumping data for table `products`
175--
176
177LOCK TABLES `products` WRITE;
178/*!40000 ALTER TABLE `products` DISABLE KEYS */;
179INSERT INTO `products` VALUES (1001,'Ibuprofen',300,190),(1002,'Tylenol',100,200),(1003,'Aleve',200,50);
180/*!40000 ALTER TABLE `products` ENABLE KEYS */;
181UNLOCK TABLES;
182
183--
184-- Table structure for table `stocks`
185--
186
187DROP TABLE IF EXISTS `stocks`;
188/*!40101 SET @saved_cs_client     = @@character_set_client */;
189 SET character_set_client = utf8mb4 ;
190CREATE TABLE `stocks` (
191  `id` int(11) NOT NULL,
192  `product_id` int(11) NOT NULL,
193  `qty_added` int(11) NOT NULL,
194  `renew_date` date NOT NULL,
195  `store_code` int(11) NOT NULL,
196  PRIMARY KEY (`id`),
197  KEY `product_id` (`product_id`),
198  CONSTRAINT `stocks_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`)
199) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
200/*!40101 SET character_set_client = @saved_cs_client */;
201
202--
203-- Dumping data for table `stocks`
204--
205
206LOCK TABLES `stocks` WRITE;
207/*!40000 ALTER TABLE `stocks` DISABLE KEYS */;
208INSERT INTO `stocks` VALUES (1,1001,3,'2014-04-15',1311001),(2,1002,2,'2014-04-15',1311001),(3,1003,5,'2012-04-15',1311002);
209/*!40000 ALTER TABLE `stocks` ENABLE KEYS */;
210UNLOCK TABLES;
211
212--
213-- Table structure for table `stores`
214--
215
216DROP TABLE IF EXISTS `stores`;
217/*!40101 SET @saved_cs_client     = @@character_set_client */;
218 SET character_set_client = utf8mb4 ;
219CREATE TABLE `stores` (
220  `code` int(11) NOT NULL,
221  `name` varchar(20) NOT NULL,
222  `city_id` int(11) NOT NULL,
223  PRIMARY KEY (`code`)
224) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
225/*!40101 SET character_set_client = @saved_cs_client */;
226
227--
228-- Dumping data for table `stores`
229--
230
231LOCK TABLES `stores` WRITE;
232/*!40000 ALTER TABLE `stores` DISABLE KEYS */;
233INSERT INTO `stores` VALUES (1311001,'CVS',1),(1311002,'Apollo Pharmacy',2),(1311003,'Rite Aid',1);
234/*!40000 ALTER TABLE `stores` ENABLE KEYS */;
235UNLOCK TABLES;
236
237--
238-- Dumping events for database 'pharmacy'
239--
240
241--
242-- Dumping routines for database 'pharmacy'
243--
244/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
245
246/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
247/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
248/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
249/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
250/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
251/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
252/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
253
254-- Dump completed on 2019-02-07 12:57:58