Introduction to Database Design

This assignment will address the following unit objectives:

$1.$ Create relational tables that incorporate entity integrity and referential integrity constraints.

$2.$ Use normalization to decompose a relation with anomalies into well$-$structured relations.

Grading:

The assignment is worth $20$ points. Each question is worth $2$ points, except #$6,$ which is worth $8$ points.

Task $1$

$($objectives $1$ & $2)$

A local bookstore has asked you to help them track book information for coming special events. The store manager came to you with the attached spreadsheet, hoping there was an easier way.

Answer the following questions based on the manager$$s spreadsheet:

$1.$ List the columns that the manager wants to track.

$2.$ Are any of these columns not atomic? If so$,$ which ones? How should those columns be broken down?

$3.$ What tables or entities do propose to group these attributes together?

$4.$ For each of your proposed entities, what field should be used the primary key?

$5.$ What relationships will exist between your proposed entities? Are there foreign key fields that need to be added to any of the tables as a result? If so$,$ what foreign keys need to be added?

$6.$ Head over to LucidChart and draw out the ERD based on your work so far. Include an export of that model here.

$7.$ Look over the model one last time, checking for any repeating groups. Did you find any redundancies? If so$,$ how would you adjust to normalize out those redundancies?