Normalization Health History Report


Added on  2019-09-25

4 Pages534 Words4977 Views
Part 1The normalization of the given entities under the health History Report in 1st, 2nd and 3rd NormalForms are as follows.1NF:PET (PET ID,VISIT ID, OWNER ID, PET NAME, PET TYPE, PET AGE , OWNER, VISITDATE, PROCEDURE)2NF:Removal of partial dependency from the relation PET of 1NF given the relation in 2NF.Attributes PET NAME, PET AGE, PROCEDUES are functionally dependent on PET ID,attributes VISIT DATE is functionally dependent on VISIT ID, attribute OWNER is functionallydependent on OWNER ID. So, relation has partial dependency. Relation and requiresdecomposition to get the relation in 2NF.PET (PET ID, PET NAME, PET TYPE, PET AGE)VISIT (VISIT ID, PET ID, OWNER ID, OWNER, VISIT DATE, PROCEDURE)3NF:In above relation OWNER dependent upon OWNER ID and OWNER ID dependent upon VISITID. Similarly, PROCEDURE functional dependent on PET ID and further PET ID dependent onVISIT ID. Thus, relation has transitive dependency. Removal this transitive dependency makesthe relation in 3NF. The decomposition is taken to remove transitive dependency.PET (PET ID, PET NAME, PET TYPE, PET AGE)VISIT (VISIT ID, OWNER ID PET ID, VISIT DATE, PROCEDURE)PET OWNER (OWNER ID, OWNER, PET ID)

Part 2The Logical ERD is constructed as follows.PETPKPET IDPET NAMEPET TYPEPET AGEOWNER IDPET OWNERPKOWNER IDOWNERVISITPKVISIT IDOWNER IDPET IDVISIT DATEPROCEDURE1..N1..11..11..N1..11..1Part 31NF:INVOICE (INVOICE ID,OWNER ID, PET ID, VISIT ID, OWNER, OWNER ADDRESS,OWNER ZIP, DATE, AMOUNT, PET NAME, PROCEDURE, PET AGE)In above relation DATE, is functionally dependent on INVOICE ID, OWNER, OWNER NAME,OWNER ADDRESS, OWNER ZIP are functionally dependent on OWNER ID, PROCEDUE,AMOUNT are functionally dependent on VISIT ID, and PET NAME, PET AGE are functionallydependent on PET ID. Hence there are partial dependency. Decomposition is used to make therelation in 2NF.2NF:INVOICE (INVOICE ID, DATE, OWNER ID, PET ID, VISIT ID)OWNER (OWNER ID, OWNER, OWNER ADDRESS, OWNER ZIP)

End of preview

Want to access all the pages? Upload your documents or become a member.

Related Documents
Normalization & Customer Service Professional

Assignment On Customer Service Professional | Normalization Assignment

Relational Database Design Report 2022

Introduction to the ERD diagram

COIT20247 Database Design and Development Assignment

Enhanced Database Management Strategy for Multinational Tour Operator Agency