Sample Deliverable #4: DDL Script and DML Population

1a.   Schema DDL Script

This section of the script file creates all tables, including PRIMARY KEY, UNIQUE, and NOT NULL constraints, and also assigns FOREIGN KEY constraints.

/*------------------------------------------------------------------------------
-- Drop Section 
-- Necessary to be able to run the script multiple times
------------------------------------------------------------------------------*/

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

ALTER   TABLE Borrow_T
DROP    CONSTRAINT fk_borrow_condition;

ALTER   TABLE Borrow_T
DROP    CONSTRAINT fk_borrow_item;

ALTER   TABLE Borrow_T
DROP    CONSTRAINT fk_borrow_swapper;

ALTER   TABLE Item_T
DROP    CONSTRAINT fk_item_category;

ALTER   TABLE Item_T
DROP    CONSTRAINT fk_item_swapper;

ALTER   TABLE Group_Membership_T
DROP    CONSTRAINT fk_group_membership_swapper;

ALTER   TABLE Group_Membership_T
DROP    CONSTRAINT fk_group_membership_swapper_group;

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

DROP TABLE Borrow_T;
DROP TABLE Category_T;
DROP TABLE Condition_T;
DROP TABLE Group_Membership_T;
DROP TABLE Item_T;
DROP TABLE Swapper_T;
DROP TABLE Swapper_Group_T;

/*------------------------------------------------------------------------------
-- Create Section 
------------------------------------------------------------------------------*/

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

CREATE  TABLE Borrow_T (
        ItemID INTEGER NOT NULL,
        BorrowerID INTEGER NOT NULL,
        RequestDate DATE NOT NULL,
        LoanDate DATE NULL,
        ReturnDate DATE NULL,
        ReturnCondition VARCHAR(100) NULL,
        
        CONSTRAINT pk_borrow PRIMARY KEY (ItemID, BorrowerID, RequestDate)
        );

CREATE  TABLE Category_T (
        MediaCategory VARCHAR(100) NOT NULL,
        
        CONSTRAINT pk_category PRIMARY KEY (MediaCategory)
        );

CREATE  TABLE Condition_T (
        RetCondition VARCHAR(100) NOT NULL,
        Points INTEGER NOT NULL,
        
        CONSTRAINT pk_condition PRIMARY KEY (RetCondition)
        );

CREATE  TABLE Group_Membership_T (
        GroupName VARCHAR(100) NOT NULL,
        SwapperID INTEGER NOT NULL,
        JoinDate DATE NOT NULL,
        
        CONSTRAINT pk_group_membership PRIMARY KEY (GroupName, SwapperID)
        );

CREATE  TABLE Item_T (
        ID INTEGER NOT NULL,
        Title VARCHAR(100) NOT NULL,
        Description VARCHAR(1000) NOT NULL,
        MediaID VARCHAR(50) NULL,
        MustReturn BOOLEAN NOT NULL,
        AvailableDate DATE NOT NULL,
        UnavailableDate DATE NULL,
        OwnerID INTEGER NOT NULL,
        Category VARCHAR(100) NOT NULL,
        
        CONSTRAINT pk_item PRIMARY KEY (ID)
        );
        
CREATE  TABLE Swapper_T (
        ID INTEGER NOT NULL,
	Email VARCHAR(100) NOT NULL,
        Name VARCHAR(100) NOT NULL,
        Password VARCHAR(100) NOT NULL,
        
        CONSTRAINT uk_swapper UNIQUE (email),
        CONSTRAINT pk_swapper PRIMARY KEY (ID)
        );
        
CREATE  TABLE Swapper_Group_T (
        Name VARCHAR(100) NOT NULL,
        IsInvitationOnly BOOLEAN NOT NULL,
        
        CONSTRAINT pk_swapper_group PRIMARY KEY (Name)
        );

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

ALTER   TABLE Borrow_T
ADD     CONSTRAINT fk_borrow_condition
        FOREIGN KEY (ReturnCondition)
        REFERENCES Condition_T (RetCondition);

