CMSC320 - Project 3 (SQL)

Fork
CREATE TABLE MOVIE (
	movieID    NUMBER PRIMARY KEY,
	accoladeID   NUMBER,
	availabilityID    NUMBER, 
	distributorID    NUMBER,
    productTypeID    NUMBER,
    catalogID   NUMBER,
	title    VARCHAR2,
	priceFromDistributor   NUMBER,
	priceToConsumer    NUMBER,
	isDiscounted    BOOLEAN,
	category    VARCHAR2,
	lengthInMins    NUMBER,
    FOREIGN KEY (accoladeID) REFERENCES ACCOLADE(accoladeID),
    FOREIGN KEY (catalogID) REFERENCES PRODUCT_CATALOG(catalogID),
    FOREIGN KEY (availabilityID) REFERENCES AVAILABILITY(availabilityID),
    FOREIGN KEY (distributorID) REFERENCES DISTRIBUTOR(distributorID),
    FOREIGN KEY (productTypeID) REFERENCES PRODUCT_TYPE(productTypeID)
);

CREATE TABLE DISTRIBUTOR (
    distributorID	NUMBER PRIMARY KEY,
	catalogID	NUMBER,
	shipmentID	NUMBER,
	isActive	BOOLEAN,
    FOREIGN KEY (catalogID)  REFERENCES PRODUCT_CATALOG(catalogID),
  	FOREIGN KEY (shipmentID)  REFERENCES SHIPMENT(shipmentID)
);

CREATE TABLE PRODUCT_CATALOG (
    catalogID   NUMBER PRIMARY KEY,
	movieID	NUMBER,
    FOREIGN KEY (movieID) REFERENCES MOVIE(movieID)
);

CREATE TABLE ACCOLADE (
    accoladeID NUMBER PRIMARY KEY,
  	actorID	NUMBER,
    directorID	NUMBER,
    awardID	NUMBER,
  	FOREIGN KEY (actorID)  REFERENCES ACTOR(actorID),
  	FOREIGN KEY (directorID)  REFERENCES DIRECTOR(directorID),
  	FOREIGN KEY (awardID)  REFERENCES AWARD(awardID)	
);

CREATE TABLE ACTOR (
    actorID	NUMBER PRIMARY KEY,
	name VARCHAR2
);

CREATE TABLE DIRECTOR (
    directorID	NUMBER PRIMARY KEY,
	name VARCHAR2
);

CREATE TABLE AWARD (
    awardID	NUMBER PRIMARY KEY,
	name VARCHAR2
);

CREATE TABLE SHIPMENT (
    shipmentID	NUMBER PRIMARY KEY,
    movieID	NUMBER,
    deliveryDate DATE,
    FOREIGN KEY (movieID)  REFERENCES MOVIE(movieID)	
);

CREATE TABLE AVAILABILITY (
    availabilityID	NUMBER PRIMARY KEY,
    movieID	NUMBER,
    numCurrentlyAvailable  NUMBER,
    nextShipmentDate  DATE,
    isInStock  BOOLEAN,
    FOREIGN KEY (movieID)  REFERENCES MOVIE(movieID)	
);

CREATE TABLE PRODUCT_TYPE (
    productTypeID	NUMBER PRIMARY KEY,
    isVHS	BOOLEAN,
    isDVD	BOOLEAN
);

CREATE TABLE ORDER_RECORD (
    orderID	NUMBER PRIMARY KEY,
    movieID	NUMBER,
    dateRented	DATE,
    dateExpectedReturned	DATE,
    isOverdue	BOOLEAN,
    wasDamaged	BOOLEAN,
    FOREIGN KEY (movieID)  REFERENCES MOVIE(movieID)	
);

CREATE TABLE CUSTOMER (
    customerID	NUMBER PRIMARY KEY,
	orderID	NUMBER,
    name	VARCHAR2,
	address	  VARCHAR2,
    telephoneNumber	  NUMBER,
    FOREIGN KEY (orderID)  REFERENCES ORDER_RECORD(orderID)	
);

