BCA-501 Introduction to DBMS

UNIT-I Introduction:

Characteristics of database approach, data models, DBMS architecture and data

independence.

UNIT-II E-R Modeling:

Entity types, Entity set, attribute and key, relationships, relation types, roles and
structural constraints, weak entities, enhanced E-R and object modeling, Sub
classes; Super classes, inheritance, specialization and generalization.

UNIT-III File Organization:

Indexed sequential access files; implementation using B & B++ trees, hashing,
hashing functions, collision resolution, extendible hashing, dynamic hashing
approach implementation and performance.

UNIT-IV Relational Data Model:

Relational model concepts, relational constraints, relational alzebra SQL: SQL

queries, programming using SQL.

UNIT-V EER and ER to relational mapping:

Data base design using EER to relational language.

UNIT-VI Data Normalization:

Functional Dependencies, Normal form up to 3rd normal form. Concurrency

Control: Transaction processing, locking techniques and associated, database
recovery, security and authorization. Recovery Techniques, Database Security
UNIT-I: Introduction to DBMS

1. Characteristics of Database Approach

A database approach involves organizing and managing data using a database management
system (DBMS), which provides several key advantages over traditional file systems. Here are
the characteristics explained in detail:

1. Self-Describing Nature of a Database System

○ A database contains not only the data but also the metadata, which describes the
structure, types, and constraints of the data.
○ Example: If a "Student" database contains a "Name" field, the metadata will store
information about the data type (e.g., VARCHAR) and length constraints (e.g., 50
characters).
2. Program-Data Independence
○ Changes in how data is stored (physical layer) do not require changes in
application programs that access the data. This ensures flexibility and reduces
maintenance costs.
○ Example: Changing the database storage from a local file system to a cloud
database does not require modifying the programs accessing the data.
3. Data Abstraction
○ The database system provides multiple levels of abstraction:
■ Physical Level: Details of how data is stored.
■ Logical Level: Structure of the database and relationships.
■ View Level: User-specific views.
○ Example: A user interacting with a "Products" database may only see product
names and prices without knowing the underlying storage mechanism.
4. Multi-User Access
○ Allows multiple users to access and modify the database simultaneously while
maintaining consistency.
○ Example: In an online shopping system, several customers can browse and
place orders at the same time.
5. Data Integrity and Consistency
○ Enforces rules (e.g., constraints) to maintain data correctness and accuracy.
 ○ Example: A "StudentID" field might enforce uniqueness, ensuring no duplicate
entries.
6. Security
○ Access is controlled through user authentication and role-based permissions.
○ Example: A database admin can access all tables, but a sales employee may
only view customer orders.
7. Backup and Recovery
○ DBMS provides tools for automatic backup and recovery to protect data from
accidental loss or corruption.
○ Example: A crash recovery system ensures the database is restored to its last
consistent state.

2. Data Models

Data models define how data is structured, stored, and manipulated within a database system.
There are several types of data models:

1. Hierarchical Data Model

○ Organizes data in a tree-like structure with parent-child relationships.
○ Advantages:
■ Simple to implement and understand.
■ Efficient for 1:N relationships.
○ Disadvantages:
■ Rigid structure makes it hard to adapt to changes.
○ Example: An organization structure where a manager (parent) has several
employees (children).
2. Network Data Model
○ Data is represented as a graph with nodes (entities) and edges (relationships).
○ Advantages:
■ Supports complex relationships like M:N.
○ Disadvantages:
■ Complex to design and implement.
○ Example: Airline routes between cities.
3. Relational Data Model
○ Represents data in tables with rows (tuples) and columns (attributes).
○ Advantages:
 ■ Simple and widely used.
■ Strong mathematical foundation (relational algebra).
○ Example: A "Student" table with fields such as "StudentID," "Name," and "Age."
4. Object-Oriented Data Model
○ Represents data as objects with attributes (properties) and methods (behaviors).
○ Advantages:
■ Ideal for multimedia and complex data types.
○ Example: A "Car" object with attributes like "Color" and methods like "Drive."

