mysql数据库武洪萍版第四章习题与答案

一．选择题1.下面哪种数字数据类型不可以存储数据256？（D）A.bigintB. intC. SmallintD. tinyint2.下面是有关主键和外键之间的关系描述，正确的是（AC）A.一个表最多只能有一个主键约束，多个外键约束。

B.一个表中最多只有一个外键约束，一个主键约束。

C.在定义主键外键约束时，应该首先定义主键约束，然后定义外键约束。

D.在定义主键外键约束时，应该首先定义主键约束，然后定义主键约束。

3.下面关于数据库中表的行和列的叙述正确的是（D）A.表中的行是有序的，列是无序的B. 表中的列是有序的，行是无序的C. 表中的行和列都是有序的D. 表中的行和列都是无序的4.SQL语言的数据操作语句包括SELECT、INSERT、UPDATE、DELETE等。

其中最重要的，也是使用最频繁的语句是（A）A.SELECTB.INSERTC.UPDATED.DELETE5.在下列SQL语句中，修改表结构的语句是（A）。

A.ALTERB. CREATEC. UPDATED. INSERT6.设有关系R（A,B,C)和S（C,D），与关系代数表达式πA,B,D(σR.C=S.C(R ∞S)等价的SQL语句是（B）。

A.SELECT *FROM R,S WHERE R.C=S.CB.SELECT A,B,D FROM R,S WHERE R.C=S.CC.SELECT A,B,D FROM R,S WHERE R=SD.SELECT A,B FROM R WHERE(SELECT D FROM S WHERE R.C=S.C)7.设关系R（A,B,C) 与SQL语句“SELECT DISTINST A FROM R WHERE B=17”等价的关系代数表达式是（A）A.πA(σB=17 (R))B. σB=17 (πA(R))C. σB=17 (πA. C(R))D. πA. C(σB=17 (R))下面第（8）-（12）题，基于“学生-选课-课程”数据库中的3个关系。

MySQL数据库基础与实例教程练习题参考答案由于时间仓促，中难免存在错误，不妥之处恳请读者批评指正！第一章答案1．数据库管理系统中常用的数学模型有哪些？数据库管理系统通常会选择某种“数学模型”存储、组织、管理数据库中的数据，常用的数学模型包括“层次模型”、“网状模型”、“关系模型”以及“面向对象模型”等。

2．您听说过的关系数据库管理系统有哪些？数据库容器中通常包含哪些数据库对象？目前成熟的关系数据库管理系统主要源自欧美数据库厂商，典型的有美国微软公司的SQL Server、美国IBM公司的DB2和Informix、德国SAP公司的Sybase、美国甲骨文公司的Oracle。

数据库容器中通常包含表、索引、视图、存储过程、触发器、函数等数据库对象。

3．通过本章知识的讲解，SQL与程序设计语言有什么关系？SQL并不是一种功能完善的程序设计语言，例如，不能使用SQL构建人性化的图形用户界面（Graphical User Interface，GUI），程序员需要借助Java、VC++等面向对象程序设计语言或者HTML的FORM表单构建图形用户界面（GUI）。

如果选用FORM表单构建GUI，程序员还需要使用JSP、PHP或者.NET编写Web应用程序，处理FORM表单中的数据以及数据库中的数据。

其他答案：1、首先SQL语言是数据库结构化查询语言，是非过程化编程语言。

而程序设计语言则有更多的面向对象及逻辑程序设计。

比如用SQL语言编写图形用户界面（例如窗口、进度条），是无法实现的。

2、SQL语言可以说是，程序设计语言和数据库之间的一个翻译官。

程序设计语言需要操作数据库时，需要借助（或者说调用）SQL语言来翻译给数据库管理系统。

3、不同数据库管理系统会有一些特殊的SQL规范，比如limit关键词在SQL Server 中无法使用。

而这些规范与程序设计语言无关。

4．通过本章的学习，您了解的MySQL有哪些特点？与题目2中列举的商业化数据库管理系统相比，MySQL具有开源、免费、体积小、便于安装，但功能强大等特点。

项目4一、选择题1、D2、D3、B D4、C5、B6、C7、C8、D二、填空题1、逻辑排序查询效率（速度）2、删除3、基表4、WITH CHECK OPTION三、判断题1、对2、对3、对4、对5、对6、对四、简答题1、答：索引的优点：（1）提高数据查询的速度：索引能够以一列或多列的值为排序依据，实现快速查找数据行。

（2）优化查询：数据库系统的查询优化器是依赖于索引起作用的，索引能够加速连接、分组和排序等操作。

（3）确保数据的唯一性：通过给列创建唯一索引，可以保证表中的数据不重复。

索引的缺点：（1）创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

（2）索引需要占物理空间，如果要建立聚簇索引，则需要的空间就会更大。

（3）当对表中数据进行增加、修改和删除时，索引也要动态的进行维护，因而会降低数据的更新速度。

表中索引越多，则更新表的时间就越长。

2、答：（1）很少或从来不作为查询条件的列。

（2）在小表中通过索引查找行可能比简单地进行全表扫描还慢。

（3）只从很小的范围内取值的列，即字段重复值比较多的列。

（4）数据类型为Text、Blob和Bit的列上不宜创建索引。

（5）值需要经常修改的列不适合创建索引。

答：按照索引作用的机理分类，可分为普通索引（INDEX）、唯一索引（UNIQUE）、主键（PRIMARY KEY）、全文索引（FULLTEXT）和空间索引（SPATIAL）等类型。

4、答：在MySQL中，可通过CREATE OR REPLACE VIEW语句和ALTER VIEW语句两种方法来修改视图。

CREATE OR REPLACE VIEW语句基本语法格式为：CREATE OR REPLACE VIEW 视图名[(列名[,...n ])]AS SELECT语句;ALTER VIEW语句基本语法格式为：ALTER VIEW 视图名[(列名[, ...n])]AS SELECT语句;五、项目实践（训）题(1)ALTER TABLE bemployee ADD UNIQUE INDEX ix_i dentity(i dentity);或CREATE UNIQUE INDEX ix_i dentity ON bemployee(i dentity);查看是否创建成功：SHOW CREATE TABLE bemployee\G(2)ALTER TABLE bleave ADD INDEX mulix_date(employeeid,start_date);或CREATE INDEX mulix_date ON bleave(employeeid,start_date);查看是否创建成功：SHOW CREATE TABLE bleave\G(3)ALTER TABLE bsalary ADD INDEX ix_employeeid(employeeid), ADD INDEX ix_totalsalary(total_salary DESC);(4)创建视图：USE peopleCREATE VIEW dept_infoASSELECT deptid, deptname, deptnum FROM bdept;从该视图中查询出部门人数大于10人的部门记录:SELECT * FROM dept_info WHERE deptnum >10创建视图：USE peopleCREATE VIEW employee_infoASSELECT * FROM bemployee WHERE deptid= '2003';查看该视图的结构信息：DESC employee_info;(6)SELECT name, sex,politicalFROM employee_infoWHERE name LIKE '张%' AND sex= '女';。

第4章数据库安全性1 ．什么是数据库的安全性？答：数据库的安全性是指保护数据库以防止不合法的使用所造成的数据泄露、更改或破坏。

2 ．数据库安全性和计算机系统的安全性有什么关系？答：安全性问题不是数据库系统所独有的，所有计算机系统都有这个问题。

只是在数据库系统中大量数据集中存放，而且为许多最终用户直接共享，从而使安全性问题更为突出。

系统安全保护措施是否有效是数据库系统的主要指标之一。

数据库的安全性和计算机系统的安全性，包括操作系统、网络系统的安全性是紧密联系、相互支持的，3 ．试述可信计算机系统评测标准的情况，试述TDI / TCSEC 标准的基本内容。

答：各个国家在计算机安全技术方面都建立了一套可信标准。

目前各国引用或制定的一系列安全标准中，最重要的是美国国防部（DoD ）正式颁布的《DoD 可信计算机系统评估标准》（伽sted Co 哪uter system Evaluation criteria ，简称TcsEc ，又称桔皮书）。

（TDI / TCSEC 标准是将TcsEc 扩展到数据库管理系统，即《可信计算机系统评估标准关于可信数据库系统的解释》（Tmsted Database Interpretation 简称TDI , 又称紫皮书）。

在TDI 中定义了数据库管理系统的设计与实现中需满足和用以进行安全性级别评估的标准。

TDI 与TcsEc 一样，从安全策略、责任、保证和文档四个方面来描述安全性级别划分的指标。

每个方面又细分为若干项。

