## CS 430/630 Homework 6 FDs, Normalization, Access Control due Wed., May 10 in class, on paper

1. Exercise 19.3, which is solved online. Do those 2 parts and add a third part,
3. Change the original contents by changing the lower two y1 values in the Y column to y2, leaving the upper two equal to y1. List all the FDs this new instance satisfies.

2. Exercise 19.2

3. (From last term's final exam) Here is a table for classes at a college, with numbered time periods in each day:

 Name Department Room Period Java 1 CS 100 5 Java 2 CS 110 4 Data Structures CS 100 6 Calc 1 Math 350 6 Calc 2 Math 390 6 Linear Algebra Math 350 4

a.       Find a functional dependency (FD) with one attribute (other than Name) on each side that holds for this instance.

b.      Disprove the proposed FD “department period -> room”

c.       Find a single-column key and a multi-column key that hold for this instance

d.      Propose a decomposition that removes the redundancy implied by the FD you found in a. Show the smaller table in full and state what column can be dropped in the larger table compared to the original table. Hint: this is a lot like the R -> W dependency example of Figures 19.1 and 19.2 and in the slides of Lecture 20.

4. Exercise 19.10 (a. and b.) For part b., a good decomposition means it provides lossless join(s) and preserves FDs. For each case 1-4, first check if the join(s) are lossless and give up if not. If lossless, determine whether the FDs are preserved.

5. Find a BCNF decomposition of the relation of 19.10 for parts 1. and 2., using the FD sets given for each case. Analyze the BCNF decomposition for unpreserved dependencies. If you find a 3NF decomposition along the way, report it. Note that the first step is to find the keys, so you can test FDs for as described on pp. 616-618. When you have a choice as to which non-BCNF FD to work on (in part 2), choose the first such FD and use it for a decomposition, then later come back and determine what happens if you choose the second one.

6. (CS630 only) Check that the FD sets of 19.10 parts 1-2 are in fact minimal.

Sample Proof of Minimality: Show that (1) AC->E, (2) E->D, (3) A->B is minimal for R=ABCDE (Example at top of pg. 627)

1. Put in standard form: already that way.

2. Try to reduce LHS's:
Try (1') A->E instead of AC->E: Now A+ = AE by (1'), = AED by (2) = AEDB by (3), but previously A+ = AB by (3), so different
Try (1') C->E instead of AC->E: Now C+ = CE by (1'), = CED by (2), but previously C+ = C, so different

3. Try to drop FDs:
Drop (1), leaving (2) and (3). Compute AC+ = ACB by (3). Previously, AC+ = ACE by (1), = ACED by (2) = ABCDE by (3), different, so can't drop (1)
Drop (2), leaving (1) and (3). Compute E+ = E. Previously, E+ = ED by (2), different, so can't drop (2).
Drop (3), leaving (1) and (2). Compute A+ = A. Previously, A+ = AB by (3), different, so can't drop (3).

7. A web application usually runs all its database code (all DML) as a single user. Write PL/SQL code to create an Oracle user webapp_user with just enough privileges to do all normal DML operations.  See the 9 lines of code in Murach on pg. 397 (fig 12-12) for creating the ap_user role, and use it as a model to write similar commands for a webapp user (not a role, but you can use the same sort of grant command as for the ap_user role) that needs to access the tables we used in hw5 (flights, employees, certified, aircraft, assigned_flights, delayed_flights), giving minimal needed privileges (for example, grant select but not insert, etc. if a certain table is never updated). Assume the tables exist already in the account being used for the script execution, so you can use their simple names like flights. The user does not need synonyms here. The first line of  your code should create the user webapp_user using the default tablespace users and password sesame, and unlimited quota on users, like the command on pg. 385 (fig. 12-6). Then grant role create session (for login, as on pg. 389 fig 12-8) to the new user, and then specify the table-related privileges. You can't test this script because it takes DBA privilege to run.

8. Suppose you need to provide access to the employees table without the salary information, to user clerk. Give the code to define a view empinfo(eid, ename) that provides this information from table employees, which is in schema HR for this problem. The view is also in schema HR. Give the command to grant select access to empinfo for user clerk, while keeping employees protected from clerk. Assume you can login to account HR, so you have full privileges on employees and empinfo. We don't need to do anything special about employees to hide it from user clerk because by default it is not visible to other users. You can test the view online but not the grant access, so just record everything in your paper.