考查内容：

1.数据库设计知识。 2.需求分析、实体以及实体间联系的抽象。 3.绘制E-R图。 4.使用PowerDesigner设计概念数据模型和物理数据模型。 5. 聚合函数、分组查询、联接查询和子查询综合应用。

重点实践目标：

1.非相关子查询和相关子查询。 2. EXISTS查询。 3.在DML语句中使用子查询。 # 一、系统需求分析： 1.满足航空售票的基本业务流程和功能需求。 2.该系统支持的主要功能包括： n提供航班信息、乘客信息的维护。 n提供满足乘客需求的航班信息、航班余票显示等功能。 n票务预订自动化。 n帮助民航管理部门和航空公司对机票营业额以及票务数据进行全方位的查询、统计和分析。 3.系统具体的需求信息如下： 每个航空公司可以经营多条航班，但每一条航班仅隶属于某个航空公司。 飞机舱位有三个等级，分别为头等舱、商务舱和普通舱。 每位乘客可以订购多条航班的机票；每个航班在有余票的情况下，可以被多名乘客预订。 系统支持乘客为订票而查询票源情况，要求显示舱位信息、座位信息和剩余座位数信息。

二、确定实体之间的联系

考核任务：查询、统计航班信息和票务信息 - 图1

n根据在系统需求分析阶段所得到的具体需求信息，可以对收集到的数据进行分类、组织和概括，形成实体。分别如下**：** - 乘客（身份证、姓名、性别、出生年月、电话和地址） - 航空公司（航空公司编号、公司名称、公司地址和服务热线） - 航班（航班编号、机型、出发城市、到达城市、里程数和起飞时间） - 舱位（舱位编号、舱位等级、座位数、剩余座位数和全价） - 购买机票（订票编号、舱位等级、登机日期、座位、价格和登机口）

三、概念结构设计

考核任务：查询、统计航班信息和票务信息 - 图2

四、逻辑结构设计

1.逻辑结构设计就是将在概念结构设计阶段所设计完成的基本E-R图，转换为与选用的DBMS产品所支持的数据模型相符合的逻辑结构。 2.由于目前我们所使用的数据库大多为关系数据库，因此数据库逻辑结构设计的任务就是将E-R图转换为关系模式。

E-R图转换为关系模式的一般规则：

1. <font style="color:black;">将每一个实体转换为一个关系，关系名与实体同名，实体的属性即为关系的属性，实体的码即为关系的主键。</font>
2. <font style="color:#00cc00;"></font><font style="color:black;">如果两个实体间的联系是1:m，则在m端所对应的关系上添加1端实体的码，作为m端关系的外键。</font>
3. <font style="color:black;">如果两个实体间的联系是m:n，则生成一个新的关系，在此新关系上分别添加m端和n端实体的码，作为新关系的外键。</font>

航空售票系统的关系模式：

n乘客（身份证、姓名、性别、出生年月、电话、地址）。 n航空公司（航空公司编号、公司名称、公司地址、服务热线）。 n航班（航班编号、机型、出发城市、到达城市、里程数、起飞时间、航空公司编号）。 n舱位（舱位编号、舱位等级、座位数、剩余座位数、全价、航班编号）。 n购买机票（订票编号、登机日期、舱位等级、座位、价格、登机口、身份证、航班编号）。

五、生成表结构（转化为物理模式）

使用PowerDesigner可以将物理数据模型生成所选定的数据库。步骤为：PowerDesigner→Database（数据库）→Generate Database（生成数据库）。在此选择MySQL 5.0，即可生成航空售票系统MySQL数据库表结构的创建代码。

六、业务数据应用（任务清单）

教师指导：

1.查询从武汉飞往北京的航班信息，要求显示航班号、航空公司名称、机型、始发地、目的地、里程数和起飞时间，按照起飞时间升序显示。（10分）

SELECT f.flightID 航班号,ac.companyName 航空公司名称,
                f.model 机型,f.fromCity  始发地,
                f.toCity 目的地,f.mileAge 里程数,
                f.departureTime 起飞时间
FROM flight f
NATURAL JOIN aircompany ac
WHERE fromCity = '武汉' AND toCity = '北京' -- 武汉飞往北京的
ORDER BY 起飞时间;

