【正文】 rthOfDate DATE NOT NULL,出生日期CREW_TELEPHONE VARCHAR(50) NOT NULL,電話CAMPANY_NUMBER1 VARCHAR(50) NOT NULL,公司編號CONSTRAINT FK_Crew_CAMPANY_NUMBER FOREIGN KEY(CAMPANY_NUMBER1)references CAMPANY(CAMPANY_NUMBER),CONSTRAINT FK_Crew_FIGHT_NUMBER FOREIGN KEY(FIGHT_NUMBER)references FIGHT(FIGHT_NUMBER),)。男39。,39。,39。,39。,39。,39。,39。,39。,39。,39。,39。,39。,39。,39。,39。,39。), (39。,39。,39。,39。,39。), (39。,39。,39。,39。,39。), (39。,39。,39。,39。,39。), (39。,39。,39。,39。,39。), (39。,39。,39。), (39。,39。,39。,39。,39。,39。,39。), (39。,39。,39。,39。,39。,39。,39。,39。,39。,39。,39。,39。,39。), (39。,39。,39。,39。,39。,39。,39。)SELECT * FROM BUY_TICKETSuse 航空機票預售系統(tǒng)INSERT INTO REFUND(REFUND_TIME,PASSAGER_ID)VALUES(39。,39。), (39。,39。,39。), (39。,39。,39。,分組查詢統(tǒng)計每個公司的業(yè)務員人數(shù)，要求顯示公司號，公司名，業(yè)務員員工人數(shù)USE 航空機票預售系統(tǒng)SELECT CAMPANY_NUMBER,COMPANY_NAME,COUNT(EmployeeNumber)FROM CAMPANY INNER JOIN Clerkon =group by CAMPANY_NUMBER,COMPANY_NAME。15040319941020000639。女39。Booking success! Wele to use the next!39。參考文獻《數(shù)據(jù)庫系統(tǒng)教程》（第三版）,施伯樂，丁寶康，汪衛(wèi)編著，高等教育出版社，2008年。在本次課程設(shè)計和過程中，由于時間不是很長，再加上要求獨立完成，系統(tǒng)功能可能不是很全面，程序中還存在很多缺陷，希望老師能給予批評和指導。1234567890139。15020119951006000739。已售39。只能有一個公司，插入失?。。。?9。,39。,39。,39。), (39。,39。,39。)INSERT INTO SELL_TICKETS(SELL_TIME,TICKET_SUM,EmployeeNumber)VALUES(39。,39。,39。,39。), (39。,39。,39。,39。,39。,39。,39。), (39。,39。,39。,39。,39。,39。,39。,39。,39。,39。,39。,39。,39。), (39。,39。,39。,39。,39。,39。,39。,39。,39。), (39。,39。,39。,39。,39。), (39。,39。,39。,39。,39。), (39。,39。,39。,39。,39。), (39。,39。,39。,39。,39。), (39。,39。,39。,39。,39。,39。,39。,39。,39。,39。,39。,39。,39。ALTER TABLE BUY_TICKETSADDCONSTRAINT CK_REAL_PAY CHECK(REAL_PAY=0)ALTER TABLE BUY_TICKETSADDCONSTRAINT CK_BUY_TICKETS_LEN CHECK(LEN(PASSAGER_ID)=18)CREATE TABLE REFUND退票表(REFUND_TIME VARCHAR(50),退票時間PASSAGER_ID VARCHAR(50),旅客身份證號TICKET_ID INT IDENTITY(1,1) NOT NULL,機票編號CONSTRAINT PK_退旅客機票 PRIMARY KEY(PASSAGER_ID,TICKET_ID),CONSTRAINT FK_REFUND_PASSAGER_ID FOREIGN KEY(PASSAGER_ID) references PASSAGER(PASSAGER_ID),CONSTRAINT FK_REFUND_TICKET_ID FOREIGN KEY(TICKET_ID) references TICKET(TICKET_ID),)。女39。)) ALTER TABLE CLERKADDCONSTRAINT CK_CLERK_LAN CHECK(LEN(CLERK_TELEPHONE)=11)CREATE TABLE BUY_TICKETS買票表(TICKET_COUNT VARCHAR(50) NOT NULL,數(shù)量DISCOUNT VARCHAR(50) NOT NULL,折扣GET_TICKETS_TIME DATE,取票時間BUY_TICKETS_TIME DATE NOT NULL,購買時間REAL_PAY VARCHAR(50) NOT NULL,實付款PASSAGER_ID VARCHAR(50) NOT NULL,旅客身份證號TICKET_ID int NOT NULL,機票編號CONSTRAINT PK_旅客機票 PRIMARY KEY(PASSAGER_ID,TICKET_ID),CONSTRAINT FK_BUY_PASSAGER_ID FOREIGN KEY(PASSAGER_ID) references PASSAGER(PASSAGER_ID),CONSTRAINT FK_BUY_TICKET_ID FOREIGN KEY(TICKET_ID) references TICKET(TICKET_ID),)。,39。業(yè)務員工號→性別，業(yè)務員工號→公司編號。根據(jù)以上分析可以畫出系統(tǒng)ER圖，：旅客購買退票數(shù)量實付款購買時間折扣取票時間退票時間 M M賣票時間 N N機票屬于航班擁有屬于乘務人員公司售票員售票包含座位屬于收益金額 NN M 1 N 1 1 N N 1 N 1列名數(shù)據(jù)類型允許NULL值鍵描述PASSAGER_NAMEVarchar（50）否主鍵旅客姓名PASSAGER_IDVarchar（50）否身份證PASSAGER_TELEPHONEVarchar（50）否旅客電話PASSAGER_SEXVarchar（50）否旅客性別CONTACT_NAMEVarchar（50）否聯(lián)系人姓名CONTACT_TELEPHONEVarchar（50）否聯(lián)系人電話列名數(shù)據(jù)類型允許NULL值鍵描述TICKET_IDINT否主鍵機票編號FIGHT_NUMBERVARCHAR(50)否外鍵航班號TICKET_TYPEVARCHAR(50)否機票類型TICKET_PRICEVARCHAR(50)否定價狀態(tài)VARCHAR(50)否售票狀態(tài)S_TICKETVARCHAR(50)是剩余票數(shù)列名數(shù)據(jù)類型允許NULL值鍵描述SELL_TIMEvarchar(50)否售票時間TICKET_SUMvarchar(50)否收益金額EmployeeNumbervarchar(50)否主鍵員工號TICKET_IDdecimal(18, 0)否主鍵機票編號列名數(shù)據(jù)類型允許NULL值鍵描述TICKET_COUNTvarchar(50)否票數(shù)DISCOUNTvarchar(50)否折扣GET_TICKETS_TIMEDate是取票時間BUY_TICKETS_TIMEDate否買票時間REAL_PAY