0

I was going through the weak entity concepts and was trying to model the relation Hotel has many rooms. I have come up with the partial solution presented below but i'm having a problem to uniquely identify the tuples in the room entity related to the specific hotel.

My Assumption

  1. Hotel(hotel_number, name) is a strong entity.
  2. Rooms(room_number, bed) is a weak entity whose existence depends on the existence of the hotel.

Modeled ER Diagram

1. Hotel entity's table

hotel_id name
125 n1
132 n2
173 n3
103 n4
193 n5

2. Room entity's table

room_number beds
101 3
101 2
102 1
103 6

There are two hotels with hotel_id 125 and 132. Hotel with hotel_id 125 has room numbered 101 with 3 beds and another hotel has room_number 101 with 2 beds. So how can i model the given relation in such condition as there's no pk for the weak entity i.e. room ??

2 Answers 2

3

Room should have PK of (hotel_id,room_number).

Weak entities have compound keys with the strong entity key/foreign key as the leading column(s).

There are people who disagree with this approach, but they are wrong.

How can i find the room belonging to specific hotel which has the different number of beds limiting the attributes in the domain of my problem

Something like:

select *
from rooms
where hotel_id  = 1001
  and beds = 3
4
  • So if i were to implement that in SQL i've to create a table for the weak entity with the partial key and the foreign key(from the pk of strong entity participating in relation).This is what i was taught in my DBMS course for mapping weak entity into relational schema. Still my confusion is How can i find the room belonging to specific hotel which has the different number of beds limiting the attributes in the domain of my problem ?. In the domain of the problem theres no such attributes that can be used as pk for hotel though i can add the ID as a new field to the hotel. Commented Mar 12, 2021 at 16:17
  • But the attribute ID doesnt belong to the domain i'm trying to model i.e. ID has no meaning to the hotel entity. Commented Mar 12, 2021 at 16:21
  • 1
    Like many entities, a Hotel has a Name, but that can change, and isn't necessarily unique. So you introduce a "synthetic" or "artificial" key. Commented Mar 12, 2021 at 17:21
  • Thanks for your time @Browne - Microsoft. To identify a entry in the room for specific hotel i need beds and the hotel_number. Which implies partial key for weak entity(room) is going to be bed. Correct me if i'm wrong. Thank you :) Commented Mar 12, 2021 at 21:57
1

The real question is why is there no primary key for the Rooms table? In most cases (except a few edge cases like staging tables) you should have a primary key on your tables.

Despite this, even without a primary key on the Rooms table, it looks like the relationship is naturally many-to-one with Hotels (that is any room can only belong to one hotel), and therefore you could at least add a foreign key field to the Rooms table on hotel_id. So your foreign key constraint would be on Rooms.hotel_id to Hotels.hotel_id.

But my recommendation would be to add both a primary key and then foreign key field to your Rooms table. Without doing at least one of those things, it's not possible to establish a relationship between Rooms and Hotels. The primary key on the Rooms table would be defined on hotel_id, room_number.

7
  • A bit confused here. But my recommendation would be to add both a primary key and then foreign key field to your Rooms table. Without doing at least one of those things, it's not possible to establish a relationship between Rooms and Hotels. If so then what's the purpose of relation has. Commented Mar 11, 2021 at 18:34
  • And there's pk in the room entity, because that's going to be the weak entity. Commented Mar 11, 2021 at 18:50
  • @sbhusal123 I'm not familiar with the syntax of your ER diagram, but it's not possible to relate the two without a field for them to relate on. Right now I don't see a field in your Rooms table that is relatable between the two (which I think is your whole issue). Which field is the primary key in your Rooms table?
    – J.D.
    Commented Mar 11, 2021 at 20:07
  • Weak entity dont have primary key that's the reason we call it weak. And the existence of such entity depends on the strong entity Commented Mar 12, 2021 at 4:43
  • @Sbhusal123 Sorry I misunderstood your last reply thinking you said that there is a primary key. Anyway see David Browne's answer confirming what I said about using a primary key (or alternatively just a foreign key on hotel_id) to establish the relationship. Without either of these, there is no relationship defined. (P.s. he works for Microsoft, so I think he knows his stuff. :)
    – J.D.
    Commented Mar 12, 2021 at 4:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.