Foothills Animal Hospital is a full-service small animal veterinary hospital located in Morrison, Colorado, specializing in routine medical care, vaccinations, laboratory testing, and surgery. The hospital has experienced tremendous growth over the past six months due to customer referrals. While Foothills Animal Hospital has typically kept its daily service records in a workbook format, it feels the need to expand its reporting capabilities to develop a relational database as a more functional structure.
Foothills Animal Hospital organizes its treatments using the codes displayed in Figure 1. The entities and primary keys for the database have been identified in Figure 2.
|
FIGURE 1 Treatment Codes, Treatments, and Price Descriptions |
|
FIGURE 2 Entity Names and Primary Keys Foothills Animal Hospital |
The following business rules have been identified:
1A customer can have many pets but must have at least one.
2A pet must be assigned to one and only one customer.
3A pet can have one or more treatments per visit but must have at least one.
4A pet can have one or more medications but need not have any.
Project Focus
Your job is to complete the following tasks:
Note:Use normalization to assure the correctness of the tables (relations).
1 Create the database using a personal DBMS package (Microsoft Access).
2Use the data in Pet.xlsx, HospitalCustomer.xlsx, medication.xlsx Treatments.xlsx, Visit.xlsx, and Visit Detail.xlsx to create your tables Pet, Customer, Medication, Treatment, Visit, and Visit Detail respectively.
3 Create 2 forms: Customer and Pet. Include the logo and at least 4 buttons in each form (add a record, delete a record, next, and previous).
4 Create two queries: one for treatments, and one for sales. Include the logo.
- Lastname_FirstName_Treatment query: CustomerName, PetName, TreatmentName, PriceTreatment, NumbTreatment and the formula Total Treatments: [PriceTreatment]*[NumbTreatment]
- Lastname_FirstName_Sales query: CustomerName, PetName, MedicationName, PriceMedication,NumbMedication,TreatmentName, PriceTreatment, NumbTreatment and the formula Total Sale: ([PriceTreatment]*[ NumbTreatment]) + ([PriceMedication]*[ Numb Medication])
5 Create 2 reports and save them as PDFs:
- Lastname_FirstName_Treatment query Report
- Lastname_FirstName_Sales query Report.
0 comments