/*------------------------------------------------------------------------------

-- Drop Section

-- Necessary to be able to run the script multiple times

------------------------------------------------------------------------------*/

 

/* "DROP" Foreign Key Constraints --------------------------------------------*/

 

ALTER   TABLE Customer_QVC

DROP    CONSTRAINT fk_order_id;

 

ALTER   TABLE Customer_QVC

DROP    CONSTRAINT fk_qvcapps_id;

 

ALTER   TABLE Customer_QVC

DROP    CONSTRAINT fk_account_id;

 

ALTER   TABLE Customer_QVC

DROP    CONSTRAINT fk_qcard_id;

 

ALTER   TABLE ORDER_QVC

DROP    CONSTRAINT fk_product_id;

 

 

/* DROP Tables ---------------------------------------------------------------*/

 

DROP TABLE Customer_QVC;

DROP TABLE QCard_QVC;

DROP TABLE Account_QVC;

DROP TABLE QVCApps_T;

DROP TABLE Order_QVC;

DROP TABLE Product_QVC;

DROP TABLE Employee_QVC;

DROP TABLE Locations_QVC;

 

/*------------------------------------------------------------------------------

-- Create Section

------------------------------------------------------------------------------*/

 

/* CREATE Tables -------------------------------------------------------------*/

 

 

CREATE  TABLE Customer_QVC (

 

        CustomerID INTEGER NOT NULL,

        Email VARCHAR(100) NULL,

        Phone VARCHAR(11) NULL,

        Address VARCHAR(100) NULL,

        ZipCode INTEGER NULL,

        Country VARCHAR(100) NULL,

  

        CONSTRAINT pk_customer PRIMARY KEY (CustomerID)

        );

 

 

CREATE  TABLE QCard_QVC (

        QCardNumber INTEGER NOT NULL,

        Balance INTEGER NOT NULL,   

 

        CONSTRAINT pk_qcard PRIMARY KEY (QCardNumber)

        );

 

 

CREATE  TABLE Account_QVC (

 

        AccountNumber INTEGER NOT NULL,

        History VARCHAR(100) NULL,

        PIN INTEGER NOT NULL,

 

        CONSTRAINT pk_account PRIMARY KEY (AccountNumber)

        );

 

 

CREATE  TABLE QVCApps_QVC (

 

        QVCAppsID INTEGER NOT NULL,

        InitialDownloadPrice INTEGER NULL,

        MonthlyFee INTEGER NULL,    

 

        CONSTRAINT pk_qvcapps PRIMARY KEY (QVCApps)

        );

 

CREATE  TABLE Order_QVC (

 

        OrderID INTEGER NOT NULL,

        ShippingDate DATE NOT NULL,

        ArrivalDate DATE NULL,    

 

        CONSTRAINT pk_order PRIMARY KEY (OrderID)

        );

      

 

CREATE  TABLE Product_QVC (

 

        ProductID INTEGER NOT NULL,

        Name VARCHAR(100) NOT NULL,

        Cost INTEGER NOT NULL,

        Description VARCHAR(100) NOT NULL,

 

        CONSTRAINT pk_product PRIMARY KEY (ProductID)

        );

 

 

CREATE  TABLE Employee_QVC (

 

        EmployeeID INTEGER NOT NULL,

        Name VARCHAR(100) NOT NULL,

        Title VARCHAR(100) NOT NULL,

        Department VARCHAR(100) NOT NULL,

 

        CONSTRAINT pk_employee PRIMARY KEY (EmployeeID)

        );

 

CREATE  TABLE Location_QVC (

 

        BusinessID INTEGER NOT NULL,

        Address VARCHAR(100) NOT NULL,

        State VARCHAR(30) NOT NULL,

        Country VARCHAR(40) NOT NULL,

 

        CONSTRAINT pk_product PRIMARY KEY (BusinessID)

        );

 

      

/* "CREATE" Foreign Key Constraints ------------------------------------------*/

 

 

ALTER   TABLE Customer_QVC

ADD     CONSTRAINT fk_order_id

        FOREIGN KEY (QVCOrderID)

        REFERENCES Order_QVC (OrderID);

 

ALTER   TABLE Customer_QCV

ADD     CONSTRAINT fk_apps_id

        FOREIGN KEY (AppsID)

        REFERENCES QVCApps_QVC(QVCAppsID);

 

ALTER   TABLE Customer_QVC

ADD     CONSTRAINT fk_account_id

        FOREIGN KEY (QVCAccountID)

        REFERENCES Account_QVC (AccountNumber);

 

ALTER   TABLE Customer_QVC

ADD     CONSTRAINT fk_qcard_id

        FOREIGN KEY (QCardID)

        REFERENCES Category_T (QCardNumber);

 

ALTER   TABLE Order_QVC

ADD     CONSTRAINT fk_product_id

        FOREIGN KEY (QVCProductID)

        REFERENCES Order_QVC (ProductID);

 

ALTER   TABLE Employee_QVC

ADD     CONSTRAINT fk_account_id

        FOREIGN KEY (QVCAccountID)

        REFERENCES Employee_QVC (AccountNumber);

 

 

 

/*------ Populate Tables ---------------------------------------------*/

 

 

INSERT INTO Customer_QVC VALUES (1, 'pgriffin@verizon.net', '6100001234', 'Cohog', 00001, 'United States');

 

INSERT INTO Customer_QVC VALUES (2, 'lgriffin@gmail.com', '6100001234', 'Cohog', 00002, 'United States');

 

INSERT INTO Customer_QVC VALUES (3, 'hsimpson@yahoo.com', '6106106789', 'Springfield', 00003, 'United States');

 

INSERT INTO Customer_QVC VALUES (4, 'ubolt@gmail.com', '6106106789', 'St Andrew', 00004, 'Jamaica');

 

 

 

