班号 学号三星手机爆炸事件 姓名 成绩
《 数据库系统概论 》期末考试卷
注意事项：1、考试时间2小时；
2、答案写在答题纸上
题目：
一、 ……………………………………………………………( 分)
二、 ……………………………………………………………( 分)
三、 ……………………………………………………………( 分)
四、 ……………………………………………………………( 分)
五、 ……………………………………………………………( 分)
六、 ……………………………………………………………( 分)
Problem 1: TRUE or FALSE QUESTIONS (30 points).
For each of the following statements, indicate whether the entire statement is TRUE or FALSE
1. There are five different entities and five binary relationships in an ER diagram. Two of the relationships are 1:1 relationship; 3 of them are M:N. After translating from ER model to Relation model, we can probably get 9 relations (tables). TURE
2. An E-R diagram will translate uniquely to a relational schema. FALSE
3. 3. A relation with two attributes is always in 3NF, but may not be in BCNF.
FALSE
4. R ▷◁ (S ∩ T) = (R ▷◁ S) ∩ (R ▷◁ T) TRUE
5. In relational algebra, join is a derived operator. TRUE
6. In a table, there is exactly one key, but there can be multiple candidate keys.
TRUE
7. The natural join of two relations R(A,B) and S(C,D), which have no common attributes, is equivalent to their Cartesian product. TRUE
静止状态8. In SQL, without GROUP BY, we cannot u HAVING.
TRUE
9. Triggers can operate on inrtion, deletion, and updates. TRUE
10. There are two relations: Stu_Cour(stu_name, cour, score), Score_Sum(stu_name, Sum). The ur wants to define a constraint as: for the same student, the value of Sum attribute in score_sum table equals the sum of all score attribute values in stu_cour table. We can u Check constraint to apply this requirement. FALSE
Problem 2: (10 Points)
槽溜Consider the following schedule：
T1 STARTS |
T1 reads item B |
T1 writes item B with old value 11, new value 12 |
T2 STARTS |
T2 reads item B |
T2 writes item B with old value 12, new value 13 |
T3 STARTS |
T3 reads item A |
T3 writes item A with old value 29, new value 30 |
T2 reads item A |
T2 writes item A with old value 30, new value 31 |
T2 COMMITS |
T1 reads item D |
T1 writes item D with old value 44, new value 45 |
T3 COMMITS |
T1 COMMITS |
|
(a) What rial schedule is this equivalent to? If none, then explain why.
The rializability graph for the above schedule is: T1 T2 T3. Any order that complies with the
topological order of the graph like T1 T3 T2 is an equivalent rial schedule for our schedule
(b) Is this schedule consistent with two pha locking? Explain why.
If we assume that all transactions get the locks exactly before the operation and relea them afterwards, it is not consistent with two pha locking. This is becau T1 releas its lock on B after its cond operation while acquiring a lock on D at its last two operations. By removing the last two operations of开学典礼主持词 T1 the schedule becomes 2PL.
If we assume that the transactions get all the locks they need at the beginning of the transaction, and relea them after the finish the operation, this schedule will be 2PL. The minimum operations that could be added to the schedule will be "T1 reads item A". In
this ca, T1 has to acquire the lock on A again after releasing its lock on A after its first write.
Problem 3: (5 Points)
There are two relations:
R: S:
Give the answers to the two queries:
∏R.A, S.B (σR.A=S.D（R▷◁S）)， R]▷◁S (left outer join of R and S).
Problem4: (5 points)
Consider a relation R(A,B,C,D,E), with FDs AB → C, C → A, C → BD, D → E
Remember Armstrong’s Axioms:
1) XY → X (reflexive)
2) X → Y => XZ → YZ (augmentation)
3) X → Y & Y → Z => X → Z (transitivity)
4) X → Y & X → Z => X → YZ (union)
5) X → YZ => X → Y & X → Z (decomposition)
6) X → Y & YZ → U => XZ → U (pudo transitivity)
(a) Is the FD: ABC -> E implied? Show your derivation. (5 points)
ABC → C reflexive
C → BD given
C → D decomposition
D → E given
(b) Complete the missing values in the following table. The last column is filled in as an example
a. Consider the attribute subts, X, in the table below.
b. Compute the attribute closure of each subt X+
c. Determine if the subt, X, is a superkey or a (minimal) key
X | A | B | C | AB | AC | AD | 芹菜炒肉片CD | ABC | ABCD |
X+ | A | B | ABCDE | ABCDE | ABCDE | ADE | ABCDE | ABCDE | ABCDE |
Key? | | | Key顺序拼音 | Key | SuperKey | | SuperKey | SuperKey85属什么生肖 | SuperKey |
| | | | | | | | | |
(e) Is this relation in BCNF? If you answer is yes, explain why it is. If you answer is no, decompo relation into BCNF, showing your decomposition steps.
No it is not, the FD, D → E, violates BCNF. We can parate into two relations:
