南京理工大学《数据库系统基础教程》试题和标准答案

合集下载

数据库系统课后习题及答案

数据库系统课后习题及答案第1章绪论习题参考答案1、试述数据、数据库、数据库管理系统、数据库系统的概念。

（3、4、5页）答：描述事物的符号记录称为数据；数据库是长期储存在计算机内的、有组织的、可共享的数据集合；数据库管理系统是位于用户与操作系统之间的一层数据管理软件; 数据库系统是指在计算机系统中引入数据库后的系统，一般由数据库、数据库管理系统(及其开发工具)、应用系统、数据库管理员和用户构成。

2．使用数据库系统有什么好处?（12页）答：数据库系统使信息系统从以加工数据的程序为中心转向围绕共享的数据库为中心的阶段，这样既便于数据的集中管理，又有利于应用程序的研制和维护，提高了数据的利用率和相容性，提高了决策的可靠性。

3．试述文件系统与数据库系统的区别和联系。

（8、9、10页）答：1）数据结构化是数据库与文件系统的根本区别。

在文件系统中，相互独立的文件的记录内部是有结构的，管其记录内部已有了某些结构，但记录之间没有联系。

数据库系统实现整体数据的结构化，是数据库的主要特征之一。

2）在文件系统中，数据的最小存取单位是记录，粒度不能细到数据项。

而在数据库系统中，存取数据的方式也很灵活，可以存取数据库中的某一个数据项、一组数据项一个记录或或一组记录。

3）文件系统中的文件是为某一特定应用服务的，文件的逻辑结构对该应用程序来说是优化的，因此要想对现有的数据再增加一些新的应用会很困难，系统不容易扩充。

而在数据库系统中数据不再针对某一应用，而是面向全组织，具有整体的结构化。

5．试述数据库系统的特点。

（9、10、11页）答：数据结构化；数据的共享性高、冗余度低、易扩充；数据独立性高；数据由DBMS统一管理和控制。

6．数据库管理系统的主要功能有哪些? （4页）答：数据定义功能、数据操纵功能、数据库的运行管理、数据库的建立和维护功能。

7．试述数据模型的概念（13页）、数据模型的作用、数据模型的三个要素。

（14、15页）答：数据模型(Data Model)也是一种模型，它是现实世界数据特征的抽象。

《数据库系统教程》试卷及答案

《数据库系统教程》试卷及答案《数据库系统教程》该书详细介绍了数据库理论知识与数据库技术实践等实际内容教程。

以下是由关于《数据库系统教程》试卷的内容，希望大家喜欢!一、单项选择题(本大题共15小题，共25分。

1、DBS具有较高的数据独立性，是因为DBS采用了 [ ]A、嵌入式语言B、三级模式构造C、DDD、六个层次的存储介质2、在层次、网状模型中，起导航数据作用的是 [ ]A、指针B、关键码C、DDD、索引2=13、设关系R(A,B,C)和关系S(B,C,D), 那么与R?S等价的关系代数表达式是 [ ]A、σ2=4(R?S)B、σ2=4(R×S)C、σ2=1(R?S)D、σ2=1(R×S)4、设关系R和S的构造相同，分别有m和n个元组，那么R-S操作的结果中元组个数为 [ ]A、为m-nB、为mC、小于等于mD、小于等于(m-n)5、元组比拟操作(a1，a2)>=(b1，b2)的意义是 [ ]A、(a1>=b1)AND(a2>=b2)B、(a1>=b1)OR((a1=b1)AND(a2>=b2))C、(a1>b1)AND((a1=b1)AND(a2>=b2))D、(a1>b1)OR((a1=b1)AND(a2>=b2))6、设有关系R(A，B，C)的值如下：A B C5 6 56 7 56 8 6以下表达正确的选项是 [ ]A、函数依赖C→A在上述关系中成立B、函数依赖AB→C在上述关系中成立C、函数依赖A→C在上述关系中成立D、函数依赖C→AB在上述关系中成立7、设关系模式R(A，B，C，D)，F是R上成立的FD集，F={ A→C，BC→D }，那么ρ={ ABD，AC }相对于F [ ]A、是无损联接分解，也是保持FD的分解B、是无损联接分解，但不保持FD的分解C、不是无损联接分解，但保持FD的分解D、既不是无损联接分解，也不保持FD的分解8、概念设计的结果得到的是概念模式。

南京理工大学复试科目数据库系统基础教程考试大纲

第一章数据库系统的世界(概述)一、要求掌握的主要概念数据库、数据独立性、数据库管理系统、模式、子模式、内模式、数据库系统二、应理解和掌握的主要内容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) 键答案：a) 表格2. 数据库管理系统(DBMS)的主要功能包括：a) 数据的存储和管理b) 数据的查询和分析c) 数据的备份和恢复d) 数据的安全性控制答案：a) 数据的存储和管理、b) 数据的查询和分析、c) 数据的备份和恢复、d) 数据的安全性控制3. 关系数据库中，用于唯一标识一条记录的属性被称为：a) 主键b) 外键c) 候选键d) 索引答案：a) 主键4. 数据库的范式用于描述数据库的结构是否符合某种规范。

