LAB $2$

Assignment Instructions

Materials and Resources

ASSIGNMENT INSTRUCTIONS

To complete this assignment:

Read the case example and carry out all of the tasks. Then submit your lab to the assignment submission folder.

Case Example: Suppose that you are hired by a used record store to build a database for their inventory. They tell you that they have some data needs, including:

The format $(45,$ LP$,$ CD$,$ DVD$)$

Title of the item

A list of songs

A list of artists credited on the album cover

Price the item was bought for

ID of the Customer the item was bought from $(-1$ if not registered$)$

Name of customer the item was bought from

Date the item was sold $(0$ for still in stock$)$

ID of the customer the item was sold to $(0$ for unsold, $-1$ for not registered$)$

Name of the customer the item was sold to

Price the item was sold for

To reduce inefficiencies and anomalies, you recognize that you need more than one table.

Section A $$ Developing Requirements $(210$ Marks$)$: Answer the following questions:

If you convert this to first normal form, which attributes need to be pulled into a separate table $(90$ marks$)?$

Normal forms

Keeping the customer ID and customer name in the same table as the record sales information $($for both the customer who sold you the record and the customer who you sold it to$),$ violates which normal form requirement $(30$ marks$)?$

How would you adjust to meet the requirements of this normal form $(90$ marks$)?$

Section B $$ Design $(150$ Marks$)$: Answer the following questions:

In order to satisfy requirements for $3$NF$,$ what tables would you recommend to store all the required information $(120$ marks$)?$

What changes, if any, would you make so that the $3$NF design satisfies BCNF $(30$ marks$)?$

Section C $$ Queries $(180$ Marks$)$: Complete the following tasks:

Resale Query: Using your design, describe the tables and attributes you would need to write a query that identifies any record bought by a customer, and then later presented to be sold back to you by that same customer. Do not write the query! $(30$ marks$).$

Song Title Query: Write an SQL query that could take a word, find all song titles that contain that word, and return the name of the albums that include those songs $(120$ marks$).$

Creating Queries

Write a query to identify which albums are held in inventory in multiple copies and how long the oldest copies have been in stock $(30$ marks$).$

Answer these questions: Would you recommend keeping one row per album even if there are multiple copies? How would that affect your design? $(40$ marks$)$

Section D $$ Updates $(120$ Marks$)$: Answer the following question:

How would you add information about the quality of the record $($Mint$,$ Excellent, Good, Scratched, Junk$)?(100$ marks$)$

Refer to this rubric for scoring.