数据库系统应用期中复习

Part I. (30 Points ,2 points for each problem)

1.

Database system can solve the data redundancy and inconsistency problems with file­processing systems.

(A) True

(B) False

2.

A foreign key is a referential constraint between two tables.

(A) True

(B) False

3.

When a client requests a record that contains a BLOB (binary large object), the database server that receives the request should return the entire record at a time.

(A) True

(B) False

4.

SQL allows you to not only create a new table, but also to delete an existing table from the database.

(A) True

(B) False

5.

The relation R(A,B,C,D) consists of ONLY the tuples that are listed and NO other tuples.

The functional dependency C→Bis not satisfied.

(A) True

(B) False

6.

Given the two tables: Staff(id, name, deptno, job, salary) and Org(deptno, deptname, location) .

The statement “ Update Staff set salary=salary*1.1 where deptno in (select deptno from org where location=’Shanghai’)” will give all staffs in Shanghai a 10 percent salary raise.

(A) True

(B) False

7.

Which statement is true?

(A) Physical level of data abstraction describes what data are stored in the database.

(B) Logical level of data abstraction describes what data are stored in the database.

8.

True or false? Primary key can be null, foreign keys can not be null.

(A) True

(B) false

9.

Given the following database tables.

Primary keys are in bold font.

person( SSN , name, address) car( license , year, model)

accident( license, accident_date , driver, damage_amount)

owns( SSN, license )

We want to find the SSN of every person who owns one or more cars, none of which has ever been involved in a car accident. Which expression is true?

(A)

(B)

10.

What does the following SQL statement do?

“ select name from person where address = ‘Beijing’ ” ?

(A) find the name of all people who live in Beijing and have cars.

(B) find all people who live in Beijing from the person table.

(C) find the name of all people who live in Beijing.

(D) None of the above

11.

Which is the benefit of denormalization?

(A) performance improvement

(B) higher security

(C) less storage

(D) none of the above

12.

Which is true?

(A) Entity sets are weak when their key attributes come from other

classes to which they are related.

(B) It is impossible for tuples to have a null value.

13.

Which of the following is false?

(A) A relation can have only one candidate keys.

(B) A candidate key can uniquely identify a row.

14.

Suppose we have a relation declared by: CREATE TABLE R (name VARCHAR(50) PRIMARY KEY, salary INT CHECK(salary <= 40000) ) ;

Initially, the relation has three records:

We execute the following sequence of modifications.

Some of them may be rejected due to the constraints in the relation.

(1) INSERT INTO R VALUES (‘Fred’, 12000);

(2) UPDATE R SET salary = 50000 WHERE name = ‘Sue’;

(3) INSERT INTO R VALUES (‘Tom’, 13000);

(4) DELETE FROM R WHERE name = ‘Joe’;

At the end of these statements, the sum of the salaries over all the tuples in R is:

(A) 52,000

(B) 62,000

(C) 65,000

(D) 72,000

15.

Which statement about the key is correct?

(a) A primary key must also be a super key.

(b) A candidate key must also be a super key.

(c) A primary key must also be a super key.

(d) All of statements are correct.

Part II. Relational Model (10*4=40 Points)

Chapter 2 Introduction to the Relational Model

章2术语中英对照

英文	中文	注释
Table	表
Relation	关系
Tuple	元组
Null value	空值
Database schema	数据库模式
Database instance	数据库实例
Relation schema	关系模式
Relation instance	关系实例
Keys	码
Superkey	超码	一个或多个属性的集合， 这些属性的组合可以使我们在一个关系中唯一地标识一个元组
Candidate key	候选码	任意真子集都不能成为超码的最小超码
Primary key	主码	用来在一个关系中区分不同元组的候选码
Foreign key	外码	一个关系模式在它的属性中包含另一个关系模式的主码
Referencing relation	参照关系
Referenced relation	被参照关系
Attribute	属性
Domain	域
Atomic domain	原子域
Referential integrity constraint	参照完整性约束	参照完整性约束要求在参照关系中任意元组在特定属性上的取值必然等于被参照关系中某个元组在特定属性上的取值
Schema diagram	模式图	表示一个含有主码和外码依赖的数据库模式
Query language	查询语言
Procedural language	过程化语言
Nonprocedural language	非过程化语言
Operations on relations	关系运算
Selection of tuples	选择元组
Selection of attributes	选择属性
Natural join	自然连接
Cartesian product	笛卡尔积
Set operations	集合运算
Relational algebra	关系代数

