1.

0 Introduction to Database and Data Modeling

Abhishek kumar
January 2026

1 Introduction
1.1 What is Data ?
It is a collection of unprocessed information . data is the organized collection of facts, figures, text,
numbers, images, or any relevant information, stored in a structured format (like tables with rows and
columns) that can be easily accessed, managed, and updated.

• Numbers Data: 50, 60, 70

• Name Data: Abhishek, Alice , Bob,
• DOB Data: 10-01-2026

1.2 Data Base Management System

The Database Management System (DBMS) is the software that enables to store, modify, and extract
data from a database. If Processes users requests for data retrieval, updation , or deletion and also
maintains the integrity of the database.

1.3 Entity-Relationship (ER) Model

The Entity-Relationship (ER) Model is a conceptual framework used in database design to visually
represent the structure of a database. It defines the entities (objects), their attributes (properties),

1.4 Types of ER Model

Strong ER Model
• Entity has its own primary key
• Exists independently
• Example: Employee, Customer

Weak ER Model
• Depends on another entity
• Has no primary key of its own

• Example: Dependent (depends on Employee)

1
2 Data Base Management System
2.1 Definition :
The Database Management System (DBMS) is the software that enables to store, modify, and extract
data from a database. If Processes users requests for data retrieval, updation , or deletion and also
maintains the integrity of the database.

Examples of Database Applications

Automated Teller Machines (ATM)
• Cash withdrawal
• Balance inquiry
• Fund transfer

• Account management

Computerized Library Systems

• Book management

• Member records
• Issue and return tracking
• Search facility

Flight Reservation Systems

• Flight schedules
• Ticket booking

• Seat availability
• Passenger records

Computerized Parts Inventory Systems

• Parts management
• Stock tracking
• Supplier details
• Order management

Database Management System

• Data storage

• Data retrieval
• Data update
• Data integrity

2
Advantages of Using a Database
1. Redundancy can be controlled: Duplicate data is reduced, so the same data is not stored again
and again. This saves storage space and avoids data inconsistency.
2. Data can be shared: Multiple users can access and use the same data at the same time. This
improves collaboration and efficiency in an organization.
3. Security can be applied: Only authorized users are allowed to access the data using usernames
and passwords. This helps protect sensitive information.
4. Integrity can be maintained: Data remains accurate, consistent, and reliable by applying rules
and constraints in the database.

tikz

Components of a DBMS

Data Access Database Data CatalogManagement

Storage Management

Language Interface

Security Management

Recovery Management

Concurrency Control

Transaction Management

Applications

3
Examples of Three-Level Architecture Views
1. External Level (User View)
• Student sees only name, roll number, and marks.
• Teacher sees student name, subject, and attendance.
• Librarian sees book name and issue status.
2. Conceptual Level (Logical View)
• Tables: Student, Teacher, Course, Library.
• Relationships between Student and Course.
• Constraints like primary key and foreign key.
3. Internal Level (Physical View)
• Data stored in files on disk.
• Indexing on roll number for fast search.
• Records stored using B-tree or hash structure.

Three-Level Architecture of a Database System

External View (Users)

Student Teacher Librarian

Conceptual Level (Logical Database Schema)

Internal Level (Physical Storage)

4
2.2 Data Models - An Introduction
The term ”data model” refers to a structured framework that defines how data is organized, stored,
accessed, and managed within a system or database. It provides a blueprint for representing real-
world entities, their relationships, and constraints. Data models are essential for ensuring consistency,
efficiency, and clarity in data handling.

Types of Data Models

Graphical Representation of Database Models

Hierarchical Model

CEO

VP Eng VP Mkt

Team A Team B

Network Model

Student 1 Course 1

Student 2 Course 2

Relational Model Example

Author Table
AuthorID Name
A101 J.K. Rowling
A102 Chetan Bhagat
A103 Ruskin Bond

Book Table
ISBN Title AuthorID
B201 Harry Potter A101
B202 Biswadip Pal A102
B203 The Blue Umbrella A103

5
Object-Oriented Model

Car
VIN
Model
startEngine()

ElectricCar
BatteryCapacity
charge()

6
3 ER Diagram
The Entity-Relationship (ER) Model is a conceptual framework used in database design to visually
represent the structure of a database. It defines the entities (objects), their attributes (properties),

3.1 Entities
In Database Management Systems (DBMS), an entity is a real-world object or concept that is repre-
sented and stored in a database. Entities are typically modeled as tables in relational databases or as
documents/collections in NoSQL databases.

3.1.1 Entity
An entity is a real-world object such as a person, place, thing, or event about which data is stored.
Example: In a university database, entities include:
• Student
• Course

• Professor

3.1.2 Entity in Relational Database

In a relational database, an entity is represented as a table (relation) with rows (tuples) and columns
(attributes).
Example: Student Entity

StudentID Name Age Department

101 Rahul 20 CSE
102 Anita 21 IT
103 Suresh 22 CSE(AIML)

