Project Deliverable #5: Sample DML Population

This deliverable populates the database designed for the Swapaholic.com site with a selection of fabricated, but hopefully realistic, data. Refer to the project page for sample deliverables for previous project phases.

Schema Population Script

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';

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)

Changes from the Previous Deliverable

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