本篇涵盖软考数据库系统工程师，2019年上半年考试下午试题部分。

01 学生跟踪系统

场景描述

某学校欲开发一学生跟踪系统，以便更自动化、更全面地对学生在校情况(到课情况和健康状态等)进行管理和追踪，使家长能及时了解子女的到课情况和健康状态，并在有健康问题时及时与医护机构对接。

主要功能:

(1) 采集学生状态。通过学生卡传感器，采集学生心率、体温(摄氏度) 等健康指标及其所在位置等信息并记录。每张学生卡有唯一的标识(ID)与一个学生对应。

(2) 健康状态告警。在学生健康状态出问题时，系统向班主任、家长和医护机构健康服务系统发出健康状态警告，由医护机构健康服务系统通知相关医生进行处理。

(3) 到课检查。综合比对学生状态、课表以及所处校园场所之间的信息对学生到课情况 进行判定。对旷课学生，向其家长和班主任发送旷课警告。

(4) 汇总在校情况。定期汇总在校情况，并将报告发送给家长和班主任。

(5) 家长注册。家长注册使用该系统，指定自己子女，存入家长信息，待审核。

(6) 基础信息管理。学校管理人员对学生及其所用学生卡和班主任、课表(班级、上课时间及场所等)、校园场所(名称和所在位置区域)等基础信息进行管理:对家长注册申请进行审核，更新家长状态，将家长 ID 加入学生信息记录中使家长与其子女进行关联，向家长发送注册结果。一个学生至少有一个家长，可以有多个家长。课表信息包括班级、班主任.时间和位置等。

现采用结构化方法对学生跟踪系统进行分析与设计，获得如图 1-1 所示的上下文数据流图和图1-2 所示的0层数据流图。

问题

答案

问题-1：El:学生卡， E2:管理人员 , E3:班主任， E4:家长, E5:医护机构健康服务系统

问题-2：D1:学生状态, D2:学生, D3:校园场所, D4: 课表

(注:名称后面可以带有“信息”以及“文件”或“表”)

问题-3：

数据流	起点	终点
课表信息	D4 课表	P3 到课检查
场所信息	D3 校园场所	P3 到课检查
家长信息	D5 家长	P3 到课检查
课表信息	D4 课表	P4 汇总在校情况
场所信息	D3 校园场所	P4 汇总在校情况
家长信息	D5 家长	P4 汇总在校情况
家长ID	P6 基础信息维护	D2 学生
家长注册申请	P5 家长注册	P6 基础信息管理

按照要求写出其中3条即可

问题-4：

学生状态=学生卡 ID+心率+体温+位置+时间
学生信息=学生 ID+学生卡 ID+1家长 ID* +班主任I+班级

讲解

问题


答案


分析

02 创业孵化基地信息系统

场景描述

某创业孵化基地管理若干孵化公司和创业公司，为规范管理创业项目投资业务，需要开发一个信息系统。请根据下述需求描述完成该系统的数据库设计。

需求描述

(1) 记录孵化公司和创业公司的信息。孵化公司信息包括公司代码、公司名称、法人代表名称、注册地址和一个电话;创业公司信息包括公司代码、公司名称和一个电话。孵化公司和创业公司的公司代码编码不同。

(2) 统一管理孵化公司和创业公司的员工。员工信息包括工号、身份证号、姓名、性别、 所属公司代码和一个手机号，工号唯一标识每位员工。

(3) 记录投资方信息，投资方信息包括投资方编号、投资方名称和一个电话。

(4) 投资方和创业公司之间依靠孵化公司牵线建立创业项目合作关系，具体实施由孵化公司的一位员工负责协调投资方和创业公司的一个创业项目。一个创业项目只属于一个创业公司，但可以接受若干投资方的投资。创业项目信息包括项目编号、创业公司代码、投资方编号和孵化公司员工工号。

答案

讲解

问题


答案


分析

03 快递信息系统

场景描述

某快递公司对每个发出的快递进行跟踪管理，需要建立一个快递跟踪管理系统，对该公司承接的快递业务进行有效管理。

需求描述

1.公司在每个城市的每个街道都设有快递站点。这些站点负责快递的接收和投递。站点信息包括站点地址、站点名称、责任人、一部联系电话、开始营业时间、结束营业时间。每个站点每天的营业时间相同。每个站点只能有一个责任人。

2.系统内需记录快递员、发件人的基本信息。这些信息包括姓名、身份证号、一个联系地址、一部联系电话。快递站点的责任人由快递员兼任，且每个快递站点只有一个责任人。每个快递员只负责一个快递站点的揽件和快递派送业务。发件人和快递员需实名认证。

3.快递需要提供翔实的信息，包括发件人姓名、身份证号、一部发件人电话号码、发件人地址、收件站点、收件人姓名、收件地址、一部收件人电话、投递时间、物品类别、物品名称及物品价值。每个发件人和收件人在系统里只能登记一个电话和地址。

4.每个快递员接手一份快递后，需在系统中录入每个快递的当前状态信息，包括当前位置、收到时间、当前负责人和上一负责人。状态信息包括待揽件、投递中、已签收。如果快递已签收，应记录签收人姓名及一个联系电话。每个快递在一个站点只能对应一个负责的快递员。

注:试题不需要考虑快递退回的相关问题。

逻辑设计

根据上述需求，设计出如下关系模式:

快递 (快递编号，收件人姓名，收件地址编号，收件人电话，投递时间，物品类别，物品名称，物品价值)，其中收件地址编号是地址实体的地址编号。

快递员 (姓名，身份证号，电话号码，联系地址编号，工作站点编号)。

快递站点 (站点编号，站点名称，责任人编号，站点地址编号，开始营业时间，联系电话，结束营业时间)，责任人编号是负责该站点的快递员的身份证号。

