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 */

Drop Table if Exists Patron_T;
Drop Table if Exists Librarian_T;
Drop Table if Exists Loan_T;
Drop Table if Exists Content_T;
Drop Table if Exists Book_T;
Drop Table if Exists Magazine_T;
Drop Table if Exists Newspaper_T;
Drop Table if Exists CD_T;
Drop Table if Exists DVD_T;
Drop Table if Exists Author_T;
/* Create Table section */

Create Table Patron_T (
       PatronID Integer Not Null,
       FirstName VarChar(25) Not Null,
       LastName VarChar(25) Not Null,
       EmailAddress VarChar(50) Not Null,

       Constraint pk_patron Primary Key (PatronID)
       );

Create Table Librarian_T (
       LibrarianID Integer Not Null,

       Constraint pk_librarian Primary Key (LibrarianID)
       );

Create Table Loan_T (
       PatID Integer Not Null,
       ContID Integer Not Null,
       
       Constraint pk_loan Primary Key (PatID,ContID)
       );

Create Table Content_T (
       ContentID Integer Not Null,
       Title VarChar(100) Not Null,
       PublicationDate Date Null,
       Cost Integer Not Null,
       NumCopies Integer Not Null,

       Constraint pk_content Primary Key (ContentID)
       );

Create Table Book_T (
       BookID Integer Not Null,
       Publisher VarChar(50) Not Null,
       BookGenre VarChar(15) Not Null,

       Constraint pk_book Primary Key (BookID)
       );

Create Table Magazine_T (
       MagazineID Integer Not Null,
       MagazineVolNo Integer Not Null,
       MagazineIssueNo Integer Not Null,

       Constraint pk_magazine Primary Key (MagazineID)
       );

Create Table Newspaper_T (
       NewspaperID Integer Not Null,
       NewspaperVolNo Integer Not Null,
       NewspaperIssueNo Integer Not Null,

       Constraint pk_newspaper Primary Key (NewspaperID)
       );

Create Table CD_T (
       CDID Integer Not Null,
       ArtistName VarChar (50) Not Null,
       CDGenre VarChar (15) Not Null,
       RecordCompany VarChar (25) Null,

       Constraint pk_CD Primary Key (CDID)
       );

Create Table DVD_T (
       DVDID Integer Not Null,
       DVDGenre VarChar(15) Not Null,
       MovieProducer VarChar(25) Not Null,

       Constraint pk_DVD Primary Key (DVDID)
       );

Create Table Author_T (
       BID Integer Not Null,
       AuthorName VarChar(25) Not Null,

       Constraint pk_author Primary Key (BID)
       );

/* Create Foreign Key Constraints Section */

Alter Table Librarian_T
      Add Constraint fk_librarian1
      Foreign Key (LibrarianID)
      References Patron_T (PatronID);

Alter Table Loan_T
      Add Constraint fk_loan1
      Foreign Key (PatID)
      References Patron_T (PatronID);

Alter Table Loan_T
      Add Constraint fk_loan2
      Foreign Key (ContID)
      References Content_T (ContentID);
     
Alter Table Book_T
      Add Constraint fk_book1
      Foreign Key (BookID)
      References Content_T (ContentID);

Alter Table Magazine_T
      Add Constraint fk_magazine1
      Foreign Key (MagazineID)
      References Content_T (ContentID);

Alter Table Newspaper_T
      Add Constraint fk_newspaper1
      Foreign Key (NewspaperID)
      References Content_T (ContentID);

Alter Table CD_T
      Add Constraint fk_cd1
      Foreign Key (CDID)
      References Content_T (ContentID);

Alter Table DVD_T
      Add Constraint fk_dvd1
      Foreign Key (DVDID)
      References Content_T (ContentID);

Alter Table Author_T
      Add Constraint fk_author1
      Foreign Key (BID)
      References Book_T (BookID);

1b.   Sample DML Population

