GTU Database Management System Exam Paper Mar 2010
GUJARAT TECHNOLOGICAL UNIVERSITY
B.E. Sem-III Remedial Examination March 2010
Subject code: 130703 Subject Name: Data Base Management system
Total Marks: 70
- Attempt all questions.
- Make suitable assumptions wherever necessary.
- Figures to the right indicate full marks.
Q.1 (a) Consider following schema and write SQL for given statements. 08
- student( rollno, name, branch)
- exam(rollno, subject_code, obtained_marks ,
- papers(paper_code, paper_satter_name,
( i ) Display name of student who got first class in subject ‘130703’.
(ii) Display name of all student with their total mark.
(iii) Display list number of student in each university.
(iv) Display list of student who has not given any exam.
(b) Explain ACID properties of Transaction with suitable example. 06
Q.2 (a) Draw E-R diagram for Hospital management system and covert into set of table 07 schema.
(b) Describe various disadvantages of file system compare to Data base management 07 system.
(b) With example explain various mapping cardinalities and total participation. 07
Q.3 (a) Consider following schema and represent given statements in relation algebra 06 form.
* Account(branch_name, acc_no, balance)
( i ) Find out list of customer who have account at ‘abc’ branch.
( ii ) Find out all customer who have account in
‘Ahmedabad’ city and balance is greater than 10,000.
(iii) find out list of all branch name with their maximum balance.
(b) Normalize (decompose) following relation into lower to higher normal 08
|form.(From 1NF to 4 NF|
|PLANT||MANAGER||MACHINE||SUPPLIER NAME||SUPPLIER CITY|
|Plant-A||Ravi||LathBoiler||Jay industry Abb aplliance||AhmedabadSurat|
|Raj machinery Daksh industry Jay industry||VadodaraRajkot
Q.3 (a) What is canonical cover? Consider following set F of functional dependencies on schema R(A,B,C) and compute canonical cover for F.
A -> BC B -> C A -> B AB -> C
(b) Consider wholesaler of booh schema.
B ook(B ook_i d, b ook_titl e, publisher,b ook_pri ce)
( i ) Implement procedure which print details of books whose price is more than average price.(Use cursor).
(ii) Write a trigger such that if record is deleted from book table, insert old record in book_backup table.
Q.4 (a) Explain various steps involved in Query evaluation. 07
(b) Explain conflict serializability with example. 07
Q.4 (a) Explain transformation of relational expression into equivalent relational 07 expression .
(b) Explain view serializability with example. 07
Q.5 (a) Consider schedule S with transaction T1 and T2. T1 transfer Rs. 150 from account 05 A to C and T2 adds Rs. 50 into account A. Prepare concurrent schedule with two phase locking protocol.
(b) Explain various deadlock prevention methods. 05
(c) Write short note on data encryption. 04
(a) Explain immediate database modification log based recovery method. Also 07 explain role of check point in log base.
(b) Explain working of 2-phase commit protocol. 07