Showing posts with label Normalization. Show all posts
Showing posts with label Normalization. Show all posts

Normalize the relational schema to third normal form

How to normalize a table to 3NF? / Show the steps in 3nf normalization / Normalization solved exercises / 1nf, 2fn, 3nf


Question:
Consider the relation schema Membership for a library database as follows;
Membership (MID, Name, Address, PhoneNum, ParentMID, ISBN, Title, Authors, BorrowDate, ReturnedDate, FineDue, FinePaid).
Here, ParentMID may have the values Null, Father_Name, Mother_Name or both. The following is the set F of functional dependencies that hold in Membership table;
F = { MID → Name, Address, PhoneNum, ParentMID;
(MID, ISBN, BorrowDate) → ReturnedDate, FinePaid, FineDue;
ISBN → Title, Authors}
Normalize the Membership schema to 3NF and show the steps.

Answer:
Given set of FDs;

  • FD 1: MID → Name, Address, PhoneNum, ParentMID
  • FD 2: (MID, ISBN, BorrowDate) → ReturnedDate, FinePaid, FineDue
  • FD 3: ISBN → Title, Authors

Is the table Membership in 1NF?

It is not in 1NF because of the attribute ParentMID which is a multi-valued attribute (when both parents MIDs are issued and stored for a member). To solve this we may create a separate table with MID and ParentMID attributes as follows;
Parent (MID, ParentMID)
For parent table the key is {MID, ParentMID}, hence the table is in 3NF.
After this decomposition, we have the following schemas;

  • Schema 1: Parent (MID, ParentMID)
  • Schema 2: Membership (MID, Name, Address, PhoneNum, ISBN, Title, Authors, BorrowDate, ReturnedDate, FineDue, FinePaid)
At this stage, the Membership table is in 1NF.

Is Membership in 2NF?

2NF is about eliminating partial keydependencies (if any) from a relational schema. To do this, we need to find the primary key for Membership table.
Let us find the closure of LHS attributes of given functional dependencies to check whether the LHS attributes form a key or not;
From FD1,
(MID)+ = MID, Name, Address, PhoneNum ≠ Membership
From FD2,
(MID, ISBN, BorrowDate)+ = MID, Name, Address, PhoneNum, ISBN, Title, Authors, BorrowDate, ReturnedDate, FineDue, FinePaid = Membership
Hence, the key for Membership is a composite key (MID, ISBN, BorrowDate).
Now let us check for partial dependencies;

  • The attributes MID, Name, Address, and PhoneNum can be determined by using MID alone.
  • The attributes ISBN, Title, and Authors can be determined by using ISBN alone. [from FD3].
So, the schema shows partial functional dependencies;
We shall break schema 2 to convert into a set of 2NF schemas;
Schema 2a: (MID, Name, Address, PhoneNum)
Schema 2b: (ISBN, Title, Authors)
Schema 2c: (MID, ISBN, BorrowDate, ReturnedDate, FineDue, FinePaid)
Is schema 2a in 2NF? – Yes. MID is the key and no partial key dependency.
Is schema 2b in 2NF? – Yes. ISBN is the key and no partial dependencies.
Is schema 2c in 2NF? – Yes. (MID, ISBN, BorrowDate) is the composite key and no partial dependencies.

Are schema 1, 2a, 2b and 2c are in 3NF?

As per the given set of functional dependencies, we do not have any transitive functional dependencies in any of these tables. Hence, the list of 3NF tables is as follows;

  • Schema 1: Parent (MID, ParentMID)
  • Schema 2a: (MID, Name, Address, PhoneNum)
  • Schema 2b: (ISBN, Title, Authors)
  • Schema 2c: (MID, ISBN, BorrowDate, ReturnedDate, FineDue, FinePaid)




 


Identify the anomalies that are present in the given table

Finding anomalies that are present in a given relation table, Solved exercise for finding the anomalies like insertion, deletion and modifcation, Fix the given table by eliminating the anomalies


