Database

m.s11

sql_file_.docx

Home >Computer Science homework help >Database

DROP TABLE IF EXISTS Employees;

DROP TABLE IF EXISTS Customers;

DROP TABLE IF EXISTS Interests;

DROP TABLE IF EXISTS Sales;

DROP TABLE IF EXISTS Rentals;

DROP TABLE IF EXISTS Purchases;

DROP TABLE IF EXISTS Copies;

DROP TABLE IF EXISTS Titles;

DROP TABLE IF EXISTS CDs;

DROP TABLE IF EXISTS DVDs;

DROP TABLE IF EXISTS Video_games;

DROP TABLE IF EXISTS Rental_copies;

CREATE TABLE Employees

(employee_id VARCHAR(12),

NAME VARCHAR(30),

address VARCHAR(30),

phone VARCHAR(15),

email VARCHAR(20),

rank INT,

salary VARCHAR(10),

PRIMARY KEY (employee_id))

ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO Employees VALUES

('1234a','Salem Ahmed',' 2808 E 11th St. Tulsa, OK 74104','918-863-9665','[email protected]',4,'25,000');

INSERT INTO Employees VALUES

('5678b', 'Salma Mohammed','3201 E. 5th Pl. Tulsa, OK 74104','918-852-5813','[email protected]',3,'34,000');

INSERT INTO Employees VALUES

('9101c', 'Sara Anwar','2818 E. 8th St. Tulsa, OK 74104','918-859-9931','[email protected]',2,'48,000');

CREATE TABLE Customers

(customer_id VARCHAR(15),

first_name VARCHAR(15),

last_name VARCHAR(15),

address VARCHAR(30),

zip_code VARCHAR(20),

phone VARCHAR(10),

email VARCHAR(20),

CE_emp_id VARCHAR(12),

CONSTRAINT Customers_PK PRIMARY KEY (customer_id),

CONSTRAINT Customers_emp_id_FK FOREIGN KEY (CE_emp_id) REFERENCES Employees(employee_id))

ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO Customers VALUES

('1234a','Salem Ahmed',' 2808 E 11th St. Tulsa, OK 74104','918-863-9665','[email protected]','y777');

INSERT INTO Customers VALUES

('5678b', 'Salma Mohammed','3201 E. 5th Pl. Tulsa, OK 74104','918-852-5813','[email protected]','u999');

INSERT INTO Customers VALUES

('9101c', 'Sara Anwar','2818 E. 8th St. Tulsa, OK 74104','918-859-9931','[email protected]','m666');

CREATE TABLE Interests

(customer_id VARCHAR(15),

interests VARCHAR(20),

CONSTRAINT Interests_PK PRIMARY KEY (customer_id, interests),

CONSTRAINT Interests_cust_id_FK FOREIGN KEY (customer_id) REFERENCES Customers(customer_id))

ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO Interests VALUES

('1234a','Geology');

INSERT INTO Interests VALUES

('5678b','Sports');

INSERT INTO Interests VALUES

('9101c','football');

CREATE TABLE Sales

(S_id VARCHAR(2),

S_date DATE,

S_amount FLOAT(5,2),

ES_emp_id VARCHAR(12),

CS_cust_id VARCHAR(15),

CONSTRAINT Sales_PK PRIMARY KEY(S_id),

CONSTRAINT Sales_FK1 FOREIGN KEY (ES_emp_id) REFERENCES Employees(employee_id),

CONSTRAINT Sales_FK2 FOREIGN KEY (CS_cust_id) REFERENCES Customers(customer_id))

ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO Sales VALUES

('S1','2016-09-01', 20.25, 'mm345','1234a');

INSERT INTO Sales VALUES

('S2','2016-07-02', 10.00, 'mm345','5678b');

INSERT INTO Sales VALUES

('S3','2016-07-01', 19.00, 'mm345','9101c');

CREATE TABLE Rentals

(R_id VARCHAR(2),

R_date DATE,

R_paid TINYINT(1),

R_due DATE,

R_amount FLOAT(5,2),

RE_emp_id VARCHAR(12),

RC_cust_id VARCHAR(15),

CONSTRAINT Rentals_PK PRIMARY KEY(R_id),

CONSTRAINT Rentals_FK1 FOREIGN KEY (RE_emp_id) REFERENCES Employees(employee_id),

CONSTRAINT Rentals_FK2 FOREIGN KEY (RC_cust_id) REFERENCES Customers(customer_id))

ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO Rentals VALUES

('R1','2016-01-9', 1, '2016-01-18', 22.20, 'mm345','1234a');

INSERT INTO Rentals VALUES

('R2','2016-01-11', 1, '2016-01-20', 19.00, 'mm345','5678b');

INSERT INTO Rentals VALUES

('R3','2016-01-15', 1, '2016-01-25', 50.00, 'mm345','9101c');

CREATE TABLE Purchases

