Database Management System - Complete Notes

SE IT/SEM-III/DSE
Vasantdada Patil College of Engineering

Module 1: Introduction to Database Management System

1.1 Basic Concepts

What is Data?
Data: Collection of raw facts and figures (text, image, audio, video)
Information: Result of data processing - sorted or calculated data
Database: Coherent collection of data with inherent meaning, representing real-world
aspects

What is DBMS?
Database Management System: General purpose software system that defines, constructs,
and manipulates databases
Acts as interface between users and database
Ensures data consistency, organization, and accessibility

1.2 Characteristics of DBMS

1. Self-Describing Nature
Contains catalog with complete database definition
Meta-data stored separately from access programs
2. Insulation between Program & Data (Data Abstraction)
Program-data independence
Program-operation independence
Structure of data files stored separately in database catalog
3. Support of Multiple Views
Different views as per user needs
Views are subsets of data
4. Sharing of Data and Multi-user Transaction Processing
Multi-user DBMS with concurrency control
 Supports simultaneous access by multiple users

1.3 DBMS vs File System

Feature DBMS File System

Data Management Automatic with SQL/queries Manual code required

Security Advanced user roles & permissions Basic OS-level security

Data Redundancy Low (normalization) High

Query Support Yes (SQL) No built-in support

Concurrency Control Built-in locking mechanisms Difficult to manage

Backup/Recovery Automatic features Manual procedures

Integrity Constraints Built-in support Must be coded manually

Data Relationships Easy with relational models Hard to implement

1.4 Data Abstraction and Data Independence

Levels of Data Abstraction:

1. Physical Level (Lowest)
How data is stored (data structures, file systems)
Physical storage details
2. Logical Level
What data is stored and relationships
Tables, attributes, data types, constraints
3. View Level (Highest)
Part of database for particular users
User interaction through queries and forms

Data Independence:
Physical Data Independence: Modify physical storage without affecting logical schema
Logical Data Independence: Change logical schema without affecting external schema

1.5 Database Architecture Components

Storage Manager
Manages physical storage of data
Handles file organization and indexing

Query Processor
Interprets and executes database queries
Optimizes query performance

1.6 Database Users and Administrators

Types of Users:
1. Naive Users - End users with simple interface needs
2. Application Programmers - Develop database applications
3. Sophisticated Users - Use complex queries and analysis tools
4. Specialized Users - Domain-specific applications

Database Administrator (DBA) Functions:

Schema Definition: Define database structure using DDL
Data Access Control: Grant permissions and authorization
Storage Structure Definition: Manage physical storage
Routine Maintenance: Backup, recovery, monitoring
Performance Tuning: Optimize database performance
Security Management: Implement security measures

1.7 Applications of DBMS

Banking Systems:
Customer data management
Account management and transaction processing
Loan management
Fraud detection and prevention
Regulatory compliance
E-Commerce:
Product information management
Customer relationship management
Order processing and inventory management
Marketing and business analysis
Security and access control

Module 2: Entity-Relationship (ER) Model

2.1 Basic Components of ER Model

Entities
Entity: Real-world object distinguishable from others
Entity Set: Collection of similar entities sharing same attributes
Strong Entity: Independent existence (single rectangle)
Weak Entity: Depends on strong entity (double rectangle)

Attributes
1. Simple Attributes: Atomic, indivisible values
2. Composite Attributes: Combination of multiple attributes
3. Multi-valued Attributes: Multiple values for single entity
4. Derived Attributes: Values derived from other attributes

Relationships
Relationship: Association among entities
Relationship Set: Collection of similar relationships
Descriptive Attributes: Attributes of relationships

2.2 Keys in ER Model

Types of Keys:
1. Super Key: Set of attributes that uniquely identify entity
2. Candidate Key: Minimal super key
3. Primary Key: Selected candidate key
4. Foreign Key: References primary key of another relation
5. Composite Key: Primary key with multiple attributes
 6. Alternate Key: Candidate key not chosen as primary
7. Unique Key: Similar to primary key but allows one NULL

2.3 Relationship Constraints

Mapping Cardinality:
1. One-to-One (1:1): Single entity associated with single entity
2. One-to-Many (1:M): One entity associated with multiple entities
3. Many-to-One (M:1): Multiple entities associated with one entity
4. Many-to-Many (M:M): Multiple entities associated with multiple entities

Participation Constraints:
1. Total Participation: Every entity must participate (double line)
2. Partial Participation: Some entities may not participate (single line)

2.4 ER Diagram Symbols

Entity: Rectangle
Weak Entity: Double rectangle
Attribute: Oval
Multi-valued Attribute: Double oval
Derived Attribute: Dashed oval
Relationship: Diamond
Weak Relationship: Double diamond

2.5 Extended ER (EER) Model

Additional Concepts:
Specialization: Top-down approach, creating subclasses
Generalization: Bottom-up approach, creating superclass
Aggregation: Treating relationship as higher-level entity
Composition: Strong form of aggregation
Module 3: Relational Model & Relational Algebra

3.1 Relational Model Concepts

Basic Terminology:
Relation: Two-dimensional table storing data
Tuple: Row representing real-world entity
Attribute/Field: Column describing entity properties
Domain: Set of permitted values for attribute
Degree: Number of attributes in relation
Cardinality: Number of tuples in relation

Relational Schema:

TABLE_NAME(ATTRIBUTE_1 TYPE_1, ATTRIBUTE_2 TYPE_2, ...)

Example: STUDENT(ROLL_NUMBER INTEGER, NAME VARCHAR(20), CGPA FLOAT)

3.2 Integrity Constraints

1. Domain Constraints
Restrict type of values a column can hold
Define data types: string, integer, character, DateTime

2. Entity Integrity Constraints

Primary key cannot be NULL
Ensures unique identification of records

3. Referential Integrity Constraints

Foreign key must reference valid primary key
Maintains valid relationships between tables

4. Key Constraints
Primary key contains unique, non-null values
Ensures entity uniqueness
3.3 Mapping ER to Relational Schema

Step 1: Strong Entities

Create table for each strong entity
Include all simple attributes
Choose primary key

Step 2: Weak Entities

Create table including all simple attributes
Add foreign key from owner entity
Primary key = owner's primary key + weak entity's key

Step 3: 1:1 Relationships

Choose relation with total participation
Add foreign key and relationship attributes

Step 4: 1:N Relationships

Add foreign key to N-side relation
Include relationship attributes

Step 5: M:N Relationships

Create new relation
Include primary keys of both entities as foreign keys
Add relationship attributes

Step 6: Multi-valued Attributes

Create separate relation
Include entity's primary key as foreign key
Add multi-valued attribute

3.4 Binary Relational Operations

Set Operations:
1. Union (∪): Combines tuples from two relations
2. Intersection (∩): Common tuples in both relations
3. Difference (-): Tuples in first but not in second
 4. Cartesian Product (×): All possible combinations

Relational Operations:
1. Selection (σ): Selects tuples satisfying condition
2. Projection (π): Selects specific attributes
3. Join: Combines relations based on common attributes
4. Division: Finds tuples related to all tuples in another relation

Join Types:
Natural Join: Based on common attributes
Theta Join: Based on condition
Equijoin: Theta join with equality condition
Outer Join: Includes unmatched tuples (LEFT, RIGHT, FULL)

Important Exam Topics Summary

Module 1 Key Points:

✅ DBMS characteristics and advantages
✅ DBMS vs File System comparison
✅ Data abstraction levels

✅ DBA roles and responsibilities

✅ Database architecture components

Module 2 Key Points:

✅ ER diagram components and symbols
✅ Entity types and attribute types
✅ Key definitions with examples

✅ Relationship cardinalities
✅ Participation constraints
✅ EER model concepts

Module 3 Key Points:

✅ Relational model terminology
✅ Integrity constraints types

✅ ER to Relational mapping steps

✅ Binary relational operations
 ✅ Join operations and types

Common Exam Questions Pattern

Short Answer Questions (2-5 marks):

1. Define Primary Key and Foreign Key with examples
2. Define total participation with example
3. What are basic characteristics of databases?
4. Explain different types of attributes in ER model

Long Answer Questions (5+ marks):

1. Draw and explain DBMS system architecture
2. Draw ER diagram for given scenario (Library Management System)
3. Explain Extended Entity-Relationship (EER) Model
4. Explain binary relational operations in relational algebra
5. Map ER diagram to relational schema

Practical Tips:
Practice drawing ER diagrams for different scenarios
Memorize key definitions and examples
Understand mapping process from ER to relational schema
Practice relational algebra operations
Focus on real-world applications of concepts

Good Luck with your IAI Examination! 🎯