习题二
一、 一个简单的著作管理数据库，涉及如下数据：
作家Author：作家号Authorno、姓名Name、性别Sex和级别Grade
作品Book：书号Bookno、书名Title、种类Type、发行时间Date和出版社Publish
一位作家可以有多本著作，一本著作可同时多人合著，要求能描述作家出书的情况。
1）、设计简单的著作管理系统的E/R图；
2）转换成关系模型（请注明每个关系的键码）
二、 设有一个简单的经销商产品数据库如下：
生产厂商关系：Producer（PrNo, Name, Addr, Tel），分别表示生产厂商号、
厂商名称、地址和联系电话
产品关系：Product（TtNo, Brand, Model, Price），分别表示产品号、名称、
品牌型号和价格等
供应关系：Supply（PrNo, PtNo, Quantity），分别表示生产厂商号、产品号以及数量
1）、查找生产厂商“方正科技公司”供应的产品的名称和价格，结果按价格的
降序排列；
2）、求各类产品的最高价格、最低价格、平均价格和数量；
3）、查询一种产品供应供应数量在500件以上的生产厂商名称。
三、 用SQL的有关语句定义：（共12分，每小题4分）
1)、航班关系Flight，包括航班号Number、机型Kind、起飞日期GoDate；
2）、乘客关系Customer，包括身份证号码Idno、姓名Name、性别Sex、地址Addr；
3）、搭乘关系Booking，包括身份证号码Idno、航班号Number、座位Seat；
要求说明每个关系的主键码和外键码（如果有的话），并在键码属性上建立索引。
4）、授予王飞对航班关系Flight的修改和删除的权限；
5）、删除2001年4月1日的所有航班记录；
6）、将航班号为KH3001的机型改为波音757；
7）、对航班关系的日期属性进行约束，使得其日期不得是2000年以前的。
四、按嵌入式SQL的格式在给出的产品关系Product中插入任意一个产品的产品号、
名称、品牌型号、价格等。）
一、
注：Part表明作家是独立完成著作还是第一、第二作者等。
2）转换成关系模型为：
Author ( Authorno, Name, Sex, Grade)
Book ( Bookno, Title, Type, Date, Publish)
Write ( Authorno, Bookno, Part)
二、SQL查询语句：
1）、SELECT Brand, Price
FROM Product
WHERE PtNo IN
( SELECT PtNo
FROM Supply, Producer
WHERE Supply.PrNo=Producer.PrNo AND Name= “方正科技公司”)
ORDER BY Price DESC ;
或者：
SELECT Brand, Price
FROM Product, Supply, Producer
WHERE Product.PtNo=Supply.PtNo AND Supply.PrNo=Producer.PrNo
AND Name== “方正科技公司”
ORDER BY Price DESC ;
或者：
SELECT Brand, Price
FROM Product
WHERE PtNo IN
( SELECT PtNo
FROM Supply
WHERE PrNo IN
( SELECT PrNo
FROM Producer
WHERE Name== “方正科技公司”))
ORDER BY Price DESC ;
2)、SELECE Brand, max(Price) AS 最高价, min(Price) AS 最低价,
avg(Price) AS 平均价, count(Brand) AS 数量
FROM Product
GROUP BY Brand ;
3）、 SELECT DISTINCT Name
FROM Producer
WHERE PrNo IN ( SELECT PrNo
FROM Supply
WHERE Quantity>500) ;
或者：
SELECT DISTINCT Name
FROM Producer, Supply
WHERE Producer.PrNo=Supply.PrNo AND Quantity>500 ;
三、用SQL的有关语句定义：（共12分，每小题4分）
1）、 CREATE TABLE Flight
(Number CHAR(10) NOT NULL PRIMARY KEY ,
Kind CHAR(10) ,
GoDate DATE ) ;
CREATE INDEX Fkey ON Flight (Number);
2）、 CREATE TABLE Customer
( Idno VARCHAR(18) NOT NULL PRIMARY KEY ,
Name CHAR(10) NOT NULL ,
Sex BOOLEAN ,
Addr VARCHAR(30) ) ;
CREATE INDEX Ckey ON Customer (Idno);
3）、 CREATE TABLE Booking
( Idno VARCHAR(18) NOT NULL ,
Number CHAR(10) NOT NULL ,
Seat CHAR(4) ,
PRIMARY KEY(Idno, Number) ,
FOREIGN KEY(Idno) REFERENCES Customer(Idno ) ,
FOREIGN KEY(Number) REFERENCES Flight ( Number) ) ;
CREATE INDEX Bkey ON Booking (Idno, Number) ;
三 SQL的有关语句实现：（共12分，每小题3分）
1）、GRANT UPDATE, DELETE ON Flight TO 王飞
2）、DELETE FROM Flight WHERE GoDate= “2001-04-01” ;
3)、UPDATE Flight SET Kind= “波音757”
WHERE Number= “KH3001” ;
4)、 在定义Flight时，加入对属性GoDate的约束如下：
GoDate DATE CHECK (GoDate>= “2001-01-01”) ,
四、（8分）
1）说明： EXEC SQL BEGIN DECLARE SECTION;
char pno[10];
char b[20];
char m[20];
float p;
char SQLSTATE[6];
EXEC SQL END DECLARE SECTION;
2）赋值： pno:= “P300101” ;
b:= “内存条” ;
m:= “Kindmax 128” ;
p:=136 ;
3）插入： EXEC SQL INSERT INTO Product
VALUES(:pno, :b, :m, :p) ;
