- (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.
- (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.
- (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.
- (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).
- (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.
- 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.
- 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.