utkarsh_40m_paper.md

DBMS VJTI Paper - I

MID SEMESTER EXAMINATION

Course Name: Database Management Systems 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 Answer the following questions (10 Marks)

a) Explain the Three-Schema Architecture in a DBMS. How does this architecture help in achieving Logical and Physical Data Independence? (5M)

b) Define the concepts of Entity Integrity and Referential Integrity constraints in the relational model. Explain with a suitable example demonstrating primary and foreign keys. (5M)

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

a) Consider the University Management System ER model provided in the diagram below. Convert this ER model into the most appropriate Relational Model.

1. Show the step-by-step reduction of the ER schema to tables.
2. Clearly identify the Primary Keys, Foreign Keys, and how you handled composite, multivalued, and derived attributes.
3. Prepare the final Schema Diagram for the same. (10M)

b) Explain the Extended E-R features of Generalization and Specialization. Give an example demonstrating the difference between total-partial and overlap-disjoint constraints. (5M)

Q.3 SQL Queries and Relational Design (15 Marks)

a) Consider the following relational database schema:

• Employee (Emp_ID, EName, Salary, Dept_ID)
• Department (Dept_ID, DName, Location) (Note: Primary keys are underlined)

Write SQL commands for the following operations: (10M)

1. DDL: Write the CREATE TABLE statement for the Employee table, strictly enforcing the Primary Key and establishing Dept_ID as a Foreign Key referencing the Department table.
2. Nested Query: Display the names of employees who earn a salary strictly greater than the average salary of all employees in the company.
3. Joins & Aggregate: Find the total number of employees working in the 'Research' department.
4. Views: Create a View named High_Earners that displays the EName and Salary of all employees earning more than Rs. 85,000.
5. Update: Using a single UPDATE command, increase the salary of all employees by 10% only if they work in the 'Mumbai' location.

b) What are the common pitfalls in relational database design? Explain the concept of Functional Dependencies and Lossless Decomposition with a brief example to show how Normal Forms (up to 3NF) address these pitfalls. (5M)

---

End of Paper

Here is the complete Answer Key and Marking Scheme for the 40-mark Database Management Systems Mid-Semester paper.

---

ANSWER KEY & MARKING SCHEME

Course Name: Database Management Systems | Max Marks: 40

---

Q.1 Answer the following questions (10 Marks)

a) Three-Schema Architecture & Data Independence (5M)

• Three-Schema Architecture (2.5 Marks):
  • Internal Level: Describes the physical storage structure of the database (how data is actually stored).
  • Conceptual Level: Describes the structure of the whole database for a community of users, hiding physical storage details (Entities, datatypes, relationships).
  • External Level: Describes the part of the database that a particular user group is interested in (Views), hiding the rest of the database.
• Data Independence (2.5 Marks):
  • Logical Data Independence: The capacity to change the conceptual schema (e.g., adding a new table or column) without having to change external schemas or application programs.
  • Physical Data Independence: The capacity to change the internal schema (e.g., changing file organization or indexes) without having to change the conceptual schema.

b) Entity Integrity and Referential Integrity (5M)

• Entity Integrity (1.5 Marks): States that no primary key value can be NULL. This ensures that every tuple in a relation can be uniquely identified.
• Referential Integrity (1.5 Marks): States that a foreign key value must either match an existing primary key value in the referenced relation or be exactly NULL. This ensures consistency between cross-referenced tables.
• Example (2 Marks):
  • Student (RollNo PK, Name) -> RollNo cannot be NULL (Entity Integrity).
  • Enrollment (EnrollID PK, RollNo FK) -> The RollNo in Enrollment MUST exist in the Student table or be NULL (Referential Integrity).

---

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

a) ER to Relational Model Conversion (University Database) (10M)

• Step 1: Strong Entities and Simple/Composite Attributes (3 Marks)
  • Flatten composite attributes (Name into First, Last; Address into City, State).
  • Derived attributes (Age) are omitted from the schema as they can be calculated dynamically from DOB.
  • Professor (Prof_Id PK, First_Name, Last_Name)
  • Course (Course_Code PK, Title, Credits)
  • Student (Stud_Id PK, DOB, City, State)
• Step 2: Multivalued Attributes (2 Marks)
  • Create a separate table. The PK is a combination of the entity's PK and the multivalued attribute itself.
  • Prof_Expertise (Prof_Id FK, Expertise) -> Composite PK: (Prof_Id, Expertise)
