Consider the following relations for a database. ANIMAL(AnimalID, AnimalName, Sp

Question

Consider the following relations for a database.

ANIMAL(AnimalID, AnimalName, SpeciesID, AnimalDOB)

SPECIES(SpeciesID, SpeciesName, SpeciesDiet)

ZOO_HOUSE(HouseID, HouseName, HouseCapacity)

EXHIBITION_AREA(ExhibitionID, HouseID, RoomNumber, AnimalID)

Specify all foreign keys for this schema, stating any assumptions you make. Next, populate the relations with a few sample tuples. Then give an example of insertions into the EXHIBITION_AREA and ANIMAL relations that violate the referential integrity constraints, and examples of insertions that don't violate the referential integrity constraints.

Can someone please explain properly. so that i can solve similiar questions like this one.

Thanks

Explanation / Answer

part a)

Table SPECIES(SpeciesID, SpeciesName, SpeciesDiet) has no foreign key

Table ZOO_HOUSE(HouseID, HouseName, HouseCapacity) has no foreign key

Table ANIMAL(AnimalID, AnimalName, SpeciesID, AnimalDOB)

Has SpeciesID as foreign key which is derived from SPECIES table

Table EXHIBITION_AREA(ExhibitionID, HouseID, RoomNumber, AnimalID)

Has HouseID as foreign key which is derived from ZOO_HOUSE table

Has AnimalID as foreign key which is derived from ZOO_HOUSE table

part b)

For example take

TABLE SPECIES

table ZOO_HOUSE

Table ANIMAL

INSERT INTO ANIMAL(AnimalID, AnimalName, SpeciesID, AnimalDOB) VALUES (1,'tiger,1,'20/01/2017')

this will don't violate the referential integrity constraints because foreign key SpeciesId =1 as entry in table SPECIES

INSERT INTO ANIMAL(AnimalID, AnimalName, SpeciesID, AnimalDOB) VALUES (1,'eleghant,5,'20/01/2017')

this will violate the referential integrity constraints because foreign key SpeciesId =5 as no entry in table SPECIES

Table EXHIBITION_AREA

INSERT INTO EXHIBITION_AREA(ExhibitionID, HouseID, RoomNumber, AnimalID) VALUES(1,2,17,1)

this will NOT violate the referential integrity constraints because foreign key HouseID=1 as no entry in table ZOO_HOUSE and AnimalID=1 as entry in table ANIMAL

INSERT INTO EXHIBITION_AREA(ExhibitionID, HouseID, RoomNumber, AnimalID) VALUES(1,5,17,1)

this will violate the referential integrity constraints because foreign key HouseID=5 as no entry in table ZOO_HOUSE.

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

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