4 ．试述T csEC ( TDI ）将系统安全级别划分为4 组7 个等级的基本内容。

答：根据计算机系统对安全性各项指标的支持情况，TCSEC ( TDI ）将系统划分为四组（division ) 7 个等级，依次是D 、C ( CI , CZ ）、B ( BI , BZ , B3 ）、A ( AI ) ，按系统可靠或可信程度逐渐增高。

这些安全级别之间具有一种偏序向下兼容的关系，即较高安全性级别提供的安全保护包含较低级别的所有保护要求，同时提供更多或更完善的保护能力。

第四章一、填空题1.椭圆框2.属性3.关系4.物理设计5.一个或多个二、判断题1.错2.对3.错4.对5.错三、选择题1. B2. B3. C4.B、C、D5.B、C四、简答题1.请简述数据库设计规范化的必要性。

答：数据库设计对数据的存储性能、数据的操作都有很大的关系。

为了避免不规范的数据库出现数据冗余，造成插入、删除、更新操作异常等情况，就要进行数据库设计规范化。

2.请分析数据库范式1NF、2NF、3NF的区别。

答：（1）1NF：数据库表的每一列都是不可分割的基本数据项。

（2）2NF：在满足1NF基础上，遵从唯一性，非主键字段需完全依赖主键（3）3NF：在满足2NF基础上，非主键字段不能相互依赖。

五、实训题1.请完成电子商务网站用户等级的数据表设计。

CREATE TABLE sh_user_level (id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '等级id',name VARCHAR(20) NOT NULL DEFAULT '' COMMENT '等级名称',config VARCHAR(255) NOT NULL DEFAULT '' COMMENT '满足条件') DEFAULT CHARSET=utf8;12.请完成电子商务网站用户订阅、用户收藏的数据表设计。

# 订阅CREATE TABLE sh_user_subscribe (id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '订阅id',email VARCHAR(60) COMMENT '邮箱地址',status INT COMMENT '是否确认，0未确认，1已确认',code VARCHAR(10) COMMENT '邮箱确认的验证码',add_time INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '开始订阅时间') DEFAULT CHARSET=utf8;# 收藏CREATE TABLE sh_user_favorite (id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '收藏id',user_id INT UNSIGNED NOT NULL COMMENT '用户id',goods_id INT UNSIGNED NOT NULL COMMENT '商品id',add_time INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '收藏时间') DEFAULT CHARSET=utf8;2。

一．选择题
1.下列选项中属于创建数据库的语句是（A）
A.CREATE DATABASE
B. ALTER DATABASE
C. DROP DATABASE
D. 以上都不是
2.在创建数据库时，每个数据库都对应放在一个与数据库同名的（B）中
A.文件
B.文件夹
C.路径
D.以上都不是
3.显示当前所有的数据库的命令是（A）。

A.SHOW DATABASES;
B. SHOW DATABASE;
C. LIST DATABASES;
D. LIST DATABASE;
4.在MySQL
5.5以上系统中，默认的存储引擎是（C）。

A.MyISAM
B.MEMORY
C.InnoDB
D.ARCHIVE
5.SQL 系统中，表结构文件的扩展名是（A）。

A..frm
B. .myd
C. myi
D.mdf
6.MySQL 使用（D）文件中的配置参数。

A.my-larger.ini
B. my-small.ini
C. My-huge.ini
D.my.ini 二．简述题
（1）简述数据库定义以及数据库的作用。

（2）简述MySQL数据库的组成。

（3）简述创建数据库的方法。

习题1．选择题（1）设A、B两个数据表的记录数分别为3和4，对两个表执行交叉联接查询，查询结果中最多可获得（C ）条记录。

A．3 B. 4 C. 12 D. 81（2）如果查询的SELECT子句为SELECT A, B, C * D，则不能使用的GROUP B子句是（ A ）。

A．GROUP BY AB．GROUP BY A,BC．GROUP BY A,B,C*DD．GROUP BY A,B,C,D（3）关于查询语句中ORDER BY子句使用正确的是（ C ）。

A．如果未指定排序字段，则默认按递增排序B．数据表的字段都可用于排序C．如果在SELECT子句中使用了DISTINCT关键字，则排序字段必须出现在查询结果中D．联合查询不允许使用ORDER BY子句（4）在查询设计器中，不能与其他窗格保持同步的是（D ）。

A．关系图窗格 B. 网格窗格C．SQL窗格 D. 结果窗格（5）下列函数中，返回值数据类型为int的是（B）。

A．LEFT B. LENC．LTRIM D. SUNSTRING2．填空题(1) 在启动查询分析器时，在登录对话框中可使用（Local）作为本地服务器名称。

(2) 查询分析器窗口主要由对象浏览器和（查询）窗口组成。

(3) 从Windows“开始”菜单启动查询分析器后，默认数据库为（master）。

(4) 以表格方式显示的查询结果保存为（导出）文件，其文件扩展名为（csv）；以文本方式显示的查询结果保存为（报表）文件，其文件扩展名为（rpt）。

(5) 可使用（PRINT）或（SELECT）语句来显示函数结果。

(6) 在查询语句中，应在（SELECT）子句中指定输出字段。

(7) 如果要使用SELECT语句返回指定条数的记录，则应使用（TOP）关键字来限定输出字段。

(8) 联合查询指使用（UNION）运算将多个（查询结果）合并到一起。

(9) 当一个子SELECT的结果作为查询的条件，即在一个SELECT语句的WHERE子句中出现另一个SELECT语句，这种查询称为（嵌套）查询。

mysql数据库武洪萍版第四章习题与答案一．选择题1.下面哪种数字数据类型不可以存储数据256？（D）A.bigintB.intC.SmallintD.tinyint2.下面是有关主键和外键之间的关系描述，正确的是（AC）A.一个表最多只能有一个主键约束，多个外键约束。

B.一个表中最多只有一个外键约束，一个主键约束。

C.在定义主键外键约束时，应该首先定义主键约束，然后定义外键约束。

D.在定义主键外键约束时，应该首先定义主键约束，然后定义主键约束。

3.下面关于数据库中表的行和列的叙述正确的是（D）A.表中的行是有序的，列是无序的B.表中的列是有序的，行是无序的C.表中的行和列都是有序的D.表中的行和列都是无序的4.SQL语言的数据操作语句包括SELECT、INSERT、UPDATE、DELETE 等。

其中最重要的，也是使用最频繁的语句是（A）A.SELECTB.INSERTC.UPDATED.DELETE5.在下列SQL语句中，修改表结构的语句是（A）。

A.ALTERB.CREATEC.UPDATED.INSERT6.设有关系R（A,B,C)和S（C,D），与关系代数表达式πA,B,D(σR.C=S.C(R∞S)等价的SQL语句是（B）。

A.SELECT某FROMR,SWHERER.C=S.CB.SELECTA,B,DFROMR,SWHERER.C=.SELECTA,B,D FROMR,SWHERER=SD.SELECTA,BFROMRWHERE(SELECTDFROMSWHERER.C=S.C)7.设关系R（A,B,C)与SQL语句“SELECTDISTINSTAFROMRWHEREB=17”等价的关系代数表达式是（A）A.πA(σB=17(R))B.σB=17(πA(R))C.σB=17(πA.C(R))D.πA.C(σB=17(R))下面第（8）-（12）题，基于“学生-选课-课程”数据库中的3个关系。

SolutionsChapter 4 4.1.14.1.2a)b)c)In c we assume that a phone and address can only belong to a single customer (1-m relationship represented by arrow into customer).d)In d we assume that an address can only belong to one customer and a phone can exist at only one address.If the multiplicity of above relationships were m-to-n, the entity set becomes weak and the key ssNo of customers will be needed as part of the composite key of the entity set.In c&d, we convert attributes phones and addresses to entity sets. Since entity sets often become relations in relational design,we must consider more efficient alternatives.Instead of querying multiple tables where key values are duplicated, we can also modify attributes:(i) Phones attribute can be converted into HomePhone, OfficePhone and CellPhone.(ii) A multivalued attribute such as alias can be kept as an attribute where a single column can be used in relational design i.e. concatenate all values. SQL allows a query "like '%Junius%'" to search the multiple values in a column alias.4.1.34.1.4a)c)The relationship "played" between Teams and Players is similar to relationship "plays" between Teams and Players.4.1.54.1.6 The information about children can be ascertained from motherOf and fatherOf relationships. Attribute ssNo is required since names are not unique.4.1.74.1.8a)(b)4.1.9AssumptionsA Professor only works in at most one department.A course has at most one TA.A course is only taught by one professor and offered by one department.Students and professors have been assigned unique email ids.A course is uniquely identified by the course no, section no, and semester (e.g. cs157-3 spring09).4.1.10Given that for each movie, a unique studio exists that produces the movie. Each star is contracted to at most one studio.But stars could be unemployed at a given time. Thus the four-way relationship in fig 4.6 can be easily into converted equivalent relationships.4.2.1Redundancy: The owner address is repeated in AccSets and Addresses entity sets. Simplicity: AccSets does not serve any useful purpose and the design can be more simply represented by creating many-to-many relationship between Customers and Accounts.Right kind of element: The entity set Addresses has a single attribute address. A customer cannot have more than one address.Hence address should be an attribute of entity set Customers.Faithfulness: Customers cannot be uniquely identified by their names. In real world Customers would have a unique attribute such as ssNo or customerNo4.2.2Studios and Presidents can be combined into one entity set Studios with Presidents becoming an attribute of Studios under following circumstances:1. The Presidents entity set only contains a simple attribute viz. presidentName. Additional attributes specific to Presidents might justify making Presidents into an entity set.4.2.34.2.4 The entity sets should have single attribute.a) Stars: starNameb) Movies: movieNamec) Studios: studioName. However there exists a many-to-many relationship between Studios and Contracts. Hence, in addition, we need more information about studios involved. If a contract always involves two studios, two attributes such as producingStudio and starStudio can replace theStudios entity set. If a contact can be associated with at most five studios, it may be possible to replace the Studios entity set by five attributes viz. studio1, studio2, studio3, studio4, and studio5. Alternately, a composite attribute containing concatenation of all studio names in a contact can be considered. A separator character such as "$" can be used. SQL allows searching of such an attribute using query like '%keyword%'4.2.5From Augmentation rule of Functional Dependency,givenB -> M (B=Baby, M=Mother)thenBND -> M (N=Nurse, D=Doctor)Hence we can just put an arrow entering mother.a) Put an arrow entering entity set Mothers for the simplest solution (As in fig. 4.4, where a multi-way relationship was allowed, even though Movies alone could identify the Studio). However, we can display more accurate information with below figure.c)Again from Augmentation rule of Functional Dependency,givenBM -> DthenBMN -> DThus we can just add an arrow entering Doctors to fig 4.15. Below figure represents more accurate information however.4.2.6a)b) Transitivity and Augmentation rules of Functional Dependency allow arrow entering Mothers from Births. However, a new relationship in below figure represents more accurate information.c)Design flaws in abc above 1. As suggested above, using Transitivity and Augmentation rules of Functional Dependency, much simpler design is possible.4.2.7In below figure there exists a many-to-one relationship between Babies and Births and another many-to-one relationship between Births and Mothers. From transitivity of relationships, thereis a many-to-one relationship between Babies and Mothers. Hence a baby has a unique mother while a birth can allow more than one baby.4.3.1a)b)A captain cannot exist without a team. However a player can (free agent). A recently formed (or defunct) team can exist without players or colors.c)Children can exist without mother and father (unknown).4.3.2a)The keys of both E1 and E2 are required for uniquely identifying tuples in Rb)The key of E1c)The key of E2d)The key of either E1 or E24.3.3Special Case: All entity sets have arrows going into them i.e. all relationships are 1-to-1Any KiOtherwise: Combination of all Ki's where there does not exist an arrow going from R to Ei.4.4.1No, grade is not part of the key for enrollments. The keys of Students and Courses become keys of the weak entity set Enrollments.4.4.2It is possible to make assignment number a weak key of Enrollments but this is not good design (redundancy since multiple assignments correspond to a course). A new entity set Assignment is created and it is also a weak entity set. Hence the key attributes of Assignment will come from the strong entity sets to which Enrollments is connected i.e. studentID, dept, and CourseNo.4.4.3a)b)4.4.4a)b)Customers(SSNo,name,addr,phone)Flights(number,day,aircraft)Bookings(custSSNo,flightNo,flightDay,row,seat)Relations for toCust and toFlt relationships are not required since the weak entity set Bookings already contains the keys of Customers and Flights.4.5.2(a)Schema is changed. Since toCust is no longer an identifying relationship, SSNo is no longer a part of Bookings relation.Bookings(flightNo,flightDay,row,seat)ToCust(custSSNO,flightNo,flightDay,row,seat)The above relations are merged intoBookings(flightNo,flightDay,row,seat,custSSNo)However custSSNo is no longer a key of Bookings relation. It becomes a foreign key instead.4.5.3Ships(name, yearLaunched)SisterOf(name, sisterName)4.5.4(a)Stars(name,addr)Studios(name,addr)Movies(title,year,length,genre)Contracts(starName,movieTitle,movieYear,studioName,salary)Depending on other relationships not shown in ER diagram, studioName may not be required as a key of Contracts (or not even required as an attribute of Contracts).(b)Students(studentID)Courses(dept,courseNo)Enrollments(studentID,dept,courseNo,grade)(c)Departments(name)Courses(deptName,number)Teams(leagueName,teamName)Players(leagueName,teamName,playerName)4.6.1The weak relation Courses has the key from Depts along with number. Hence there is no relation for GivenBy relationship.(a)Depts(name, chair)Courses(number, deptName, room)LabCourses(number, deptName, allocation)(b) LabCourses has all the attributes of Courses.Depts(name, chair)Courses(number, deptName, room)LabCourses(number, deptName, room, allocation)(c) Courses and LabCourses are combined into one relation.Depts(name, chair)Courses(number, deptName, room, allocation)4.6.2(a)Person(name,address)ChildOf(personName,personAddress,childName,childAddress)Child(name,address,fatherName,fatherAddress,motherName,motherAddresss)Father(name,address,wifeName,wifeAddresss)Mother(name,address)Since FatherOf and MotherOf are many-one relationships from Child, there is no need for a separate relation for them. Similarly the one-one relationship Married can be included in Father (or Mother). ChildOf is a many-many relationship and needs a separate relation.However the ChildOf relation is not required since the relationship can be deduced from FatherOf and MotherOf relationships contained in Child relation.(b)A person cannot be both Mother and Father.Person(name,address)PersonChild(name,address)PersonChildFather(name,address)PersonChildMother(name,address)PersonFather(name,address)PersonMother(name,address)ChildOf(personName,personAddress,childName,childAddress)FatherOf(childName,childAddress,fatherName,fatherAddress)MotherOf(childName,childAddress,motherName,motherAddress)Married(husbandName,husbandAddress,wifeName,wifeAddress)The many-many ChildOf relationship again requires a relation.An entity belongs to one and only one class when using object-oriented approach. Hence, the many-one relations MotherOf and FatherOf could be added as attributes toPersonChild,PersonChildFather, and PersonChildMother relations.Similarly the Married relation can be added as attributes to PersonChildMother and PersonMother (or the corresponding father relations).(c) For the Person relation at least one of husband and wife attributes will be null.Person(personName,personAddress,fatherName,fatherAddress,motherName,motherAddresss,wifeName,wife Addresss,husbandName,husbandAddress)ChildOf(personName,personAddress,childName,childAddress)4.6.3(a)People(name,fatherName,motherName)Males(name)Females(name)Fathers(name)Mothers(name)ChildOf(personName,childName)People(name)PeopleMale(name)PeopleMaleFathers(name)PeopleFemale(name)PeopleFemaleMothers(name)ChildOf(personName,childName)FatherOf(childName,fatherName)MotherOf(childName,motherName)People cannot belong to both male and female branch of the ER diagram.Moreover since an entity belongs to one and only one class when using object-oriented approach, no entity belongs to People relation.Again we could replace MotherOf and FatherOf relations by adding as attributes toPeopleMale,PeopleMaleFathers,PeopleFemale, and PeopleFemaleMothers relations.(c)People(name,fatherName,motherName)ChildOf(personName,childName)4.6.4(a)Each entity set results in one relation. Thus both the minimum and maximum number of relationsis e.The root relation has a attributes including k keys. Thus the minimum number of attributes is a. All other relations include the k keys from root along with their a attributes. Thus the maximum number of attributes is a+k.The relation for root will have a attributes. The relation representing the whole tree will have e*a attributes.The number of relations will depend on the shape of the tree. A tree of e entities where onlyone child exists(say left child only) would have the minimum number of relations. Thus below figure will only contain 4 subtrees that contain root E1,E1E2,E1E2E3, and E1E2E3E4. With eentity sets, minimum e relations are possible.The maximum number of subtrees result when all the entities(except root) are at depth 1. Thus below figure will contain 8 subtrees that contain rootE1,E1E2,E1E3,E1E4,E1E2E3,E1E3E4,E1E2E4,and E1E2E3E4. With e entity sets, maximum 2^(e-1)relations are possible.(c)The nulls method always results in one relation and contains attributes from all e entities i.e. e*a attributes.Summarizing for a,b, and c above;#Components #RelationsMin Max Min MaxMethodstraight-E/R a a e eobject-oriented a e*a e 2^(e-1)nulls e*a e*a 1 14.7.14.7.2a)b)c)d)4.7.34.7.44.7.5Males and Females subclasses are complete. Mothers and Fathers are partial. All subclasses are disjoint.4.7.64.7.74.7.8We convert the ternary relationship Contracts into three binary relationships between a new entity set Contracts and existing entity sets.4.7.9a)c)4.7.10A self-association ParentOf for entity set people has multiplicity 0..2 at parent role end.In a Library database, if a patron can loan at most 12 books, them multiplicity is 0..12.For a FullTimeStudents entity set, a relationship of multiplicity 5..* must exist with Courses(A student must take at least5 courses to be classified FullTime.4.8.1Customers(SSNo,name,addr,phone)Flights(number,day,aircraft)Bookings(row,seat,custSSNo,FlightNumber,FlightDay)Customers("SSNo",name,addr,phone)Flights("number","day",aircraft)Bookings(row,seat,"custSSNo","FlightNumber","FlightDay")4.8.2a)Movies(title,year,length,genre)Studios(name,address)Presidents(cert#,name,address)Owns(movieTitle,movieYear,studioName)Runs(studioName,presCert#)Movies("title","year",length,genre)Studios("name",address)Presidents("cert#",name,address)Owns("movieTitle","movieYear",studioName)Runs("studioName",presCert#)b)Since the subclasses are disjoint, Object Oriented Approach is used. The hierarchy is not complete. Hence four relations are required Movies(title,year,length,genre)MurderMysteries(title,year,length,genre,weapon)Cartoons(title,year,length,genre)Cartoon-MurderMysteries(title,year,length,genre,weapon)Movies("title","year",length,genre)MurderMysteries("title","year",length,genre,weapon)Cartoons("title","year",length,genre)Cartoon-MurderMysteries("title","year",length,genre,weapon)c)Customers(ssNo,name,phone,address)Accounts(number,balance,type)Owns(custSSNo,accountNumber)Customers("ssNo",name,phone,address)Accounts("number",balance,type)Owns("custSSNo","accountNumber")d)Teams(name,captainName)Players(name,teamName)Fans(name,favoriteColor)Colors(colorname)For Displays association,TeamColors(teamName,colorname)RootsFor(fanName,teamName)Admires(fanName,playerName)Teams("name",captainName)Players("name",teamName)Fans("name",favoriteColor)Colors("colorname")For Displays association,TeamColors("teamName","colorname")RootsFor("fanName","teamName")Admires("fanName","playerName")e)People(ssNo,name,fatherSSNo,motherSSNo)People("ssNo",name,fatherssNo,motherssNo)f)Students(email,name)Courses(no,section,semester,professorEmail)Departments(name)Professors(email,name,worksDeptName)Takes(letterGrade,studentEmail,courseNo,courseSection,courseSemester)Students("email",name)Courses("no","section","semester",professorEmail)Departments("name")Professors("email",name,worksDeptName)Takes(letterGrade,"studentEmail","courseNo","courseSection","courseSemester")4.8.3a)Each and every object is a member of exactly one subclass at leaf level. We have nine classes at the leaf of hierarchy. Hence we need nine relations.b)All objects only belong to one subclass and its ancestors. Hence, we need not consider every possible subtree but rather the total number of nodes in tree.Hence we need thirteen relations.c)We need all possible subtrees. Hence 218 relations are required.4.9.1class Customer (key (ssNo)){attribute integer ssNo;attribute string name;attribute string addr;attribute string phone;relationship Set<Account> ownsAcctsinverse Account::ownedBy;};class Account (key (number)){attribute integer number;attribute string type;attribute real balance;relationship Set<Customer> ownedByinverse Customer::ownsAccts;};4.9.2a)Modify class Account to contain relationship Customer ownedBy (no Set)b)Also remove set in relationship ownsAccts of class Customer.c)ODL allows a collection of primitive types as well as structures. To class Customer add following attributes in place of simple attributes addr and phone:Set<string phone>Set<Struct addr{string street,string city,string state}>d)ODL allows structures and collections recursively.Set<Struct addr{string street,string city,string state},Set<string phone>>4.9.3Collections are allowed in ODL. Hence, Colors Set can become an attribute of Teams. class Colors(key(colorname)){attribute string colorname;relationship Set<Fans> FavoredByinverse Fans::Favors;relationship set<Teams> DisplayedByinverse Teams::Displays;};class Teams(key(name)){attribute string name;relationship set<Colors> Displaysinverse Colors::DisplayedBy;relationship set<Players> PlayedByinverse Players::Plays;relationship PLayers CaptainedByinverse Platyers::Captains;relationship set<Fans> RootedByinverse Fans::Roots;};class Players(key(name)){attribute string name;relationship Set<Teams> Playsinverse Teams::PlayedBy;relationship Teams Captainsinverse Teams::CaptainedBy;relationship Set<Fans> AdmiredByinverse Fans::Admires;};class Fans(key(name)){attribute string name;relationship Colors Favorsinverse Colors::FavoredBy;relationship Set<Teams> RootedByinverse Teams::Roots;relationship Set<Players> Admiresinverse Players::AdmiredBy;};4.9.4class Person {attribute string name;relationship Person motherOfinverse Person::childrenOfFemale;relationship Person fatherOfinverse Person::childrenOfMale;relationship Set<Person> childreninverse Person::parentsOf;relationship Set<Person> childrenOfFemaleinverse Person::motherOf;relationship Set<Person> childrenOfMaleinverse Person::fatherOf;relationship Set<Person> parentsOfinverse Person::children;};4.9.5The struct education{string degree,string school,string date} cannot have duplication. Hence use of Sets does not make any different as compared to bags, lists, or arrays. Lists will allow faster access/queries due to the already sorted nature.4.9.6a)class Departments(key (name)) {attribute string name;relationship Courses offersinverse Courses::offeredBy;};class Courses(key (number,offeredBy)) {attribute string number;relationship Departments offeredByinverse Departments::offers;};b)class Leagues (key (name)) {attribute name;relationship Teams containsinverse Teams::belongs;};class Teams(key (name,belongs)) {attribute name,relationship Leagues belongsinverse Leagues::contains;relationship Players playinverse Players::plays;};class Players (key(number,plays)) {attribute number,relationship Teams playsinverse Teams::play;};4.9.7class Students (key email) {attribute string email;attribute string name;relationship Courses isTAinverse Courses::TA;relationship Courses Takesinverse Courses::TakenBy;};class Professors (key email) {attribute string email;attribute string name;relationship Departments WorksForinverse Department::Works;relationship Courses Teachesinverse Courses::TaughtBy;};class Courses (key (no,semester,section)) {attribute string no;attribute string semester;attribute string section;relationship Students TAinverse Students::isTA;relationship Students TakenByinverse Students::Takes;relationship Professors TaughtByinverse Professors::Teaches;relationship Departments OfferedByinverse Departments::Offer;};class Departments (key name) {attribute name;relationship Courses Offerinverse Courses::OfferedBy;relationship Professors Worksinverse Professors::WorksFor;};4.9.8A relationship is its own inverse when for every attribute pair in the relationship, the inverse pair also exists. A relation with such a relationship is called symmetric in set theory. e.g. A relationship called SiblingOf in Person relation is its own inverse.4.10.1a)Customers(ssNo,name,addr,phone)Account(number,type,balance)Owns(ssNo,accountNumber)b)Accounts(number,balance,type,owningCustomerssNo)Customers(ssNo,name)Addresses(ownerssNo,street,state,city)Phones(ownerssNo,street,state,city,phonearea,phoneno)We can remove Addresses relation since its attributes are a subset of relation Phones.Fans(name,colors)RootedBy(fan_name,teamname)Admires(fan_name,playername)Players(name,teamname,is_captain)Teams(name)--remove subset of teamcolorTeamcolors(name,colorname)Colors(colorname)d)class Person {attribute string name;relationship Person motherOfinverse Person::childrenOfFemale;relationship Person fatherOfinverse Person::childrenOfMale;relationship Set<Person> childreninverse Person::parentsOf;relationship Set<Person> childrenOfFemaleinverse Person::motherOf;relationship Set<Person> childrenOfMaleinverse Person::fatherOf;relationship Set<Person> parentsOfinverse Person::children;};Person(name,mothername,fathername)The children relationship is many-many but the information can be deduced from Person relation. Hence below relation is redundant.Parent-Child(parent, child)4.10.2First consider each struct as if it were an atomic value i.e. key and value association pairs can be treated as two attributes. After applying normalization,the attributes can be replaced by the fields of the structs.4.10.3(a)Struct Card { string rank, string suit };(b)class Hand {attribute Set theHand;};(c)Hands(handId, rank, suit)Each tuple corresponds to one card of a hand. HandId is required key to identify a hand.(d) Hand contains an array of 5 elementsclass PokerHand{attribute Array Hand(Card card1,Card card2,Card card3,Card card4,Card card5)} PokerHandS(handId,rank1,suit1,,rank2,suit2,rank3,suit3,rank4,suit4,rank5,suit5)(e)class Deal {attribute Set <Struct PlayerHand { string Player, Hand theHand } > theDeal;}(f) PokerDeal consist of a player and array of five card deal.class PokerDeal{string Player,attribute Array Hand(Card card1,Card card2,Card card3,Cardcard4,Card card5)}(g) Above can similarly be represented by key player and a value consisting of five element array.(h)dealID is a key for Deals. Thus the relations for classes Deals and Hands are:Deals(dealID, player, handID)Hands(handID, rank, suit)A simpler relation Deals below can also represents the classes:Deals(dealID, player, rank, suit)(i)The relation Deals(dealID,card) cannot identify the hand to which a card belongs. Also two attributes are required for a card;its rank and suit.Deals(dealID, handID, rank, suit)4.10.4(a)C(a, f, g)(b)C(a, f, g, count)(c)C(a, f, g, position)(d)C(a, f, g, i, j)。

