Version 1.0 - initial release

Version 1.1 - Removed old message about data fix


Due Date: 11:59PM, Sunday March 25th
Late Period Ends: 11:59PM, Tuesday March 27th


Lab Assignment 3: Creating a Larger Database in SQL

The purpose of this assignment is for you:

  • To extend the previous database with additional relations;
  • To populate the relations within this database with additional data;
  • To implement a view into the database.
  • To implement a trigger within the database.
  • To test the final database.


Description of the Problem

After implementing all the books in the main library, you boss has decided to add additional information and to add a new library.
Consider, this enhanced Entity-Relationship diagram found here.

To the database you created for Lab 2, add the following relations:
  • Shelf
        Table containing information about the Shelf that a specific book resides on.
  • Library
        Table containing information about the Library that a specific shelf resides in.
  • Audit
        Every time specific transactions are done to specific tables , a record is kept for later perusal. This record should contain the table name being modified, the action (insert, update, or delete), and the date and time the modification took place. Use an auto-generated primary key for this table.

Also add the relationships (or modify your exising relations) between these two new relations and the entities from Lab 2.

In addition, you need to add the gender attribute to Member and populate it from the original data.


Description of Task

You need to create file(s) of SQL commands, similar to the ones provided to you for Lab Assignment 1 and those that you created for Lab Assignment 2, that will perform the steps to create and query the database, as described below.

When you are satisfied that your file of SQL commands works correctly, run it once again with the tee command, to get a log of your work.

Output from the queries should be saved to a separate file. Make sure this file has a .txt extension.

You need to submit your file(s) of SQL commands, your output file, and your README file.


Steps of the Task

Setup

  1. Drop all the tables from Lab 2.

  2. Check your Lab 2 data against the data for Lab 3, adding or modifying your Lab 2 SQL CREATE, INSERT and LOAD statements as needed for Lab 3. The data modifications should be minor - but should be made as necessary.

  3. Create your new tables as defined above. The data to populate these tables can be found as below or from the original data.

    1. Library Data .
      Format of the data is Name, Address. Again, you should break up the composite field (address).
    2. Books in the South Park Library .
      Format of the file is the same as the book data in Lab 2.
      Note: the data for the main libary was in Lab 2.

  4. The current version of MySQL enforces referential integrity.
    Note: this will require data to be loaded in a specific order, and for tables to be dropped in a specific order or for referential integrity to be turned off. The default behavior should be RESTRICT for all tables.

  5. Create SQL statements to provide the following information:

    • List the contents of the Library relation in order according to name.

    • List the contents of the Shelf relation in alphabetic order according to Libary then floor.

    • List the contents of the Stored on relation in alphabetic order according to ISBN.

    • For each book that has copies in both libraries, list the book name, shelf #, and library sorted by book name.

    • For each shelf, list the shelf, library, and number of titles sorted by library and shelf.


  6. Create a set of triggers that stores action, date and time anyone successfully adds an author, adds or deletes a book from a shelf, or modifies the number of copies of a book. An example of creating a trigger in MySQL can be found here.

  7. Create a view that gives Book name, list of authors, shelf, floor, and library name on one line. (Hint: The GROUP_CONCAT clause could be handy here)
    Our implementation of using views requires use of the SECURITY INVOKER syntax. An example is:

    CREATE SQL SECURITY INVOKER VIEW view_name AS SELECT * FROM table_name;

  8. Using this view, provide a list of books, authors, shelf, and library name sorted by book name.

Activity

Create an SQL script to perform the following activities. Note: some of these activities will not be allowed by refential integrity. Identify them in your README.txt.

  1. Add a new book to the Main library, ISBN # 96-42013-10510, shelf 8, floor 2, Title Growing your own Weeds, published by pubid 10000 on 6/24/2012.
  2. Modify the number of copied of ISBN 96-42103-10907 to 8 in the Main library.
  3. Delete Grace Slick from the Author table.
  4. Add Commander Adams to the author table, id 305, office phone 970-555-5555
  5. Add a new book to the South Park library, ISBN # 96-42013-10510, shelf 8, floor 3, Title Growing your own Weeds, published by pubid 10000 on 6/24/2012.
  6. Delete the book Missing Tomorrow from the Main Library.
  7. Add 2 new copies of Eating in the Fort in the South Park library.
  8. Add a new book to the Main library, ISBN # 96-42013-10513, shelf 8, floor 2, Title Growing your own Weeds, published by pubid 90000 on 6/24/2012.
  9. Print the final contents of the Audit table.




Submitting Your Assignment

Submit
  1. your README file,
  2. your SQL command file(s) containing all the CREATE, INSERT/LOAD, SELECT/DELETE/UPDATE,
  3. your output file from your queries (.txt)

using the Canvas CS 430dl Assignment Submission icon
shown on the Canvas CS 430dl Assignments page.

This is Lab Assignment 3.

Late Policy

There will be a 20% late penalty for late assignments.

For more information on SQL

Refer to http://dev.mysql.com/doc/mysql/en/ for the online MySQL Reference Manual, as well as information on downloading MySQL onto your own machine.

You may also wish to check the FAQ on using faure on the RamCT course Assignment Descriptions page.

Please let me know if there are any obvious errors in this writeup.