Database Management System (1333204) - Summer 2025 Solution

Question 1(a) [3 marks]

#

Write a short note: Data Dictionary

Answer: A Data Dictionary is a centralized repository that stores metadata about database structure, elements, and relationships.

Table: Data Dictionary Components

Key Features:

• Metadata Storage: Contains information about data structure
• Data Integrity: Maintains consistency rules and constraints
• Documentation: Provides comprehensive database documentation

Mnemonic: “Data Dictionary Delivers Details”

Question 1(b) [4 marks]

#

Define (i) E-R model (ii) Entity (iii) Entity set and (iv) attributes

Answer:

Table: ER Model Definitions

Diagram: ER Model Components

Key Points:

• Conceptual Design: High-level database design approach
• Visual Representation: Uses diagrams for clear understanding

Mnemonic: “Entities Relate Meaningfully”

Question 1(c) [7 marks]

#

Explain Advantages of DBMS

Answer:

Table: DBMS Advantages

Key Benefits:

• Centralized Control: Single point of data management
• Cost Effectiveness: Reduces development and maintenance costs
• Data Consistency: Ensures uniform data across applications
• Concurrent Access: Multiple users can work simultaneously
• Query Optimization: Efficient data retrieval mechanisms

Mnemonic: “Database Benefits Business Better”

Question 1(c) OR [7 marks]

#

Explain Architecture of DBMS

Answer:

Diagram: Three-Level DBMS Architecture

graph TB
    A[External Level<br/>User Views] --> B[Conceptual Level<br/>Logical Schema]
    B --> C[Internal Level<br/>Physical Storage]
    D[User 1] --> A
    E[User 2] --> A
    F[DBA] --> B
    G[System] --> C

Table: Architecture Levels

Key Features:

• Data Independence: Changes at one level don’t affect others
• Security: Different access levels for different users
• Abstraction: Hides complexity from users

Mnemonic: “External Conceptual Internal Architecture”

Question 2(a) [3 marks]

#

Explain UNIQUE KEY and PRIMARY KEY

Answer:

Table: Key Comparison

Key Differences:

• Primary Key: Uniquely identifies each record, cannot be null
• Unique Key: Ensures uniqueness but allows one null value

Mnemonic: “Primary Prevents Nulls, Unique Understands Nulls”

Question 2(b) [4 marks]

#

Write a short note on Participation of Entity in ER diagram

Answer:

Table: Participation Types

Diagram: Participation Example

Key Concepts:

• Mandatory Participation: Every instance must be involved
• Optional Participation: Some instances may not be involved
• Business Rules: Reflects real-world constraints

Mnemonic: “Total Participation Requires All”

Question 2(c) [7 marks]

#

Describe Generalization concept in Detail for ER diagram

Answer:

Diagram: Generalization Example

erDiagram
    PERSON {
        int person_id
        string name
        string address
    }
    EMPLOYEE {
        int employee_id
        decimal salary
        string department
    }
    STUDENT {
        int student_id
        string course
        decimal fees
    }
    PERSON ||--|| EMPLOYEE : is-a
    PERSON ||--|| STUDENT : is-a

Table: Generalization Characteristics

Key Features:

• Attribute Inheritance: Common attributes moved to superclass
• Relationship Inheritance: Relationships also inherited
• Constraint Types: Total/partial, disjoint/overlapping
• ISA Relationship: Represents “is-a” connection

Mnemonic: “Generalization Groups Similar Entities”

Question 2(a) OR [3 marks]

#

Explain Mapping Cardinality in ER diagram

Answer:

Table: Cardinality Types

Key Concepts:

• Relationship Constraints: Defines how entities can be related
• Business Rules: Reflects real-world relationship limits

Mnemonic: “One Or Many Mappings Matter”

Question 2(b) OR [4 marks]

#

Explain Aggregation in E-R diagram

Answer:

Diagram: Aggregation Example

Key Features:

