Hey I have a question here about my tables. Would appreciate the help!

The question is about my foreign key for Hotel_id in the Place_Reservation table. I have it referencing hotel, but should I reference the foreign key to the hotel id in Has_Room. Has_Room is a weak entity of Hotel.




CREATE TABLE Hotel (
hotel_id varchar(20),
hotel_location varchar(40),
hotel_name varchar(30),
hotel_capacity integer,
hotel_rating integer,
pets_allowed bit,
primary key(hotel_id))

CREATE TABLE Has_Room (
room_available bit,
room_type varchar(30),
room_price real,
guest_count int,
room_number int,
hotel_id varchar(20),
PRIMARY KEY (room_number, hotel_id),
FOREIGN KEY (hotel_id) REFERENCES Hotel
ON DELETE CASCADE)

CREATE TABLE Place_Reservation (
room_number int,
hotel_id varchar(20),
reservation_id integer,
to_date date,
from_date date,
user_id varchar(30) NOT NULL,
PRIMARY KEY (reservation_id),
FOREIGN KEY (user_id) REFERENCES Customer,
FOREIGN KEY (room_number) references Has_ROOM,
FOREIGN KEY (hotel_id) references Hotel)

The foreign key of a child table must reference a complete primary or unique key of the parent. So your foreign key from Place_Reservation to Has_Room should be like this:

CREATE TABLE Place_Reservation (
room_number int,
hotel_id varchar(20),
reservation_id integer,
to_date date,
from_date date,
user_id varchar(30) NOT NULL,
PRIMARY KEY (reservation_id),
FOREIGN KEY (user_id) REFERENCES Customer,
FOREIGN KEY (room_number, hotel_id) references Has_ROOM)

There is then no need for any foreign key to Hotel.

Incidentally, it is unusual (wrong, really) to use verbs in table names. I would rename Has_Room to Room and Place_Reservation to Reservation.

Hey Thanks a lot! That really clarified things. Also thanks for the tip about the verb thing. Kind of my first time doing this!