Questions

1. Which of the following relation instances satisfies the functional dependency (name → price)? Briefly justify your answer.
   

   Relation Instance A

   code	name	price
   1001	flower	8.50
   1002	clothes	29.99
   1003	pizza	3.00
   1004	clothes	39.99
   1005	glasses	39.99
   1006	water	1.29

   
   

   Relation Instance B

   code	name	price
   1001	pizza	7.50
   1002	clothes	39.99
   1003	flower	12.50
   1004	clothes	39.99
   1005	water	1.29
   1006	glasses	39.99

   
   

   Relation Instance C

   code	name	price
   (empty relation)

2. 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 functional dependencies that should hold on relation R: F = {A → D, BC → E, CD → A }.
   1. List all the candidate keys of R.
   2. R is not in Boyce-Codd normal form (BCNF). Why?
   3. Find a lossless join decomposition to dcompose R into a collection of relations such that each decomposed relation is in BCNF.
   4. Is R in Third normal form (3NF)? Briefly justify your answer.
3. Define a view called "LatestOrders" for Food sharing Program that provides the result to show that how many orders were placed by each member this month, no matter how many days past this month.
   The view should show the account number and contact name of each member, and the number of orders placed by the member this month. (Note: "to_char(current_date, 'yyyy-mm')" will provide the current month information.)
   If a member never placed any order this month, the count should be 0.
4. Suppose the DBA (database administrator) of the Food Sharing Program (used in Assignment 1 to 8) decided to create an Oracle account with the username projacc as a dedicated account to just handle the place_order/unsubscribe activities described in Assignment 7. That is, the username projacc 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 projacc 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 csci server, in the directory that holds all the files you want to submit, execute the following command:
  ~liuh/bin/submit 370 A9 .
  This submit script currently accepts .txt, .pdf and .md files. If your file has other extension names, please ask me to update the submit list before you submit.
  You can use the following command to check what you have submitted to A9:
  ~liuh/bin/checksubmit 370 A9