CSCI 5931A
Advanced Database Developement
Fall 1998
Suggested Solution To Project #2

(1)     For example (please report any error as I have not double checked):
 
 
Persons(PersonID, PersonLastName, PersonMiddleInitial, PersonFirstName, PersonStreet, PersonCity, PersonState, PersonZipCode, PersonEmailAddress, PersonPhoneNumber) 

Candidate keys: {PersonID} 
Foreign keys: None
Staff(StaffPersonID, StaffSocialSecurityNumber)
Candidate keys: 
  • StaffID
  • StaffSocialSecurityNumber
Foreign keys:
  • StaffPersonID is a foreign key of Persons(PersonID).
Administrator(AdministratorStaffID)
Candidate keys: AdministratorStaffID
Foreign keys:
  • AdministratorStaffID is a foreign key of Staff(StaffPersonID).
Faculty(FacultyStaffID, DepartmentID)
Candidate keys: FacultyStaffID
Foreign keys:
  • FacultyStaffID is a foreign key of Staff(StaffPersonID).
  • DepartmentID is a foreign key of Department(DepartmentID).
Clerk(ClerkStaffID)
Candidate keys: ClerkStaffID
Foreign keys:
  • ClerkStaffID is a foreign key of Staff(StaffPersonID).
Account(AccountID, Password, PersonID, AccountType)
Candidate keys:
  • AccountID
  • PersonID
Foreign keys:
  • PersonID is a foreign key of Persons(PersonID).
  • AccountType is a foreign key of AccountType(AccountType).
AccountType(AccountType, TypeDescription)
Candidate keys:
  • AccountType
  • TypeDescription
Foreign keys:  None.
Students(StudentID, StudentWorkCompany, StudentCompanyStreet, StudentCompanyStreet, StudentCompanyState, StudentCompanyZipCode, StudentCompanyPhone)
Candidate keys: 
  • StudentID
Foreign keys:
  • StudentID is a foreign key of Persons(PersonID).
Courses(CourseNumber, CourseCredit, CourseTitle, CourseDescription, CourseHour, EFID)
Candidate keys:
  • CourseNumber
Foreign keys: 
  • EFID is a foreign key of EvaluationForm(EFID).
CoursePrerequisites(CourseNumber, RequiredCourseNumber)
Candidate keys: CourseNumber, RequiredCourseNumber
Foreign keys:
  • CourseNumber is a foreign key of Courses(CourseNumber).
  • RequiredCourseNumber is a foreign key of Courses(CourseNumber).
Department(DepartmentID, DepartmentName, ChairFacultyStaffID)
Candidate keys: 
  • DepartmentID
  • DepartmentName
Foreign keys:
  • ChairFacultyStaffID is a foreign key of Faculty(ChairFacultyStaffID).
Certificates(CertificateID, CertificateName, CertificateDescription, DepartmentID)
Candidate keys:
  • CertificateID
  • CertificateName
Foreign keys:
  • DepartmentID is a foreign key of Department(DepartmentID).
CertificateCourse(CertificateID, CourseNumber)
Candidate keys: CertificateID, CourseNumber
Foreign keys:
  • CourseNumber is a foreign key of Courses(CourseNumber).
  • CertificateID is a foreign key of Certificates(CertificateID).
CertificateEnrollment(CertificateID, StudentID)
Candidate keys: CertificateID, StudentID
Foreign keys:
  • StudentID is a foreign key of Students(StudentID).
  • CertificateID is a foreign key of Certificates(CertificateID).
TransferredCourses(TransferredCourseID, TCInstitute, TCNumber, TCName, TCDate, StudentID, CourseNumber)
Candidate keys:
  • TransferredCourseID
Foreign keys:
  • StudentID is a foreign key of Students(StudentID).
  • CourseNumber is a foreign key of Courses(CourseNumber).
Enrollment(EnrollmentID, Tuition, StudentID, CourseOfferingID, DiscountTypeID, Grade, RegistrationID)
Candidate keys: EnrollmentID
Foreign keys:
  • StudentID is a foreign key of Students(StudentID).
  • Grade is a foreign key of Grades(Grade).
  • CourseOfferingID is a foreign key of CourseOffering(CourseOfferingID).
  • RegistrationID is a foreign key of Registration(RegistrationID).
  • DiscountType is a foreign key of DiscountType(DiscountTypeID).
