DBMS Unit 1-Introduction

1. Data, Information, Database, and DBMS

In the digital world, everything starts with data. To understand DBMS, we first need to
know the difference between Data, Information, Database, and DBMS.

Data: Raw facts and figures without context or meaning. Can be in the form of text, numbers,
images, audio, video, or symbols. Example: 'boy ram good' – This is just a random set of
words without any meaning.

Information: When data is processed or organized in a way that makes sense and conveys
meaning, it becomes information. Example: Processing 'boy ram good' into 'Ram is a good
boy' gives a clear meaning.

Database: An organized collection of related data stored in a structured way so it can be

accessed and managed easily. Example: A student database storing details such as Name,
Roll No., Class, Address.

DBMS (Database Management System): Software that allows us to create, manage, update,
delete, insert, and retrieve data from a database efficiently. Examples: Oracle, MySQL, IBM
Db2, MongoDB, SQL Server, PostgreSQL.

Real-life Example: In a school, raw admission forms are data, the processed student list is
information, the entire computerized student record is a database, and the school
management software that maintains and retrieves it is the DBMS.
2. Why We Need to Learn DBMS
DBMS plays a vital role in modern data handling. Here are the reasons why it is important:
1. Organize Data Efficiently – Stores data in structured form, avoiding messy and scattered
records.
2. Easy Data Retrieval – Quickly search and retrieve data using queries. Example: Finding all
students in Class 8 with marks above 90.
3. Data Security – Provides user permissions so only authorized people can view or change
data.
4. Avoid Data Redundancy – Prevents duplicate records. Example: A student’s address
stored only once in the system.
5. Multi-User Support – Allows many users to work on the same data simultaneously
without conflict.
6. Data Consistency – Ensures the same updated information is shown everywhere.
7. Backup & Recovery – Automatically backs up data and restores it if lost.
8. Better Decision Making – Reliable and up-to-date data helps in making better decisions.

Real-life Example: An online shopping site storing customer orders and stock data in DBMS
can update all information in real-time, helping in fast and accurate delivery.
3. File System Approach (Without DBMS)
Before DBMS, data was stored in files like .doc, .txt, .xls. Each application had its own
separate files.
To access or modify data, you needed to open the file, locate the data, update it, and save the
file again. Sometimes you had to write custom programs to handle different file formats.

Problems with File System:

- Data Redundancy: Duplicate data stored in multiple files. Example: Student details stored
by both the Computer Department and Examination Department.
- Data Inconsistency: Same data is different in different files. Example: Address change
updated in one department but not in another.
- Difficulty in Accessing Data: Unsupported file formats require conversion or custom
programs.
- Poor Security: Anyone with file access can modify the data.
- No Multi-User Support: Difficult for multiple people to work on the same files.
- No Backup & Recovery: No automatic backup or restore mechanism.

Real-life Example: Keeping all financial records in separate Excel files on different
computers can cause duplication, mismatch, and make it hard to secure.
4. Difference Between File System and DBMS
Feature File System DBMS
Data Storage Separate files (.doc, .txt, .xls) Centralized database
Data Redundancy High – duplicate data Low – stored once
Data Inconsistency Common Minimal
Data Access Complex, needs manual Easy, via queries
programs
Security Poor Strong with permissions
Multi-User Access Not supported Supported
Backup & Recovery Not automatic Automatic
Data Organization Unstructured Structured tables
Portability Format-dependent Standardized
Management Manual Automated by DBMS

5. Client-Server Architecture
Client-Server Architecture defines how clients (users) interact with a server (central
system) to request and receive services or data.

Two-Tier Architecture:
- Client (front-end) directly Database Server (back-end) se connect hota hai.
- Data requests, queries, aur updates directly client se server tak jaati hain.
- Issues:
1. Client-side bugs server ko affect kar sakte hain.
2. Agar software update karna ho toh har client machine pe install karna padega.
3. Client requests zyada hone par server performance slow ho sakti hai.
- Example: Ticket window se directly train booking karna.

