数据库原理例题文档

设某商业集团关于商店销售商品的数据库中有三个基本表：

    商店    SHOP（S#，SNAME，AREA，MGR_NAME）

            其属性是商店编号，商店名称，区域名，经理姓名。

    销售    SALE（S#，G#， QUANTITY）

其属性是商店编号，商品编号，销售数量。

    商品    GOODS（G#，GNAME，PRICE）

            其属性是商品编号，商品名称，单价。

36．试写出下列查询的关系代数表达式、元组表达式和关系逻辑规则：

    检索销售“冰箱”的商店的编号和商店名称。

37．试写出上面第36题的SELECT语句表达形式。并写出该查询的图示形式。

38．试写出下列操作的SQL语句：

    从SALE表中，把“开开商店”中销售单价高于1000元的商品的销售元组全部删除。

39．写一个断言，要求区域名为“EAST”的商店里销售商品的单价不能低于100元。

40．试写出下列操作的SQL语句：

        统计区域名为“EAST”的所有商店销售的每一种商品的总数量和总价值。

    要求显示（G#，GNAME，SUM_QUANTITY，SUM_VALUE），其属性为商品编号、商品名称、销售数量、销售价值。

五．综合题（本大题共2小题，每小题5分，共10分）

41．某汽车运输公司数据库中有一个记录司机运输里程的关系模式：

        R（司机编号，汽车牌照，行驶公里，车队编号，车队主管）

此处每个汽车牌照对应一辆汽车。“行驶公里”为某司机驾驶某辆汽车行驶的总公里数。如果规定每个司机属于一个车队，每个车队只有一个主管。

（1）试写出关系模式R的基本FD和关键码。

（2）说明R不是2NF模式的理由，并指出数据冗余之所在。试把R分解成2NF模式集。

（3）进而把R分解成3NF模式集，并说明理由。

42．设某商业集团数据库中有三个实体集。一是“仓库”实体集，属性有仓库号、仓库名和地址等；二是“商店”实体集，属性有商店号、商店名、地址等；三是“商品”实体集，属性有商品号、商品名、单价。

       设仓库与商品之间存在“库存”联系，每个仓库可存储若干种商品，每种商品存储在若干仓库中，每个仓库每存储一种商品有个日期及存储量；商店与商品之间存在着“销售”联系，每个商店可销售若干种商品，每种商品可在若干商店里销售，每个商店销售一种商品有月份和月销售量两个属性；仓库、商店、商品之间存在着“供应”联系，有月份和月供应量两个属性。

（1）试画出ER图，并在图上注明属性、联系类型、实体标识符；

（2）将ER图转换成关系模型，并说明主键和外键。

（3）将ER图转换成对象联系图。

（4）将ER图转换成UML的类图。

四．设计题答案

36．解：关系代数表达式：πS#，SNAME（σGNAME='冰箱'（SHOP⋈SALE⋈GOODS））

    元组表达式：{ t | (u) (v) (w) (SHOP (u)∧SALE (v)∧GOODS (w)

∧u[1]=v[1]∧v[2]=w[1]∧w[2]= '冰箱'∧t[1]=u[1]∧t[2]=u[2])}

关系逻辑规则：W(u1,u2)SHOP(u1,u2,u3,u4)∧SALE(u1,v2,v3)∧GOODS(v2, '冰箱',w3)

37．解：SELECT语句如下：

        SELECT A.S#，SNAME

        FROM SHOP A，SALE B，GOODS C

WHERE A.S#=B.S# AND B.G#=C.G# AND GNAME='冰箱'； 

    该查询语句的图示形式如下：

        WHERE S# IN（SELECT S#

                     FROM SHOP

                     WHERE SNAME='开开商店'）

          AND G# IN（SELECT G#

FROM GOODS

                      WHERE PRICE>1000）；

39．解：CREATE ASSERTION ASSE8 CHECK

        （100<=ALL（SELECT PRICE

               FROM SHOP A，SALE B，GOODS C

                   WHERE A.S#=B.S# AND B.G#=C.G# AND AREA='EAST'））；

    或 CREATE ASSERTION ASSE8 CHECK

        （NOT EXISTS（SELECT *

                     FROM SHOP A，SALE B，GOODS C

                         WHERE A.S#=B.S# AND B.G#=C.G# 

AND AREA='EAST' AND PRICE<100））；

40．解：SELECT C.G#，GNAME，SUM（QUANTITY）AS SUM_QUANTITY，