MySQL练习题4参考答案1.表结构如下:#课程表CREATE TABLE `course` (`c_id` int(11) NOT NULL,`c_name` varchar(50) DEFAULT NULL,`t_id` int(11) DEFAULT NULL,PRIMARY KEY (`c_id`),KEY `t_id` (`t_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `course` VALUES ('1', 'python', '1');INSERT INTO `course` VALUES ('2', 'java', '2');INSERT INTO `course` VALUES ('3', 'linux', '3');INSERT INTO `course` VALUES ('4', 'web', '2');#成绩表CREATE TABLE `score` (`id` int(11) NOT NULL AUTO_INCREMENT,`s_id` int(11) DEFAULT NULL,`c_id` int(11) DEFAULT NULL,`num` double DEFAULT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;INSERT INTO `score` VALUES ('1', '1', '1', '79');INSERT INTO `score` VALUES ('2', '1', '2', '78');INSERT INTO `score` VALUES ('3', '1', '3', '35');INSERT INTO `score` VALUES ('4', '2', '2', '32');INSERT INTO `score` VALUES ('5', '3', '1', '66');INSERT INTO `score` VALUES ('6', '4', '2', '77');INSERT INTO `score` VALUES ('7', '4', '1', '68');INSERT INTO `score` VALUES ('8', '5', '1', '66');INSERT INTO `score` VALUES ('9', '2', '1', '69');INSERT INTO `score` VALUES ('10', '4', '4', '75');INSERT INTO `score` VALUES ('11', '5', '4', '66.7');#学⽣表CREATE TABLE `student` (`s_id` varchar(20) NOT NULL,`s_name` varchar(50) DEFAULT NULL,`s_age` int(10) DEFAULT NULL,`s_sex` char(1) DEFAULT NULL,PRIMARY KEY (`s_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `student` VALUES ('1', '鲁班', '12', '男');INSERT INTO `student` VALUES ('2', '貂蝉', '20', '⼥');INSERT INTO `student` VALUES ('3', '刘备', '35', '男');INSERT INTO `student` VALUES ('4', '关⽻', '34', '男');INSERT INTO `student` VALUES ('5', '张飞', '33', '⼥');#⽼师表CREATE TABLE `teacher` (`t_id` int(10) NOT NULL,`t_name` varchar(50) DEFAULT NULL,PRIMARY KEY (`t_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `teacher` VALUES ('1', '⼤王');INSERT INTO `teacher` VALUES ('2', 'alex');INSERT INTO `teacher` VALUES ('3', 'egon');INSERT INTO `teacher` VALUES ('4', 'peiqi');数据脚本数据脚本2.查询:1. 查询学习课程"python"⽐课程 "java" 成绩⾼的学⽣的学号;#先查询"python"课程和"java"课程的学⽣成绩,临时表#让两个临时表进⾏⽐较-- select * from course c,score s where c.c_id = s.c_id and c.c_name='python';-- select * from course c,score s where c.c_id = s.c_id and c.c_name='java';select A.s_id from(select s.s_id,s.num from course c,score s where c.c_id = s.c_id and c.c_name='python') AS A ,(select s.s_id,s.num from course c,score s where c.c_id = s.c_id and c.c_name='java') AS Bwhere A.s_id = B.s_id and A.num > B.num;2. 查询平均成绩⼤于65分的同学的姓名和平均成绩(保留两位⼩数);　select round(avg(num),2) as num,student.s_name from score sLEFT JOIN student ON s.s_id = student.s_id group by s.s_id having num > 65;3. 查询所有同学的姓名、选课数、总成绩；#先来分析需要哪些表:学⽣表/成绩表#然后进⾏多表查询即可select s_name,count(*) '选课数',sum(num)as '总成绩' from student st,score s where st.s_id = s.s_id GROUP BY s.s_id;4. 查询所有的课程的名称以及对应的任课⽼师姓名;select c_name,t_name from course,teacher where course.t_id = teacher.t_id;5. 查询没学过“alex”⽼师课的同学的姓名;#先看看alex教什么课程#看看谁学了alex的课程#最后把学了的⼈过滤掉就是没学过的学⽣-- select c_id from teacher t, course c where t.t_id = c.t_id and t.t_name ='alex';-- select s_id from score where c_id in(2,4);select s_name from studentwhere s_id not in(select s_id from score where c_id in(2,4));6. 查询学过'python'并且也学过编号'java'课程的同学的姓名;-- select * from score where score.c_id='1' and score.c_id='2'#查询python和java课程号-- select c_id from course where course.c_name in('python','java');SELECT st.s_name from score s ,student stwhere s.s_id = st.s_id AND s.c_id in(1,2) GROUP BY s.s_id HAVING COUNT(*) = 2;7. 查询学过“alex”⽼师所教的全部课程的同学的姓名；#先知道alex⽼师教什么课程#然后来看看学了alex课程的学⽣有哪些⼈#按学⽣分组,看看谁学的课程数 = alex⽼师教授的课程数-- select c_id from teacher t, course c where t.t_id = c.t_id and t.t_name ='alex';select student.s_name from score,student where score.s_id =student.s_id and score.c_id in(select c_id from teacher t, course c where t.t_id = c.t_id and t.t_name ='alex') GROUP BY score.s_idHAVING count(*) = (select count(*) from teacher t, course c where t.t_id = c.t_id and t.t_name ='alex');8. 查询挂科超过两门(包括两门)的学⽣姓名;SELECT student.s_name from score,studentwhere score.s_id = student.s_id and score.num <60 GROUP BY student.s_id HAVING count(*)>=2;9. 查询有课程成绩⼩于60分的同学的姓名;SELECT DISTINCT student.s_name from score,studentwhere score.s_id = student.s_id and score.num <60;10. 查询选修了全部课程的学⽣姓名;-- select count(*) from course;select student.s_name from score,studentwhere score.s_id = student.s_id GROUP BY score.s_id HAVING count(*) = (select count(*) from course) 11. 查询⾄少有⼀门课程与“貂蝉”同学所学课程相同的同学姓名；-- SELECT c_id from score,student where score.s_id =student.s_id and student.s_name='貂蝉'select student.s_name from score,student where score.s_id = student.s_id and score.c_id in(SELECT c_id from score,student where score.s_id =student.s_id and student.s_name='貂蝉') and student.s_name <> '貂蝉' GROUP BY student.s_id;12. 查询学过'貂蝉'同学全部课程的其他同学姓名；-- SELECT c_id from score,student where score.s_id =student.s_id and student.s_name='貂蝉'select student.s_name,count(*) from score,student where score.s_id = student.s_idand score.c_id in(SELECT c_id from score,student where score.s_id =student.s_id and student.s_name='貂蝉')and student.s_name <> '貂蝉' GROUP BY student.s_idHAVING count(*) = (SELECT count(*) from score,student where score.s_id =student.s_id and student.s_name='貂蝉');13. 查询和'貂蝉'同学学习的课程完全相同的,其他同学姓名；解题思路:#1. 找出与'貂蝉'学习课程数相同的学⽣s_id (你学两门,我也学两门)　#2. 再找出学过'貂蝉'课程的学⽣,剩下的⼀定是⾄少学过⼀门'貂蝉'课程的学⽣#3. 再根据学⽣ID进⾏分组,剩下学⽣数count(1) = 貂蝉学⽣所学课程数#1.找出与'貂蝉'学习课程数相同的学⽣s_id (你学两门,我也学两门)select * FROM score where score.s_id in(select s_id from score GROUP BY score.s_id HAVING count(*) = (select count(*) from student,score where student.s_id = score.s_id and student.s_name= '貂蝉'))#2.然后再找出学过'貂蝉'课程的学⽣,剩下的⼀定是⾄少学过⼀门'貂蝉'课程的学⽣select * FROM score where score.s_id in(select s_id from score GROUP BY score.s_id HAVING count(*) = (select count(*) from student,score where student.s_id = score.s_id and student.s_name= '貂蝉'))and score.c_id in(select c_id from student,score where student.s_id = score.s_id and student.s_name= '貂蝉')#3.再根据学⽣ID进⾏分组,剩下学⽣数count(1) = 貂蝉学⽣所学课程数select * FROM score where score.s_id in(select s_id from score GROUP BY score.s_id HAVING count(*) = (select count(*) from student,score where student.s_id = score.s_id and student.s_name= '貂蝉'))and score.c_id in(select c_id from student,score where student.s_id = score.s_id and student.s_name= '貂蝉')GROUP BY score.s_id HAVING count(*) =(select count(*) from student,score where student.s_id = score.s_id and student.s_name= '貂蝉') and score.s_id !=2;14. 按平均成绩倒序显⽰所有学⽣的“python”、“java”、“linux”三门的课程成绩，按如下形式显⽰：学⽣ID,python,java,linux,课程数,平均分#1.先查询单⼀学⽣的python课程分数select num from score,course where score.c_id = course.c_id AND course.c_name ='python' and score.s_id = 1;#2.将上⾯查询的结果作为列字段使⽤select s.s_id,(select num from score,course where score.c_id = course.c_id AND course.c_name ='python' and score.s_id = s.s_id ) as 'python', (select num from score,course where score.c_id = course.c_id AND course.c_name ='java' and score.s_id = s.s_id ) as 'java',(select num from score,course where score.c_id = course.c_id AND course.c_name ='linux' and score.s_id = s.s_id ) as 'linux',count(c_id)as '课程数',avg(num) as '平均分'from score s GROUP BY s.s_id;15. 统计各科各分数段⼈数.显⽰格式:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]select score.c_id,course.c_name, sum(CASE WHEN num BETWEEN 85 and 100 THEN 1 ELSE 0 END) as '[100-85]', sum(CASE WHEN num BETWEEN 70 and 85 THEN 1 ELSE 0 END) as '[85-70]', sum(CASE WHEN num BETWEEN 60 and 70 THEN 1 ELSE 0 END) as '[70-60]', sum(CASE WHEN num < 60 THEN 1 ELSE 0 END) as '[ <60]'from score,course where score.c_id=course.c_id GROUP BY score.c_id;16. 查询每门课程被选修的次数select c_name,count(*) from course,score where course.c_id = score.c_id GROUP BY score.c_id;17. 查询出只选修了⼀门课程的学⽣的学号和姓名select student.s_id,student.s_name from student,score wherestudent.s_id = score.s_id GROUP BY score.s_id HAVING count(*)=118. 查询学⽣表中男⽣、⼥⽣各有多少⼈注意:不⽤group by 分组select sum(CASE WHEN s_sex ='男' THEN 1 ELSE 0 END) as '男⽣',sum(CASE WHEN s_sex ='⼥' THEN 1 ELSE 0 END) as '⼥⽣'FROM student19. 查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列select course.c_name,avg(num) as '平均成绩' from course,scorewhere course.c_id = score.c_id GROUP BY score.c_id ORDER BY avg(num),score.c_id desc;20. 查询课程名称为“python”，且分数低于60的学⽣姓名和分数select student.s_name,score.num from score,course,studentwhere score.c_id = course.c_id and student.s_id = score.s_id and course.c_name = 'python' and score.num < 67 。

