Assignment 3

ITC556 Tutorial-5

Section-A – Write short answers

1. What is normalization and de-normalisation?

2. List different normalisation levels.

3. What is a functional dependency in relation to normalisation process?

4. When a table is considered that it is in 1NF? Give an example.

5. When a table is considered that it is in 2NF? Give an example.

6. When a table is considered that it is in 3NF? Give an example.

7. When a table is considered that it is in BCNF? Give an example.

8. When a table is considered that it is in 4NF? Give an example.

9. What are advantages of de-normalisation?

10. Discussion question-- How would you answer: Suppose that someone tells you that an attribute that is part of a composite primary key is also a candidate key. How would you respond to that statement?

Section-B – Normalising tables

Questions

1. Following are data found in an order form. Normalize them into several tables to reduce redundancy of data. Use dependency diagrams.

CUST_NO, CUST_NAME, CUST_ADDRESS, ORDER_NO, ORDER_DATE, PART_CODE, PART_DESC, PART_QTY

CUST_NO	CUST_NAME	CUST_ADDRESS	ORDER_NO	ORDER_DATE	PART_CODE	PART_DESC	PART_QTY
111111	John	63, Oxford Street	111	10/03/2009	1111	Tyre	2
111111	John	63, Oxford Street	111	10/03/2009	1112	Hammer	1
111111	John	63, Oxford Street	112	11/03/2009	1111	Tyre	2
111111	John	63, Oxford Street	112	11/03/2009	1113	Screw driver	3
111112	Alan	23, Park Street	113	12/03/2009	1114	Nails	100
111112	Alan	23, Park Street	113	12/03/2009	1111	Tyre	4
111113	Gerald	10, Gorge Street

Draw an ERD for the normalised tables.

2. The dependency diagram in Figure Q6.8 indicates that a patient can receive many prescriptions for one or more medicines over time. Based on the dependency diagram, create a database whose tables are in at least 2NF,

Figure Q6.8 Prescription dependency diagram

3. Given the dependency diagram shown in Figure Q6.6, answer items 6a-6c:

FIGURE Q5.6 Dependency Diagram for Question 6