-- Insert into ACTOR
INSERT INTO ACTOR (actorID, name) VALUES (1, "Tom Hanks");
INSERT INTO ACTOR (actorID, name) VALUES (2, "Scarlett Johansson");
INSERT INTO ACTOR (actorID, name) VALUES (3, "Brad Pitt");
INSERT INTO ACTOR (actorID, name) VALUES (4, "Emma Watson");
INSERT INTO ACTOR (actorID, name) VALUES (5, "Leonardo DiCaprio");

-- Insert into DIRECTOR
INSERT INTO DIRECTOR (directorID, name) VALUES (1, "Christopher Nolan");
INSERT INTO DIRECTOR (directorID, name) VALUES (2, "Quentin Tarantino");
INSERT INTO DIRECTOR (directorID, name) VALUES (3, "Steven Spielberg");
INSERT INTO DIRECTOR (directorID, name) VALUES (4, "Greta Gerwig");
INSERT INTO DIRECTOR (directorID, name) VALUES (5, "Martin Scorsese");

-- Insert into AWARD
INSERT INTO AWARD (awardID, name) VALUES (1, "Oscar for Best Picture");
INSERT INTO AWARD (awardID, name) VALUES (2, "Golden Globe for Best Actor");
INSERT INTO AWARD (awardID, name) VALUES (3, "BAFTA for Best Director");
INSERT INTO AWARD (awardID, name) VALUES (4, "SAG Award for Best Ensemble");
INSERT INTO AWARD (awardID, name) VALUES (5, "Cannes Film Festival Palme d'Or");

INSERT INTO MOVIE (movieID, accoladeID, availabilityID, distributorID, productTypeID, catalogID, title, priceFromDistributor, priceToConsumer, isDiscounted, category, lengthInMins)
VALUES (1, 1, 1, 1, 1, 1, "Inception", 15.99, 29.99, FALSE, "Sci-Fi", 148);

INSERT INTO DISTRIBUTOR (distributorID, catalogID, shipmentID, isActive)
VALUES (1, 1, 1, TRUE);
INSERT INTO DISTRIBUTOR (distributorID, catalogID, shipmentID, isActive)
VALUES (2, 2, 2, TRUE);
INSERT INTO DISTRIBUTOR (distributorID, catalogID, shipmentID, isActive)
VALUES (3, 3, 3, FALSE);
INSERT INTO DISTRIBUTOR (distributorID, catalogID, shipmentID, isActive)
VALUES (4, 4, 4, TRUE);
INSERT INTO DISTRIBUTOR (distributorID, catalogID, shipmentID, isActive)
VALUES (5, 5, 5, TRUE);

INSERT INTO PRODUCT_CATALOG (catalogID, movieID)
VALUES (1, 1);
INSERT INTO PRODUCT_CATALOG (catalogID, movieID)
VALUES (2, 1);
INSERT INTO PRODUCT_CATALOG (catalogID, movieID)
VALUES (3, 1);
INSERT INTO PRODUCT_CATALOG (catalogID, movieID)
VALUES (4, 1);
INSERT INTO PRODUCT_CATALOG (catalogID, movieID)
VALUES (5, 1);

INSERT INTO ACCOLADE (accoladeID, actorID, directorID, awardID)
VALUES (1, 1, 1, 1);
INSERT INTO ACCOLADE (accoladeID, actorID, directorID, awardID)
VALUES (2, 2, 2, 2);
INSERT INTO ACCOLADE (accoladeID, actorID, directorID, awardID)
VALUES (3, 3, 3, 3);
INSERT INTO ACCOLADE (accoladeID, actorID, directorID, awardID)
VALUES (4, 4, 4, 4);
INSERT INTO ACCOLADE (accoladeID, actorID, directorID, awardID)
VALUES (5, 5, 5, 5);

INSERT INTO SHIPMENT (shipmentID, movieID, deliveryDate)
VALUES (1, 1, "2023-01-15");
INSERT INTO SHIPMENT (shipmentID, movieID, deliveryDate)
VALUES (2, 1, "2023-02-01");
INSERT INTO SHIPMENT (shipmentID, movieID, deliveryDate)
VALUES (3, 1, "2023-02-15");
INSERT INTO SHIPMENT (shipmentID, movieID, deliveryDate)
VALUES (4, 1, "2023-03-01");
INSERT INTO SHIPMENT (shipmentID, movieID, deliveryDate)
VALUES (5, 1, "2023-03-15");

