Firefox https://amizone.net/AdminAmizone/WebForms/Academics/Moderator/P...

CSE201
Enroll. No. -_____________________
[ET]

END SEMESTER EXAMINATIONS APRIL-MAY, 2025

Time :3.00 Hrs Maximum Marks :60

Note: Attempt questions from all sections as directed.

NA

Section - A : Attempt any Four questions out of Five . Each question carries 06 marks. [24 Marks]

Q1.
ER modeling involves various types of relationships. Explain the differences between one-to-one, one-
to-many, and many-to-many relationships and how they are mapped to a relational schema.

(6)
Q2.
What are the benefits of using triggers in relational databases? Create an SQL trigger that maintains
data consistency in an employee-management system.

(6)
Q3.
Given a relation R(A, B, C, D, E, F, G) with the following functional dependencies:

F= {A → BC, CD → E, B → D, E → F, F → G}, Find the candidate keys and decompose the relation
into 3NF.

(6)
Q4.
Describe two-phase locking (2PL). How does it prevent concurrency issues? Give an example scenario
where 2PL is beneficial.

(6)
Q5.
Describe and compare the nested loop join, sort-merge join, and hash join algorithms. Which one is
preferable for a large dataset with frequent updates?

(6)
Section – B : Attempt any two questions out of three. Each question carries 10marks. [20 Marks]

1 of 3 25/4/2025, 9:51 PM
Firefox https://amizone.net/AdminAmizone/WebForms/Academics/Moderator/P...

Q6.
Consider the following two relations:

Employee(EID, Name, DeptID, Salary)

Department(DeptID, DeptName, Location)

Write the following queries in Relational Algebra and SQL:

(a)
Find the names of employees who work in the HR department.

(3)
(b)
Retrieve the names and salaries of employees whose salary is greater than 55,000.

(3)
(c)
Find the natural join of Employee and Department relations.

(4)
Q7. (a)
(5)
Explain the ACID properties in the context of relational databases. Provide real-world scenarios where
violations of these properties could lead to database inconsistencies.

(b)
Consider a relation R(A, B, C, D, E) with functional dependencies:

1. A → B
2. B → C
3. A → D
4. D → E

a) Find the candidate keys of R.

(5)
Q8. (a)
(5)
Which of the following schedules is conflict serializable ? For each serializable schedule, determine the
equivalent serial schedule.

(i) r1 (X); r3 (X); w1 (X); r2 (X); w3 (X)

(ii) r1 (X); r3 (X); w3 (X); w1 (X); r2 (X)

(b)

2 of 3 25/4/2025, 9:51 PM
Firefox https://amizone.net/AdminAmizone/WebForms/Academics/Moderator/P...

(5)
Define a cascade-less schedule and a recoverable schedule? If a schedule is not cascade-less, does it
mean ‘no recovery is feasible’? Justify your answer.

Section - C : Compulsory question [16 Marks]

Q9. (a)
(6)
What challenges arise when implementing schema evolution in databases? Discuss strategies for
managing schema changes in a large-scale enterprise database.

(b)
A company has the following Scenario: There are a set of salespersons. Some of them manage other
salespersons. However, a salesperson cannot have more than one manager. A Salesperson can be an
agent for many customers. A customer is managed by exactly one salesperson. A customer camn place
any number of orders. An order can be placed by exactly one customer. Each order lists one or more
items. An item may be listed in many orders. An item is assembled from different parts and parts can be
common for many items. One or more employees assemble an item from parts. A supplier can supply
different parts in certain quantities. A part may be supplied by different suppliers.

a. Identify and list entities, suitable attributes, primary keys, and relationships to represent the scenarios

b. Draw an ER diagram to model the scenario.

(10)

3 of 3 25/4/2025, 9:51 PM