Home

Deliverable 4: DDL Script and DML Population

1a. Schema DDL Script

CREATE TABLE Customer_T(
  ID INTEGER NOT NULL,
  First VARCHAR(20),
  Last VARCHAR(20),
  PhoneNo VARCHAR(12),
  Email VARCHAR(20),
  Address VARCHAR(50),

  CONSTRAINT pk_customer PRIMARY KEY(ID)
  );

CREATE TABLE Player_T(
  Ranking INTEGER,
  Wins INTEGER,
  Losses INTEGER,
  ID INTEGER NOT NULL,

  CONSTRAINT pk_player PRIMARY KEY(ID),
  CONSTRAINT fk_player FOREIGN KEY(ID) REFERENCES Customer_T(ID)
  );

CREATE TABLE Employee_T(
  EmployeeID INTEGER NOT NULL,
  Salary INTEGER NOT NULL,
  SSN VARCHAR(9),

  CONSTRAINT pk_employee PRIMARY KEY(EmployeeID)
  );

CREATE TABLE Game_T(
  GameID INTEGER NOT NULL AUTO_INCREMENT,
  Winner INTEGER,
  Player1ID INTEGER NOT NULL,
  Player2ID INTEGER NOT NULL,

  CONSTRAINT pk_game PRIMARY KEY(GameID),
  CONSTRAINT fk1_game FOREIGN KEY(Player1ID) REFERENCES Player_T(ID),
  CONSTRAINT fk2_game FOREIGN KEY(Player2ID) REFERENCES Player_T(ID)
);

CREATE TABLE Warehouse_T(
  WarehouseID INTEGER NOT NULL,
  Location VARCHAR(30),
  Size INTEGER,

  CONSTRAINT pk_warehouse PRIMARY KEY(WarehouseID)
  );

CREATE TABLE Account_T(
  Number INTEGER NOT NULL,
  Balance INTEGER,
  ID INTEGER NOT NULL,

  CONSTRAINT pk_account PRIMARY KEY(Number, ID),
  CONSTRAINT fk_account FOREIGN KEY(ID) REFERENCES Customer_T(ID)
  );

CREATE TABLE Order_T(
  OrderNo INTEGER NOT NULL,
  CardID INTEGER NOT NULL,
  Account INTEGER,
  TruckID INTEGER,

  CONSTRAINT pk_order PRIMARY KEY(OrderNo),
  CONSTRAINT fk1_order FOREIGN KEY(CardID) REFERENCES Card_T(CardID),
  CONSTRAINT fk2_order FOREIGN KEY(Account) REFERENCES Account_T(Number),
  CONSTRAINT fk3_order FOREIGN KEY(TruckID) REFERENCES Truck_T(ID)
  );

CREATE TABLE Card_T(
  CardID INTEGER NOT NULL,
  Name VARCHAR(25) NOT NULL,
  Price INTEGER NOT NULL,
  PowerLevel INTEGER,
  HealthLevel INTEGER,
  FactoryID INTEGER,

  CONSTRAINT pk_card PRIMARY KEY(CardID),
  CONSTRAINT fk_card FOREIGN KEY(FactoryID) REFERENCES Factory_T (FactoryID)
  );

CREATE TABLE Truck_T(
  ID INTEGER NOT NULL AUTO_INCREMENT,
  StorageLocation VARCHAR(35),

  CONSTRAINT pk_truck PRIMARY KEY(ID)
  );

CREATE TABLE Factory_T(
  FactoryID INTEGER NOT NULL,
  Location VARCHAR(35),

  CONSTRAINT pk_factory PRIMARY KEY(FactoryID)
  );

CREATE TABLE TruckDriver_T(
  ID INTEGER NOT NULL,

  CONSTRAINT pk_truckdriver PRIMARY KEY(ID),
  CONSTRAINT fk_truckdriver FOREIGN KEY(ID) REFERENCES Employee_T (EmployeeID)
  );

CREATE TABLE Imagineer_T(
  ID INTEGER NOT NULL,

  CONSTRAINT pk_imagineer PRIMARY KEY(ID),
  CONSTRAINT fk_imagineer FOREIGN KEY(ID) REFERENCES Employee_T (EmployeeID)
  );

CREATE TABLE GraphicDesigner_T(
  ID INTEGER NOT NULL,

  CONSTRAINT pk_graphicdesigner PRIMARY KEY(ID),
  CONSTRAINT fk_graphicdesigner FOREIGN KEY(ID) REFERENCES Employee_T (EmployeeID)
  );

1b. Sample DML Population


INSERT INTO Customer_T VALUES(1, 'Joe', 'Deringer', '998-997-4123', 'JDer123@g.com', '345 Square Ln, Oakland, CA');
INSERT INTO Customer_T VALUES(2, 'Naomi', 'Yamada', '786-232-6543', 'mountain34@peace.com', '36 Park Place, Atlantic City NJ');
INSERT INTO Customer_T VALUES(3, 'Shinnosuke', 'Nohara', '456-986-0876', 'oshiri@yo.com', '34 H St Kasukabe, Japan');
INSERT INTO Customer_T VALUES(4, 'Peter', 'Prancer', '876-674-8765', 'spyder@web.com', '12 Broad Street, New York City, NY');
INSERT INTO Customer_T VALUES(5, 'Max', 'Weber', '765-234-7654', 'pizza@palace.com', '70 Street St, Tallahassee, Florida');
INSERT INTO Customer_T VALUES(6, 'Nick', 'Mazzeo', '543-789-0076', 'nmazzeo@villanova.edu', '9 Ithan Ave, Radnor, PA');