PRICE*SUM（QUANTITY）AS SUM_VALUE

        FROM SHOP A，SALE B，GOODS C

        WHERE A.S#=B.S# AND B.G#=C.G# AND AREA='EAST'

        GROUP BY C.G#，GNAME；

    （注：SELECT子句中的属性C.G#，GNAME应在分组子句中出现）

五．综合题答案

41．解：（1）根据已知条件，可写出基本的FD有三个：

            司机编号 → 车队编号

                    车队编号 → 车队主管

（司机编号，汽车牌照）→ 行驶公里

从上述三个FD，可知R的关键码为（司机编号，汽车牌照）。

（2）从上述三个FD，可推出下列FD成立：

（司机编号，汽车牌照）→（车队编号，车队主管）

这是一个局部FD。因此R不是2NF模式。

此时在R的关系中，每个司机只属于一个车队及主管人员，但要记载某司机驾驶过10辆汽车的行驶公里数，在R的关系中要出现10个元组。也就是这10个元组的司机相同，其车队编号和车队主管要重复出现10次，这就是数据冗余。

      R应分解成 R1（司机编号，汽车牌照，行驶公里）

R2（司机编号，车队编号，车队主管）

这两个模式都是2NF模式。

（3）R1已是3NF模式，但R2不是3NF模式。

    因为在R2中的基本FD有两个：

司机编号 → 车队编号，

车队编号 → 车队主管。

显然，存在传递依赖：司机编号 → 车队主管。

此时在R2的关系中，一个车队只有一个主管人员，但这个车队有20名司机，则在关系中就要有20个元组。这20个元组的车队编号相同，而车队主管要重复出现20次，这就是数据冗余。

      R2应分解成    R21（司机编号，车队编号）

                    R22（车队编号，车队主管）

这样，ρ={ R1，R21，R22 }，其中每个模式均是3NF模式。

42．解：（1）ER图如图1所示。

图1

（2）据转换规则，图2的ER图可转换成6个关系模式：

仓库（仓库号，仓库名，地址）

商品（商品号，商品名，单价）

商店（商店号，商店名，地址）

库存（仓库号，商品号，日期，库存量）

销售（商店号，商品号，月份，月销售量）

供应（仓库号，商店号，商品号，月份，月供应量）

（3）图1的ER图的对象联系图如图2所示。其转换规则基本上与转换成关系模型的规则类似。三个实体类型转换成三个对象类型，两个M:N联系类型和一个M:N:P联系类型也转换成三个对象类型。因此对象联系图有六个对象类型，如图2所示。图中未标出基本数据类型属性，具体如下：

仓库（仓库号，仓库名，地址）

商品（商品号，商品名，单价）

商店（商店号，商店名，地址）

库存（日期，库存量）

销售（月份，月销售量）

供应（月份，月供应量）

（4）图1的ER图的UML类图如图3所示。三个实体类型转换成三个类，三个联系类型转换成三个关联类，如图3所示。

36．设数据库中有两个基本表：

职工表  EMP（E#，ENAME，AGE，SALARY，D#），

其属性分别表示职工工号、姓名、年龄、工资和工作部门的编号。

部门表  DEPT（D#，DNAME，MGR#）,

其属性分别表示部门编号、部门名称和部门经理的职工工号。

试指出每个表的主键和外键。并写出每个表创建语句中的外键子句。

37．在第36题的两个基本表中，写出下列查询的关系代数表达式和SQL语句：

检索每个部门经理的工资，要求显示其部门编号、部门名称、经理工号、经理姓名和经理工资。

38．在第36题的两个基本表中，建一个年龄大于50岁的职工视图，属性为（D#，DNAME，E#，ENAME，AGE，SALARY）。

39．在第36题的两个基本表中，写一个断言，要求每个部门的经理工资应大于本部门所有职工的工资。

40．下面是用ORDB的定义语言定义的数据库：

CREATE  TYPE  MyString  char  varying；

CREATE  TYPE  cname  MyString；

CREATE  TABLE  department（dno  MyString，

                           dname  MyString，

                           staff  setof（ref（employee）））；            

CREATE  TABLE  employee（eno  MyString，

                         ename  MyString，

                         salary  integer，

                         children  setof（cname），

                         works_for  res(department))；

（1）试画出上述数据库的对象联系图。

（2）试用ORDB的查询语言写出下列查询的SELECT语句：

检索部门编号为D6的部门中每个职工的子女名，要求显示职工的姓名、子女名。

五．综合题（本大题共2小题，每小题5分，共10分）

41．设有一个记录各个球队队员每场比赛进球数的关系模式

R（队员编号，比赛场次，进球数，球队名，队长名）

如果规定每个队员只能属于一个球队，每个球队只有一个队长。

