西南交大数据库第三章作业

1. 关系数据模型中，用什么来表示数据以及数据之间的联系？

关系

2.对于表中几个特殊的列，如主键、候选键和外键，分别用什么限制来保证它们的完整性？对表中其它一般性的列，用什么限制来保证它们的完整性？

主键限制保证主键的完整性，唯一限制保证候选键的完整性，外键限制保证外键的完整性，一般性限制保证一般性的列。

3.外键限制定义的条件？

定义外键限制的列必须是另一个表中的主键或候选键；在含外键的表上定义

4.SQL-92标准支持的完整性限制是否一定会在SQL SERVER中实现，举例说明？

不是，在外键限制中SQL-92支持Cascade、No action、Set Null、set default而SQL SERVER仅支持CASCADE 和NO ACTION

5.请说明在维护表间数据完整时外键限制与触发器的3点不同之处。

外键限制有前提条件，而触发器没有

外键限制只需在表中定义，而触发器在主表和从表都要写

外键限制行动固定，而触发器行动灵活可定义

6.如何避免通过视图向其基表插入数据，数据插入到基表了但视图中看不到？

在视图定义中加入With Check Option

7.关系代数中对结果有重复元组时，如何处理？SQL中呢？

关系代数会去掉重复元组而在SQL中会保留

8.连接的分类？为何有3种外连接？

条件连接、等连接、自然连接、外连接

因为R⨝S的匹配结果有三种：a.S中没有匹配R的行；b.R中没有匹配S的行；c.没有匹配的R和S的所有行

1. （3）用唯一限制来约束。

   ①主键            ②外键              ③候选键         ④简单键

2. （3）与“行”不同义。

   ①记录            ②元组              ③属性

3. （2）是关系代数的附加操作。

   ①Selection         ②Join          ③Projection          ④Intersection

1. 关系代数是与网状模型有关的查询语言。（关系数据模型）
2. 关系代数中笛卡尔乘积操作的不足是无用数据过少。（无用结果过多）
3. 条件连接是等连接的特例。（等连接是条件连接）

得分

56

四. 综合题（第1题30分，第2题34分，共64分）

1. （30分）一个电影资料库有四个实体“电影”，“演员”，“导演”，“电影公司”。“电影”的属性有电影编号，电影名，电影类型，对白语言；“演员”的属性有演员工作证号，姓名，出生年，性别；“导演”的属性有导演工作证号，姓名，出生年，性别；“电影公司”的属性有公司名称，所在国家。

这些实体间的联系及它们的属性有：演员出演电影，为多对多联系，该联系含角色属性；导演执导电影，每部电影只由一个导演执导；演员属于电影公司；导演属于电影公司；电影公司出品电影，有出品年份属性。

1. 1. 请画出ER图，要求标出实体的主键、联系的约束类型和键约束。（7分）

1. 1. 将此ER图转换为关系模型，要求标出各关系的主键，如果存在的话还应指明其候选键和外键。（7分）

电影（电影编号，电影名，电影类型，对白语言，导演工作证号，公司名称,出品年份）电影编号是主键，导演工作证号、公司名称是外键

演员（演员工作证号，姓名，出生年，性别，公司名称）演员工作证号是主键，公司名称是外键

电影公司（公司名称，所在国家）公司名称是主键

导演（导演工作证号、姓名、性别、出生年、公司名称）导演工作证号是主键，公司名称是外键

出演（角色，演员工作证号，电影编号）（演员工作证号，电影编号）是主键，演员工作证号、电影编号分别是外键

1. 1. 假定“电影公司”为主表，“导演”为从表，请利用触发器方式，定义从表到主表方向的完整性。（3分）

CREATE TRIGGER 导演_iu

ON 导演

FOR INSERT,UPDATE

AS

declare @num_rows int

select @num_rows=@@rowcount

IF @num_rows=0 RETURN

IF (select count(*)

from 电影公司 p, inserted i

where p.公司名称=i.公司名称) != @num_rows

begin

raiserror  53334

rollback transaction

return

end

RETURN

• 1. 请用关系代数表达式和SQL分别表达下列查询（9分）
• 查询1988年之前出生的男演员的姓名。

        Π姓名（σ 出生年>1988（演员）∩σ性别=男 (演员)）

         Select 姓名

         From 演员

         Where 出生年<1988 and 性别=男

• 查询2016年华谊公司出品的电影的名字和导演姓名。

Π电影.电影名，导演.姓名（σ出品年份=2016（电影⨝导演⨝出品）∩σ公司名称=华谊公司（电影⨝导演⨝出品））

Select 电影.电影名，导演.姓名

From 电影 natual join 导演 natual join 出品

Where 出品年份=2016 and 公司名称=华谊公司

• 查询江河导演所导演的影片中的主角演员姓名。

Π演员. 姓名（σ导演.姓名=江河（演员⨝导演⨝出演⨝指导）∩σ角色=主角（演员⨝导演⨝出演⨝指导））

Select 演员.姓名

From 演员natual join导演 natual join指导 natual join 出演

Where 导演.姓名=江河

