Version 1.0 - initial release

Due: 11:59 PM, Sunday March 11th
Late Period Ends: 11:59 PM, Tuesday March 13th

Lab Assignment 2: Creating a Database in SQL

The purpose of this assignment is for you:

  • To put together a set of database relation tables that are used in later assignments;
  • To extract the structure of the overall database from an E-R diagram;
  • To create the tables using SQL;
  • To insert data into the tables;
  • To do a few queries of these new tables.

Description of Problem

Congratulations! You have been selected to implement a library database. Your manager has broken the project into phases, each phase builds on the previous phase.

Consider the following Entity-Relationship diagram:

Lab 2 ER diagram

  1. Write out the relation schema for each of the following relations.
    • Author
    • Book
    • Publisher
    • Member
    • Phone

  2. You are to create the tables for a database containing the following entities. For each attribute, think about what the domains would be:
    • Author   (All fields should be appropriately defined.)

    • Phone   (All fields should be appropriately defined.)

    • Publisher   (All fields should be appropriately defined.)

    • Book   (All fields should be appropriately defined.)

    • Member   (All fields should be appropriately defined.)

    as well as the relationships between them.

We will later add the other entity sets and relationships to this set of database tables.

Description of Task

You need to create the database tables for this problem by first writing out the schemas, according to the above entity-relationship diagram.

You need to create a file of SQL commands, similar to the ones provided to you for Lab Assignment 1, that will perform the steps to create, insert data into, and query the database tables, as described below.

When you are satisfied that your file of SQL commands works correctly, run it again using the tee command.

You need to submit your schemas (in a seperate .pdf file), your file of SQL commands, and your output(with a .txt suffix).

Steps of the Task

  1. Write out the relation schema for the five entities to be considered in this database (primary keys should be underlined).

  2. Write out the schema for the corresponding relationships, as well.
    (Note - it may be appropriate for some relationships to be represented as foreign keys - or may be associative entities)

  3. Using SQL, create the Author, Phone, Book, Publisher, and Member relations as well as any relevant relationship tables (if necessary).

    Populate them with the information provided below. (Note: this data is comma-separated and contains composite fields that must be broken apart. This will require you to modify the schemas appropriately, as well as to massage the data to handle Great place to do a little programming.) As is usually the case, the data contains extra fields from the ER diagram - I'm looking for you to implement the ER diagram. This data may require other massaging as well to fit into your schemas. Be sure to keep the scripts that create and populate the database - you will need them for future lab assignments.

    1. Author Data. This data is comma-separated with the following fields: Author ID, Name, Phone numbers
    2. Publisher Data. This data is comma-separated with the following fields: Publisher ID, Name
    3. Member Data. This data is comma-separated with the following fields:
      First line: Member ID, Name, DOB
      Indented lines: ISBN, Checkout Date, Checkin Date (if no Checkin Date, book is still checked out)
    4. Book Data This data is comma-separated with the following fields:
      First line: ISBN, Number of Copies, Shelf, Floor, Title, Publisher, Date Published
      Indented line: Authors

      This may require some editing. If fields are stored as composite fields, you should break them up into their individual fields (such as name broken into last name, first name) as you would do in real life.

    5. List the entire contents of each entity set relation table Below, using SQL SELECT statements:

      • The contents of the Book relation in order according to ISBN.

      • The contents of the Member relation in order of last name then first name.

      • The contents of the Author relation in order of last name then first name.

      • The contents of the Publisher relation in order of Publisher Name.

      • The contents of the Phone relation ordered by the phone number.

    6. List the contents of each of the relationship relation tables.

    7. List the first name then last names of each Member whose last name begins with B.

    8. List the books published by Coyote Publishing sorted by title .

    9. For each Member who have a book currently checked out, print out a list of the books currently checked out by that member. Use first name, last name, member id to identify the member.

    10. For each Author, print out a list of the titles of books they have written. Use first name, last name, author id to identify the author.

    11. Print out a list of Authors (firstname lastname)and the associated phone number, who share phone numbers with another author.

Submitting Your Assignment

  • your relation schema (in PDF format),
  • your SQL command file(s), and
  • your output file (with .txt suffix)
  • you do NOT need to submit any helper programs you created to massage the data.
using the Canvas CS 430dl Assignment Submission icon
shown on the Canvas CS 430dl Assignments page.

This is Lab Assignment 2.

Late Policy

Late assignments will be assessed a 20% Late Penalty.

For more information on SQL

refer to for the online MySQL Reference Manual, as well as information on downloading MySQL onto your own machine.