Structured Question Paper - Database Management Systems (DBMS)
Total Questions: 5
Instructions: Answer all questions. Each question carries equal marks. Include relevant diagrams,
SQL code, and explanations wherever necessary.
---
Question 1: Database Design using ER Diagram
A college wants to maintain a database for students, courses, and instructors. Each student can
enroll in multiple courses. Each course is taught by one instructor, and an instructor can teach
multiple courses.
(a) Identify the entities, attributes, and relationships involved in the system.
(b) Draw an ER diagram for the scenario.
(c) Convert the ER diagram into relational schema with appropriate primary and foreign keys.
(d) Write SQL statements to create the tables based on your relational schema.
---
Question 2: SQL Queries and Commands
Given the following relational schema:
STUDENT(StudentID, Name, Department, GPA)
COURSE(CourseID, CourseName, Credits)
ENROLLMENT(StudentID, CourseID, Grade)
(a) Write SQL queries for the following:
- List all students from the 'Computer Science' department.
- Retrieve names of students who have enrolled in more than two courses.
- Find the average GPA of students grouped by department.
(b) Update the GPA of a student with StudentID = 101 to 3.8.
(c) Delete all enrollments where the grade is NULL.
---
Question 3: Normalization and Functional Dependencies
Consider the following unnormalized relation:
STUDENT_COURSE(StudentID, StudentName, CourseID, CourseName, InstructorName,
InstructorPhone)
(a) Identify all functional dependencies.
(b) Identify the candidate key(s).
(c) Normalize the relation into 1NF, 2NF, and 3NF step by step.
(d) Explain the importance of normalization in database design.
---
Question 4: Transaction Management and Concurrency
(a) Define the ACID properties of a transaction with suitable examples.
(b) Explain the problems that can occur due to concurrent execution of transactions.
(c) What is Two-Phase Locking (2PL)? Describe its phases with an example.
(d) Differentiate between pessimistic and optimistic concurrency control.
---
Question 5: Indexing and Query Optimization
(a) What is indexing in DBMS? Explain its types (Primary, Secondary, Clustered, Non-clustered).
(b) Consider a table EMPLOYEE(EmpID, Name, Salary, DepartmentID).
- How would an index on Salary improve query performance?
- Write a SQL command to create such an index.
(c) What is a query execution plan? How can it be used for query optimization?
(d) Briefly explain the role of the DBMS query optimizer.
---
End of Paper