ÄÚÃɹſƼ¼´óѧ¿Î³ÌÉè¼ÆËµÃ÷Êé
[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