3.1.3 Entity in ER Model

In ER diagrams, an entity is represented using a rectangle.

3.1.4 Strong Entity

A strong entity has its own primary key.
Example: Employee

EmpID Name

Employee

Explanation: Employee is a strong entity because it has its own primary key (EmpID).

3.1.5 Weak Entity

A weak entity depends on a strong entity for identification.
Example: Dependent depends on Employee

7
 Has

EmpID DepName

Employee Dependent

Explanation: Dependent is a weak entity because it cannot be uniquely identified without Employee.

3.1.6 Summary
• Entity represents a real-world object.

• In relational databases, entity is a table.

• In ER model, entity is shown as a rectangle.
• Strong entity has its own primary key.
• Weak entity depends on a strong entity.

8
3.2 Attribute
In Database Management Systems (DBMS), an attribute is a property or characteristic that describes
an entity. An attribute is represented as a column (field) in a table.
Example: Entity: Employee Attributes: EmpID, Name, Salary, Department

3.2.1 Types of Attributes

(a) Simple and Composite Attributes
Simple (Atomic) Attribute:
• Cannot be divided further.

• Examples: Age, Salary

Composite Attribute:
• Can be divided into sub-attributes.

• Examples:
– Address → Street, City, State
– Name → FirstName, LastName

(b) Single-Valued and Multi-Valued Attributes

Single-Valued Attribute:

• Stores only one value.

• Examples: EmpID, DateOfBirth
Multi-Valued Attribute:
• Can store multiple values.

• Examples: PhoneNumbers, Skills

(c) Stored and Derived Attributes

Stored Attribute:
• Stored directly in the database.

• Example: DateOfBirth
Derived Attribute:
• Calculated from other attributes.
• Example: Age (derived from DateOfBirth)

(d) Key Attribute

A key attribute uniquely identifies an entity.
Example:
• EmpID (Primary Key)
• SSN

(e) Null Attribute

Represents missing or unknown value.
Example: MiddleName = NULL

9
3.2.2 Representation of Attributes in ER Diagram
• Attribute → Oval
• Key Attribute → Underlined
• Multi-Valued Attribute → Double Oval
• Derived Attribute → Dashed Oval

3.2.3 ER Diagram Example

FirstName LastName

Name
EmpID PhoneNumbers

Employee

Age

3.2.4 Relational Database Example

CREATE TABLE Employee (
EmpID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
DateOfBirth DATE,
Salary DECIMAL(10,2),
Department VARCHAR(50)
);

3.2.5 Handling Multi-Valued Attributes

Since relational databases do not support multi-valued attributes directly, they are normalized.
CREATE TABLE Employee_PhoneNumbers (
EmpID INT,
PhoneNumber VARCHAR(20),
FOREIGN KEY (EmpID) REFERENCES Employee(EmpID)
);

3.2.6 Summary Table

Type Description Example

Simple Atomic attribute Age
Composite Made of sub-attributes Address
Single-Valued One value only EmpID
Multi-Valued Multiple values PhoneNumbers
Stored Stored in DB DateOfBirth
Derived Computed attribute Age
Key Attribute Unique identifier EmpID

10
3.2.7 Key Takeaways
• Attributes describe properties of an entity.

• Key attributes uniquely identify records.

• Composite and multi-valued attributes require special handling.
• Derived attributes reduce redundant storage.

11
3.3 Relationship
In Database Management Systems (DBMS), a relationship defines how two or more entities are connected
or associated with each other.
Examples:
• A Student enrolls in a Course
• An Employee works in a Department

3.3.1 Types of Relationships

(a) One-to-One Relationship (1:1)
One record in Entity A is linked to exactly one record in Entity B.
Example: A Person has one Passport.
Implementation:
• Use same primary key in both tables or

• Use a foreign key in one table

(b) One-to-Many Relationship (1:N)

One record in Entity A can link to many records in Entity B.
Example: A Department has many Employees.
Implementation:

• Add foreign key in the ”many” side table

(c) Many-to-Many Relationship (M:N)

Many records in Entity A can link to many records in Entity B.
Example: A Student can enroll in many Courses and a Course can have many Students.
Implementation:
• Create a junction (associative) table

Representation in ER Diagram
• Relationship → Diamond
• Entity → Rectangle
• Cardinality → 1, N, M notation

ER Diagram: One-to-One Relationship (1:1)

Has

1 Person Passport 1

12
ER Diagram: One-to-Many Relationship (1:N)

Works In

1 Department Employee N

ER Diagram: Many-to-Many Relationship (M:N)

Enrolls In

M Student Course N

Summary
• Relationships connect entities.
• One-to-One links one record to one record.
• One-to-Many links one record to many records.

• Many-to-Many links many records to many records.

• ER diagrams use diamonds to represent relationships.

13
 Hobby Class
First name
Street

City Address STUDENT Name

