Question

4. Consider the following Invoice table (relation) definition and sample data (Note: one invoice can only have one customer, and may have several products; Quantity is the quantity ordered by a customer in an order): InvoiceNo CustomerNo CustomerName ProductID ProductPrice Quantity 1057 30213 Rose P10 2 $29.99 1057 30213 Rose P20 $15.00 1057 30213 Rose P30 $23.00 1058 28914 Tom P10 $29.99 1058 28914 Tom $29.99 P40 1059 3 30213 $29.99 Rose PS0 Answer questions: a. Give one example for the deletion anomaly and one example for the insertion anomaly. (4 points) b. Give the all partial dependencies and transitive dependencies. (6 points) c. Normalize this relation into 2NF, Please draw the table(s) with data, and use underline to indicate the primary key and squiggly line to indicate foreign keys if a table has for each table. (8 points) underline to indicate the d. Normalize the 2NF table(s) into 3NF. Please draw the table(s) with data, and use primary key and squiggly line to indicate foreign keys if a table has for each table. (4 points)

a: Give an example of a deletion anomaly and one example for the insertion anomaly

.....

b: Give the all partial dependencies and transitive dependencies

>>>

c: Normalize this relation into 2NF. Please draw the table(s) with data, and use underline to indicate the primary key and squiggly line to indicate foreign keys if a table has for each table

<<<<<

d: Normalize the 2NF into 3NF. Please draw the table(s) with data, and use underline to indicate the primary key and squiggly line to indicate foreign keys if a table has for each table

Answer 1

a:

deletion anomaly

If a product is no more available and is deleted from the database, the corresponding invoice and customer details will also get deleted. Example : If product P50 is deleted from the table, invoice 1059 will also get deleted.

insertion anomaly

If a new customer is added to the database , but he has not purchased any product, we still have to insert invoiceNo and productId as these are primary key components. If we left them blank identity constraint is violated.

b: Full Dependency

InvoiceNo, ProductId -> Quantity

partial dependencies

InvoiceNo -> CustomerNo

ProductId -> ProductPrice

transitive dependencies

CustomerNo -> CustomerName

c: 2NF(all non key attributes should be functionally dependent on primary key)

Invoice

InvoiceNo	ProductId	Quantity
1057	P10	2
1057	P20	3
1057	P30	1
1058	P10	5
1058	P40	1
1059	P50	3

InvoiceCustomer

InvoiceNo	CustomerNo
1057	30213
1058	28914
1059	30213

Product

ProductId	ProductPrice
P10	$29.99
P20	$15.00
P30	$23.00
P40	$29.99
P50	$29.99

d: 3NF(remove transitive dependencies)

Invoice

InvoiceNo	ProductId	Quantity
1057	P10	2
1057	P20	3
1057	P30	1
1058	P10	5
1058	P40	1
1059	P50	3

InvoiceCustomer

InvoiceNo	CustomerNo
1057	30213
1058	28914
1059	30213

Product

ProductId	ProductPrice
P10	$29.99
P20	$15.00
P30	$23.00
P40	$29.99
P50	$29.99

Customer

CustomerNo	CustomerName
30213	Rose
30214	Tom

underlined are primary keys and italicised are foreign keys.

Do ask if any doubt. Please upvote.