utkarsh_40m_paper_2.md

DBMS VJTI Paper - II

VEERMATA JIJABAI TECHNOLOGICAL INSTITUTE

Mid Semester Examination (MSE) – March 2026 Course Name: Database Management Systems | Semester: Sem-IV Time Allowed: 1.5 Hours | Max Marks: 40 Instructions:

1. All questions are compulsory.
2. Figures to the right indicate full marks.
3. Assume suitable valid data wherever required.

---

Q.1 Introduction and Relational Algebra (10 Marks)

a) Compare the Database Management System (DBMS) approach with the Traditional File Processing System. Explain at least five major advantages of using a DBMS. (5M)

b) Explain the following fundamental operations in Relational Algebra with a brief mathematical notation and an example for each: (5M)

1. Select ($\sigma$)
2. Project ($\pi$)
3. Cartesian Product ($\times$)
4. Set Difference ($-$)

Q.2 Entity-Relationship Model and Mapping (15 Marks)

a) Consider the E-Commerce Retail ER model provided in the diagram below. Convert this ER model into the most appropriate Relational Schema.

1. Show the step-by-step reduction of the ER schema to tables.
2. Clearly identify the Primary Keys, Foreign Keys, and explain how you handled the multivalued, composite, and derived attributes, as well as the descriptive attribute on the relationship. (10M)

b) What is a Weak Entity Set? How is it structurally represented in an ER diagram, and how does its mapping to a relational table differ from a strong entity? Explain with a suitable example (e.g., Employee and Dependents). (5M)

Q.3 SQL Queries and Normalization (15 Marks)

a) Consider the following Library Database Schema:

• Member (Member_ID, Name, Age, Member_Type)
• Book (Book_ID, Title, Author, Publisher)
• Issue_Record (Member_ID, Book_ID, Issue_Date, Return_Date) (Note: Primary keys are underlined. Member_ID and Book_ID in Issue_Record are Foreign Keys.)

Write SQL queries for the following requirements: (10M)

1. DDL: Write the CREATE TABLE statement for Issue_Record, including the composite Primary Key and both Foreign Key constraints.
2. Aggregate: Display the name of each publisher and the total number of books published by them.
3. Joins: Print the Names of all members who have borrowed the book titled "Database System Concepts".
4. Update: Change the Member_Type to 'Senior Citizen' for all members whose Age is greater than 60.
5. Views: Create a view named Available_Books that contains the Book_ID and Title of books published by "McGraw-Hill".

b) (b) Define 1NF, 2NF, and 3NF

Consider a relation R(A, B, C, D) with the following Functional Dependencies:

• A -> B
• B -> C
• C -> D

Determine the Candidate Key of R and state the highest Normal Form of this relation. Justify your answer. (5 M)

---

End of Paper