/**************************************************
	Exericse 12.12, DVD Rentals
	Lab 5, CREATE, INSERT and UPDATE

**************************************************/



/**************************************************
	To remove tables if this script has already 
	been run. 
***************************************************/
 
DROP TABLE Rental

DROP TABLE DVDCopy

DROP TABLE DVD

DROP TABLE Member

ALTER TABLE Branch
DROP CONSTRAINT branch_manager_FK

DROP TABLE Staff

DROP TABLE Branch


CREATE TABLE Branch
(branchNo CHAR(4) PRIMARY KEY,
manager CHAR(7),
street VARCHAR(100),
city CHAR(30),
state CHAR(2),
zip CHAR(5),
phone CHAR(10))

CREATE TABLE Staff
(staffNo CHAR(7) PRIMARY KEY,
 branchNo CHAR(4) CONSTRAINT staff_branch_FK 
	FOREIGN KEY REFERENCES Branch(branchNo),
fName CHAR(30),
lName CHAR(30), 
position CHAR(30), 
salary SMALLMONEY)


/* Add the manager reference */
ALTER TABLE Branch
ADD CONSTRAINT branch_manager_FK 
FOREIGN KEY (manager) REFERENCES Staff(staffNo)


/* Insert some records into Branch */
INSERT INTO Branch (branchNo, street, city, state, zip, phone) 
	VALUES ('B299','17 West Park Street', 'Butte', 
		'MT','59701','4067234432')

INSERT INTO Branch (branchNo, street, city, state, zip, phone) 
	VALUES ('B100','500 Montana Street', 'Butte', 
		'MT','59701','4067829855')

INSERT INTO Branch (branchNo, street, city, state, zip, phone) 
	VALUES ('B516','170 Higgins', 'Missoula', 
		'MT','59834','4064984422')


/* Insert some records into Staff */
INSERT INTO Staff (staffNo, branchNo, fName, lName, 
	position, salary) VALUES ('S333333','B299', 
		'Eli', 'Rose','cashier','12.00')

INSERT INTO Staff (staffNo, branchNo, fName, lName, 
	position, salary) VALUES ('S654321','B299', 
		'Rio', 'Riley','stocker','12.00')


INSERT INTO Staff (staffNo, branchNo, fName, lName, 
	position, salary) VALUES ('S123456','B299', 
		'Jill', 'Kon','manager','22.00')


/* Jill Kon manages branch B299 and B516 */
UPDATE Branch 
SET manager='S123456' WHERE branchNo='B299'

UPDATE Branch 
SET manager='S123456' WHERE branchNo='B516'

/* Eli Rose manages branch B100 */
UPDATE Branch 
SET manager='S333333' WHERE branchNo='B100'


CREATE TABLE Member
(memberNo CHAR(7) PRIMARY KEY,
 branchNo CHAR(4) CONSTRAINT member_branch_FK 
	FOREIGN KEY REFERENCES Branch(branchNo),
fName CHAR(30),
lName CHAR(30), 
dateRegistered DATE)

INSERT INTO Member (memberNo, branchNo, fName, lName, 
	dateRegistered) VALUES ('M223344','B299', 
		'Jake', 'Smtih','1/13/2015')

INSERT INTO Member (memberNo, branchNo, fName, lName, 
	dateRegistered) VALUES ('M146786','B299', 
		'John', 'Jolly','2/3/2015')

INSERT INTO Member (memberNo, branchNo, fName, lName, 
	dateRegistered) VALUES ('M13579','B299', 
		'Jenny', 'Bean','2/13/2015')


CREATE TABLE DVD
(catalogNo CHAR(8) PRIMARY KEY,
 title VARCHAR(200),
 category CHAR(10),
 rentalCost SMALLMONEY, 
 actors VARCHAR(1000),
 directors VARCHAR(100))

INSERT INTO DVD (catalogNo, title, category, 
	rentalCost, actors, directors)
VALUES ('DR199', 'Great Gatsby', 'drama', 5.99, 
	'Toby Maguire, Leonardo DiCaprio, Carey Mulligan', 
	'Baz Luhrmann')

INSERT INTO DVD (catalogNo, title, category, 
	rentalCost, actors, directors)
VALUES ('DR200', 'Finest Hours', 'drama', 5.99, 
	'Chris Pine, Casey Affleck, Ben Foster, Eric Bana', 
	'Craig Gillespie')

INSERT INTO DVD (catalogNo, title, category, 
	rentalCost, actors, directors)
VALUES ('SF100', 'Inception', 'sci fi', 3.99, 
	'Leonardo DiCaprio, Ellen Page', 
	'Christopher Nolan')

CREATE TABLE DVDCopy
(dVDNo INTEGER IDENTITY(1,1) PRIMARY KEY, 
 catalogNo CHAR(8) CONSTRAINT dVDCopy_catalog_FK 
	FOREIGN KEY REFERENCES DVD(catalogNo),
 status CHAR(10))

INSERT INTO DVDCopy (catalogNo, status)
VALUES ('DR199', 'scratched')

INSERT INTO DVDCopy (catalogNo, status)
VALUES ('SF100', 'mint')

INSERT INTO DVDCopy (catalogNo, status)
VALUES ('SF100', 'scratched')

CREATE TABLE Rental 
(rentalNo INTEGER IDENTITY(1,1) PRIMARY KEY, 
 memberNo CHAR(7) CONSTRAINT rental_member_FK 
	FOREIGN KEY REFERENCES Member(memberNo),
 dVDNo INTEGER CONSTRAINT rental_dVDCopy_FK 
	FOREIGN KEY REFERENCES DVDCopy(dVDNo),
 dateOut DATE, 
 dateIn DATE)

INSERT INTO Rental (memberNo, dVDNo, dateOut, dateIn)
VALUES ('M223344', 1, '2016-2-5', '2016-2-10')

INSERT INTO Rental (memberNo, dVDNo, dateOut, dateIn)
VALUES ('M223344', 2, '2016-2-5', '2016-2-10')

INSERT INTO Rental (memberNo, dVDNo, dateOut, dateIn)
VALUES ('M13579', 2, '2016-1-15', '2016-2-2')
