c 0200csT204122301

Reg No.: Name:

ApJ ABDUL KALAM TECHNOLOGTCAL UMVERSITY ,
,:

B.Tech Degree 54 (S, FE) / 54 (PT) (S, FE) / 54 (WP) (S) Examination December2024 (2019 Scheme)

Course Code: CST 204

Course Name: DATABASE MANAGEMENT SYSTEMS
Max. Marks: 100 Duration: 3 Hours

PART A
(Answer all questions; each qhestion carries 3 marks) Marks
I Write a short note on (i) Multiple views on data J

(ii) Self-describing nature of data

2 Define the term "Data models" a
J

3 Discuss the significance ofthe DROP statement in DDL. When and why would you use it? J

4 Discuss the differences between an equi-join and a natural join. a

J

5 Discuss the role of triggers in SQL databases 3

6 Write the difference between correlated and non-correlated nested queries J

7 Explain the concept of functional dependency in a relational database. Provide an example J

a

to illustrate.
t P:{A)B, B)C, AC)D, AD)E} Find A+ a
J

9 Define concunency control in the context of transaction processing. Why is it important? J

l0 List and explain the desirable properties of transactions. a

J

PART B
(Answer onefull questionfrom each module, eoch question carries 14 morks)

Module -l
l'l a) Each book has a unique ISBN, title, author, publication date, publisher, and genre and 14

due date. A book can be borrowed by multiple members, but only one at a time. Each
member has a unique membership ID, name, address, phone number, and email
address. A member can borrow multiple books at a time, each with a loan start date and

due date. A book can be borrowed by multiple members, but only one at a time. If a

book is returned late, a fine is generated with an amount and payment date. A fine can
be associated with one loan. Design an ER model for this library database, identifring
the mairt entities, their attributes, and the relationships between them. Be sure to include

cardinality and participation constraints and weak entities if any.

Page 1 of 3
P
 0200csr20412230r

12 a) Explain the functions of Database Administrator 4

b) Describe the three-schema architecture in a Database Management System. Explain l0

Data independence

Module -2
l3 Convert the following ER diagram to a relational model. t4

14 a) Sailors(sid,sname,rating,age) , Boats(bid, name,color), Reserves(sid,bid,day)

Write relational algebra query for the following
(D Find the name of sailors who havedting atleast 7
(iD Find the name of sailors who have reserved boat with id I l0
(iiD Find the name of sailors who have reserved red and blue boats
b) Differcntiate Primary key, candidate key and superkey with suitable example
Module -3
15 a) Employees( employee-id, employee-name, department_id, salary, hire_date) t4
Departments( departmentjd, deparfnent_name, location)
Projec{s ( project_id, project_name, start_date, end_date)
Assignrnents( assignmentjd, employelid, project_id, assignment_date)

Page 2 of 3
e
\
 0200csT204122301

Write an SQL query expression for the following

l.Retrieve the names of employees along with their department names
2. Find the total salary expenditure per department
3. List employees who are currently assigned to a project
4. Find the average salary of employees in projects that started after 22/tL/2O22
16 a) Consider a file with a fixed block size of 256 bytes. The records in the file have a fixed 6

size of 40 bytes each. Calculate the blocking factor for this file.

b) Differentiate 8
(i) Single level and Multi level indexing
(ii) B Trees and B+ Trees
Module -4
17 a) Given a relation R( P, Q, & S, T, U, V, W, X, Y) and Functional Dependency set FD l0
= { PQ - R, P --+ ST, Q * U, U -' VW, and S -* XY}, determine whetherthe given
R is in 3NF? If not convert it into 3 NF.
b) What are the different anomalies that can occur in a poorly designed database? Provide 4
examples for each.
18 Consider a relation R with six athibutes (A, B, C, D, E, F) and the following set of 14
fi ve fu nctional dependencies :

l. A,B+C
2. C+D
3. D,E+F
4. B+E
, 5. F+A
The relation R is decomposed into Rl(A,B,C),R2(C,D),R3(D,E,F),R4(B,E), and R5
(F,A) . Check whether the given decomposition is lossless or lossy?
Module -5
19 a) What is log-based recovery, and how does it ensune database consistency after a 4
failure? ;
b) Explain the two phase locking protocol in detail l0
20 a) Check Whether the following schedule is conflict serializable or not 8

(i) fu(A), Wz(A), Rz(B), Wz(B), Rr(B), Wr(B), Rr(A), Wr(A)

(ii) R1(x)R2(x)RdY) wz(Y) Rr(Y) wr(x)

b) Rl(x), R2(x), Rl(z), R3(x), R3(y), Wl(x), Cl, W3Cr), C3, R2(y),'V,12(z), W2(y), 6
C2:
Check Whether the given schedule is strict? Justifr your answer

!trt*

Page 3 of 3
e
I