ACCESS DB WEEK 5
squashyja
week_3_possible_solution_1.docWeek 3 Possible Solution
|
tblEmployeeHRData |
|
strEmpID (primary) |
|
strFirstName |
|
strLastName |
|
dtmHireDate |
|
sngHours |
|
curHourlyRate |
|
strHealthPlanID (foreign) |
|
strParkingID (foreign) |
|
strDeptCode (foreign) |
|
tblHealthPlan |
|
strHealthPlanID (primary) |
|
strHealthPlanDesc |
|
curHealthPlanWeeklyRate |
|
tblEmployeePersonal |
|
strEmpID (primary) |
|
strAddress |
|
strCity |
|
strState |
|
sngZip |
|
strPhone |
|
tblDept |
|
strDepartCode (primary) |
|
strDepartName |
|
tblParking |
|
strParkingID (primary) |
|
strLocation |
|
curRate |
There is a 1-to-1 relationship between tblEmployeePersonal.strEmpID and tblEmployeeHRData. Each employee has just one record in HR. A relationship between 2 primary keys is ALWAYS a 1-to-1 relationship.
There is a 1-to-many relationship between tblDepartments.strDeptCode and tblEmployeeHRData.strDeptCode. Each department has many employees. A relationship between a primary key and a foreign key is ALWAYS a 1-to-many relationship.
There is a 1-to-many relationship between tblParking.strParkingID and tblEmployeeHRData.strParkingID. Each department has many employees. A relationship between a primary key and a foreign key is ALWAYS a 1-to-many relationship.
There is a 1-to-many relationship between tblHealthPlan.strHealthPlanID and tblEmployeeHRData.strHealthPlanID. Each department has many employees. A relationship between a primary key and a foreign key is ALWAYS a 1-to-many relationship.
|
tblPets |
|
strPetID (primary) |
|
strCustID (foreign) |
|
strPetName |
|
strType (foreign) |
|
dtmDOB |
|
strGender |
|
ysnSpayed |
|
strVaccinations |
|
hlkPicture |
|
memComments |
Ex: 2:
|
tblCustomers |
|
strCustomerID (primary key) |
|
strFirstName |
|
strLastName |
|
strAddress |
|
strCity |
|
strState |
|
sngZip |
|
strPhone |
|
tblPetType |
|
strTypeID (primary) |
|
strType |
|
tblAccount |
|
strInvoice# (primary) |
|
strCustomerID (foreign) |
|
strVisitID (foreign) |
|
strPaymentType |
|
dtmDatePaid |
|
currAmtPaid |
|
ysnTaxable |
|
sngTaxRate |
|
curBalance |
|
tblVisit |
|
strVisitID (primary) |
|
strPetID (foreign) |
|
dtmLastVisit |
|
dtmFollowupvisit |
|
ysnInvoiced |
|
sngWeight |
|
sngLength |
|
strMedCode (foreign) |
|
strTreatmentCode (foreign) |
|
tblTreatment |
|
strTreatmentCode (primary) |
|
strTreatmentName |
|
curTreatmentPrice |
|
tblMedication |
|
strMedCode (primary) |
|
strMedName |
|
curMedPrice |
1-to-many
1-to-1
1-to-many
1-to-many
1-to-many
1-to-many
1-to-many
1-to-many
1-to-many
1-to-many
