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

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

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

考试类型：闭卷               考试时间： 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. About relational database, _____ is wrong.

A. The relational model uses a collection of tables to represent both data and the relationships among those data.

B. Each relation has multiple columns, and each column has several names.

C. Each table contains tuples of a particular type. And each tuple type includes a fixed number of fields, or attributes.

D. In relational database, tables are also known as relations.

1. _____ can have attributes.

A. Only entity sets                        B. Only relationship sets

C. Both entity sets and relationship sets        D. None of the above

1. If the primary key of the relationship set in_charge_of between entity sets manager and project is {project_id}, then the mapping cardinality of in_charge_of is ________

A. many to many   B. one to many  C. many to one D. not sure

1. When an entity is converted to a relation schema, _____ attribute is placed in a new relation schema rather than the entity’s relation schema.

A. composite    B. multi-valued   C. derived   D. simple

1. _____ combine entity sets that share the same features into a higher-level entity-set.

A. Aggregation  B. Generalization  C. Specialization  D. None of the above

1. SQL language use the ______ statement to remove a column from a table.

A. Alter      B. Delete     C. Drop      D. Update

1. In SQL, ______ is an equivalent operator to “IN”

1. <>ALL B. <> SOME C. =ALL D. =SOME

1. Given the following tables:

Stud			SC
Sno	Sname		S#	C#	Grade
1	Smith		1	1	80
2	Jones		2	5	90
3	Smith		2	6	100
4	BOB		3	1	30

________removes the rows from the Stud table that have courses in the SC table?

A. DELETE FROM Stud WHERE S# = (SELECT Sno FROM SC);

B. DELETE FROM Stud WHERE S# IN (SELECT S# FROM SC);

