trjhtr

I have to create a Hotel database according to these requirements:

• information about guests should be found, information about rooms divided into categories, reservations (ordered by a type of a room and assigned for a specific type of a room).




• the following functionality should be provided: make a reservation by assigning a free room with the guests expectations - if you cannot meet guest's expectations, you have to save booking order to be settled by authorized employees




• check how many times given person was a guest in last 2 years




• which guests came to the hotel, accordingly to the reservation




• free the room and write the bill

My code:

CREATE TABLE client_reservation (
 client_id INTEGER NOT NULL,
 reservation_id INTEGER NOT NULL
);

ALTER TABLE client_reservation ADD CONSTRAINT client_reservation_pk PRIMARY KEY ( client_id,
reservation_id );

CREATE TABLE clients (
 client_id INTEGER NOT NULL,
 first_name VARCHAR2(50) NOT NULL,
 last_name VARCHAR2(100) NOT NULL,
 street VARCHAR2(50),
 house_number VARCHAR2(6),
 town VARCHAR2(100),
 postal_code VARCHAR2(6),
 telephone VARCHAR2(15) NOT NULL
);

ALTER TABLE clients ADD CONSTRAINT clients_pk PRIMARY KEY ( client_id );

CREATE TABLE reservations (
 reservation_id INTEGER NOT NULL,
 date_of_purchase DATE,
 beggining DATE,
 ending DATE,
 status VARCHAR2(10) NOT NULL,
 date_of_reservation DATE NOT NULL
);

ALTER TABLE reservations ADD CONSTRAINT reservation_pk PRIMARY KEY ( reservation_id );

CREATE TABLE resevation_rooms (
 reservation_id INTEGER NOT NULL,
 room_id INTEGER NOT NULL
);

ALTER TABLE resevation_rooms ADD CONSTRAINT resevation_rooms_pk PRIMARY KEY ( reservation_id,
room_id );

CREATE TABLE room_category (
 category_id INTEGER NOT NULL,
 name VARCHAR2(50) NOT NULL,
 description VARCHAR2(200)
);

ALTER TABLE room_category ADD CONSTRAINT room_category_pk PRIMARY KEY ( category_id );

CREATE TABLE rooms (
 room_id INTEGER NOT NULL,
 capacity INTEGER NOT NULL,
 price_per_day NUMBER(10) NOT NULL,
 room_category INTEGER NOT NULL
);

ALTER TABLE rooms ADD CONSTRAINT room_pk PRIMARY KEY ( room_id );

ALTER TABLE client_reservation
 ADD CONSTRAINT client_fk FOREIGN KEY ( client_id )
 REFERENCES clients ( client_id );

ALTER TABLE client_reservation
 ADD CONSTRAINT reservation_fk FOREIGN KEY ( reservation_id )
 REFERENCES reservations ( reservation_id );

ALTER TABLE resevation_rooms
 ADD CONSTRAINT reservation_rooms_fk FOREIGN KEY ( reservation_id )
 REFERENCES reservations ( reservation_id );

ALTER TABLE rooms
 ADD CONSTRAINT room_category_fk FOREIGN KEY ( room_category )
 REFERENCES room_category ( category_id );

ALTER TABLE resevation_rooms
 ADD CONSTRAINT room_reservation_fk FOREIGN KEY ( room_id )
 REFERENCES rooms ( room_id );

Model:

Thanks for every suggestions, cause I'm not so sure if I meet requirements and put right relations (identifying or nonidentifying)

I think, you don't need tables client_reservation and reservation_rooms. You have tables clients and rooms, and then, to make a reservation, you need only one table - reservation (I prefer to give table names in singular form). This table will reference to a client and to a room, and the reservation period (start date/end date). Also, you forgot about the actual state of the room - it could be just booked or busy. I would propose to create a new table - status, and reference it from reservation. The table status could contain the following values:

• 
  booked - when a client booked the room


• 
  busy - when a client came to the hotel


• 
  canceled - when a client didn't come at time and room became available for other guests


• 
  unavailable - for example, when a room needs a renovation and/or repair of equipment


• etc.

Also, a billing is mentioned in the task. It is a very wide area, to fully support it, you may need a lot of tables to store the data about billing plans (a room can have different prices depending on a season, a category, available equipment, a client type, a view from the window, the number of the floor, etc.).