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

In article <jreinert.781882918_at_rhein>, jreinert_at_rhein.informatik.uni-kl.de (Joachim Reinert - AG DVS) wrote:

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

then i have redundant data. that design doesn't make sense to me. it's not even normalized. Received on Tue Oct 11 1994 - 18:26:26 CET