数据库原理与应用期末试卷（2）SCAU_relation is a table in relational database-优快云博客

本文链接：https://blog.youkuaiyun.com/csdn3043663729/article/details/144735218

华南农业大学期末考试试卷（A卷）

2020-2021学年第一学期　考试科目： 数据库系统（双语）

考试类型：闭卷 考试时间： 120 分钟

学号姓名年级专业

题号	一	二	三	四	五	总分
得分
评阅人

Instructions to candidates:

1. Write your name, student number and class on both the question papers and the answer papers. And write ALL YOUR ANSWERS ON THE ANSWER PAPERS.

2. Write your answers in either Chinese or English.

3. Hand in all papers (both the question papers and the answer papers).

得分

Question 1 (40 points, 2 points for each problem) single-choice question, select the most appropriate answer please.

The database system is ________.

A. equivalent to the DBMS

B. equivalent to the DBMS and the managed databases

C. a collection of interrelated data

D. the overall design of the database

The collection of information stored in the database at a particular moment is called .

database instance B. database scheme C. physical scheme D. data relationship

is a nonprocedural language used in commercial database systems.

A. Relational algebra B. Tuple relational calculus

C. SQL D. Domain relational calculus

of the following is true about the number of keys with respect to a table?

A. There can be many primary keys and many candidate keys

B. There can be only one primary key and many candidate keys

C. There can be many primary keys but only one foreign key

D. There can be only one primary key and only one super key

In the following terms, is proper.

A. relation is a table in relational database B. the attribute is a row of a table

C. the tuple means a column of a table D. each table should have many records

Using functions and procedures in the database system is beneficial to ________

A. modularization B. performance C. security D. all of the above

Which the following statement about view is not correct?

A. View relation can be defined as relations containing the result of queries.

B. View is useful for hiding unneeded information

C. View is a virtual relation, that is, it is an empty table with no records

D. View can be used to collect together information from more than one relation

is not one of the three basic notions in E-R model.

A. Entity B. Relationship C. Attributes D. Schemas

Which of the following statements is false?

A. 4NF is the most strict norm form with respect to the functional dependency theory

B. A relation in 3NF is also in 1NF

C. If a relation satisfies 4NF, it also satisfies 3NF

D. BCNF is stricter than 3NF

_______ is not the property of transactions.

A. Atomicity B. Inconsistency C. Isolation D. Durability

If a transaction T has obtained an shared lock on data item A, then T can

A. only read A B. only write A C. read and write A D. none of the above

When mapping from an ER model to a relational model, a relationship can be mapped into .

A. a super key of the relation transferred from a related entity set

B. a row of the relation transferred from a related entity set

C. a table with describing attribute of the relationship

D. a table or a set of columns in the relation transferred from a related entity set

In database physical-design phase, must be given.

A. dataflow graph B. inner Schema of database

C. logical schema of database D. user Schema of database

In SQL, which of the following predicate can be used in the where clause to test ‘amount’ for a null value.

A. amount=’ ’ B. amount is null C. amount =0 D. amount = null

The SQL statement“Select count(*) From employee left outer join works”is run on the following tables, then is the output of the query?

employee works

emp_name	street	city	emp_name	company	salary
Alice	Toon	Seattle	Alice	Google	5000
Bob	Tunnel	Hollywood	Bob	Yahoo	4300
Coyote	Seaview	Carrotville	Williams	Dropbox	3800
Smith	Revolver	Death Valley

A. 2 B. 3 C. 4 D. 5

Given the table T1, created by: CREATE TABLE EMP(ID CHAR(3) PRIMARY KEY, Name CHAR(8), Age Integer, CONSTRAINT const1 CHECK (Age>18 and Age<60));

The following SQL statements are issued:

INSERT INTO EMP(ID, Age) VALUES ('001',25);

INSERT INTO EMP(ID, Age) VALUES (‘002’,35);

INSERT INTO EMP(ID, Age) VALUES (‘003’,15);

INSERT INTO EMP VALUES (‘004’, 40);

COMMIT;

How many rows are inserted into Table EMP?

A. 0 B. 1 C. 2 D. 3

In the following rules about functional dependency, is correct.

A. X®Y if XÇ Y =Æ B. WX®Z if X®Y and WY®Z

C. X®Z, Y®Z if XY®Z D. X®Y if XÍ Y

Let A, B, C and D be attributes, in the following relational algebra expressions,

is not a relation.

A. R(A´B´C´D) B.R(A) C. R(A,B) D. R(A,B) ´ S(C,D)

Among the following sentences about trigger, is wrong.

A. trigger is a special kind of stored procedure

B. trigger is different to stored procedure in that it is auto-executed and with no parameters.

C. triggers can be used to maintain data consistence

D. in triggers, temporary table new and old (or inserted and deleted) can be queried and updated.

If there is a one-to-many relationship between the entity sets A and B, then

A. there exists a functional dependency from the primary key in B to the primary key in A, i.e., PK(B) → PK(A).

B. there exists a functional dependency from the primary key in A to the primary key in B, i.e., PK(A) → PK(B).

C. both A and B. D. neither A nor B

得分

Question 2

(21 points) Consider the following relational schema

Teacher(TId, TName, office, age)

Equipment(EId, Ename, serial_number, price)

Own(TId, EId, date)

a. Print the Id of teacher who do not own any equipment. (in relational algebra，3 points).

b. Print the name of teachers who have owned any equipment with price < 1000(in both SQL and relational algebra, 3 points for each, 6 points in total).

c. Print the name of teachers who have owned the equipment with the highest price (in SQL and relational algebra, 3 points for each, 6 points in total).

d. For each teacher, print the name and the total price of equipment which is owned by that teacher. (in SQL, 3points)

e. Give all price of equipment with price>10000 a 10 percent devaluation (贬值) (in SQL, 3 points).

Question 3

(15 points)

得分

A hospital has properties like ID, name, location, rank, capacity. A hospital has many wards used for patients, and a ward can be described by attributes like roomID, building, number of hospital beds. A doctor can be described by ID, name, age, specialty and title. A patient has properties like ID, name, age, sex, address. The above objects must satisfy some constraints: Each doctor can be unemployed or employed by one hospital. If a doctor is employed, his salary needs to be recorded in the database. A patient can go to many hospitals. An inpatient is a patient lived in a certain ward and managed by a resident doctor (resident is a kind of title of doctors.). A ward belongs to only one hospital, and each hospital is equipped with multiple wards.

a. Draw ER diagram to illustrate the above database requirement (8 points).

b. Translate your ER diagram into relational database schemas, and point out the primary keys and foreign keys. You can write your answers in the following format: “R(a1, a2, a3, a4), primary key: a1, foreign key: a4” (5 points).

c. Please give an example from the database schema you designed to illustrate the function of foreign key. (2 points)

得分

Question

4 (12 points)

Let R=ABCDE be a relation, and the set of functional dependency F ={A

C, C

A, B

AC, D

AC} holds on R.

a. Compute AD+. （2 points）

b. Compute a canonical cover for the above set of functional dependencies F; give each step of your derivation with an explanation. (2 points)

c. List the candidate keys for R. (2 points)

d. Give a decomposition of R with only one time of BCNF decomposition using the original set of functional dependencies. (3 points)

e. Give a 3NF decomposition of R based on the canonical cover. (3points)

得分

Question 5 (12 points). Suppose the immediate database modification approach and the checkpoint technique are used. The following figure depicts the concurrent transactions, time of checkpoint and time of system failure.