Normalization Health History Report

Added on - 25 Sep 2019

  • 4

    pages

  • 534

    words

  • 316

    views

  • 0

    downloads

Showing pages 2 of 4
Part 1The normalization of the given entities under the health History Report in 1st, 2ndand 3rdNormalForms 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)