习题二
一、 一个简单的著作管理数据库,涉及如下数据:
作家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中插入任意一个产品的产品号、
名称、品牌型号、价格等。)
一、
Member |
Write
|
Book
|
Part
|
Name
|
Sex |
Grade
|
Authorno
|
Bookno
|
Title
|
Publish
|
注: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) ;