【最新整理，下载后即可编辑】SolutionsChapter 4 4.1.14.1.2a)b)c)In c we assume that a phone and address can only belong to a single customer (1-m relationship represented by arrow into customer).d)In d we assume that an address can only belong to one customer and a phone can exist at only one address.If the multiplicity of above relationships were m-to-n, the entity set becomes weak and the key ssNo of customers will be needed as part of the composite key of the entity set.In c&d, we convert attributes phones and addresses to entity sets. Since entity sets often become relations in relational design,we must consider more efficient alternatives.Instead of querying multiple tables where key values are duplicated, we can also modify attributes:(i) Phones attribute can be converted into HomePhone, OfficePhone and CellPhone.(ii) A multivalued attribute such as alias can be kept as an attribute where a single column can be used in relational design i.e. concatenate all values. SQL allows a query "like '%Junius%'" to search the multiple values in a column alias.4.1.34.1.4a)b)c)The relationship "played" between Teams and Players is similar to relationship "plays" between Teams and Players.4.1.54.1.6 The information about children can be ascertained from motherOf and fatherOf relationships. Attribute ssNo is required since names are not unique.4.1.74.1.8a)(b)4.1.9AssumptionsA Professor only works in at most one department.A course has at most one TA.A course is only taught by one professor and offered by one department. Students and professors have been assigned unique email ids.A course is uniquely identified by the course no, section no, and semester (e.g. cs157-3 spring 09).4.1.10Given that for each movie, a unique studio exists that produces the movie. Each star is contracted to at most one studio.But stars could be unemployed at a given time. Thus the four-way relationship in fig 4.6 can be easily into converted equivalent relationships.4.2.1Redundancy: The owner address is repeated in AccSets and Addresses entity sets. Simplicity: AccSets does not serve any useful purpose and the design can be more simply represented by creating many-to-many relationship between Customers and Accounts.Right kind of element: The entity set Addresses has a single attribute address. A customer cannot have more than one address.Hence address should be an attribute of entity set Customers. Faithfulness: Customers cannot be uniquely identified by their names. In real world Customers would have a unique attribute such as ssNo or customerNo 4.2.2Studios and Presidents can be combined into one entity set Studios with Presidents becoming an attribute of Studios under following circumstances:1. The Presidents entity set only contains a simple attribute viz. presidentName. Additional attributes specific to Presidents might justify making Presidents into an entity set.4.2.34.2.4 The entity sets should have single attribute.a) Stars: starNameb) Movies: movieNamec) Studios: studioName. However there exists a many-to-many relationship between Studios and Contracts. Hence, in addition, we need more informationabout studios involved. If a contract always involves two studios, two attributes such as producingStudio and starStudio can replace theStudios entity set. If a contact can be associated with at most five studios, it may be possible to replace the Studios entity set by five attributes viz. studio1, studio2, studio3, studio4, and studio5. Alternately, a composite attribute containing concatenation of all studio names in a contact can be considered. A separator character such as "$" can be used. SQL allows searching of such an attribute using query like '%keyword%'4.2.5From Augmentation rule of Functional Dependency,givenB -> M (B=Baby, M=Mother)thenBND -> M (N=Nurse, D=Doctor)Hence we can just put an arrow entering mother.a) Put an arrow entering entity set Mothers for the simplest solution (As in fig. 4.4, where a multi-way relationship was allowed, even though Movies alone could identify the Studio). However, we can display more accurate information with below figure.b)c)Again from Augmentation rule of Functional Dependency, givenBM -> DthenBMN -> DThus we can just add an arrow entering Doctors to fig 4.15. Below figure represents more accurate information however.4.2.6a)b) Transitivity and Augmentation rules of Functional Dependency allow arrow entering Mothers from Births. However, a new relationship in below figure represents more accurate information.c)Design flaws in abc above 1. As suggested above, using Transitivity and Augmentation rules of Functional Dependency, much simpler design is possible.4.2.7In below figure there exists a many-to-one relationship between Babies and Births and another many-to-one relationship between Births and Mothers. From transitivity of relationships, there is a many-to-one relationship between Babiesand Mothers. Hence a baby has a unique mother while a birth can allow more than one baby.4.3.1a)b)A captain cannot exist without a team. However a player can (free agent). A recently formed (or defunct) team can exist without players or colors.c)Children can exist without mother and father (unknown).4.3.2a)The keys of both E1 and E2 are required for uniquely identifying tuples in R b)The key of E1c)The key of E2d)The key of either E1 or E24.3.3Special Case: All entity sets have arrows going into them i.e. all relationships are 1-to-1Any KiOtherwise: Combination of all Ki's where there does not exist an arrow going from R to Ei.4.4.1No, grade is not part of the key for enrollments. The keys of Students and Courses become keys of the weak entity set Enrollments.4.4.2It is possible to make assignment number a weak key of Enrollments but this is not good design (redundancy since multiple assignments correspond to a course).A new entity set Assignment is created and it is also a weak entity set. Hence the key attributes of Assignment will come from the strong entity sets to which Enrollments is connected i.e. studentID, dept, and CourseNo.4.4.3a)b)c)4.4.4a)b)4.5.1Customers(SSNo,name,addr,phone)Flights(number,day,aircraft)Bookings(custSSNo,flightNo,flightDay,row,seat)Relations for toCust and toFlt relationships are not required since the weak entity set Bookings already contains the keys of Customers and Flights.4.5.2(a)(b)Schema is changed. Since toCust is no longer an identifying relationship, SSNo is no longer a part of Bookings relation.Bookings(flightNo,flightDay,row,seat)ToCust(custSSNO,flightNo,flightDay,row,seat)The above relations are merged intoBookings(flightNo,flightDay,row,seat,custSSNo)However custSSNo is no longer a key of Bookings relation. It becomes a foreign key instead.4.5.3Ships(name, yearLaunched)SisterOf(name, sisterName)4.5.4(a)Stars(name,addr)Studios(name,addr)Movies(title,year,length,genre)Contracts(starName,movieTitle,movieYear,studioName,salary)Depending on other relationships not shown in ER diagram, studioName may not be required as a key of Contracts (or not even required as an attribute of Contracts).(b)Students(studentID)Courses(dept,courseNo)Enrollments(studentID,dept,courseNo,grade)(c)Departments(name)Courses(deptName,number)(d)Leagues(name)Teams(leagueName,teamName)Players(leagueName,teamName,playerName)4.6.1The weak relation Courses has the key from Depts along with number. Hence there is no relation for GivenBy relationship.(a)Depts(name, chair)Courses(number, deptName, room)LabCourses(number, deptName, allocation)(b) LabCourses has all the attributes of Courses.Depts(name, chair)Courses(number, deptName, room)LabCourses(number, deptName, room, allocation)(c) Courses and LabCourses are combined into one relation.Depts(name, chair)Courses(number, deptName, room, allocation)4.6.2(a)Person(name,address)ChildOf(personName,personAddress,childName,childAddress)Child(name,address,fatherName,fatherAddress,motherName,motherAddresss) Father(name,address,wifeName,wifeAddresss)Mother(name,address)Since FatherOf and MotherOf are many-one relationships from Child, there is no need for a separate relation for them. Similarly the one-one relationship Married can be included in Father (or Mother). ChildOf is a many-many relationship and needs a separate relation.However the ChildOf relation is not required since the relationship can be deduced from FatherOf and MotherOf relationships contained in Child relation.(b)A person cannot be both Mother and Father.Person(name,address)PersonChild(name,address)PersonChildFather(name,address)PersonChildMother(name,address)PersonFather(name,address)PersonMother(name,address)ChildOf(personName,personAddress,childName,childAddress)FatherOf(childName,childAddress,fatherName,fatherAddress)MotherOf(childName,childAddress,motherName,motherAddress)Married(husbandName,husbandAddress,wifeName,wifeAddress)The many-many ChildOf relationship again requires a relation.An entity belongs to one and only one class when using object-oriented approach. Hence, the many-one relations MotherOf and FatherOf could be added as attributes to PersonChild,PersonChildFather, and PersonChildMother relations.Similarly the Married relation can be added as attributes to PersonChildMother and PersonMother (or the corresponding father relations).(c) For the Person relation at least one of husband and wife attributes will be null. Person(personName,personAddress,fatherName,fatherAddress,motherName,mot herAddresss,wifeName,wifeAddresss,husbandName,husbandAddress) ChildOf(personName,personAddress,childName,childAddress)4.6.3(a)People(name,fatherName,motherName)Males(name)Females(name)Fathers(name)Mothers(name)ChildOf(personName,childName)(b)People(name)PeopleMale(name)PeopleMaleFathers(name)PeopleFemale(name)PeopleFemaleMothers(name)ChildOf(personName,childName)FatherOf(childName,fatherName)MotherOf(childName,motherName)People cannot belong to both male and female branch of the ER diagram. Moreover since an entity belongs to one and only one class when using object-oriented approach, no entity belongs to People relation.Again we could replace MotherOf and FatherOf relations by adding as attributes to PeopleMale,PeopleMaleFathers,PeopleFemale, and PeopleFemaleMothers relations.(c)People(name,fatherName,motherName)ChildOf(personName,childName)4.6.4(a)Each entity set results in one relation. Thus both the minimum and maximum number of relations is e.The root relation has a attributes including k keys. Thus the minimum number of attributes is a. All other relations include the k keys from root along with their a attributes. Thus the maximum number of attributes is a+k.(b)The relation for root will have a attributes. The relation representing the whole tree will have e*a attributes.The number of relations will depend on the shape of the tree. A tree of e entities where only one child exists(say left child only) would have the minimum number of relations. Thus below figure will only contain 4 subtrees that contain rootE1,E1E2,E1E2E3, and E1E2E3E4. With e entity sets, minimum e relations are possible.The maximum number of subtrees result when all the entities(except root) are at depth 1. Thus below figure will contain 8 subtrees that contain rootE1,E1E2,E1E3,E1E4,E1E2E3,E1E3E4,E1E2E4,and E1E2E3E4. With e entity sets, maximum 2^(e-1) relations are possible.The nulls method always results in one relation and contains attributes from all e entities i.e. e*a attributes.Summarizing for a,b, and c above;#Components #RelationsMin Max Min MaxMethodstraight-E/R a a e eobject-oriented a e*a e 2^(e-1)nulls e*a e*a 1 14.7.14.7.2a)b)c)d)4.7.34.7.44.7.5Males and Females subclasses are complete. Mothers and Fathers are partial. All subclasses are disjoint.4.7.6。

