02 - room reservation system database - user and room reservation data operation
Project description
With the increasing popularity of online room reservation, the continuous improvement of policies and regulations and the continuous improvement of technical level, online room reservation will become one of the main means of commercial transactions. This project mainly operates the user table and room reservation table in the room reservation system.
The database operation requirements of the room reservation system are as follows:
1) Database HotelDB.
2) The structure of member information table member is shown in table J2-2-1.
Table J2-2-1 member table
| Field name | Field description | data type | Null allowed | remarks |
|---|---|---|---|---|
| ID | User ID | integer | no | Primary key, self incrementing (increment is 1) |
| Username | user name | Characters (20) | no | |
| Pwd | password | Characters (20) | no | |
| Linkman | User name | Characters (30) | no | |
| Phone | Telephone | Characters (18) | no |
3) The structure of the room reservation information table subscription is shown in table J2-2-2.
Table J2-2-2 subscription table
| Field name | Field description | data type | Null allowed | remarks |
|---|---|---|---|---|
| ID | Order ID | integer | no | Primary key, self incrementing (increment is 1) |
| M_ID | Order subscriber ID | integer | no | Foreign key |
| No | order number | Characters (30) | no | |
| Room | Reservation room No | Characters (30) | no |
4) Establish an association between the two tables. The ID of the member is the same as the m of the subscription_ ID Association.
5) Table basic data of member is shown in table J2-2-3.
Table j2-2-3 basic data of member
| ID | Username | Pwd | User name | contact number |
|---|---|---|---|---|
| 1 | rena | rena | Huang Hao | 13145687369 |
| 2 | john | john | Li Yang | 13678903456 |
| 3 | jack | jack | Zhang San | 13324687986 |
(6) Table basic data of subscription is shown in table J2-2-4.
Table J2-2-4 subscription basic data
| ID | M_ID | No | Room |
|---|---|---|---|
| 1 | 2 | 20100416-232641968 | 301 |
| 2 | 2 | 20101222-231152203 | 302 |
| 3 | 1 | 20110222-231152203 | 411 |
(1) Task description
**Task 1: * * create the room reservation system database HotelDB in SQL language
1) Judge whether there is a database with HotelDB name in the system. If so, delete it; If not, create the database HotelDB.
2) The initial value of the main database file is 10MB, and the maximum is 20MB. It grows automatically.
3) The initial value of the log file is 5MB and the maximum is 10MB, which grows automatically.
IF DB_ID('HotelDB') IS NOT NULL DROP DATABASE HotelDB
GO
CREATE DATABASE HotelDB
ON PRIMARY
(
NAME=HotelDB,
FILENAME='E:\xxx\HotelDB.mdf',
SIZE=10MB,
MAXSIZE=20MB
)
LOG ON
(
NAME = HotelDB_log,
FILENAME = 'E:\xxx\HotelDB_log.ldf',
SIZE = 5MB ,
MAXSIZE=10MB
)
GO
**Task 2: * * create member information table member and room reservation information table subscription in SQL language
1) Create database tables according to the provided table J2-2-1 and table J2-2-2 structure, and set primary keys and foreign keys.
create table member ( ID int not null primary key identity(1,1), Username char(20) not null, Pwd char(20) not null, Linkman char(30) not null, Phone char(18) not null, ) create table subscription ( ID int not null primary key identity(1,1), M_ID int not null , No char(30) not null, Room char(18) not null, foreign key (M_id) references member(ID) )
**Task 3: * * operate the member information table member and the room reservation information table subscription in SQL language
1) According to the data in table J2-2-3 and table J2-2-4, add the data to member information table and room reservation information table subscription.
2) Find out the room number booked by "Huang Hao" customer.
3) Change the room booked by "Huang Hao" customer to "Zhang San" customer
4) Delete the order information of "Li Yang" customer.
insert into member values('rena','rena','Huang Hao','13145687369'),('john','john','Li Yang','13678903456'),('jack','jack','Zhang San','13324687986')
insert into subscription values('2','20100416-232641968','301'),('2','20101222-231152203','302'),('1','20110222-231152203','411')
select member.Linkman,subscription.Room from subscription,member where member.Linkman='Huang Hao'and subscription.M_ID = member.ID
update subscription set M_ID = (select member.ID from member where member.Linkman='Zhang San')
where subscription.M_ID=(select member.ID from member where member.Linkman='Huang Hao')
delete from subscription where subscription.M_ID = (select member.ID from member where member.Linkman='Li Yang')