Questions

1. Given an instance (an actual table with data) of a relation R(A, B, C, D, E) where each letter in the brackets represents an attribute of relation R, and a functional dependency BC → E,
   1. write a SQL statement and explain how this SQL statement can be used to check whether BC → E holds on relation R (that is, whether the data in the instance of relation R support the claim that BC → E).
   2. suppose that BC → E does hold on relation R, then R is not in Boyce-Codd Normal Form (BCNF). Why?
   3. find a lossless join decomposition to decompose relation R into two relations R1 and R2. Prove that both R1 and R2 are in Boyce-Codd Normal Form (BCNF).
   4. write SQL statements to transfer proper data from relation R into each of the decomposed relations found in step c. The transfer process shouldn't put any redundant data to R1 or R2, and should truly be a lossless join one.
2. Given a relation R and a set of functional dependencies F, answer the following questions and justify your answers:
   1. Is it possible that this relation R doesn't have any super keys?
   2. Is it possible that this relation R has multiple candidate keys?
   3. Is it possible that this relation R has multiple primary keys?
3. Given a relation R(A, B, C, D, E) where each letter in the brackets represents an attribute of relation R, and a set of FDs F = {BC → E, AB → D, D → C, E → A},
   1. prove that {AB} is a candidate key of R;
   2. suppose one of the decomposed relations from relation R is T(BCD), prove that this relation T is not in BC normal form, but is in third normal form.
4. Suppose the DBA (database administrator) of the Forum management (used in Assignment 1 to 8) decided to create an Oracle account with the username usubmnger as a dedicated account to just handle the subscribe/unsubscribe activities described in Assignment 7. That is, the username usubmnger and its password would only be used to make connection to Oracle to execute the program implementing Assignment 7 specifications.
   Write SQL statements to grant minumum but sufficient privileges to the program's Oracle account usubmnger so that Assignment 7 program can be executed successfully.

How to Submit

You can submit your assignment solutions using one of the following two ways:

• Submit a hard copy before the due date/time.
• Login to VIU Learn, upload all your PDF and/or text solution files to A9 under CSCI 370's assessment and assignments tab.