A toy factory uses a small database with three relations to manage the toys and parts, as described in Table 1.

Question 1: Please write down relational algebra for following queries.

1. List all toys whose prices are between 100 and 200.

2. List all parts whose prices are greater than 10.

3. List all toys using the part engine.

4. The price of the toy “hello­kitty” is modified to 40. How to implement?

5. List the name of all parts used by the toy “hello­kitty”.

Question 2: Please write down SQL statements for following queries.

1. List the price of “hello­kitty”.

2. Create a view, named alltoys, to list the id and name of all toys.

3. List all toys related to “tigger”.

4. Insert a new toy, with the name as “jump­tigger”, the id as “t006” and the price 150.

5. List the number of parts in each toy, in format of (tid, num).

Part III. Entity­-Relationship Model (15 Points)

Chap 7 

章7术语中英对照

英文	中文	注释
Entity-relationship data model	实体-联系数据模型
Entity and entity set	实体和实体集
Attributes	属性
Domain	域
Simple and composite attributes	简单和复合属性
Single-valued and multivalued attributes	单值和多值属性
Null value	空值
Derived attribute	派生属性
Superkey, candidate key, and primary key	超码、候选码以及主码
Relationship and relationship set	联系和联系集
Binary relationship set	二元联系集
Degree of relationship set	联系集的度
Descriptive attributes	描述性属性
Superkey, candidate key, and primary key	超码、候选码以及主码
Role	角色
Recursive relationship set	自环联系集
E-R diagram	E-R图
Mapping cardinality	映射基数
One-to-one relationship	一对一联系
One-to-many relationship	一对多联系
Many-to-one relationship	多对一联系
Many-to-many relationship	多对多联系
Participation	参与
Total participation	全部参与
Partial participation	部分参与
Weak entity sets and strong entity sets	弱实体集和强实体集
Discriminator attributes	分辨符属性
Identifying relationship	标识联系
Specialization and generalization	特化和概化
Superclass and subclass	超类和子类
Attribute inheritance	属性继承
Single and multiple inheritance	单和多继承
Condition-defifined and userdefined membership	条件定义和用户定义的成员资格
Disjoint and overlapping generalization	不相交概化和重叠概化
Total and partial generalization	全部概化和部分概化
Aggregation	聚集
UML	UML
UML class diagram	UML类图

Model the entities and relationships (including attributes and properties of relationships) described below in an ER­diagram.

Write down any assumptions you make.

Convert the ER­diagram to relational schema and indicate the primary key and foreign keys of each relation.

Your task is to design a database for an online video service that offers hit TV series（连续剧）.

 

The following is the description of the application:

Each series has a name and a description.

We would like to record information on the regular cast （演员） of the series.

In other words, for each main actor/actress in the series, we want to record his/her name and address.

You may assume that the cast doesn’t change.

Each series has many episodes （集） .

Each episode has an episode number, the

year and date it was first aired, and the length of the episode in minutes.

The episode number uniquely identifies an episode with respect to the series, but two different series can have the same episode number.

Registered viewers can comment on any episodes.

For each comment, we want to record its post date and content.

For each registered viewer, we want to record his/her userID and password.

对ER中描述的实体和关系（包括关系的属性和属性）进行建模­图表。

写下你的假设。

转换ER­关系模式的关系图，并指示每个关系的主键和外键。