This section populates the tables created above with a selection of fabricated, but 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.

/* Insert Section */

/* Populate Table Patron_T */

Insert Into Patron_T (PatronID, FirstName, LastName, EmailAddress)
Values (1, 'Ken', 'Newcomb', 'knewco02@villanova.edu');

Insert Into Patron_T (PatronID, FirstName, LastName, EmailAddress)
Values (2, 'John', 'Frusciante', 'johnfrusciante@gmail.com');

Insert Into Patron_T (PatronID, FirstName, LastName, EmailAddress)
Values (3, 'Anthony', 'Kiedis', 'anthonykiedis@gmail.com');

Insert Into Patron_T (PatronID, FirstName, LastName, EmailAddress)
Values (4, 'Michael', 'Balzary', 'flea@gmail.com');

Insert Into Patron_T (PatronID, FirstName, LastName, EmailAddress)
Values (5, 'Chad', 'Smith', 'chadsmith@gmail.com');

/* Populate Table Content_T */

Insert Into Content_T (ContentID, Title, PublicationDate, Cost, TotalCopies, Copies)
Values (1, 'Hamlet', '2002-07-01', 10, 3, 3);

Insert Into Content_T (ContentID, Title, PublicationDate, Cost, TotalCopies, Copies)
Values (2, 'Time Magazine', '2013-11-11', 15, 2, 2);

Insert Into Content_T (ContentID, Title, PublicationDate, Cost, TotalCopies, Copies)
Values (3, 'The Villanovan', '2013-11-02', 1, 1, 1);

Insert Into Content_T (ContentID, Title, PublicationDate, Cost, TotalCopies, COpies)
Values (4, 'Stadium Arcadium', '2002-01-01', 10, 2, 2);

Insert Into Content_T (ContentID, Title, PublicationDate, Cost, TotalCopies, Copies)
Values (5, 'Harry Potter', '1999-07-14', 15, 4, 4);

/* Populate Book, Magazine, Newspaper, CD, and DVD Tables.
   These are dependent upon Content. */

Insert Into Book_T (BookID, Publisher, BookGenre)
Select c.ContentID, 'ABC Publishing', 'Tragedy'
FROM Content_T as c
Where c.ContentID = 1;

Insert Into Magazine_T (MagazineID, MagazineVolNo, MagazineIssueNo)
Select c.ContentID, 56, 2
From Content_T as c
Where c.ContentID = 2;

Insert Into Newspaper_T (NewspaperID, NewspaperVolNo, NewspaperIssueNo)
Select c.ContentID, 14, 4
From Content_T as c
Where c.ContentID = 3;

Insert Into CD_T (CDID, ArtistName, CDGenre, RecordCompany)
Select c.ContentID, 'Red Hot Chili Peppers', 'Funk Rock', 'Warner Bros Music'
From Content_T as c
Where c.ContentID = 4;

Insert Into DVD_T (DVDID, DVDGenre, MovieProducer)
Select c.ContentID, 'Fantasy', 'David Heyman'
From Content_T as c
Where c.ContentID = 5;

/* Populate Author_T, dependent upon Book_T */

Insert Into Author_T (BID, AuthorName)
Select b.BookID, 'William Shakespeare'
From Book_T as b
Where b.BookID = 1;

/* Populate Librarian_T, dependent upon Patron_T */

Insert Into Librarian_T (LibrarianID)
Select p.PatronID
From Patron_T as p
Where p.PatronID = 1;

/* Finally, Populate Loan_T with a few sample loans... */

Insert Into Loan_T (PatID, ContID)
Select p.PatronID, c.ContentID
From Patron_T as p Join Content_T as c
Where p.PatronID = 1
And c.ContentID = 4;

Update Content_T
Set Copies = (Copies - 1)
Where ContentID = 4;

Insert Into Loan_T (PatID, ContID)
Select p.PatronID, c.ContentID
From Patron_T as p Join Content_T as c
Where p.PatronID = 2
And c.ContentID = 1;

