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

l.carl.pedersen_at_dartmouth.edu (L. Carl Pedersen) writes:

>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?

Include student_type_id and loan_type_id into the table student_loan (it makes also sense to include them into the primary keys of student and loan respectively). Afterwards inlude a reference von student_loan to type_pair.

Hope this helps

Joachim

-- 
Joachim Reinert              UNI Kaiserslautern, AG-DVS
fax   : +49 631 205 3558     P.O. Box 3049
phone : +49 631 205 3282     67653 Kaiserslautern - Germany 
e-mail: jreinert_at_informatik.uni-kl.de