ALTER   TABLE Borrow_T
ADD     CONSTRAINT fk_borrow_item
        FOREIGN KEY (ItemID)
        REFERENCES Item_T(ID);

ALTER   TABLE Borrow_T
ADD     CONSTRAINT fk_borrow_swapper
        FOREIGN KEY (BorrowerID)
        REFERENCES Swapper_T (ID);

ALTER   TABLE Item_T
ADD     CONSTRAINT fk_item_category
        FOREIGN KEY (Category)
        REFERENCES Category_T (MediaCategory);

ALTER   TABLE Item_T
ADD     CONSTRAINT fk_item_swapper
        FOREIGN KEY (OwnerID)
        REFERENCES Swapper_T(ID);

ALTER   TABLE Group_Membership_T
ADD     CONSTRAINT fk_group_membership_swapper_group
        FOREIGN KEY (GroupName)
        REFERENCES Swapper_Group_T (Name);

ALTER   TABLE Group_Membership_T
ADD     CONSTRAINT fk_group_membership_swapper
        FOREIGN KEY (SwapperID)
        REFERENCES Swapper_T(ID);

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

1b.   Sample DML Population

This section populates the tables created above with a selection of fabricated, but hopefully realistic, data.

Tables not containing foreign keys are populated first using INSERT INTO VALUES, followed by the population of tables with foreign keys using INSERT INTO SELECT.

No INSERT statement ever includes a literal reference to a surrogate key value. Such values are instead queried as part of an INSERT INTO SELECT statement using related literal values from columns with applied UNIQUE constraints.

/*------------------------------------------------------------------------------
-- DELETE Section --------------------------------------------------------------
-- Rows in tables with foreign keys must be deleted first. ---------------------
------------------------------------------------------------------------------*/

DELETE FROM Borrow_T;
DELETE FROM Item_T;
DELETE FROM Group_Membership_T;
DELETE FROM Swapper_Group_T;
DELETE FROM Swapper_T;
DELETE FROM Condition_T;
DELETE FROM Category_T;

/*------------------------------------------------------------------------------
-- INSERT Section ------------------------------------------------------------
-- Rows must be inserted into tables with foreign keys last. -------------------
------------------------------------------------------------------------------*/

/*-- Populate Category_T -------------------------------------------------------*/


INSERT  INTO Category_T (MediaCategory)
VALUES  ('Book');

INSERT  INTO Category_T
VALUES  ('VHS Video');

INSERT  INTO Category_T
VALUES  ('DVD Video');

INSERT  INTO Category_T
VALUES  ('Audio CD');

/*-- Populate Condition_T ------------------------------------------------------*/


INSERT  INTO Condition_T (RetCondition, Points)
VALUES  ('No Damage', 0);

INSERT  INTO Condition_T
VALUES  ('Minor Damage', -1);

INSERT  INTO Condition_T
VALUES  ('Major Damage or Lost', -3);

INSERT  INTO Condition_T
VALUES  ('Replaced', 1);

/*-- Populate Swapper_Group_T --------------------------------------------------*/

INSERT  INTO Swapper_Group_T (Name, IsInvitationOnly)
VALUES  ('Anybody', 'false');

INSERT  INTO Swapper_Group_T
VALUES  ('Spacefarers', 'true');

/*-- Populate Swapper_T --------------------------------------------------------*/

INSERT  INTO Swapper_T (ID, Name, Email, Password)
VALUES  (1, 'Bartholomew J. Simpson', 'bart@simpsons.com', 'AyCaramba');

INSERT  INTO Swapper_T (ID, Name, Email, Password)
VALUES  (2, 'Stewie Gilligan Griffin', 'loismustdie@yahoo.com', 'Blast!');

INSERT  INTO Swapper_T (ID, Name, Email, Password)
VALUES  (3, 'Roger the Alien', 'roger@american.dad', 'Pecan Sandies');

INSERT  INTO Swapper_T (ID, Name, Email, Password)
VALUES  (4, 'Bender Bending Rodriguez', 'bender@futurama.com', 'BMSMA');

