CS145 Assignment #5

Due Wednesday, November 12, 1997

Step 5 of Your PDA

  1. (10 pts.) For each of the relation schemas of your PDA, indicate

    2. (a)
    A suitable key for the relation.
    (b)
    Any foreign key (referential integrity) constraints that you expect will hold for the relation.

    Modify your database schema to include the declaration of keys for all relations and at least one foreign-key constraint for some relation (even if you decided that no such constraints should logically hold -- we assume almost every PDA will have some natural foreign-key constraints). Show us the resulting database schema and the result of successfully declaring these relations to the database system.

  2. (15 pts.) Add two attribute-based and two tuple-based CHECK constraints to relations of your database schema. Remember that these constraints are more limited in Oracle 7.3.2 than in the SQL2 definition, see The Non-SQL2 Guide for details. Show the revised schema, its successful declaration, and the response of Oracle to inserts that violate the constraints. You may combine this part with the previous part if you like, to avoid repeating the schema.

  3. (15 pts.) Write five data modification commands on your PDA database. Each of these commands should be legal, given the constraints your created for your database in parts (1) and (2). Most of these commands should be ``interesting,'' in the sense that they involve some complex feature, such as inserting the result of a query, updating several tuples at once, or deleting a set of tuples that is more than one but less than all the tuples in a relation. As for the queries from PDA part 4, you might want to try out your commands on small data before trying it on your full database. Hand in a script that shows your modification commands running in a convincing fashion.

  4. (10 pts.) Give two examples of modification commands that would be legal, except for your constraints. Hand in a script showing these commands and the result of executing them.

  5. (10 pts.) Create two views on top of your database schema. Show your CREATE VIEW statements and the response of the system. Also, show a query involving each view and the system response (but truncate the response if there are more than a few tuples produced). Finally, show a script of what happens when you try to update your view, say by inserting a new tuple into it. Are either of your views updatable? Why or why not? (Updatable views are discussed in Section 5.8.4 of the text. Essentially, a view is updatable if it is a selection on one base table.)

Problem Set

Questions 1-5 refer to the following relation schema. All answers should use stanard SQL2 or SQL3.

Offering gives the courses that are offered in a particular Quarter - QYear is one single number that keeps track of quarter and year (QYear 19970 = Winter, 1997; 19971 = Spring, 1997, etc.). Grades are recorded as real numbers (A+ = 4.33 and so on. Assume that we have no withdrawls, incompletes or S/NC, CR/NC)

  1. (6 pts.) Write an SQL query that gives for each student their lowest grade and the course and the quarter when they received that grade. In case of multiple courses with the lowest grade, report all.

  2. (6 pts.) Write an SQL query that will produce the following information about the courses -- course#, title, dept, which quarters it was offered, and who were the instructors in those quarters. Note that a course may be offered without an instructor.

  3. (6 pts.) We wish that if a student has enrolled in a particular course, he must have completed all the prerequisites prior to enrolling in this course (i.e in an earlier quarter). Write an SQL2 assertion that forces this constraint.

  4. (6 pts.) Implement the above constraint via SQL3 trigger(s). In case an enrollment record is inserted with units outside the [min_units, max_units] bound, allow the insertion, but change the units to be within bounds (min_units if less, max_units if more).

  5. (10 pts.) For, this question, we add another relation to our schema Synonyms(course#1, course#2), which means that course#1 and course#2 are names for the same course, or both the courses cover similar material and a student is not allowed to enroll in both. Now we need to add the constraint that a student does not enroll in the same course twice (same => same name course or a synonym course). Implement this constraint using an SQL3 assertion.

  6. (6 pts.) Exercise 4.6.5(c) [p. 238].