Question:
Primary and Foreign key fields
1. How would you divide these fields in order to avoid redundancy? Lay out the different tables that you would propose creating, with the fields that would be in each of the tables. Be sure to use the Leszynski Naming Convention for all object names.
2. Which fields would be defined as the Primary and Foreign key fields in each table?
3. What type of relationship would you create between the tables? Which fields would be joined to which other fields?
Question-1
Situation
You want to keep track of the following employee information:
• Name
• ID#
• Address
• City
• State
• ZIP
• Home phone
• Hire date
• Department code
• Number of hours they work
• Hourly pay rate
• Health Plan (1 = Insurance, 2 = Insurance with Dental, 3 = Single HMO, and 4 = Family HMO)
• Weekly cost of health plan
• Parking lot to which the employee is assigned
• Parking lot rate
Question -2
Situation
You work in a Veterinarian's office and need to organize the data on pets that you treat. In your database, you want to keep track of the following information:
Customer information:
• Name
• Address
• City
• State
• ZIP
• Phone #
• Year of first visit
• Date of last visit
• Discount
• Outstanding balance
Pet information:
• Name
• Type of animal
• Breed
• Date of birth
• Gender
• Neutered/Spayed?
• Length of animal
• Weight of animal
• Last visit date
• Current vaccinations
• Picture of animal
• Comments on animal
• Date of visit
• Follow-up visit date
• Total amount billed for visit
• Payment type
• Date paid
• Amount paid
• Taxable?
• Tax Rate
• Invoiced?
Details on the individual visits:
• Type of visit
• Treatment code
• Medication code and price
Details on all medications and visits:
• Medication name
• Medication price
• Treatment name
• Treatment price