Database Design for a simple hotel System

I used to study the course design for SQL Server and accidentally found it when I sorted out the materials in the afternoon. Although it has been a few years, I found that it is good to learn SQL Server.
Currently, powerdesigner is used. In the past, all the code was written in one row, but no field descriptions were added. Do not remember whether there was a Database Design Instruction document.

---------------------------- Start -----------------------------

-- Drop database Hotel
If exists (select * from Master .. sysdatabases
Where name = 'myhotel ')
Drop database myhotel
Go

-- Create a database
Create Database myhotel -- keen Graduation Project
On
(
Name = 'myhotel _ dat ',
Filename = 'C:/myhotel. MDF ',
Size = 5,
Maxsize = 20,
Filegrowth = 1
)
Log On
(
Name = 'myhotel _ log ',
Filename = 'C:/myhotel. log ',
Size = 5,
Maxsize = 20,
Filegrowth = 1
)
------------------------------------------------------------------
Go

Use myhotel
Go

-- Create a table
-- Create Table Name (field name type: primary, key, field name, Int,
-- Field name type check (field name in (range )))
-- Bit integer data 1, 0, or null. If a table contains no more than eight bit columns, these columns will be stored as one byte

-- 1, room table (rootype = '1' indicates a deluxe double room, roomstuats = '0' indicates the last entry)
Create Table roomlist
(Roomid char (6) primary key,
Roomtypeid int,
Roomstatus bit check (roomstatus in (0, 1 )))
Go

-- 2. Server LIST)
---- The service list uses a tree structure to store the service ID
Create Table serverlist
(Serverid char (6) primary key,
Servername char (20 ),
Servercost money)
Go

-- 3. List of room categories ("roomtypelist", "1" is a luxury room, "2" is a single advanced room, and "3" is a double advanced Room)
Create Table roomtypelist
(Roomtypeid int primary key,
Roomtype char (20) not null,
Roomcost money not null)
Go

-- 4. Customer table (guest)
Create Table guest
(Guestid int Primary Key Identity (10001,1 ),
Guestname char (10) Not null,
Guestcardno char (20) not null,
Guestsex char (2 ),
Guestphone char (30 ))
Go

-- 5. serveruse)
-- Create Table Name (field name type primary key, field name type foreign key (foreign key name)
-- References table name,
-- Field name type foreign key (foreign key name) References table name ,.....)
Create Table serveruse
(Serveruseid int Primary Key Identity (1000,1 ),
Guestid int foreign key (guestid) References guest,
Serverid char (6) foreign key (serverid) References serverlist,
Servertime datetime)
Go

-- 6. Room ticket (guestroom)
Create Table guestroom
(Guestroomid int Primary Key Identity (), -- automatic number
Roomid char (6) foreign key (roomid) References roomlist,
Guestid int foreign key (guestid) References guest,
Indate datetime,
Outdate datetime)
Go

-- 7. Service revocation ticket. The structure and Service Usage Table are the same.
Create Table serverabort
(Abortid int Primary Key Identity (1000,1 ),
Guestid int foreign key (guestid) References guest,
Serverid char (6) foreign key (serverid) References serverlist,
Aborttime datetime)
Go

-- 8. checkmoney is used to store the total amount and time of customer checkout.
Create Table checkmoney
(Checkid int Primary Key Identity (1000,1 ),
Guestid int foreign key (guestid) References guest,
Totalcost money,
Intime datetime)
Go
------------------------------------------------------------------

-- Integrity problems

-- Ensure the accuracy of the relationship between all primary keys and Foreign keys
Alter table roomlist add foreign key (roomtypeid) References roomtypelist (roomtypeid)
Go

-- Create default constraint name as constant

-- The default value of the type of each room is the deluxe room (1 indicates the deluxe room)
Create default defroomtype as 1
Go
Sp_bindefault defroomtype, 'roomlist. roomtypeid' -- Note '.' and ','
Go

-- The default status value (roomstatus) of each room is null (expressed as 0)

Create default defroomstatus as 0
Go
Sp_bindefault defroomstatus, 'roomlist. roomstatus'
Go

-- Create check constraint alter table table name Add check (field range)
-- The customer's gender must be male or female'

Alter table guest add check (guestsex in ('male', 'female '))
Go

-- The default value of the customer's check-in date and service period is the current date.
Create default defdate as getdate ()
Go
Sp_bindefault defdate, 'guestroom. indate'
Go
Sp_bindefault defdate, 'serveruse. servertime'
Go

-- The default value of the service revocation time is the current time.
Create default deftime as getdate ()
Go
Sp_bindefault deftime, 'serverabort. aborttime'
Go

-- The entered service fee must be greater than 0
Alter table serverlist add check (servercost> 0)
Go

