数据库例题

一、某商业集团的销售管理系统 数据库中有三个实体集。一是“商店”实体集，属性有商店编号、商店名、地址等；二是“商品”实体集，属性有商品号、商品名、规格、单价等；三是“职工”实体集，属性有职工编号、姓名、性别、业绩等。 商店与商品间存在“销售”联系，每个商店可销售多种商品，每种商品也可放在多个商店销售，每个商店销售一种商品，有月销售量；商店与职工间存在着“聘用”联系，每个商店有许多职工，每个职工只能在一个商店工作，商店聘用职工有聘期和月薪。 （1）试画出ER图，并在图上注明属性、联系的类型。 （2）将ER图转换成关系模型，并注明主码和外码。

商店（商店编号、商店名、地址）
商品（商品号、商品名、规格、单价）
职工（职工编号、姓名、性别、业绩、商店编号、聘期、月薪）
销售（商店编号、商品号、销量）

二、某研究所有多名科研人员，每个科研人员只属于一个研究所，研究所有多个科研项目，每个科研项目有多名科研人员参加，每个科研人员可以参加多个科研项目。科研人员参加科研项目要统计工作量。研究所属性有：编号，名称，地址；科研人员属性有：职工号，姓名，年龄，职称；科研项目属性有：项目号，项目名，经费。
（1）画出ER图，并标明联系类型和属性
（2）将ER图转换成关系模型，并注明主键和外键

研究所（研究所编号、研究所名称、地址）
科研人员（职工号、姓名、年龄、职称、研究所编号）
科研项目（项目号、项目名、经费、研究所编号）
科研（职工号、项目号、工作量）
~~~~~~~ ~~~~~

三、已知pubs数据库中的三张表
Authors（作家代号，姓，名，性别，电话，洲）
Titles（书代号，书名，类型，出版代号，价格，预付款，销售数量）
Titleauthor（作家代号，书代号）
1、查询pubs数据库中authors表中，作家的姓、名和电话。
SELECT 姓 名 电话
FROM Authors;

2、查找pubs数据库中authors里所有居住在KS、CA、MI、IN州的作家的数据。
SELECT *
FROM Authors
WHERE 洲 IN(KS,CA,MI,IN);

3、查询输出pubs数据库中titles表里书的价格小于15以及大于20的书的代号，类型以及价格。

SELECT 书代号 类型 价格
FROM Titles
WHERE 价格<15 OR 价格>20;

4、查询pubs数据库中titles表里类型为商业图书的书的平均价格。

SELECT AVG(价格)
FROM Titles
WHRE 类型=’商业图书’;

5、检索作家李娜发表的图书详细资料；

不嵌套：

SELECT Titles*
FROM Authors Titles Titleauthor
WHERE Authors.作家代号=Titleauthor.作家代号
AND Titleauthor.书代号=Titles.书代号
AND Authors.姓=’李’ AND Authors.名=’娜’；

嵌套：
SELECT *
FROM T
WHERE 书代号 IN
(SELECT 书代号
FROM Titleauthor
WHERE 作家代号 IN
(SELECT 作家代号
FROM Authors
WHERE 姓=’李’,名=’娜’));

6、检索每个作家发表的图书数量；

SELECT COUNT(书代号)
FROM Titleauthor
GROUP BY 作家代号;

7、创建视图按图书类型收集图书的销售数量；

CREATE VIEW Book_Sale
AS
(SELECT 类型 销售数量
FROM Titles
GROUP BY 类型);

8、将psychology类型的图书价格提高5%。

UPDATE Titles
SET 价格=价格*1.05
WHERE 类型=’psychology’;

四、综合题
考虑下面关系模式：
研究人员（人员编号，姓名，年龄，职称）
项目（项目编号，名称，负责人编号，类别）
参与（项目编号，人员编号，工作时间）
其中，一个研究人员可以参加多个项目，一个项目可以有多个研究人员参加，工作时间为某人员参加某项目的月数
写出下列完整性约束：
1.定义三个关系，同时廷议主码、外码、参照完整性；

研究人员（人员编号，姓名，年龄，职称）
项目（项目编号，名称，负责人编号，类别）
参与（项目编号，人员编号，工作时间）
~~~~~~~~ ~~~~~~~~

2.采用alter table add constraint 声明如下约束：
（1）研究人员的年龄不能超过35岁；

ALTER 研究人员
ADD CONSTRAINT worker_age
CHECK（年龄<=’5);

（2）每个研究人员的职称只能为“讲师”、“副教授”、“教授”；

ALTER 研究人员
ADD CONSTRAIBT worker_title
CHECK (职称 IN(‘讲师’ ‘副教授’ ‘教授’ ));

（3）每个研究人员参加项目的总工作时间不能超过12个月；

CREAT ASSERTION ASSE1
CHECK(12>=ALL(SELECT SUM(工作时间)
FROM 参与
GROUP BY 人员编号))
（4）每个项目至少有5位研究人员；
CREATE ASSERTION ASSE2
CHECK(5<=ALL(SELECT COUNT(人员编号)
FROM 参与
GROUP BY 项目编号))

（5）每个研究人员参加的项目数不能超过3个；
CREATE ASSERTION ASSE3
CHECK (3>=ALL (SELECT COUNT(项目编号)
FROM 参与
GROUP BY 人员编号))

（6）负责人编号参照研究人员的“人员编号”属性，当研究人员更新时，若违反约束，则拒绝操作；

ALTER 项目
ADD CONSTRAINT worker_update
CHECK(负责人编号 REFERENCE 研究人员(人员编号) ON UPDATE RESTINCT);

（7）同（6），当违反约束时，将负责人编号置为空值；

ALTER 项目
ADD CONSTRAINT worker_update
CHECK(负责人编号 REFERENCE 研究人员(人员编号) ON UPDATE SET NULL);

（8）项目名称不能为空。

ALTER 项目
ADD CONSTRAINT name_nnull
CHECK (名称 is not NULL);

五、考虑下面关系模式：
Teacher（Tno,Tname,Tage,Tsex）
Department(Dno, Dname, Tno) Tno为系主任的工号
Work（Tno， Dno， Year ，Salary）
写出以下触发器：
1.在插入新教师时，也要将此教师信息插入到work关系中，不确定的属性赋予空值。

CREATE TIGGER TRIG1
AFTER INSERT
ON Teacher
REFERENCE NEW AS newi
FRO EACH ROW
BEGINE
INSRT INTO WORK VALUES (newi.Tno, NULL,NULL ,NULL)
END;
2.在更新教师年龄是，如果新年龄比旧年龄小，则用旧年龄代替。

CREATE TRIGGER TRIG2
AFTER UPDATE OF Tage ON Teacher
REFERENCING
OLD AS OLDTUPLE
NEW AS NEWTUPLE
FROM EACHROW
WHEN(OLDTUPLE.Tage<NEWTUPLE.Tage)
UPDATE Teacher SETAGE = LODTULE.Tage
WHERE Tage=NEWTUPLE.Tage