mysql练习题及答案（MySQL exercises and answers）MySQL query exercisesThe definition of the Sutdent tableThe field name description data type key non empty only incrementId number INT (10) is whether it isThe name of Name VARCHAR (20) whether or notSex VARCHAR (4) sex no no no no noYear of birth Birth YEAR no no no no noDepartment and VARCHAR (20) whether or notAddress home address: VARCHAR (50) no no no no noThe definition of the Score tableThe field name description data type key non empty only incrementId number INT (10) is whether it isStu_id number INT (10) whether or notC_name VARCHAR (20) no no no no noThe Grade fraction of INT (10) no no no no no1. create student and score tableCREATE TABLE (studentID INT (10) NOT NULL UNIQUE PRIMARY KEY,Name VARCHAR (20) NOT NULL,Sex VARCHAR (4),Birth YEAR,Department VARCHAR (20),Address VARCHAR (50));Create table score. The SQL code is as follows: CREATE TABLE (scoreID INT (10) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT, Stu_id INT (10) NOT NULL,C_name VARCHAR (20),Grade INT (10));2. for the student and score tables add recordTo the student table INSERT statement to insert records as follows:INSERT INTO student (VALUES 901, a 'boss',' male ',' 1985, Department of computer ',' Beijing Haidian District ');INSERT INTO student VALUES (902, Zhang 'Dick', 'male', 1986 ', Chinese', 'Beijing Changping District');INSERT INTO student VALUES (903 ', three', 'female', 1990 ', Chinese', 'Hunan Yongzhou');INSERT INTO student VALUES (904, four 'Lee', 'male', 1990 '', 'the English Department, Liaoning province Fuxin city');INSERT INTO student VALUES (905, five 'King' and 'female', 1991 '', 'the English Department, Fujian province Xiamen city');INSERT INTO student VALUES (906, six 'King', 'male', 1988 '' ', Department of computer, Hunan city of Hengyang province');To the score table INSERT statement to insert records as follows:INSERT INTO score VALUES (NULL, 901, the 'computer', 98);INSERT INTO score VALUES (NULL, 901, 'English', 80); INSERT INTO score VALUES (NULL, 902, the 'computer', 65); INSERT INTO score (VALUES NULL, 902,“中文'，88）；插入评分值（null，903，“中文'，95）；插入评分值（null，904，“计算机'，70）；插入评分值（null，904，“英语'，92）；插入评分值（null，905，“英语'，94）；插入评分值（null，906，“计算机'，90）；插入评分值（null，906，“英语'，85）；3。

习题答案第一章习题12．填空题（1）物理数据独立性（2）数据库管理系统（（DBMS）（3）现实世界、信息世界、数据世界（4）码（5）一对一（1：1）、一对多（1：n）、多对多（m:n）（6）概念数据模型 E-R模型（7）逻辑数据物理数据（8）DBMS（数据库管理系统） DBA（数据库管理员）（9）关系的参照（10）θ3．简答题(1)数据模型是对现实世界的数据特征进行的抽象，来描述数据库的结构与语义。

数据模型的三要素是：数据结构、数据操作、数据约束条件。

（2）逻辑数据独立性：当模式改变时(如增加新的关系、新的属性、改变属性的数据类型等)，由数据库管理员对各个外模式/模式映像作相应改变，可以使外模式保持不变。

因而应用程序不必修改，保证了数据与程序的逻辑独立性，简称逻辑数据独立性。

物理数据独立性：当数据库的存储结构改变了(如选用了另一种存储结构)，由数据库管理员对模式/内模式映像作相应改变，可以保证模式保持不变，因而应用程序也不必改变。

保证了数据与程序的物理独立性，简称物理数据独立性特定的应用程序是在外模式描述的数据结构上编制的，它依赖于特定的外模式，与数据库的模式和存储结构相独立。

不同的应用程序可以共用同一外模式。

数据库的两级映像保证了数据库外模式的稳定性，从而从底层保证了应用程序的稳定性，使得数据库系统具有数据与程序的独立性。

（3）数据库系统由计算机硬件、数据库、数据库管理系统(及其开发工具)、数据库应用系统、数据库用户构成。

（4）DBA的职责是对使用中的数据库进行整体维护和改进，负责数据库系统的正常运行，是数据库系统的专职管理和维护人员。

系统分析员负责应用系统的需求分析和规范说明，要和用户及DBA结合，确定系统的硬件软件配置，并参与数据库系统的概要设计。

数据库设计人员负责数据库中数据的确定、数据库各级模式的设计。

应用程序开发人员负责设计和编写应用程序的程序模块，并进行测试和安装。

（6）目前比较流行的DBMS有Visual FoxPro、Access、SQL Server、My SQL 、Oracle等。

习题参考答案第1章习题参考答案一、选择题1. C2. B3. D4. C5. D6. B7. A8. B9. D 10. B11. C 12. D 13. D 14. D 15. B 16. C 17. D 18. A 19. D 20. A21. D 22. D 23. C 24. A 25. C二、填空题1. 数据库系统阶段2. 关系3. 物理独立性4. 操作系统5. 数据库管理系统〔DBMS6. 一对多7. 独立性8. 完整性控制9. 逻辑独立性10. 关系模型11. 概念结构〔逻辑12. 树有向图二维表嵌套和递归13. 宿主语言〔或主语言14. 数据字典15. 单用户结构主从式结构分布式结构客户/服务器结构浏览器/服务器结构16. 现实世界信息世界计算机世界三、简答题1、简述数据库管理技术发展的三个阶段。

各阶段的特点是什么？答：数据库管理技术经历了人工管理阶段、文件系统阶段和数据库系统阶段。

<1、人工管理数据的特点：A、数据不保存。

B、系统没有专用的软件对数据进行管理。

C、数据不共享。

D、数据不具有独立性。

〔2、文件系统阶段的特点：A、数据以文件的形式长期保存。

B、由文件系统管理数据。

C、程序与数据之间有一定的独立性。

D、文件的形式已经多样化E、数据具有一定的共享性〔3、数据库系统管理阶段特点：A、数据结构化。

B、数据共享性高、冗余度底。

C、数据独立性高。

D、有统一的数据控制功能。

2、从程序和数据之间的关系来分析文件系统和数据库系统之间的区别和联系答：数据管理的规模日趋增大,数据量急剧增加,文件管理系统已不能适应要求,数据库管理技术为用户提供了更广泛的数据共享和更高的数据独立性,进一步减少了数据的余度,并为用户提供了方便的操作使用接口。

数据库系统对数据的管理方式与文件管理系统不同,它把所有应用程序中使用的数据汇集起来,以记录为单位存储,在数据库管理系统的监督和管理下使用,因此数据库中的数据是集成的,每个用户享用其中的一部分。

参考答案习题-第04章一、选择题1.D2.C3.AC4.C5.F6.C7.BC8.DFGHI9.B 10.ABC 11.C 12.C 13.C 14.D 15.D 16.D二、说明题1. 系统数据库用来保存有关MySQL自身的管理信息，其中：（1）information_schema：保存了mysql服务器所有数据库的信息，比如数据库的名称、数据库的表、访问权限、数据库表的数据类型、数据库索引的信息等等。

（2）mysql：MySQL的核心数据库，主要负责存储数据库的用户、权限设置、关键字等MySQL自己需要使用的控制和管理信息。

（3）performance_schema：主要用于收集数据库服务器性能参数，可用于监控服务器在一个较低级别的运行过程中的资源消耗、资源等待等情况。

（4）sys：数据来自performance_schema，把performance_schema的复杂度降低，让DBA能更好的阅读这个库里的内容，了解数据库的运行情况。

查询系统数据库的方法与查询用户创建的数据库方法相同，但某些数据需要有管理员权限才能够查看和操作。

2. 当PRIMARY KEY约束需要多列时，用自增列作为主键便于内部管理。

3. PRIMARY KEY设置主键约束，定义表的唯一主键；UNIQUE [KEY]定义唯一键约束，说明该列在表中唯一；NOT NULL设置列不允许为空；INDEX在列上创建索引，优化查询检索性能；DEFAULT设置列的默认值；CHECK定义列的完整性约束。

4. 列约束是定义在列属性中的，而表约束是定义在所有列之后的。

如果约束需要同时对多列进行那么就只能采用表约束，因为表约束面向的是表（当然就包括所有列），而列约束只能针对某一个单独的列进行约束。

但列约束直接定义声明在列的定义之后，简便、直观、易读，也是必不可少的。

三、编程题1. 在学生成绩数据库（xscj）中创建学生表（xs），执行语句：USE xscj;CREATE TABLE xs(学号char(6) NOT NULL PRIMARY KEY,姓名char(8) NOT NULL,专业名enum('计算机', '软件工程', '通信工程'),性别bit NOT NULL DEFAULT 1,出生日期date NOT NULL,年龄tinyint UNSIGNED AS(2022 - year(出生日期)) CHECK(年龄 < 30),总学分tinyint(1) DEFAULT 0,地址json,INDEX(姓名));2. 在学生成绩数据库（xscj）中创建课程表（kc），执行语句：USE xscj;CREATE TABLE kc(课程号char(3) NOT NULL PRIMARY KEY,课程名char(16) NOT NULL,开课学期tinyint(1) NOT NULL DEFAULT 1 CHECK(开课学期 >= 1 AND 开课学期 <= 8),学分tinyint(1));3. 在学生成绩数据库（xscj）中创建成绩表（cj），执行语句：USE xscj;CREATE TABLE cj(学号char(6) NOT NULL,课程号char(3) NOT NULL,成绩tinyint(1),PRIMARY KEY(学号,课程号),FOREIGN KEY(学号) REFERENCES xs(学号),FOREIGN KEY(课程号) REFERENCES kc(课程号));。

习题61、说明数据库设计的特点。

1）三分技术，七分管理，十二分基础数据2）综合性3）结构（数据）设计和行为（处理）设计相结合2、试述数据库设计的过程3、试述数据库设计过程的各个阶段设计内容。

1）需求分析阶段需求分析是对用户提出的各种要求加以分析，对各种原始数据加以综合、整理，是形成最终设计目标的首要阶段。

需求分析是整个设计过程的基础，是最困难、最耗费时间的一步。

2）概念结构设计阶段概念结构设计是对用户需求进行进一步抽象、归纳，并形成独立于DBMS和有关软、硬件的概念数据模型的设计过程。

3）逻辑结构设计阶段逻辑结构设计是将概念结构转换为某个DBMS所支持的数据模型，并对其进行优化的设计过程。

4）物理设计阶段数据库物理设计阶段，是将逻辑结构设计阶段所产生的逻辑数据模型，转换为某种计算机系统所支持的数据库物理结构的实现过程。

5）数据库实施阶段数据库实施阶段，即数据库调试、试运行阶段。

一旦数据库的物理结构形成，就可以用已选定的DBMS来定义、描述相应的数据库结构，装入数据库数据库，以生成完整的数据库，编制有关应用程序，进行联机调试并转入试运行，同时进行时间、空间等性能分析。

6）数据库运行和维护阶段数据库实施阶段结束，标志着数据库系统投入正常运行工作的开始。

在数据库系统运行过程中必须不断地对其进行评价、调整与修改。

4、需求分析中发现事实的方法有哪些？1)跟班作业。

