f9dG5A94

1-----------------------------------------------
2-- MySQL
3-- @ Author Dat Nguyen and Reid Stagemeyer
4-- Host: localhost    Database: datduyn
5-- ------------------------------------------------------
6-- Server version	5.1.57
7
8
9
10
11
12
13-- Uncomment these piece to test them locally
14-- use rstagemeyer;
15-- use datduyn;
16-- use ims;
17
18--  drop the old version before creating new DB
19DROP TABLE IF EXISTS `InvoiceProducts`;
20DROP TABLE IF EXISTS `Invoices`;
21DROP TABLE IF EXISTS `Refreshments`;
22DROP TABLE IF EXISTS `ParkingPasses`;
23DROP TABLE IF EXISTS `SeasonPasses`;
24DROP TABLE IF EXISTS `MovieTickets`;
25DROP TABLE IF EXISTS `Products`;
26DROP TABLE IF EXISTS `Customers`;
27DROP TABLE IF EXISTS `Emails`;
28DROP TABLE IF EXISTS `Persons`;
29DROP TABLE IF EXISTS `Addresses`;
30DROP TABLE IF EXISTS `StateCountries`;
31DROP TABLE IF EXISTS `Countries`;
32
33--
34-- Tables structure for Products
35--
36
37CREATE TABLE Products(
38id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
39productCode VARCHAR(255) UNIQUE NOT NULL,
40productType VARCHAR(2) NOT NULL
41);
42-- Dumping data For table `Product`
43INSERT INTO Products(productCode, productType) VALUES
44('b29e','S'),
45('ff23','R'),
46('fp12','M'),
47('90fa','P'),
48('1239','M'),
49('782g','M'),
50('3289','P'),
51('32f4','R'),
52('3y92','S'),
53('90fb','P'),
54('xer4','S'),
55('yp23','R');
56
57-- create product related object table.
58-- Refreshment DB table
59CREATE TABLE Refreshments(
60id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
61name varchar(255) NOT NULL,
62cost float NOT NULL default '0.0',
63haveTicket boolean NOT NULL default 0,# TODO:
64productID INT NOT NULL,
65FOREIGN KEY(productID) REFERENCES Products(id)
66);
67-- Dumping data For table `Refreshment`
68INSERT INTO Refreshments(name, cost, haveTicket, productID) VALUES
69('Labatt Beer-20oz',4.99,0,2),
70('Caramel Popcorn',5.50,0,8),
71('Double Cheeseburger',9.00,0,12);
72
73-- Parkingpass DB table.
74CREATE TABLE ParkingPasses(
75id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
76fee float NOT NULL DEFAULT '0.0',
77productID INT NOT NULL,
78FOREIGN KEY(productID) REFERENCES Products(id)
79);
80-- Dumping data For table `ParkingPass`
81INSERT INTO ParkingPasses(fee, productID) VALUES
82(25.00,4),
83(55.00,7),
84(20.00,10);
85
86#Seasonpass DB table.
87CREATE TABLE SeasonPasses(
88id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
89name varchar(255) NOT NULL DEFAULT 'na',
90startDate DATE NOT NULL,
91endDate DATE NOT NULL,
92cost float NOT NULL DEFAULT '0.0',
93productID INT NOT NULL,
94FOREIGN KEY(productID) REFERENCES Products(id)
95);
96-- Dumping data For table `ParkingPass`
97INSERT INTO SeasonPasses(name, startDate, endDate, cost, productID) VALUES
98('Winter Special','2016-12-13','2017-01-07',120.00,1),
99('BlackFriday Deal','2016-11-24','2016-11-29',58.00,9),
100('FiveWeeks-Unlimited','2016-10-07','2016-11-11',185.00,11);
101
102-- create Countries, States databases table
103CREATE TABLE Countries(
104id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
105name VARCHAR(255) NOT NULL DEFAULT 'na'
106);
107
108-- dumping data for Countries
109INSERT INTO Countries(name)
110VALUES ('USA'),('Canada'),('Mexico');
111
112
113-- create States database table
114CREATE TABLE StateCountries(
115id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
116name VARCHAR(5) NOT NULL DEFAULT 'na',
117fullName VARCHAR(255) NOT NULL DEFAULT 'na',
118countryID INT NOT NULL,
119FOREIGN KEY(countryID) REFERENCES Countries(id)
120);
121-- Dumping data for States
122INSERT INTO StateCountries(name,fullName,countryID) VALUES
123('AL', 'Alabama',1),('AK', 'Alaska',1),('AZ', 'Arizona',1),('AR', 'Arkansas',1),
124('CA', 'California',1),('CO', 'Colorado',1),('CT', 'Connecticut',1),('DE', 'Delaware',1),
125('DC', 'District of Columbia',1),('FL', 'Florida',1),('GA', 'Georgia',1),('HI', 'Hawaii',1),
126('ID', 'Idaho',1),('IL', 'Illinois',1),('IA', 'Iowa',1),('KS', 'Kansas',1),('KY', 'Kentucky',1),
127('LA', 'Louisiana',1),('ME', 'Maine',1),('MD', 'Maryland',1),('MA', 'Massachusetts',1),
128('MI', 'Michigan',1),('MN', 'Minnesota',1),('MS', 'Mississippi',1),('MO', 'Missouri',1),
129('MT', 'Montana',1),('NE', 'Nebraska',1),('NV', 'Nevada',1),('NH', 'New Hampshire',1),
130('NJ', 'New Jersey',1),('NM', 'New Mexico',1),('NY', 'New York',1),('NC', 'North Carolina',1),
131('ND', 'North Dakota',1),('OH', 'Ohio',1),('OK', 'Oklahoma',1),('OR', 'Oregon',1),
132('PA', 'Pennsylvania',1),('PR', 'Puerto Rico',1),('RI', 'Rhode Island',1),('SC', 'South Carolina',1),
133('SD', 'South Dakota',1),('TN', 'Tennessee',1),('TX', 'Texas',1),('UT', 'Utah',1),
134('VT', 'Vermont',1),('VA', 'Virginia',1),('WA', 'Washington',1),('WV', 'West Virginia',1),
135('WI', 'Wisconsin',1),('WY', 'Wyoming',1),('ON','Ontra',2),('FD','Farade',3),('BCS','BaoCCongSang',3);
136
137
138-- create address DB table
139CREATE TABLE Addresses(
140id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
141street varchar(255) NOT NULL DEFAULT 'na',
142city varchar(255) NOT NULL DEFAULT 'na',
143stateCountryID INT NOT NULL,
144zipcode varchar(255) NOT NULL DEFAULT 'na',
145FOREIGN KEY(stateCountryID) REFERENCES StateCountries(id)
146);
147-- Dumping data For table `Address`
148INSERT INTO Addresses(street,city,stateCountryID,zipcode) VALUES
149('1060 West Addison Street','Chicago',14,'600613'),
150('123 N 1st Street','Omaha',27,'68116'),
151('8753 West 3rd Ave.','Dallas',44,'75001'),
152('123 Friendly Street','Ottawa',52,'KA 0G9'),
153('1 Wall Street	','New York',32,'10005-0012'),
154('321 Bronx Street','New York',32,'10004'),
155('301 Front St W','Toronto',52,'M5V 2T6'),
156('1 Blue Jays Way','Toronto',52,'M5V 1J1'),
157('Campos El290','Mexico City', 53,' '),
158('Avery Hall','Lincoln',27,'68503'),
159('126-01 Roosevelt Ave','Flushing',32,'11368'),
160('1 MetLife Stadium Dr', 'East Rutherford', 30,'07073'),
161('1 E 161st St', 'Bronx', 32,'10451'),
162('700 E Grand Ave', 'Chicago', 14, '60611'),
163('333 W 35th St', 'Chicago', 14,'60616'),
164('800 West 7th Street', 'Albuquerque', 31, '87105'),
165('123 Cabo San Lucas', 'Los Cabos', 54,'11111'),
166('259 Concorde Suites', 'Lincoln', 27,'68588-0115'),
167('1223 Oldfather Hall', 'Lincoln', 27,'68503'),
168('123 Venture Way', 'Culver City', 5,'90230' ),
169('9800 Savage Rd', 'Fort Meade', 20,'20755'),
170('456 West 7th St.', 'Omaha', 27,'68500'),
171('3555 South 140th Plaza', 'Omaha', 27,'68144'),
172('755 Willard Drive','Ashwaubenon',50,'54304'),
173('6301 University Ave','Cedar Falls',15,'50613');
174
175
176-- Movie Ticket DB table
177CREATE TABLE MovieTickets(
178id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
179dateTime datetime NOT NULL,
180movieName VARCHAR(255) NOT NULL,
181addressID INT NOT NULL,
182screenNumber VARCHAR(255) NOT NULL,
183pricePerUnit FLOAT NOT NULL DEFAULT '0.0',
184productID INT NOT NULL,
185FOREIGN KEY(productID) REFERENCES Products(id),
186FOREIGN KEY(addressID) REFERENCES Addresses(id)
187);
188-- Dumping data For table `MovieTicket`
189INSERT INTO MovieTickets(dateTime, movieName, addressID, screenNumber, pricePerUnit, productID) VALUES
190('2016-10-21 13:10','A Monster Calls',23,'2A',21.50,3),
191('2016-09-13 15:30','The Wild Life',24,'12F',11.00,5),
192('2016-11-10 10:00','Shut In',25,'32D',25.00,6);
193
194--
195-- Tables structure for Person
196--
197
198-- create PersonDB
199CREATE TABLE Persons(
200id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
201personCode varchar(255) UNIQUE NOT NULL,
202lastName varchar(255) NOT NULL,
203firstName varchar(255) NOT NULL,
204addressID INT NOT NULL,
205foreign key(addressID) references Addresses(id)
206);
207
208INSERT INTO Persons(personCode,lastName,firstName,addressID) VALUES
209('944c','Castro','Starlin',1),
210('306a','Sampson','Brock',2),
211('55bb','O''Brien','Miles',3),
212('2342','O''Brien','Miles',4),
213('aef1','Gekko','Gordon',5),
214('321f','Fox','Bud',6),
215('ma12','Sveum','Dale',7),
216('321nd','Hartnell','William',8),
217('nf32a','Troughton','Patrick',9),
218('321na','Pertwee','Jon',10),
219('231','Baker','Tom',11),
220('6doc','Hurndall','Richard',12),
221('321dr','Baker','C.',13),
222('1svndr','McCoy','Sylvester',14),
223('1231st','McGann','Paul',15),
224('nwdoc1','Eccleston','Chris',16),
225('2ndbestd','Tennant','David',17),
226('wrddoc','Smith','Matt',18),
227('bbchar','Ehrmantraut','Kaylee',19),
228('doc05','Davison','Peter',20);
229
230-- create join table named Emails
231create table Emails(
232id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
233personID INT NOT NULL,
234email 	VARCHAR(255) NOT NULL,
235FOREIGN KEY(personID) REFERENCES Persons(id)
236);
237
238INSERT INTO Emails(personID,email) VALUES
239(1,'scastro@cubs.com'),
240(1, 'starlin_castro13@gmail.com'),
241(2,'brock_f_sampson@gmail.com'),
242(2, 'bsampson@venture.com'),
243(3,'obrien@ds9.com'),
244(3, 'obrien@enterprise.gov'),
245(6,'bfox@gmail.com'),
246(6, 'csheen@crazy.net'),
247(7,'sveum@cubs.com'),
248(8, 'whartnell@doctors.com'),
249(8,'dr@who.com'),
250(9, 'ptroug@cse.unl.edu'),
251(9,'ptrou32@unl.edu'),
252(10, 'jpet@whofan.com'),
253(11,'famousdoc@who.com'),
254(11, 'tbaker@cse.unl.edu'),
255(11,'mostfamous@whoian.com'),
256(11, 'thedoctor@bbc.com'),
257(12,'rhurndall@cse.unl.edu'),
258(12, 'richard@unl.edu'),
259(13,'dr@baker.com'),
260(14, 'slyguy@hotmail..com'),
261(14, 'mccoy@whofan.com'),
262(15, 'pmcgann@mlb.com'),
263(15, 'foo@bar.com'),
264(15, 'pmc@unl.edu'),
265(16, 'newguy@whoian.com'),
266(17, 'actor@shakespeare.com'),
267(17, 'tdavid@unl.edu'),
268(18, 'msmith@who.com'),
269(18, 'thedoc@cse.unl.edu');
270--
271-- Tables structure for Customer
272--
273CREATE TABLE Customers(
274id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
275customerCode VARCHAR(255) UNIQUE NOT NULL,
276customerName VARCHAR(255) NOT NULL,
277customerType VARCHAR(255) NOT NULL DEFAULT 'na',
278primaryContactID INT NOT NULL,
279addressID INT NOT NULL,
280FOREIGN KEY(primaryContactID) REFERENCES Persons(id),
281FOREIGN KEY(AddressID) REFERENCES Addresses(id)
282);
283
284
285INSERT INTO Customers(customerCode,customerName,customerType,primaryContactID,addressID) VALUES
286('C001','Clark Consultants','G',11, 19),
287('C002','CAS International Fellows','S',1, 20),
288('C003','Valueless Club','S',2, 21),
289('C004','Stony Brook','G', 6, 22),
290('C005','Hewlett Industries','G', 19, 1),
291('C006','Gregory Smith','S', 13, 23);
292
293--
294-- Table structure for Invoices
295--
296-- create table to store Invoice attribute
297CREATE TABLE Invoices(
298id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
299invoiceCode VARCHAR(255) UNIQUE NOT NULL,
300customerID INT NOT NULL DEFAULT 0,
301salesPersonID INT NOT NULL DEFAULT 0,
302invoiceDate DATE NOT NULL,
303FOREIGN KEY(CustomerID) REFERENCES Customers(id),
304FOREIGN KEY(salesPersonID) REFERENCES Persons(id)
305);
306
307INSERT INTO Invoices(invoiceCode,customerID,salesPersonID,invoiceDate) VALUES
308('INV001',1,16,'2016-09-03'),
309('INV002',2,17,'2016-11-10'),
310('INV003',5,18,'2016-11-26'),
311('INV004',3,12,'2016-10-16');
312
313
314
315-- create table to store product associate to an invoice.
316-- note field were created so user can add any additional note
317-- for ex. ticket asociated with the Parkingpass
318-- or if he want to add special discount for any specfic product
319--
320CREATE TABLE InvoiceProducts(
321id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
322invoiceID INT NOT NULL ,
323productID INT NOT NULL,
324unit INT NOT NULL,
325note VARCHAR(255),
326FOREIGN KEY(invoiceID) REFERENCES Invoices(id),
327FOREIGN KEY(productID) REFERENCES Products(id),
328CONSTRAINT uniqueIP UNIQUE(invoiceID, productID)
329);
330
331-- Added CONSTRAIN unique key word to prevent duplicate product in the
332-- same Invocie
333INSERT INTO InvoiceProducts(invoiceID, productID, unit) VALUES
334(1,3,2),
335(1,7,1),
336(1,2,4),
337(2,1,23),
338(2,5,31),
339(2,4,30),
340(2,8,17),
341(3,6,7),
342(3,9,6),
343(3,7,2),
344(3,12,4),
345(4,10,3),
346(4,8,3);
347
348--
349-- General select Statesments to see if tables are created and stored properly
350--
351-- select * from Addresses;
352-- select * from Persons;
353-- select * from Customers;
354-- select * from Emails;
355-- select * from Products;
356-- select * from MovieTickets;
357-- select * from SeasonPasses;
358-- select * from ParkingPasses;
359-- select * from Refreshments;
360-- select * from Invoices;
361-- select * from InvoiceProducts;