Bengal Engineering and Science University 2007 B.E Computer Science and Engineering Database management system - Question Paper
The ques. paper is with the attachment.
B.E.(CST) 6th SEMESTER EXAMINATION, JUNE 2007 Datal3a.se Management Systems (CST G03)
Answer any 5 questions.
F.M. 100 Time: 3 hrs
1. a) List four significant differences between a file-processing system and DBMS
b) Explain the difference between physical and logical data independence?
c) What is meant by a recursive relationship type? Give one example of recursive relationship type.
d) Construct, an E-R diagram for a car insurance company whose customers own one or more car each. Each car has associated with it zero to any number of recorded accidents. (6 + 1 + 1 + 6]
2. a) Discuss insertion, deletion and modification anomalies. Why are they considered bad?
b) Consider a relational schema R(A, B, C) with FDs AB * C' and C > A.
What is the highest normal form satisfied by the relational schema R? Determine the key for R.
c) Consider the relation schema R(A, B, C', D. E) and set of functional dependencies A C\ AB D and D * E. The relational schema is decomposed into following relational schema: R1(A, C). R2(A. B, D) and R3(D, E).
Show the above decomposition is lossless.
[8 + G + ii]
3 a) Define database trigger and cursor.
11) Fair Deals Limited have decided to award some of their si a If a pav-rise. They intended to increase each persons salary individually by 10 percent, starting with lowest paid earner and working up. If at any point the total salary bill exceeds Rs. GO.000 then 110 further employees are to be given an increase.
Write a Pl/SQL program to do this for EMP table.
c) A database has the following relations:
EMP(eno. ename, sal)
DEPT(dno, dname)
STUDENTS(sname, sroll. totalmarks)
Write the SQL queries for the following:
i) List the name and salary of the employees who have the salary greater than the average salary of the employees in their respective department.
iil Display the roll number(s) of the student who got the second highest marks.
d) Write clo you mean by outer join ? Explain with an example. [-1 + 6 + 0 + 5]
4. a) Clearly explain the deferred database modification and immediate database modification and also identify their differences.
(*) What are the differences between primary and secondary indexes?
A D+ tree index is to be built on the name attribute of the STUDENT relation. Assume that all student names are of the length 8 bytes, disk blocks are of size 512 bytes and index pointers are of size 4 bytes. Given the scenario, what would be the best choice of the degree of the ii+ tree. [8 + 4 + 8]
5. What is a ciuerv execution plan? Disctss the main hcuristics that are applied during query optimization.
b) Let relation rl( A.B-C) and r2(C.D.E) have the following properties: rl has 20.000 tuples. r2 has 45.000 tuples; 25 tuples of rl fit on one block and 30 tuples of r2 fit on one block. For the following join strategies, estimate the number of block accesses required to join rl and r2.
i) Nestcd-loop join; ii) Block nested-loop join
c) What is the difference between pipelining and materialization? [8 + 7 + 5]
(5.a) Discuss Ihe problem of deadlock and the different approaches to dealing with the problem.
. b) Define conflict serializability. What is the two-phase protocol? How does it guarantee serializability?
c) Let Tj. T>. and Tj be transactions that operate on the same data items A. B. and C. Let ri(4) means that T\ reads A. if'i(.4) means that T\ writes A and so on for Ti and T3. Consider the following schedule SI: 1-2(0. ri(B). v:2(B): r3(B). r3(C). r,(.4). i(4). w3(C), r7(A), r,(B), ),(), v:2(A).
Is t he schedule serializable? [6 + 8 + 6]
?. a) When are two sets of functional dependencies equivalent? There are two sets of FDs for a relation R(A. B. C. D. E) as given below.
i) A - B AB > C D - AC D > E E
ii) A BC D AE Are they equivalent?
b) During its execution, a transaction passes through several states until it finally commits or aborts. Draw a diagram which shows all possible sequence .A Males through which the transaction may pass. Explain why each state transition may occur.
c) List the ACID properties. Explain the usefulness of each. [G + 8 -i- 6) 8. Write short notes on the following.
i) Referential integrity and its importance.
ii) Time-stamp protocol
iii) View and its importance *fv) Security in DBMS
[4 x 5]
|
Attachment: |
| Earning: Approval pending. |
