database|数据库|mysql|人大数据库

《数据库原理及应用》(Introduction to Database Systems)

一、关系代数(共15分，每小题3分)

设有供应商-零件-工程数据库模式如下：

供应商表S(sNo,sName, sCity, sTel)

零件表P(pNo, pName, pColor, pOrigin)

工程项目表J(jNo, jName, jCity)

供应情况表SPJ(sNo, jNo, pNo, Qty)

给出得到如下结果的关系代数表达式：

(1) 供应工程J1零件的供应商号码sNo?

(2) 供应工程J1零件P1的供应商号码sNo?

(3) 供应工程J1零件为红色的供应商号码sNo?

(4) 没有使用天津供应商生产的红色零件的工程号jNo?

(5) 至少用了供应商S1所供应的全部零件的工程号jNo?

参考答案：

P_sNo(σ_jNo=”J1”(SPJ))
P_sNo(σ_jNo=”J1”_{Ù pNo=”P1”}(SPJ))
P_sNo(σ_jNo=”J1”_{Ù pColor=”Red”}(SPJ ⋈ P)), or

P_sNo(σ_jNo=”J1” (SPJ) ⋈ σ_{pColor=”Red”} (P))

其中，Red用”红色”或”红”均可，⋈用q或X也可以

P_jNo(J) –P_jNo(σ_{pOrigin=”Tianjin”}(S)⋈ SPJ⋈ σ_{pColor=”Red”}(P))

其中，Tianjin用”天津”，Red用”红色”或”红”均可，⋈用q或X也可以

P_{jNo, pNo}(SPJ) ¸ P_pNo(σ_sNo=”S1”(SPJ))

二、半结构化数据(共15分，每小题3分)

参阅给出的stu.xml文档样例，回答如下问题：

(1) 为该xml文档设计一个合适的DTD。

(2) 给出一个XPath表达式，返回成绩低于70的所有男同学的姓名。

(3) 给出一个XPath表达式，返回学号为”102″的同学的所有技能。

(4) 编写一段XQuery代码，查找掌握MySQL技能的所有同学的姓名。

(5) 编写一段XQuery代码，查找成绩低于平均成绩的所有同学的姓名。

参考答案：