Registration(RegistrationID, , RegistrationDate, RegistrationTotalAmount, DiscountType, ClerkStaffID, Payment)
Candidate keys: RegistrationID
Foreign keys:
  • DiscountType is a foreign key of DiscountType(DiscountTypeID).
  • ClerkStaffID is a foreign key of Clerk(ClerkStaffID).
  • PaymentID is a foreign key of Payment(PaymentID).
Payment(PaymentID, Amount, ReceiptNumber, Date)
Candidate keys: PaymentID
Foreign keys: none
ChequePayment(ChequePaymentID, BankName, AccountNumber, ChequeNumber)
Candidate keys: ChequePaymentID
Foreign keys:
  • ChequePaymentID is a foreign key of Payment(PaymentID).
CreditCardPayment(CreditCardPaymentID, CreditCardAccountID)
Candidate keys: CreditCardPaymentID
Foreign keys:
  • CreditCardPaymentID is a foreign key of Payment(PaymentID).
CreditCardAccount(CreditCardAccountID, HolderName, CardName, IsuingInstitution, CreditNumber, ExpirationDate, StudentID)
Candidate keys: CreditCardAccountID
Foreign keys:
  • StudentID is a foreign key of Students(StudentID).
Grades (Grade, Description)
Candidate keys: Grade
Foreign keys: none.
DiscountType(DiscountTypeID, DiscountTypeName, DiscountTypeDescription)
Candidate keys: DiscountTypeID
Foreign keys: none.
CourseOffering(CourseOfferingID, CourseNumber, FacultyID, HourlyRate)
Candidate keys: CourseOfferingID
Foreign keys:
  • CourseNumber is a foreign key of Courses(CourseNumber).
  • FacultyID is a foreign key of Faculty(FacultyID).
Notes(NoteID, Date, Description, EnrollmentID, CourseOfferingID)
Candidate keys: NoteID
Foreign keys:
  • EnrollmentID is a foreign key of Enrollment(EnrollmentID).
  • CourseOfferingID is a foreign key of CourseOffering(CourseOfferingID).
Meeting(MeetingID, MeetingDate, MeetingStartTime, MeetingEndTime, CourseOfferingID, ClassroomNumber)
Candidate keys: MeetingID
Foreign keys:
  • CourseOfferingID is a foreign key of CourseOffering(CourseOfferingID).
  • ClassroomNumber is a foreign key of Classroom.
Classroom(ClassroomNumber, ClassroomSize)
Candidate keys: ClassroomNumber
Foreign keys: none.
CourseClassroomSupport(CourseNumber, ClassroomNumber)
Candidate keys: CourseNumber, ClassroomNumber
Foreign keys:
  • CourseNumber is a foreign key of Courses(CourseNumber).
  • ClassroomNumber is a foreign key of Classroom.
EvaluationForm(EFID, EFName, EFDescription)
Candidate keys: EFID
Foreign keys: none.
QuestionSet(QuestionSetID, EFID)
Candidate keys: QuestionID
Foreign keys:
  • EFID is a foreign key of EvaluationForm(EFID).
QuestionItems(QuestionItemID, QuestionText, QuestionSetID)
Candidate keys: QuestionItemID
Foreign keys:
  • QuestionSetID is a foreign key of QuestionSetID.
Choices(ChoiceID, ChoiceValue, ChoiceDescription, QuestionSetID)
Candidate keys: ChoiceID
Foreign keys:
  • QuestionSetID is a foreign key of QuestionSetID.
Evaluation(EvaluationID, Date, EnrollmentID, Comments, EFID)
Candidate keys: EvaluationID
Foreign keys:
  • EnrollmentID is a foreign key of Enrollment(EnrollmentID).
  • EFID is a foreign key of EvaluationForm(EFID).  Note that EFID is added as a redundant derived attribute to speed up access in this design.  EFID can be found via EnrollmentID.
Answer(AnswerID, EvaluationID, QuestionItemID, ChoiceID)
Candidate keys: AnswerID
Foreign keys:
  • EvaluationID is a foreign key of Evaluation(EvaluationID).
  • QuestonItemID is a foreign key of QuestionItems(QuestionItemID).
  • ChoiceID is a foreign key of Choices(ChoiceID).