Printed Page: 1 of 3
Subject Code: BCS501
0Roll No: 0 0 0 0 0 0 0 0 0 0 0 0 0
BTECH
(SEM V) THEORY EXAMINATION 2024-25
DATABASE MANAGEMENT SYSTEM
TIME: 3 HRS M.MARKS: 70
Note: Attempt all Sections. In case of any missing data; choose suitably.
SECTION A
1. Attempt all questions in brief. 2 x 07 = 14
Q no. Question CO Leve
l
a. Define candidate key and super key with example. 1 K1
b. Differentiate TRUNCATE and DELETE command 2 K2
c. Define triggers and its types. 2 K1
d. Analyze and find the FDS in the following relation 3 K4
X Y Z
1 4 2
2 5 3
1 4 2
2
3 2 2
13
e.
90
List all prime and non-prime attributes In Relation R(A,B,C,D,E) with FD 3 K1
2.
set F = {AB→C, B→E, C→D}.
_2
f. Explain properties of Transaction. 4 K2
24
P1
g. Define multiple granuality. 5 K1
5.
5D
.5
17
SECTION B
P2
2. Attempt any three of the following: |1 07 x 3 = 07
Q
Q no. Question CO Leve
AM
l
a. Explain the architecture of DBMS in terms of its components with a 1 K2
proper diagram.
0
:3
b. Explain Joins? Discuss all types of Joins with the help of proper example 2 K2
05
of each.
c. Consider the relation R (P, Q, S, T, X, Y, Z, W) with the following 3 K4
9:
functional dependencies.
25
PQ → X; P → YX; Q → Y; Y → ZW
Consider the decomposition of the relation R into the constituent
20
relations according to the following two decomposition schemes.
n-
D1 : R = [(P, Q, S, T); (P, T, X); (Q, Y); (Y, Z, W)]
D2 : R = [(P, Q, S); (T, X); (Q, Y); (Y, Z, W)]
Ja
Identify whether it is and lossy decomposition and justify your answer.
9-
d. Determine different types of failures in case of transactions and how it 4 K3
|0
can be recovered based on log file? Explain with suitable example.
e. Discuss Concurrency control. Why it is needed in DBMS? Also explain 5 K2
timestamp based ordering in Concurrency control.
1|Page
QP25DP1_290 | 09-Jan-2025 9:05:30 AM | 117.55.242.132
Printed Page: 2 of 3
Subject Code: BCS501
0Roll No: 0 0 0 0 0 0 0 0 0 0 0 0 0
BTECH
(SEM V) THEORY EXAMINATION 2024-25
DATABASE MANAGEMENT SYSTEM
TIME: 3 HRS M.MARKS: 70
SECTION C
3. Attempt any one part of the following: 07 x 1 = 07
Q no. Question CO Level
a. Illustrate the concept of data independence and its type with example. 1 K4
Why is it required?
b. Explain the process of reducing an ER diagram to tables. Provide a 1 K4
simple example.
Given the following ER diagram representing a university database:
Entities:
Student: Attributes - StudentID (Primary Key), Name, Email
Course: Attributes - CourseID (Primary Key), CourseName, Credits
Enrollment: Attributes - EnrollmentID (Primary Key), DateEnrolled
Relationships:
Enrolls: A many-to-many relationship between Student and Course,
2
represented by the Enrollment entity.
13
90
a) Reduce the ER diagram to relational tables by listing the tables and
2.
their corresponding attributes.
_2
24
b) Identify primary keys and foreign keys in the tables you created.
P1
5.
5D
4. Attempt any one part of the following: 07 x 1 = 07
.5
17
Q no. Question CO Level
P2
a. Employee (ename, street, city) |1 2 K3
Q
Worksfor (ename, company_name, salary)
Company (Company_name, city)
AM
Construct the SQL statements for the following
(i) Create the above given tables with suitable data types and size.
0
(ii) Find the name of employees who live in the same city where they
:3
work.
05
(iii) Find the name of employees who have salary more than Rs. 50000.
9:
(iv) Find the name of employees who don’t work in “tcs” company.
(v) Find all employees whose name has second letter ‘A’.
25
(vi) Find the employee’s name having second highest salary.
20
b. Consider the following three relation schema S, P and SP in which S# is 2 K3
supplier code, P# part number and qty is quantity and others carry their
n-
respective meanings:
Ja
S(S#, SNAME, SCITY, TURNOVER)
P(P#,WEIGHT, COLOR, COST, SELLING PRICE)
9-
SP(S#, P#, QTY)
|0
Construct the appropriate RA statement for the following queries.
i Get all details of supplier who operate from CALCUTTA with
TURNOVER=80.
ii Get part numbers weighting between 25 and 55.
iii Get the part# which has cost greater than selling price.
iv Get the part numbers whose color is red or black.
v Get the SNAME where S# is 101.
2|Page
QP25DP1_290 | 09-Jan-2025 9:05:30 AM | 117.55.242.132
Printed Page: 3 of 3
Subject Code: BCS501
0Roll No: 0 0 0 0 0 0 0 0 0 0 0 0 0
BTECH
(SEM V) THEORY EXAMINATION 2024-25
DATABASE MANAGEMENT SYSTEM
TIME: 3 HRS M.MARKS: 70
5. Attempt any one part of the following: 07 x 1 = 07
Q no. Question CO Level
a. Consider the following relational schemes for a library database: Book 3 K4
(Title, Author, Catalog_no, Publisher, Year, Price), Collection (Title,
Author, Catalog_no) with in the following functional dependencies:
I. Title Author --> Catalog_no
II. Catalog_no --> Title, Author, Publisher, Year
III. Publisher Title Year --> Price
Analyze and find the highest normal form of the relation Book and
Collection.
b. Consider the universal relation R = {A, B, C, D, E, F, G, H, I, J} and the 3 K4
set of functional dependencies F = { {A, B}→{C}, {A}→{D, E},
{B}→{F}, {F}→{G,H}, {D}→{I, J} }. Identify the key for R? Also
2
define MVD with example.
13
90
2.
_2
6. Attempt any one part of the following: 07 x 1 = 07
24
P1
Q no. Question CO Level
5.
a. Illustrate Conflict Serializable Schedule. Check the given Schedule S1 4 K4
5D
.5
is Conflict Serializable or not? Also check is it view Serializable?
17
P2
S1: R1(X), R2(X),R2(Y),W2(Y),R1(Y),W1(X)
b. |1
Explain schedule and transaction. Define the concepts of recoverable, 4 K4
Q
cascade less, and strict schedules, and compare them in terms of their
AM
recoverability.
0
7. Attempt any one part of the following: 07 x 1 = 07
:3
05
Q no. Question CO Leve
l
9:
a. Explore the following 5 K4
(i) Multi version protocol
25
(ii) Validation based protocol
20
b. Explain deadlock. What are the necessary conditions for it? How it can be 5 K4
detected and recovered?
n-
Ja
9-
|0
3|Page
QP25DP1_290 | 09-Jan-2025 9:05:30 AM | 117.55.242.132
