Database Design & Development NI-Pet health care for domestic pets in Northern Ireland

Part 1. Entity-Relationship modeling

A veterinary practice called NI-Pet provides health care for domestic pets in Northern Ireland. You have been approached by its director for the design and implementation of a relational database system that will assist in the efficient running of the practice and improve resource management and information sharing within the practice. The requirement collection and analysis phase of the database design process provided the following requirements:

The practice has many clinics located in the main cities of the country. Information stored on each clinic include the clinic number, clinic address (consisting of the street, city, and postcode), and the telephone and fax numbers. Each clinic has a number of staff (for example, vets, nurses, secretaries, cleaners). The clinic number is unique throughout the practice. A staff member is allocated to one clinic and may or may not have an employment history or dependents. Some more experienced staff may act as a mentor to a number of staff. Each mentor is assigned no more than five mentees and each mentee is assigned to only one mentor. Information stored on each member of staff include the staff number, name (first and last), address (street, city, and postcode), telephone numbers, date of birth, sex, national insurance number (NIN), position, qualifications, employment history, and current annual salary. The staff number is unique throughout the practice.

When a pet owner first contacts a clinic, the details of the pet owner are recorded, which include an owner number, owner name (first name and last name), address (street, city, and postcode), home telephone number, and email address. The owner number is unique to a particular clinic. Information stored on each pet include a pet number, pet name, type of pet, description, date of birth (if unknown, an approximate date is recorded), date registered at clinic, current status (alive or deceased), and the details of the pet owner. The pet number is unique to a particular clinic.

When a sick pet is brought to a clinic, the vet on duty examines the pet and information stored on each examination include an examination number, the date and time of the examination, the name of the vet, the pet number, pet name, and type of pet, and a full description of the examination results. The examination number is unique to a particular clinic. As a result of the examination, the vet may propose treatment(s) for the pet.

The practice provides various treatments for all types of pets. These treatments are provided at a standard rate across all clinics. The details of each treatment include a treatment number, a full description of the treatment, and the cost to the pet owner. The treatment number uniquely identifies each type of treatment and is used by all clinics.

Based on the results of the examination of a sick pet, the vet may propose one or more types of treatment. For each type of treatment, the information recorded includes the examination number and date, the pet number, name and type, treatment number, description, quantity of each type of treatment, and date the treatment is to begin and end. Any additional comments on the provision of each type of treatment are also recorded.

In some cases, it’s necessary for a sick pet to be admitted to the clinic. Each clinic has 15–25 animal pens, each capable of holding between one and four pets. Each pen has a unique pen number, capacity, and availability status. The sick pet is allocated to a pen and the details of the pet, any treatment(s) required by the pet, and any additional comments about the care of the pet are recorded. The details of the pet’s stay in the pen are also noted, which include a pen number, and the date the pet was put into and taken out of the pen. Depending on the pet’s illness, there may be more than one pet in a pen at the same time. The pen number is unique to a particular clinic.

The pet owner is responsible for the cost of the treatment given to a pet. The owner is invoiced for the treatment arising from each examination, and the details recorded on the invoice include the invoice number, invoice date, owner number, owner name and full address, pet number, pet name, and the details of the treatment given. The invoice provides the cost for each type of treatment and the total cost of all treatments given to the pet. Additional data is also recorded on the payment of the invoice, including the date the invoice was paid and the method of payment (for example, cheque, cash, visa). The invoice number is unique throughout the practice.

If the pet requires to be seen by the vet at a later date, the owner and pet are given an appointment. The details of an appointment are recorded and include an appointment number, owner number, owner name (first name and last name), home telephone number, the pet number, pet name, type of pet, and the appointment date and time. The appointment number is unique to a particular clinic.

- Create an Entity–Relationship (ER) diagram, which models the data requirements for this NI-Pet practice case study.
Note: You should identify on your diagram the entity types and their attributes including primary keys, alternate keys (if any), composite and multi-valued attributes (if any).
You should also identify the relationship types and their multiplicities (cardinalities and participation). The attributes associated with relationship types should also be identified (if any).

Part 2. Database schema design and implementation

1. 1. You are required to derive the relational database schema from the ER model created in Part 
1. Where appropriate, identify the primary key, alternate and foreign key(s) for each relation.
2. Note: use the following notation to describe your relational schema, as shown in the example below:
3. TableName (Attribute1, Attribute2, Attribute3, Attribute4, Attribute5, Attribute6, Attribute7, ...etc) PRIMARY KEY Attribute1 (or in case of a composite primary key: e.g PRIMARY KEY Attribute1, Attribute3) ALTERNATE KEY Attribute4 ALTERNATE KEY Attribute5, Attribute6 (in case of composite key) FOREIGN KEY Attribute7 REFERENCES TableName2 (AttributeNameInHomeTable)
2. Create the relational database schema (set of tables) for the NI-Pet database in Access 2016 using SQL DDL ONLY. Ensure that referential integrity is established between related tables. Where appropriate set attribute and table properties, including primary key, foreign key(s), alternate key(s), required/not required attributes.
3. NOTE: NO MARKS will be awarded for tables defined and created manually using the Graphical User Interface of Access 2016 (you MUST use only SQL DDL statements to define and create your tables).
1. Populate the tables using 25 records per table.
2. Note: You have the option to populate each table by using SQL code OR alternatively by using the graphical user interface of Access (i.e. manually entering data using Datasheet View in Access 2016).
4. Create and save the following queries in SQL:
1. List the name, position and salary of all staff who work in Derry in alphabetical order of last name.
2. How many dogs registered in Belfast?
3. List the historic details of examinations for a cat called ‘Benito’ and owned by ‘John Smith’.
4. List the maximum, minimum, and average cost for treatments.
5. List the staff number and name of all staff earning the highest salary.
6. List the names of staff who have the second highest salary.

Get Project Solution by contacting us

- via WhatsApp: +92-324-7042178
- via email: codelogixstudio@gmail.com