3. DBMS Architecture

The architecture of a DBMS defines how the system is designed to manage data, process
requests, and provide access to users.

1. Three-Level Architecture:
○ A layered approach that provides data abstraction:
■ Internal Level:
■ Details physical storage (e.g., how records are placed on disk).
■ Conceptual Level:
■ Represents the logical structure of the database.
■ Example: Relations, attributes, constraints.
■ External Level:
■ Tailored user views of the data.
■ Example: A report showing only sales data for a specific region.
2. Client-Server Architecture:
○ Client: User-facing application that sends requests.
○ Server: Processes requests and returns results.
○ Example: An online shopping website where the client (browser) requests
product details from the server (database).
3. Centralized vs. Distributed DBMS:
○ Centralized DBMS:
■ Data is stored and managed on a single server.
■ Example: A university database hosted on a mainframe.
○ Distributed DBMS:
■ Data is distributed across multiple locations but appears unified to the
user.
 ■ Example: Global banking systems.

4. Data Independence

Data independence refers to the ability to modify the database schema without affecting the
applications that use the database.

1. Logical Data Independence:

○ Changes in the conceptual schema (e.g., adding a new table) do not affect
external schemas or application programs.
○ Example: Adding a new "Phone Number" field to a "Customer" table.
2. Physical Data Independence:
○ Changes in the internal schema (e.g., changing file storage format) do not affect
the conceptual schema or application programs.
○ Example: Migrating from a local disk to a cloud-based storage system.

Importance of Data Independence:

● Simplifies database maintenance and updates.

● Reduces the risk of application disruptions.
UNIT-II: E-R Modeling

Entity-Relationship (E-R) modeling is a foundational step in database design. It uses a

high-level conceptual data model to describe data and its relationships.

1. Entity Types and Entity Sets

1. Entity:
○ An object in the real world that can be identified uniquely and stored as data.
○ Example: A "Student" in a university database.
2. Entity Types:
○ A collection of similar entities.
○ Example: "Student" is an entity type, while individual students like "John" or
"Priya" are entities.
3. Entity Set:
○ A collection of all entities of a particular entity type at any point in time.
○ Example: All students currently enrolled in a course.

2. Attributes and Keys

1. Attributes:
○ Properties or characteristics of an entity.
○ Example: A "Student" entity might have attributes like "StudentID," "Name," and
"Age."
2. Types of Attributes:
○ Simple Attributes: Cannot be divided further.
■ Example: "Age."
○ Composite Attributes: Can be divided into smaller parts.
■ Example: "Full Name" divided into "First Name" and "Last Name."
○ Derived Attributes: Values derived from other attributes.
■ Example: "Age" calculated from "Date of Birth."
○ Multivalued Attributes: Can have multiple values.
■ Example: "Phone Numbers."
 3. Keys:
○ Attributes that uniquely identify an entity in an entity set.
■ Primary Key: Unique and mandatory attribute for each entity.
Example: "StudentID."
■ Candidate Key: All attributes that could serve as a primary key.
■ Composite Key: A combination of two or more attributes that uniquely
identify an entity.
Example: "CourseCode" + "Semester."

3. Relationships and Relationship Types

1. Relationship:
○ A logical connection between entities.
○ Example: "Enrolls" relationship between "Student" and "Course."
2. Relationship Types:
○ Unary (Self-Relationship): Entities of the same type relate to each other.
■ Example: "Employee" supervises another "Employee."
○ Binary Relationship: Two entities are related.
■ Example: "Student" enrolls in "Course."
○ Ternary Relationship: Involves three entities.
■ Example: "Student" gets "Grades" in a "Course."

4. Roles and Structural Constraints

1. Roles:
○ Explain how entities participate in a relationship.
○ Example: In a "WorksFor" relationship, "Employee" and "Department" have roles
like "Worker" and "Employer."
2. Structural Constraints:
○ Cardinality: Minimum and maximum number of times an entity participates in a
relationship.
■ Example: "One-to-Many (1:N):" One teacher teaches many students.
○ Participation Constraints:
 ■ Total Participation: All entities must participate.
