Database Design due Tue 19 Sep 10:30

\begin{purpose}
This assignment provides practice designing databases. As with ...
...hat will require you to make
some decisions and interpretations.
\end{purpose}

Overview

You will be creating designs for three databases which will be provided in a text file (i.e., has a .txt extension). For each design you will do the following:
  1. A sentence explaining the purpose of the database.
  2. List all fields the database will store along with a phrase or sentence that explains what it is.
  3. Identify and list all functional dependencies.
  4. Show the proposed BCNF design using list notation.

If I were doing this assignment for the book database we've been using my solution would look something like this:

Book Database

List of Fields
  isbn        the standard book number
  title       title of the book
  num_pages   number of pages in the book
  year        copyright year of the book
  auth_id     author's id number
  auth_first  book author's first name
  auth_last   book author's last name
  pub_id      publisher's id number
  pub_name    name of the book's publisher
  pub_addr    city/state of the publisher

Functional Dependencies
  FD: pub_id --> pub_name, pub_addr
  FD: author_id --> author_first, author_last
  FD: isbn --> title,num_pages,year

Design
  publisher=(pub_id*,pub_name,pub_addr)
  author=(author_id*,auth_first,auth_last)
  book=(isbn*,title,num_pages,year,auth_id,pub_id)

Three Designs

Produce a database design for each of these:
  1. The ``candy'' database given in the class handout. You may need to modify/adjust fields in order for the design to make sense.

  2. The ``four'' database (dealing with recipes) given in the class handout. You may need to modify/adjust fields in order for the design to make sense.

  3. Imagine a major hotel is wanting to keep track of room reservations. To do so they need to store the following information: customer name, customer payment information, date of reservation, number of adults with the reservation, number of children with the reservation, room number, number of beds in the room, maximum occupancy for the room, smoking permitted in the room or not.

Grading and Submission

All three of your designs should be in a single text document and should follow the instructions provided. Upload the document into Canvas prior to the beginning of class on the due date.

The assignment has 30 possible points with each design being designated 10 points as follows: 1 point for the descriptive sentence, 3 points for list of fields and descriptions, 2 points for identifying functional dependencies, and 4 points for the final design.

Quick Links