• Relationship as Entity: Treats relationship set as entity
• Higher-level Relationships: Allows relationships between relationships
• Complex Modeling: Handles advanced business scenarios
• Abstraction Mechanism: Simplifies complex relationships

Table: Aggregation Benefits

Mnemonic: “Aggregation Abstracts Advanced Associations”

Question 2(c) OR [7 marks]

#

Draw ER diagram of Library Management system using Enhanced ER model

Answer:

Diagram: Library Management System

erDiagram
    PERSON {
        int person_id
        string name
        string address
        string phone
    }
    MEMBER {
        int member_id
        date join_date
        string membership_type
    }
    LIBRARIAN {
        int employee_id
        decimal salary
        string department
    }
    BOOK {
        int book_id
        string title
        string author
        string isbn
        int copies
    }
    CATEGORY {
        int category_id
        string category_name
        string description
    }
    TRANSACTION {
        int transaction_id
        date issue_date
        date return_date
        string status
    }
    
    PERSON ||--|| MEMBER : is-a
    PERSON ||--|| LIBRARIAN : is-a
    MEMBER ||--o{ TRANSACTION : makes
    BOOK ||--o{ TRANSACTION : involved_in
    BOOK }o--|| CATEGORY : belongs_to
    LIBRARIAN ||--o{ TRANSACTION : processes

Enhanced ER Features Used:

• Generalization: Person superclass with Member and Librarian subclasses
• Specialization: Different attributes for different person types
• Aggregation: Transaction relationship involving multiple entities
• Multiple Inheritance: Complex relationship handling

Mnemonic: “Library Links Literature Logically”

Question 3(a) [3 marks]

#

Explain SQL data types

Answer:

Table: Common SQL Data Types

Key Points:

• Data Integrity: Ensures correct data storage
• Storage Optimization: Appropriate size allocation
• Validation: Automatic data type checking

Mnemonic: “Data Types Define Storage”

Question 3(b) [4 marks]

#

Compare DROP and TRUNCATE commands

Answer:

Table: DROP vs TRUNCATE Comparison

Code Examples:

-- DROP command
DROP TABLE student;

-- TRUNCATE command  
TRUNCATE TABLE student;

Key Differences:

• Structure Impact: DROP removes everything, TRUNCATE keeps structure
• Performance: TRUNCATE is faster for large tables

Mnemonic: “DROP Destroys, TRUNCATE Trims”

Question 3(c) [7 marks]

#

Consider a following Relational Schema and give Relational Algebra Expression for the following Queries Students (Name, SPI, DOB, Enrollment No)

Answer:

Relational Algebra Expressions:

i) List out all students whose SPI is lower than 6.0:

σ(SPI < 6.0)(Students)

ii) List name of student whose enrollment number contains 006:

π(Name)(σ(Enrollment_No LIKE '%006%')(Students))

iii) List all students with same DOB:

Students ⋈ (ρ(S2)(Students)) WHERE Students.DOB = S2.DOB AND Students.Enrollment_No ≠ S2.Enrollment_No

iv) Display students name starting from same letter:

π(Name)(Students ⋈ (ρ(S2)(Students)) WHERE SUBSTR(Students.Name,1,1) = SUBSTR(S2.Name,1,1) AND Students.Enrollment_No ≠ S2.Enrollment_No)

Table: Relational Algebra Operators Used

Mnemonic: “Select Project Join Rename”

Question 3(a) OR [3 marks]

#

Explain use of Grant and Revoke command with example

Answer:

Code Examples:

-- GRANT command
GRANT SELECT, INSERT ON student TO user1;
GRANT ALL PRIVILEGES ON database1 TO user2;

-- REVOKE command  
REVOKE INSERT ON student FROM user1;
REVOKE ALL PRIVILEGES ON database1 FROM user2;

Key Features:

• Access Control: Manages user permissions
• Security: Prevents unauthorized access
• Granular Control: Specific privilege assignment

Table: Common Privileges

Mnemonic: “Grant Gives, Revoke Removes”