Pin
State Last name
Date of birth

Roll number

Age

Fig. 3.1 ERD for the Student Entity

14
Example: Entity Lists
• EMPLOYEE (emp no, name, age, gender, salary)
• CONSULTANT (emp no, client, designation)

• DEPARTMENT (dept id, name)

• AUTHOR (name, age, gender, address, phone number)
• PUBLISHER (name, address, editor)

Relationships
• EMPLOYEE and CONSULTANT — One-to-One Relationship
• EMPLOYEE and DEPARTMENT — One-to-Many Relationship
• AUTHOR and PUBLISHER — Many-to-Many Relationship

15
 Age
Name Gender

emp no EMPLOYEE Salary

emp no CONSULTANT Designation

Client

Fig 3.11 One-to-One Relationship

Age
Name Gender

emp no EMPLOYEE Salary

dept id DEPARTMENT Name

Fig 3.12 One-to-Many Relationship

16
 Age
Name Gender

Address AUTHOR Phone no

Name PUBLISHER Editor

Address

Fig 3.13 Many-to-Many Relationship

17
4 Enhanced Entity- Relationship Model
The Extended Entity-Relationship (EER) model enhances the basic ER model with additional semantic
concepts to better represent complex database requirements. The three key extensions are:

1. Specialization
Specialization is a top-down design approach in which a higher-level entity is divided into multiple
lower-level entities based on specific characteristics.
Example: Employee is specialized into Manager and Engineer.
2. Generalization
Generalization is a bottom-up design approach in which multiple lower-level entities are combined
into a higher-level entity based on common attributes.
Example: Car and Bike are generalized into Vehicle.

3. Categorization
Categorization is a process where a subclass is formed from the union of multiple superclasses that
are not directly related.
Example: A Person can be a Student or an Employee.

Superclass and Subclass in ER Model

Superclass (Parent Entity): A superclass is a higher-level entity that contains common attributes
shared by multiple subclasses.
Subclass (Child Entity): A subclass is a specialized entity that inherits attributes and relationships
from its superclass and may have additional attributes.
Example: Employee is a superclass, while Manager and Engineer are subclasses.

Employee

Manager Engineer

18
 Address
Name Date of birth

Employee no EMPLOYEE Date of joining

FULLTIME PART TIME CONSULTANT

Salary Allowances Contract no Billing rate

Hourly rate

Fig 3.13 EMPLOYEE superclass with three Subclass

19
5 ACID Properties in DBMS
Introduction
In a Database Management System (DBMS), a transaction is a set of operations that are executed as
a single unit. To ensure that transactions are processed reliably and correctly, DBMS follows the ACID
properties.

ACID stands for:

• Atomicity
• Consistency
• Isolation

• Durability
These properties guarantee data accuracy and reliability even in the case of system failures.

1. Atomicity
Atomicity means all or nothing.
A transaction must be completed fully or not executed at all. If any operation in a transaction fails,
the entire transaction is rolled back.

Example
Consider a bank transfer of 500 from Account A to Account B.

• 500 is deducted from Account A

• 500 is added to Account B

If the amount is deducted from Account A but not credited to Account B due to a failure, Atomicity
ensures that the deducted amount is returned to Account A.

Result: Either both steps happen or none happen.

2. Consistency
Consistency ensures that a transaction moves the database from one valid state to another valid state.
All database rules, constraints, and integrity conditions must be followed.

Example
Assume a bank account has a minimum balance rule of 1000.

• Current balance = 1200

• Withdrawal request = 500

After withdrawal, the balance becomes 700, which violates the rule. Hence, the transaction is rejected.

Result: The database always remains correct and valid.

3. Isolation
Isolation ensures that multiple transactions executing at the same time do not affect each other.
Each transaction works independently, and intermediate results are not visible to other transactions.

20
Example
Suppose only one seat is available for booking.

• Transaction T1 checks the seat and finds it available

• Transaction T2 also checks the seat at the same time

Without isolation, both users may book the same seat. Isolation ensures that transactions are exe-
cuted in such a way that the final result appears as if they were executed one after another.

4. Durability
Durability means that once a transaction is committed, its changes are permanent.
Even in the case of system crash, power failure, or restart, the data will not be lost.

Simple Example
A user makes an online payment of 1000.

• Payment is marked successful

• System crashes immediately after

Due to durability, the payment record remains safely stored in the database.

Real-Life Example
Online food ordering system:
• Atomicity: Order and payment both succeed or both fail

• Consistency: Order follows all rules (valid items, address)

• Isolation: Your order does not mix with other customers’ orders
• Durability: Confirmed order is saved permanently

Summary Table
Property Meaning
Atomicity All operations succeed or none
Consistency Database remains valid
Isolation Transactions do not interfere
Durability Data is permanently stored

Conclusion
ACID properties ensure reliable transaction processing in DBMS. They protect data from inconsistency,
loss, and interference, making database systems safe and trustworthy.

21