考核任务：查询、统计航班信息和票务信息 - 图3

2.旅客或机场管理人员查询2013年12月1日，由武汉飞往北京的所有航班的机票销售情况，要求显示不同航班不同舱位等级的机票销售信息。（10分）

考核任务：查询、统计航班信息和票务信息 - 图4

3.旅客或民航管理局可以查询2013年12月1日，由武汉飞往北京航班的机票销售情况，要求显示每个航班不同舱位等级的机票销售信息，显示信息包括：航班号、舱位等级、总票数、已售票数和剩余票数。（10分）

SELECT t1.flightID 航班号,t1.grade 舱位等级,t1.seats 总票数,
IF(t2.`已售票数` IS NULL,0,t2.`已售票数`) 已售票数 ,
(t1.seats - IF(t2.`已售票数` IS NULL,0,t2.`已售票数`)) 剩余票数
from 
(SELECT *  # 由武汉飞往北京所有航班的机票
     FROM cabin c
     WHERE c.flightID in(
      -- 2.1 由武汉飞往北京的所有航班
      (SELECT flightID
                   FROM flight f
                   WHERE f.fromCity = '武汉' AND f.toCity = '北京')
    )) t1
LEFT JOIN 
( # 第2题 查询
   SELECT ts.flightID ,ts.grade,COUNT(ordersID) 已售票数,SUM(price) 销售额
   FROM ticketsell ts
   WHERE ts.flightDate = '2013-12-1'  AND
   ts.flightID in
   -- 2.1 由武汉飞往北京的所有航班
   (SELECT flightID
                   FROM flight f
                   WHERE f.fromCity = '武汉' AND f.toCity = '北京')
   GROUP BY ts.flightID ,ts.grade
  ) t2  USING(flightID,grade);

考核任务：查询、统计航班信息和票务信息 - 图5

-- 2.用已售信息更新cabin表
UPDATE cabin c
LEFT JOIN 
        (SELECT ts.flightID ,ts.grade,COUNT(ordersID) 已售票数
        FROM ticketsell ts
        WHERE ts.flightDate = '2013-12-1'  AND
                ts.flightID in
                                    -- 2.1 由武汉飞往北京的所有航班
                                    (SELECT flightID
                                    FROM flight f
                                    WHERE f.fromCity = '武汉' AND f.toCity = '北京')
        GROUP BY ts.flightID ,ts.grade) t
USING(flightID,grade)
SET c.availableSeats = c.seats - IF(t.`已售票数` IS NULL,0,t.`已售票数`);
-- 查询，要求显示每个航班不同舱位等级的机票销售信息
SELECT c.flightID 航班编号,c.grade 舱位等级,c.seats 总票数,
    (c.seats - c.availableSeats) 已售票数,c.availableSeats  余票数
FROM cabin c
WHERE flightID in(
-- 2.1 由武汉飞往北京的所有航班
                                    (SELECT flightID
                                    FROM flight f
                                    WHERE f.fromCity = '武汉' AND f.toCity = '北京')
                    );

考核任务：查询、统计航班信息和票务信息 - 图6

合作探究：

旅客“杨博”或机场管理局查询 “杨博”所有的购票信息，要求输出航班号、出发城市、目的城市、机票价格、飞行日期和起飞时间。（10分）

考核任务：查询、统计航班信息和票务信息 - 图7

民航管理局查询各个航班的乘客人数和机票销售额，要求按照乘客人数和机票销售额升序显示。（10分**）**

考核任务：查询、统计航班信息和票务信息 - 图8

个人考核：

（1）查询所有飞往上海的航班信息。（10分）

（2）查询乘客“杨博”飞往上海的所有票务信息。要求输出航班号、出发城市、目的城市、机票价格、出行日期和起飞时间。（10分）

（3）统计所有飞往上海的票务信息。要求查询每个航班不同舱位等级的机票销售信息，要求显示信息包括：航班号、舱位等级、总票数、已售票数和剩余票数。（10分）

（4）统计每个航空公司所属航班的总乘客人数和总机票销售额，要求按照总乘客人数和总机票销售额升序显示。（10分）

项目总结：（10分）

（描述项目收获、不足和改进措施，不少于100字）

考核任务：查询、统计航班信息和票务信息