Name Part I Using The Sample Report Below

Name Part Iusing The Sample Report Below Pu

Part I: Using the sample report below, put the data elements in the report into their normal forms using relational notation (relational notation was first shown in your textbook on page 38). Just to be clear, I want to see (using relational notation) what this report looks like in 1NF, 2NF, and 3NF. Sample Report Data Source:

HEALTH HISTORY REPORT

  • PET ID
  • PET NAME
  • PET TYPE
  • PET AGE
  • OWNER
  • VISIT DATE
  • PROCEDURE

Sample Data:

  • 246 ROVER DOG 12 SAM COOK JAN 13/ - RABIES VACCINATION
  • MAR 27/ - EXAMINE and TREAT WOUND
  • APR 02/ - HEART WORM TEST
  • 298 SPOT DOG 2 TERRY KIM JAN 21/ - TETANUS VACCINATION
  • MAR 10/ - HEART WORM TEST
  • 341 MORRIS CAT 4 SAM COOK JAN 23/ - RABIES VACCINATION
  • JAN 13/ - RABIES VACCINATION
  • 519 TWEEDY BIRD 2 TERRY KIM APR 30/ - ANNUAL CHECK UP
  • APR 30/ - EYE WASH

List the relational notation for each normal form (e.g. Pet (abc, def, ghi): 1NF: 2NF: 3NF:)

Paper For Above instruction

The transformation of the provided health history report data into relational databases entails a systematic normalization process, progressing through First Normal Form (1NF), Second Normal Form (2NF), and Third Normal Form (3NF). This process seeks to eliminate redundancies, prevent anomalies, and ensure data integrity within the database design. Each step involves identifying appropriate entities and dependencies, ultimately culminating in a well-structured relational schema suitable for efficient data storage and retrieval.

First Normal Form (1NF)

In 1NF, the primary goal is to ensure that all table attributes contain atomic (indivisible) values and that there are no repeating groups. The raw data provided displays a notable violation of 1NF due to multiple procedures listed for individual visits within a single row or record, and the presence of multi-valued fields such as procedures and dates.

To convert this data into 1NF, we decompose the data into a tabular format where each record represents a single visit for a pet, with atomic attributes.

Relational notation for 1NF:

  • Visits(PetID, PetName, PetType, PetAge, Owner, VisitDate, Procedure)

Each row now uniquely represents a pet’s visit with a single procedure. However, the table still might contain duplicate data for pet attributes and procedures, which are not yet normalized but conform to the atomicity requirement.

Second Normal Form (2NF)

To achieve 2NF, the schema must be in 1NF, and all non-key attributes must depend entirely on the primary key. The primary key in the Visit table is a combination of PetID and VisitDate, assuming that each visit is uniquely identified by the Pet and date.

However, in this schema, certain attributes such as PetName, PetType, PetAge, and Owner describe the pet, which can have multiple visit records. Hence, redundancy exists because pet information repeats multiple times.

Therefore, we decompose into separate entities:

  • Pets(PetID, PetName, PetType, PetAge, Owner)
  • Visits(PetID, VisitDate, Procedure)

Relational notation for 2NF:

  • Pets(PetID, PetName, PetType, PetAge, Owner)
  • Visits(PetID, VisitDate, Procedure)

The Pets table contains information solely dependent on PetID, and Visits records link each visit to the pet via PetID.

Third Normal Form (3NF)

Achieving 3NF requires that the schema be in 2NF, and all attributes should be non-transitively dependent on the primary key. In this context, attributes like PetType or Owner might be further normalized if they are dependent on other attributes, such as Owner potentially being an entity with contact details.

If Owner information were expanded to include contact details, it would be advisable to create a separate Owners table to normalize dependency and avoid redundancy.

  • Pets(PetID, PetName, PetType, PetAge, OwnerID)
  • Owners(OwnerID, OwnerName, ContactInfo)
  • Visits(PetID, VisitDate, Procedure)

Relational notation for 3NF:

  • Pets(PetID, PetName, PetType, PetAge, OwnerID)
  • Owners(OwnerID, OwnerName, ContactInfo)
  • Visits(PetID, VisitDate, Procedure)

This structure minimizes redundancy, maintains data integrity, and allows for flexible data management.

Summary

In summary, the normalization process converts the raw health report data into relational tables that are free of multi-valued attributes, partial dependencies, and transitive dependencies. This ensures a robust database architecture aligned with relational database design principles, facilitating efficient data operations and integrity.

References

Related Assignments