-- The storage format of telephone numbers is (999) 999-99999999
-- Like strings cannot be separated by line breaks. How can we wrap them?
Alter table guest
Add constraint chkphone
Check (guestphone is null or guestphone like
'([0-9] [0-9] [0-9]) [0-9] [0-9] [0-9]-[0-9] [0-9] [0-9] [0-9] [0-9]] [0-9] [0-9] [0-9] ')
Go
------------------------------------------------------------------

-- Insert records to the base table (the service cancel table does not insert records at the moment. During this process, you can test 'cancelling Service ')

Go
Insert serverlist values ('a01', 'car wash-trolley ', 10)
Insert serverlist values ('a02', 'car wash-chelay', 20)
Insert serverlist values ('b01', 'laundry-outerwear ', 10)
Go
Select * From serverlist
Go
Insert roomtypelist values (1, 'deluxe room', 688)
Insert roomtypelist values (2, 'single superior room', 188)
Insert roomtypelist values (3, 'DOUBLE master room', 288)
Select * From roomtypelist
Go
Insert roomlist (roomid, roomtypeid) values ('20140901', 1)
Insert roomlist values ('20170101', 1002)
Insert roomlist values ('20170101', 1003)
Insert roomlist (roomid, roomtypeid) values ('20140901', 3)
Insert roomlist (roomid, roomtypeid) values ('20140901', 2)
Insert roomlist (roomid, roomtypeid) values ('20140901', 2)
Go
Select * From roomlist
Insert guest values ('zhangsheng', '123', 'male', '(080) 027-87654321 ')
Insert guest values ('cui ying', '123', 'female ',' (080) 027-87654321 ')
Insert guest values ('Liu xianhui ', '123', 'male',' (080) 027-87654321 ')
Select * From guest
Go
Insert serveruse (guestid, serverid) values (10002, 'a01 ')
Insert serveruse (guestid, serverid) values (10002, 'a02 ')
Insert serveruse (guestid, serverid) values (10001, 'b01 ')
Insert serveruse (guestid, serverid) values (10001, 'a01 ')
Select * From serveruse
Go
Insert guestroom (roomid, guestid) values ('20140901', 1001)
Insert guestroom (roomid, guestid) values ('20140901', 1002)
Insert guestroom (roomid, guestid, indate) values ('201312', 1003, '2017-6-20 ')
Go
Update roomlist set roomstatus = 1
Where roomid in (select roomid from guestroom)
Select * From guestroom
Go
Insert serverabort (guestid, serverid) values (10001, 'a01 ')
Select * From serverabort
Go
-- Test record inserted
------------------------------------------------------------------

-- Design the corresponding View
-- Room view (including the number, type, price, and room status of a room)
Create view viewroom
As
Select a. roomid, B. roomtype, B. roomcost, room status =
(Case A. roomstatus when 1 then 'booked'
Else 'null' end)
From roomlist
Join roomtypelist B on A. roomtypeid = B. roomtypeid
Go
Select * From viewroom
Go
-- Customer view (including the customer's room number, customer number, customer name, and basic information)
Create view viewguest
As
Select a. roomid, B. guestid, B. guestname, B. guestsex, B. guestphone
From guestroom
Join Guest B on A. guestid = B. guestid
Go
Select * From viewguest
Select * From guest
Go
------------------------------------------------------------------

-- Create an index on the corresponding key

-- Create nonclustered Index name on table name (field name) 'Non-clustered Index'
-- Create unique index name on table name (field name) 'unique Index'
-- Create clustered Index name on table name (field name) 'clustered Index'
-- View the index sp_helpindex table name
-- Delete the index Dorp index table name. Index name

-- Create a non-clustered index for the customer name in the customer table
Create nonclustered index indguestname on guest (guestname)
Go
------------------------------------------------------------------

-- Create a stored procedure

-- 1: Number of rooms in use. If roomstatus is set to 1, the room is occupied.
Create proc roomuse @ temp int output
As
Select @ temp = count (*) from roomlist where roomstatus = 1
Go
Declare @ count int
Execute roomuse @ count output
Select @ count as 'number of currently occupied house'
Go

-- 2. Number of free rooms. The value of roomstatus = 0 indicates that the room is empty.
Create proc roomisvoid @ roomcount int output
As
Select @ roomcount = count (*) from roomlist where roomstatus = 0
Go
Declare @ count int
Execute roomisvoid @ count output
Select @ count as 'current number of free house'
Go

-- 3. Empty room list. The value of roomstatus = 0 indicates empty room.
Create proc roomvoidlist
As
Select roomid as 'EMPTY room number' from roomlist where roomstatus = 0
Order by roomid
Go
Exec roomvoidlist
Go

-- 4: Empty double room list. It is specified that roomstatus = 0 is the vacancy status.
Create proc roomdoublevoid
As
Select roomid, 'EMPTY double room' from roomlist where roomtypeid in (
Select roomtypeid from roomtypelist where roomtype = 'double superior room ')
And roomstatus = 0
Order by roomid
Go
Exec roomdoublevoid
Go

-- 5, empty single room list
Create proc roomonevoid
As
Select roomid, 'EMPTY single room' from roomlist
Where roomtypeid in (
Select roomtypeid from roomtypelist where roomtype = 'single-user advanced house'
And roomstatus = 0) order by roomid
Go
Exec roomonevoid
Go

-- 6, service list (procserverlist)
-- The table name and process name cannot be the same
Create proc procserverlist
As
Select 'service name' = servername, 'service price' = servercost
From serverlist order by serverid
Go
Exec procserverlist
Go
-- 7. view the detailed information (including the room number) of a customer by name)
/* Alter proc guestinfobyname @ guestname char (10)
As
Select * From guest where guestname = @ guestname
Union
Select * From guestroom where guestid in
(Select guestid from guest where guest = @ guestname)
*/
Create proc guestinfobyname @ guestname char (10)
As
Select B. roomid, A. * From guest a join guestroom B
On a. guestid = B. guestid
Where a. guestname = @ guestname
Go
Exec guestinfobyname 'zhangsheng'
Go

