A19aPQQk

1#
2# DUMP FILE
3#
4# Database is ported from MS Access
5#------------------------------------------------------------------
6# Created using "MS Access to MySQL" form http://www.bullzip.com
7# Program Version 5.1.242
8#
9# OPTIONS:
10#   sourcefilename=C:UsersDilanDesktopDatabase teaching materialDatabase assignmentsAssignment oneinventory_system.mdb
11#   sourceusername=
12#   sourcepassword=
13#   sourcesystemdatabase=
14#   destinationdatabase=inventory
15#   storageengine=InnoDB
16#   dropdatabase=0
17#   createtables=1
18#   unicode=1
19#   autocommit=1
20#   transferdefaultvalues=1
21#   transferindexes=1
22#   transferautonumbers=1
23#   transferrecords=1
24#   columnlist=1
25#   tableprefix=
26#   negativeboolean=0
27#   ignorelargeblobs=0
28#   memotype=LONGTEXT
29#
30
31CREATE DATABASE IF NOT EXISTS `inventory`;
32USE `inventory`;
33
34#
35# Table structure for table 'Customer'
36#
37
38DROP TABLE IF EXISTS `Customer`;
39
40CREATE TABLE `Customer` (
41  `Customer_ID` INTEGER AUTO_INCREMENT, 
42  `Name` VARCHAR(25) NOT NULL, 
43  `Customer_Descr` VARCHAR(80), 
44  `Address` VARCHAR(50), 
45  `Postcode` VARCHAR(50), 
46  `Inactive` TINYINT(1) DEFAULT 0, 
47  UNIQUE (`Customer_ID`), 
48  INDEX (`Postcode`), 
49  PRIMARY KEY (`Name`)
50) ENGINE=innodb DEFAULT CHARSET=utf8;
51
52SET autocommit=1;
53
54#
55# Dumping data for table 'Customer'
56#
57
58INSERT INTO `Customer` (`Customer_ID`, `Name`, `Customer_Descr`, `Address`, `Postcode`, `Inactive`) VALUES (1, 'ABC-123', 'Test job', '1 Main St', '21619', 0);
59INSERT INTO `Customer` (`Customer_ID`, `Name`, `Customer_Descr`, `Address`, `Postcode`, `Inactive`) VALUES (2, 'ABC-456', 'Test job 2', 'Sri Lanka', '40000', 0);
60INSERT INTO `Customer` (`Customer_ID`, `Name`, `Customer_Descr`, `Address`, `Postcode`, `Inactive`) VALUES (3, 'CBD-345', 'Job Desc', 'Colombo Sri Lanka', '40000', 1);
61INSERT INTO `Customer` (`Customer_ID`, `Name`, `Customer_Descr`, `Address`, `Postcode`, `Inactive`) VALUES (4, 'ERD-678', 'This is the Job Desc', 'Kandy Sri Lanka', '60000', 0);
62INSERT INTO `Customer` (`Customer_ID`, `Name`, `Customer_Descr`, `Address`, `Postcode`, `Inactive`) VALUES (5, 'BCD-938', 'This is the Job Desc', 'London, UK', '123456', 1);
63INSERT INTO `Customer` (`Customer_ID`, `Name`, `Customer_Descr`, `Address`, `Postcode`, `Inactive`) VALUES (6, 'ERD-475', 'This is the Job Desc', 'London, UK', '123456', 1);
64# 6 records
65
66#
67# Table structure for table 'Inventory'
68#
69
70DROP TABLE IF EXISTS `Inventory`;
71
72CREATE TABLE `Inventory` (
73  `Barcode` INTEGER NOT NULL AUTO_INCREMENT, 
74  `Descr` VARCHAR(50), 
75  `Item_Price` DECIMAL(19,4) DEFAULT 0, 
76  `Inv_Qty` INTEGER DEFAULT 0, 
77  `Supplier_ID` INTEGER, 
78  INDEX (`Barcode`), 
79  PRIMARY KEY (`Barcode`), 
80  INDEX (`Supplier_ID`)
81) ENGINE=innodb DEFAULT CHARSET=utf8;
82
83SET autocommit=1;
84
85#
86# Dumping data for table 'Inventory'
87#
88
89INSERT INTO `Inventory` (`Barcode`, `Descr`, `Item_Price`, `Inv_Qty`, `Supplier_ID`) VALUES (100001, 'LEAD ROOF', 11, 990, 5);
90INSERT INTO `Inventory` (`Barcode`, `Descr`, `Item_Price`, `Inv_Qty`, `Supplier_ID`) VALUES (100004, 'W/M CPVC W/AIR CHAMBERS', 22, 525, 1);
91INSERT INTO `Inventory` (`Barcode`, `Descr`, `Item_Price`, `Inv_Qty`, `Supplier_ID`) VALUES (100005, 'W/M PEX W/AIR CHAMBERS', 1, 524, 1);
92INSERT INTO `Inventory` (`Barcode`, `Descr`, `Item_Price`, `Inv_Qty`, `Supplier_ID`) VALUES (100006, 'ICE CPVC W/AIR CHAMBER', 2, 690, 3);
93INSERT INTO `Inventory` (`Barcode`, `Descr`, `Item_Price`, `Inv_Qty`, `Supplier_ID`) VALUES (100007, 'ICE COPPER W/AIR CHAMBER', 3, 990, 1);
94INSERT INTO `Inventory` (`Barcode`, `Descr`, `Item_Price`, `Inv_Qty`, `Supplier_ID`) VALUES (100008, 'STUD 2-H FOR WOOD', 4, 746, 1);
95INSERT INTO `Inventory` (`Barcode`, `Descr`, `Item_Price`, `Inv_Qty`, `Supplier_ID`) VALUES (100010, 'STRAINERS 2-3 ALL PURPOSE', 6, 203, 4);
96INSERT INTO `Inventory` (`Barcode`, `Descr`, `Item_Price`, `Inv_Qty`, `Supplier_ID`) VALUES (100011, '2-H MICKEY', 7, 943, 5);
97INSERT INTO `Inventory` (`Barcode`, `Descr`, `Item_Price`, `Inv_Qty`, `Supplier_ID`) VALUES (100012, 'STOUT BRACKET 10-18 SPAN', 8, 303, 2);
98INSERT INTO `Inventory` (`Barcode`, `Descr`, `Item_Price`, `Inv_Qty`, `Supplier_ID`) VALUES (100013, '2-H STRAIGHT', 9, 349, 5);
99INSERT INTO `Inventory` (`Barcode`, `Descr`, `Item_Price`, `Inv_Qty`, `Supplier_ID`) VALUES (100014, '0630-C2814 H/B COPPER', 11, 990, 3);
100INSERT INTO `Inventory` (`Barcode`, `Descr`, `Item_Price`, `Inv_Qty`, `Supplier_ID`) VALUES (100015, '15X60 PROTECTIVE LINER', 4.9, -644, 3);
101INSERT INTO `Inventory` (`Barcode`, `Descr`, `Item_Price`, `Inv_Qty`, `Supplier_ID`) VALUES (100016, 'LH CAST IRON VILLAG 60X30', 264.6, 102, 1);
102# 13 records
103
104#
105# Table structure for table 'Inventory_Out_Items'
106#
107
108DROP TABLE IF EXISTS `Inventory_Out_Items`;
109
110CREATE TABLE `Inventory_Out_Items` (
111  `Inventory_Out_Item_ID` INTEGER AUTO_INCREMENT, 
112  `Invoice_ID` INTEGER NOT NULL DEFAULT 0, 
113  `Barcode` INTEGER NOT NULL DEFAULT 0, 
114  `Qty_Out` INTEGER DEFAULT 0, 
115  UNIQUE (`Inventory_Out_Item_ID`), 
116  INDEX (`Barcode`), 
117  INDEX (`Invoice_ID`), 
118  PRIMARY KEY (`Invoice_ID`, `Barcode`)
119) ENGINE=innodb DEFAULT CHARSET=utf8;
120
121SET autocommit=1;
122
123#
124# Dumping data for table 'Inventory_Out_Items'
125#
126
127INSERT INTO `Inventory_Out_Items` (`Inventory_Out_Item_ID`, `Invoice_ID`, `Barcode`, `Qty_Out`) VALUES (4, 1, 100004, 4);
128INSERT INTO `Inventory_Out_Items` (`Inventory_Out_Item_ID`, `Invoice_ID`, `Barcode`, `Qty_Out`) VALUES (5, 1, 100006, 70);
129INSERT INTO `Inventory_Out_Items` (`Inventory_Out_Item_ID`, `Invoice_ID`, `Barcode`, `Qty_Out`) VALUES (6, 1, 100008, 20);
130INSERT INTO `Inventory_Out_Items` (`Inventory_Out_Item_ID`, `Invoice_ID`, `Barcode`, `Qty_Out`) VALUES (7, 1, 100011, 3);
131INSERT INTO `Inventory_Out_Items` (`Inventory_Out_Item_ID`, `Invoice_ID`, `Barcode`, `Qty_Out`) VALUES (8, 1, 100010, 0);
132INSERT INTO `Inventory_Out_Items` (`Inventory_Out_Item_ID`, `Invoice_ID`, `Barcode`, `Qty_Out`) VALUES (9, 2, 100005, 12);
133INSERT INTO `Inventory_Out_Items` (`Inventory_Out_Item_ID`, `Invoice_ID`, `Barcode`, `Qty_Out`) VALUES (10, 2, 100010, 144);
134INSERT INTO `Inventory_Out_Items` (`Inventory_Out_Item_ID`, `Invoice_ID`, `Barcode`, `Qty_Out`) VALUES (11, 2, 100011, 34);
135INSERT INTO `Inventory_Out_Items` (`Inventory_Out_Item_ID`, `Invoice_ID`, `Barcode`, `Qty_Out`) VALUES (12, 2, 100015, 55);
136INSERT INTO `Inventory_Out_Items` (`Inventory_Out_Item_ID`, `Invoice_ID`, `Barcode`, `Qty_Out`) VALUES (13, 2, 100013, 232);
137INSERT INTO `Inventory_Out_Items` (`Inventory_Out_Item_ID`, `Invoice_ID`, `Barcode`, `Qty_Out`) VALUES (14, 2, 100007, 347);
138INSERT INTO `Inventory_Out_Items` (`Inventory_Out_Item_ID`, `Invoice_ID`, `Barcode`, `Qty_Out`) VALUES (15, 2, 100016, 23);
139INSERT INTO `Inventory_Out_Items` (`Inventory_Out_Item_ID`, `Invoice_ID`, `Barcode`, `Qty_Out`) VALUES (16, 3, 100008, 234);
140INSERT INTO `Inventory_Out_Items` (`Inventory_Out_Item_ID`, `Invoice_ID`, `Barcode`, `Qty_Out`) VALUES (17, 3, 100010, 643);
141INSERT INTO `Inventory_Out_Items` (`Inventory_Out_Item_ID`, `Invoice_ID`, `Barcode`, `Qty_Out`) VALUES (18, 3, 100006, 280);
142INSERT INTO `Inventory_Out_Items` (`Inventory_Out_Item_ID`, `Invoice_ID`, `Barcode`, `Qty_Out`) VALUES (19, 3, 100004, 455);
143INSERT INTO `Inventory_Out_Items` (`Inventory_Out_Item_ID`, `Invoice_ID`, `Barcode`, `Qty_Out`) VALUES (20, 3, 100015, 669);
144INSERT INTO `Inventory_Out_Items` (`Inventory_Out_Item_ID`, `Invoice_ID`, `Barcode`, `Qty_Out`) VALUES (21, 3, 100012, 687);
145INSERT INTO `Inventory_Out_Items` (`Inventory_Out_Item_ID`, `Invoice_ID`, `Barcode`, `Qty_Out`) VALUES (22, 3, 100013, 43);
146INSERT INTO `Inventory_Out_Items` (`Inventory_Out_Item_ID`, `Invoice_ID`, `Barcode`, `Qty_Out`) VALUES (23, 3, 100005, 454);
147INSERT INTO `Inventory_Out_Items` (`Inventory_Out_Item_ID`, `Invoice_ID`, `Barcode`, `Qty_Out`) VALUES (24, 3, 100016, 888);
148INSERT INTO `Inventory_Out_Items` (`Inventory_Out_Item_ID`, `Invoice_ID`, `Barcode`, `Qty_Out`) VALUES (25, 3, 100007, 100);
149INSERT INTO `Inventory_Out_Items` (`Inventory_Out_Item_ID`, `Invoice_ID`, `Barcode`, `Qty_Out`) VALUES (30, 6, 100007, 150);
150INSERT INTO `Inventory_Out_Items` (`Inventory_Out_Item_ID`, `Invoice_ID`, `Barcode`, `Qty_Out`) VALUES (31, 6, 100016, 20);
151# 24 records
152
153#
154# Table structure for table 'Invoice'
155#
156
157DROP TABLE IF EXISTS `Invoice`;
158
159CREATE TABLE `Invoice` (
160  `Invoice_ID` INTEGER AUTO_INCREMENT, 
161  `Invoice_No` VARCHAR(25) NOT NULL, 
162  `Customer_ID` INTEGER DEFAULT 0, 
163  `Out_Date` DATETIME, 
164  `Ship_To` TINYINT DEFAULT 1, 
165  INDEX (`Invoice_No`), 
166  UNIQUE (`Invoice_ID`), 
167  INDEX (`Out_Date`), 
168  INDEX (`Customer_ID`), 
169  PRIMARY KEY (`Invoice_No`)
170) ENGINE=innodb DEFAULT CHARSET=utf8;
171
172SET autocommit=1;
173
174#
175# Dumping data for table 'Invoice'
176#
177
178INSERT INTO `Invoice` (`Invoice_ID`, `Invoice_No`, `Customer_ID`, `Out_Date`, `Ship_To`) VALUES (1, 'Joes Job', 1, '2006-09-28 00:00:00', 1);
179INSERT INTO `Invoice` (`Invoice_ID`, `Invoice_No`, `Customer_ID`, `Out_Date`, `Ship_To`) VALUES (2, '12345', 5, '2010-02-22 00:00:00', 1);
180INSERT INTO `Invoice` (`Invoice_ID`, `Invoice_No`, `Customer_ID`, `Out_Date`, `Ship_To`) VALUES (3, '23445', 4, '2010-02-22 00:00:00', 1);
181INSERT INTO `Invoice` (`Invoice_ID`, `Invoice_No`, `Customer_ID`, `Out_Date`, `Ship_To`) VALUES (6, '23446', 5, '2010-03-05 00:00:00', 1);
182# 4 records
183
184#
185# Table structure for table 'Supplier'
186#
187
188DROP TABLE IF EXISTS `Supplier`;
189
190CREATE TABLE `Supplier` (
191  `Supplier_ID` INTEGER AUTO_INCREMENT, 
192  `Supplier_name` VARCHAR(50) NOT NULL, 
193  `Address` VARCHAR(50), 
194  `City` VARCHAR(50), 
195  `Zip` VARCHAR(50), 
196  `Phone` VARCHAR(50), 
197  `Inactive` TINYINT(1) DEFAULT 0, 
198  UNIQUE (`Supplier_ID`), 
199  PRIMARY KEY (`Supplier_name`)
200) ENGINE=innodb DEFAULT CHARSET=utf8;
201
202SET autocommit=1;
203
204#
205# Dumping data for table 'Supplier'
206#
207
208INSERT INTO `Supplier` (`Supplier_ID`, `Supplier_name`, `Address`, `City`, `Zip`, `Phone`, `Inactive`) VALUES (1, 'Joe Dean', '148 Kirwans Landing Lane', 'Chester', '21619', '+9477123455', 0);
209INSERT INTO `Supplier` (`Supplier_ID`, `Supplier_name`, `Address`, `City`, `Zip`, `Phone`, `Inactive`) VALUES (2, 'David', '546 th Lane Kolpity', 'Kolpity', '60000', '+9477123456', 0);
210INSERT INTO `Supplier` (`Supplier_ID`, `Supplier_name`, `Address`, `City`, `Zip`, `Phone`, `Inactive`) VALUES (3, 'Anne', '1 Victoria Street', 'London', NULL, '+441234532', 0);
211INSERT INTO `Supplier` (`Supplier_ID`, `Supplier_name`, `Address`, `City`, `Zip`, `Phone`, `Inactive`) VALUES (4, 'John', 'N2 East Finchley', 'London', '60000', '+4434532234', 0);
212INSERT INTO `Supplier` (`Supplier_ID`, `Supplier_name`, `Address`, `City`, `Zip`, `Phone`, `Inactive`) VALUES (5, 'Jonna', 'L indicates Liverpool', 'Liverpool', '345399', '+442345898', 0);
213# 5 records