内蒙古科技大学课程设计说明书

[ticket_id] [int] NOT NULL PRIMARY KEY, [ticket_num] [varchar](10) NOT NULL, [ticket_out] [varchar](10) NULL, [ticket_destination] [varchar](10) NULL, [ticket_time] [date] NULL, [ticket_pirce] [money] NULL, [tourist_id] [int] NOT NULL,);

CREATE TABLE [staff] (

[staff_id] [int] NOT NULL PRIMARY KEY, [staff_name] [varchar](10) NULL,

[staff_sex] [varchar](2) NULL DEFAULT ('男'), [staff_tel] [varchar](17) NULL, [staff_academic] [varchar](10) NULL, [staff_work] [varchar](40) NULL,);

CREATE TABLE [tourist] (

[tourist_id] [int] NOT NULL PRIMARY KEY, [tourist_name] [varchar](10) NOT NULL, [tourist_sex] [varchar](2) NULL DEFAULT ('男') , [tourist_num] [varchar](18) NULL, [tourist_address] [varchar](30) NULL, [tourist_age] [int] NULL, [tourist_tel] [varchar](17) NULL, [tourist_occupation] [varchar](8) NULL, [tourist_income] [int] NULL,

33

内蒙古科技大学课程设计说明书

[Email] [varchar](18) NULL, [group_id] [int] NOT NULL, [staff_id] [int] NOT NULL);

CREATE TABLE [route] (

[route_id] [int] NOT NULL PRIMARY KEY, [route_name] [varchar](10) NOT NULL, [route_introduce] [varchar](50) NULL, [route_traffic] [varchar](50) NULL, [route_itinerary] [varchar](50) NULL, [group_id] [int] NOT NULL,);

CREATE TABLE [hotel] (

[hotel_id] [int] NOT NULL PRIMARY KEY, [hotel_name] [varchar](10) NOT NULL, [hotel_star] [int] NULL, [hotel_room] [int] NULL,

[hotel_address] [varchar](30) NULL, [hotel_person] [varchar](5) NULL, [hotel_tel] [varchar](17) NULL, [hotel_introduce] [varchar](50) NULL, [hotel_surroundings] [varchar](50) NULL, [tourist_id] [int] NOT NULL,);

CREATE TABLE [serve] (

[serve_event] [char](10) NOT NULL,

34

内蒙古科技大学课程设计说明书

[serve_time] [date] NULL, [tourist_id] [int] NOT NULL, [staff_id] [int] NOT NULL);

建Check约束：

alter table tourist add

constraint CK_tourist_sex check(tourist_sex='男' or tourist_sex='女'); alter table staff add

constraint CK_staff_sex check(staff_sex='男' or staff_sex='女'); alter table tourist add

constraint CK_tourist_num check(tourist_num<999999999999999999); alter table hotel add

constraint CK_hotel_star check(hotel_star<99999);

建唯一约束：

alter table tour add

constraint UQ_group_name UNIQUE(group_name);

建单表及多表视图：

create view view_tourist as

select tourist.tourist_id,tourist.tourist_name,tourist.tourist_num,tourist.tourist_address FROM tourist create view view_tr as

select tour.group_id,group_name,group_person,route.route_id,route_name FROM tour,route;

建存储过程：

create procedure SelProc

35

内蒙古科技大学课程设计说明书

as

select * from hotel;

create procedure Count1Proc as

select * from route

添加数据：

insert into tour

(group_id,group_name,group_person,group_start,group_back,route_id,staff_id) values('310','东方红','15','2013-1-1','2013-1-3','430','150'), ('311','红月亮','20','2013-1-5','2013-1-7','431','151'), ('312','雪峰','18','2013-3-4','2013-3-10','432','152'), ('313','在我心中','27','2013-4-7','2013-4-9','433','153'), ('314','昨天','10','2013-5-1','2013-5-20','434','154'), ('315','今天','17','2013-5-16','2013-5-20','435','155'), ('316','明天','20','2013-10-1','2013-10-11','436','156'), ('317','姗姗来迟','15','2013-7-8','2013-7-18','437','157'), ('318','月明星稀','10','2013-8-9','2013-8-10','438','158'), ('319','红高粱','20','2013-8-17','2013-8-20','439','159');

insert into staff(staff_id,staff_name,staff_sex,staff_tel,staff_academic,staff_work) values('150','蔡依琳','女','1122330','本科','2011-2012年兼职导游，年正式导游'), ('151','尹浩','男','1122331','大专','2011-2012年兼职导游，年正式导游'), ('152','陈伟光','男','1122332','本科','2010-2012年兼职导游，年正式导游'), ('153','张佳环','女','1122333','硕士','2009-2012年正式导游，年导游组长'), ('154','李俊英','男','1122334','本科','2011-2012年兼职导游，年正式导游'), ('155','王晓杰','男','1122335','大专','2011-2012年兼职导游，年正式导游'), ('156','尹静琳','女','1122336','大专','2009-2012年兼职导游，年正式导游'),

36