TABLE CREATION

DROP DATABASE VideoDB;

CREATE DATABASE VideoDB;

USE VideoDB;

DROP TABLE IF EXISTS User_T;

DROP TABLE IF EXISTS Employee_T;

DROP TABLE IF EXISTS CUSTOMER_T;

DROP TABLE IF EXISTS ORDER_T;

DROP TABLE IF EXISTS Return_T;

Drop TABLE IF EXISTS Hub_T;

Drop Table IF EXISTS Item_T;

DROP TABLE IF EXISTS Game_T;

Drop TABLE IF EXISTS Movie_T;

DROP TABLE IF EXISTS Res_T;

Drop TABLE IF EXISTS Rating_T;

CREATE TABLE User_T (

    UserID Integer PRIMARY KEY,

    Name Varchar(45),

    Phone Varchar(12),

    Email Varchar(30),

    Address VarChar(45)

)Engine=InnoDB;

CREATE TABLE Hub_T (

    HubID INTEGER Primary Key,

    Name Varchar(45),

    Location VARCHAR(45)

)Engine=InnoDB;

CREATE TABLE Employee_T (

    EmployeeID INTEGER PRIMARY KEY,

    HubID Integer,

    CONSTRAINT fk FOREIGN KEY(HubID) REFERENCES Hub_T(HubID)

        ON DELETE CASCADE

)Engine=InnoDB;

CREATE TABLE Item_T (

    ItemID INTEGER PRIMARY KEY,

    Name Varchar(45),

    ReleaseDate DATE,

    Price FLOAT,

    HubID INTEGER,

    CONSTRAINT fk2 FOREIGN KEY(HubID) REFERENCES Hub_T(HubID)

        ON DELETE CASCADE

)Engine=InnoDB;

CREATE TABLE Order_T (

    OrderID INTEGER PRIMARY KEY,

    UserID INTEGER,

    OrderDate DATE,

    DeliveryDate DATE,

    ReturnDate DATE,

    CONSTRAINT orderfk FOREIGN KEY(UserID) REFERENCES User_T(UserID)

        ON DELETE CASCADE

)Engine=InnoDB;

Create TABLE Return_T (

    ReturnID INTEGER PRIMARY KEY,

    UserID INTEGER,

    ItemID INTEGER,

    ReturnDate DATE,

    ConditionReport VARCHAR(45),

    CONSTRAINT refk1 FOREIGN KEY(UserID) REFERENCES User_T(UserID)

        ON DELETE CASCADE,

    CONSTRAINT refk2 FOREIGN KEY(ItemID) REFERENCES Item_T(ItemID)

        ON DELETE CASCADE

)Engine=InnoDB;

CREATE TABLE Rating_T (

    RatingID INTEGER PRIMARY KEY,

    UserID INTEGER,

    ItemID INTEGER,

    Rating INTEGER,

    CONSTRAINT ratingfk1 FOREIGN KEY(UserID) REFERENCES User_T(UserID),

    CONSTRAINT ratingfk2 FOREIGN KEY(ItemID) REFERENCES Item_T(ItemID)

)Engine=InnoDB;

CREATE TABLE Res_T (

    ReservationID INTEGER PRIMARY KEY,

    UserID INTEGER,

    ItemID INTEGER,

    ResDate DATE,

    ExprDate DATE,

    CONSTRAINT resfk1 FOREIGN KEY(UserID) REFERENCES User_T(UserID),

    CONSTRAINT resfk2 FOREIGN KEY(ItemID) REFERENCES Item_T(ItemID)

)Engine=InnoDB;

CREATE TABLE Orderline_T (

    ItemID INTEGER,

    HubID INTEGER,

    OrderID INTEGER,

    PRIMARY KEY(OrderID,ItemID,HubID),

    CONSTRAINT Foreign Key(ItemID) REFERENCES Item_T(ItemID),

    CONSTRAINT FOREIGN KEY(HubID) REFERENCES Hub_T(HubID),

    CONSTRAINT FOREIGN KEY(OrderID) REFERENCES Order_T(OrderID)

);

DATA INSERTION

