2nd In-Semester Examination April 2025
B.Tech CSE Semester-IV
Subject: Database Management System (BCO010C)
Time: 75 mins Maximum marks: 40 Instructions:
1. Attempt all the questions.
2. Illustrate your answers with suitable examples and diagrams, wherever
necessary. 3. Write relevant question numbers before writing the answer.
Course Outcomes (CO):
CO2: Design and architecture of relational model, relational algebra and SQL
queries. CO4: Logical representation of internet database.
SECTION A (2*2=04 Marks)
(CO2) What is difference between a table and view.
(CO4)
SECTION B (7*2=14 Marks)
(CO2)
(CO4) Explain serializable scheduling.
Suppose a schedule "S" having two transactions t1, and t2 working simultaneously.
t1 t2
R(x)
W(x)
R(x)
W(x)
R(y)
W(y)
R(y)
W(y)
Check whether the above is view serializable schedule.
Answer:
for the above table the serial schedule is as follows:
Let us call this Schedule S2
T1 T2
R(x)
W(x)
R(y)
W(y)
R(x)
W(x)
R(y)
W(y)
As transaction T1 performed the first operation we wrote all of its operations on data item X
as well as Y first and then all the operations of T2 are written next.
Two schedules are said to be viewed as equivalent if they satisfy the following three
conditions:
Condition 1: Initial Read
Initially if transaction T1 of Schedule S1 reads data item X, then transaction T1 of Schedule
S2 must read data item X initially as well.
In the example above, the Initial read is made by the T1 transaction for both the data items X
and Y in Schedule S1 and in S2 as well as initial read is made by transaction T1 for both.
Thus, the initial read condition is satisfied in S1 and S2 for data items X and Y.
Condition 2: Update Read
If transaction Ti is reading updated data item X by transaction Tj in S1, then in Schedule
S2 Ti should read X which is updated by Tj.
The value of X and Y is updated by transaction T1 in schedule S1, which is read by
transaction T2. In Schedule S2 the X and Y data items are updated by T1 and are read by
T2 as well. Thus, in the above two schedules, the update read condition is also satisfied.
Condition 3: Final Write
If transaction T1 updates data item Y at last in S1, then in S2 also, T1 should perform the
final write operation on Y.
The final write for data items X and Y are made by Transaction T2 in Schedule S1. Also, the
final write is made by T2 in Schedule S2 in Schedule S1 as well as serial schedule S2. Thus,
the condition for the final write is also satisfied.
As all three conditions are satisfied for our schedule S1 and its Serial Schedule S2, they are
view equivalent. Thus, we can say that the given schedule S1 is the view-serializable
schedule and is consistent.
SECTION C (11*2=22 Marks)
(CO2) a) Write an SQL query to create a “Students” table with the following attributes,
specifying appropriate data types : StudentID, Name, Marks. StudentID is a primary
key, Name should not be null and Marks should be greater than 0.
b) Write an SQL query to fetch the name of students whose marks is greater than
50.
Answer
a)
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Marks INT CHECK (Marks > 0));
b)
SELECT Name
FROM Students
WHERE Marks > 50;
(CO4) What are various modes of locks? Explain two-phase locking protocols.
S: R1(X); R2(Z); R1(Z); R3(X); R3(Y); W1(X); W3(Y); R2(Y); W2(Z); W2(Y)
Find conflicting pairs
R3(X) → W1(X)
→ T3 reads X, T1 writes X
→ T3 → T1
W1(X) conflicts with R3(X)
R3(Y) → W3(Y)
→ T3 reads Y, then writes Y
W3(Y) → R2(Y)
→ T3 writes Y, then T2 reads Y
→ T3 → T2
W3(Y) → W2(Y)
→ T3 writes Y, then T2 writes Y
→ T3 → T2
Nodes: T1, T2, T3
Edges:
T3 → T1
T3 → T2
The graph:
T3 → T1
T3 → T2
No cycles, so the schedule is conflict serializable.
Since T3 must come before both T1 and T2, one possible conflict-equivalent serial
schedule is: T3 → T1 → T2
CO2 Write following SQL Queries
student(id, name)
enrolledIn(id, code)
subject(code, lecturer)
a) Names of students enrolled in cs3020:
SELECT name
FROM student JOIN enrolledIn ON s.id = e.id
WHERE code = 'cs3020';
b) Names of students in cs1500 or cs3010
SELECT name
FROM student
JOIN enrolledIn ON s.id = e.id
WHERE code IN ('cs1500', 'cs3010');
c) Who teaches cs1500 or cs3020:
SELECT lecturer
FROM subject
WHERE code IN ('cs1500', 'cs3020');