/*-- Populate Group_Membership_T -----------------------------------------------*/

INSERT  INTO Group_Membership_T (GroupName, SwapperID, JoinDate)
SELECT  'Anybody', s.ID, '2008-10-01'
FROM    Swapper_T AS s
WHERE   s.Email = 'bart@simpsons.com';

INSERT  INTO Group_Membership_T (GroupName, SwapperID, JoinDate)
SELECT  'Anybody', s.ID, '2008-10-05'
FROM    Swapper_T AS s
WHERE   s.Email = 'loismustdie@yahoo.com';

INSERT  INTO Group_Membership_T (GroupName, SwapperID, JoinDate)
SELECT  'Anybody', s.ID, '2008-10-10'
FROM    Swapper_T AS s
WHERE   s.Email = 'roger@american.dad';

INSERT  INTO Group_Membership_T (GroupName, SwapperID, JoinDate)
SELECT  'Spacefarers', s.ID, '2008-10-09'
FROM    Swapper_T AS s
WHERE   s.Email = 'roger@american.dad';

INSERT  INTO Group_Membership_T (GroupName, SwapperID, JoinDate)
SELECT  'Spacefarers', s.ID, '2008-10-02'
FROM    Swapper_T AS s
WHERE   s.Email = 'bender@futurama.com';

/*-- Populate Item_T -----------------------------------------------------------*/

INSERT  INTO Item_T (ID, Title, Description, MediaID, MustReturn, AvailableDate, UnavailableDate, OwnerID, Category)
SELECT  1, 'This is Spinal Tap', 'Bootleg copy from TV.', NULL, 'false', '2008-10-01', NULL, s.ID, 'VHS Video'
FROM    Swapper_T AS s
WHERE   s.Email = 'bart@simpsons.com';

INSERT  INTO Item_T (ID, Title, Description, MediaID, MustReturn, AvailableDate, UnavailableDate, OwnerID, Category)
SELECT  2, 'The Godfather', 'Widescreen edition.', 'B0001NBNB6', 'true', '2008-10-05', NULL, s.ID, 'DVD Video'
FROM    Swapper_T AS s
WHERE   s.Email = 'loismustdie@yahoo.com';

INSERT  INTO Item_T (ID, Title, Description, MediaID, MustReturn, AvailableDate, UnavailableDate, OwnerID, Category)
SELECT  3, 'Richard III', 'Winter of our discontent.', '0792844041', 'false', '2008-10-05', '2008-10-10', s.ID, 'DVD Video'
FROM    Swapper_T AS s
WHERE   s.Email = 'loismustdie@yahoo.com';

INSERT  INTO Item_T (ID, Title, Description, MediaID, MustReturn, AvailableDate, UnavailableDate, OwnerID, Category)
SELECT  4, 'Chemistry of Powder and Explosives', 'Count your fingers.', '0913022004', 'false', '2008-10-07', NULL, s.ID, 'Book'
FROM    Swapper_T AS s
WHERE   s.Email = 'loismustdie@yahoo.com';

INSERT  INTO Item_T (ID, Title, Description, MediaID, MustReturn, AvailableDate, UnavailableDate, OwnerID, Category)
SELECT  5, 'This is Spinal Tap', 'Rockumentary.', '6305922756', 'true', '2008-10-02', '2008-11-02', s.ID, 'DVD Video'
FROM    Swapper_T AS s
WHERE   s.Email = 'bender@futurama.com';

INSERT  INTO Item_T (ID, Title, Description, MediaID, MustReturn, AvailableDate, UnavailableDate, OwnerID, Category)
SELECT  6, 'Public Domain Folk Songs', 'Bender''s faves.', NULL, 'true', '2008-10-02', NULL, s.ID, 'Audio CD'
FROM    Swapper_T AS s
WHERE   s.Email = 'bender@futurama.com';

