1. 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.

 

 

1. Consider the following table schema for accounts in a banking system:

Accounts(acct#, owner_id, owner_name, acct_type, balance, interest_rate, time_opened)

It has the following functional dependencies F = {acct# → own_id acct_type balance time_opened, owner_id → owner_name, acct_type balance → interest_rate}.

(1) (5 points) Explain why the schema is not in BCNF.

(2) (14 points) Use Algorithm lossless-join-decomposition BCNF (LLJD-BCNF) to decompose it. Show the steps.

2

 

 

(3) (5 points) Is your decomposition dependency-preserving? Justify your answer.

 

1. (10 points) Disprove the following rule:

 

{B → CD, AB → E, E → C} |= {AE → CD}

To disprove a rule, construct a relation with attributes (A, B, C, D, E) and some tuples such that the tuples of the relation satisfy the functional dependencies on the left-hand side of the rule (left of |=) but do not satisfy the functional dependency on the right-hand side of the rule.