CS145 Assignment #5
Due Wednesday, November 12, 1997
Step 5 of Your PDA
- (10 pts.) For each of the relation schemas of your PDA, indicate
- A suitable key for the relation.
- 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.
- (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
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.
- (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.
- (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.
- (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
Questions 1-5 refer to the following relation schema.
All answers should use stanard SQL2 or SQL3.
- Course (course#, title, dept, min_units, max_units)
- PreRequisite(course#, prereq_course#)
- Offering(course#, QYear, classroom, time)
- Student(SUID, name, address, dept)
- Faculty(name, dept)
- Instructor(course#, QYear, faculty_name)
- TA(course#, QYear, SUID)
- Enrollment(course#, QYear, SUID, units, Grade)
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)
- (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.
- (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.
- (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.
- (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).
- (10 pts.)
For, this question, we add another relation to our schema
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 pts.) Exercise 4.6.5(c) [p. 238].