Question 3(b) OR [4 marks]

#

Describe DML commands with Example

Answer:

Table: DML Commands

Code Examples:

-- INSERT command
INSERT INTO Students (name, spi, dob) 
VALUES ('Alice', 8.5, '2000-05-15');

-- UPDATE command
UPDATE Students SET spi = 9.0 
WHERE name = 'Alice';

-- DELETE command
DELETE FROM Students 
WHERE spi < 6.0;

-- SELECT command
SELECT name, spi FROM Students 
WHERE spi > 8.0;

Key Features:

• Data Manipulation: Core database operations
• Transaction Support: Can be rolled back
• Conditional Operations: WHERE clause support

Mnemonic: “Insert Update Delete Select”

Question 3(c) OR [7 marks]

#

List all Conversion function of DBMS and explain any three of them in detail

Answer:

Table: Conversion Functions

Detailed Explanation of Three Functions:

1. TO_CHAR Function:

• Purpose: Converts dates and numbers to character strings
• Syntax: TO_CHAR(value, format)
• Usage: Date formatting, number formatting with specific patterns

2. TO_DATE Function:

• Purpose: Converts character strings to date values
• Syntax: TO_DATE(string, format)
• Usage: String to date conversion with specified format

3. TO_NUMBER Function:

• Purpose: Converts character strings to numeric values
• Syntax: TO_NUMBER(string, format)
• Usage: String to number conversion for calculations

Key Benefits:

• Data Type Flexibility: Seamless conversion between types
• Format Control: Specific formatting options
• Error Handling: Validation during conversion

Mnemonic: “Convert Characters Dates Numbers”

Question 4(a) [3 marks]

#

Write short note: Domain Integrity Constraint

Answer:

Domain Integrity Constraints ensure that data values fall within acceptable ranges and formats for specific attributes.

Table: Domain Constraint Types

Key Features:

• Data Validation: Ensures data quality at entry
• Business Rules: Implements domain-specific rules
• Automatic Checking: Validation occurs during DML operations

Mnemonic: “Domain Defines Data Boundaries”

Question 4(b) [4 marks]

#

List all JOIN in DBMS and explain any two

Answer:

Table: Types of JOINs

Detailed Explanation:

1. INNER JOIN:

SELECT s.name, c.course_name
FROM students s
INNER JOIN courses c ON s.course_id = c.course_id;

• Returns only matching records from both tables
• Most commonly used join type

2. LEFT JOIN:

SELECT s.name, c.course_name
FROM students s
LEFT JOIN courses c ON s.course_id = c.course_id;

• Returns all students, even if no course assigned
• NULL values for unmatched records

Mnemonic: “Join Tables Together Thoughtfully”

Question 4(c) [7 marks]

#

Explain Concept of Functional Dependency in detail

Answer:

Functional Dependency occurs when the value of one attribute uniquely determines the value of another attribute.

Notation: A → B (A functionally determines B)

Table: Types of Functional Dependencies

Diagram: Functional Dependency Example

Key Properties:

• Reflexivity: A → A (trivial dependency)
• Augmentation: If A → B, then AC → BC
• Transitivity: If A → B and B → C, then A → C
• Decomposition: If A → BC, then A → B and A → C

Applications:

• Normalization: Eliminates redundancy using FD
• Database Design: Determines table structure
• Data Integrity: Maintains consistency

Mnemonic: “Functions Determine Dependencies Directly”

Question 4(a) OR [3 marks]

#

Write short note: Referential integrity Constraints

Answer:

Referential Integrity ensures that foreign key values in one table correspond to existing primary key values in referenced table.

Table: Referential Integrity Rules

Key Features:

• Foreign Key Constraint: Links related tables
• Data Consistency: Prevents orphaned records
• Relationship Maintenance: Preserves table relationships

Code Example:

ALTER TABLE Orders 
ADD CONSTRAINT FK_Customer 
FOREIGN KEY (customer_id) 
REFERENCES Customers(customer_id);