INSERT INTO Card_T VALUES(87565432, 'Argath the Blaster', 20, 7, 3, 1);
INSERT INTO Card_T VALUES(65748323, 'Deaths Blade, Zarnax' 35, 4, 3, 2);
INSERT INTO Card_T VALUES(47365321, 'Deathtail', 99, 2, 12, 1);
INSERT INTO Card_T VALUES(37483748, 'Legendary Warrior', 100, 13, 12, 1);
INSERT INTO Card_T VALUES(32489878, 'Legendary Paladin', 101, 14, 11, 2);
INSERT INTO Card_T VALUES(11134555, 'Legendary Marksman', 87, 15, 5, 2);

INSERT INTO Factory_T VALUES(1, 'Little Rock, Arkansas');
INSERT INTO Factory_T VALUES(2, 'Cheyenne, Wyoming');

INSERT INTO Truck_T (StorageLocation) SELECT f.Location FROM Factory_T WHERE f.ID = 1;
INSERT INTO Truck_T (StorageLocation) SELECT f.Location FROM Factory_T WHERE f.ID = 2;

INSERT INTO Imagineer_T VALUES(1);

INSERT INTO TruckDriver_T VALUES(1);

INSERT INTO GraphicDesigner_T VALUES(1);

INSERT INTO Player_T VALUES(1, 7, 0, 3);

INSERT INTO Order_T VALUES(1, 87565432, 1, 1);

INSERT INTO Warehouse_T VALUES(1, 'New York City, New York', 1400);

INSERT INTO Account_T VALUES(1, 800, 3);

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

2. Table Contents

Each of the following tables' contents was produced using the SQL Query: SELECT * FROM table

Customer_T Table

+----+------------+----------+--------------+----------------------+------------------------------------+
| ID | First      | Last     | PhoneNo      | Email                | Address                            |
+----+------------+----------+--------------+----------------------+------------------------------------+
|  1 | Joe        | Deringer | 998-997-4123 | JDer123@g.com        | 345 Square Ln, Oakland, CA         | 
|  2 | Naomi      | Yamada   | 786-232-6543 | mountain34@peace.com | 36 Park Place, Atlantic City NJ    | 
|  3 | Shinnosuke | Nohara   | 456-986-0876 | oshiri@yo.com        | 34 H St Kasukabe, Japan            | 
|  4 | Peter      | Prancer  | 876-674-8765 | spyder@web.com       | 12 Broad Street, New York City, NY | 
|  5 | Max        | Weber    | 765-234-7654 | pizza@palace.com     | 70 Street St, Tallahassee, Florida | 
|  6 | Nick       | Mazzeo   | 543-789-0076 | nmazzeo@villanova.ed | 9 Ithan Ave, Radnor, PA            | 
+----+------------+----------+--------------+----------------------+------------------------------------+
6 rows in set (0.00 sec)

Player_T Table

+---------+------+--------+----+
| Ranking | Wins | Losses | ID |
+---------+------+--------+----+
|       1 |    7 |      0 |  3 | 
+---------+------+--------+----+
1 row in set (0.00 sec)

Game_T Table

+--------+--------+-----------+-----------+
| GameID | Winner | Player1ID | Player2ID |
+--------+--------+-----------+-----------+
|      1 |      1 |         1 |         2 | 
+--------+--------+-----------+-----------+
1 row in set (0.00 sec)

Warehouse_T Table

+-------------+-------------------------+------+
| WarehouseID | Location                | Size |
+-------------+-------------------------+------+
|           1 | New York City, New York | 1400 | 
+-------------+-------------------------+------+
1 row in set (0.00 sec)

Account_T Table

+--------+---------+----+
| Number | Balance | ID |
+--------+---------+----+
|      1 |     800 |  3 | 
+--------+---------+----+
1 row in set (0.00 sec)

Order_T Table

+---------+----------+---------+---------+
| OrderNo | CardID   | Account | TruckID |
+---------+----------+---------+---------+
|       1 | 87565432 |       1 |       1 | 
+---------+----------+---------+---------+
1 row in set (0.00 sec)

Card_T Table

+----------+--------------------+-------+------------+-------------+-----------+
| CardID   | Name               | Price | PowerLevel | HealthLevel | FactoryID |
+----------+--------------------+-------+------------+-------------+-----------+
| 87565432 | Argath the Blaster |    20 |          7 |           3 |         1 | 
| 47365321 | Deathtail          |    99 |          2 |          12 |         1 | 
| 37483748 | Legendary Warrior  |   100 |         13 |          12 |         1 | 
| 32489878 | Legendary Paladin  |   101 |         14 |          11 |         2 | 
| 11134555 | Legendary Marksman |    87 |         15 |           5 |         2 | 
+----------+--------------------+-------+------------+-------------+-----------+
5 rows in set (0.00 sec)

Truck_T Table

+----+-------------------+
| ID | StorageLocation   |
+----+-------------------+
|  1 | Cheyenne, Wyoming | 
+----+-------------------+
1 row in set (0.00 sec)

Factory_T Table

+-----------+-----------------------+
| FactoryID | Location              |
+-----------+-----------------------+
|         1 | Little Rock, Arkansas | 
|         2 | Cheyenne, Wyoming     | 
+-----------+-----------------------+
2 rows in set (0.00 sec)

TruckDriver_T Table

+----+
| ID |
+----+
|  1 | 
+----+
1 row in set (0.01 sec)

Imagineer_T Table

+----+
| ID |
+----+
|  1 | 
+----+
1 row in set (0.00 sec)

GraphicDesigner_T Table

+----+
| ID |
+----+
|  1 | 
+----+
1 row in set (0.00 sec)

3. Changes from the Previous Deliverable

There were no changes made to the Relational Schema, Entity-Relationship Model, or Proposal documents. The only change we made was a minor syntactical change to the name of one of the ID fields.

Home