Binghamton University

MIS 533

1. (22 points) The following are some of the relations transformed from the ER diagram for the Student Registration System (note that some changes have been made due to the creation of a single-attribute primary key for Classes): Students(B#, first_name, last_name, level, gpa, email, bdate) Courses(dept_code, course#, title, credits, deptname) Classes(classid, dept_code, course#, sect#, year, semester, start_time, end_time, limit, size, room, Faculty_B#, TA_B#) /* note: classid is added to serve as a single-attribute primary key */ Faculty(B#, first_name, last_name, title, office, email, phone#, deptname) G_Enrollment(G_B#, classid, lgrade, score) Do the following for each relation schema, identify all non-trivial functional dependencies based on the Requirements Document (but take into consideration that classid is added as the primary key for Classes). For this question, we also make the following assumptions: (1) each dept_code corresponds to a unique department and vice versa; (2) only faculty members in the same department could share an office and a phone number; (3) each faculty office (shared or not) has one phone with a unique number. Don’t make other assumptions about the data. Use the union rule to combine the functional dependencies as much as possible to avoid having multiple functional dependencies with the same left-hand side but different right-hand side. Furthermore, try not to list redundant FDs. For example, if you already have A  BC, you don’t need to also list A  B and A  C. As another example, if you already have A  B and B  C, you don’t need to include A  C. But you are not required to eliminate all redundant FDs at this time. Answer: Students(B#, first_name, last_name, level, gpa, email, bdate) FDs: B#  first_name last_name level gpa email bdate email  B# Courses(dept_code, course#, title, credits, deptname) FDs: dept_code course#  title credits deptname dept_code  deptname deptname  dept_code course#  credits Classes(classid, dept_code, course#, sect#, year, semester, start_time, end_time, limit, size, room, Faculty_B#, TA_B#) FDs: classid  dept_code course# sect# year semester start_time end_time limit size room Faculty_B# TA_B# dept_code course# sect# year semester  classid Faculty(B#, first_name, last_name, title, office, email, phone#, deptname) FDs: B#  first_name last_name title office email phone# email  B# office  phone# deptname