Question

1. The functional dependencies for the ProAudio relation:

c_id -> f_name, l_name, address, city, state, zip

item_id -> title, price

ord_no -> c_id, order_date

ord_no  + item_id  -> shipped

zip -> city, state

Original ProAudio relation:

c_id

f_name

I_name

address

city

state

zip

ord_no

item_id

title

price

order_date

shipped

01

Jane

Doe

123 Elm St

Ely

NV

11111

1-1

12-31

More Blues

8.99

12-2-00

no

02

Fred

Fish

321 Oak St

Ely

NV

11111

2-1

21-12

Jazz Songs

9.99

11-9-00

yes

01

Jane

Doe

123 Elm St

Ely

NV

11111

1-2

12-21

The Blues

8.99

12-2-00

yes

determining functional dependencies for ProAudio database

Normalization is a set of rules that ensures the proper design of a database. In theory, the higher the normal form, the stronger the design of the database.

Use the file for Functional Dependencies Above to answer the following questions:

Now that you are familiar with the mission statement and the entities and attributes the for ProAudio:

1. Identify anomalies in ProAudio- Give 3 examples means that you list specific instances from ProAudio on insert, delete or update anomalies, describing each in the context of the ProAudio database.

1. • ProAudio Anomaly example 1
   • ProAudio Anomaly example 2
   • ProAudio Anomaly example 3
2. Resolve ProAudio anomalies- citing the anomalies you described in the previous section- specifically say how to fix each one
3. • Fix 1
   • Fix 2
   • Fix 3

Answer 1

ProAudio Anomaly example 1

Consider a new item is available and admin need to put the details of new item to the database. The new item is not ordered or shipped by any customer. And the database will not allow null values for c_id. This will cause an insertion anomaly because admin is not able to add the new item to the database in this condition.

ProAudio Anomaly example 2

If the admin want to remove a specific customer from the database it may remove the details of some of items which was ordered by only that customer. Consider in the given table, if admin removes the details of c-id = 2, then it causes the deletion of item with item_id= 21-12 because no other rows with this item data in the table. This is called deletion anomaly.

ProAudio Anomaly example 3

If there is an update in zipcode for the city Ely, the admin need to update all the rows which contain this city. If there is a chance of not editing any of the row, this will lead to data mismatch or data inconsistency. This is called update anomaly.

For fixing the anomalies, we need to normalize the table according to the functional dependencies as shown below

Address(city, state, zip)

primary key: zip

Customer( c_id, f_name, l_name, address, zip)

primary key: c_id

Foreign key: zip references primary key of Address

Item( item_id, title, price)

primary key: item_id

Order(ord_no, c_id, order_Date)

primary key: ord_no

Foreign key: c_id references primary key of Customer

Shipment(ord_no, item_id, shipped)

primary key: ord_no+item_id

Foreign key: item_id references primary key of Item, ord_no references primary key of Order

The example data can arrange as follows;

Address

zip	City	State
1111	Ely	NV

Customer

c_id	f_name	l_name	address	zip
01	Jane	Doe	123 Elm st	1111
01	Fred	Fish	321 Oak st	1111

Item

item_id	title	price
12-31	More Blues	8.99
21-12	Jazz Songs	9.99
12-21	The Blues	8.99

Order

ord-no	c_id	order_Date
1-1	01	12-02-00
2-1	02	11-09-00
1-2	01	12-02-00

Shipment

ord-no	item_id	shipped
1-1	12-31	no
2-1	21-12	yes
1-2	12-21	yes

Fix 1

Now the admin can easily insert new item details to the table Item.

Fix 2

Now the admin can remove the details of a customer from Customer table, and it will not affect any item details.

Fix 3

Now the updating of zip is easy and need to update only one row of the Address table.