通过亲身参加业务工作来观察和了解业务活动的情况。

2)开调查会。

通过与用户座谈来了解业务活动的情况及用户需求。

3)检查文档。

通过检查与当前系统有关的文档、表格、报告和文件等，进一步理解原系统，并有利于提供与原系统问题相关的业务信息。

4)问卷调查。

5、需求分析阶段的设计目标是什么？调查的内容是什么？需求分析阶段的目标是通过详细调查现实世界要处理的对象（组织、部门、企业等），充分了解原系统（手工系统或计算机系统）工作概况，确定企业的组织目标，明确用户的各种需求，进而确定新系统的功能，并把这些要求写成用户和数据库设计者都能够接受的文档。

mysql习题和答案MySQL习题和答案MySQL是一种开源的关系型数据库管理系统，广泛应用于各种Web应用程序和企业级应用中。

掌握MySQL的基本知识和技能对于数据库开发和管理人员来说至关重要。

本文将介绍一些常见的MySQL习题和答案，帮助读者更好地理解和掌握MySQL的使用。

一、基础知识篇1. 什么是数据库？答：数据库是一个有组织的数据集合，用于存储和管理数据。

2. 什么是关系型数据库？答：关系型数据库是一种基于关系模型的数据库，数据以表格的形式组织，表格由行和列组成。