/*-- Populate Borrow_T ---------------------------------------------------------

-- Since ITEM does not have a non-surrogate primary key or a unique constraint,
-- specific items are selected using the title and MediaID columns together.
-- That doesn't guarantee uniqueness, but works for this sample. -------------*/

/*-- Completed Swaps --*/

INSERT  INTO Borrow_T (ItemID, BorrowerID, RequestDate, LoanDate, ReturnDate, ReturnCondition)
SELECT  i.ID, s.ID, '2008-10-05', '2008-10-05', '2008-10-08', 'Minor Damage'
FROM    Item_T AS i JOIN Swapper_T AS s
WHERE   i.Title = 'Richard III'
AND     i.MediaID = '0792844041'
AND     s.Email = 'bart@simpsons.com';

INSERT  INTO Borrow_T (ItemID, BorrowerID, RequestDate, LoanDate, ReturnDate, ReturnCondition)
SELECT  i.ID, s.ID, '2008-10-05', '2008-10-05', '2008-11-08', 'No Damage'
FROM    Item_T AS i JOIN Swapper_T AS s
WHERE   i.Title = 'Public Domain Folk Songs'
AND     i.MediaID IS NULL
AND     s.Email = 'roger@american.dad';

INSERT  INTO Borrow_T (ItemID, BorrowerID, RequestDate, LoanDate, ReturnDate, ReturnCondition)
SELECT  i.ID, s.ID, '2008-10-05', '2008-10-05', '2008-10-29', 'Replaced'
FROM    Item_T AS i JOIN Swapper_T AS s
WHERE   i.Title = 'The Godfather'
AND     i.MediaID = 'B0001NBNB6'
AND     s.Email = 'bart@simpsons.com';

INSERT  INTO Borrow_T (ItemID, BorrowerID, RequestDate, LoanDate, ReturnDate, ReturnCondition)
SELECT  i.ID, s.ID, '2008-10-06', '2008-10-08', '2008-10-10', 'Major Damage or Lost'
FROM    Item_T AS i JOIN Swapper_T AS s
WHERE   i.Title = 'Richard III'
AND     i.MediaID = '0792844041'
AND     s.Email = 'roger@american.dad';

INSERT  INTO Borrow_T (ItemID, BorrowerID, RequestDate, LoanDate, ReturnDate, ReturnCondition)
SELECT  i.ID, s.ID, '2008-10-08', '2008-10-08', '2008-10-21', 'No Damage'
FROM    Item_T AS i JOIN Swapper_T AS s
WHERE   i.Title = 'This is Spinal Tap'
AND     i.MediaID IS NULL
AND     s.Email = 'loismustdie@yahoo.com';

INSERT  INTO Borrow_T (ItemID, BorrowerID, RequestDate, LoanDate, ReturnDate, ReturnCondition)
SELECT  i.ID, s.ID, '2008-10-08', '2008-10-08', '2008-11-02', 'Minor Damage'
FROM    Item_T AS i JOIN Swapper_T AS s
WHERE   i.Title = 'Chemistry of Powder and Explosives'
AND     i.MediaID = '0913022004'
AND     s.Email = 'roger@american.dad';

/*-- Current Swaps --*/

INSERT  INTO Borrow_T (ItemID, BorrowerID, RequestDate, LoanDate)
SELECT  i.ID, s.ID, '2008-10-11', '2008-10-11'
FROM    Item_T AS i JOIN Swapper_T AS s
WHERE   i.Title = 'This is Spinal Tap'
AND     i.MediaID = '6305922756'
AND     s.Email = 'roger@american.dad';

INSERT  INTO Borrow_T (ItemID, BorrowerID, RequestDate, LoanDate)
SELECT  i.ID, s.ID, '2008-10-15', '2008-10-21'
FROM    Item_T AS i JOIN Swapper_T AS s
WHERE   i.Title = 'This is Spinal Tap'
AND     i.MediaID IS NULL
AND     s.Email = 'roger@american.dad';

/*-- Requested Swaps --*/

