Scribd
Upload
7 views27 pages

MySQL Hotel Chain Database Design

The document outlines the normalization process for a hotel chain database using MySQL, detailing the creation of various tables such as hotel_branch, staff, room_booking, customer, loyalty_points, feedback, pre_order, and task. It discusses the insertion of values and the achievement of the third normal form (3NF) for these tables, while noting challenges in achieving the second and third normal forms due to dependencies. The document is a submission by Nimra Ashraf and Noor Fatima, dated January 5, 2025.

Uploaded by

Arslan Ashraf
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
7 views27 pages

MySQL Hotel Chain Database Design

The document outlines the normalization process for a hotel chain database using MySQL, detailing the creation of various tables such as hotel_branch, staff, room_booking, customer, loyalty_points, feedback, pre_order, and task. It discusses the insertion of values and the achievement of the third normal form (3NF) for these tables, while noting challenges in achieving the second and third normal forms due to dependencies. The document is a submission by Nimra Ashraf and Noor Fatima, dated January 5, 2025.

Uploaded by

Arslan Ashraf
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

GROUP # 16

HOTEL_CHAIN NORMALIZATION

TABLES ON MYSQL COMMAND LINE CLIENT


SUBMITTED TO MAM SEHRISH KHAN
MADE BY:
NIMRA ASHRAF & NOOR FATIMA

ROLL NOs:
110829 & 110830

GROUP NO:
16

DATED:
05/01/2025
MOST COMMONLY USED KEYWORDS
• create database database_name;
• use database;
• create table table_name(attribute datatype(size),…);
• INSERT INTO database_name values(attribute_name
datatype(size));
• DESCRIBE table_name; select * from table_name;
• ALTER table table_name ADD attribute_name
type(domain);
• Update table_name set attribute=‘value’ WHERE
attribute_PK=‘target-value’;
ENTITY RELATIONSHIP DIAGRAM
RELATIONAL SCHEMA

 hotel_branch (h_id, h_name, h_address, staff_id)


 staff (s_id, s_name, s_address, s_role)
 room_booking (R_id, R_no, check_in_date, check_out_date,
weekend_pricing, holiday_pricing, early_bird_offers, s_id)
 customer (c_id, c_name, c_address, r_id)
 loyalty_points (points_id, upgrade_points, service_points, cus_id)
 feedback (f_id, f_type, comment, customer_id)
 pre_order (order_id, meal, taxi_service, cust_id)
 task (t_id, type, s_id)
CREATE DATABASE & HOTEL_BRANCH TABLE
INSERTING VALUES, 3 NF OF HOTEL_BRANCH &
CREATE STAFF TABLE
INSERTING VALUES AND MAKE 3RD NORMAL
FORM OF STAFF
ADD S_ID COLUMN IN HOTEL BRANCH TABLE &
CHANGE ITS NAME ( FROM S_ID TO STAFF_ID)
MAKE STAFF_ID FOREIGN KEY AND INSERT ITS
VALUES
MAKE ROOM_BOOKING TABLE AND MODIFY
CHECK_IN AND CHECK_OUT DATES
DESCRIBE ROOM_BOOKING, MODIFY
EARLY_BIRD_OFFERS & INSERTING VALUES
MADE STAFF_ID FOREIGN KEY IN ROOM_BOOKING
ADD VALUES IN FOREIGN KEY
3 NF OF ROOM_BOOKING AND MAKE ROOM_ID
FOREIGN KEY IN STAFF TABLE
CREATE CUSTOMER TABLE AND ADD MISSING
C_ADDRESS COLUMN IN IT
INSERTING VALUES AND MAKE R_ID FOREIGN
KEY IN CUSTOMER TABLE
CREATE LOYALTY POINTS TABLE, MAKE CUS_ID
FOREIGN KEY IN IT
INSERTING VALUES AND DESCRIBE
LOYALTY_POINTS TABLE
CREATE FEEDBACK TABLE AND MAKE
CUSTOMER_ID FOREIGN KEY IN IT
INSERTING VALUES AND CREATE PRE_ORDER
TABLE WITH A CUST_ID COLUMN
MADE CUS_ID FOREIGN KEY FOR PRE_ORDER
TABLE AND INSERT VALUES
CREATE TASK TABLE, ADD STAFF_ID FOREIGN
KEY AND DESCRIBE IT
INSERT VALUES IN TASK TABLE AND SHOW IT
PROBLEMS IN 2 ND AND 3RD NORMAL FORMS
2ND NORMAL FORM:
2nd normal form of a table is made when there is partial dependency,
means when some of the attributes of a table depend on one attribute and some
other depends on any other attribute. But in the above normalization there is no
table having partial dependency. So, 2nd normal form is not possible.

3RD NORMAL FORM:


3rd normal form of a table is created when a non-key attribute is easily
determined by any other non-key attribute called transition dependency. In above
normalization case, there are some table having 3NF but mostly tables don’t have
transition dependency.
THANKS

You might also like