数据库综合练习3

数据库综合练习三习题一1.现有一个局部应用，包括两个实体：“出版社”和“作者”，这两个实体是多对多的联系，请设计适当的属性，画出E-R图，再将其转换为关系模型（包括关系名、属性名、码和完整性约束条件）。

2.请设计一个图书馆数据库，此数据库中对每个借阅者保存的记录包括：读者号，姓名，地址，性别，年龄，单位。

对每本书保存有：书号，书名，作者，出版社。

对每本被借出的书保存有读者号、借出日期和应还日期。

要求：给出该图书馆数据库的E-R图，再将其转换为关系模型。

3.图4-1是某个教务管理数据库的E-R图，请把它们转换为关系模型（图中关系、属性和联系的含义，已在它旁边用汉字标出）。

图4-1教学管理数据库4.图4-2是一个销售业务管理的E-R图，请把它转换成关系模型。

5.设有一家百货商店，已知信息有：l）每个职工的数据是职工号、姓名、地址和他所在的商品部。

2）每一商品部的数据有：它的职工，经理和它经销的商品。

3）每种经销的商品数有：商品名、生产厂家、价格、型号（厂家定的）和内部商品代号（商店规定的）。

4）关于每个生产厂家的数据有：厂名、地址、向商店提供的商品价格。

请设计该百货商店的概念模型，再将概念模型转换为关系模型。

注意某些信息可用属性表示，其他信息可用联系表示。

6.下列有关E-R模型向关系模型转换的叙述中。

不正确的是_______。

A. 一个实体类型转换为一个关系模式B. 一个1:1 联系可以转换为一个独立的关系模式，也可以与联系的任意一端实体所对应的关系模式合并C. 一个1:n 联系可以转换为一个独立的关系模式，也可以与联系的任意一端实体所对应的关系模式合并D. 一个m:n 联系转换为一个关系模式习题一解答1．答：转换后的关系模型如图6所示。

