For all of your assignments in the class this summer, you will be working on a database
Question:
For all of your assignments in the class this summer, you will be working on a database for a local veterinary clinic. The focus on the clinic is small animals (pets) and will not provide services for large animals (livestock). The clinic has hired you to create a small database that will allow them to handle their customer database and document their examination and treatment procedures. You will implement the very basic functionality of the database with the option to add more functionality as the vet clinic grows.
After meeting with the office manager, you have been provided with the following information that the clinic will collect about each customer, their pets, and each examination. Starting off, they just want something that can collect the basic data and do some basic searching and reporting.
For the first part of the assignment, you are to create the following tables in Access with the fields requested by the customer.
Customer Table with the following data:
• Customer ID
• Customer’s first name
• Customer’s last name
• Customer’s mailing address
• Customer’s city
• Customer’s state
• Customer’s zip code
• Customer’s home phone
• Customer’s cell phone
Pet Table with the following data:
• Pet ID
• Pet’s name
• Pet species (cat, dog, etc.)
• Pet breed (boxer, poodle, etc.)
• Pet Color • Pet Birthday
• Pet Neutered/Spayed (Yes or No)
• Owner’s ID
Employee Info
• Employee ID
• Employee first name
• Employee last name
• Position (vet, vet tech, etc.)
Visit Table
• Visit ID
• Pet ID
• Date of visit
• Diagnosis
• Treatment instructions (info for owner)
• Prescription instructions (info for owner, dosage, when to administer, etc.)
Visit to Employee Join Table (used to allow many people to work on many visits)
• Visit Employee Join ID
• Visit Id
• Employee ID
For each of these tables, you need to define a primary key and you will choose the appropriate data type and format for each field in the table. For example, should cell phone be a number field or a text field? Can you format it in a specific way to make it look like a phone number? What about those date fields? Also, be aware of that spayed/neutered option…there’s a data type for that!
In each of the fields of your table, I want you to BRIEFLY explain why you chose the data type and format you did. Use the description field in the table design area.
Once your tables are complete, add all of your tables to the relationship tool and establish the appropriate relationships that match pets to owners and match the employees to the visit. Remember, this one is a little tricky because many employees can work on one visit and employees will work on many visits. That’s what that join table is for!
The last thing for this assignment…..create some data with the following requirements.
• Add at least 10 customers to the database. Have at least two customers that live in a different city and at least one customer that has a mailing address in a different state.
• All customers have at least one pet.
• At least one customer has both a cat and a dog.
• At least one customer has three dogs…a boxer, a poodle, and a great day.
• The ages, breeds, birthdates, etc. of all of the animals should be varied. In other words, I don’t want mostly Chihuahuas all born on 12/1/2013.
• There should be at least one visit on record for each pet.
• Two pets have visited three times each. Each visit was at least 2 months apart.
• Each visit has at least one vet tech assigned to the visit. At least 8 visits also required a vet.
• The clinic employs two vets and six vet techs which should all be on the employee table.
Financial Accounting Tools for Business Decision Making
ISBN: 978-1119368458
7th Canadian edition
Authors: Paul D. Kimmel, Jerry J. Weygandt, Donald E. Kieso, Barbara Trenholm, Wayne Irvine