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.
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';
Each of the following tables' contents was produced using the SQL query: SELECT * FROM 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)
MediaCategory ----------- Book VHS Video DVD Video Audio CD (4 rows)
RetCondition | Points ----------------------+-------- No Damage | 0 Minor Damage | -1 Major Damage or Lost | -3 Replaced | 1 (4 rows)
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)
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)
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)
GroupName | IsInvitationOnly -------------+-------------------- Anybody | f Spacefarers | t (2 rows)
No changes were made to the Relational Schema, Entity-Relationship Model, or Proposal documents.