南京理工大学《数据库系统基础教程》试题和答案
《数据库系统概论》课程习题及参考答案
《数据库系统概论》课程习题及参考答案一、选择题1. 以下哪项不是数据库系统的组成部分？A. 数据库B. 数据库管理系统C. 数据字典D. 应用程序答案：C2. 数据库管理系统的核心是？A. 数据定义B. 数据操纵C. 数据控制D. 数据库语言答案：D3. 在数据库系统中，下列哪个不是数据模型的三要素？A. 数据结构B. 数据操作C. 数据约束D. 数据存储答案：D4. 以下哪个不是SQL语言的数据定义功能？A. CREATEB. DROPC. ALTERD. SELECT答案：D5. 在关系数据库中，下列哪个不是关系的基本操作？A. 插入B. 删除C. 更新D. 排序答案：D二、填空题1. 数据库系统由____、____、____和____组成。
答案：数据库、数据库管理系统、数据库管理员、应用程序2. 数据库管理系统的主要功能包括____、____、____和____。
答案：数据定义、数据操纵、数据控制、数据维护3. 数据模型分为____、____、____和____。
答案：层次模型、网状模型、关系模型、面向对象模型4. SQL语言的数据定义功能包括____、____和____。
答案：CREATE、DROP、ALTER5. 关系数据库的完整性约束包括____、____和____。
答案：实体完整性、参照完整性、用户定义的完整性三、简答题1. 简述数据库系统的特点。
答案：数据库系统的特点如下：（1）数据共享性高，冗余度低；（2）数据独立性高；（3）数据安全性好；（4）数据一致性；（5）支持事务处理。
2. 简述关系数据库的三大范式。
答案：关系数据库的三大范式如下：（1）第一范式（1NF）：每个字段都是不可分割的最小数据单位，不存在重复的组；（2）第二范式（2NF）：在1NF的基础上，表中的所有非主属性完全依赖于主键；（3）第三范式（3NF）：在2NF的基础上，表中的所有字段之间不存在传递依赖。
3. 简述SQL语言的数据操纵功能。
南京理工大学复试科目数据库系统基础教程考试大纲
第一章数据库系统的世界(概述)一、要求掌握的主要概念数据库、数据独立性、数据库管理系统、模式、子模式、内模式、数据库系统二、应理解和掌握的主要内容1）数据库的特点2）数据库与文件系统的区别3）数据库管理系统的组成及主要功能4）数据库的模式(schema)结构第二章数据库建模一、要求掌握的主要概念数据模型、实体/联系模型、联系的多重性、联系中的角色、子类、键约束、单值约束、参照完整性约束、弱实体集二、应理解和掌握的主要内容1）数据模型的分类2）实体/联系图的基本表示3）联系的多重性的表示4）联系中的角色表示5）多向联系转换为二元联系6）子类的表示7）各种约束的表示8）弱实体集的表示第三章关系数据模型一、要求掌握的主要概念关系、属性、元组、关系模式、关系的实例、函数依赖、键、主键、候选键、超键、函数依赖规则、分解合并规则、平凡依赖、平凡依赖规则、属性的闭包、传递规则、函数依赖的闭包、范式、BC 范式、分解为BC范式、函数依赖的投影、最小函数依赖集、第三范式、第二范式、第一范式、多值依赖、多值依赖规则、第四范式二、应理解和掌握的主要内容1）关系的等价表示2）实体集到关系的转换3）E/R联系到关系的转换4）弱实体集到关系的转换5）子类结构到关系的设计6）如何判断关系中的函数依赖7）键与超键的关系8）属性闭包的计算9）属性闭包的作用10）BC范式的判定11）分解为BC范式12）函数依赖的投影算法13）其它范式的判定14）范式间的联系第四章关系模型中的运算一、要求掌握的主要概念关系代数、基本运算与导出运算、包及包的运算、关系的约束二、应理解和掌握的主要内容1）关系的集合运算（并、交、差）2）投影运算3）选择运算4）笛卡尔积5）自然连接6）θ连接7）改名运算8）用复合运算表示各类复杂查询9）用关系代数表示约束10）包的运算第五章数据库语言SQL一、要求掌握的主要概念视图、空值、外部连接二、应理解和掌握的主要内容1）SQL简单查询2）多关系查询3）集合运算4）子查询5）聚合运算6）数据更新7）用SQL定义关系模式8）视图的定义及查询9）关于视图的更新10）连接表达式11）外部连接第六章SQL中的约束和触发程序一、要求掌握的主要概念键码约束、保持参照完整性、非空约束、基于属性的Check约束、基于元组的Check约束、断言、触发器二、应理解和掌握的主要内容1）说明键码2）说明外键3）说明其它约束第七章SQL系统概况一、要求掌握的主要概念宿主语言、游标、共享变量、存储过程、事务、事务隔离级别、授权二、应理解和掌握的主要内容1）在宿主语言中如何使用共享变量2）游标的定义和应用3）事务的特性4）并发事务可能引起哪些问题5）有哪些事务隔离级别(isolation level)来避免冲突6）数据库安全性的主要问题是什么7）如何为用户授权8）如何收回授予用户的权利附录一数据库系统考试题型本考试共分六种题型，即单项选择题、填空题、名词解释、简答题、设计题、综合题，共120分钟。
数据库基础课后习题及答案
数据库基础课后习题及答案数据库基础课后习题及答案数据库是计算机科学中非常重要的一个概念，它用于存储和管理大量的数据。
在数据库基础课程中，学生通常需要完成一些习题来巩固所学的知识。
本文将介绍一些常见的数据库基础课后习题，并提供相应的答案。
一、选择题1. 数据库是指什么？A. 存储和管理数据的软件系统B. 存储和管理硬件设备的软件系统C. 存储和管理网络的软件系统D. 存储和管理操作系统的软件系统答案：A2. 数据库管理系统（DBMS）的主要功能是什么？A. 存储和管理数据B. 分析和处理数据C. 网络和通信D. 操作系统管理答案：A3. 下列哪个不属于数据库的特点？A. 数据共享B. 数据冗余C. 数据独立性D. 数据一致性答案：B4. 数据库中的数据是以什么形式存储的？A. 文件B. 表格C. 文本D. 图像答案：B5. 数据库中的主键是什么？A. 唯一标识一个记录的属性B. 存储在数据库中的所有数据C. 数据库中的表格D. 数据库中的索引答案：A二、填空题1. 数据库中的关系是指什么？关系是指数据之间的联系和关联。
2. 数据库中的SQL是什么意思？SQL是结构化查询语言（Structured Query Language）的缩写。
3. 数据库中的DDL是什么意思？DDL是数据定义语言（Data Definition Language）的缩写。
4. 数据库中的DML是什么意思？DML是数据操作语言（Data Manipulation Language）的缩写。
5. 数据库中的索引有什么作用？索引可以提高数据库的查询效率，加快数据检索的速度。
三、简答题1. 数据库的三级模式是什么？数据库的三级模式包括外模式、概念模式和内模式。
外模式是用户对数据库的直接接口，概念模式是数据库的全局逻辑结构，内模式是数据库在物理存储上的表示。
2. 数据库的ACID是什么意思？ACID是数据库事务的四个特性，包括原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）。
数据库系统原理练习题库(附参考答案)
数据库系统原理练习题库（附参考答案）一、单选题（共100题，每题1分，共100分）1.属于数据库结构设计阶段的是A、程序设计B、功能设计C、事务设计D、逻辑结构设计正确答案：D2.人工管理阶段,计算机主要应用于A、数据集成B、科学计算C、过程控制D、故障恢复正确答案：B3.下列关于数据控制语言的说法中,正确的是A、REVOKE语句用于授予权限B、GRANT语句用于收回权限C、数据控制语言主要用于数据执行流程管理D、数据控制语言包括的主要SQL语句是GRANT和REVOKE正确答案：D4.下列属于关联分析算法的是A、AprioriB、GMMC、RedisD、HBase正确答案：A5.同一数据被反复存储的情况是A、删除异常B、插入异常C、更新异常D、数据冗余正确答案：D6.产生数据不一致的主要原因是并发操作破坏了事务的A、持续性B、一致性C、原子性D、隔离性正确答案：D7.DBMS提供【】来严格地定义模式。
A、模式描述语言B、子模式描述语言C、内模式描述语言D、程序设计语言正确答案：A8.关系数据库以【】作为数据的逻辑模型。
A、二维表B、关系C、关系模型D、数据库正确答案：C9.关于调用存储过程的说法,错误的是A、可以从交互式界面调用B、可以使用CALL语句来调用存储过程C、可以由嵌入式SQL调用D、不是所有的SQL接口都能调用存储过程正确答案：D10.可唯一标识实体的属性集称为A、键B、实体型C、域D、属性正确答案：A11.目的是为可实际运行的应用程序设计提供依据与指导,并作为设计评价的基础的是A、设计评价B、编制应用程序设计说明C、模型转换D、子模式设计正确答案：B12.在使用游标的过程中,需要注意的事项不包括A、游标不能单独在查询操作中使用B、在一个BEGIN···END语句块中每一个游标的名字并不是唯一的C、游标是被SELECT语句检索出来的结果集D、在存储过程或存储函数中可以定义多个游标正确答案：B13.对关系的描述不正确的是A、关系中的元组次序可交换B、关系可以嵌套定义C、关系是一张二维表D、关系是一个集合正确答案：B14.下列关于MySQL的说法中,正确的是A、在MySQL中,一个关系对应多个基本表B、在MySQL中,一个或多个基本表对应一个存储文件C、在MySQL中,一个表只能有一个索引D、在MySQL中,索引不能存放在存储文件中正确答案：B15.关系数据库是以【】的形式组织数据。
南京理工大学《数据库系统基础教程》试题和标准答案
一、选择题60(选择一个最合适的答案，在答题纸上涂黑)１.一个事务中的一组更新操作是一个整体,要么全部执行,要么全部不执行。
这是事务的：A．原子性B.一致性 C.隔离性 D.持久性２．在数据库的三级模式结构中,描述一个数据库中全体数据的全局逻辑结构和特性的是：Ａ．外模式 B．内模式 C.存储模式D．模式3．关于联系的多重性,下面哪种说法不正确？A.一个多对多的联系中允许多对一的情形。
B.一个多对多的联系中允许一对一的情形。
C．一个多对一的联系中允许一对一的情形。
Ｄ．一个多对一的联系中允许多对多的情形。
4.考虑学校里的"学生"和"课程＂之间的联系，该联系的多重性应该是:A. 一对一 B．多对一 C．一对多 D. 多对多5．下面哪种约束要求一组属性在同一实体集任意两个不同实体上的取值不同。
A. 键(key）约束。
Ｂ．单值约束。
C．参照完整性。
D．域(domａin)约束6．关系模型要求各元组的每个分量的值必须是原子性的。
对原子性,下面哪种解释不正确: A.每个属性都没有内部结构。
ﻩＢ.每个属性都不可再分解。
Ｃ.各属性值应属于某种基本数据类型。
ﻩD.属性值不允许为ＮUＬL。
７．对于一个关系的属性(列)集合和元组（行）集合，下面哪种说法不正确:A.改变属性的排列次序不影响该关系。
B．改变元组的排列次序不影响该关系。
C.改变元组的排列次序会改变该关系。
D.关系的模式包括其名称及其属性集合。
８．若Ｒ是实体集R1与R２间的一个多对多联系,将其转换为关系R'，哪种说法不正确：Ａ.R'属性应包括R１与R2的所有属性。
B.R'属性应包括R1与R2的键属性。
C.R1与R２的键属性共同构成R'的键。
D.R'的属性应包括R自身定义的属性。
9.关于函数依赖的判断,下面哪种说法不正确？A.若任意两元组在属性A上一致，在Ｂ上也一致，则有A →B成立。
B.若任意两元组在属性A上一致,在B上不一致,则A → B不成立。
数据库系统基础教程课后答案
Solutions Chapter 44.1.14.1.2a)b)In c we assume that a phone and address can only belong to a single customer (1-m relationship represented by arrow into customer).In d we assume that an address can only belong to one customer and a phone canexist at only one address.If the multiplicity of above relationships were m-to-n, the entity set becomesweak and the key ssNo of customers will be needed as part of the composite keyof the entity set.In c&d, we convert attributes phones and addresses to entity sets. Since entitysets 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 asingle column can be used in relational design i.e. concatenate all values. SQLallows 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.Redundancy: 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 withPresidents 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 Presidentsinto 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 aboutstudios 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 compositeattribute containing concatenation of all studio names in a contact can be considered. A separator character such as "$" can be used. SQL allows searchingof such an attribute using query like '%keyword%'From 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.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 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)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 weak4.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 foreign4.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)(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 relationshipMarried can be included in Father (or Mother). ChildOf is a many-manyrelationship 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,motherAddres ss,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 attributesto 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 withtheir 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 eentities 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 root E1,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.(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.5Males and Females subclasses are complete. Mothers and Fathers are partial. All subclasses are disjoint.4.7.7We convert the ternary relationship Contracts into three binary relationships between a new entity set Contracts and existing entity sets.4.7.9a)b)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.class 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>>Collections 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.c)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.class PokerHand{attribute Array Hand(Card card1,Card card2,Card card3,Cardcard4,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,Cardcard3,Card card4,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)。
数据库系统考试题库和答案
数据库系统考试题库和答案一、选择题1. 数据库管理系统（DBMS）的主要目的是什么？A. 存储数据B. 管理数据库C. 查询数据D. 所有上述内容答案：D2. 在SQL中，哪个关键字用于定义主键？A. PRIMARY KEYB. FOREIGN KEYC. UNIQUE KEYD. PRIMARY答案：A3. 以下哪个不属于关系数据库的特性？A. 表结构B. 数据完整性C. 数据独立性D. 文件系统答案：D4. 哪个SQL语句用于删除表中的数据？A. UPDATEB. DELETEC. DROPD. REMOVE答案：B5. 在数据库设计中，哪个是实体-关系模型中的基本元素？A. 实体B. 属性C. 关系D. 所有上述内容答案：D二、填空题1. 在SQL中，用于定义列的类型为_______。
答案：数据类型2. 数据库系统由_______、数据库、数据库管理系统、应用程序和用户组成。
答案：硬件和软件3. 为了确保数据的完整性和一致性，数据库管理系统通常执行_______。
答案：完整性约束4. _______是数据库中存储数据的方式。
答案：表5. 在关系数据库中，_______用于表示表之间的关系。
答案：外键三、简答题1. 请简述SQL中的SELECT语句的基本语法。
答案：SELECT column1, column2, ...FROM table_nameWHERE condition;2. 请解释什么是数据库范式，并简要说明第一范式和第三范式。
答案：数据库范式是一组规则，用于设计关系数据库，以减少数据冗余和维护数据完整性。
第一范式（1NF）：要求表中的所有字段都是原子性的，即每个字段只能包含一个值。
第三范式（3NF）：要求表中的所有非主键字段都必须直接依赖于主键，而不是依赖于其他非主键字段。
四、案例题假设您正在为一个图书馆管理系统设计数据库。
请列出您认为在这个系统中必要的表，以及每个表的结构和主键。
数据库系统基础教程答案
c)
Again from Augmentation rule of Functional Dependency,
given
BM -> D
then
BMN -> D
Thus we can just add an arrow entering Doctors to fig 4.15. Below figure represents more accurate information however.
Studios entity set. If a contact can be associated with at most five studios, it may be possible to replace the Studios entityset by five attributes viz. studio1, studio2, studio3, studio4, and studio5. Alternately, a composite attribute containing concatenation ofall 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%'
In c&d, we convert attributes phones and addresses to entity sets. Since entity sets often become relations in relational design,
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
一、选择题60(选择一个最合适的答案，在答题纸上涂黑)1．一个事务中的一组更新操作是一个整体，要么全部执行，要么全部不执行。
这是事务的：A.原子性B.一致性 C.隔离性 D.持久性2．在数据库的三级模式结构中，描述一个数据库中全体数据的全局逻辑结构和特性的是：A．外模式 B．模式 C．存储模式D．模式3．关于联系的多重性，下面哪种说法不正确？A.一个多对多的联系中允许多对一的情形。
B.一个多对多的联系中允许一对一的情形。
C.一个多对一的联系中允许一对一的情形。
D.一个多对一的联系中允许多对多的情形。
4．考虑学校里的"学生"和"课程"之间的联系，该联系的多重性应该是：A. 一对一B. 多对一C. 一对多D. 多对多5．下面哪种约束要求一组属性在同一实体集任意两个不同实体上的取值不同。
A. 键(key)约束。
B. 单值约束。
C. 参照完整性。
D. 域(domain)约束6．关系模型要求各元组的每个分量的值必须是原子性的。
对原子性，下面哪种解释不正确：A.每个属性都没有部结构。
B.每个属性都不可再分解。
C.各属性值应属于某种基本数据类型。
D.属性值不允许为NULL。
7．对于一个关系的属性(列)集合和元组(行)集合，下面哪种说法不正确：A.改变属性的排列次序不影响该关系。
B.改变元组的排列次序不影响该关系。
C.改变元组的排列次序会改变该关系。
D.关系的模式包括其名称及其属性集合。
8．若R是实体集R1与R2间的一个多对多联系，将其转换为关系R'，哪种说法不正确：A.R'属性应包括R1与R2的所有属性。
B.R'属性应包括R1与R2的键属性。
C.R1与R2的键属性共同构成R'的键。
D.R'的属性应包括R自身定义的属性。
9．关于函数依赖的判断，下面哪种说法不正确？A.若任意两元组在属性A上一致，在B上也一致，则有A → B成立。
B.若任意两元组在属性A上一致，在B上不一致，则A → B不成立。
C.若任意两元组在属性A上不可能一致，则不管在B上是否一致，有A → B成立。
D.若任意两元组在属性A上不可能一致，则A → B不成立。
10．若某关系R的属性集A函数决定R中所有其它属性，则A为关系R的一个：A.键。
B.主键。
C.超键。
D.外键。
11．当且仅当函数依赖A→BC，则有A→B和A→C。
此规则是A.分解/合并规则。
B.平凡依赖规则。
C.传递规则。
D.增长规则。
12．对于某关系R的某个属性集A，下面哪种说法不正确：A.若属性集A是R的键，则闭包A+是R中所有属性集合。
B.若闭包A+是R中所有属性集合，则属性集A是R的键。
C.若闭包A+是R中所有属性集合，则属性集A是R的超键。
D.当且仅当属性集A是R的超键，闭包A+是R中所有属性集合。
13．某关系R(A, B, C, D)有函数依赖A→B, BC→D, D→A，R总共有几个超键？A.3B.4C.6D.714．某关系R(A, B, C, D)有函数依赖A→B, BC→D, D→A，下面哪个函数依赖不蕴含于已知依赖？A. D→BB. AC→BDC. BC→ADD. BD→AC15．某关系R(A, B, C, D)有函数依赖A→B, BC→D, D→A，该关系若违背BCNF，则应分解成几个关系才能满足BCNF：A.符合BCNF，无需分解B.2个关系C.3个关系D.4个关系16．关系product(prodid,prodname,type,factory)表示所销售的每一种商品的货号、品名、种类及生产厂家，一种商品有唯一货号。
该关系所满足下列最高式是：A 2NF。
B 3NF。
C BCNF。
D 4NF。
17．关系prodsales(salesman,prodid,prodname,quantity,amount)表示销售员销售商品情况：销售员、货号、品名、销售数量及销售金额。
一名销售员可销售多种商品，一种商品可由多名销售员销售。
该关系违背下列最低式是：A.1NF。
B.2NF。
C.3NF。
D.BCNF。
18．关系代数运算中，下面哪种计算可等价表示为其它几种计算的复合形式：A.投影∏B.选择σC.笛卡尔积×D.自然连接∞19．设有关系R(A,B,C)和S(C,D,E)，其元组如下所示：πB,E (R ∞A=D S)的值是：20．在学生选课表studentcourse(stdno,courseno,grade)中，查询选修3号课程、且成绩在70和80分之间的学生的学号(stdno)及其成绩(grade)。
实现该功能的SQL 语句是: A ．SELECT stdno,grade FROM studentcourse WHERE courseno='3' AND BETWEEN 70<grade<80； B ．SELECT stdno,grade FROM studentcourse WHERE courseno='3' AND grade BETWEEN 70 AND 80； C ．SELECT stdno,grade FROM studentcourse WHERE courseno='3' AND grade BETWEEN 70,80； D ．SELECT stdno,grade FROM studentcourse WHERE courseno='3' AND grade IN(70,80)； 21．已知关系R 和S ：Select * From RWhere a<>ALL(Select b From S) 结果是；D.语法错22．学生成绩表R 如下Select (score/10)*10 as ScoreLevel, count(no) as num From RGroup by (score/10)*10 Order by ScoreLevel desc; 结果是:23．关于视图view ，下面哪种说法不正确？ A.基于一条Select 语句可定义一个视图。
D.语法错B.Create View viewName AS...命令可定义视图。
C.查询视图与查询表在语法上是一样的。
D.Select 语句中的From 子句中视图和表不能同时出现。
24．关系R 如下Select * From R Where a>no*2 OR (a>no*2)is Unknown 结果为25．已知关系如下Select empname,amountFrom salesman Natural Left Outer Join salesorder; 结果为:D.语法错语法错26．关于表的主键约束，下面哪种说法不正确：A.select命令不可能违背主键约束。
B.insert命令可能违背主键约束。
C.delete命令不可能违背主键约束。
D.update命令不可能违背主键约束。
27．关于数据库表中的外键，下面哪种说法不正确：A.表R1到R2有外键，R2到R1同时也能定义外键。
B.同一个表中属性A1到主键属性之间也能定义外键。
C.外键所关联的双方属性应属同种数据类型。
D.一个外键只能在单个属性上定义。
28．在并发性事务环境中，事务1先读取某行数据，随后事务2修改了此行数据并提交，造成事务1再读此行时先后结果不一致。
这种现象被称为：A.(Dirty Reads)脏读B.(Non-repeatable Reads)不可重复读C.(Phantom Rows)幻像行D.事务冲突。
29．关于事务控制，下面哪种说法不正确？mit提交当前事务，并结束当前事务。
B.Rollback退回当前事务，取消本事务已做的数据更新，并结束当前事务。
mit提交当前事务，并释放本事务施加的锁。
D.Rollback退回当前事务，继续持有本事务施加的锁。
30．下面哪一种指令能为用户授权，使其能以一定的权限访问特定的数据库对象。
A.GrantB.RevokemitD.Privilege二、综合题40一个简化的图书馆信息管理系统。
系统需求如下：1.图书馆有若干管理员librarian，各自有员工号empid、name、号idno等属性。
2.图书馆中备有若干种图书booktype，每种图书有ISBN、名称title、publisher、作者writers、价格price等属性，每种图书有唯一的ISBN号，同种图书可购入多本。
3.每一本图书book有唯一标记bookid和种类booktype。
4.读者reader在办理借书证后方可借阅。
一个读者有唯一的借书证号cardno，还有name、号idno、住址address、注销标记logoff等。
读者在注销之前，须归还所有已借图书或报失。
5.需处理以下基本业务：①借书：在某时刻某读者通过某管理员借阅某一本书。
②还书：在某时刻通过某管理员归还某一本书。
③报失：在某时刻某读者向某管理员报失某一本书；报失之后该书不能再借。
每一次还书和报失记录都须对应某一次借书记录，且可由不同管理员处理。
按次序回答下列问题：1．用E/R图建立该系统的数据库模型。
为每个实体集确定键属性，并确定每个联系的多重性。
(8) 2．由E/R模型建立该系统的关系模式，并确定每个关系的主键和可能的外键。
注意各关系中的函数依赖和多值依赖，并使你的关系能符合更高式。
(8)3．基于以上关系模式，用关系代数表示下面计算：(6)1)计算“清华大学”出版的图书名称及作者。
2)计算借书证号为“A00345”的读者未归还的图书名称。
3)计算未处理过借书手续的管理员的员工号及其。
4．使用SQL语言实现上述计算，并使输出结果不重复。
(6)5．使用SQL语言完成下面的计算：(12)1) 增加借书记录: 借书证号为'A00345',通过管理员'E0035'于当前日期借到图书'B00124'。
(提示：today(*)函数返回当前日期)2）还书记录：通过管理员'E0034'于当前日期归还图书'B00124'。
(提示：还书无需确认借书证)3）计算借书证号为“A00345”的读者已借超过30天而未还的图书数量。
(提示：days(d1,d2)函数返回从日期d1到d2的天数)4）计算借书次数最多的读者的和号。
(注意使用视图)5）计算所有图书的ISBN、书名、借阅次数，并按借阅次数从大到小排列。
(注意使用外连接)参考答案1．ER 图(一些非键属性未列出)2.关系模型Librarian(empid, name, idno)3.关系代数运算1) 计算“清华大学”出版的图书名称及作者πtitle,writers(σpublisher=’清华大学’Booktype)NULL 1=return 2=loseNOT NULL2) 计算借书证号为“A00345”的读者未归还的图书名称πtitle(σcardno=’A00345’AND end=NULL(Record∞Book∞Booktype))3) 计算未处理过借书手续的管理员的员工号及其。