Three-Tier Architecture:
- Client (front-end) → Application Server (middle layer) → Database Server (back-end).
- Application Server request receive karke processing karta hai, fir Database Server ko send
karta hai.
- Benefits:
1. Server load kam hota hai.
2. Updates central location pe hoti hain, clients pe automatically reflect hoti hain.
3. Better security & scalability.
- Example: IRCTC website se online train booking.

Comparison: Two-Tier vs Three-Tier Architecture

Feature Two-Tier Three-Tier
Connection Direct client-server Client → Dummy Server →
Main Server
Server Crash Risk High Low
Update Management Update each client Automatic updates
separately
Performance Slows with more clients Handles more clients
efficiently
Security Lower Higher
Example Ticket window IRCTC website
6. Schema and Instances
Schema: The overall design or blueprint of the database, including tables, columns, data
types, and relationships. Example: Designing a Student_Info table with Roll_No, Name,
Address, Class, Year, Semester, DOB.

Instance: The actual data present in the database at a specific moment in time. Example:
SELECT Roll_No, Name FROM Student_Info WHERE Year=3; returns the instance for that
moment.

Analogy: Schema is like a building’s blueprint, while Instance is like the people and
furniture currently inside.

Difference: Schema vs Instance

Feature Schema Instance
Meaning Design of database Data at a given time
Change Frequency Rare Frequent
Example Table columns and types Current rows of data
Analogy Building blueprint People/furniture inside

7. Advantages and Disadvantages of DBMS

Advantages:
1. Reduces data redundancy.
2. Ensures data consistency.
3. Improves data security.
4. Supports multi-user environment.
5. Provides backup & recovery.
6. Easy data retrieval with queries.
7. Enforces data integrity.

Disadvantages:
1. Costly to purchase and maintain.
2. Requires high-end hardware.
3. Complex to learn and use.
4. Centralized failure can affect all users.

Real-life Example: Banks use DBMS for account management because it ensures accuracy,
security, and backup, but it also requires investment in software, hardware, and skilled
staff.
 Lecture 2 – Data Models, Database Languages, Data Abstraction, Data Independence, and
Database Users
1. Data Models
A Data Model defines how the logical structure of a database is designed and how data is stored, organized, and
manipulated.
• Types of Data Models (in order):
•
a) Entity-Relationship (ER) Model
Definition: Uses entities, attributes, and relationships to design a database.
Key Concepts:
• Entity – An object in the real world (e.g., Student, Teacher, Course).
• Attribute – Characteristics of an entity (e.g., Name, Roll No, Address).
• Relationship – Association between entities (e.g., Student enrolled in Course).
Example: In a school database – Entity: Student, Attributes: Roll_No, Name, DOB, Relationship: Student enrolled in
Class.

b) Relational Model
Definition: Represents data in the form of tables (relations) containing rows (tuples) and columns (attributes).
Example Table – Student:
Roll_No Name Class Year
101 Ram 10th 2025
102 Sita 10th 2025

c) Hierarchical Model
Definition: Organizes data in a tree-like structure where each record has a single parent but can have many children.
Example: Company structure – CEO → Manager → Employee.
Key Point: Fast for one-to-many relationships but not flexible for complex queries.

d) Network Model
Definition: Organizes data in a graph structure allowing records to have multiple parent and child relationships.
Example: A student enrolled in multiple courses, and each course taught by multiple teachers.
Key Point: More flexible than hierarchical but more complex to manage.

2. Database Languages
a) Data Definition Language (DDL)
Used to define or change the structure of database objects.
Commands: CREATE, ALTER, DROP, TRUNCATE.
Example:
CREATE TABLE Student (Roll_No INT, Name VARCHAR(50));

b) Data Manipulation Language (DML)

Used to insert, update, delete data.
Commands: INSERT, UPDATE, DELETE.
Example:
INSERT INTO Student VALUES (101, 'Ram');

c) Data Query Language (DQL)