地址(地址编号，所在省，所在市，所在街道，其他)，其他信息是需补充的地址信息。

快递投递 (快递编号，快递员编号，发件人姓名，发件人身份证号，发件人电话号码,发件人地址编号)，其中发件人地址编号为发件人地址的地址编号，揽件站点编号为接收该快递的站点编号。

快递跟踪 (快递编号，当前负责人编号，前一负责人编号，当前状态，收到时间，当前站点编号)。

快递签收 (快递编号，签收人姓名，签收人联系电话)

答案

问题-1： 对关系“快递投递”

(1) 候选键:快递编号

(2)不是 3NF。存在非主属性“发件人姓名”对候选键“快递编号”的传递依赖: 快递编号一发件人身份证号，发件人身份证号一发件人姓名。所以快递编号一发件人姓名，为传递依赖。所以，“快递投递”关系模式不满足第三范式。

(3) 分解后的关系模式: 快递投递 1(快递编号，快递员编号，发件人身份证号)。 快递投递 2(发件人姓名，发件人身份证号，发件人电话号码，发件人地址编号)。

问题-2： 对关系“快递跟踪”

(1) 候选键:(快递编号，当前负责人编号)。

(2)不是 2NF。候选键(快递编号，当前负责人编号)部分决定非主属性“当前站点编号” 。

(3)分解后的关系模式: 快递跟踪1(快递编号，当前负责人编号，前一负责人编号，当前状态，收到时间)。“当前站点编号”已在站点信息中出现，并可以通过当前负责人编号查询，无需再用关系模式处理。

问题-3：增加关系

因为需要针对每个快递，统计支付的金额、时间及支付方式，所以在增加的“快递收费关系模式中需要体现快递编号、金额、支付时间和支付方式，即增加的关系模式为:快递费支付(快递编号，金额，支付时间，支付方式)。

讲解

问题


答案


分析

04 学生信息管理系统

场景描述

某学生信息管理系统的部分数据库关系模式如下:

学生: Student( stuno，stuname，stuage，stusex，schno)，各属性分别表示学生的学号、姓名、年龄、性别，以及学生所属学院的编号;

学院: School ( schno， schname， schstunum )，各属性分别表示学院的编号、名称及学生人数;

俱乐部: club ( clubno，clubname，clubyear，clubloc )，各属性分别表示俱乐部的编号、名称、成立年份和活动地点;

参加: JoinClub ( stuno，clubno，jonyear )，各属性分别表示学号、俱乐部编号以及学生加入俱乐部的年份。

有关关系模式的说明如下:

(1)学生的性别取值为“F’和“M’(F 表示女性，M 表示男性)。

(2)删除一个学院的记录时，通过外键约束级联删除该学院的所有学生记录。

(3)学院表中的学生人数值与学生表中的实际人数要完全保持一致。也就是说，当学生表中增减记录时，就要自动修改相应学院的人数。

答案

问题-1：创建学生表

create table Student (
  stuno char(11) primary key,
  stuname varchar(20),
  stuage smallint,
  stuexCHAR(1) check ( stusex IN ('F', 'M') ),
  schnoCHAR(3) references School (schno) ON DELETE CASCADE
);


问题-2：创建 CS_Number SQL，从 JoinClub 中选出“暂时没有学生参加的俱乐部”

create view CS_Number( clubno, clubname, clubstunum ) AS
  SELECT JoinClub.clubno, MIN(clubname), COUNT(stuno)
    FROM JoinClub, Club
    WHERE JoinClub.clubno = Club.clubno
    GROUP BY JoinClub.clubno

UNION

  SELECT clubno, clubname, 0
    FROM Club
    WHERE clubno NOT IN (
      SELECT DISTINCT clubno FROM JoinClub
    );


问题-3：触发器设计，Student 表插入一条数据，则 schstunum 自增一条记录；该表删除一条数据，则 schstunum 自减一条记录。

create trigger STU_NUM_TRG after insert or delete ON Student
referencing new row AS nrow, old row AS orow
for each row
BEGIN
  IF inserting then
    update School set schctunum = schstunum+1 where schno = nrow.schno;
  END IF;
  IF deleting then
    update School set schstunum = schstunum-1 where schno = orow.schno;
  END IF;
END;


问题-4：查询年龄小于 19岁的学生学号、姓名、所属学院名，一个学院排在一起。

select stuno, stuname, schname
  FROM Student, School
  WHERE Student.schno = School.schno
    AND stuage < 19
  ORDER BY schname;

讲解

问题


答案


分析

05 银行账务系统

场景描述

某商业银行账务系统的部分关系模式如下:

账户号码、账户名账户表: Account (anso，aname，balance)，其中属性含义分别为: 称和账户余额。

交易明细表: TranDetails(tno, ano，time，toptr，amount， ttype)，其中属性分别为: 交易编号、账户号码、交易时间、交易操作员、交易金额、交易类型(1-存款、2-取款、3-转账)。

余额汇总表: AcctSums (adate，atime，allamt)，其中属性分别为: 汇总日期、汇总时间、总余额。

常见的交易规则如下:

存/取款交易:操作员核对用户相关信息，在系统上执行存/取款交易。账务系统增加/减少该账户余额，并在交易明细表中增加一条存/取款交易明细。

转账交易: 操作员核对用户相关信息，核对转账交易账户信息，在系统上执行转账交易账务系统对转出账户减少其账户余额，对转入账户增加其账户余额，并在交易明细表中增加条转账交易明细。

余额汇总交易: 将账户表中所有账户余额累计汇总。

ano	aname	balance
101	张一	500
102	李二	350
103	王三	550
104	赵四	200

问题-2



问题-2

答案

讲解

问题


答案


分析