数据库原理与应用期末试卷（2）SCAU

标签：key database 数据库 points relation 期末试卷 following relational SCAU

华南农业大学期末考试试卷（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.

1. 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

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

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

1.         is a nonprocedural language used in commercial database systems.

A. Relational algebra    B. Tuple relational calculus

C. SQL               D. Domain relational calculus

1.       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

1. 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

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

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

1. 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

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

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

1. 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

1. _______ is not the property of transactions.

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

1. 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

1. 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

1. 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

1. 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

1. 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

1. 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

1.  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

1. 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)

1. 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.

1.  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.

a. Write down the log records according to the above figure. (3 points)

c. What is the advantage of using checkpoint technique? (3 points)

d. Draw the state diagram of a transaction. (Hint: there are five states) (3 points)

标签：key,database,数据库,points,relation,期末试卷,following,relational,SCAU
From： https://blog.csdn.net/csdn3043663729/article/details/144735218