Example: Each "Student" must enroll in at least one "Course."
■ Partial Participation: Some entities may not participate.
Example: Some "Professors" might not supervise "Projects."

5. Weak Entities

1. Definition:
○ Entities that cannot be uniquely identified without a related strong entity.
○ Example: "Dependent" in a "Dependent-BelongsTo-Employee" relationship.
2. Characteristics of Weak Entities:
○ Lack a primary key.
○ Depend on a strong entity for identification.
○ Use a discriminator (partial key) to identify entities within the same weak entity
set.
○ Have a total participation constraint.

6. Enhanced E-R and Object Modeling

Enhanced E-R (EER) modeling extends the basic E-R model to support advanced concepts.

1. Subclasses and Superclasses:

○ Superclass: Represents a general entity type.
■ Example: "Vehicle."
○ Subclass: Represents a specialized entity type derived from a superclass.
■ Example: "Car" and "Bike."
2. Inheritance:
○ Subclasses inherit attributes and relationships from the superclass.
○ Example: "Car" inherits "EngineType" and "Number of Wheels" from "Vehicle."
3. Specialization and Generalization:
○ Specialization: Process of defining subclasses from a superclass.
■ Example: "Employee" specialized into "Manager" and "Engineer."
○ Generalization: Combining multiple entity types into a single superclass.
■ Example: "Car" and "Bike" generalized into "Vehicle."
7. Example of EER Diagram

Let's design a simplified example:

1. Entities:
○ Student (StudentID, Name, Age)
○ Course (CourseID, Title, Credits)
2. Relationships:
○ Enrolls (StudentID, CourseID, Grade)
3. Enhanced Features:
○ Specialization of "Student" into "Undergraduate" and "Graduate."
UNIT-III: File Organization

File organization refers to the method of arranging data in files on a storage medium to ensure
efficient data storage, retrieval, and manipulation.

1. Indexed Sequential Access Files (ISAM)

1. Definition:
○ A hybrid of sequential and direct access methods.
○ Files are organized sequentially, and an index is maintained for quick access.
2. Structure:
○ Index Table: Contains keys and pointers to the actual records.
○ Data Blocks: Store the actual data in sequential order.
3. Advantages:
○ Faster searches due to indexing.
○ Supports sequential and random access.
4. Example:
○ A phone directory where names are stored alphabetically, and an index table
helps locate a specific name quickly.

2. Implementation Using B-Trees and B++ Trees

1. B-Tree:
○ A self-balancing tree structure used for database indexing.
○ Ensures all leaf nodes are at the same level.
○ Keys and data are stored in the nodes.
2. B++ Tree:
○ An extension of B-Tree.
○ Data is stored only in leaf nodes, and internal nodes store only keys for
navigation.
3. Features:
○ Balanced height for efficient searches, insertions, and deletions.
○ Supports sequential access due to linked leaf nodes (in B++ Trees).
 4. Applications:
○ Database indexing and file systems.

3. Hashing

1. Definition:
○ A technique to map data to a fixed-size key (hash code) using a hashing function.
2. Hashing Functions:
○ A mathematical formula that converts input data into a hash code.
○ Example: hash(key) = key % table_size
3. Collision:
○ Occurs when two keys generate the same hash code.

4. Collision Resolution Techniques

1. Separate Chaining:
○ Store collided keys in a linked list or bucket at the hashed location.
2. Open Addressing:
○ Linear Probing: Check the next available slot.
○ Quadratic Probing: Check slots at intervals of squares of numbers.
○ Double Hashing: Use a secondary hashing function.

5. Extendible Hashing

1. Definition:
○ A dynamic hashing technique that adjusts the hash table size based on the data
volume.
2. Implementation:
○ Uses a directory of pointers to buckets.
○ Hash function generates binary values, and the directory adjusts based on data
growth.
3. Advantages:
○ Efficient storage utilization.
 ○ Handles dynamic data growth without performance loss.

6. Dynamic Hashing

1. Definition:
○ A flexible hashing technique that grows and shrinks dynamically with data.
○ Used to address limitations of static hashing.
2. Features:
○ Hash table size increases with data volume.
○ Avoids overflow by redistributing records into new buckets.
3. Applications:
○ Databases with frequently changing data sizes.

7. Performance of Hashing

1. Evaluation Criteria:
○ Search Time: Average time to locate a record.
○ Storage Utilization: Efficient use of storage space.
○ Collision Handling: Effectiveness of the chosen collision resolution technique.
2. Factors Affecting Performance:
○ Quality of the hashing function.
○ Load factor (ratio of filled slots to total slots).
○ Handling of collisions.
UNIT-IV: Relational Data Model

The relational data model is a widely used model that organizes data into tables (relations) with
rows and columns.

1. Relational Model Concepts

1. Relation (Table):
○ A two-dimensional structure with rows (tuples) and columns (attributes).

Example:

Student Table:
+-----------+--------+-----+
| StudentID | Name | Age |
+-----------+--------+-----+
| 101 | John | 21 |
| 102 | Priya | 22 |
+-----------+--------+-----+

○
2. Tuple:
○ A single row in a table representing a single entity instance.
○ Example: (101, John, 21) is a tuple in the "Student" table.
3. Attribute:
○ A column in a table representing a property of the entity.
○ Example: "Name" is an attribute of the "Student" table.
4. Domain:
○ The set of all possible values for an attribute.
○ Example: The domain of the "Age" attribute is all integers between 18 and 60.
5. Relation Schema:
○ A description of a relation’s structure, including its name, attributes, and domains.
○ Example: Student(StudentID, Name, Age)
6. Relation Instance:
 ○ A snapshot of the relation at a given point in time, i.e., the actual data in the
table.

2. Relational Constraints

1. Domain Constraints:
○ Ensure attribute values fall within the defined domain.
○ Example: "Age" should be an integer between 18 and 60.
2. Key Constraints:
○ Primary Key: Uniquely identifies each tuple in a relation.
■ Example: "StudentID" in the "Student" table.
○ Candidate Key: Attributes that can serve as a primary key.
○ Foreign Key: An attribute in one relation that references a primary key in another
relation.
■ Example: "CourseID" in the "Enrollment" table references "CourseID" in
the "Course" table.
3. Integrity Constraints:
○ Entity Integrity: Primary keys cannot have NULL values.
○ Referential Integrity: Foreign key values must match primary key values in the
referenced table.
4. Other Constraints:
○ User-defined constraints like "GPA must be between 0 and 4."

3. Relational Algebra

1. Definition:
○ A procedural query language that uses operations to retrieve and manipulate
data from relations.
2. Basic Operations:
○ Selection (σ): Filters rows based on a condition.
■ Example: σ Age > 20 (Student) retrieves students older than 20.
○ Projection (π): Selects specific attributes.
■ Example: π Name, Age (Student) retrieves only the "Name" and
"Age" columns.
 ○ Union (∪): Combines tuples from two relations.
○ Intersection (∩): Returns tuples common to both relations.
○ Difference (-): Returns tuples in one relation but not in another.
○ Cartesian Product (×): Combines tuples from two relations.
○ Join (⋈): Combines related tuples from two relations based on a condition.

4. SQL (Structured Query Language)

1. Definition:
○ A standard programming language for managing and querying relational
databases.
2. Basic SQL Queries:
○ SELECT Statement:

Retrieve specific data from a table.

SELECT Name, Age FROM Student WHERE Age > 20;

○ INSERT Statement:

Add new records to a table.

INSERT INTO Student (StudentID, Name, Age) VALUES (103, 'Alice', 23);

○ UPDATE Statement:

Modify existing records.

UPDATE Student SET Age = 22 WHERE StudentID = 101;