• Step 3: 1:N Relationship ("teaches") (2 Marks)
  • Post the Primary Key of the "1" side (Professor) as a Foreign Key to the "N" side (Course).
  • Course (Course_Code PK, Title, Credits, Prof_Id FK)
• Step 4: M:N Relationship ("enrolled_in") (2 Marks)
  • Create a new relation containing the primary keys of both participating entities as foreign keys, combining them to form a composite primary key.
  • Enrolled_in (Course_Code FK, Stud_Id FK) -> Composite PK: (Course_Code, Stud_Id)
• Final Schema Diagram / List (1 Mark):
  1. Professor(Prof_Id, First_Name, Last_Name)
  2. Prof_Expertise(Prof_Id, Expertise) — Prof_Id references Professor
  3. Student(Stud_Id, DOB, City, State)
  4. Course(Course_Code, Title, Credits, Prof_Id) — Prof_Id references Professor
  5. Enrolled_in(Course_Code, Stud_Id) — Course_Code references Course, Stud_Id references Student

b) Extended ER Features & Constraints (5M)

• Generalization/Specialization (2 Marks):
  • Generalization: Bottom-up design process combining lower-level entities to form a higher-level entity (e.g., Savings_Account and Checking_Account generalized to Account).
  • Specialization: Top-down process creating subgroups within an entity set.
• Constraints (3 Marks):
  • Total vs. Partial: Total means every entity in the superclass must belong to at least one subclass. Partial means an entity may not belong to any subclass.
  • Disjoint vs. Overlap: Disjoint means an entity can belong to at most one subclass. Overlap means an entity can belong to multiple subclasses simultaneously.
  • Example: Employee generalizing into Faculty and Staff is Disjoint (can't be both) and Total (every employee must be one of the two).

---

Q.3 SQL Queries and Relational Design (15 Marks)

a) SQL Queries (10 Marks - 2 Marks per correct query)
(Deduct 0.5 marks for minor syntax errors like missing semicolons or typos; deduct 1-2 marks for logical errors)

1. DDL (Create Table with PK & FK):

CREATE TABLE Employee (
    Emp_ID INT PRIMARY KEY,
    EName VARCHAR(50),
    Salary DECIMAL(10,2),
    Dept_ID INT,
    FOREIGN KEY (Dept_ID) REFERENCES Department(Dept_ID)
);

2. Nested Query (Salary > Average):

SELECT EName 
FROM Employee 
WHERE Salary > (SELECT AVG(Salary) FROM Employee);

3. Joins & Aggregate (Count in 'Research'):

SELECT COUNT(E.Emp_ID) 
FROM Employee E JOIN Department D ON E.Dept_ID = D.Dept_ID 
WHERE D.DName = 'Research';

(Accept subquery logic: SELECT COUNT(*) FROM Employee WHERE Dept_ID = (SELECT Dept_ID FROM Department WHERE DName = 'Research');)

4. Views (High_Earners):

CREATE VIEW High_Earners AS 
SELECT EName, Salary 
FROM Employee 
WHERE Salary > 85000;

5. Update (Increase salary in 'Mumbai'):

UPDATE Employee 
SET Salary = Salary + (Salary * 0.10) 
WHERE Dept_ID IN (SELECT Dept_ID FROM Department WHERE Location = 'Mumbai');

b) Pitfalls, Functional Dependencies, and Normalization (5M)

• Pitfalls (1.5 Marks): Bad relational design leads to redundancy, spurious tuples, and anomalies (Insertion, Deletion, and Modification anomalies).
• Functional Dependency & Lossless Decomposition (1.5 Marks):
  • FD (X → Y): Attributes Y are functionally dependent on X if the value of X uniquely determines the value of Y.
  • Lossless Decomposition: Breaking a table into two or more tables such that joining them back (using Natural Join) yields the exact original data without losing records or creating fake (spurious) records.
• Normal Forms (2 Marks):
  • 1NF: Addresses pitfalls by ensuring all domain values are atomic (no multivalued attributes).
  • 2NF: Addresses pitfalls by removing partial dependencies (non-key attributes must depend on the entire primary key).
  • 3NF: Addresses pitfalls by removing transitive dependencies (non-key attributes must not depend on other non-key attributes).