数据库设计例题共9页.docx

1、数据库设计例题共9页习题(xt)一1用SQL的有关(yugun)语句定义1）学生关系Student，包括(boku)学号Sno、姓名SN、年龄SA；2）课程(kchng)关系Course，包括课程号Cno、课程名CN、任课教师CT；3）学生选课关系，包括(boku)Sno、Cno和成绩G。注意：说明主键码和外键码（如果有的话），并在键码属性上建立索引。2按题1的学生关系模式，用SQL的有关语句1）授予赵华对学生关系Student的插入和修改权限；2）插入任意3个学生的完整信息；3）修改所有学生的年龄（加1）。3结合题1定义的学生选课数据库，用SQL语句查询计算机系学生数据库课的成绩，输出学生姓

2、名和成绩，按成绩排序（降序），若成绩相同，再按学号排序（升序）。4按嵌入式SQL的格式插入任意一个学生的学号、姓名、系别和年龄。（6分）5商店销售商品的数据库，涉及如下数据：商店号、商店名、地址商品号、商品名、单价。某商店销售某商品的数量和日期。1）设计E/R图（属性名用中、英文均可）；2）转换成关系模型（注明键码和函数依赖）。6有关图书发行的关系模型如下：书店S（书店号SNo，书店名SN，地址A）图书B（书号BNo，书名BN，价格P）图书馆L（馆号LNo，馆名LN，城市C）图书发行LBS（LNo，BNo，SNo，发行量Q） 分别用关系代数和SQL语言表达如下查询： 图书馆L4（馆号）收藏图书

3、的书名和出售该书的书店名。7写出3个关系模式分别满足：1）是1NF，不是2NF；2）是2NF，不是3NF；3）是3NF，也是BCNF；各用两句话分别说明你所写的关系模式是前者，不是（或也是）后者。11） CREATE TABLE Student( Sno INT PRIMARY KEY, SN CHAR(30), SA INT); 2) CREATE TABEL Course( Cno INT PRIMARY KEY, CN CHAR(30), CT CHAR(30); 3) CREATE TABEL SC( Sno INT, Cno INT, G INT, PRIMAYR KEY(Sno,C

4、no), FOREIGN KEY(Sno) REFERENCES Student(Sno), FOREIGN KEY(Cno) REFERENCES Course(Cno);2 1） GRANT INSERT, UPDATE, ON Student TO 赵华； 2） INSERT INTO Student (TABLE (01001,赵华,19), (01002,李芳,18), (01003,刘红,20), ); 3） UPDATE Student SET SA=SA+1;3 SELECT SN,G FROM Student,Course,SC WHERE Student.Sno=SC.Sn

5、o AND Course.Cno=SC.Cno AND Course.CN=数据库 ORDER BY G DESC,Sno;4 1）说明(shumng)： EXEC SQL BEGIN DECLARE SECTION; int sno; char sname30; int sage; char SQLSTATE6; EXEC SQL END DECLARE SECTION;2）赋值： sno:=01001; sname:=赵华; sage:=19;3）插入(ch r)： EXEC SQL INSERT INTO Student VALUES(:sno,:sname,:sage);51）2） 商

6、店(shngdin)（商店(shngdin)号，商店(shngdin)名，地址） 商店号商店名，地址 商品（商品号，商品名，单价） 商品号商品名，单价 销售(xioshu)（商店(shngdin)号，商品号，数量(shling)，日期） 商店号，商品号数量(shling)，日期6 1） BN,SN（ LNo=L4（B LBS S） 2） SELECT BN,SN FROM B,LBS,S WHERE B.BNo=LBS.BNo AND LBS.SNo=S.SNo AND LBS.LNo=L4;7 1）学生(xu sheng)选课（学号，姓名，课程号，成绩） 属性不可分，是1NF；存在非主属性对

7、键码的部分依赖（学号，课程号姓名），不是2NF。 2）学生（学号，姓名，系别，系主任） 键码为单属性，不存在部分依赖，是2NF；存在非主属性对键码的传递依赖（学号姓名，系别；系别学号；系别系主任；学号系主任），不是3NF。3）学生（学号，姓名，年龄）非主属性（姓名，年龄）对键码不存在部分依赖和传递依赖，是3NF；主属性（学号）对键码也不存在部分依赖和传递依赖，是BCNF。习题(xt)二一、一个简单的著作(zhzu)管理数据库，涉及如下数据：作家Author：作家号Authorno、姓名(xngmng)Name、性别Sex和级别Grade作品Book：书号Bookno、书名Title、种类(zh

8、ngli)Type、发行时间Date和出版社Publish一位作家可以有多本著作，一本著作可同时多人合著(h zh)，要求能描述作家出书的情况。1）、设计简单的著作管理系统的E/R图；2）转换成关系模型（请注明每个关系的键码）二、设有一个简单的经销商产品数据库如下： 生产厂商关系：Producer（PrNo, Name, Addr, Tel），分别表示生产厂商号、厂商名称、地址和联系电话 产品关系：Product（TtNo, Brand, Model, Price），分别表示产品号、名称、品牌型号和价格等 供应关系：Supply（PrNo, PtNo, Quantity），分别表示生产厂商号、

9、产品号以及数量1）、查找生产厂商“方正科技公司”供应的产品的名称和价格，结果按价格的降序排列； 2）、求各类产品的最高价格、最低价格、平均价格和数量； 3）、查询一种产品供应供应数量在500件以上的生产厂商名称。三、用SQL的有关(yugun)语句定义：（共12分，每小题4分）1)、航班关系(gun x)Flight，包括航班号Number、机型Kind、起飞日期GoDate；2）、乘客关系Customer，包括身份证号码Idno、姓名(xngmng)Name、性别Sex、地址Addr；3）、搭乘关系(gun x)Booking，包括身份证号码Idno、航班号Number、座位Seat；要求说