C. DELETE FROM Stud WHERE Sno IN (SELECT S# FROM SC);

D. DELETE FROM Stud WHERE Sno = (SELECT S# FROM SC);

1. In the statements, ________ is incorrect?

A. The project operator ∏ is equivalent to the “Select” clause in SQL.

B. The order of columns in a relation is changeable.

C. One view may be used in the expression defining another view.

D. The values within one column can come from different domains.

1. ____ensures that, once a transaction has been committed, that transaction’s updates do not get lost, even if there is a system failure.

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

1. Schedule must be _______ to make sure that if transaction Ti sees the effects of transaction Tj, and Tj then aborts, then Ti also gets abort.

A. recoverable   B. cascadeless   C. serializable   D. conflict serializable

1.         releases of exclusive locks only at the end of transaction, not requiring that shared locks are released only at the end of transaction.

A. The locking protocol

B. The two-phase locking protocol

C. The strict two-phase locking protocol

D. The rigorous two-phase locking protocol

1. The statement that the two-phase locking protocol ensures deadlock freedom is _____.

A. true    B. false    C. true or false    D. not known

1. A transaction is considered to have committed when ________

A. its last log record for the transaction has been output to stable storage.

B. all updates are recorded on a log.

C. all write operations are done.

D. all updates are rolled back.

1. Applications are said to exhibit________ if they do not depend on the physical schema of database and thus need not be rewritten if the physical schema changes.

A. logical independence B. logical schema independence

C. physical schema independence D. independence to database

1. data dictionary is         

A. a special type of tables that contain metadata of the database.

B. Tables used to contain user data.

C. Tables used to contain the output of the DML.

D. that contains metadata and can be updated by DBA.

1. The authorization sentence: ‘grant select on department to Amit, Satoshi;’ is executed successfully, then

A. The user Satoshi can update or delete some records of table department.

B. The user Satoshi can update all records of the table department.

C. The user Amit can modify schema of the table department.

D. The user Amit can read all records of table department.

1. There is a trigger defined on the table instructor as:

create trigger setnull before insert of instructor

referencing new row as nrow

for each row

when (nrow.dept_name = ’ ’) begin atomic

set nrow.dept_name = null;
end;”

then the insert sentence “insert into instructor (id, name, dept_name, salary) values (‘12123’,’Kate’,’ ’ , 32767)”will insert           into table takes.

A. ‘12123’,’Kate’,  ’ ’, 32767 B. ‘12123’,’Kate’, null, 32767

C. ‘12123’,’Kate’,’’, 32767 D. Failed to insert a record

1. Which of the following statements is false?

A. Relation schema R is in 1NF if the domains of all attributes of R are atomic.

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

C. If a relation is in BCNF it also in 3NF

D. If a relation is in 3NF it also in BCNF

1. Let R be a relational schema, and X,Y, Z Í R, in the following rules about functional dependency,            is correct.

A. X®Y if XÇ Y ≠Æ   B. X®WZ if X®Y and WY®Z

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

得分

Question (21 points) Consider the following relational schema in an Insurance database.

Person(driver_id, name, address);

Car(license_plate, model, year);

Accident(report_no, year, location);

Owns(driver_id, license_plate);

Participated(report_no, license_plate, driver_id, damage_amount);

1. Find the license plates of cars owned by the driver named “Smith” (in relational algebra and SQL 6 points)
2. Find names and driver id(s) for these persons who never had any accidents in 2021. (in relational algebra and SQL 6 points )
3. Find the location with the most of accidents in 2021.(in SQL 3 points)
4. Find the driver id and person names that had at least two accidents in 2021.(in SQL 3 points)
5. Increase the damage amount by 10% for the car with license plate “SCAU888” in the accident(s) located at “Wushan” in 2021.(in SQL 3 points)

Question 3

(13 points)

得分

A paper has attributes ID and title. A journal has attributes ID, name, factor and category. An author has attributes ID, first name, last name, title and email. A funding has attributes ID, title, agency and year. A paper can be written by several authors. A paper must have at least one author. A paper can be published in only one journal. If a paper is published, the publication information like year, volume, issue, page must be recorded in the database. An author can be supported by many fundings, and a funding must support exactly one author.

(1) Draw ER diagram to illustrate the above database requirement (7 points).

(2) 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 reference R:A (with R is the referenced relation and A are the referenced attributes)”.(6 points)

得分

Question 

4 (11 points)

 Let R=ABCDEF be a relation, and the set of functional dependencies F ={AB→CD, D→C, DEF→AB, DE→B, AC→DC } holds on R.

(1) List all candidate keys of relation R. (3 points)

(2) Does the functional dependency AC→B holds on R, Explain why. (2 points)

(3) Give a decomposition of R with only one time of BCNF decomposition. (3 points)

(4) Give a 3NF decomposition of R, and list the main procedure for calculating your canonical cover. (3 points)

得分

Question 5 (15 points).

Part one Consider the following four transactions:

T0	T1	T2	T3
Read(A);	Read(B);	Read(C)	Read(D)
Read(B);	Read(C);	C:=C-100;	D:=D+100;
A:=A-50;	B:=B-500;	Write(C);	Write(D)
B:=B+50;	C:=C+500;
Write(A);	Write(B);
Write(B).	Write(C)

Let A=1000, B=2000, C=700, D=300 as the initial values.

(1) Explain the distinction between the terms serial schedule and serializable schedule. (2 points)

(2) Give a concurrent execution of T0, T1, T2, and T3 that produces a serializable schedule such that the order in which the transaction commit is different from the serializable order. Then show its serializability order. (4 points)

Part 2

Consider the following log. Suppose there is a crash just before the log record <T1, abort> is written out.

Beginning of log

<To, start>

<To, A, 1000, 950>

<To, B, 2000, 2050>

<T1, start>

<T0, commit>

<T1, B, 2050, 1550>

<T2, start>

<T2, C, 700, 600>

<checkpoint {T1, T2}>

<T3, start>

<T2, commit>

<T3, D, 300, 400>

<T1, B, 2050>

<T1, abort>

(3) Explain the checkpoint technology. (2 points)

(4) Describe the change of Undo List In the Redo Pass. (3 points)

(5) Show the log records which should be added during recovery. (2 points)

(6) List the final value of B, C and D after completing recovery. (2 points)