你的任务是为提供热门电视剧的在线视频服务设计一个数据库(连续剧）.

以下是申请说明：

每个系列都有一个名称和描述。

我们想记录常规演员的信息(演员）系列的一部分。

换句话说，对于这个系列中的每一个主要演员，我们都要记录他/她的名字和地址。

你可以假设演员阵容没有改变。

每个系列都有很多集(集）.

每集都有一个集号

它第一次播出的年份和日期，以及每集的长度（以分钟为单位）。

插曲编号唯一地标识了与该系列相关的一个插曲，但是两个不同的系列可以具有相同的插曲编号。

注册观众可以评论任何一集。

对于每条评论，我们要记录其发布日期和内容。

对于每个注册的查看器，我们要记录他/她的用户名和密码。

Part IV. Relational Database design (15 Points, 5 points for each problem）

Chap 8 Relational Database Design

 

章8术语中英对照

英文	中文	注释
E-R model and normalization	E-R模型和规范化
Decomposition	分解
Functional dependencies	函数依赖
Lossless decomposition	无损分解
Atomic domains	原子域
First normal form (1NF)	第一范式（1NF）
Legal relations	合法关系
Superkey	超码
R satisfifies F	R满足F
F holds on R	F在R上成立
Boyce –Codd normal form ( BCNF )	范式（BCNF）	具有函数依赖集F的关系模式R属于BCNF的条件是，对F+中所有形如α→β的函数依赖（其中α  R且β  R）， 下面至少有一项成立： α→β是平凡的函数依赖（即，β  α） α是模式R的一个超码
Dependency preservation	保持依赖
Third normal form ( 3NF )	第三范式（3NF）	具有函数依赖集F的关系模式R属于第三范式的条件是，对F+中所有形如α→β的函数依赖（其中α  R且β  R）， 以下至少一项成立： α→β是一个平凡的函数依赖（即，β  α） α是R的一个超码 β-α中的每个属性A都包含于R的一个候选码中
Trivial functional dependencies	平凡的函数依赖
Closure of a set of functional dependencies	函数依赖集的闭包
Armstrong’s axioms	Armstrong公理
Closure of attribute sets	属性集闭包	令α为一个属性集，我们将函数依赖集F下被α函数确定的所有属性集的集合成为F下α的闭包，记为α+
Restriction of F to R i	F在Ri上的限定
Canonical cover	正则覆盖
Extraneous attributes	无关属性
BCNF decomposition algorithm	BCNF分解算法
3NF decomposition algorithm	3NF分解算法
Multivalued dependencies	多值依赖
Fourth normal form ( 4NF )	第四范式（4NF）
Restriction of a multivalued dependency	多值依赖的限定
Project-join normal form( PJNF )	投影-连接范式（PJNF）
Domain-key normal form( DKNF )	域-码范式（DKNF）
Universalrelation	泛关系
Unique-roleassumption	唯一角色假设
Denormalization	去规范化

1.

2.

3.

Answer

III

Assumption:

– The name of each series is unique, so name is a key for Series.

– The name of actors/actresses is unique, so name is a key for Cast.

– userID is unique, so userID is a key for Users.

– Many actors/actresses can star in one series.

– One actor/actress can star in multiple different series. Hence, Act is many­many.

– Many users can comment on one episode. One user can comment on different

episodes. Hence, Comment is many­many.

1. Cast(name, address)

PK: (name)

2. Act(castName, seriesName)

PK: (castName, seriesName) FK: (castName)

FK: (seriesName)

3. Series(name, description)

PK: (name)

4. Episodes(EpiID, seriesName, year, date, length)

PK: (seriesName, EpiID)

FK: (seriesName)

5. Comment(userID, EID, seriesName, date, content)

PK: (userID, seriesName, EpiID)

FK: (userID)

FK: (seriesName, EpiID)

6. Users(userID, password)

PK: (userID)

IV-1

IV-2

IV-3