INSERT INTO User_T (UserID,Name,Phone,Email,Address)

    VALUES(12345,'Rudolph Steiner',4443331111,'rsguy@hotmail.com','999

        Linden Road Toronto Canada');

INSERT INTO User_T (UserID,Name,Phone,Email,Address)

    VALUES(12346,'Schlomo Riddling',4442221111,'Eiguy@hotmail.com','324 West

        STreet Road Portland Oregan');

INSERT INTO User_T (UserID,Name,Phone,Email,Address)

    VALUES(12347,'Dan Sickles',1231234321,'gettysburgguy@hotmail.com','999

        Coast Street Philadelphia Pennsylvania');

INSERT INTO User_T (UserID,Name,Phone,Email,Address)

    VALUES(12348,'Hermes Triguglio',4356450099,'lololololol@gmail.com','

        123 Aloha Ave Honolulu Hawaii');

INSERT INTO User_T (UserID,Name,Phone,Email,Address)

    VALUES(12349,'Albert Pike',1236667890,'mzns@gmail.com',

        '456 Park Street Washington DC');

INSERT INTO Hub_T (HubID,Name,Location)

    Values(1,'Primary Hub','Philadelphia PA');

   

INSERT INTO Hub_T (HubID,Name,Location)

    Values(2,'Secondary Hub','West Chester PA');

INSERT INTO Hub_T (HubID,Name,Location)

    Values(3,'Tertiary Hub','Downingtown PA');

INSERT INTO Item_T(ItemID,Name,ReleaseDate,Price,HubID)

    Values(1,'Wayne\'s World','1992-5-13',20.00,1);

INSERT INTO Item_T(ItemID,Name,ReleaseDate,Price,HubID)

    Values(2,'World Of Warcraft','2006-10-13',60.00,2);

INSERT INTO Item_T(ItemID,Name,ReleaseDate,Price,HubID)

    Values(3,'Pulp Fiction','1999-7-12',20.00,3);

INSERT INTO Item_T(ItemID,Name,ReleaseDate,Price,HubID)

    Values(4,'The Hangover','2008-4-12',20.00,1);

INSERT INTO Employee_T(EmployeeID,HubID)

    Values (1,1);

INSERT INTO Employee_T(EmployeeID,HubID)

    Values (2,1);

INSERT INTO Employee_T(EmployeeID,HubID)

    Values (3,2);

INSERT INTO Employee_T(EmployeeID,HubID)

    Values (4,3);

INSERT INTO Order_T(OrderID,UserID,OrderDate,DeliveryDate,ReturnDate)

    SELECT 1,UserID,'2013-1-1','2013-2-3','2013-3-4'

    FROM User_T

    WHERE Name = 'Albert Pike';

INSERT INTO Order_T(OrderID,UserID,OrderDate,DeliveryDate,ReturnDate)

    SELECT 2,UserID,'2013-1-1','2013-2-3','2013-3-4'

    FROM User_T

    WHERE Name = 'Rudolp Steiner';

INSERT INTO Order_T(OrderID,UserID,OrderDate,DeliveryDate,ReturnDate)

    SELECT 3,UserID,'2013-1-1','2013-2-3','2013-3-4'

    FROM User_T

    WHERE Name = 'Schlomo Riddling';

INSERT INTO Orderline_T(ItemID,HubID,OrderID)

    SELECT ItemID,Item_T.HubID,OrderID

    FROM Item_T,Order_T

    Where Name = 'World of Warcraft' AND OrderID = 1;

INSERT INTO Orderline_T(ItemID,HubID,OrderID)

    SELECT ItemID,Item_T.HubID,OrderID

    FROM Item_T,Order_T

    Where Name = 'Wayne\'s World' AND OrderID = 2;

INSERT INTO Orderline_T(ItemID,HubID,OrderID)

    SELECT ItemID,Item_T.HubID,OrderID

    FROM Item_T,Order_T

    Where Name = 'The Hangover' AND OrderID = 3;

INSERT INTO Rating_T(RatingID,UserID,ItemID,Rating)

    SELECT 1,UserID,ItemID,10

    FROM User_T,Item_T

    WHERE Item_T.Name='The Hangover' AND User_T.Name = 'Schlomo Riddling';

INSERT INTO Rating_T(RatingID,UserID,ItemID,Rating)

    SELECT 2,UserID,ItemID,9

    FROM User_T,Item_T

    WHERE Item_T.Name='Wayne\'s World' AND User_T.Name = 'Albert Pike';

INSERT INTO Res_T(ReservationID,UserID,ItemID,ResDate,ExprDate)

    SELECT 1,UserID,ItemID,'2013-3-4','2013-4-7'

    FROM User_T,Item_T

    WHERE User_T.Name='Albert Pike' And Item_T.Name='The Hangover';

INSERT INTO Return_T(ReturnID,UserID,ItemID,ReturnDate,ConditionReport)

    SELECT 1,UserID,ItemID,'2013-11-17','Good'

    FROM User_T,Item_T

    WHERE User_T.Name='Albert Pike' AND Item_T.Name='The Hangover';

TABLES

Untitled document

+------------+-------+

| EmployeeID | HubID |

+------------+-------+

|          1 |     1 |

|          2 |     1 |

|          3 |     2 |

|          4 |     3 |

+------------+-------+

4 rows in set (0.00 sec)

+-------+---------------+-----------------+

| HubID | Name          | Location        |

+-------+---------------+-----------------+

|     1 | Primary Hub   | Philadelphia PA |

|     2 | Secondary Hub | West Chester PA |

|     3 | Tertiary Hub  | Downingtown PA  |

+-------+---------------+-----------------+

3 rows in set (0.00 sec)

+--------+-------------------+-------------+-------+-------+

| ItemID | Name              | ReleaseDate | Price | HubID |

+--------+-------------------+-------------+-------+-------+

|      1 | Wayne's World     | 1992-05-13  |    20 |     1 |

|      2 | World Of Warcraft | 2006-10-13  |    60 |     2 |

|      3 | Pulp Fiction      | 1999-07-12  |    20 |     3 |

|      4 | The Hangover      | 2008-04-12  |    20 |     1 |

+--------+-------------------+-------------+-------+-------+

4 rows in set (0.00 sec)

+---------+--------+------------+--------------+------------+

| OrderID | UserID | OrderDate  | DeliveryDate | ReturnDate |

+---------+--------+------------+--------------+------------+

|       1 |  12349 | 2013-01-01 | 2013-02-03   | 2013-03-04 |

|       3 |  12346 | 2013-01-01 | 2013-02-03   | 2013-03-04 |

+---------+--------+------------+--------------+------------+

2 rows in set (0.00 sec)

+--------+-------+---------+

| ItemID | HubID | OrderID |

+--------+-------+---------+

|      2 |     2 |       1 |

|      4 |     1 |       3 |

+--------+-------+---------+

2 rows in set (0.00 sec)

+----------+--------+--------+--------+

| RatingID | UserID | ItemID | Rating |

+----------+--------+--------+--------+

|        1 |  12346 |      4 |     10 |

|        2 |  12349 |      1 |      9 |

+----------+--------+--------+--------+

2 rows in set (0.00 sec)

+---------------+--------+--------+------------+------------+

| ReservationID | UserID | ItemID | ResDate    | ExprDate   |

+---------------+--------+--------+------------+------------+

|             1 |  12349 |      4 | 2013-03-04 | 2013-04-07 |

+---------------+--------+--------+------------+------------+

1 row in set (0.00 sec)

+----------+--------+--------+------------+-----------------+

| ReturnID | UserID | ItemID | ReturnDate | ConditionReport |

+----------+--------+--------+------------+-----------------+

|        1 |  12349 |      4 | 2013-11-17 | Good            |

+----------+--------+--------+------------+-----------------+

1 row in set (0.00 sec)

+--------+------------------+------------+---------------------------+-----------------------------------------------+

| UserID | Name             | Phone      | Email                     | Address                                       |

+--------+------------------+------------+---------------------------+-----------------------------------------------+

|  12345 | Rudolph Steiner  | 4443331111 | rsguy@hotmail.com         | 999

        Linden Road Toronto Canada        |

|  12346 | Schlomo Riddling | 4442221111 | Eiguy@hotmail.com         | 324 West

        STreet Road Portland Oregan |

|  12347 | Dan Sickles      | 1231234321 | gettysburgguy@hotmail.com | 999

        Coast Street Philadelphia Pennsyl |

|  12348 | Hermes Triguglio | 4356450099 | lololololol@gmail.com     |

        123 Aloha Ave Honolulu Hawaii        |

|  12349 | Albert Pike      | 1236667890 | mzns@gmail.com            | 456 Park Street Washington DC                 |

+--------+------------------+------------+---------------------------+-----------------------------------------------+

5 rows in set (0.00 sec)


© Andrew Keenan & Kamel Branch-Powers