COLLEGE (CollegeName, ZipCode, CarnegieClass) LECTURER (LectureID, Name, Email, PhoneNumber) EXPERTISE (TopicOfExpertise, LevelOfExpertise, LecturerID) CONTRACT (ContractDate, CollegeName, LecturerID, Salary) ZIP (ZipCode, City, State) Also assume that there are frequent queries involving the following information: a. List of colleges sorted by zip code. b. Access to information on a specific lecturer—their name, email address, and phone number. c. List of lecturers sorted by their area of expertise and within each area of expertise, their expertise level. d. List of colleges sorted by their CarnegieClass and within the CarnegieClass by zip code. e. Access to information on a specific lecturer based on their email address. For each situation above, specify the types of index(es) you would recommend. Justify your decisions based on the given relational model. This model is by normal form
COLLEGE (CollegeName, ZipCode, CarnegieClass)
LECTURER (LectureID, Name, Email, PhoneNumber)
EXPERTISE (TopicOfExpertise, LevelOfExpertise, LecturerID)
CONTRACT (ContractDate, CollegeName, LecturerID, Salary)
ZIP (ZipCode, City, State)
Also assume that there are frequent queries involving the following information:
a. List of colleges sorted by zip code.
b. Access to information on a specific lecturer—their name, email address, and phone number.
c. List of lecturers sorted by their area of expertise and within each area of expertise, their expertise level.
d. List of colleges sorted by their CarnegieClass and within the CarnegieClass by zip code.
e. Access to information on a specific lecturer based on their email address.
For each situation above, specify the types of index(es) you would recommend. Justify your decisions
based on the given relational model.
This model is by normal form
Step by step
Solved in 3 steps