10、明每个关系的主键码和外键码（如果有的话(dehu)），并在键码属性上建立索引。 4）、授予王飞对航班关系Flight的修改和删除的权限；5）、删除2001年4月1日的所有航班记录；6）、将航班号为KH3001的机型改为波音757；7）、对航班关系的日期属性进行约束，使得其日期不得是2000年以前的。四、按嵌入式SQL的格式在给出的产品关系Product中插入任意一个产品的产品号、名称、品牌型号、价格等。）一、 1）、简单图书管理系统的E/R图为： 注：Part表明作家是独立完成著作还是第一、第二作者等。2）转换成关系模型为：Author ( Authorno, Name, Sex, Grade

11、)Book ( Bookno, Title, Type, Date, Publish)Write ( Authorno, Bookno, Part)二、SQL查询语句： 1）、SELECT Brand, Price FROM Product WHERE PtNo IN( SELECT PtNoFROM Supply, ProducerWHERE Supply.PrNo=Producer.PrNo AND Name= “方正科技公司(n s)”)ORDER BY Price DESC ;或者(huzh)：SELECT Brand, Price FROM Product, Supply, Prod

12、ucer WHERE Product.PtNo=Supply.PtNo AND Supply.PrNo=Producer.PrNo AND Name= “方正科技公司(n s)” ORDER BY Price DESC ;或者(huzh)：SELECT Brand, Price FROM Product WHERE PtNo IN( SELECT PtNoFROM SupplyWHERE PrNo IN ( SELECT PrNo FROM Producer WHERE Name= “方正科技公司(n s)”) ORDER BY Price DESC ; 2)、SELECE Brand, ma

13、x(Price) AS 最高价, min(Price) AS 最低价, avg(Price) AS 平均价, count(Brand) AS 数量FROM ProductGROUP BY Brand ; 3）、 SELECT DISTINCT Name FROM Producer WHERE PrNo IN ( SELECT PrNoFROM SupplyWHERE Quantity500) ; 或者： SELECT DISTINCT Name FROM Producer, Supply WHERE Producer.PrNo=Supply.PrNo AND Quantity500 ;三、用S

14、QL的有关语句(yj)定义：（共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 INDE

15、X 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) ;三 SQ

16、L的有关(yugun)语句实现：（共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的约束(yush)如下： GoDate DATE CHECK (GoDate= “2001-01-01”) ,四、（8分）1）说明(shumng)： EXEC SQL BEGIN DECLARE

17、SECTION; char pno10; char b20; char m20; float p; char SQLSTATE6; EXEC SQL END DECLARE SECTION;2）赋值： pno:= “P300101” ; b:= “内存条” ; m:= “Kindmax 128” ; p:=136 ;3）插入(ch r)： EXEC SQL INSERT INTO Product VALUES(:pno, :b, :m, :p) ; 内容总结（1）习题一用SQL的有关语句定义学生关系Student，包括学号Sno、姓名SN、年龄SA（2）3）、查询一种产品供应供应数量在500件以上的生产厂商名称（3）p:=136