以下哪一项不是数据库的范式：a) 一范式b) 二范式c) 三范式d) 四范式答案：d) 四范式5. 在SQL语句中，用于插入新记录的关键字是：a) SELECTb) UPDATEc) INSERTd) DELETE答案：c) INSERT二、填空题1. 在关系数据库中，每一行都代表一个________。

答案：记录2. 数据库中具有相同属性的记录集合被称为____________。

答案：表3. 在关系数据库中，用于检索某一部分记录的语句是________。

答案：SELECT4. 数据库设计中常用的三个范式依次是一范式、二范式和__________。

答案：三范式5. 数据库表中，用于唯一标识一条记录的属性被称为________。

答案：主键三、应用题1. 请列举至少三种数据库管理系统(DBMS)的类型，并简要描述它们的特点。

答案：关系型数据库管理系统(RDBMS)：采用表格的形式组织数据，具有高度结构化和强一致性的特点。

常见的关系型数据库管理系统有MySQL、Oracle、SQL Server等。

数据库基础课后习题及答案

数据库基础课后习题及答案数据库基础课后习题及答案数据库是计算机科学中非常重要的一个概念，它用于存储和管理大量的数据。

在数据库基础课程中，学生通常需要完成一些习题来巩固所学的知识。

本文将介绍一些常见的数据库基础课后习题，并提供相应的答案。

一、选择题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.关系数据库是以【】的形式组织数据。

数据库系统基础教程课后答案

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）：要求表中的所有非主键字段都必须直接依赖于主键，而不是依赖于其他非主键字段。

四、案例题假设您正在为一个图书馆管理系统设计数据库。

请列出您认为在这个系统中必要的表，以及每个表的结构和主键。

1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。

一、选择题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不成立。

Ｃ.若任意两元组在属性Ａ上不可能一致，则不管在B上是否一致，有A →B成立。

D.若任意两元组在属性A上不可能一致,则A → B不成立。

1０.若某关系Ｒ的属性集A函数决定Ｒ中所有其它属性,则A为关系R的一个:
A.键。

B．主键。

C.超键。

D.外键。

１１.当且仅当函数依赖A→BC,则有A→B和A→Ｃ。

此规则是
A.分解／合并规则。

B.平凡依赖规则。

C.传递规则。

Ｄ.增长规则。

１2．对于某关系R的某个属性集Ａ，下面哪种说法不正确：
A．若属性集A是R的键,则闭包A+是R中所有属性集合。

B．若闭包A+是R中所有属性集合，则属性集A是R的键。

C.若闭包A+是Ｒ中所有属性集合，则属性集A是R的超键。

D.当且仅当属性集Ａ是R的超键，闭包Ａ+是R中所有属性集合。

13．某关系Ｒ（A， B, C, D)有函数依赖A→Ｂ,BC→D，D→A，R总共有几个超键？
A.3
B.4 Ｃ.6Ｄ.7
14.某关系R（Ａ， B, Ｃ, D)有函数依赖A→B,BC→D, Ｄ→A，下面哪个函数依赖不蕴含于已知依赖?
A.D→B B． AＣ→BD C.BC→ＡＤ D. BD→AC
15．某关系R（A, B, C, D)有函数依赖A→B,BＣ→Ｄ,Ｄ→A，该关系若违背ＢCNF,则应分解成几个关系才能满足BCNＦ：
Ａ.符合BＣＮF,无需分解 B.2个关系 C.3个关系 D.4个关系
16.关系prｏduｃt(pｒodｉd,prodnaｍe,ｔype,fａctory)表示所销售的每一种商品的货号、品名、种类及生产厂家,一种商品有唯一货号。

该关系所满足下列最高范式是:
A 2NＦ。

B3NF。

C ＢCＮＦ。

Ｄ4NF。

１7.关系prｏdsaleｓ（salesman,pｒoｄid,proｄname,quａntｉｔy,amounｔ）表示销售员销售商品情况:销售员、货号、品名、销售数量及销售金额。

一名销售员可销售多种商品,一种商品可由多名销售员销售。

该关系违背下列最低范式是:
A.1NF。

Ｂ.2NF。

C.3NF。

D．ＢCNＦ。

18.关系代数运算中，下面哪种计算可等价表示为其它几种计算的复合形式：
A.投影∏
B.选择σ
C.笛卡尔积×
D.自然连接∞
19．设有关系R(A,B，Ｃ)和S(C,D,E），其元组如下所示：。