INSERT  INTO Borrow_T (ItemID, BorrowerID, RequestDate)
SELECT  i.ID, s.ID, '2008-10-14'
FROM    Item_T AS i JOIN Swapper_T AS s
WHERE   i.Title = 'This is Spinal Tap'
AND     i.MediaID IS NULL
AND     s.Email = 'loismustdie@yahoo.com';

2.   Table Contents

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

Borrow_T Table

        ItemID     | BorrowerID  |  RequestDate   |  LoanDate   |  ReturnDate   |  ReturnCondition
-------------------+-------------+----------------+-------------+---------------+----------------------
                 3 |           1 | 2008-10-05     | 2008-10-05  | 2008-10-08    | Minor Damage
                 6 |           3 | 2008-10-05     | 2008-10-05  | 2008-11-08    | No Damage
                 2 |           1 | 2008-10-05     | 2008-10-05  | 2008-10-29    | Replaced
                 3 |           3 | 2008-10-06     | 2008-10-08  | 2008-10-10    | Major Damage or Lost
                 1 |           2 | 2008-10-08     | 2008-10-08  | 2008-10-21    | No Damage
                 4 |           3 | 2008-10-08     | 2008-10-08  | 2008-11-02    | Minor Damage
                 5 |           3 | 2008-10-11     | 2008-10-11  |               |
                 1 |           3 | 2008-10-15     | 2008-10-21  |               |
                 1 |           2 | 2008-10-14     |             |               |
(9 rows)

Category_T Table

 MediaCategory
-----------
 Book
 VHS Video
 DVD Video
 Audio CD
(4 rows)

Condition_T Table

    RetCondition      | Points
----------------------+--------
 No Damage            |      0
 Minor Damage         |     -1
 Major Damage or Lost |     -3
 Replaced             |      1
(4 rows)

Group_Membership_T Table

 GroupName   |  SwapperID | JoinDate
-------------+------------+------------
 Anybody     |          1 | 2008-10-01
 Anybody     |          2 | 2008-10-05
 Anybody     |          3 | 2008-10-10
 Spacefarers |          3 | 2008-10-09
 Spacefarers |          4 | 2008-10-02
(5 rows)

Item_T Table

      ID |               Title                |        Description        |   MediaID   | MustReturn  | AvailableDate  | UnavailableDate  | OwnerID  | Category
---------+------------------------------------+---------------------------+-------------+-------------+----------------+------------------+----------+-----------
       1 | This is Spinal Tap                 | Bootleg copy from TV.     |             | f           | 2008-10-01     |                  |        1 | VHS Video
       2 | The Godfather                      | Widescreen edition.       | B0001NBNB6  | t           | 2008-10-05     |                  |        2 | DVD Video
       3 | Richard III                        | Winter of our discontent. | 0792844041  | f           | 2008-10-05     | 2008-10-10       |        2 | DVD Video
       4 | Chemistry of Powder and Explosives | Count your fingers.       | 0913022004  | f           | 2008-10-07     |                  |        2 | Book
       5 | This is Spinal Tap                 | Rockumentary.             | 6305922756  | t           | 2008-10-02     | 2008-11-02       |        4 | DVD Video
       6 | Public Domain Folk Songs           | Bender's faves.           |             | t           | 2008-10-02     |                  |        4 | Audio CD
(6 rows)

Swapper_T Table

       ID   |          Name            |         Email         |   Password
------------+--------------------------+-----------------------+---------------
          1 | Bartholomew J. Simpson   | bart@simpsons.com     | AyCaramba
          2 | Stewie Gilligan Griffin  | loismustdie@yahoo.com | Blast!
          3 | Roger the Alien          | roger@american.dad    | Pecan Sandies
          4 | Bender Bending Rodriguez | bender@futurama.com   | BMSMA
(4 rows)

Swapper_Group_T Table

 GroupName   | IsInvitationOnly
-------------+--------------------
 Anybody     | f
 Spacefarers | t
(2 rows)

3.   Changes from the Previous Deliverable

No changes were made to the Relational Schema, Entity-Relationship Model, or Proposal documents.