Update Content_T
Set Copies = (Copies - 1)
Where ContentID = 1;

Insert Into Loan_T (PatID, ContID)
Select p.PatronID, c.ContentID
From Patron_T as p Join Content_T as c
Where p.PatronID = 4
And c.ContentID = 1;

Update Content_T
Set Copies = (Copies - 1)
Where ContentID = 1;

2.   Table Contents

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

Patron_T Table

+----------+-----------+------------+--------------------------+
| PatronID | FirstName | LastName   | EmailAddress             |
+----------+-----------+------------+--------------------------+
|        1 | Ken       | Newcomb    | knewco02@villanova.edu   | 
|        2 | John      | Frusciante | johnfrusciante@gmail.com | 
|        3 | Anthony   | Kiedis     | anthonykiedis@gmail.com  | 
|        4 | Michael   | Balzary    | flea@gmail.com           | 
|        5 | Chad      | Smith      | chadsmith@gmail.com      | 
+----------+-----------+------------+--------------------------+
5 rows in set (0.00 sec)

Librarian_T Table

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

Loan_T Table

+-------+--------+
| PatID | ContID |
+-------+--------+
|     1 |      4 | 
|     2 |      1 | 
|     4 |      1 | 
+-------+--------+
3 rows in set (0.00 sec)

Content_T Table

+-----------+-----------------+------+-----------+
| ContentID | PublicationDate | Cost | NumCopies |
+-----------+-----------------+------+-----------+
|         1 | 2002-07-01      |   10 |         3 | 
|         2 | 2013-11-11      |   15 |         2 | 
|         3 | 2013-11-02      |    1 |         1 | 
|         4 | 2002-01-01      |   10 |         2 | 
|         5 | 1999-07-14      |   15 |         4 | 
+-----------+-----------------+------+-----------+
5 rows in set (0.00 sec)

Book_T Table

+--------+--------+----------------+-----------+
| BookID | Title  | Publisher      | BookGenre |
+--------+--------+----------------+-----------+
|      1 | Hamlet | ABC Publishing | Tragedy   | 
+--------+--------+----------------+-----------+
1 row in set (0.00 sec)

Magazine_T Table

+------------+--------------+---------------+-----------------+
| MagazineID | MagazineName | MagazineVolNo | MagazineIssueNo |
+------------+--------------+---------------+-----------------+
|          2 | Time         |            56 |               2 | 
+------------+--------------+---------------+-----------------+
1 row in set (0.00 sec)

Newspaper_T Table

+-------------+----------------+----------------+------------------+
| NewspaperID | NewspaperName  | NewspaperVolNo | NewspaperIssueNo |
+-------------+----------------+----------------+------------------+
|           3 | The Villanovan |             14 |                4 | 
+-------------+----------------+----------------+------------------+
1 row in set (0.00 sec)

CD_T Table

+------+-----------------------+------------------+-----------+-------------------+
| CDID | ArtistName            | AlbumName        | CDGenre   | RecordCompany     |
+------+-----------------------+------------------+-----------+-------------------+
|    4 | Red Hot Chili Peppers | Stadium Arcadium | Funk Rock | Warner Bros Music | 
+------+-----------------------+------------------+-----------+-------------------+
1 row in set (0.00 sec)

DVD_T Table

+-------+--------------+----------+---------------+
| DVDID | DVDTitle     | DVDGenre | MovieProducer |
+-------+--------------+----------+---------------+
|     5 | Harry Potter | Fantasy  | David Heyman  | 
+-------+--------------+----------+---------------+
1 row in set (0.00 sec)

Author_T Table

+-----+---------------------+
| BID | AuthorName          |
+-----+---------------------+
|   1 | William Shakespeare | 
+-----+---------------------+
1 row in set (0.00 sec)

3.   Changes from the Previous Deliverable

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