Assignment #5
Due Wednesday November 13

This assignment will be graded out of 40 points.

  1. (3 points)
    Consider the following relational database schema, which you also used in Assignments 3 and 4:
       lives (name, street, city)
       works (name, company, salary)
       located (company, city)
       manages (name, manager-name)
    Make the following assumptions:

    (A1)
    Attributes lives.name, works.name, located.company, and manages.name are keys for their relations.

    (A2)
    Every person in the works relation also appears in the lives relation, but not necessarily vice-versa (i.e., noone works without living, but people may live without working).

    Based on these assumptions and your understanding of the real-world scenario being modeled, specify an appropriate set of referential integrity constraints for this schema. To specify a referential integrity constraint simply state ``R.A references S.B,'' where R.A and S.B are attributes.

  2. (10 points, 2 per part)
    In class we discussed six types of integrity constraints:
    (1)
    Key constraints
    (2)
    Referential integrity constraints
    (3)
    Not-null constraints
    (4)
    Attribute-based check constraints
    (5)
    Relation-based check constraints
    (6)
    General assertions

    We observed that the higher-numbered types of constraints tend to be more general than the lower-numbered types, and in fact many of the lower-numbered types can be expressed using the higher-numbered types instead. Consider the following very simple SQL relation declarations:
       create table R (A integer, B integer)
       create table S (C integer)
    Consider each of the following problem parts separately; that is, start ``fresh'' with the above relations definitions for each one. Use the SQL syntax for constraints described in the course notes and in class. (For single-attribute keys you may specify unique or primary key together with the attribute or at the end of the relation declaration, it doesn't matter to us; similarly for single-attribute referential integrity constraints.) For this problem, assume that all types of constraints are enforced correctly by the system--do not concern yourself with the constraint-checking ``hole'' described in class. (See also Problem 3 below.)

    (a)
    Specify that attribute A is a key for relation R, in each of the following ways: (i) as a key constraint; (ii) as one or more attribute-based check constraints; (iii) as one or more relation-based check constraints; (iv) as one or more general assertions.

    (b)
    Specify that there is a referential integrity constraint from attribute S.C (the foreign key) to attribute R.A (the primary key), in each of the following ways: (i) as a referential integrity constraint; (ii) as one or more attribute-based check constraints; (iii) as one or more relation-based check constraints; (iv) as one or more general assertions.

    (c)
    Specify that attribute R.A may not take on the value null, in each of the following ways: (i) as a not-null constraint; (ii) as one or more attribute-based check constraints; (iii) as one or more relation-based check constraints; (iv) as one or more general assertions.

    (d)
    Specify that the value of attribute R.A must be at least 10 and no greater than 20, in each of the following ways: (i) as an attribute-based check constraint; (ii) as one or more relation-based check constraints; (iii) as one or more general assertions.

    (e)
    Specify that for each tuple in R the value of attribute R.A must be at least twice the value of attribute R.B, in each of the following ways: (i) as a relation-based check constraint; (ii) as one or more general assertions.

  3. (7 points, 1 per part)
    Consider again the relational schema and assumptions used in Problem 1. For each of the following constraints, specify the constraint in SQL using the lowest-numbered constraint type from Problem 2 that guarantees the constraint cannot become violated. For this problem you should take into account the constraint-checking ``hole'' discussed in class. (Recall that the ``hole'' can occur with attribute-based and relation-based check constraints: in most systems such constraints are checked only when the relation in which the constraint is specified is modified, and not when other relations mentioned in the constraint are modified. Thus, for certain check constraints it is possible for the constraint to become violated and for the system not to discover it.) The constraints you write for this problem should never become violated, i.e., avoid the hole! Consider each constraint separately--do not assume that one constraint holds when specifying another one.

    (a)
    No person is his or her own manager.

    (b)
    No person is his or her own second-level manager (i.e., no person is his or her manager's manager).

    (c)
    No person who works for IBM earns more than $100,000.

    (d)
    No companies are located in the city of Atherton.

    (e)
    No more than five companies are located in the city of Atherton.

    (f)
    There is no person in the database who does not work (i.e., assumption (A2) is strengthened so that every person in the lives relation also appears in the works relation).

    (g)
    No person who lives in the city of Atherton works.

  4. (6 points, 1 per part)
    In this problem you will show how referential integrity enforcement can be implemented using triggers. Use the SQL trigger specification language given in class. In addition to SQL statements in the trigger ``action'', you may use the special action raise-error which generates an error. Consider two relations R(P,...) and S(...,F,...) where P is the primary key for R. We are interested in enforcing a referential integrity constraint from attribute S.F to attribute R.P. Imagine we're using a database management system that supports triggers but does not support referential integrity constraints.

    (a)
    Suppose we want to enforce the default policy for referential integrity enforcement, where an error is raised if there is an integrity violation. Write one or more triggers that implement this enforcement policy.
    (b)
    Suppose we want to enforce the set null policy for deletes and updates on relation R. Make any necessary modifications to your trigger or triggers from part (a).
    (c)
    Suppose we want to enforce the cascade policy for deletes and updates on relation R. Make any necessary modifications to your trigger or triggers from part (a).

  5. (4 points, 2 per part)
    Consider the relation works from Problem 1. Continue to assume that attribute name is a key for works.

    (a)
    Consider the following trigger, specified using the for each row option:
       create trigger IBMCutSalary
       after update of company on works
       referencing old as O, new as N
       when (N.company = "IBM" and O.company <> "IBM")
       update works
         set salary = .9 * salary
         where name = N.name
       for each row
    First describe in English what this trigger does. Then write an equivalent trigger that uses the for each statement option instead of for each row.

    (b)
    Specify another trigger on the works relation using the for each statement option such that an equivalent trigger cannot be written using the for each row option. (Assume that the equivalent trigger is not allowed to introduce temporary variables or tables.) In addition to specifying the trigger in SQL, describe in English what your trigger does.
    Don't worry if you're having trouble with this one; it's not obvious.

  6. PDA: Constraints

    (6 points, 1 each for (b)-(d), 1 for (e) and (f) together, 2 for (g))
    So far in your PDA the system has not enforced any key or referential integrity constraints that hold over your relations. In this problem you will recreate your relations, adding specifications for keys and referential integrity.

    (a)
    Remind us again of the relational schema you're using for your PDA.

    (b)
    For each relation R in : List all possible minimal keys for R and choose one key as the primary key.

    (c)
    List any referential integrity constraints that should hold on the relations in . Include only referential integrity constraints that involve two different relations, i.e., the foreign key is in one relation and the primary key is in another. (In other words, don't include referential integrity constraints within a single relation--they don't seem to be allowed in Sybase.)

    (d)
    Recreate your PDA database schema in Sybase. (That is, redo Problem 6(e) from Assignment #2.) This time use unique, primary key, and foreign key-referencing clauses within your create table statements so that Sybase will enforce the uniqueness of keys and will enforce referential integrity. You may use the default option for handling referential integrity violations (violations are not allowed and generate an error).

    (e)
    Reload your small hand-created database from Problem 5(a) in Assignment #3. Did you get any key or referential integrity violations?

    (f)
    Reload your large computer-generated database from Problem 5(b) in Assignment #3. Did you get any key or referential integrity violations?

    (g)
    You don't necessarily need to modify your program for generating data if it creates violations. However, for this part of the problem you should start with a database (small or large) that does not create violations. Write data modification commands to illustrate the following five scenarios:
    1.
    An insert command creating a key violation
    2.
    An update command creating a key violation
    3.
    An insert command creating a referential integrity violation
    4.
    A delete command creating a referential integrity violation
    5.
    An update command creating a referential integrity violation

    Parts (a)-(c) should be turned in on paper. For parts (d)-(g), turn in a script illustrating that you have done the required work.

    Note: We expect that everyone's PDA will include at least one referential integrity constraint. If your PDA has no natural referential integrity constraints, then it probably is either far simpler than we asked for, or a very poor design. Please contact one of the course staff.

  7. PDA: Views

    (4 points, 2 per view)
    Create and test at least two views in your PDA, one updatable and one non-updatable. For each one, define the view, write at least two queries that use the view, and (attempt) to update the database by updating the view. The command for defining a view in Sybase is:

    create view view-name as (query)
    If you want your view to use different attribute names than those from the base relations you can write:
    create view view-name (attr-name, attr-name, ..., attr-name) as (query)
    You can get rid of a view using the command:
    drop view view-name
    For each view, turn in a script illustrating that you have done the required work.



The TAs of CS145, cs145ta@cs.stanford.edu, last modified: 11/05/96