CITC 1303 NSCC Modifying a Database Design and Understanding Diagrams Questions Please submit a Word document with your responses to the Extend Your Knowledge exercises on page 11-36.
1. Using the shorthand representation illustrated in this module, indicate the changes you would need to make to the CMF Vets database design shown in Figure 11–8 in order to support the following requirements: a. A given appointment may be assigned to more than one veterinarian. b. However, only one treatment is given by one veterinarian at a time.
2. Using the shorthand representation illustrated in this module, indicate the changes you would need to make to the Car First Insurers data design shown in Figure 11–10 to support the following requirements: a. Car First Insurers is expanding its business to insure recreational vehicles (RVs). b. In this expansion, the class of RV must be recorded along with any indication of ownership, loan, or rental.
3. Use the Access Relationships Report for the Financial Services database shown in Figure 11–35 to answer the following: a. Identify the foreign keys in the Client table. b. What is the purpose of the Services table? c. What are the primary keys of the Services table?
4. Financial Services has decided to add two tax advisors to the database. One tax advisor can
service many clients and clients may use different tax advisors during the year, depending on their needs. What changes would you need to make to the database design for Financial Services? NOTE: In the shorthand representation for a table containing a foreign key, you would represent the foreign key by using
the letters FK, followed by an arrow, followed by the name of the table in which that field is the primary key. For example, to
indicate that the Patient ID in the Appointments table is a foreign key that must match the primary key of the Patients table,
you would write FK Patient ID → Patients.
The shorthand representation for the tables and foreign keys is shown in
Figure 11–8. It is common to list a table containing a foreign key after the table
that contains the corresponding primary key, when possible. Thus, in the figure, the
Appointments table comes after the other tables because it contains foreign keys from
the tables listed above.
Owners (Owner ID, Last Name, First Name, Street Address, City, State, Postal Code,
Home Phone, Mobile Phone, Email Address)
Patients (Patient ID, Patient Name, Animal Type, Breed, Owner ID)
FK Owner ID Owners
Veterinarians (Veterinarian ID, Last Name, First Name, Street Address, City, State,
Postal Code, Work Phone, Mobile Phone, Email Address, Board Certified, Specialty)
Appointments (Appointment ID, Patient ID, Appointment Date, Appointment Time,
Veterinarian, Owner, Treatment Number)
FK Patient ID Patient
FK Veterinarian ID Veterinarians
In the Vehicle Driver table, the primary key consists of two fields, VN and Driver ID. Do you need both
fields to be key fields?
Yes, because this table implements the many-to-many relationship between vehicles and drivers. It is perfectly legitimate for the
table that implements a many-to-many relationship to contain only the two columns that constitute the primary key.
The shorthand representation for the tables and foreign keys is shown in Figure 11-10.
Customer Customer ID, First Name, Last Name, Address, City, State, Postal Code,
Telephone, Email Address)
Driver (Driver ID, First Name, Last Name, Address, City, State, Postal Code, Telephone,
Policy (Policy Number, Effective Date, Expire Date, Customer ID)
FK Customer ID → Customer
Vehicle (VIN, Make, Model, Year, Policy Number)
FK Policy Number Policy
Vehicle Driver (VIN, Driver ID)
FK VIN → Vehicles
FK Driver ID Driver
Purchase answer to see full