1. Solve for below relations (note that some changes have been made due to the c

Question

1. Solve for below relations (note that some changes have been made due to the creation of a single-attribute key for Classes):(Highlighted are the primary keys)
TAs(B#, level, pay_rate, office, office_hour_start_time, office_hour_end_time)
Courses(dept_code, course#, title, credits, deptname)
Classes(classid, dept_code, course#, sect#, year, semester, start_time, end_time, limit, size, room, TA_B#) /* note: classid is added to serve as a single-attribute key */
Faculty(B#, first_name, last_name, rank, office, email, phone#, deptname)
Enrollments(Student_B#, classid, lgrade, ngrade)
Do the following for each relation schema:
(a)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. 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, if a functional dependency is redundant (i.e., it can be derived from the ones you keep), it should not be included.
(b) Use functional dependencies identified in Question 1(a) to determine whether or not the schema is in 3NF or in BCNF. Justify your conclusion. You need to compute all candidate keys for each relation first.
(c) For each schema that is not in 3NF, decompose it into 3NF schemas using Algorithm LLJD-DPD-3NF. Show the result after each step of the algorithm, i.e., show the candidate keys (from Question 1(b)), show the minimal cover (Step 2), show the decomposition based on functional dependencies in the minimal cover (Step 3), and mention whether an additional schema needs to be added to the decomposition (Step 4). Don’t forget to underscore the primary key of each new relation.

Explanation / Answer

(a) Answer:

For TAs(B#, level, pay_rate, office, office_hour_start_time, office_hour_end_time) non trivial dependecy:

B# -> level, pay_rate, office, office_hour_start_time, office_hour_end_time

level->pay_rate

For Courses(dept_code, course#, title, credits, deptname) non trivial dependecy:

dept_code,course#->, title, credits, deptname

dept_code-> deptname

course# ->title, credits

For Classes(classid, dept_code, course#, sect#, year, semester, start_time, end_time, limit, size, room, TA_B#) non trivial dependecy:

classid->dept_code, course#, sect#, year, semester, start_time, end_time, limit, size, room, TA_B#

dept_code->course#
For Faculty(B#, first_name, last_name, rank, office, email, phone#, deptname)

FB#,->first_name, last_name, rank, office, email, phone#, deptname

email->first_name, last_name, phone#
For Enrollments(Student_B#, classid, lgrade, ngrade) non trivial dependecy:

Student_B#, classid->lgrade, ngrade

(b) Answer:

TAs(B#, level, pay_rate, office, office_hour_start_time, office_hour_end_time)

If a realation has dependency like key->realation in its F+ then it is said to be in BCNF as well as in 3NF,

B# -> level, pay_rate, office, office_hour_start_time, office_hour_end_time

level->pay_rate    the above condition is not satisfied with this dependency so we can decompose it as

TAs(B#, level, pay_rate, office, office_hour_start_time, office_hour_end_time)

L_P(level, pay_rate)

Courses(dept_code, course#, title, credits, deptname)
dept_code->course#, deptname the above condition is not satisfied with this dependency so we can decompose it as

course# ->title, credits the above condition is not satisfied with this dependency so we can decompose it as

Courses1(dept_code, course#, deptname) dept_code->course#, deptname

Courses2(course# ,title, credits) course# ->title, credits

Classes(classid, dept_code, course#, sect#, year, semester, start_time, end_time, limit, size, room, TA_B#)

classid->dept_code, course#, sect#, year, semester, start_time, end_time, limit, size, room, TA_B#

Classes(classid, dept_code, course#, sect#, year, semester, start_time, end_time, limit, size, room, TA_B#)

Classes1(dept_code,course#) dept_code->course#
Faculty(B#, first_name, last_name, rank, office, email, phone#, deptname)

FB#,->first_name, last_name, rank, office, email, phone#, deptname

email->first_name, last_name, phone#

(email, first_name, last_name, phone#)
Enrollments(Student_B#, classid, lgrade, ngrade) it is in 3NF

Student_B#, classid->lgrade, ngrade

(c)

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.