SQL Database Design for Hotel Management
Translated by
ScribdTranslationsSQL Database Design for Hotel Management
Translated by
ScribdTranslationsINSTITUTE
SUPERIOR
TECHNOLOGICAL
PRIVATE HIPOLITO
UNANUE
NOMBRES: YESICA RAMIREZ
PAUCAR
GABRIELA CHURA GUZMÁN
SEMESTRE: VI
SECTION: "V"
Area: SQL SERVER
PROF: DANIEL ALEJANDRO VERA
AMÉZQUITA
2013
DATABASE FOR A HOTEL
CATEGORY TABLE
create table category(
categoryidintnot null,
name varchar(40)
primary key(idcategory)
);
DESCRIPTION
The category table helps us identify the different categories of the rooms.
that exist in the hotel, has an idcategory and its respective name.
CLIENT TABLE
create table client(
clientid integer not null
varchar(40) name
lastnamesvarchar(50)
email varchar(40)
address varchar(40)
telephone call
primary key(idclient)
);
DESCRIPTION
The clients table helps us store the names of the hotel’s frequent clients.
with general information for your registration.
CLIENTTABLE
create tablehabcliente(
roomintnot null,
customer_id int not null
fechaentradadate,
date
primary key(idroom)
);
DESCRIPTION
The habcliente table identifies the room of each client with their respective ID and the date of
check-in and check-out date of the hotel.
ROOMS TABLE
create table rooms(
go not null,
tipovarchar
bathroomsvarchar
pricevarchar
occupied varchar,
primary key(idhab)
);
DESCRIPTION
The rooms table contains the description of the type of room, its price, as well as its
availability for customers.
SCHEDULE TABLE
create table schedules(
idhorariointnot null
hora_iniciodate,
hour_findate
capacity varchar(30)
monday varchar(30)
Tuesday varchar(30),
Wednesday varchar(30)
Thursdayvarchar(30)
Friday varchar(30)
sabadovarchar(30)
sunday varchar(30)
primary key(idhorario)
);
DESCRIPCIÓN
The schedule table contains the availability of hotel timings, including the start and end times.
schedules of the days of the week.
SERVICE TABLE
create table servicehab(
idservicehabin not null
inhabit room int
description varchar(50)
close date
state varchar(30)
primary key(idserviciohab)
);
DESCRIPCIÓN
The service table 'servciciohab' is the one that will help us store the different services available.
the hotel, specifically the room service, has a date, description, status (if it has
used or not room service) and its respective id.
SERVICE TABLE
create tableservices(
idserint not null
name varchar(40)
description varchar(50)
priceint
capacity varchar(50)
idtipoint
durationvarchar(20)
primary key(idser)
);
DESCRIPTION
The services table is the one that will help us store the different services available.
hotel.
TABLE SERVICE_TYPE
create table service_type(
idtipoint not null,
category id
name varchar(40)
description varchar(50)
primary key(idtipo)
);
DESCRIPTION
The table type_service gives us a reference to the types of services provided by the hotel, something like this
like the categories.
INCIDENTS TABLE
create table incidents(
incidentID not null
room
description varchar(40)
close date
state varchar(30)
primary key(idincident)
);
DESCRIPTION
The incidents table contains everything related to complaints, suggestions, or mishaps that
they arise in the hotel rooms.
RESERVATION TABLE
create tablereservas(
idreservaint not null
clientidint
idserviceint
close date
now start time
finalsecondtime
primary key(idreserve)
);
DESCRIPTION
The reservations table informs us about the reservations being made, it contains a relationship with the id of the
client and with the service id.