Printed Page: 1 of 2
Subject Code: KCS501
0Roll No: 0 0 0 0 0 0 0 0 0 0 0 0 0
BTECH
(SEM V) THEORY EXAMINATION 2020-21
DATABASE MANAGEMENT SYSTEM
Time: 3 Hours Total Marks: 100
Note: 1. Attempt all Sections. If require any missing data; then choose suitably.
SECTION A
1. Attempt all questions in brief. 2 x 10 = 20
Qno. Question Marks CO
a. What is Data Independency in DBMS? 2 CO1
b. Write the difference between DDL and DML. 2 CO1
c. What are different Integrity Constraints? 2 CO2
d. Explain different Features of SQL. 2 CO2
e. What are advantages of normalization? 2 CO3
f. Write different Inference Rule for Functional Dependency? 2 CO3
g. What are ACID properties of Transaction? 2 CO4
h. What are various reasons for transaction failure? 2 CO4
P
i. What are Concurrent Transactions? 2 CO5
0Q
1
j. What is Lock in Transaction Management? 2 CO5
13
29
2.
0E
SECTION B
24
P2
2. Attempt any three of the following: 3 x 10 = 30
5.
_Q
Qno. Question Marks CO
.5
17
a. What is ER Diagram? Explain different Components of an ER 10 CO1
TU
Diagram with thier Notation. Also make an ER Diagram for Employee
|1
Project Management System.
AK
b. What is Relational Algebra? Explain Different Operations of 10 CO2
4
Relational Algebra with Example.
:2
c. (i) What is highest normal form of the Relation R(W,X,Y,Z) with the 10 CO3
13
set F= { WY → XZ, X →Y }
:
09
(ii) Consider a relation R(A,B,C,D,E) with set F= { A→CD,
1
C→B,B→AE} What are the prime attributes of this Relation and
02
Decompose the given relation in 3NF.
2
b-
d. Explain the method of testing the serializability. Consider the schedule 10 CO4
S1 and S2 given below
Fe
3-
S1: R1(A),R2(B),W1(A),W2(B)
|2
S2: R2(B),R1(A),W2(B), W1(A)
Check whether the given schedules are conflict equivalent or not?
e. Explain the Validation Based protocol for concurrency control. 10 CO5
1|Page
AKTU_QP20E290QP | 23-Feb-2021 09:13:24 | 117.55.242.131
Printed Page: 2 of 2
Subject Code: KCS501
0Roll No: 0 0 0 0 0 0 0 0 0 0 0 0 0
SECTION C
3. Attempt any one part of the following:
Qno. Question Marks CO
a. What is Data Abstraction? How the Data Abstraction is achieved in 10 CO1
DBMS?
b. Explain the following with example 10 CO1
(i) Generalization
(ii) Specialization
(iii) Aggregation
4. Attempt any one part of the following:
Qno. Question Marks CO
a. What is Aggregate Function in SQL? Write SQL query for different 10 CO2
Aggregate Function.
b. Explain Procedure in SQL/PL SQL. 10 CO2
P
0Q
5. Attempt any one part of the following:
1
13
29
Qno. Question Marks CO
2.
a. What is Functional Dependency? Explain the procedure of 10 CO3
0E
calculating the Canonical Cover of a given Functional Dependency
24
P2
Set with suitable example.
5.
b. (i) Consider the relation R(a,b,c,d) with Set F={a→c,b→d}. 10 CO3
_Q
.5
Decompose this relation in 2 NF.
17
(ii) Explain the Loss Less Decomposition with example.
TU
|1
AK
6. Attempt any one part of the following:
4
Qno. Question Marks CO
:2
13
a. What is Conflict Serializable Schedule? Check the given Schedule S1 10 CO4
is Conflict Serializable or not?
:
09
S1: R1(X), R2(X),R2(Y),W2(Y),R1(Y),W1(X)
1
b. Explain Deadlock Handling with Suitable Example 10 CO4
2 02
7. Attempt any one part of the following:
b-
Fe
Qno. Question Marks CO
a. Explain Time Stamp Based Concurrency Control technique. 10 CO5
3-
b. Explain Recovery from Concurrent Transaction. 10 CO5
|2
2|Page
AKTU_QP20E290QP | 23-Feb-2021 09:13:24 | 117.55.242.131
Printed Page: 1 of 2
Subject Code: KCS501
0Roll No: 0 0 0 0 0 0 0 0 0 0 0 0 0
B. TECH.
(SEM V) THEORY EXAMINATION 2021-22
DATABASE MANAGEMENT SYSTEM
Time: 3 Hours Total Marks: 100
Note: 1. Attempt all Sections. If require any missing data; then choose suitably.
SECTION A
1. Attempt all questions in brief. 2 x 10 = 20
a. What is the significance of Physical Data Independence?
b. List the four functions of DBA.
c. When a relation set is called a recursive relationship set?
d. What do you mean by currency with respect to database?
e. What is Relational Calculus?
f. What is Equi-Join in database?
g. What is a CLAUSE in terms of SQL?
h. Define the closure of an attribute set.
i. When is a transaction Rolled Back?
j. List the various levels of locking?
SECTION B
2. Attempt any three of the following: 10 x 3 = 30
1
90
13
a. Draw the overall structure of DBMS and explain its various components.
_2
b. Which relational algebra operations require the participating tables to be union-
2.
compatible? Give the Reason in detail.
1P
24
c. What do you understand by transitive dependencies? Explain with an example
2O
5.
any two problems that can arise in the database if transitive dependencies are
.5
present in the database.
P2
17
d. List ACID properties of transaction. Explain the usefulness of each. What is
Q
the importance of log?
|1
e. What do you mean by time stamping protocol for concurrency controlling?
38
Discuss multi version scheme of concurrency control.
9:
SECTION C
:5
3. Attempt any one part of the following: 10 x 1 = 10
08
(a) What are the different types of Data Models in DBMS? Explain them.
(b) State the procedural DML and nonprocedural DML with their differences.
2
02
4. Attempt any one part of the following: 10 x 1 = 10
-2
(a) Consider the following schema for institute library:
n
Student (RollNo, Name, Father_ Name, Branch)
Ja
Book (ISBN, Title, Author, Publisher)
3-
Issue (RollNo, ISBN, Date-of –Issue)
|0
Write the following queries in SQL and relational algebra:
I. List roll number and name of all students of the branch ‘CSE’.
II. Find the name of student who has issued a book published by ‘ABC’
publisher.
III. List title of all books and their authors issued to a student ‘RAM’.
IV. List title of all books issued on or before December 1, 2020.
V. List all books published by publisher ‘ABC’.
(b) What do you mean by trigger? Explain it by a suitable example.
QP22O1P_290 | 03-Jan-2022 08:59:38 | 117.55.242.131
Printed Page: 2 of 2
Subject Code: KCS501
0Roll No: 0 0 0 0 0 0 0 0 0 0 0 0 0
5. Attempt any one part of the following: 10 x 1 = 10
(a) Describe Armstrong’s axioms in detail. What is the role of these rules in
database development process?
(b) Describe the term MVD in the context of DBMS by giving an example. Discuss
4NF and 5NF also.
6. Attempt any one part of the following: 10 x 1 = 10
(a) Describe serializable schedule. Discuss conflict serializability with suitable
example.
(b) Discuss the procedure of deadlock detection and recovery in transaction?
7. Attempt any one part of the following: 10 x 1 = 10
(a) Given a schedule S for transactions T1 and T2 with set of read and write
operations,
S: R1(X) R2(X) R2(Y) W2(Y) R1(Y) W1(X).
Identify, whether given schedule is equivalent to serial schedule or not?
(b) Discuss 2 phase commit (2PC) protocol and time stamp based protocol with
suitable example. How the validation based protocols differ from 2PC?
1
90
13
_2
2.
1P
24
2O
5.
.5
P2
17
Q
|1
38
9:
:5
08
2
02
n -2
Ja
3-
|0
QP22O1P_290 | 03-Jan-2022 08:59:38 | 117.55.242.131
Printed Pages: 02 Sub Code:KCS-501
Paper Id: 231456 Roll No.
B.TECH.
(SEM V) THEORY EXAMINATION 2022-23
DATABASE MANAGEMENT SYSTEM
Time: 3 Hours Total Marks: 100
Note: Attempt all Sections. If you require any missing data, then choose suitably.
SECTION A
1. Attempt all questions in brief. 2x10 = 20
(a) List any four disadvantages of file system approach over database approach.
(b) Differentiate between physical and logical data independence.
(c) What is the difference between DROP and DELETE command?
(d) What are different Integrity Constraints?
(e) List all prime and non-prime attributes In Relation R(A,B,C,D,E) with FD set
F = {AB→C, B→E, C→D}.
(f) Explain MVD with the help of suitable example.
(g) Discuss Consistency and Isolation property of a transaction.
(h) Draw a state diagram and discuss the typical states that a transaction goesthrough
2
90
13
during execution.
_2
(i) Discuss Conservative 2PL and Strict 2PL.
2.
(j) Describe how view serializability is related to conflict serializability.
P1
24
5.
3D
SECTION B
.5
P2
2. Attempt any three of the following: 10x3 = 30
17
Q
(a) A database is being constructed to keep track of the teams and games of a sport
|1
league. A team has a number of players, not all of whom participate in each
game. It is desired to keep track of players participating in each game for each
5
:2
team, the positions they play in that game and the result of the game.
23
(i) Design an E-R schema diagram for this application.
(ii) Map the E-R diagram into relational model
:
13
(b) What are Joins? Discuss all types of Joins with the help of suitable examples.
(c) A set of FDs for the relation R{A, B, C, D, E, F} is AB →C, C → A, BC →
3
D,ACD → B, BE → C, EC → FA, CF → BD, D→ E. Find a minimum cover
02
forth is set of FDs
-2
(d) What is a schedule? Define the concepts of recoverable, cascade less and strict
schedules, and compare them in terms of their recoverability.
01
(e) Discuss the immediate update recovery technique in both single-user and
2-
multiuser environments. What are the advantages and disadvantages of
|1
immediate update?
SECTION C
3. Attempt any one part of the following: 10x1 = 10
(a) Describe the three-schema architecture. Why do we need mappings between
schema levels? How do different schema definition languages support this
architecture?
(b) What are the different types of Data Models in DBMS? Explain them.
QP23DP1_290 | 12-01-2023 13:23:25 | 117.55.242.132
4. Attempt any one part of the following: 10 x1 = 10
(a) Consider the following schema for institute library:
Student (RollNo, Name, Father_ Name, Branch)
Book (ISBN, Title, Author, Publisher)
Issue (RollNo, ISBN, Date-of –Issue)
Write the following queries in SQL and relational algebra:
(i) List roll number and name of all students of the branch ‘CSE’.
(ii) Find the name of student who has issued a book published by ‘ABC’ publisher.
(iii) List title of all books and their authors issued to a student ‘RAM’.
(iv) List title of all books issued on or before December 1, 2020.
(v) List all books published by publisher ‘ABC’
(b) Explain different types of Triggers in SQL/PL SQL.
5. Attempt any one part of the following: 10x1 = 10
(a) Given the following set of FDs on schema R (V,W,X,Y,Z)
{Z→V, W→Y, XY→Z, V→WX}State whether the following decomposition are
loss-less-join decompositions or not.
(i) R1=(V,W,X) , R2=(V,Y,Z)
(ii) R1=(V,W,X), R2=(X,Y,Z)
(b) Consider the universal relation R = {A, B, C, D, E, F, G, H, I, J} and the set of
functional dependencies F = { {A, B}→{C}, {A}→{D, E}, {B}→{F},
{F}→{G,H}, {D}→{I, J} }. What is the key for R? Decompose R into 2NF and
then3NF relations.
2
90
13
_2
2.
6. Attempt any one part of the following: 10x1 = 10
P1
24
(a) Consider schedules S1, S2, and S3 below. Determine whether each schedule is strict,
cascade less, recoverable, or non recoverable. (Determine the strictest recoverability
5.
3D
condition that each schedule satisfies.)
.5
P2
S1: r1 (X); r2 (Z); r1 (Z); r3 (X); r3 (Y); w1 (X); c1; w3 (Y); c3; r2 (Y); w2 (Z); w2
(Y); c2; 17
Q
|1
S2: r1 (X); r2 (Z); r1 (Z); r3 (X); r3 (Y); w1 (X); w3 (Y); r2 (Y); w2 (Z); w2 (Y);
c1;c2; c3;
5
S3: r1 (X); r2 (Z); r3 (X); r1 (Z); r2 (Y); r3 (Y); w1 (X); c1; w2 (Z); w3 (Y); w2
:2
(Y);c3; c2;
23
(b) Consider the three transactions T1, T2, and T3, and the schedules S1 and S2given
below. State whether each schedule is serializable or not. If a schedule is serializable,
:
13
write down the equivalent serial schedule(s).
T1: r1 (X); r1 (Z); w1 (X);
3
T2: r2 (Z); r2 (Y); w2 (Z); w2 (Y);
02
T3: r3 (X); r3 (Y); w3 (Y);
-2
S1: r1 (X); r2 (Z); r1 (Z); r3 (X); r3 (Y); w1 (X); w3 (Y); r2 (Y); w2 (Z); w2 (Y);
01
S2: r1 (X); r2 (Z); r3 (X); r1 (Z); r2 (Y); r3 (Y); w1 (X); w2 (Z); w3 (Y); w2 (Y);
2-
7. Attempt any one part of the following: 10x1 = 10
|1
(a) Discuss the timestamp ordering protocol for concurrency control. How does strict
timestamp ordering differ from basic timestamp ordering?
(b) How do optimistic concurrency control techniques differ from other concurrency
control techniques? Why they are also called validation or certification techniques?
Discuss the typical phases of an optimistic concurrency control method.
QP23DP1_290 | 12-01-2023 13:23:25 | 117.55.242.132