Used to retrieve/query data.
Command: SELECT.
Example:
SELECT Name FROM Student WHERE Year = 3;
d) Data Control Language (DCL)
Used for security and permissions.
Commands: GRANT, REVOKE.
Example:
GRANT SELECT ON Student TO User1;

e) Transaction Control Language (TCL)

Used to manage transactions ensuring data integrity.
Commands: COMMIT, ROLLBACK, SAVEPOINT.
Example:
COMMIT;

3. Data Abstraction
Data abstraction hides the complex details of data storage and shows only relevant information to the user.
Levels of Abstraction:
1. Physical Level (Lowest Level) – Defines how data is stored (storage structures, memory addresses, bits/bytes,
offsets). Example: Whether data is stored using B-trees or hashing is hidden from the user.
2. Logical Level (Middle Level) – Defines what data is stored and the relationships among them. Example: Table
structure, column names, data types, constraints.
3. View Level (Highest Level) – Defines how the user views the data. Example: A user only sees Name and Marks, not
the entire table.

4. Data Independence
The ability to change one level of the database without affecting the next higher level.
Types:
1. Physical Data Independence – Changes in physical storage do not affect the logical level. Example: Changing HDD to
SSD or changing indexing technique.
2. Logical Data Independence – Changes in logical structure (schema) do not affect the view level. Example: Adding a
new column to a table does not affect user views. Logical independence is harder to achieve.

5. Database Users
a) Database Administrator (DBA)
Full control over the database.
Tasks: Create & maintain databases, authorize users, perform backups, maintain server uptime, manage storage space.

b) Naïve Users
Access database indirectly through applications.
Example: ATM users, Gmail users.

c) Sophisticated Users
Access database directly by writing queries.
Example: SQL developers, data analysts.

d) Application Programmers
Develop applications for naïve users.
Example: Developers of banking apps, e-commerce platforms.
Lecture 3: ER Modelling (Entity-Relationship Model)

1. Definition of ER Model
The Entity-Relationship (ER) model is a high-level conceptual data model used to define the data elements and
relationships for a specified system. It is primarily used for database design, representing the real-world entities and the
relationships among them in a simple and understandable way. The ER model is represented visually using an ER
diagram, which acts as a blueprint for the database structure.

2. Uses of ER Model
- Helps in designing a database at the conceptual level.
- Acts as a communication tool between database designers and end-users.
- Provides a clear picture of the system requirements before actual implementation.
- Identifies entities, attributes, relationships, and constraints for database creation.

3. Components of ER Model

3.1 Entity and Entity Sets

Entity: An entity is a real-world object that is distinguishable from other objects. It can be a physical object (like a
student, book, or car) or a concept (like a course or department).
Example: A 'Student' having Roll No, Name, and Address.

Entity Set: A collection of similar types of entities.

Example: All students in a college make up the 'Student' entity set.

3.2 Attributes and Types

Attributes are the properties or characteristics of an entity.
Types of Attributes:
1. Simple Attribute: Cannot be divided further. Example: Age.
2. Composite Attribute: Can be divided into smaller subparts. Example: Full Name → First Name, Last Name.
3. Single-Valued Attribute: Holds only one value for an entity. Example: Roll Number.
4. Multi-Valued Attribute: Can hold multiple values. Example: Phone Numbers.
5. Derived Attribute: Can be derived from other attributes. Example: Age (from Date of Birth).

3.3 Relationship and Relationship Sets

Relationship: A relationship represents an association between two or more entities.
Example: 'Enrolled' relationship between Student and Course.

Relationship Set: A collection of similar types of relationships.

Example: The set of all 'Enrolled' associations between students and courses.

4. Notations Used in ER Modelling

- Rectangle: Represents Entity.
- Ellipse: Represents Attribute.
- Diamond: Represents Relationship.
- Lines: Link attributes to entities and entities to relationships.
- Double Ellipse: Multi-valued Attribute.
- Dashed Ellipse: Derived Attribute.