<!DOCTYPE Roster [

<!ELEMENT Roster (stu*)>

<!ELEMENT stu (name, sex, birthday, score, skill+)>

<!ATTLIST stu id ID #REQUIRED>

<!ELEMENT name (#PCDATA)>

<!ELEMENT sex (#PCDATA)>

<!ELEMENT birthday (#PCDATA)>

<!ELEMENT score (#PCDATA)>

<!ELEMENT skill (#PCDATA)>

其中，ID用CDATA也可以

doc(“stu.xml”)/Roster/stu/name[../sex=”男” and ../score<70]

doc(“stu.xml”)/Roster/stu[@id=”102”]/skill

let $d:=document(“stu.xml”)/Roster

for $x in $d/stu/name[contains(..//skill, “MySQL”)]
return $x

let $d=document(“stu.xml”)/Roster
for $x in $d/stu
where $c in $x/score satisfies $c > data(
{let $plist := $d/score
return avg($plist)}
)

三、SQL查询语言(16分，每小题4分)

有如下数据库创建的脚本文件(c:\b_n_b.sql)。

(1) 请检查其中存在的错误，说明原因，并进行纠正。

(2) 写出在MySQL中运行该脚本的命令。

create database if not exists b_n_b;

use b_n_b;

create table if not exists bars (

name varchar(20) primary key,

phone char(12),

addr varchar(20)

);

create table if not exists beers (

name varchar(20) primary key,

manf varchar(30)

);

create table if not exists sells (

bar varchar(20),

beer varchar(20),

price real,

primary key (bar,beer),

foreign key (beer)

references beers(name)

on delete set null

on update cascade

);

insert into bars values (‘Sally’, ‘555-328-7651’, ‘123 Sesame Street’);

insert into bars values (‘Joe”s’, ‘555-367-5809’, ‘4567 Jasper Avenue’);

insert into beers values (‘Bud’,’AB’);

insert into beers values (‘Bud Lite’,’AB’);

insert into beers values (‘TsingTao’,’TsingTao’);

insert into sells values (‘Joe”s’,’Bud’,2.5);

insert into sells values (‘Joe”s’,’Bud Lite’,2.5);

insert into sells values (‘Joe”s’,’TsingTao’,3.5);

insert into sells values (‘Sally’,’Bud’,2.5);

insert into sells values (‘Sally’,’TsingTao’,3.5);

(3) 以下为基于上述数据库的两个查询Q1和Q2，在什么情况下这两个查询返回的结果不同?

Q1:

select name

from bars

where name in (select bar from sells where beer=’TsingTao’ and price>3.0)

Q2:

select bars.name

from bars, sells

where bars.name=sells.bar and sells.beer=’TsingTao’ and sells.price>3.0

(4) 给出如下查询的SQL或MySQL查询语句：

青岛啤酒(TsingTao)卖的最贵的酒吧(Bar)出售Bud啤酒的价格。

参考答案：

(1)

错误：在创建表sells，外键beer与表beers的关联规则时出错，即on delete set null。

原因：在sells中beer既是foreign key，又是sells的组合键的一部分，而这个组合键又设置为primary key（而非unique），所有primary key不能为null。

纠正：把on delete set null改为on delete cascade；或者干脆把这一子句删除。

(2)

mysql> source c:/b_n_b.sql;

(3)

没有这样的情况。

(4)

select price
from sells
where beer=’Bud’ and bar = (
            select bar
            from sells
            where beer=’TsingTao’ and price >= all(
                        select price
                        from sells
                        where beer=’TsingTao’
            )
);

四、数据库设计(共10分)

关系数据库模式R{A, B, C, D, E, F}满足如下函数依赖:

AB->C, C->A, BC->D, ACD->B, BE->C, CE->FA, CF->BD, D->EF

找到该函数依赖集的最小依赖集，并写出求解过程。

参考答案：

(1) 去掉F中冗余的函数依赖

判断AB->C是否冗余

G1={C->A, BC->D, ACD->B, BE->C, CE->FA, CF->BD, D->EF}

AB_G1+=AB

∵C Ï AB_G1+

不冗余

判断C->A是否冗余

G2={BC->D, ACD->B, BE->C, CE->FA, CF->BD, D->EF}

C_G2+=C

∵A Ï C_G2+

不冗余

判断BC->D是否冗余

G3={ACD->B, BE->C, CE->FA, CF->BD, D->EF}

BC_G3+=BC

∵D Ï BC_G3+

不冗余

判断ACD->B是否冗余

G4={BE->C, CE->FA, CF->BD, D->EF}

ACD_G4+=ABCDEF

∵B Î ACD_G4+

冗余

判断BE->C是否冗余

G5={CE->FA, CF->BD, D->EF}

BE_G5+=BE

∵C Ï BE_G5+

不冗余

判断CE->FA是否冗余

G6={CF->BD, D->EF}

CE_G6+=CE

∵FA Ï CE_G6+

不冗余

判断CF->BD是否冗余

G7={D->EF}

CF_G7+=CF

∵BD Ï CF_G7+

不冗余

Fmin={C->A, BC->D, BE->C, CE->FA, CF->BD, D->EF}

五、综合分析与设计(共54分)

RentTheRunway是一家提供著名设计师的服装和配饰租赁的公司。刚开始时是一家纯电子商务公司，自2013年开始在线下扩张，在美国开了3个零售店。创始人Jennifer Hyman和Jennifer Fleiss在哈佛大学商学院相识，公司开张于2009年9月。截至2014年2月，该公司提供50,000件女装和10,000件配饰租赁，有400万会员注册，雇用了250员工，与200名服装设计师建立了伙伴关系，包括Badgley Mischka, Vera Wang, Alexis Bittar, Carolina Herrera and Calvin Klein。

该公司本质上向女性提供奢侈品的使用权，无论是买不起或者是买了也不会使用几次。与零售商一样，商品直接来自设计师，通过提供女性以10%零售价格租赁著名设计师服装和配饰，从而改变了时装产业。租赁期限4或8天，费用为零售价格的10%，服装尺寸0到22(取决于设计师)。每一服装租赁包括一套确保合身的后备尺寸，无需额外付费。订单内的第二套服装款式只需另加$32.50。同时为客户提供了一个预付费的、已写好收件地址用于归还服装的包装盒，以及$5保险费以防服装意外损伤。租赁价格包括干洗和衣物护理，亦出租配饰，包括首饰珠宝和手包；出售“必需品”，包括内衣、紧身衣、塑身内衣和化妆品。

会员需要注册，收费标准是，$350/月注册费，一个月内无限次，同时不超过4件；$900注册费，1/2/3月份无限次租赁，同时不超过4件。

请根据你自己的理解和创意，帮助RentTheRunway公司设计一个关系数据库系统，旨在提升客户管理部门的运营效率和对于客户的洞察力。

具体要求如下：

(1) 给出符合BCNF范式的关系数据库模式(10)；

(2) 画出该关系数据库的E/R图(10)；

(3) 给出在MySQL中创建该数据库的DDL语句(10)；

(4) 设计常用的3个不同查询，并写出实现这些查询的SQL(或MySQL)语句(12)；

(5) 设计有创意的3个不同查询，并写出实现这些查询的SQL(或MySQL)语句(12)。

参考答案

(1)

会员(Customers) C(cID, cName, cAddress, cPhone, cMail, cDate)

其中，cID会员号，cName会员姓名，cAddress会员地址，cPhone会员电话，cDate会员注册日期

员工(Employees) E(eID, eName, eDept, ePhone, eMail, eDate)

其中，eID员工编号，eName员工姓名，eDept员工所在部门，ePhone员工联系电话，eMail员工邮箱，eDate员工入职日期

设计师(Designers) D(dID, dName, dAddress, dPhone, dMail, dDate)

其中，dID是设计师编号，dName设计师姓名，dAddress设计师地址，dPhone设计师联系电话，dMail设计师邮箱，dDate设计师签约日期

服装(Products) P(pDD, pType, pDescription, dID)

其中，pDD服装编号（同一个pDD服装可能有多件，其pID不一样），pType服装类型，pDescription服装描述，dID设计师编号（外键）

服装明细(Product Details) PD(pID, pDD, pPrice,pLaunchDate, pStatus)

其中，pID服装唯一编号，pDD服装编号（外键），pPrice价格，pLaunchDate投放市场日期，pStatus状态（0-在库，1-已租出）

订单(Order Master) O(oID, oDate, cID, eID, oReturnDate)

其中，oID订单编号，oDate订单日期，cID会员编号，eID订单员工（外键），oReturnDate归还日期

订单明细(Order Details) OD(oID, pID, pReturnStatus)

其中，oID订单编号，pID服装唯一编号（外键），eID归还检查员工（外键），pReturnStatus归还状态（0-完好，1-有破损，2-破损严重）；oID+pID组合键

(2)

(3)

create database if not exists RTR;

use RTR;

create table if not exists C (
            cID char(8) primary key,
            cName varchar(20),
            cAddress varchar(40),
            cPhone char(12),
            cMail varchar(20),
            cDate date
);

create table if not exists E(
            eID char(4) primary key,
            eName varchar(20),
            eDept char(10),
            ePhone char(12),
            eMail varchar(2),
            eDate date
);

create table if not exists D(
            dID char(4) primary key,
            dName varchar(20),
            dAddress varchar(40),
            dPhone char(12),
            dMail varchar(20),
            dDate date
);

create table if not exists P(
            pDD char(8) primary key,
            pType char(3),
            pDescription varchar(40),
            dID char(4) references D(dID)
);

create table if not exists PD(
            pID char(8) primary key,
            pDD char(8) references P(pDD),
            pPrice real,
            pLaunchDate date,
            pStatus char(1)
);

create table if not exists O(
            oID char(12) primary key,
            oDate date,
            cID char(8) references C(cID),
            eID char(4) references E(eID),
            oReturnDate date
);

create table if not exists OD(
            oID char(12) references O(oID),
            pID char(8) references PD(pID),
            pReturnStatus,
            primary key (oID, pID)
);

(4)

(5)

stu.xml文档

<?xml version=”1.0″ encoding=”gb2312″ ?>

<skill>Oracle</skill>

<skill>C sharp</skill>

</stu>

<skill>Visual Basic</skill>

<skill>SQL Server</skill>

</stu>

<skill>C sharp</skill>

</stu>

<skill>Visual C++</skill>

<skill>SQL Server</skill>

</stu>

<skill>C Sharp</skill>

<skill>SQL Server</skill>

</stu>

</roster>

Published by admin

Leave a Reply Cancel reply