Mnemonic: “References Require Related Records”

Question 4(b) OR [4 marks]

#

Explain union and intersection operations of relational algebra

Answer:

Table: Set Operations Comparison

Union Operation:

• Syntax: R ∪ S
• Result: All tuples from R and S (duplicates removed)
• Requirement: Same number and types of attributes

Intersection Operation:

• Syntax: R ∩ S
• Result: Tuples that exist in both R and S
• Requirement: Union compatible relations

Example:

Students_CS ∪ Students_IT = All students from both departments
Students_CS ∩ Students_IT = Students in both departments

Key Points:

• Union Compatibility: Relations must have same structure
• Duplicate Elimination: Results contain unique tuples only

Mnemonic: “Union Unites, Intersection Identifies Common”

Question 4(c) OR [7 marks]

#

Explain Concept of Normalization in DBMS in detail

Answer:

Normalization is the process of organizing database tables to minimize data redundancy and improve data integrity.

Table: Normal Forms

Normalization Process:

Step 1 - First Normal Form (1NF):

• Eliminate repeating groups
• Each cell contains single value
• Each record is unique

Step 2 - Second Normal Form (2NF):

• Must be in 1NF
• Remove partial dependencies
• Non-key attributes fully dependent on primary key

Step 3 - Third Normal Form (3NF):

• Must be in 2NF
• Remove transitive dependencies
• Non-key attributes not dependent on other non-key attributes

Benefits of Normalization:

• Reduced Redundancy: Eliminates duplicate data
• Data Integrity: Maintains consistency
• Storage Efficiency: Minimizes storage space
• Update Anomalies: Prevents inconsistent updates

Drawbacks:

• Complex Queries: May require multiple joins
• Performance Impact: Can slow down retrieval

Mnemonic: “Normalize to Neat, Non-redundant Tables”

Question 5(a) [3 marks]

#

Describe Need of Normalization in DBMS

Answer:

Table: Problems Solved by Normalization

Key Needs:

• Data Consistency: Ensures uniform data across database
• Storage Optimization: Reduces redundant storage
• Maintenance Simplicity: Easier database updates

Benefits:

• Improved Data Quality: Reduces errors and inconsistencies
• Flexible Design: Easier to modify and extend
• Better Performance: For update operations

Mnemonic: “Normalization Needs Neat Organization”

Question 5(b) [4 marks]

#

Explain properties of Transaction in DBMS

Answer:

Table: ACID Properties

Detailed Explanation:

Atomicity:

• Transaction is indivisible unit
• Either all operations complete or none

Consistency:

• Database transitions from one valid state to another
• All integrity constraints maintained

Isolation:

• Concurrent transactions appear to run sequentially
• Intermediate states not visible to other transactions

Durability:

• Once committed, changes survive system failures
• Data permanently stored

Mnemonic: “ACID Assures Correct Database”

Question 5(c) [7 marks]

#

Explain View Serializability in detail

Answer:

View Serializability determines if a concurrent schedule produces the same result as some serial schedule by examining read and write operations.

Table: View Equivalence Conditions

Key Concepts:

View Equivalent Schedules: Two schedules are view equivalent if:

1. For each data item, if transaction T reads initial value in one schedule, it reads initial value in other
2. For each read operation, if T reads value written by T’ in one schedule, same holds in other
3. For each data item, if T performs final write in one schedule, it performs final write in other

Testing View Serializability:

1. Precedence Graph: Create directed graph
2. Cycle Detection: Check for cycles in graph
3. Conflict Analysis: Examine read-write conflicts

Example Analysis:

Schedule S1: R1(X) W1(X) R2(X) W2(X)
Schedule S2: R1(X) R2(X) W1(X) W2(X)

Benefits:

• Concurrency Control: Ensures correctness
• Performance: Allows maximum concurrency
• Consistency: Maintains database integrity

Comparison with Conflict Serializability:

