Re: problem with referential integrity notation in SQL (and in ER diagrams?)

In article <l.carl.pedersen-1010942130400001_at_kip-1-sn-271.dartmouth.edu>, l.carl.pedersen_at_dartmouth.edu (L. Carl Pedersen) wrote:

> suppose i have a student loan system that allows certain types of loans
> only to certain types of students. i might represent this with
>
> create table student
> ( student_id number primary key,
> student_type_id number references student_type,
> name..., ssn... etc. );
>
> create table student_type
> ( student_type_id number primary key,
> student_type_description.... etc.)
>
> create table loan
> ( loan_id number primary key,
> loan_type_id number references loan_type,
> interest_rate... etc.);
>
> create table loan_type
> ( loan_type_id number primary key,
> loan_type_description.... etc.)
>
> create table student_loan
> ( student_id number references student,
> loan_id number references loan,
> amount... etc. );
>
> STUDENT_LOAN tells which loans are held by which students, but what about
> our type restriction saying that only certain types of loans are allowed
> to certain types of students? makes sense to make another table for this:
>
> create table type_pair
> ( student_type_id number references student_type,
> loan_type_id number references loan_type,
> primary key(student_type_id,loan_type_id) );
>
> OK, now what do i do?
>
> for each row in STUDENT_LOAN, i want to enforce the restriction that the
> LOAN_TYPE_ID associated with the loan must appear in the same row of the
> TYPE_PAIR table with the STUDENT_TYPE_ID associated with the student.
>
> i see no way to do this with static referential integrity clauses as
> defined in SQL (89 or 92). i *could* do it with a trigger in ORACLE or
> Sybase, but that seems obnoxious since the constraint is clearly *static*.
>

SOUNDS LIKE A HOMEWORK ASSIGNMENT.....WELL HERE GOES ANYWAY: WHAT YOU HAVE IS A CONSTRAINT:
FOR A STUDENT THE LIST OF PERMISSIBLE LOAN_TYPES ARE THOSE IN TYPE_PAIR SUCH THAT STUDENT.STUDENT_TYPE = TYPE_PAIR.STUDENT_TYPE AND THIS LIST SHOULD BE > 0. THIS CAN BE EXPRESSED AS A CONSTRAINT... CHECK ((SELECT COUNT(*) FROM STUDENT S, TYPE_PAIR TP         WHERE S.STUDENT_TYPE = TP.STUDENT_TYPE) > 0 )
> i wonder if this lack stems from a similar lack in E-R diagrams? my
> knowledge of the latter is somewhat limited, but in the notations i'm
> familiar with there is no way to state this kind of relationship in an E-R
> diagram either.

WHAT YOU HAVE HERE IS A BUSINESS RULE. THE CLASSIC EXAMPLE OF A BUSINESS RULS IS THAT THE SALARY OF AN EMPLOYEE CANNOT BE GREATER THAN THE SALARY OF THE SUPERVISOR. NOW, E-R DIAGRAMS WERE NEVER INTENDED TO MODEL BUSINESS RULES. THE PROBLEM IS NOT A LIMITATION OF THE E-R NOTATION, IT IS THE MISGUIDED ATTEMPT OF TRYING TO GET MORE INFORMATION OUT OF THEM THAN WHAT THEY WERE DESIGNED TO PROVIDE. CODD'S NEW RESEARCH PROJECT (DELTA) IS MORE APPROPRIATE FOR HANDLING BUSINESS RULES.

-- 
Don Baltz
EG&G/Energy Measurements Inc, P.O. Box 1912, Las Vegas, NV  89121
(702) 295-2675               (702) 295-2543 (fax)
e-mail: baltz-dw_at_egg.nv.doe.gov