Homework 7 (due 3/11)
CSC 355

We completed normalization with the 3NF decomposition algorithm (Section 3.5), and a comparison of 3NF and BCNF. We then talked about constraints and started mentioning triggers (Chapter 7). Next week, we will talk about triggers, views and indices (Chapter 8). Time permitting, we will move on to database programming (Chapter 9).
Handing it in: Submit to the d2l dropbox. Use some standard format for your homework (Word or pdf, for example, but avoid zip), and make sure to include your name and assignment number. Include screenshots for code and testruns.

1. (Reading Assignment) Read Section 3.5 and Chapter 7 (the trigger material we will cover this Monday). Sections 7.1-7.3 are review of earlier material. If you want to read ahead, move on to Sections 8.1, and 8.2.

2. (3NF, 12pt) Decompose the following relations into 3NF. Use the algorithm from class (so you need to compute the keys and minimal cover of each relation). Include the details of determining the Canonical Cover.

  1. R(A,B,C,D) with functional dependencies {A→BC, AD→B, CD→AB}.

  2. R(A,B,C,D,E) with functional dependencies {AD→BC, D→CE, C→E}.

  3. R(A,B,C,D,E) with functional dependencies {AD->B, B->D, C->EA}

3. (Checks and Assertions, 12pt) For the following tasks write attribute/tuple level check constraints or assertions as appropriate  (so points will be given for picking the right type). Only submit the relevant lines of code. Since assertions are not supported, you do not have to submit testruns for this problem.

  1. Ensure that no more than 3 students enroll in each class (your query should be easibly modifiable to change 3 to any other number).

  2. Ensure that every PhD student has 'GRD' as career.

  3. Ensure that Department labels in course are restricted to 'CSC', 'DC', 'IT', 'GAM', and 'SE'.

4. (Triggers, 16pt) Write triggers for the following tasks. Test the triggers by performing operations that activate the triggers and submit outputs.

  1. When a student is added as a member of a student group, automatically set the joined year to the current year. Hint: recall select extract(year from sysdate) from dual;

  2. We do not want courses with CourseNr less than 100 or larger than 699. If a course is inserted or updated to result in a course number outside the acceptable range, the operation should proceed, but the CourseNr should be set to null and a warning should be issued. Hint: use dbms_output.put_line to create the warning message. You'll need to run set serveroutput on; before running the other code to see the output.

5. (Extra Credit, requires complexity theory) Show that determining whether an attribute in a given relation with a set of functional dependencies is prime is NP-complete.
Marcus Schaefer
Last updated: March 6th, 2014.