• View serializability is less restrictive
• Some view serializable schedules are not conflict serializable
• More complex to test

Mnemonic: “View Verifies Valid Schedules”

Question 5(a) OR [3 marks]

#

Perform 2NF on any Database

Answer:

Example: Student Course Database

Original Table (Not in 2NF):

Student_Course (Student_ID, Student_Name, Course_ID, Course_Name, Grade, Instructor)
Primary Key: {Student_ID, Course_ID}

Functional Dependencies:

• Student_ID → Student_Name (Partial dependency)
• Course_ID → Course_Name, Instructor (Partial dependency)
• {Student_ID, Course_ID} → Grade

2NF Decomposition:

Table 1: Students

Students (Student_ID, Student_Name)
Primary Key: Student_ID

Table 2: Courses

Courses (Course_ID, Course_Name, Instructor)  
Primary Key: Course_ID

Table 3: Enrollments

Enrollments (Student_ID, Course_ID, Grade)
Primary Key: {Student_ID, Course_ID}
Foreign Keys: Student_ID → Students, Course_ID → Courses

Result: All partial dependencies eliminated, now in 2NF.

Mnemonic: “Second Normal Form Separates Dependencies”

Question 5(b) OR [4 marks]

#

Explain States of Transaction

Answer:

Diagram: Transaction State Diagram

stateDiagram-v2
    [*] --> Active
    Active --> PartiallyCommitted : commit
    Active --> Failed : failure/abort
    PartiallyCommitted --> Committed : successful completion
    PartiallyCommitted --> Failed : failure
    Failed --> Aborted : rollback completed
    Committed --> [*]
    Aborted --> [*]

Table: Transaction States

State Transitions:

• Active to Failed: Due to errors or explicit abort
• Active to Partially Committed: After final statement
• Partially Committed to Committed: Successful completion
• Failed to Aborted: After rollback operations

Key Points:

• Recovery: System can recover from failed states
• Durability: Committed changes are permanent
• Atomicity: Aborted transactions leave no trace

Mnemonic: “Transactions Travel Through States”

Question 5(c) OR [7 marks]

#

Explain Conflict Serializability in detail

Answer:

Conflict Serializability ensures that a concurrent schedule is equivalent to some serial schedule by analyzing conflicting operations.

Table: Conflicting Operations

Testing Conflict Serializability:

Step 1: Identify Conflicts

• Find pairs of operations on same data item
• Check if operations belong to different transactions
• Determine if operations conflict

Step 2: Create Precedence Graph

• Nodes represent transactions
• Directed edges represent conflicts
• Edge from Ti to Tj if Ti conflicts with Tj

Step 3: Check for Cycles

• If graph has no cycles → Conflict serializable
• If graph has cycles → Not conflict serializable

Example Analysis:

Schedule: R1(A) W1(A) R2(A) W2(B) R1(B) W1(B)

Conflicts:
- W1(A) conflicts with R2(A) → T1 before T2
- W2(B) conflicts with R1(B) → T2 before T1
- W2(B) conflicts with W1(B) → T2 before T1

Precedence Graph:

Result: Contains cycle, therefore NOT conflict serializable.

Table: Serializability Testing Steps

Key Properties:

• Conflict Equivalent: Same conflicts, same relative order
• Serial Schedule: One transaction at a time
• Precedence Graph: Directed graph showing dependencies
• Cycle Detection: Determines conflict serializability

Benefits:

• Concurrency Control: Ensures correctness
• Performance: Maximizes concurrent execution
• Consistency: Maintains database integrity

Comparison with View Serializability:

• Conflict serializability is more restrictive
• All conflict serializable schedules are view serializable
• Easier to test than view serializability

Algorithms for Testing:

1. Precedence Graph Method: Build graph and check cycles
2. Timestamp Ordering: Use timestamps to order operations
3. Two-Phase Locking: Use locks to ensure serializability

Mnemonic: “Conflicts Create Cycles, Check Carefully”