-- 8. You can view the customer information based on the room number.
Create proc guestinfobyroom @ roomid char (6)
As
Select * From guest where guestid in
(Select guestid from guestroom where roomid = @ roomid)
Go
Exec guestinfobyroom '000000'
Go

-- 9. Room price (based on the entered room number or room type)
Create proc roomcost @ roomid char (6) = '%', @ roomtype char (20) = '%'
As
Select a. roomid, room status =
(Case A. roomstatus when 1 then 'booked'
Else 'null' end), B. roomtype, B. roomcost
From roomlist
Join roomtypelist B on A. roomtypeid = B. roomtypeid
Where a. roomid = @ roomid or B. roomtype = @ roomtype
Go
Exec roomcost '123 ',''
Exec roomcost '', 'deluxe room'
Go

-- 10, can calculate the customer's checkout fee
Create proc totalcost @ guestid int, @ total money output
As
Declare @ servercost money, @ roomcost money
Select @ servercost = sum (servercost)
From serverlist
Where serverid in
(Select serverid
From serveruse
Where guestid = @ guestid)
Select @ roomcost = roomtypelist. roomcost
From roomtypelist
Where roomtypeid in
(Select roomtypeid
From roomlist
Where roomlist. roomid in -- a subquery is required.
(Select roomid
From guestroom
Where guestid = @ guestid ))
Select @ roomcost = @ roomcost * (select datediff (day, indate, outdate)
From guestroom
Where guestid = @ guestid)
Select @ Total = @ servercost + @ roomcost
Go
Declare @ servercost money, @ roomcost money
Exec totalcost 10001, @ servercost output
Select 'check-out amount' = (Case @ servercost when null then '0' end)
Go

-- 11. Based on the customer number, the Service list and payable amount of the customer's consumption on a specific date can be displayed.
Create proc serverlistbyguestid @ guestid int, @ time datetime -- count the amount
As
Select serverid, 'amount payable '= sum (servercost)
From serverlist
Where serverid in
(Select serverid
From serveruse
Where guestid = @ guestid and servertime = @ time)
Group by serverid
Go
Exec serverlistbyguestid 10003, '2017-6-24'

Go

-- 12. count the number of currently active customers (when the value of outdate is null indicates that the guest is staying)
Create proc guestnumber
As
Select count (*) as guestinroom from guestroom where outdate is null
Go
Exec guestnumber
Go

-- 13. display the service list enjoyed by the guests Based on the room number
Create proc serverbyroom @ roomid char (6)
As
Begin
If (select count (*) from guestroom where roomid = @ roomid) <> 0
Select a. serverid, A. servername, A. servercost from serverlist
Where serverid in
(Select serverid from serveruse where guestid in
(Select guestid from guestroom where roomid = @ roomid ))
Else
Select 'invalid room number or unused room number'
End
Go
Exec serverbyroom '000000'
Exec serverbyroom '000000'
-- Enter invalid room number or unused room number
Exec serverbyroom '000000'
Go

-- 14. delete a service enjoyed by a customer -- whether the service date should be included --
Create proc deleserveruse @ guestname char (6), @ servername char (20)
As
Delete serveruse
Where guestid =
(Select guestid from viewguest where guestname = @ guestname)
And serverid =
(Select serverid from serverlist where servername = @ servername)
Go
Exec deleserveruse 'zhangsheng', 'car wash-chelay'
Select * From serveruse
Go

