--报名表（ZX_apply）代码 CREATE TABLE ZX_apply (
G_ID CHAR(8) NOT NULL,--旅行团编号 C_ID CHAR(8) NOT NULL,--客户编号 AP_TIME DATE NOT NULL,--报名时间 AP_COST CHAR(8) NOT NULL,--报名费用
FOREIGN KEY(G_ID)REFERENCES ZX_group(G_ID),--外键约束 FOREIGN KEY(C_ID)references ZX_client(C_ID)--外键约束 );
2.3 建立约束 2.3.1默认约束
代码：
--建立默认约束
CREATE DEFAULT ZX_SEX AS '男' CREATE DEFAULT ZX_AGE AS '20' --绑定到列
EXEC sp_bindefault 'ZX_SEX','ZX_staff.S_SEX' EXEC sp_bindefault 'ZX_AGE','ZX_staff.S_AGE' EXEC sp_bindefault 'ZX_SEX','ZX_client.C_SEX' EXEC sp_bindefault 'ZX_AGE','ZX_client.C_SEX'
2.3.2检查约束
代码：
--建立检查约束 --1 员工staff
ALTER TABLE ZX_staff ADD CONSTRAINT ck_zx_1
CHECK(S_NATURE IN ('导游','其他')); --员工性质
ALTER TABLE ZX_staff ADD
CONSTRAINT ck_zx_2
CHECK(S_SEX IN ('男','女'));--员工性别
16
ALTER TABLE ZX_staff ADD
CONSTRAINT ck_zx_3
CHECK(LEN(S_IDCARD)=18);--员工身份证号
--2 客户client
ALTER TABLE ZX_client ADD
CONSTRAINT ck_zx_5
CHECK(C_NATURE IN ('新','老'));-客户性质
ALTER TABLE ZX_client ADD
CONSTRAINT ck_zx_6
CHECK(C_SEX IN ('男','女'));--客户性别
ALTER TABLE ZX_client ADD
CONSTRAINT ck_zx_7
CHECK(LEN(C_IDCARD)=18);
ALTER TABLE ZX_client ADD
CONSTRAINT ck_zx_8
CHECK(LEN(C_CONTACT )=11);客户联系电话
--3 住宿 hotel
ALTER TABLE ZX_hotel ADD
CONSTRAINT ck_zx_9
CHECK(H_LEVEL IN ('一','二','三','四','五'));--酒店星级
-- 4票务 ticket
ALTER TABLE ZX_ticket ADD
CONSTRAINT ck_zx_10
CHECK(TI_NATURE IN ('火车票','汽车票','飞机票'));--票务性质
17
2.4 数据录入
录入数据代码：
--插入数据
--向旅行社中插入数据 INSERT INTO ZX_zx VALUES
('Z100','尊虚旅行社','尊虚旅行社是一家综合性的国内旅游企业。','北京','15623567652');
--旅游团数据
INSERT INTO ZX_group VALUES
('G001','成都单人两天游','20151228','20151229',10,'Z100','包头'), ('G002','包头单人两天游','20151228','20151229',10,'Z100','包头'), ('G003','天津单人两天游','20151228','20151229',10,'Z100','包头'), ('G004','大连单人两天游','20151228','20151229',10,'Z100','包头'), ('G005','杭州单人两天游','20151228','20151229',10,'Z100','包头'), ('G006','北京单人两天游','20151228','20151229',10,'Z100','包头'), ('G007','湖南单人两天游','20151228','20151229',10,'Z100','包头'), ('G008','青岛单人两天游','20151228','20151229',10,'Z100','包头'), ('G009','银川单人两天游','20151228','20151229',10,'Z100','包头'), ('G010','太原单人两天游','20151228','20151229',10,'Z100','包头');
select * from ZX_group 数据输入效果演示：
图2.4.1 旅行团信息表
18
-- 游程
INSERT INTO ZX_travel VALUES
('T001','参观成都景点','成都景点......','20151228','成都'), ('T002','参观包头景点','成都景点......','20151228','包头'), ('T003','参观天津景点','天津景点......','20151228','天津'), ('T004','参观大连景点','大连景点......','20151228','大连'), ('T005','参观杭州景点','杭州景点......','20151228','杭州'), ('T006','参观北京景点','北京景点......','20151228','北京'), ('T007','参观湖南景点','湖南景点......','20151228','湖南'), ('T008','参观青岛景点','青岛景点......','20151228','青岛'), ('T009','参观银川景点','银川景点......','20151228','银川'), ('T010','参观太原景点','太原景点......','20151228','太原');
select * from ZX_travel
图2.4.2 游程信息表
--员工表
INSERT INTO ZX_staff VALUES
('D001','路飞','男',21,'导游','152322199611033521','18247256780'), ('D002','索隆','男',21,'导游','152322199612013521','18247256781'), ('D003','香吉','男',21,'导游','152322199612223521','18247256782'), ('D004','阿美','女',21,'导游','152322199604013521','18247256783'), ('D005','鬼青','男',21,'导游','152322199611093521','18247256786'), ('Q001','小丑','男',21,'其他','152322199611023521','18247256333'), ('Q002','七王','男',21,'其他','152322199605063521','18247256745'), ('Q003','赵吏','男',21,'其他','152322199603223521','18247256778'), ('Q004','吴涛','男',21,'其他','152322199607023521','18247256785'), ('Q005','上官','男',21,'其他','152322199612113521','18247256789');
19
select * from ZX_staff
图2.4.3 员工信息表
--住宿
INSERT INTO ZX_hotel VALUES
('J001','花儿酒店','一','成都民主路','15661493041'), ('J002','安心酒店','二','包头幸福路','15661493042'), ('J003','寝宫酒店','四','天津富强路','15561493043'), ('J004','云山酒店','三','大连民主路','15661493044'), ('J005','浅语酒店','五','杭州友谊路','15661493075'), ('J006','华山酒店','三','北京建设路','15661493046'), ('J007','千玺酒店','二','湖南胜利路','15661493047'), ('J008','飞宫酒店','四','青岛民主路','15561493048'), ('J009','敏思酒店','三','银川幸福路','15661493049'), ('J010','华商酒店','五','太原富强路','15661493010'); select * from ZX_hotel
图2.4.4酒店信息表
20