INSERT INTO QCard_QVC VALUES (1, 100);

 

INSERT INTO QCard_QVC VALUES (2, 500);

 

INSERT INTO QCard_QVC VALUES (3, 50);

 

INSERT INTO QCard_QVC VALUES (4, 25);

 

 

 

INSERT INTO Account_QVC  VALUES (1, 'No Recent Purchases', 000001);

 

INSERT INTO Account_QVC  VALUES (2, 'Recent Purchases', 000010);

 

INSERT INTO Account_QVC  VALUES (3, 'No Recent Purchases', 000231);

 

INSERT INTO Account_QVC  VALUES (4, 'No Recent Purchases', 000021);

 

 

 

 INSERT INTO QVCApps_QVC VALUES (1, 2, 1);

 

INSERT INTO QVCApps_QVC VALUES (2, 1, 2);

 

INSERT INTO QVCApps_QVC VALUES (3, 1, 1);

 

INSERT INTO QVCApps_QVC VALUES (4, 5, 1);

 

 

 

INSERT INTO Order_QVC VALUES (1, '2012-09-10', '2012-09-11');

 

INSERT INTO Order_QVC VALUES (2, '2010-05-10’, '2010-07-10’);

 

INSERT INTO Order_QVC VALUES (3, '2008-11-10', '2008-11-15');

 

 

 

INSERT INTO Product_QVC VALUES (1, 'Glasses Case', 5, 'Made In China');

 

INSERT INTO Product_QVC VALUES (2, 'Diamond Necklace', 299, 'Made In USA');

 

INSERT INTO Product_QVC VALUES (3, 'Toy Car', 12, 'Made In China');

 

 

 

INSERT INTO Location_QVC VALUES (1, ‘1200 Wilson Drive’, ‘PA’, ‘United States’);

 

INSERT INTO Location_QVC VALUES (2, ‘1 Qvc Drive’, ‘VA’, ‘United States’);

 

INSERT INTO Location_QVC VALUES (3, ‘8815 Silent Wings’, ‘TX’, ‘United States’);

 

INSERT INTO Location_QVC VALUES (4, ‘1365 Enterprise’, ‘PA’, ‘United States’);

 

INSERT INTO Location_QVC VALUES (5, ‘4000 Arrowhead Blvd’, ‘NC’, ‘United States’);

 

 

INSERT INTO Employee_QVC Values (1, ‘Joe Smith’, ‘Information Technology’, ‘Services’);

 

INSERT INTO Employee_QVC Values (2, ‘Jill George’, ‘Marketing’, ‘Media’);

 

INSERT INTO Employee_QVC Values (3, ‘Sam Mason’, ‘Distribution’, ‘Warehouse’);

 

 

 

 

/*----------------------Show Tables-----------------------------------*/

 

mysql> SELECT * FROM Customer_QVC;

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

| CustomerID | Email                | Phone      | Address     | ZipCode | Country       |

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

|          1 | pgriffin@verizon.net | 6100001234 | Cohog       |       1 | United States |

|          2 | lgriffin@gmail.com   | 6100001234 | Cohog       |       2 | United States |

|          3 | hsimpson@yahoo.com   | 6106106789 | Springfield |       3 | United States |

|          4 | ubolt@gmail.com      | 6106106789 | St Andrew   |       4 | Jamaica       |

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

4 rows in set (0.00 sec)

 

mysql> SELECT * FROM QCard_QVC;

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

| QCardNumber | Balance |

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

|           1 |     100 |

|           2 |     500 |

|           3 |      50 |

|           4 |      25 |

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

4 rows in set (0.00 sec)

 

mysql> SELECT * FROM Account_QVC;

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

| AccountNumber | History             | PIN |

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

|             1 | No Recent Purchases |   1 |

|             2 | Recent Purchases    |  10 |

|             3 | No Recent Purchases | 231 |

|             4 | No Recent Purchases |  21 |

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

4 rows in set (0.00 sec)

 

mysql> SELECT * FROM QVCApps_QVC;

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

| QVCAppsID | InitialDownloadPrice | MonthlyFee |

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

|         1 |                    2 |          1 |

|         2 |                    1 |          2 |

|         3 |                    1 |          1 |

|         4 |                    5 |          1 |

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

4 rows in set (0.01 sec)

 

mysql> SELECT * FROM Order_QVC;

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

| OrderID | ShippingDate | ArrivalDate |

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

|       1 | 2012-09-10   | 2012-09-11  |

|       3 | 2008-11-10   | 2008-11-15  |

|       2 | 2010-05-10   | 2010-07-10  |

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

3 rows in set (0.00 sec)

 

mysql> SELECT * FROM Product_QVC;

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

| ProductID | Name             | Cost | Description   |

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

|         1 | Glasses Case     |    5 | Made In China |

|         2 | Diamond Necklace |  299 | Made In USA   |

|         3 | Toy Car          |   12 | Made In China |

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

3 rows in set (0.00 sec)

 

mysql> SELECT * FROM Location_QVC;

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

| BusinessID | Address             | State | Country       |

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

|          1 | 1200 Wilson Drive   | PA    | United States |

|          2 | 1 Qvc Drive         | VA    | United States |

|          3 | 8815 Silent Wings   | TX    | United States |

|          4 | 1365 Enterprise     | PA    | United States |

|          5 | 4000 Arrowhead Blvd | NC    | United States |

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

5 rows in set (0.01 sec)

 

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

| EmployeeID | Name        | Title                  | Department |

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

|          1 | Joe Smith   | Information Technology | Services   |

|          2 | Jill George | Marketing              | Media      |

|          3 | Sam Mason   | Distribution           | Warehouse  |

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

3 rows in set (0.00 sec)