-- 15. query the amount payable by the customer at any time
Create proc guestcost @ guestid int, @ total money output
As
Declare @ servercost money, @ roomcost money
Select @ servercost = sum (servercost)
From serverlist
Where serverid in
(Select serverid
From serveruse
Where guestid = @ guestid)
Select @ roomcost = roomtypelist. roomcost
From roomtypelist
Where roomtypeid in
(Select roomtypeid
From roomlist
Where roomlist. roomid in -- settlement with the above
(Select roomid
From guestroom
Where guestid = @ guestid ))
Select @ roomcost = @ roomcost *
(Select datediff (day, (select indate
From guestroom
Where guestid = @ guestid), getdate ())
From guestroom
Where guestid = @ guestid)
Select @ Total = @ servercost + @ roomcost
Go

-- 16. service projects can be appended.
Create proc addserverlist @ serverid char (6), @ servername char (20), @ servercost money
As
Insert into serverlist values (@ serverid, @ servername, @ servercost)
Go
Exec addserverlist '02', 'apple juice ', 12
Select * From serverlist
Go

-- 17. The housing type may increase
Create proc addroomtype @ roomtypeid int, @ roomtype char (20), @ roomcost money
As
Insert into roomtypelist values (@ roomtypeid, @ roomtype, @ roomcost)
Go
Exec addroomtype 4, 'regular room', 108
Select * From roomtypelist
Go

-- 18. No default value is set for the card number field in the 'Card No. 'field after registering the guest information
Create proc addguest
@ Guestcardno char (20 ),
@ Guestname char (10 ),
@ Guestsex char (2 ),
@ Guestphone char (20)
As
Insert into guest values (@ guestcardno, @ guestname, @ guestsex, @ guestphone)
Go
Exec addguest 'keen', '000000', 'male', '(080) 027-12345678'
Select * From guest
Go

-- 19. Registration room ticket
Create proc addroomuse @ roomid char (6), @ guestid int
As
Insert into guestroom (roomid, guestid) values (@ roomid, @ guestid)
Go
Exec addroomuse '000000', 1004
Select * From guestroom
Go
---------------------------- Keen graduation project -------------------------------------------------

-- Trigger
-- 1. There should be a trigger for customer registration to add a customer record (default) to the transaction table)
Create trigger trgaddguest on guest
For insert
As
Declare @ guestid int
Select @ guestid = guestid from inserted
Insert checkmoney (guestid) values (@ guestid)
Go
-- 2. Once the customer enjoys any service, the transaction must be recorded in the transaction table and the service fee must be updated.
Create trigger trgaddserveruse on serveruse
For insert
As
Declare @ servercost money
Select servercost from serverlist where serverid = (select serverid from inserted)
-- Calculate the cost of current customers
Update checkmoney set totalcost = totalcost + @ servercost where guestid = (select guestid from inserted)
Go
Select * From serverlist
Go
-- 3. On the day of checkout, the room fee and total fee of the Computer customer must be updated to null.
-- When a guest checks out, the outtime of the room ticket must be updated.
Create trigger trgguestout on guestroom
For Delete
As
Declare @ servercost money, @ roomcost money, @ total money, @ guestid int
-- Get the customer number
Select @ guestid = guestid from deleted
-- Calculate the total cost of other Customer Services
Select @ servercost = sum (servercost)
From serverlist
Where serverid in
(Select serverid from serveruse where guestid = @ guestid)
-- Calculates the customer's room fee
Select @ roomcost = roomtypelist. roomcost
From roomtypelist
Where roomtypeid in
(Select roomtypeid from roomlist
Where roomlist. roomid in
(Select roomid from guestroom where guestid = @ guestid ))
Select @ roomcost = @ roomcost * (select datediff (day, indate, outdate)
From guestroom where guestid = @ guestid)
-- Update the total fee for customer checkout
Select @ Total = @ servercost + @ roomcost
Select @ total
-- Update the room status to null (0)
Update roomlist set roomstatus = 0
Where roomid in (select roomid from deleted)
Go

-- 4. If consumption is canceled, the consumption amount should be automatically reduced.
Create trigger trgserverabort on serverabort
For insert
As
Declare @ servercost money
Select servercost from serverlist where serverid = (select serverid from inserted)
-- Update current customer spending
Update checkmoney set totalcost = totalcost-@ servercost
Where guestid = (select guestid from deleted)
Go
------------------------------------------------------------------
---------------------------- Debugging completed -------------------------------
------------------------------------------------------------------
(This article is my graduation project. If you have reprinted or referenced it, please indicate the source .)