Database Normalization, ERD and Relational Database Design

This assignment focuses on database normalization and Entity-Relationship Diagram (ERD) design. The solution begins by normalizing a 'PET' relation to 1st, 2nd, and 3rd Normal Forms (1NF, 2NF, and 3NF), addressing partial and transitive dependencies. The solution identifies and resolves these dependencies, leading to a well-structured database design. Part 2 involves creating a Logical ERD. Part 3 extends the normalization process to an 'INVOICE' relation, again progressing through 1NF, 2NF, and 3NF, with a focus on decomposing the relation to eliminate redundancies and dependencies. Part 4 presents the resulting Logical ERD for the 'INVOICE' relation. The assignment demonstrates a practical understanding of database normalization principles and ERD creation for efficient data management and relational database design.

Part 1
The normalization of the given entities under the health History Report in 1st, 2nd and 3rd Normal
Forms are as follows.
1NF:
PET (PET ID,VISIT ID, OWNER ID, PET NAME, PET TYPE, PET AGE , OWNER, VISIT
DATE, 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 functionally
dependent on OWNER ID. So, relation has partial dependency. Relation and requires
decomposition 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 VISIT
ID. Similarly, PROCEDURE functional dependent on PET ID and further PET ID dependent on
VISIT ID. Thus, relation has transitive dependency. Removal this transitive dependency makes
the 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 2
The Logical ERD is constructed as follows.
Part 3
1NF:
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 functionally
dependent on PET ID. Hence there are partial dependency. Decomposition is used to make the
relation in 2NF.
2NF:
INVOICE (INVOICE ID, DATE, OWNER ID, PET ID, VISIT ID)
OWNER (OWNER ID, OWNER, OWNER ADDRESS, OWNER ZIP)

VISIT (VISIT ID, OWNER ID, PET ID, PROCEDURE, AMOUNT)
PET (PET ID, PET NAME, PET AGE)
In above relations OWNER has transitive dependency as OWNER ZIP functionally dependent
on ADDRESS and ADDRESS is functionally dependent on OWNER ID. So OWNER relation is
further decomposed to make relation in 3NF.
3NF:
INVOICE (INVOICE ID, DATE, VISIT ID)
OWNER (OWNER ID, OWNER, OWNER ADDRESS)
ADDRESS (OWNER ID, ZIP)
VISIT (VISIT ID, PET ID, OWNER ID, PROCEDURE ID)
PROCEDURE (PROCEDURE ID, AMOUNT)
PET (PET ID, PET NAME, PET AGE)

Part 4
Logical ERD is as follows.