(P_id VARCHAR(5),

P_date DATE,

P_amount FLOAT(5,2),

EP_emp_id VARCHAR(12),

CP_cust_id VARCHAR(15),

CONSTRAINT Purchases_PK PRIMARY KEY (P_id),

CONSTRAINT Purchases_FK1 FOREIGN KEY (EP_emp_id) REFERENCES Employees(employee_id),

CONSTRAINT Purchases_FK2 FOREIGN KEY (CP_cust_id) REFERENCES Customers(customer_id))

ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO Purchases VALUES

('P1','2016-01-01', 100.25, 'mm345','1234a');

INSERT INTO Purchases VALUES

('P2','2016-01-02', 25.25, 'sll963','5678b');

INSERT INTO Purchases VALUES

('P3','2016-01-08', 30.51, 'sll963','9101c');

CREATE TABLE Titles

(Title_id VARCHAR(4),

T_name VARCHAR(30),

medium_type VARCHAR(10),

CONSTRAINT Titles_PK PRIMARY KEY (Title_id))

ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO Titles VALUES

('T1','Titanic', 'CD');

INSERT INTO Titles VALUES

('T2', 'Titanic', 'DVD');

INSERT INTO Titles VALUES

('T3', 'Grace', 'DVD');

CREATE TABLE Copies

(Copy_id VARCHAR(5),

Title_id VARCHAR(5),

PC_P_id VARCHAR(5),

SC_S_id VARCHAR(5),

CONSTRAINT Copies_PK PRIMARY KEY (Copy_id, Title_id),

CONSTRAINT Copies_FK1 FOREIGN KEY (PC_P_id) REFERENCES Purchases(P_id),

CONSTRAINT Copies_FK2 FOREIGN KEY (SC_S_id) REFERENCES Sales(S_id))

ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO Copies VALUES

('C1', 'T1', 'P2', 'S3');

INSERT INTO Copies VALUES

('C2', 'T1', 'P3', 'S2');

INSERT INTO Copies VALUES

('C3', 'T1', 'P4', 'S1');

CREATE TABLE CDs

(Title_id VARCHAR (5),

music_artist VARCHAR(20),

music_type VARCHAR(20),

music_label VARCHAR(20),

CONSTRAINT CDs_PK1 PRIMARY KEY (Title_id),

CONSTRAINT CDs_FK1 FOREIGN KEY (Title_id) REFERENCES Titles(Title_id))

ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO CDs VALUES

('T1', 'Back Street Boys', 'Rock', 'RC');

INSERT INTO CDs VALUES

('T2', 'Michael Jackson', 'pop', 'RC');

INSERT INTO CDs VALUES

('T3', 'One Direction', 'pop', 'RC');

CREATE TABLE DVDs

(Title_id VARCHAR (5),

movie_genre VARCHAR(10),

movie_director VARCHAR(15),

movie_hero VARCHAR(20),

movie_heroine VARCHAR(20),

movie_year INT,

CONSTRAINT DVDs_PK1 PRIMARY KEY (Title_id),

CONSTRAINT DVDs_FK1 FOREIGN KEY (Title_id) REFERENCES Titles(Title_id))

ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO DVDs VALUES

('T1', 'family', 'Sally', 'Michael', 'Maggy', 2002);

INSERT INTO DVDs VALUES

('T3', 'horror', 'Lisa', 'Sami', 'Sara', 2011);

INSERT INTO DVDs VALUES

('T4', 'kids', 'Larry', 'Bob', 'Noor', 2010);

CREATE TABLE Video_games

(Title_id VARCHAR (5),

game_type_platform VARCHAR(10),

game_genre VARCHAR(15),

game_age_rating VARCHAR(10),

CONSTRAINT Video_games_PK1 PRIMARY KEY (Title_id),

CONSTRAINT Video_games_FK1 FOREIGN KEY (Title_id) REFERENCES Titles(Title_id))

ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO Video_games VALUES

('T1', 'PS3', 'Family', 'T3');

INSERT INTO Video_games VALUES

('T2', 'Windows', 'Teen', 'M3');

INSERT INTO Video_games VALUES

('T2', 'PS4', 'adults', 'M2');

CREATE TABLE Rental_Copies

(RC_R_id VARCHAR(5),

RC_Copy_id VARCHAR(5),

Title_id VARCHAR(5),

CONSTRAINT Rental_Copies_PK1 PRIMARY KEY (RC_R_id, RC_Copy_id, Title_id),

CONSTRAINT Rental_Copies_FK1 FOREIGN KEY (RC_R_id) REFERENCES Rentals(R_id),

CONSTRAINT Rental_Copies_FK2 FOREIGN KEY (RC_Copy_id, Title_id) REFERENCES Copies(Copy_id, Title_id))

ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO Rental_Copies VALUES

('R1', 'C2', 'T1');

INSERT INTO Rental_Copies VALUES

('R5', 'C1', 'T2');

INSERT INTO Rental_Copies VALUES

('R2', 'C3', 'T2');