Hi, this question is SQL related. The following tables form part of a database h

Question

Hi, this question is SQL related.

The following tables form part of a database held in a relational DBMS: Hotel hotelNo. hotelName, City) Room (roomNo. hoteINo. type, price) Booking(hotelNo.GuestNo, dateFrom dateTo, roomNo Guest(guestNo, guestName, guestAddress) Where Hotel contains hotel details and hotelNo is the primary key; Room contains room details for each hotel and (room No hotelNo) forms the primarykey Booking contains details ofbooking, and (hotelNo, guestNo dateFrom) forms the primarykey Guest contains the guest details and guestNo is the primarykey Question: 1.2 Identify the foreign keys in this schema. Explain howthe entity and referential integrity rules apply to these relations [5 marks]

Explanation / Answer

Foreign Key: Foreign key is a key or column(columns) in one table that refers to the PRIMARY KEY in another table.

In other words,a primary key of one table is acting as an ordinary(sometimes primary key)key in the other table which is mainly used to related the tables.

Foreign Keys in the given schema are:

1)Room table has hotelNo as Foreign key which along with roomNo forms the primary key of Room table.

2)Booking table has hotelNo, roomNo as Foreign keys. We can have multiple foreign keys on the same table.

In this table we have two Foreign keys(hotelNo,roomNo )

3)Guest table has guestNo as Foreign key which also a primary key of the Guest table itself.

Entity integrity:

Entity integrity requires that each value of a primary key be unique within the table, and that there are no NULL values.

In this cae, If we attempts to insert or update a primary key values in any table(Hotel,Room,Booking,Guest) and provides values that are not unique, entity integrity would be breached and throws error.

Referential Integrity:
Referential integrity ensures the relationships between tables remain preserved as data is inserted, deleted, and modified.

In this, case if we insert,update,delete any date from the 4 tables, the relantionship between each table should be preserved. Otherwise,it throws error if we attempts to modify data on refrerntial constriants

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

---

---

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