○ DELETE Statement:

Remove records.

DELETE FROM Student WHERE Age < 21;

3. SQL Programming:
○ Joins:
Combine rows from multiple tables.

SELECT Student.Name, Course.Title

FROM Student
JOIN Enrollment ON Student.StudentID = Enrollment.StudentID
JOIN Course ON Enrollment.CourseID = Course.CourseID;

○ Group By and Aggregates:

Perform operations like COUNT, AVG, MAX, MIN.

SELECT AVG(Age) AS AverageAge FROM Student;

○ Nested Queries:

Use a query inside another query.

SELECT Name FROM Student WHERE Age = (SELECT MAX(Age) FROM Student);

UNIT-V: EER and ER to Relational Mapping

This unit focuses on extending the concepts of the Entity-Relationship (ER) model and
transforming it into a relational schema for database implementation.

1. EER (Enhanced Entity-Relationship) Model

The Enhanced Entity-Relationship model builds on the basic ER model by introducing additional
concepts to represent complex relationships and hierarchical structures.

Key Concepts of the EER Model:

1. Subclasses and Superclasses:

○ Superclass: A general entity type with common attributes.
○ Subclass: A specialized entity type that inherits attributes from its superclass.
○ Example:
■ Superclass: Person
■ Subclasses: Student, Teacher
2. Inheritance:
○ Subclasses inherit attributes and relationships from their superclass.
○ Ensures reusability and consistency in design.
○ Example:
■ Student inherits the Name and Address attributes of Person.
3. Specialization:
○ The process of defining a subclass from a superclass by identifying specific
attributes or relationships.
○ Example:
■ Superclass: Vehicle
■ Subclasses: Car, Truck
4. Generalization:
○ The reverse process of specialization, where common attributes of two or more
entities are combined into a superclass.
○ Example:
■ Subclasses: Doctor, Nurse
■ Superclass: MedicalStaff
 5. Categories (Union Types):
○ Represents a single superclass that may be related to one or more subclasses.
○ Useful when an entity can belong to multiple classes.
6. Aggregation:
○ Represents a “whole-part” relationship where a higher-level entity is made up of
multiple lower-level entities.
○ Example:
■ Entity: Project
■ Aggregation: Team is an aggregation of Employees.

2. ER to Relational Mapping

Converting an ER or EER diagram into a relational schema involves a step-by-step process to

ensure all entities and relationships are correctly represented in tables.

Steps in ER to Relational Mapping:

1. Mapping Regular Entities:

○ Create a table for each regular entity.
○ Include all attributes of the entity as columns in the table.
○ Identify a primary key.
○ Example:
■ Entity: Student(StudentID, Name, Age)
2. Mapping Weak Entities:
○ Create a table for the weak entity.
○ Include a foreign key referencing the primary key of the owner entity.
○ The primary key of the weak entity includes the foreign key.
○ Example:
■ Entity: Dependent(DependentID, Name, EmployeeID) (with
EmployeeID as a foreign key).
3. Mapping Relationships:
○ One-to-One Relationship:
■ Add the primary key of one entity as a foreign key in the other entity.
○ One-to-Many Relationship:
 ■ Add the primary key of the “one” side as a foreign key in the “many” side.
○ Many-to-Many Relationship:
■ Create a new table to represent the relationship.
■ Include the primary keys of both entities as foreign keys in the new table.
■ Example:
■ Relationship: Enrollment
■ Table: Enrollment(StudentID, CourseID,
EnrollmentDate)
4. Mapping Subclasses and Superclasses:
○ Option 1: Create separate tables for the superclass and subclasses.
■ Superclass table contains common attributes.
■ Subclass tables contain specific attributes and a foreign key referencing
the superclass.
○ Option 2: Use a single table for both superclass and subclasses with NULL
values for unused attributes.
5. Mapping Aggregation:
○ Treat aggregation as a relationship and map it like any other relationship.

Example ER to Relational Mapping:

ER Diagram:

● Entities: Student(StudentID, Name, Age), Course(CourseID, Title)

● Relationship: Enrollment(Student, Course, EnrollmentDate)

Relational Schema:

● Student(StudentID, Name, Age)

● Course(CourseID, Title)
● Enrollment(StudentID, CourseID, EnrollmentDate)

UNIT-VI: Data Normalization and Concurrency Control

1. Data Normalization

Normalization is a process of organizing data in a database to minimize redundancy and

dependency. It divides large tables into smaller ones and defines relationships between them,
improving data consistency and efficiency.

Key Concepts of Normalization

1. Functional Dependencies:
○ A relationship where one attribute uniquely determines another.
○ Example: StudentID → Name, Age
■ Knowing StudentID determines the Name and Age.
2. Normal Forms:
○ Different levels of database normalization, each addressing specific types of
redundancy.

Normal Forms

1. First Normal Form (1NF):

○ Ensures that all columns contain atomic (indivisible) values.
○ No repeating groups or arrays are allowed.

Example:
Non-1NF Table:
+-----------+-------+-----------+
| StudentID | Name | Subjects |
+-----------+-------+-----------+
| 101 | John | Math, Eng |
+-----------+-------+-----------+
1NF Table:
+-----------+-------+----------+
| StudentID | Name | Subject |
+-----------+-------+----------+
| 101 | John | Math |
| 101 | John | Eng |
+-----------+-------+----------+

2. Second Normal Form (2NF):

○ Must be in 1NF and have no partial dependency (non-prime attributes depend on
part of a composite key).

Example:
Non-2NF Table:
+-----------+----------+-------+
| StudentID | CourseID | Name |
+-----------+----------+-------+
| 101 | C1 | John |
| 102 | C2 | Priya |
+-----------+----------+-------+
2NF Table:
+-----------+----------+
| StudentID | Name |
+-----------+----------+
| 101 | John |
| 102 | Priya |
+-----------+----------+

3. Third Normal Form (3NF):

○ Must be in 2NF and have no transitive dependency (non-prime attributes depend
on other non-prime attributes).

Example:
Non-3NF Table:
+-----------+-------+--------+
| StudentID | Name | Dept |
+-----------+-------+--------+
| 101 | John | CS |
| 102 | Priya | IT |
+-----------+-------+--------+
3NF Table:
+-----------+-------+
| StudentID | Dept |
+-----------+-------+
| 101 | CS |
| 102 | IT |
+-----------+-------+

2. Concurrency Control

Concurrency control ensures multiple transactions can occur simultaneously in a database

without conflicts.

Key Concepts of Concurrency Control

1. Transaction Processing:
○ A transaction is a sequence of database operations treated as a single logical
unit.
○ Properties of transactions (ACID):
■ Atomicity: Transactions are all or nothing.
■ Consistency: Transactions maintain database integrity.
■ Isolation: Concurrent transactions do not interfere with each other.
■ Durability: Once committed, changes persist even if the system fails.
2. Locking Techniques:
○ Prevents concurrent access conflicts.
○ Types of locks:
■ Shared Lock (S): Multiple transactions can read data but not modify it.
■ Exclusive Lock (X): Only one transaction can read or write the data.
○ Two-phase locking (2PL):
■ Growing phase: Locks are acquired.
■ Shrinking phase: Locks are released.
3. Deadlocks:
 ○ Occur when two or more transactions wait for each other to release locks.
○ Resolution Strategies:
■ Timeout
■ Deadlock detection and recovery
4. Database Recovery:
○ Ensures data integrity in case of failure.
○ Types of recovery:
■ Deferred Update: Changes are applied only after the transaction
commits.
■ Immediate Update: Changes are applied as the transaction executes,
but logs ensure recovery.
5. Database Security and Authorization:
○ Protects data from unauthorized access and manipulation.
○ Techniques:
■ User authentication (passwords, biometrics)
■ Access control (GRANT and REVOKE in SQL)
■ Encryption for secure data transmission.