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
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 );
Thanks for every suggestions, cause I'm not so sure if I meet requirements and put right relations (identifying or nonidentifying)