How to model weak entity in ER diagram?

All we need is an easy explanation of the problem, so here it is.

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.

How to model weak entity in 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 ??

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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

Method 2

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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply