Database Management System
Solved Answers for University Examination
Mumbai University Format
Q.1. Explain ACID properties with examples. 10 Marks
ACID Properties in DBMS
ACID properties are a set of properties that guarantee reliable processing of database transactions. These
properties ensure data validity despite errors, power failures, and other issues.
Atomicity: Ensures that all operations within a transaction are completed successfully; otherwise, the
transaction is aborted and previous operations are rolled back to their former state.
Example: Consider a bank transfer of ₹5000 from Account A to Account B. Atomicity ensures either both the debit from
Account A and credit to Account B happen, or neither happens. If the system fails after debiting from A but before
crediting to B, atomicity guarantees the debit operation is rolled back.
Consistency: Ensures that a transaction can only bring the database from one valid state to another,
maintaining database invariants and constraints.
Example: If a bank has a rule that no account balance can be negative, consistency ensures that no transaction will leave
an account with a negative balance. If Account A has ₹3000 and a transaction attempts to transfer ₹5000, the transaction
will be rejected as it violates the consistency rule.
Isolation: Ensures that concurrent execution of transactions results in a system state that would be obtained
if transactions were executed sequentially.
Example: If Transaction 1 is transferring ₹5000 from Account A to B, and simultaneously Transaction 2 is depositing ₹1000
to Account A, isolation ensures that Transaction 2 either sees the balance before the transfer or after it, but not an
intermediate state. This prevents the "dirty read" problem.
Durability: Ensures that once a transaction has been committed, it will remain committed even in the case of
a system failure.
Example: If a customer receives a confirmation for a successful transfer of ₹5000, durability guarantees that the transfer
will persist even if there is a power outage or system crash immediately after the confirmation. When the system comes
back online, the transaction remains committed.
Implementation of ACID Properties:
Property Implementation Technique
Atomicity Write-ahead logging, Shadow paging
Consistency Constraints, Triggers, Cascades
Isolation Locks, MVCC (Multi-Version Concurrency Control)
Durability Transaction logs, Database checkpoints
In conclusion, ACID properties provide a framework for ensuring reliable transaction processing in database
systems, maintaining data integrity and consistency even in the presence of errors and concurrent operations.
Q.2. Explain different types of Normalization with examples. 10 Marks
Database Normalization
Normalization is a systematic approach of decomposing tables to eliminate data redundancy and undesirable
characteristics like Insertion, Update, and Deletion anomalies.
First Normal Form (1NF):
A relation is in 1NF if it contains only atomic (indivisible) values and there are no repeating groups.
Example:
Consider a table with student details:
Student_ID Name Courses
101 Amit Kumar DBMS, OS, Networks
This table is not in 1NF because the Courses column contains multiple values.
After applying 1NF:
Student_ID Name Course
101 Amit Kumar DBMS
101 Amit Kumar OS
101 Amit Kumar Networks
Second Normal Form (2NF):
A relation is in 2NF if it is in 1NF and every non-prime attribute is fully functionally dependent on the primary key.
Example:
Consider a table storing student course enrollment with grades:
Student_ID Course_ID Grade Course_Name
101 C1 A Database Systems
Here, the primary key is (Student_ID, Course_ID), but Course_Name depends only on Course_ID, not on the
entire primary key. This violates 2NF.
After applying 2NF:
Table 1: Enrollment
Student_ID Course_ID Grade
101 C1 A
Table 2: Course
Course_ID Course_Name
C1 Database Systems
Third Normal Form (3NF):
A relation is in 3NF if it is in 2NF and no non-prime attribute is transitively dependent on the primary key.
Example:
Consider a table storing student details:
Student_ID Department_ID Department_Name
101 D1 Computer Science
Here, Department_Name is functionally dependent on Department_ID, which in turn depends on Student_ID.
This is a transitive dependency, violating 3NF.
After applying 3NF:
Table 1: Student
Student_ID Department_ID
101 D1
Table 2: Department
Department_ID Department_Name
D1 Computer Science
Boyce-Codd Normal Form (BCNF):
A relation is in BCNF if it is in 3NF and for every functional dependency X → Y, X is a superkey.
Example:
Consider a table storing student enrollment in courses with professors:
Student_ID Course_ID Professor_ID
101 C1 P1
If each course is taught by only one professor, then Course_ID → Professor_ID is a functional dependency.
This violates BCNF if the primary key is (Student_ID, Course_ID).
After applying BCNF:
Table 1: Course_Professor
Course_ID Professor_ID
C1 P1
Table 2: Student_Course
Student_ID Course_ID
101 C1
Fourth Normal Form (4NF):
A relation is in 4NF if it is in BCNF and has no multi-valued dependencies.
Fifth Normal Form (5NF):
A relation is in 5NF (also called Project-Join Normal Form or PJNF) if every join dependency in it is implied by
the candidate keys.
The primary goal of normalization is to minimize redundancy and dependency, which helps in maintaining data
integrity and reducing update anomalies.
Q.3. Explain different types of Database users with their roles and 10
Marks
responsibilities.
Database Users: Roles and Responsibilities
Database systems support various types of users, each with different responsibilities and privileges. These
users interact with the database system in distinct ways depending on their roles.
1. Database Administrators (DBA)
Responsible for the overall administration of the database system
Create and manage database user accounts and access privileges
Perform database backup, recovery, and maintenance operations
Monitor database performance and tune for optimization
Implement and enforce security policies
Plan for capacity and manage database growth
Coordinate with application developers for schema modifications
Example responsibilities include running GRANT/REVOKE commands, configuring replication, and managing storage
resources.
2. Database Designers
Responsible for designing the database schema
Identify data entities, relationships, and constraints
Normalize the database structure
Create ER diagrams and other design documentation
Design for performance, security, and data integrity
Work with stakeholders to understand data requirements
Example tasks include creating tables with appropriate relationships, designing indexes, and documenting database
structures.
3. Application Programmers
Design and implement database applications
Write queries to retrieve, insert, update, and delete data
Implement business logic that interacts with the database
Optimize code for database performance
Handle database errors and exceptions in applications
Implement data validation at the application level
Example work includes writing SQL statements embedded in programming languages, stored procedures, and
implementing ORM (Object-Relational Mapping).
4. End Users
End users can be classified into several categories:
4.1 Casual Users
Access database occasionally for specific information
Use predefined queries and reports
Limited knowledge of database structure or query languages
Usually interact through user-friendly interfaces
Example: A manager checking monthly sales reports through a dashboard
4.2 Naive Users
Use database regularly but with simple operations
Interact with databases through well-defined forms and applications
No awareness of underlying database structure
Examples include bank tellers, reservation clerks, etc.
Example: A bank clerk processing customer transactions
4.3 Sophisticated Users
Familiar with database structure and query languages
Can write complex queries for data analysis
May develop personal applications using database
Examples include analysts, engineers, scientists
Example: A data analyst writing complex SQL queries for business intelligence reports
5. Database System Operators
Handle technical operation of the DBMS software
Manage physical hardware resources
Perform routine maintenance operations
Handle system backups and restores
Monitor system logs and handle errors
Example tasks include starting/stopping database services, maintaining storage systems, and applying software patches.
6. Data Scientists and Analysts
Extract and analyze data for business insights
Create complex queries for data mining
Build statistical models using database data
Generate reports and visualizations
Identify trends and patterns in data
Example work includes creating business intelligence dashboards, performing statistical analysis, and building predictive
models.
Database User Hierarchy
┌─────────────────────┐
│ Database │
│ Administrators │
└──────────┬──────────┘
/|\
/ | \
┌──────────┘ | └──────────┐
| | |
┌────────────┴─────┐ ┌─────┴─────┐ ┌─────┴────────────┐
│ Database │ │ System │ │ Application │
│ Designers │ │ Operators │ │ Programmers │
└────────────┬─────┘ └───────────┘ └─────┬────────────┘
| |
| |
┌────────────┴─────────────┐ ┌────────┴────────────┐
│ Sophisticated │ │ End Users │
│ Users │ │ (Casual/Naive) │
└───────────────────────┬──┘ └─────────────────────┘
|
┌─────────────┴──────────────┐
│ Data Scientists/ │
│ Analysts │
└────────────────────────────┘
Each type of user requires different privileges and tools to interact with the database effectively. A well-designed
database management system provides appropriate interfaces and security mechanisms to support all user
types while maintaining data integrity and security.
Q.4. Explain Transaction States in DBMS with diagram. 10 Marks
Transaction States in DBMS
A transaction is a logical unit of work that must be either completed in its entirety or not executed at all. In
database systems, transactions move through various states from initiation to completion.
Transaction States:
1. Active State: The initial state of a transaction. The transaction stays in this state while it is executing read or
write operations.
2. Partially Committed State: After the final operation is executed, the transaction enters this state. At this point,
the transaction has completed its execution, but the output is still in main memory and has not been written
to disk.
3. Committed State: If a transaction completes all its operations successfully, it enters the committed state. All
changes are now permanently stored in the database. The transaction cannot be aborted at this point.
4. Failed State: If normal execution can no longer proceed, the transaction enters the failed state. This might
happen due to hardware failure, logical errors, deadlocks, etc.
5. Aborted State: If a transaction fails and enters the failed state, the database is restored to its state prior to
the start of the transaction. This process is called rollback. After rollback, the transaction enters the aborted
state.
6. Terminated State: The final state in the transaction life cycle. After committing or aborting, the transaction
eventually enters the terminated state.
Transaction State Diagram:
┌─────────────┐
│ │
│ Active │
│ │
└──────┬──────┘
│
▼
┌─────────────┐ Error ┌─────────────┐
│ Partially │───────────────▶│ Failed │
│ Committed │ │ │
└──────┬──────┘ └──────┬──────┘
│ │
│ │
│ │
▼ ▼
┌─────────────┐ ┌─────────────┐
│ │ │ │
│ Committed │ │ Aborted │
│ │ │ │
└──────┬──────┘ └──────┬──────┘
│ │
│ │
│ │
└─────────────┬───────────────┘
│
▼
┌─────────────┐
│ │
│ Terminated │
│ │
└─────────────┘
Transaction State Transitions:
From State To State Condition/Action
Active Partially Committed When the final operation is executed
Active Failed When an error occurs during execution
Partially Committed Committed When all changes are permanently stored in the database
Partially Committed Failed When system failure occurs before commit
Failed Aborted After the transaction is rolled back
Committed Terminated After the transaction completes
Aborted Terminated After the transaction completes (after rollback)
Aborted Active If the transaction is restarted (optional)
Example Scenario:
Consider a banking transaction for transferring ₹5000 from Account A to Account B:
1. Active State: Transaction begins, reads balance of Account A (₹10,000) and Account B (₹3,000)
2. Active State: Transaction deducts ₹5,000 from Account A, making it ₹5,000
3. Active State: Transaction adds ₹5,000 to Account B, making it ₹8,000
4. Partially Committed: All operations completed, changes are in memory buffer
5. Committed: Changes are written to disk, transaction is successful
6. Terminated: Transaction completes and resources are released
Alternative scenario with failure:
1. Active State: Transaction begins, reads balance of Account A (₹10,000)
2. Active State: Transaction deducts ₹5,000 from Account A, making it ₹5,000
3. Failed State: System failure occurs before adding amount to Account B
4. Aborted State: Transaction is rolled back, Account A is restored to ₹10,000
5. Terminated: Transaction ends in failure
The transaction state model ensures that database operations maintain the ACID properties (Atomicity,
Consistency, Isolation, Durability) by providing a structured approach to handling transaction execution and
potential failures.
Q.5. Write short notes on (any two): 10 Marks
Concurrency Control
Entity-Relationship Model
Distributed Databases
Short Notes
(a) Concurrency Control in DBMS
Concurrency control is a mechanism that ensures multiple transactions can execute simultaneously without
violating database integrity. It manages the concurrent access to shared data, preventing interference between
simultaneous transactions.
Concurrency Problems:
Lost Update Problem: When two transactions read and update the same data simultaneously, one update
may be lost
Dirty Read Problem: When a transaction reads data that has been modified by another uncommitted
transaction
Unrepeatable Read Problem: When a transaction reads the same data twice but gets different results
because another transaction modified the data between reads
Phantom Read Problem: When a transaction rereads a set of rows that satisfy a condition and finds new
rows added by another committed transaction
Concurrency Control Techniques:
1. Lock-Based Protocols:
Shared Lock (S): Multiple transactions can hold shared locks for reading data
Exclusive Lock (X): Only one transaction can hold an exclusive lock for writing data
Two-Phase Locking (2PL): Transactions acquire all locks before releasing any lock
2. Timestamp-Based Protocols:
Assigns a unique timestamp to each transaction
Orders transactions based on their timestamps
Basic approach: If T1 has timestamp < T2, then T1's operations should appear before T2's operations
3. Optimistic Concurrency Control:
Assumes conflicts are rare and doesn't block transactions
Three phases: Read, Validate, Write
During validation, checks if concurrent transactions conflict
4. Multiversion Concurrency Control (MVCC):
Maintains multiple versions of data items
Each transaction sees a snapshot of the database at a particular point in time
Readers don't block writers and writers don't block readers
Deadlock Handling:
Deadlock Prevention: Design systems to prevent deadlocks from occurring
Deadlock Avoidance: Decide dynamically if a lock request might lead to deadlock
Deadlock Detection: Allow deadlocks to occur but detect and resolve them
Deadlock Recovery: Select a victim transaction to abort and roll back
Transaction Isolation Levels:
Isolation Level Dirty Read Unrepeatable Read Phantom Read
Read Uncommitted Possible Possible Possible
Read Committed Prevented Possible Possible
Repeatable Read Prevented Prevented Possible
Serializable Prevented Prevented Prevented
Effective concurrency control is crucial for maintaining database consistency while allowing multiple users to
access and update data simultaneously. Different techniques offer varying levels of performance and isolation,
which should be selected based on application requirements.
(b) Entity-Relationship Model
The Entity-Relationship (ER) model is a conceptual data model used in database design to represent the real-
world entities, relationships between them, and their constraints. It provides a visual representation of data
structures that can be easily understood by both technical and non-technical stakeholders.
Basic Components of ER Model:
1. Entities: Objects or concepts in the real world about which data is collected.
Represented by rectangles in ER diagrams
Example: Student, Course, Employee
2. Attributes: Properties or characteristics of entities.
Represented by ovals in ER diagrams
Example: Student_ID, Name, Age
Types: Simple, Composite, Single-valued, Multi-valued, Derived
3. Relationships: Associations between entities.
Represented by diamonds in ER diagrams
Example: "Enrolls" relationship between Student and Course
Types of Attributes:
Simple Attribute: Cannot be divided further (e.g., Age)
Composite Attribute: Can be divided into smaller parts (e.g., Address has Street, City, State)
Single-valued Attribute: Has only one value (e.g., Social Security Number)
Multi-valued Attribute: Can have multiple values (e.g., Phone Numbers)
Derived Attribute: Value can be derived from other attributes (e.g., Age from Date of Birth)
Key Attribute: Uniquely identifies an entity (e.g., Student_ID)
Types of Relationships:
1. One-to-One (1:1): An entity in set A is associated with at most one entity in set B, and vice versa.
Example: One person has exactly one passport, and one passport belongs to exactly one person.
2. One-to-Many (1:N): An entity in set A can be associated with multiple entities in set B, but an entity in B is
associated with at most one entity in A.
Example: One department has many employees, but each employee works in exactly one department.
3. Many-to-One (N:1): Multiple entities in set A can be associated with one entity in set B, but an entity in A is
associated with at most one entity in B.
Example: Many students can have the same advisor, but each student has only one advisor.
4. Many-to-Many (M:N): An entity in set A can be associated with multiple entities in set B, and vice versa.
Example: Students can enroll in multiple courses, and courses can have multiple students.
Entity Types:
Strong Entity: Exists independently and has a primary key
Weak Entity: Depends on a strong entity for its existence and doesn't have a complete primary key
Cardinality and Participation Constraints:
Cardinality: Maximum number of relationship instances an entity can participate in
Participation: Whether all entities participate in the relationship (total) or only some (partial)
Extended ER Features:
Specialization: Process of defining a set of subclasses of an entity type
Generalization: Process of defining a generalized entity type from a set of specialized entity types
Aggregation: Represents a relationship between a whole object and its component parts
Simple ER Diagram Example:
┌──────────┐ ┌───────────┐ ┌──────────┐
│ │ │ │ │ │
│ Student ├───────┤ Enrolls ├───────┤ Course │
│ │ │ │ │ │
└──────┬───┘ └───────────┘ └────┬─────┘
│ │
┌─┴─┐ ┌─┴─┐
│ │ │ │
┌────┴───┴─┐ ┌────┴───┴─┐
│ │ │ │
│Student_ID│ │Course_ID │
│ │ │ │
└──────────┘ └──────────┘
Importance of ER Model:
Provides a clear visual representation of data structures
Facilitates communication between designers and end users
Helps in identifying entities and relationships before implementation
Can be easily converted to relational database schema
Allows for validation of data requirements
The ER model continues to be an essential tool in database design, offering a structured approach to capturing
requirements and translating them into an effective database schema. Its visual nature makes it particularly
valuable for communicating complex data relationships to stakeholders from diverse backgrounds.
Database Management System - Solved Answers
Formatted for Mumbai University Examinations
Made with Genspark
