Question

(mysql)Lab 10 Database Normalization

The relation (PatientLab) below provides some sample data for a clinic office that stores patient, insurance, lab test, and lab test result information. The relation is already in first normal form (1NF). Assuming that one patient can have multiple different tests performed in one day and same test can be performed for the same patient in different dates.

Patient _ID	Name	Insurance_Code	Insurance_Name	DOB	Lab_Test_ID	Lab_Test_Name	Lab_Result	Lab_Test_Date
P001	Joe Doe	IN001	B&B	1/1/1990	L001	WBC	5	5/5/2019
P001	Joe Doe	IN001	B&B	1/1/1990	L002	RBC	5.2	5/5/2019
P001	Joe Doe	IN001	B&B	1/1/1990	L003	MCV	90	5/5/2019
P002	John Smith	IN002	Aetna	2/2/1988	L002	RBC	5.8	5/6/2019
P002	John Smith	IN002	Aetna	2/2/1988	L004	MCH	32	5/6/2019
P001	Joe Doe	IN001	B&B	1/1/1990	L001	WBC	4.2	5/7/2019

1. Provide examples to show that insertion, deletion, and modification anomalies could occur on this table.

a. Insertion anomaly

b. Deletion anomaly

c. Update anomaly

2. Identify the functional dependencies represented by the attributes shown in the above table.   State any assumptions you make about the data and the attributes.

Note: Assume that the primary key is (Patient_ID, Lab_Test_ID, Lab_Test_Date) which fully functionally determines the Lab_Result.

Patient _ID

Name

Insuranc_Code

Insurance_Name

DOB

Lab_Test_ID

Lab_Test_Name

Lab_Result

Lab_Test_Date

3. Describe and illustrate the process of normalizing the table shown above to 3NF. Identify the primary keys and foreign keys in your resulting relations after 3NF.

1NF: (Find any repeating group and process it if there is any.)

Relations after 1NF

2NF: (Find any partial dependency and process it if there is any.)

Relations after 2NF

3NF: (Find any transitive dependency and process it if there is any.)

Relations after 3NF (Show primary key and foreign key in each relation.)

Q: answer for 1,2,3 plz

Answer 1

Answer 1)

a)

Insertion anomaly is the inability of a database to add a new record to an existing database due to the absence of the other attributes.

In the given database if we want to add a new Patient detail in the table we won’t be able to add unless we have Lab test detail also. Here both Patient_ID and Lab_test_ID are combined identifier of the table hence one record cannot be added in the absence of other value.

b)

Delete anomaly in a database exist when certain attributes are lost from a table because of the deletion of other attributes.

In the given database, if we want to delete a particular Patient then all the associated Lab test detail will also get deleted.

c)

Update anomaly is a database exist when one or more instances of duplicate data is updated, but not all.

For the given database, suppose the name of a lab test ID L001 need to be changed. This can not be done easily because of the multiple occurrence of the lab test ID L001.