出版社(出版社名，地址，邮政编码）；作者（姓名，性别，年龄，证件号码，单位）；出版（出版社名，作者姓名，出书数量，联系方式）。

图62．答：该图书馆数据库的E－R图如图7图7其中：读者：读者号，姓名，地址，性别，年龄，单位；图书．书号，书名，作者，出版社。

转换后的关系模型为：借阅者（读者号，姓名，地址，性别，年龄，单位）；书籍（书号，书名，作者，出版社）；借阅（读者号，书号，借出日期，应还日期）。

3 答：转换后的关系模型如下：Student（SNO，SN，SD，SA）；Teacher(TNO，TN，TD，TG）；Course（CNO，CN，PCNO）；ST（SNO，TNO）；SC（SNO，CNO，G）；TC（TNO，CNO）。

4．答：转换后的关系模型如下工厂（厂名，厂长，地址）；产品（编号，型号，单价）；用户（姓名，地址，电话）；工厂-产品-用户（厂名，编号，姓名）。

5．答：概念模型如图8所示。

关系模型为：职工（职工号，姓名，住址，工作商品部）；商品部（商品部号，名称，经理职工号，经理名）；商品（商品代号，价格，型号，出厂价格）；生产厂家（厂名，地址）；销售（商品代号，商品部号）；生产（厂名，商品代号）。

图8 百货商店E-R图模型图中：职工：职工号，姓名，住址；商品部：商品部号，名称；商品：商品代号，价格，型号，出厂价格；生产厂家：厂名，地址。

6．答：D习题二1. 设职工社团数据库有三个基本表:职工（职工号，姓名，年龄，性别）；社会团体（编号，名称，负责人，活动地点）；参加（职工号，编号，参加日期）。

其中：1）职工表的主码为职工号。

2）社会团体表的主码为编号；外码为负责人，被参照表为职工表，对应属性为职工号。

3）参加表的职工号和编号为主码；职工号为外码，其被参照表为职工表，对应属性为职工号；编号为外码，其被参照表为社会团体表，对应属性为编号。

试用SQL语句表达下列操作：l）定义职工表、社会团体表和参加表，并说明其主码和参照关系。

2）建立下列两个视图。

社团负责人（编号，名称，负责人职工号，负责人姓名，负责人性别）；参加人情况（职工号，姓名，社团编号，社团名称，参加日期）3）查找参加唱歌队或篮球队的职工号和姓名。

4）查找没有参加任何社会团体的职工情况。

5）查找参加了全部社会团体的职工情况。

6）查找参加了职工号为“1001”的职工所参加的全部社会团体的职工号。

7）求每个社会团体的参加人数。

8）求参加人数最多的社会团体的名称和参加人数。

9）求参加人数超过100人的社会团体的名称和负责人。

10）把对社会团体和参加两个表的数据查看、插入和删除数据的权力赋给用户李平，并允许他再将此权力授予其他用户。

2. SQL 语言集数据查询、数据操作、数据定义和数据控制功能于一体，语句INSERT、DELETE、UPDATA实现下列哪类功能________。

A. 数据查询B. 数据操纵C. 数据定义D. 数据控制3. 下面列出的关于视图（View）的条目中，不正确的是________。

A. 视图是外模式B. 视图是虚表C. 使用视图可以加快查询语句的执行速度D. 使用视图可以简化查询语句的编写4. 在SQL语言的SELECT语句中，能实现投影操作的是________。

A. SELECTB. FROMC. WHERED. GROUP BY5. SQL语言集数据查询、数据操纵、数据定义和数据控制功能于一体，语句ALTER TABLE 实现哪类功能_______。

A. 数据查询B. 数据操纵C. 数据定义D. 数据控制6. 在关系数据库系统中，为了简化用户的查询操作，而又不增加数据的存储空间，常用的方法是创建_______。

A. 另一个表B. 游标C. 视图D. 索引7. 设要建立学生选课数据库，库中包括学生、课程和选课3个表，其表结构为：学生（学号，姓名，性别，年龄，所在系）；课程（课程号，课程名，先行课），选课（学号，课程号，成绩）。

用 Transact-SQL完成下列操作。

l）建立学生选课库。

2）建立学生、课程和选课表。

3）建立各表以主码为索引项的索引。

4）建立学生性别只能为“男”或“女”的规则，性别为“男”的缺省，并将它们绑定在学生表上。

5）建立在对选课表输入或更改数据时，必须服从参照完整性约束的INSERT和UPDATE 触发器。

6）建立在删除学生记录时，同时也要把相应的选课记录删除的触发器。

7）查询各系及学生数，最后来出共有多少系和多少学生。

8）将学生表和选课表进行内连接、左外连接和右外连接。

9）列出学生学号、姓名及学习情况。

学习情况用好、较好、一般或较差表示。

当平均成绩大于85分时，学习情况为好；当平均成绩在70～85分之间，学习情况为较好；当平均成绩在60～70分之间，学习成绩为一般；平均成绩在60分以下的为学习情况较差。

习题二解答1．答：1）CREAT TABLE职工（职工号CHAR（l0）NOT NULL UNIQUE，姓名CHAR（8）NOT NULL，年龄SMALLINT，性别CHAR（2）,CONSTRAINT C1 CHECK（性别IN（‘男’，‘女’）））；CREAT TABLE社会团体（编号CHAR（8）NOT NULL UNIQUE，名称CHAR（12）NOT NULL，负责人CHAR（8），活动地点V ARCHAR（50），CONSTRAINT C2 FOREIGN KEY（负责人）REFERENCES职工（职工号））；CREAT TABLE参加（职工号CHAR（8），编号CHAR（8），参加日期DATECONSTRAINT C3 PRIMARY KEY（职工号，编号），CONSTRAIN C3 FOREIGN KEY (职工号）REFERENCES职工（职工号））；2）CREAT VIEW社团负责人（编号，名称，负责人职工号，负责人姓名，负责人性别）AS SELECT编号，名称，负责人，姓名，性别FROM社会团体，职工WHERE社会团体，负责人=职工．职工号；CREAT VIEW参加人情况（职工号，姓名，社团编号，社团名称，参加日期）AS SELECT参加．职工号，姓名，社会团体编号，名称，参加日期FROM职工，社会团体，参加WHERE职工．职工号=参加．职工号AND参加．编号=社会团体．编号；3）SELECT职工号，姓名FROM 职工，社会团体，参加WHERE职工．职工号=参加．职工号AND参加．编号=社会团体．编号AND社会团体．名称IN（‘唱歌队’，‘篮球队’）4）SELECT*FROM 职工WHERE NOT EXISTS（SELECT*FROM参加WHERE参加．职工号=职工．职工号）；5）SELECT*FROM职工WHERE NOT EXISTS（SELECT*FROM参加WHERE NOT EXISTS（SELECT*FROM社会团体WHERE参加．职工号=职工．职工号AND参加．编号=社会团体．编号））；6）SELECT职工号FROM职工WHERE NOT EXISTS（SELECT*FROM参加参加1WHERE参加1．职工号=‘1001’AND NOT EXISTS（SELECT*FROM参加参加2WHERE 参加2．编号=参加1．编号AND参加2．职工号一职工．职工号））；7）SELECT TCOUNT（职工号）FROM 参加GROUP BY编号；2．答：B3．答：C4．答：A5．答：C6．答：C7．答：l）CREATE DA TABASE学生选课库ON PRIMARY（NANE=学生选课库，FILENAME＝‘C:＼msSQL\data\学生选课．mdf’，SIZE=4MB，MAXSIZE＝6MB，FILEROWHT=2MB）GO2）CREAT TABLE学生（学号CHAR10）PRIMARY KEY CLUSTERED，姓名CHAR（8），性别CHAR（2），年龄SMALLINT所在系VARCHAR（50））GOCREAT TABLE课程（课程号CHAR（10）PRIMARY KEY CLUSTERED，课程名V ARCHAR（50），先行课V ARCHAR（50））GOCREATE TABLE选课（学号CHAR（10），课程号VARCHAR（50），成绩SMALLINT，CONSTRAINT C1 PRIMARY KEY（学号，课程号）,CONSTRAINT C2 FOREIGN KEY（学号）REFRENCES学生（学号）,CONSTRAINT FOREIGN KEY（课程号）REFERENCES课程（课程号））GO3）CREATE INDEX student_ind ON学生（学号）GOREATEI＊＊巨class_ind ON课程（课程号）GOCREATE INDEX select_ind ON选课（学号，课程号）GO4）CREAT RULE value_rule AS ＠V ALUE IN（‘男’，‘女’）GOCREAT DEFAULE性别缺省AS‘男’GOEXEC sp_bindrule ‘value_rule’，‘学生．性别’GOEXEC Sp_binddefault ‘性别缺省’，‘学生．性别’GO5）CREATE TRIGGER sc_insert ON选课FOR INSERTAS IF（SELECT COLJNT（*）FROM 学生，inserted，课程WHERE学生．学号=inserted．学号AND课程．课程号=inserted．课程号)=0 ROLLBACK TRANSACTIONGOCREATE TRIGGER sc_updat6 ON选课FOR UPDA TEAS IF（SELECT COUNT（*）FROM 学生，updated，课程WHERE学生．学号=updated．学号AND课程．课程号=updated．课程号)=0 ROLLBACK TRANSACTIONGO6）CREATE TRIGGER delete_all ON 学生FOR DELETEAS DELETEFROM选课WHERE选课．学号=deleted．学号GO7）SELECT所在系，COUNT(学号）FROM学生GROUP BY学生．所在系COMPUTE COUNT（DISTINCT（所在系））COMPUTE COUNT学号）BY所在系GO8）SELECT*FROM学生INNER JOIN课程GOSELECT*FROM学生LEFT OUTER JOIN课程GOSELECT*FROM学生RIGHT OUTER JOIN课程GO9）SELECT选课．学号，学生．姓名，学习情况=CASEWHEN A VG(成绩）＞＝85 THEN‘好’WHEN A VG(成绩）＞=75 AND A VG(成绩)< 85 THEN‘较好’WHEN A VG(成绩）＜60 THEN‘较差’ENDFROM 学生选课WHERE 学生．学号=选课．学号 GROUP BY 选课．学号GO习题三1. 设关系模式R<A ，B ，C ，D>，函数依赖集F ＝{A→C，C→A， B→AC，D→AC，BD→A｝。