DBMS Short Revision Notes
1. File System vs DBMS
File System
Traditional method of storing data in separate files. Example: [Link], [Link]
Problems
• Data redundancy (same data repeated)
• Data inconsistency
• Difficult searching
• Weak security
• No proper backup/recovery
• Difficult multi-user access
• No relationships between files
DBMS
Software used to store and manage data in tables. Examples: MySQL, Oracle, SQL Server
Advantages of DBMS
• Reduced redundancy
• Better security
• Fast searching using SQL
• Backup and recovery available
• Data consistency maintained
• Relationships between tables possible
• Multi-user support
• Data independence
2. Three Level Architecture
DBMS architecture divided into 3 levels.
Levels
1. External Level (View Level)
• Highest level
1
• Different users see different data
• Improves security
Example:
• Student → marks
• Accountant → fees
2. Conceptual Level (Logical Level)
• Complete logical structure of database
• Contains tables, attributes, relationships
• No physical storage details
3. Internal Level (Physical Level)
• Lowest level
• Describes physical storage
• Includes indexing, file organization
Mapping
• External ↔ Conceptual mapping
• Conceptual ↔ Internal mapping
Data Independence
Physical Data Independence
Changes in storage should not affect tables.
Logical Data Independence
Changes in logical schema should not affect user views.
Advantages
• Security
• Data abstraction
• Easy maintenance
• Multiple views
• Data independence
2
3. Data Models
Data model describes:
• How data is stored
• How data is connected
• How data is accessed
Types of Data Models
1. Hierarchical Model
• Tree structure
• One-to-many relationship
• One child usually has one parent
Advantages
• Simple
• Fast access
Disadvantages
• Rigid structure
• Many-to-many not supported properly
2. Network Model
• Graph structure
• Many-to-many relationships possible
• Child can have multiple parents
Advantages
• Flexible
• Supports complex relationships
Disadvantages
• Complex design
• Difficult navigation
3. Relational Model
• Data stored in tables
• Most popular model
3
Advantages
• Easy to understand
• SQL support
• Reduced redundancy
• Flexible
Disadvantages
• Complex queries may be slow
• Large databases need more memory
4. ER Model
Used for visual database design. Uses entities, attributes, relationships.
5. Object-Oriented Model
Stores data as objects. Contains data + methods.
4. ER Model Basics
ER Model
Entity Relationship Model used for database design.
Main Components
• Entity
• Attribute
• Relationship
Entity
Real-world object whose data is stored. Examples: Student, Teacher, Course
Types of Entities
Strong Entity
• Has primary key
• Exists independently
4
Weak Entity
• Depends on strong entity
• No primary key of its own
Attribute
Properties of entity. Examples: Name, Age, Roll No
Types of Attributes
Simple Attribute
Cannot be divided further. Example: Age
Composite Attribute
Can be divided. Example: Address
Single-Valued Attribute
Only one value. Example: Roll No
Multi-Valued Attribute
Multiple values. Example: Phone Numbers
Derived Attribute
Calculated from another attribute. Example: Age from DOB
Key Attribute
Uniquely identifies entity. Example: Roll No
Relationship
Connection between entities. Example: Student studies Course
Degree of Relationship
Unary
Relationship within same entity.
5
Binary
Relationship between two entities.
Ternary
Relationship among three entities.
Cardinality Constraints
1:1
One-to-one
1:M
One-to-many
M:1
Many-to-one
M:N
Many-to-many
Participation Constraints
Total Participation
Participation compulsory.
Partial Participation
Participation optional.
ER Symbols
• Rectangle → Entity
• Double Rectangle → Weak Entity
• Oval → Attribute
• Double Oval → Multi-valued attribute
• Dashed Oval → Derived attribute
• Diamond → Relationship
• Double Diamond → Weak relationship
6
5. Aggregation
Aggregation treats a relationship as a higher-level entity.
Need
Used when relationship itself participates in another relationship.
Example
Employee works on Project. Manager monitors this work.
Manager monitors: (Employee works on Project)
Advantages
• Better real-world representation
• Reduces confusion
6. Specialization and Generalization
Specialization
Dividing one entity into smaller entities. Top-down approach.
Example: Employee → Teacher, Clerk, Accountant
Advantages
• Better organization
• Easy management
• Reduces confusion
Disadvantages
• Complex design
• Large ER diagrams
Generalization
Combining smaller entities into one entity. Bottom-up approach.
7
Example: Car + Bike + Truck → Vehicle
Advantages
• Reduces redundancy
• Simpler design
• Common attributes stored once
Disadvantages
• Complex hierarchy
Difference
Specialization Generalization
One entity divided Multiple entities combined
Top-down Bottom-up
Creates specific entities Creates general entity
7. ER to Relational Model Conversion
Rules
Entity → Relation
Each entity becomes a table.
1:M Relationship
Primary key of one side added as foreign key in many side.
M:N Relationship
Create separate relation.
Example
Department(Dept_ID, Dept_Name)
Student(Roll_No, Name, Branch, Dept_ID)
Course(Course_ID, Course_Name)
8
Studies(Roll_No, Course_ID)
Keys
Primary Key
Uniquely identifies tuples.
Foreign Key
References primary key of another table.
8. Joins
Join combines data from multiple tables.
Types of Joins
Theta Join
Uses comparison operators.
Equi Join
Uses only '=' operator.
Natural Join
Automatically joins common columns. No ON condition needed.
Outer Join
Shows unmatched rows also.
Left Outer Join
All rows from left table.
Right Outer Join
All rows from right table.
9
Full Outer Join
All rows from both tables.
Equi Join vs Natural Join
Equi Join Natural Join
Condition written manually Automatic condition
Uses ON clause No ON clause
Duplicate columns possible Duplicate columns removed
9. Triggers
Trigger
Special SQL procedure executed automatically on events.
Events
• INSERT
• UPDATE
• DELETE
Types
BEFORE Trigger
Executes before event.
AFTER Trigger
Executes after event.
Uses
• Security
• Auditing
• Backup
• Rule enforcement
• Automatic actions
10
Syntax
CREATE TRIGGER trigger_name
AFTER INSERT
ON table_name
FOR EACH ROW
BEGIN
statements;
END;
Advantages
• Automatic execution
• Maintains consistency
• Improves security
• Useful for auditing
10. Relational Algebra
Procedural query language of DBMS. Foundation of SQL.
Operators
Selection (σ)
Selects rows.
Projection (π)
Selects columns.
Union (∪)
Combines relations.
Difference (−)
Returns tuples in first relation only.
Cartesian Product (×)
Combines every row of first table with every row of second table.
11
Rename (ρ)
Renames relation or attributes.
Natural Join
Joins using common attributes.
Importance
• Base of SQL
• Helps query optimization
• Standard DBMS operations
11. Functional Dependency (FD)
Functional Dependency
Relationship between attributes.
Represented as: X → Y
Meaning: X determines Y.
Example
Roll_No → Name
Importance
• Normalization
• Reducing redundancy
• Good database design
Types of FD
Full Functional Dependency
Depends on complete primary key.
Partial Dependency
Depends on part of composite key.
12
Transitive Dependency
Depends through another non-key attribute.
Trivial FD
Right side already present in left side. Example: (A,B) → A
Non-Trivial FD
Right side not part of left side. Example: Roll_No → Name
12. Closure of Functional Dependency
Closure
Set of all attributes determined from given attribute set.
Represented as: X+
Uses
• Finding candidate keys
• Checking normalization
• Checking dependencies
Steps to Find Closure
1. Start with given attribute
2. Apply FDs
3. Keep adding attributes until no new attribute added
Candidate Key
If closure contains all relation attributes, then it is candidate key.
13
13. Normalization
Normalization
Process of organizing database to reduce:
• Redundancy
• Inconsistency
• Anomalies
Objectives
• Reduce duplicate data
• Remove anomalies
• Improve consistency
Anomalies
Insertion Anomaly
Problem inserting data.
Update Anomaly
Problem updating repeated data.
Deletion Anomaly
Deleting data causes loss of useful information.
Normal Forms
1NF
• Atomic values only
• No multivalued attributes
2NF
• Must be in 1NF
• No partial dependency
3NF
• Must be in 2NF
• No transitive dependency
14
BCNF
Stronger form of 3NF. Every determinant must be candidate key.
Important Terms
Term Meaning
Redundancy Repetition of data
Anomaly Database problem
Atomic Value Single value
Partial Dependency Depends on part of key
Transitive Dependency Depends through another attribute
14. Multivalued Dependency (MVD)
MVD
One attribute has multiple independent values.
Represented as: X ↠ Y
Example
Student has:
• Multiple phone numbers
• Multiple hobbies
This creates unnecessary combinations.
Problems
• Redundancy
• Extra rows
Solution
Split into separate tables.
Student_Phone(Student, Phone)
15
Student_Hobby(Student, Hobby)
15. Transaction
Transaction
Sequence of database operations treated as one logical unit.
Operations may include:
• INSERT
• UPDATE
• DELETE
• SELECT
Example
Bank Transfer: 1. Deduct money from A 2. Add money to B
Both steps together form one transaction.
Transaction States
Active
Transaction executing.
Partially Committed
Final statement executed.
Committed
Transaction completed successfully.
Failed
Transaction cannot continue.
Aborted
Failed transaction rolled back.
16
Important Property
Transaction must execute completely or not at all. This is Atomicity.
16. ACID Properties
ACID properties ensure reliable transactions.
A → Atomicity
All or nothing execution.
C → Consistency
Database remains correct before and after transaction.
I → Isolation
Transactions execute independently.
D → Durability
Committed changes become permanent.
Advantages
• Reliable transactions
• Prevents data corruption
• Better concurrency
• Proper failure handling
17. Deadlock Handling
Deadlock
Situation where transactions wait for each other forever.
Example
T1 holds A and waits for B. T2 holds B and waits for A.
17
Conditions for Deadlock
1. Mutual Exclusion
One resource used by one transaction at a time.
2. Hold and Wait
Transaction holds one resource and waits for another.
3. No Preemption
Resource cannot be forcibly taken.
4. Circular Wait
Transactions form waiting cycle.
Deadlock Prevention
• Remove mutual exclusion
• Remove hold and wait
• Allow preemption
• Prevent circular wait
18. Recovery Schemes
Recovery
Restoring database after failure.
Need
• Maintain consistency
• Recover lost data
• Handle crashes
Types of Recovery Schemes
1. Log-Based Recovery
All operations stored in log file.
18
Log contains:
• Transaction ID
• Old value
• New value
Types
Deferred Update
Database updated after commit.
Immediate Update
Database updated before commit. Undo may be required.
Undo
Restores old value.
Redo
Reapplies committed changes.
2. Shadow Paging
Maintains:
• Current page
• Shadow copy
Old page remains safe.
Advantages
• Fast recovery
• Easy rollback
• No undo/redo needed
Disadvantages
• Extra memory required
• Complex page management
Advantages of Recovery Schemes
• Protects database
19
• Maintains consistency
• Improves reliability
20