Question:
Consider the relation Treatment with the schema Treatment (doctorID, doctorName, patientID, diagnosis) and functional dependencies;
doctorID doctorName and
(doctorID, patientID) diagnosis.
Describe different types of anomaly that can arise for this table with example records.

Answer:
Example:
doctorID
doctorName
patientID
diagnosis
D001
Mohan
PAT123
Fever
D002
Vijay
PAT110
Alergy
D003
Jenifer
PAT112
Fever
D002
Vijay
PAT121
Cold

TREATMENT has two FDs. From the FDs, we can derive that the combination (doctorID, patientID) is the primary key for TREATMENT.

Anomalies:

Insertion anomaly: The inability to store certain attributes’ values without the other attributes. If we are not able to insert a record into a relational table because of the absence of values for other attributes is called insertion anomaly.

For Treatment, we cannot insert the doctor information like doctorID and doctorName without any patient. That is, we need at least one patient to include the doctor information into the table. For example, if one more doctor Dr.Neeraj is appointed, we need to allocate at least one patient to insert Dr.Neeraj’s information. This inability is insertion anomaly.

Deletion anomaly: Deletion of values of certain attributes from any records (rows) will lead to lose of other attributes’ values of the same records. That means, we lose the complete information about an entity if we delete few values. This is called as deletion anomaly.

Deleting patients’ diagnosis could delete the name of their doctor. For example, Dr.Mohan has only one patient PAT123 registered for him. If we delete the patient PAT123, we need to delete Dr.Mohan’s details as well. This is the deletion anomaly present in the TREATMENT table.

Modification anomaly: Modification of certain values in a table may lead to change the values in more than one record if that particular value has duplicates. If we miss any one occurrence of that data, that leads to inconsistency of the table. This is called as modification anomaly.

A doctor may have more than one patient, so an update anomaly may result if a doctor’s name is changed for a given doctorID for only one patient. For example, in our table TREATMENT Dr.Vijay has two patients. Suppose that we need to change the doctor name from Vijay to Vijay Kumar. This change has to be done on two records. What if we change with second record and not changing with fourth record? If we do such thing, we are not able to find the exact name of the doctor of doctor ID D002. This stage is called as modification anomaly.


*******************







Find the key of a given relation R

How to find the key of a relation R?, Easy way to find the candidate key of a table




Question:
7. Consider a relation R with set of functional dependencies F as follows; {A B, C D, AC E, D F}. How many keys does R have and what are they?

Solution:
Closure:
In simple terms, if you know an attribute (or set of attributes) in a relation R, then what other attribute (or set of attributes) you would determine uniquely is called the closure. We normally find the closure of left hand side (LHS) attributes of the functional dependencies of relation R. Closure is used to find the candidate keys of the relation. Refer here to know more about attribute closure.

If we find the closure of all the left hand side attributes of all the FDs given, we would find the keys of R. If the closure includes all the attributes of R then that closure is one of the keys of R.
Let us find the closure of A, i.e., A+;
Result = A
Result = AB from A B (if you know, then you know B uniquely)
We don’t have any other FDs that have either A or B or both on the left hand side. Hence, our algorithm stops here and the result is AB.
The closure of A is AB. (not a key because A+ does not contain all the attributes of R)
Let us find the closure of C, i.e., C+;
Result = C
Result = CD from C D
Result = CDF from D F
We don’t have any other FDs that have either C or D or F or any combinations on the left hand side. Hence, our algorithm stops here and the result is CDF.
The closure of C is CDF. (not a key because C+ does not contain all the attributes of R)
Let us find the closure of AC, i.e, (AC)+;
Result = AC
Result = ABC from A B
Result = ABCD from C D
Result = ABCDF from D F
Result = ABCDEF from AC E
The closure of AC is ABCDEF which is R. Hence, AC is the only key of R.

**********************


Similar topics

How to find closure of set of functional dependencies?

How to find closure of attributes?

How to find canonical cover for a set of functional dependencies?

How to find extraneous attribute?




Go back to Question/QUIZ page













Popular Posts