INSERT INTO AVAILABILITY (availabilityID, movieID, numCurrentlyAvailable, nextShipmentDate, isInStock)
VALUES (1, 1, 50, "2023-01-20", TRUE);
INSERT INTO AVAILABILITY (availabilityID, movieID, numCurrentlyAvailable, nextShipmentDate, isInStock)
VALUES (2, 1, 20, "2023-02-05", TRUE);
INSERT INTO AVAILABILITY (availabilityID, movieID, numCurrentlyAvailable, nextShipmentDate, isInStock)
VALUES (3, 1, 10, "2023-02-20", FALSE);
INSERT INTO AVAILABILITY (availabilityID, movieID, numCurrentlyAvailable, nextShipmentDate, isInStock)
VALUES (4, 1, 30, "2023-03-05", TRUE);
INSERT INTO AVAILABILITY (availabilityID, movieID, numCurrentlyAvailable, nextShipmentDate, isInStock)
VALUES (5, 1, 30, "2023-03-20", TRUE);

INSERT INTO PRODUCT_TYPE (productTypeID, isVHS, isDVD)
VALUES (1, TRUE, FALSE);
INSERT INTO PRODUCT_TYPE (productTypeID, isVHS, isDVD)
VALUES (2, FALSE, TRUE);
INSERT INTO PRODUCT_TYPE (productTypeID, isVHS, isDVD)
VALUES (3, TRUE, TRUE);
INSERT INTO PRODUCT_TYPE (productTypeID, isVHS, isDVD)
VALUES (4, FALSE, TRUE);
INSERT INTO PRODUCT_TYPE (productTypeID, isVHS, isDVD)
VALUES (5, TRUE, FALSE);

-- Insert into ORDER_RECORD
INSERT INTO ORDER_RECORD (orderID, movieID, dateRented, dateExpectedReturned, isOverdue, wasDamaged)
VALUES (1, 1, "2023-01-21", "2023-01-28", FALSE, FALSE);
INSERT INTO ORDER_RECORD (orderID, movieID, dateRented, dateExpectedReturned, isOverdue, wasDamaged)
VALUES (2, 1, "2023-02-02", "2023-02-09", FALSE, TRUE);
INSERT INTO ORDER_RECORD (orderID, movieID, dateRented, dateExpectedReturned, isOverdue, wasDamaged)
VALUES (3, 1, "2023-02-16", "2023-02-23", TRUE, FALSE);
INSERT INTO ORDER_RECORD (orderID, movieID, dateRented, dateExpectedReturned, isOverdue, wasDamaged)
VALUES (4, 1, "2023-03-02", "2023-03-09", FALSE, FALSE);
INSERT INTO ORDER_RECORD (orderID, movieID, dateRented, dateExpectedReturned, isOverdue, wasDamaged)
VALUES (5, 1, "2023-03-16", "2023-03-23", FALSE, TRUE);

-- Insert into CUSTOMER
INSERT INTO CUSTOMER (customerID, orderID, name, address, telephoneNumber)
VALUES (1, 1, "John Doe", "123 Main St, Cityville", 555-1234);
INSERT INTO CUSTOMER (customerID, orderID, name, address, telephoneNumber)
VALUES (2, 2, "Jane Smith", "456 Oak St, Townsville", 555-5678);
INSERT INTO CUSTOMER (customerID, orderID, name, address, telephoneNumber)
VALUES (3, 3, "Bob Johnson", "789 Pine St, Villageland", 555-9012);
INSERT INTO CUSTOMER (customerID, orderID, name, address, telephoneNumber)
VALUES (4, 4, "Alice Brown", "101 Cedar St, Burgville", 555-3456);
INSERT INTO CUSTOMER (customerID, orderID, name, address, telephoneNumber)
VALUES (5, 5, "Eve Davis", "202 Maple St, Hamletown", 555-7890);
Fork
Embed on website
