I just recently started learning database design, and I'm am working with Oracle 11G and SQL Developer.
I have these 3 business rules for a DB:
- Each OFFICER must enroll in one and only one INSURANCE COMPANIES. each INSURANCE COMPANY may enroll one or more OFFICERS
- Each INSURANCE COMPANY must provide at least five different types of INSURANCE TYPES. Each TYPE OF INSURANCE may be provided by up to 4 INSURANCE COMPANIES or none at all
- Each INSURANCE TYPE may be subscribed to by one or more OFFICERS. Each OFFICER may subscribe to up to FIVE different INSURANCE COVERS provided by the same company.
. . .
so far, so good, i came up with five TABLES (INS_COY, OFFR, INS_TYPE, PROVIDE, and SUBSCRIBE). PROVIDE and SUBSCRIBE came about as composite tables since the relationships between INS_COY and INS_TYPE, and OFFR and INS_TYPE are both M:M relationships.
PK and FK attributes for each of the table is as below:
INS_COY TABLE
coy_id - PK
OFFR TABLE
offr_id - PK
coy_id - (FK referencing INS_COY.coy_id))
INS_TYPE TABLE
type_id - PK
PROVIDE
coy_id and type_id - (composite PK)
coy_id - (FK referencing COY.coy_id)
type_id - (FK referencing ins_type.type_id)
SUBSCRIBE
naf_no and type_id - (composite PK)
naf_no - (FK referencing offr.offr_id)
type_id (FK referencing ins_type.type_id)
.
.
.
the tables have been sucessfully created, and sample data inserted.
so, the problem is - on the SUBSCRIBE TABLE, HOW DO I ENSURE INTEGRITY THAT THE TYPE_ID ATTACHED TO AN OFFR_ID IS AN INS_TYPE PROVIDED BY THE COY HE IS ENROLLED IN?
i.e ...from the tables, "offr 4250" is enrolled in "coy 1", and "coy 1" doesn't provide "ins_type 13", however, because there's no constraint to check this, "offr 1" is subscribed to "ins_type 13" on the SUBSCRIBE TABLE.
See Question&Answers more detail:os