3. 什么是SQL？答：SQL（Structured Query Language）是一种用于管理和操作关系型数据库的语言。

4. 什么是MySQL？答：MySQL是一种开源的关系型数据库管理系统，被广泛应用于各种Web应用程序和企业级应用中。

二、查询语句篇1. 如何查询表中的所有数据？答：使用SELECT语句，例如：SELECT * FROM table_name;2. 如何查询表中的特定列？答：使用SELECT语句，并指定需要查询的列，例如：SELECT column1,column2 FROM table_name;3. 如何使用WHERE子句进行条件查询？答：使用SELECT语句，并在WHERE子句中指定条件，例如：SELECT * FROM table_name WHERE condition;4. 如何对查询结果进行排序？答：使用SELECT语句，并在ORDER BY子句中指定排序的列和排序方式，例如：SELECT * FROM table_name ORDER BY column_name ASC（升序）/DESC（降序）;三、数据操作篇1. 如何插入新数据？答：使用INSERT INTO语句，例如：INSERT INTO table_name (column1,column2) VALUES (value1, value2);2. 如何更新已有数据？答：使用UPDATE语句，例如：UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;3. 如何删除数据？答：使用DELETE FROM语句，例如：DELETE FROM table_name WHERE condition;四、表操作篇1. 如何创建新表？答：使用CREATE TABLE语句，例如：CREATE TABLE table_name (column1datatype, column2 datatype);2. 如何修改已有表的结构？答：使用ALTER TABLE语句，例如：ALTER TABLE table_name ADDcolumn_name datatype;3. 如何删除已有表？答：使用DROP TABLE语句，例如：DROP TABLE table_name;五、高级技巧篇1. 如何进行表的连接查询？答：使用JOIN语句，例如：SELECT * FROM table1 JOIN table2 ONtable1.column = table2.column;2. 如何进行数据的分组统计？答：使用GROUP BY语句，例如：SELECT column, COUNT(*) FROMtable_name GROUP BY column;3. 如何进行数据的多表排序和限制查询结果数量？答：使用ORDER BY和LIMIT语句，例如：SELECT * FROM table_name ORDER BY column_name LIMIT 10;六、总结通过以上习题和答案的学习，我们可以更好地掌握MySQL的使用。