· 9 years ago · Jan 20, 2017, 02:18 PM
1-- MySQL Administrator dump 1.4
2--
3-- ------------------------------------------------------
4-- Server version 5.4.3-beta-community
5
6
7/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
8/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
9/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
10/*!40101 SET NAMES utf8 */;
11
12/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
13/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
14/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
15
16
17--
18-- Create schema test
19--
20
21CREATE DATABASE /*!32312 IF NOT EXISTS*/ test;
22USE test;
23
24--
25-- Table structure for table `test`.`barcode`
26--
27
28DROP TABLE IF EXISTS `barcode`;
29CREATE TABLE `barcode` (
30 `barcode_ean` char(13) NOT NULL,
31 `item_id` int(11) NOT NULL,
32 PRIMARY KEY (`barcode_ean`),
33 KEY `item_id` (`item_id`),
34 CONSTRAINT `barcode_item_id_fk` FOREIGN KEY (`item_id`) REFERENCES `item` (`item_id`)
35) ENGINE=InnoDB DEFAULT CHARSET=utf8;
36
37--
38-- Dumping data for table `test`.`barcode`
39--
40
41/*!40000 ALTER TABLE `barcode` DISABLE KEYS */;
42INSERT INTO `barcode` (`barcode_ean`,`item_id`) VALUES
43 ('6241527836173',1),
44 ('6241574635234',2),
45 ('6241527746363',3),
46 ('6264537836173',3),
47 ('7465743843764',4),
48 ('3453458677628',5),
49 ('6434564564544',6),
50 ('8476736836876',7),
51 ('6241234586487',8),
52 ('9473625532534',8),
53 ('9473627464543',8),
54 ('4587263646878',9),
55 ('2239872376872',11),
56 ('9879879837489',11);
57/*!40000 ALTER TABLE `barcode` ENABLE KEYS */;
58
59
60--
61-- Table structure for table `test`.`customer`
62--
63
64DROP TABLE IF EXISTS `customer`;
65CREATE TABLE `customer` (
66 `customer_id` int(11) NOT NULL AUTO_INCREMENT,
67 `title` char(4) DEFAULT NULL,
68 `fname` varchar(32) DEFAULT NULL,
69 `lname` varchar(32) NOT NULL,
70 `addressline` varchar(64) DEFAULT NULL,
71 `town` varchar(32) DEFAULT NULL,
72 `zipcode` char(10) NOT NULL,
73 `phone` varchar(16) DEFAULT NULL,
74 PRIMARY KEY (`customer_id`)
75) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;
76
77--
78-- Dumping data for table `test`.`customer`
79--
80
81/*!40000 ALTER TABLE `customer` DISABLE KEYS */;
82INSERT INTO `customer` (`customer_id`,`title`,`fname`,`lname`,`addressline`,`town`,`zipcode`,`phone`) VALUES
83 (1,'Miss','Jenny','Stones','27 Rowan Avenue','Hightown','NT2 1AQ','023 9876'),
84 (2,'Mr','Andrew','Stones','52 The Willows','Lowtown','LT5 7RA','876 3527'),
85 (3,'Miss','Alex','Matthew','4 The Street','Nicetown','NT2 2TX','010 4567'),
86 (4,'Mr','Adrian','Matthew','The Barn','Yuleville','YV67 2WR','487 3871'),
87 (5,'Mr','Simon','Cozens','7 Shady Lane','Oahenham','OA3 6QW','514 5926'),
88 (6,'Mr','Neil','Matthew','5 Pasture Lane','Nicetown','NT3 7RT','267 1232'),
89 (7,'Mr','Richard','Stones','34 Holly Way','Bingham','BG4 2WE','342 5982'),
90 (8,'Mrs','Anna','Stones','34 Holly Way','Bingham','BG4 2WE','342 5982'),
91 (9,'Mrs','Christine','Hickman','36 Queen Street','Histon','HT3 5EM','342 5432'),
92 (10,'Mr','Mike','Howard','86 Dysart Street','Tibsville','TB3 7FG','505 5482'),
93 (11,'Mr','Dave','Jones','54 Vale Rise','Bingham','BG3 8GD','342 8264'),
94 (12,'Mr','Richard','Neill','42 Thached way','Winersby','WB3 6GQ','505 6482'),
95 (13,'Mrs','Laura','Hendy','73 Margeritta Way','Oxbridge','OX2 3HX','821 2335');
96INSERT INTO `customer` (`customer_id`,`title`,`fname`,`lname`,`addressline`,`town`,`zipcode`,`phone`) VALUES
97 (14,'Mr','Bill','Neill','2 Beamer Street','Welltown','WT3 8GM','435 1234'),
98 (15,'Mr','David','Hudson','4 The Square','Milltown','MT2 6RT','961 4526'),
99 (16,NULL,NULL,'Wolski',NULL,NULL,'MX2 6RV',NULL);
100/*!40000 ALTER TABLE `customer` ENABLE KEYS */;
101
102
103--
104-- Table structure for table `test`.`item`
105--
106
107DROP TABLE IF EXISTS `item`;
108CREATE TABLE `item` (
109 `item_id` int(11) NOT NULL AUTO_INCREMENT,
110 `description` varchar(64) NOT NULL,
111 `cost_price` decimal(7,2) DEFAULT NULL,
112 `sell_price` decimal(7,2) DEFAULT NULL,
113 PRIMARY KEY (`item_id`)
114) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;
115
116--
117-- Dumping data for table `test`.`item`
118--
119
120/*!40000 ALTER TABLE `item` DISABLE KEYS */;
121INSERT INTO `item` (`item_id`,`description`,`cost_price`,`sell_price`) VALUES
122 (1,'Wood Puzzle','15.23','21.95'),
123 (2,'Rubik Cube','7.45','11.49'),
124 (3,'Linux CD','1.99','2.49'),
125 (4,'Tissues','2.11','3.99'),
126 (5,'Picture Frame','7.54','9.95'),
127 (6,'Fan Small','9.23','15.75'),
128 (7,'Fan Large','13.36','19.95'),
129 (8,'Toothbrush','0.75','1.45'),
130 (9,'Roman Coin','2.34','2.45'),
131 (10,'Carrier Bag','0.01','0.00'),
132 (11,'Speakers','19.73','25.32'),
133 (12,'SQL Server 2005',NULL,NULL);
134/*!40000 ALTER TABLE `item` ENABLE KEYS */;
135
136
137--
138-- Table structure for table `test`.`orderinfo`
139--
140
141DROP TABLE IF EXISTS `orderinfo`;
142CREATE TABLE `orderinfo` (
143 `orderinfo_id` int(11) NOT NULL AUTO_INCREMENT,
144 `customer_id` int(11) NOT NULL,
145 `date_placed` date NOT NULL,
146 `date_shipped` date DEFAULT NULL,
147 `shipping` decimal(7,2) DEFAULT NULL,
148 PRIMARY KEY (`orderinfo_id`),
149 KEY `customer_id` (`customer_id`),
150 CONSTRAINT `orderinfo_customer_id_fk` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`)
151) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
152
153--
154-- Dumping data for table `test`.`orderinfo`
155--
156
157/*!40000 ALTER TABLE `orderinfo` DISABLE KEYS */;
158INSERT INTO `orderinfo` (`orderinfo_id`,`customer_id`,`date_placed`,`date_shipped`,`shipping`) VALUES
159 (1,3,'2000-03-13','2000-03-17','2.99'),
160 (2,8,'2000-06-23','2000-06-23','0.00'),
161 (3,15,'2000-09-02','2000-09-12','3.99'),
162 (4,13,'2000-09-03','2000-09-10','2.99'),
163 (5,8,'2000-07-21','2000-07-24','0.00');
164/*!40000 ALTER TABLE `orderinfo` ENABLE KEYS */;
165
166
167--
168-- Table structure for table `test`.`orderline`
169--
170
171DROP TABLE IF EXISTS `orderline`;
172CREATE TABLE `orderline` (
173 `orderinfo_id` int(11) NOT NULL,
174 `item_id` int(11) NOT NULL,
175 `quantity` int(11) NOT NULL,
176 PRIMARY KEY (`orderinfo_id`,`item_id`),
177 KEY `item_id` (`item_id`),
178 CONSTRAINT `orderline_orderlinfo_id_fk` FOREIGN KEY (`orderinfo_id`) REFERENCES `orderinfo` (`orderinfo_id`),
179 CONSTRAINT `orderline_item_id_fk` FOREIGN KEY (`item_id`) REFERENCES `item` (`item_id`)
180) ENGINE=InnoDB DEFAULT CHARSET=utf8;
181
182--
183-- Dumping data for table `test`.`orderline`
184--
185
186/*!40000 ALTER TABLE `orderline` DISABLE KEYS */;
187INSERT INTO `orderline` (`orderinfo_id`,`item_id`,`quantity`) VALUES
188 (1,4,1),
189 (1,7,1),
190 (1,9,1),
191 (2,1,1),
192 (2,4,2),
193 (2,7,2),
194 (2,10,1),
195 (3,1,1),
196 (3,2,1),
197 (4,5,2),
198 (5,1,1),
199 (5,3,1);
200/*!40000 ALTER TABLE `orderline` ENABLE KEYS */;
201
202
203--
204-- Table structure for table `test`.`stock`
205--
206
207DROP TABLE IF EXISTS `stock`;
208CREATE TABLE `stock` (
209 `item_id` int(11) NOT NULL AUTO_INCREMENT,
210 `quantity` int(11) NOT NULL,
211 PRIMARY KEY (`item_id`),
212 CONSTRAINT `stock_item_id_fk` FOREIGN KEY (`item_id`) REFERENCES `item` (`item_id`)
213) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
214
215--
216-- Dumping data for table `test`.`stock`
217--
218
219/*!40000 ALTER TABLE `stock` DISABLE KEYS */;
220INSERT INTO `stock` (`item_id`,`quantity`) VALUES
221 (1,12),
222 (2,2),
223 (4,8),
224 (5,3),
225 (7,8),
226 (8,18),
227 (10,1);
228/*!40000 ALTER TABLE `stock` ENABLE KEYS */;
229
230/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
231/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
232/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
233/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
234/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
235/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
236/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;