Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -----------------------------------------------
- -- MySQL
- -- @ Author Dat Nguyen and Reid Stagemeyer
- -- Host: localhost Database: datduyn
- -- ------------------------------------------------------
- -- Server version 5.1.57
- -- Uncomment these piece to test them locally
- -- use rstagemeyer;
- -- use datduyn;
- -- use ims;
- -- drop the old version before creating new DB
- DROP TABLE IF EXISTS `InvoiceProducts`;
- DROP TABLE IF EXISTS `Invoices`;
- DROP TABLE IF EXISTS `Refreshments`;
- DROP TABLE IF EXISTS `ParkingPasses`;
- DROP TABLE IF EXISTS `SeasonPasses`;
- DROP TABLE IF EXISTS `MovieTickets`;
- DROP TABLE IF EXISTS `Products`;
- DROP TABLE IF EXISTS `Customers`;
- DROP TABLE IF EXISTS `Emails`;
- DROP TABLE IF EXISTS `Persons`;
- DROP TABLE IF EXISTS `Addresses`;
- DROP TABLE IF EXISTS `StateCountries`;
- DROP TABLE IF EXISTS `Countries`;
- --
- -- Tables structure for Products
- --
- CREATE TABLE Products(
- id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
- productCode VARCHAR(255) UNIQUE NOT NULL,
- productType VARCHAR(2) NOT NULL
- );
- -- Dumping data For table `Product`
- INSERT INTO Products(productCode, productType) VALUES
- ('b29e','S'),
- ('ff23','R'),
- ('fp12','M'),
- ('90fa','P'),
- ('1239','M'),
- ('782g','M'),
- ('3289','P'),
- ('32f4','R'),
- ('3y92','S'),
- ('90fb','P'),
- ('xer4','S'),
- ('yp23','R');
- -- create product related object table.
- -- Refreshment DB table
- CREATE TABLE Refreshments(
- id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
- name varchar(255) NOT NULL,
- cost float NOT NULL default '0.0',
- haveTicket boolean NOT NULL default 0,# TODO:
- productID INT NOT NULL,
- FOREIGN KEY(productID) REFERENCES Products(id)
- );
- -- Dumping data For table `Refreshment`
- INSERT INTO Refreshments(name, cost, haveTicket, productID) VALUES
- ('Labatt Beer-20oz',4.99,0,2),
- ('Caramel Popcorn',5.50,0,8),
- ('Double Cheeseburger',9.00,0,12);
- -- Parkingpass DB table.
- CREATE TABLE ParkingPasses(
- id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
- fee float NOT NULL DEFAULT '0.0',
- productID INT NOT NULL,
- FOREIGN KEY(productID) REFERENCES Products(id)
- );
- -- Dumping data For table `ParkingPass`
- INSERT INTO ParkingPasses(fee, productID) VALUES
- (25.00,4),
- (55.00,7),
- (20.00,10);
- #Seasonpass DB table.
- CREATE TABLE SeasonPasses(
- id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
- name varchar(255) NOT NULL DEFAULT 'na',
- startDate DATE NOT NULL,
- endDate DATE NOT NULL,
- cost float NOT NULL DEFAULT '0.0',
- productID INT NOT NULL,
- FOREIGN KEY(productID) REFERENCES Products(id)
- );
- -- Dumping data For table `ParkingPass`
- INSERT INTO SeasonPasses(name, startDate, endDate, cost, productID) VALUES
- ('Winter Special','2016-12-13','2017-01-07',120.00,1),
- ('BlackFriday Deal','2016-11-24','2016-11-29',58.00,9),
- ('FiveWeeks-Unlimited','2016-10-07','2016-11-11',185.00,11);
- -- create Countries, States databases table
- CREATE TABLE Countries(
- id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
- name VARCHAR(255) NOT NULL DEFAULT 'na'
- );
- -- dumping data for Countries
- INSERT INTO Countries(name)
- VALUES ('USA'),('Canada'),('Mexico');
- -- create States database table
- CREATE TABLE StateCountries(
- id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
- name VARCHAR(5) NOT NULL DEFAULT 'na',
- fullName VARCHAR(255) NOT NULL DEFAULT 'na',
- countryID INT NOT NULL,
- FOREIGN KEY(countryID) REFERENCES Countries(id)
- );
- -- Dumping data for States
- INSERT INTO StateCountries(name,fullName,countryID) VALUES
- ('AL', 'Alabama',1),('AK', 'Alaska',1),('AZ', 'Arizona',1),('AR', 'Arkansas',1),
- ('CA', 'California',1),('CO', 'Colorado',1),('CT', 'Connecticut',1),('DE', 'Delaware',1),
- ('DC', 'District of Columbia',1),('FL', 'Florida',1),('GA', 'Georgia',1),('HI', 'Hawaii',1),
- ('ID', 'Idaho',1),('IL', 'Illinois',1),('IA', 'Iowa',1),('KS', 'Kansas',1),('KY', 'Kentucky',1),
- ('LA', 'Louisiana',1),('ME', 'Maine',1),('MD', 'Maryland',1),('MA', 'Massachusetts',1),
- ('MI', 'Michigan',1),('MN', 'Minnesota',1),('MS', 'Mississippi',1),('MO', 'Missouri',1),
- ('MT', 'Montana',1),('NE', 'Nebraska',1),('NV', 'Nevada',1),('NH', 'New Hampshire',1),
- ('NJ', 'New Jersey',1),('NM', 'New Mexico',1),('NY', 'New York',1),('NC', 'North Carolina',1),
- ('ND', 'North Dakota',1),('OH', 'Ohio',1),('OK', 'Oklahoma',1),('OR', 'Oregon',1),
- ('PA', 'Pennsylvania',1),('PR', 'Puerto Rico',1),('RI', 'Rhode Island',1),('SC', 'South Carolina',1),
- ('SD', 'South Dakota',1),('TN', 'Tennessee',1),('TX', 'Texas',1),('UT', 'Utah',1),
- ('VT', 'Vermont',1),('VA', 'Virginia',1),('WA', 'Washington',1),('WV', 'West Virginia',1),
- ('WI', 'Wisconsin',1),('WY', 'Wyoming',1),('ON','Ontra',2),('FD','Farade',3),('BCS','BaoCCongSang',3);
- -- create address DB table
- CREATE TABLE Addresses(
- id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
- street varchar(255) NOT NULL DEFAULT 'na',
- city varchar(255) NOT NULL DEFAULT 'na',
- stateCountryID INT NOT NULL,
- zipcode varchar(255) NOT NULL DEFAULT 'na',
- FOREIGN KEY(stateCountryID) REFERENCES StateCountries(id)
- );
- -- Dumping data For table `Address`
- INSERT INTO Addresses(street,city,stateCountryID,zipcode) VALUES
- ('1060 West Addison Street','Chicago',14,'600613'),
- ('123 N 1st Street','Omaha',27,'68116'),
- ('8753 West 3rd Ave.','Dallas',44,'75001'),
- ('123 Friendly Street','Ottawa',52,'KA 0G9'),
- ('1 Wall Street ','New York',32,'10005-0012'),
- ('321 Bronx Street','New York',32,'10004'),
- ('301 Front St W','Toronto',52,'M5V 2T6'),
- ('1 Blue Jays Way','Toronto',52,'M5V 1J1'),
- ('Campos El290','Mexico City', 53,' '),
- ('Avery Hall','Lincoln',27,'68503'),
- ('126-01 Roosevelt Ave','Flushing',32,'11368'),
- ('1 MetLife Stadium Dr', 'East Rutherford', 30,'07073'),
- ('1 E 161st St', 'Bronx', 32,'10451'),
- ('700 E Grand Ave', 'Chicago', 14, '60611'),
- ('333 W 35th St', 'Chicago', 14,'60616'),
- ('800 West 7th Street', 'Albuquerque', 31, '87105'),
- ('123 Cabo San Lucas', 'Los Cabos', 54,'11111'),
- ('259 Concorde Suites', 'Lincoln', 27,'68588-0115'),
- ('1223 Oldfather Hall', 'Lincoln', 27,'68503'),
- ('123 Venture Way', 'Culver City', 5,'90230' ),
- ('9800 Savage Rd', 'Fort Meade', 20,'20755'),
- ('456 West 7th St.', 'Omaha', 27,'68500'),
- ('3555 South 140th Plaza', 'Omaha', 27,'68144'),
- ('755 Willard Drive','Ashwaubenon',50,'54304'),
- ('6301 University Ave','Cedar Falls',15,'50613');
- -- Movie Ticket DB table
- CREATE TABLE MovieTickets(
- id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
- dateTime datetime NOT NULL,
- movieName VARCHAR(255) NOT NULL,
- addressID INT NOT NULL,
- screenNumber VARCHAR(255) NOT NULL,
- pricePerUnit FLOAT NOT NULL DEFAULT '0.0',
- productID INT NOT NULL,
- FOREIGN KEY(productID) REFERENCES Products(id),
- FOREIGN KEY(addressID) REFERENCES Addresses(id)
- );
- -- Dumping data For table `MovieTicket`
- INSERT INTO MovieTickets(dateTime, movieName, addressID, screenNumber, pricePerUnit, productID) VALUES
- ('2016-10-21 13:10','A Monster Calls',23,'2A',21.50,3),
- ('2016-09-13 15:30','The Wild Life',24,'12F',11.00,5),
- ('2016-11-10 10:00','Shut In',25,'32D',25.00,6);
- --
- -- Tables structure for Person
- --
- -- create PersonDB
- CREATE TABLE Persons(
- id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
- personCode varchar(255) UNIQUE NOT NULL,
- lastName varchar(255) NOT NULL,
- firstName varchar(255) NOT NULL,
- addressID INT NOT NULL,
- foreign key(addressID) references Addresses(id)
- );
- INSERT INTO Persons(personCode,lastName,firstName,addressID) VALUES
- ('944c','Castro','Starlin',1),
- ('306a','Sampson','Brock',2),
- ('55bb','O''Brien','Miles',3),
- ('2342','O''Brien','Miles',4),
- ('aef1','Gekko','Gordon',5),
- ('321f','Fox','Bud',6),
- ('ma12','Sveum','Dale',7),
- ('321nd','Hartnell','William',8),
- ('nf32a','Troughton','Patrick',9),
- ('321na','Pertwee','Jon',10),
- ('231','Baker','Tom',11),
- ('6doc','Hurndall','Richard',12),
- ('321dr','Baker','C.',13),
- ('1svndr','McCoy','Sylvester',14),
- ('1231st','McGann','Paul',15),
- ('nwdoc1','Eccleston','Chris',16),
- ('2ndbestd','Tennant','David',17),
- ('wrddoc','Smith','Matt',18),
- ('bbchar','Ehrmantraut','Kaylee',19),
- ('doc05','Davison','Peter',20);
- -- create join table named Emails
- create table Emails(
- id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
- personID INT NOT NULL,
- email VARCHAR(255) NOT NULL,
- FOREIGN KEY(personID) REFERENCES Persons(id)
- );
- INSERT INTO Emails(personID,email) VALUES
- (1,'scastro@cubs.com'),
- (1, 'starlin_castro13@gmail.com'),
- (2,'brock_f_sampson@gmail.com'),
- (2, 'bsampson@venture.com'),
- (3,'obrien@ds9.com'),
- (3, 'obrien@enterprise.gov'),
- (6,'bfox@gmail.com'),
- (6, 'csheen@crazy.net'),
- (7,'sveum@cubs.com'),
- (8, 'whartnell@doctors.com'),
- (8,'dr@who.com'),
- (9, 'ptroug@cse.unl.edu'),
- (9,'ptrou32@unl.edu'),
- (10, 'jpet@whofan.com'),
- (11,'famousdoc@who.com'),
- (11, 'tbaker@cse.unl.edu'),
- (11,'mostfamous@whoian.com'),
- (11, 'thedoctor@bbc.com'),
- (12,'rhurndall@cse.unl.edu'),
- (12, 'richard@unl.edu'),
- (13,'dr@baker.com'),
- (14, 'slyguy@hotmail..com'),
- (14, 'mccoy@whofan.com'),
- (15, 'pmcgann@mlb.com'),
- (15, 'foo@bar.com'),
- (15, 'pmc@unl.edu'),
- (16, 'newguy@whoian.com'),
- (17, 'actor@shakespeare.com'),
- (17, 'tdavid@unl.edu'),
- (18, 'msmith@who.com'),
- (18, 'thedoc@cse.unl.edu');
- --
- -- Tables structure for Customer
- --
- CREATE TABLE Customers(
- id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
- customerCode VARCHAR(255) UNIQUE NOT NULL,
- customerName VARCHAR(255) NOT NULL,
- customerType VARCHAR(255) NOT NULL DEFAULT 'na',
- primaryContactID INT NOT NULL,
- addressID INT NOT NULL,
- FOREIGN KEY(primaryContactID) REFERENCES Persons(id),
- FOREIGN KEY(AddressID) REFERENCES Addresses(id)
- );
- INSERT INTO Customers(customerCode,customerName,customerType,primaryContactID,addressID) VALUES
- ('C001','Clark Consultants','G',11, 19),
- ('C002','CAS International Fellows','S',1, 20),
- ('C003','Valueless Club','S',2, 21),
- ('C004','Stony Brook','G', 6, 22),
- ('C005','Hewlett Industries','G', 19, 1),
- ('C006','Gregory Smith','S', 13, 23);
- --
- -- Table structure for Invoices
- --
- -- create table to store Invoice attribute
- CREATE TABLE Invoices(
- id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
- invoiceCode VARCHAR(255) UNIQUE NOT NULL,
- customerID INT NOT NULL DEFAULT 0,
- salesPersonID INT NOT NULL DEFAULT 0,
- invoiceDate DATE NOT NULL,
- FOREIGN KEY(CustomerID) REFERENCES Customers(id),
- FOREIGN KEY(salesPersonID) REFERENCES Persons(id)
- );
- INSERT INTO Invoices(invoiceCode,customerID,salesPersonID,invoiceDate) VALUES
- ('INV001',1,16,'2016-09-03'),
- ('INV002',2,17,'2016-11-10'),
- ('INV003',5,18,'2016-11-26'),
- ('INV004',3,12,'2016-10-16');
- -- create table to store product associate to an invoice.
- -- note field were created so user can add any additional note
- -- for ex. ticket asociated with the Parkingpass
- -- or if he want to add special discount for any specfic product
- --
- CREATE TABLE InvoiceProducts(
- id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
- invoiceID INT NOT NULL ,
- productID INT NOT NULL,
- unit INT NOT NULL,
- note VARCHAR(255),
- FOREIGN KEY(invoiceID) REFERENCES Invoices(id),
- FOREIGN KEY(productID) REFERENCES Products(id),
- CONSTRAINT uniqueIP UNIQUE(invoiceID, productID)
- );
- -- Added CONSTRAIN unique key word to prevent duplicate product in the
- -- same Invocie
- INSERT INTO InvoiceProducts(invoiceID, productID, unit) VALUES
- (1,3,2),
- (1,7,1),
- (1,2,4),
- (2,1,23),
- (2,5,31),
- (2,4,30),
- (2,8,17),
- (3,6,7),
- (3,9,6),
- (3,7,2),
- (3,12,4),
- (4,10,3),
- (4,8,3);
- --
- -- General select Statesments to see if tables are created and stored properly
- --
- -- select * from Addresses;
- -- select * from Persons;
- -- select * from Customers;
- -- select * from Emails;
- -- select * from Products;
- -- select * from MovieTickets;
- -- select * from SeasonPasses;
- -- select * from ParkingPasses;
- -- select * from Refreshments;
- -- select * from Invoices;
- -- select * from InvoiceProducts;
Add Comment
Please, Sign In to add comment