Appendix 1: Copies Of Manual Records Held So Far Owner Detai

Appendix 1 Copies Of Manual Records Held So Farowner Detailsfirst

Using the information from the records on page 1 and 2, design and create a relational database. Draw 3 tables—one for customer details, one for pet details, and one for vet details—with appropriate field names, data types, sizes, primary keys, foreign keys, and referential integrity rules. Explain the primary key for each entity, establish relationships and relationship rules, provide an Entity-Relationship Model, demonstrate how foreign keys are added, and justify your choices for referential integrity, including cascade update and delete rules.

Paper For Above instruction

The task of designing a relational database based on manual records involves analyzing the data to identify entities, attributes, and the relationships between them. In this case, the primary entities are Owner (or Customer), Pet, and Veterinarian (Vet). Each entity must be represented as a table with clearly defined fields, data types, and constraints. This approach ensures data consistency, integrity, and efficient retrieval of information, which are essential for managing veterinary records effectively.

Firstly, the owner (customer) details can be structured into a Customer table. The attributes include First Name, Surname, Address, and Telephone Number. The data types are primarily text (varchar) with sizes adjusted for typical name lengths and addresses. The primary key should be a unique identifier such as OwnerID, which can be an auto-incremented number or a UUID to ensure uniqueness and simplicity in referencing. Since each owner can have multiple pets, the Customer table is linked to the Pet table via a foreign key, OwnerID, establishing a one-to-many relationship.

Similarly, the Pet table contains attributes like PetID (primary key), Name, Dog/Cat (species), Breed, Last Vaccine Date, and VetID (foreign key). The data types include text for names and breed, date for vaccine dates, and integer for ID references. The foreign key VetID establishes a relationship with the Vet table, which holds vet-specific information, such as VetID (primary key), Vet’s Name, Practice Name, Address, and Telephone Number.

The Vet table serves as a lookup for veterinarian details. Its fields include VetID (primary key), Vet’s Name, Practice Name, Address, and Telephone Number. Data types are mostly text for names and addresses, with the primary key being a unique integer or UUID.

In establishing relationships, the OwnerID foreign key in the Pet table links back to the Customer table, enforcing referential integrity so that every pet record must relate to an existing owner. The VetID foreign key in the Pet table links to the Vet table, ensuring each pet’s vet information is valid. Referencing constraints such as ON UPDATE CASCADE and ON DELETE SET NULL or CASCADE can be used to maintain consistency—e.g., if a vet’s record is updated, the change cascades to related pet records, or if a vet is deleted, their reference is set to null or the associated pets are reassigned or deleted, depending on policy.

The Entity-Relationship Model visually depicts these relationships with boxes for entities and lines indicating their relationships with cardinalities. For example, a one-to-many relationship from Owner to Pet demonstrates that one owner can have multiple pets, but each pet has only one owner. Similarly, a one-to-many relationship from Vet to Pet indicates that each vet may attend multiple animals, but each animal has one primary vet.

The addition of foreign keys in the database tables is a crucial step in establishing these relationships. For example, the Pet table’s VetID foreign key references Vet.VetID, ensuring referential integrity. When implementing this, constraints are set in the database schema, specifying actions upon updates or deletions via cascade options. Cascade update ensures that changes to vet IDs propagate through related pet records, while cascade delete (if used) ensures that deleting a vet record also deletes or updates related pet records, maintaining data consistency and integrity.

Referential integrity rules are justified based on the data's logical dependencies. For example, deleting an owner would ideally cascade deletion to all pets owned by them to prevent orphaned records. Conversely, updating a vet’s ID should cascade to all associated pet records to keep data synchronized. These rules help maintain a reliable and normalized database structure, essential for accurate data retrieval and reporting in a veterinary practice.

References

• Codd, E. F. (1970). A relational model of data for large shared data banks. Communications of the ACM, 13(6), 377–387.