5. Mapping Cardinality
Mapping cardinality specifies the number of relationship instances in which an entity can participate.
Types:
1. One-to-One (1:1): A student has one ID card, and one ID card belongs to one student.
2. One-to-Many (1:N): One department has many employees, but each employee belongs to one department.
3. Many-to-One (N:1): Many students belong to one department.
4. Many-to-Many (M:N): Students enroll in many courses, and each course has many students.

6. Keys in ER Model
Keys are attributes that uniquely identify entities within an entity set.
Types:
1. Super Key: Set of attributes that can uniquely identify an entity.
2. Candidate Key: Minimal super key.
3. Primary Key: Chosen candidate key for unique identification.
4. Foreign Key: Attribute that refers to the primary key in another entity set.

Conclusion
The ER model is a crucial step in database design. Understanding its components like entities, attributes, relationships,
mapping cardinalities, and keys helps in creating a well-structured and efficient database.
 Lecture 4 — Advanced ER Modeling and ER → Table Conversion

1. Advantages of ER Model

The ER model is widely used for database design because:

1. Easy to Understand – It uses simple diagrams with entities, relationships, and attributes, making it
easier for beginners.
2. Clear Design Phase – Helps plan database structure before actual implementation.
3. Visual Representation – Shows the relationships between entities in a clear way.
4. Supports Different Views – Can represent different levels of abstraction (high-level to detailed).
5. Can be Converted into Tables Easily – Logical design can be mapped to relational schema.

Example:
If we want to design a student management system, ER diagrams make it clear how students, courses,
and instructors relate to each other before creating tables.

2. Limitations of ER Model

1. Not a Standard Notation – Different designers use different symbols.

2. Does Not Show Data Constraints Well – Can show primary keys, but not always detailed constraints
like check conditions.
3. No Direct Support for Complex Operations – ER diagrams focus on structure, not queries.
4. Can Become Complex – For large systems, the diagram may become very cluttered.
5. No Time Dimension – Cannot show changes in data over time.

3. Enhanced ER Model (EER)

The EER model adds more features to the ER model to represent complex relationships.

3.1 Specialization

• Process of dividing a higher-level entity into sub-entities based on specific characteristics.

• Example: An Employee entity can be specialized into:
o Engineer
o Manager
o Technician

Key Points:

• Top-down approach.
• Inherits attributes from the parent entity.
 3.2 Generalization

• Opposite of specialization.
• Combines two or more lower-level entities into a higher-level entity.
• Example:
o Car and Bike can be generalized as Vehicle.

Key Points:

• Bottom-up approach.
• Used when two or more entities share common attributes.

3.3 Aggregation

• Represents a relationship between a relationship and another entity.

• Used when a relationship itself needs to be treated as an entity.
• Example:
o A works_on relationship between Employee and Project has an attribute Hours.
o If we need to relate this to another entity like Department, we use aggregation.

4. Conversion of ER Diagrams to Tables (Relational Schema)

4.1 Entity → Table

• Each entity becomes a table.

• Attributes become columns.
• Primary key is marked.

4.2 Relationship → Table

Rules:

1. 1:1 Relationship
o Usually implement using foreign key in one of the entity tables.
o Separate table only if relationship has attributes.
2. 1:N Relationship
o Implement by adding foreign key in the table on the "N" side.
3. M:N Relationship
o Always create a new table.
o Primary key = combination of primary keys from both entities.
4. Relationship with Descriptive Attributes
o Regardless of cardinality, create a separate table to store those attributes.
Example 1: Many-to-Many

STUDENT (RollNo, Name, Class)

COURSE (CourseID, Title, Credits)
ENROLLS (RollNo, CourseID, EnrollmentDate)

Example 2: 1:N with Foreign Key

DEPARTMENT (DeptID, DeptName)

EMPLOYEE (EmpID, Name, DeptID) -- DeptID is foreign key

Example 3: Relationship with Attributes

EMPLOYEE (EmpID, Name)

PROJECT (ProjID, Title)
WORKS_ON (EmpID, ProjID, Hours) -- Hours is descriptive attribute