According to Database Systems A Practical Approach to Design, Implementation, an

Question

According to Database Systems A Practical Approach to Design, Implementation, and Management SIXth edition, page 534, the relation of the subclass/ superclass for the logical data model is as below:

PARTICIPATION CONSTRAINT

it will become

Alumni(Donor_ID, Alumni_ID, Alumni_Name, Type)

Primary Key(Donor_ID)

Company(Donor_ID, Company_ID, Company_Name, Type)

Primary Key(Donor_ID)

OtherDonor(Donor_ID, Other_Donor_ID, Other_Donor_Name, Type)

Primary Key(Donor_ID)

Donation(Donation_ID, Amount, Donor_ID)

Primary Key(Donation_ID)

Foreign Key(Donor_ID) references Alumni(Donor_ID) and Company(Donor_ID) and OtherDonor (Donor_ID) <- unable to create the constrainst like this in oracle

In the oracle relational database, it will allow the user to have the same Donor_ID across multiple relations (Alumni, Company and OtherDonor). For example, in the Alumni relation, the alumni can have the Donor_ID: 1234 while the company in the company relation also can have the same Donor_ID:1234 which is not the desired case.
Besides, the foreign key Donor_ID in the Donation relation also cannot refer to the Donor_ID in the Alumni, Company and OtherDonor relations at once.

Therefore, how should I modify in order to have a unique Donor_ID for each of the tuples in Alumni, Company and Other Donor?
Shall I create an additional table called Donor and proceed as below:

Alumni(Alumni_ID, Alumni_Name, Donor_ID)

Primary Key(Alumni_ID)

Foreign Key(Donor_ID) references Donor(Donor_ID)

Company(Company_ID, Company_Name, Donor_ID)

Primary Key(Company_ID)

Foreign Key(Donor_ID) references Donor(Donor_ID)

OtherDonor(Other_Donor_ID, Other_Donor_Name, Donor_ID)

Primary Key(Other_Donor_ID)

Foreign Key(Donor_ID) references Donor(Donor_ID)

Donation(Donation_ID, Amount, Donor_ID)

Primary Key(Donation_ID)

Foreign Key(Donor_ID) references Donor(Donor_ID)

Donor(Donor_ID, Type)

Primary Key(Donor_ID)

Or is there any other suitable modification that I can make?

PARTICIPATION CONSTRAINT

DISJOINT CONSTRAINT RELATIONS REQUIRED Mandatory Or Many relations: one relation for each combined superclass/subclass Donation Donation ID {PK) Amount Donor ID FK) Provides 1..1 Donor Donor ID {PK) Iype (Mandatory,Or) Alumni Alumni ID Ahumni Name Company_ID Company Name Other_Donor_ID OtherDonorName

Explanation / Answer

What ever the solution you came up with is absolutely perfect as Donor_id should be in master table. Where you came up with Donor relation.

Now in Donor table, as Donor_ID is primary key , each Type like Alumni/Donation/Company/Other Donor will have different and unique Donor_id.

Please go ahead and implement your solution. It is just to confirm you that your solution works.

Cheers.

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.