1试写出关系模式R的基本FD和关键码。

2说明R不是2NF模式的理由，并把R分解成2NF模式集。

3进而把R分解成3NF模式集，并说明理由。

四．设计题答案

36．答：EMP表的主键为E#，外键为D#。

        DEPT表的主键为D#，外键为MGR#

        在EMP表的创建语句中,可写一个外键子句：

            FOREIGN  KEY  D#  REFERENCES  DEPT（D#）；

        在DEPT表的创建语句中,可写一个外键子句：

            FOREIGN  KEY  MGR#  REFERENCES  EMP（E#）；

MGR#=E#

37．答：关系表达式为：πDEPT.D#，DNAME，MGR#，ENAME，SALARY（DEPT  ⋈  EMP）

        SELECT语句为：

            SELECT  DEPT.D#，DNAME，MGR#，ENAME，SALARY

            FROM  DEPT，EMP

            WHERE  MGR#=E#；

38．解：CREATE  VIEW  VIEW5

            AS    SELECT  DEPT.D#，DNAME，E#，ENAME，AGE，SALARY

                FROM  DEPT，EMP

            WHERE  DEPT.D#=EMP.D#  AND  AGE>50；

39．解：CREATE  ASEERTION  ASSE8  CHECK

（NOT  EXISTS（ SELECT  *

                FROM  EMP，DEPT

                WHERE  E#=MGR#

                  AND  SALARY<=ALL

                        (SELECT  SALARY

                         FROM  EMP

                         WHERE  D#=DEPT.D#）））；

40．解：（1）对象联系图如图4所示

图4

（2） SELECT  B.ename,C.cname

FROM  department  as  A，A.staff  as  B，B.children  as  C

WHERE  A.dno=‘D6’；

    或    SELECT  B.ename,C.cname

FROM  employee  as  B，B.children  as  C

WHERE  B.works_for.dno=‘D6’；

（3）

五．综合题答案

41．解：⑴ 根据每个队员只能属于一个球队，可写出FD  队员编号球队名；

根据每个球队只有一个队长，可写出FD  球队名队长名；

“每个队员每场比赛只有一个进球数”，这条规则也是成立的，因此还可写出FD ：

        （队员编号，比赛场次）进球数。

从上述三个FD可知道，R的关键码为（队员编号，比赛场次）。

⑵ 从⑴可知，R中存在下面两个FD：

（队员编号，比赛场次）（球队名，队长名）

队员编号  （球队名，队长名）

显然，其中第一个FD是一个局部依赖，因此R不是2NF模式。

对R应该进行分解，由第二个FD的属性可构成一个模式，即

R1（队员编号，球队名，队长名）；

另一个模式由R的属性集去掉第二个FD右边的属性组成，即

R2（队员编号，比赛场次，进球数）。

R1和R2都是2NF模式，因此ρ={ R1，R2 }

⑶ R2（队员编号，比赛场次，进球数）中，FD是（队员编号，比赛场次）进球数，关键码为（队员编号，比赛场次），可见R2已是3NF模式。

R1（队员编号，球队名，队长名）中，FD有两个：

队员编号球队名

  球队名队长名

关键码为队员编号，可见存在传递依赖，因此R1不是3NF模式。

对R1应分解成两个模式：R11（队员编号，球队名），R12（球队名，队长名）。这两个模式都是3NF模式。

因此，R分解成3NF模式集时，ρ={ R11，R12，R2 }。

司机（司机编号，姓名，电话，聘期）

使用（使用日期，公里数）

图6

图7

四．设计题（本大题共5小题，每小题4分，共20分）

31．设某商业集团为仓库存储商品设计了三个基本表：

        仓库    STORE（S#，SNAME，SADDR），其属性是仓库编号、仓库名称和地址。

    存储    SG（S#，G#，QUANTITY），其属性是仓库编号、商品编号和数量。

        商品    GOODS（G#，GNAME，PRICE），    其属性是商品编号、商品名称和单价

    现检索仓库名称为“莘庄”的仓库里存储的商品的编号和名称。试写出相应的关系代数表达式、元组表达式、关系逻辑规则和SELECT语句。

32．在第31题的基本表中，检索存储全部种类商品的仓库的编号及名称。试写出相应的关系代数表达式、元组表达式、关系逻辑规则和SELECT语句。

33．在第31题的基本表中，检索每个仓库存储商品的总价值。试写出相应的SELECT语句。要求显示（S#，SUM_VALUE），其属性为仓库编号及该库存储商品的总价值。

34．在第31题的基本表中，写一个断言，规定每个仓库存储商品的单价为1万元以上的商品种类最多为20种。

35．图8是有关大学（university）和学生（student）信息的对象联系图：

图8

（1）试用ORDB的定义语言，定义这个数据库。

（2）试用ORDB的查询语言写出下列查询的SELECT语句：检索每个大学里，籍贯为本地的学生，要求显示大学名、城市、学生身份证号和学生姓名。

五．综合题（本大题共4小题，每小题5分，共20分）

36．设有关系模式

R（职工名，项目名，工资，部门名，部门经理）

如果规定每个职工可参加多个项目，各领一份工资；每个项目只属于一个部门管理；每个部门只有一个经理。

1试写出关系模式R的基本FD和关键码。

2说明R不是2NF模式的理由，并把R分解成2NF模式集。

3进而把R分解成3NF模式集，并说明理由。

四．设计题答案

31．解：关系代数表达式为：πG#，GNAME（σSNAME= '莘庄'（STORE⋈SG⋈GOODS））

    元组表达式为：{ t | (u) (v) (w) (GOODS(u)∧SG(v)∧STORE（w）

∧u[1]=v[2]∧v[1]=w[1]∧w[2]= '莘庄'∧t[1]=u[1]∧t[2]=u[2])}

关系逻辑规则如下：

W（x，y）GOODS（x，y，a）∧SG（b，x，c）∧STORE（b，'莘庄'，d）

SELECT语句为：

SELECT  A.G#，GNAME

FROM  GOODS  AS  A，SG  AS  B，STORE  AS  C

WHERE  A.G#=B.G#  AND  B.S#=C.S#  AND  SNAME= '莘庄'；

32．解：关系代数表达式为：

    πS#，SNAME（STORE⋈（πS#，G#（SG）÷πG#（GOODS）））

    元组表达式为：

{ t | (u) (v) (w) (STORE(u) ∧ GOODS（v）∧ SG(w) ∧ w[1]=u[1] ∧ w[2]=v[1] ∧ t[1]=u[1] ∧ t[2]=u[2])}

关系逻辑规则如下：

W（x，y）← STORE（x，y，a）∧┐GOODS（b，c，d）∧┐SG（x，b，e）

SELECT语句为：

SELECT  S#，SNAME

FROM  STORE

WHERE  NOT  EXISTS

（SELECT  *

FROM  GOODS

WHERE  NOT  EXISTS

（SELECT  *

FROM  SG

WHERE  SG.S#=STORE.S#

AND  SG.G#=GOODS.G#））；

33．解：SELECT语句为：

        SELECT  S#，SUM（QUANTITY *PRICE）AS SUM_VALUE

        FROM  SG，GOODS

        WHERE  SG.G#=GOODS.G#

        GROUP  BY  S#；

34．解：CREATE  ASSERTION  ASSE6  CHECK

        （20>=ALL（SELECT  COUNT（SG.G#）

FROM  SG，GOODS

WHERE SG.G#=GOODS.G# AND PRICE>10000

GROUP  BY  S#））；

35．解：

（1）     CREATE  TYPE  MyString  char  varying；

CREATE  TABLE  university（uname  MyString，

city  MyString，

staff  setof（ref（student）））；

        CREATE  TABLE  student（sno  MyString，

sname  Mystring，

city  MyString，

languages setof（MyString），

study  ref（university））；

（2）    SELECT  A.uname，A.city，B.sno，B.sname

        FROM  university  as  A，A.staff  as  B

        WHERE  A.city=B.city；

    也可以用另一种写法：

        SELECT  B.study.uname，B.study.city，B.sno，B.sname

        FROM  student  as  B

        WHERE  B.study.city=B.city；

五．综合题答案

36．解：⑴ R的基本FD有三个：

（职工名，项目名）工资

  项目名  部门名

  部门名  部门经理

关键码为（职工名，项目名）。

⑵ 根据⑴，R中存在下列两个FD：

（职工名，项目名）（部门名，部门经理）

  项目名  （部门名，部门经理）

其中前一个FD是一个局部依赖，因此R不是2NF模式。

R应分解成两个模式：    R1（项目名，部门名，部门经理）

R2（职工名，项目名，工资）

R1和R2都是2NF模式。

⑶ R2已是3NF模式。

在R1中，由于存在两个FD：

项目名部门名

部门名部门经理

即存在一个传递依赖，因此R1不是3NF模式。

对R1应分解成两个模式：R11（项目名，部门名），R12（部门名，部门经理）。这两个模式都是3NF模式。

因此，R分解成3NF模式集时，ρ={ R11，R12，R2 }。