• 1. 请用SQL表达下列综合查询（4分）
• 按电影公司名称查询各公司所出品电影数。

        select 公司名称, count(电影编号)

from 出品

group by 公司名称

？

② 按演员工作证号和姓名查询所出演的电影数。

select 演员工作证号, 演员姓名, count(电影编号)

from 演员 A, 出演 B

where A.演员工作证号=B.演员工作证号

group by 演员工作证号, 演员姓名

1. （34分）某出版社管理系统有四个实体，即出版社（Publisher）、编辑（Editor）、作者（Author）和书籍（Book）。“出版社”的属性有出版社编码（Pid）、出版社名称（Pname）、地址（Paddr）和电话（Ptel）；“编辑”的属性有编辑工号（Eid）、姓名（Ename）、性别（Egender）、出生日期；“作者”的属性有作者编码（Aid）、姓名（Aname）、性别（Agender）、电话（Atel）；“书籍”的属性有国际图书分类号（Isbn）、书名（Bname）、单价（Bprice）。

这些实体间的联系及它们的属性有：作者“主编”（ZX）书籍，为1:n联系；编辑“校对”（JD）书籍，为1:n联系；出版社“出版”（CB）书籍，为1:n联系；“出版”的属性有出版日期（Pdate）。

1. 请画出概念数据模型的E-R图，要求标注联系的约束类型和键约束。（7分）

1. 将此E-R图表示的数据模型转换为关系模型，要求标出各关系的主键。（7分）

出版社（出版社编码（Pid）、出版社名称（Pname）、地址（Paddr）、电话（Ptel））出版社编码（Pid）是主键

编辑（编辑工号（Eid）、姓名（Ename）、性别（Egender）、出生日期）编辑工号是主键

作者（作者编码（Aid）、姓名（Aname）、性别（Agender）、电话（Atel））作者编码是主键

书籍（国际图书分类号（Isbn）、书名（Bname）、单价（Bprice）、出版社编码（Pid）、编辑工号（Eid）、作者编码（Aid）、出版日期（Pdate））国际图书分类号（Isbn）是主键出版社编码（Pid）、编辑工号（Eid）、作者编码（Aid）是外键

1. 给出创建“出版”关系（表）的SQL语句（需要创建相应的主键约束和外键约束）。（4分）

        CREATE TABLE CB

        （

Pid char（4）NOT BULL

CONSTRATNT  pid_const

PEFRENCES Publisher(pid)

ON DELETE CASCADE

ON UPDATE CASCADE

Isbn char(4) PRIMARY KEY

CONSTERAINT book(isbn)

ON DELETE CASCADE

ON UPDATE CASCADE

PDate date NOT NULL

）

1. 创建一个由地址中含有“北京市”的出版社出版的书籍的视图。（3分）

      CREATE VIEW shitu

   AS SELECT Isbn,Bname, Bprice,pid ,aid,eid,pdate

From publisher natual join book

Where publisher.paddr like ‘%北京市%’

1. 请分别用关系代数表达式和SQL查询语句表达下列查询。（9分）

• 由出版社“QHDXP”出版的、由编辑名为“LTR”校对的书籍的ISBN号和书名。

     ΠISBN，bname（σpname=qhdxp（publisher⨝editor⨝book）∩σename=ltr（publisher⨝editor⨝book））

Select isbn ,bname

From publisher  natual  join  editor  natual  join  book

Where pname=qhdxp and ename=ltr

• 由“女”性作者主编的、且由出版社“SCDXP”在2017.1.1至2017.12.31之间出版的书籍的ISBN号和书名。

   Π ISBN，bname（σagender=‘女’（author⨝pulisher⨝book⨝cb）∩σpname=scdxp（author⨝pulisher⨝book⨝cb）∩σpdate>=2017.1.1(author⨝pulisher⨝book⨝cb)∩σpdate<=2017.12.31(author⨝pulisher⨝book⨝cb)  ）

Select isbn ,bame

From author natual join pulisher natual join book natual join cb

Where agender=女 and  pname=scdxp  and  pdate>=2017.1.1  and  pdate  <=2017.12.31

• 由“男”性编辑校对的、且单价在30至90元之间的书籍的ISBN号和书名。

ΠISBN，bname(σegender=‘男’ (editor⨝book)∩σbprice>=30 (editor⨝book)∩σbprice<=90 (editor⨝book))

Select isbn bame

From editor natual join book

Where egender=男 and  bprice>=30 and bprice<=90

1. 请用SQL表达下列综合查询。（4分）

• 按出版社编码和名称查询所出版书籍的作者数。

     select Pid, Pname, count(Aid)

from Publisher P, CB C, Book B

where P.Pid=C.Pid and C.Isbn=B.Isbn

group by Pid, Pname

② 按编辑工号和姓名查询所校对的书籍数。

     select Eid, Ename, count(Isbn)

from Editor E, Book B

wherer E.Eid=B.Eid

group by Eid, Ename

通过这次学习，我了解了关系数据模型和关系上的完整性约束或限制以及关系代数，对data本身有了更加深入的理解，学会了如何表达数据间的联系，知道了完整性约束，学会了关系模型的DB设计，同时对select语句有了更深入的理解。