Module 1

Introduction
 Introduction to Databases

• A database is a collection of related data that is organized and stored

for easy access.
• We interact with databases daily, such as:
• Withdrawing money from a bank
• Making hotel or airline reservations
• Shopping online
• Searching for books in a library catalog
• Traditional databases mainly store textual and numerical data.
• Modern Database Applications
• Advances in technology allow databases to store different types of
data:
• Multimedia databases store images, audio, and video.
• Geographic Information Systems (GIS) store maps, weather data, and
satellite images.
• Data warehouses & OLAP systems analyze business data for decision-making.
• Real-time databases are used in industrial processes.
• Web databases improve search results on the internet.
• Importance of Databases
• Databases are used in almost all fields, including:
• Business and E-commerce (e.g., Amazon)
• Engineering and Medicine
• Law and Education
• Genetics and Library Science
• A database stores known facts (data) that can be recorded and have
meaning.
Properties of a Database
• Represents the real world: The database reflects real-world events and
changes.
• Logically organized: It has a clear structure and meaningful data.
• Has a purpose: It is designed for a specific use and group of users.
Example of Database Size and Complexity
• Small database: A personal contact list with names, addresses, and phone numbers.
• Medium database: A library catalog with books indexed by title, author, and subject.
• Large database: The IRS (tax system) maintains records of millions of taxpayers.
• Very large database: Amazon.com stores details of 20+ million products, requiring 2
terabytes of storage.
Manual vs. Computerized Databases
• Manual database: A library card catalog with handwritten records.
• Computerized database: A digital library catalog accessed through a computer.
What is a Database Management System (DBMS)?
• A DBMS is a software system that helps users create, maintain, and manage databases.
• Functions of a DBMS:
• Defining: Specifies data types, structures, and constraints.
• Constructing: Stores data on a storage device.
• Manipulating: Retrieves, updates, and generates reports.
• Sharing: Allows multiple users to access the database.
• Protecting: Prevents data loss due to system crashes or unauthorized access.
How Databases Work
• Users send queries (requests) to retrieve or modify data.
• A transaction is an operation that involves reading and writing data.
• The DBMS ensures accuracy by updating the database as real-world events happen.
Database Protection and Maintenance
• A DBMS protects the database from:
• Hardware failures (e.g., power outages)
• Software errors (e.g., bugs or crashes)
• Unauthorized access (e.g., hacking)
• A database may last many years and must evolve as needs change.
General-Purpose vs. Special-Purpose Databases
• A general-purpose DBMS (e.g., MySQL, Oracle) manages various types of
databases.
• A special-purpose DBMS is custom-built for a specific application.
• Both require complex software to function efficiently.
• What is a Database System?
• The Database System includes:
• Database: The actual stored data.
• DBMS: The software that manages the data.
• Application programs: The software that interacts with the database.
• Introduction to the UNIVERSITY Database
• The UNIVERSITY database stores and manages information about
students, courses, and grades.
• It consists of five files, each storing specific types of data:
• STUDENT – Stores student details.
• COURSE – Stores course details.
• SECTION – Stores details of course sections.
• GRADE_REPORT – Stores student grades for completed sections.
• PREREQUISITE – Stores course prerequisites.
• Structure of Each File in the Database
• Each file contains multiple records, and each record has several data fields.
• (a) STUDENT File
• Stores data about students.
• Each STUDENT record includes:
• Name (e.g., "John Doe") → String (Alphabetic characters)
• Student_number (e.g., 12345) → Integer (Unique ID for each student)
• Class (e.g., Freshman, Sophomore) → Coded as:
• 1 = Freshman
• 2 = Sophomore
• 3 = Junior
• 4 = Senior
• 5 = Graduate student
• Major (e.g., "CS" for Computer Science)
• (b) COURSE File
• Stores data about university courses.
• Each COURSE record includes:
• Course_name (e.g., "Database Systems") → String
• Course_number (e.g., "CS101") → Unique identifier
• Credit_hours (e.g., 3) → Integer
• Department (e.g., "Computer Science") → String
• SECTION File
• Stores details about different sections of a course.
• Each SECTION record includes:
• Course_number → Links to the COURSE file.
• Section_ID → Identifies a specific section of a course.
• Semester → Term in which the section is offered (e.g., Fall 2024).
• Year → Year of the section (e.g., 2024).
• Instructor → Name of the faculty teaching the section.
• (d) GRADE_REPORT File
• Stores grades received by students in different sections.
• Each GRADE_REPORT record includes:
• Student_number → Links to the STUDENT file.
• Section_ID → Links to the SECTION file.
• Grade (e.g., ‘A’, ‘B’, ‘C’, ‘D’, ‘F’, ‘I’ for incomplete).
• PREREQUISITE File
• Stores prerequisite requirements for courses.
• Each PREREQUISITE record includes:
• Course_number → The course being taken.
• Prerequisite_number → The course that must be completed before.
• Relationships Between Files
• The records in different files are related to each other.
• Example:
• The STUDENT file stores a student named "Smith."
• Smith’s grades are recorded in the GRADE_REPORT file, linking STUDENT and
GRADE_REPORT.
• The PREREQUISITE file links courses together by showing required
prerequisites
• Data Types in the Database
• Each data element has a data type that defines how the data is stored:
• Strings → Used for names, course names, department names.
• Integers → Used for student numbers, course numbers, credit hours.
• Characters → Used for letter grades (A, B, C, D, F, I).
• Coding Schemes
• Some data is represented using codes for efficiency.
• Example:
• Class levels are stored as:
• 1 → Freshman
• 2 → Sophomore
• 3 → Junior
• 4 → Senior
• 5 → Graduate
• How Data is Stored and Used
• When a new student enrolls, their record is added to the STUDENT file.
• When a new course is created, a record is added to the COURSE file.
• When a student registers for a section, a record is added to the SECTION file.
• When a student completes a course, their grade is recorded in the
GRADE_REPORT file.
• If a course has prerequisites, this information is stored in the PREREQUISITE
file.
• Complexity of Larger Databases
• Medium and large databases have many types of records and many
relationships.
• Example:
• A university with 10,000 students and 1,000 courses will have millions of related
records.
• What is the UNIVERSITY Database?
• It stores data related to students, courses, sections, grades, and prerequisites.
• The data is structured in files, where each file holds records about a specific entity.
2. Relationships Between Records
• Students & Grades: A student record is linked to multiple grade records.
• Courses & Prerequisites: A course may have a prerequisite, meaning it depends on another course.
• Sections & Students: Students enroll in sections of a course, and their grades are recorded.
3. Queries (Data Retrieval Examples)
• Get Transcript: Retrieve all courses and grades for a student.
• List Course Attendees: Find students who took a specific course in a specific semester and their grades.
• Find Prerequisites: Show the prerequisite courses for a specific course.

4. Updates (Data Modification Examples)

• Change Student Information: Update a student’s class (e.g., from freshman to sophomore).
• Add a New Course Section: Create a new section of a course for a new semester.
• Enter Student Grades: Assign a grade for a student in a particular course section.

5. Database and Information Systems

• A database is part of a larger Information System managed by the IT department.
• An Information System includes computers, storage, software, and databases.
• Database Design Process
• Requirements Specification & Analysis:
• Understand what data the system needs to store.
• Document all necessary requirements.
• Conceptual Design:
• Create a high-level design of the database.
• Use models like the Entity-Relationship (ER) model to visualize data relationships.
• Logical Design:
• Convert the conceptual design into a structure compatible with a Database
Management System (DBMS).
• Use models like the Relational Data Model to define tables and relationships.
• Physical Design:
• Define how the data will be stored and accessed efficiently.
• Optimize storage and retrieval processes.
• Implementation and Maintenance:
• Build the database, enter data, and ensure it is updated regularly.
• Comparison: Database Approach vs. File-Processing Approach
1. Traditional File Processing Approach
• Each user or department maintains separate files for their needs.
• Redundant data storage (e.g., student data stored separately by grade
reporting and accounting offices).
• Wasted storage space and extra effort in maintaining up-to-date records.
• Inconsistencies may occur due to multiple versions of the same data.
2. Database Approach
• A single centralized repository stores all data.
• Data is defined once and shared across different users and applications.
• Common data labels/names prevent inconsistencies.
• Users access data through queries, transactions, and applications instead
of managing separate files.
• Key Characteristics of the Database Approach
1. Self-Describing Nature of a Database System
• A database contains metadata (data about data).
• Metadata describes structure, types, constraints, and relationships of stored
data.
• Users can access the database without needing to understand its internal
structure.
2. Insulation Between Programs and Data (Data Abstraction)
• In file-based systems, programs are directly tied to data formats—changes in
data require modifying all related programs.
• In a database system, data is abstracted from programs, making modifications
easier.
• DBMS (Database Management System) handles data structure changes,
allowing programs to work with updated data without being rewritten.
3.Support of Multiple Views of Data
• Different users see different representations (views) of the same database.
• Example:
• A student sees only their grades.
• A professor sees all students’ grades.
• The accounting office sees only students’ fee payments.
• Views improve security and accessibility by limiting unnecessary data
exposure.
4. Sharing of Data and Multiuser Transaction Processing
• Multiple users can access and update data simultaneously without conflicts.
• Transactions ensure data consistency, preventing issues like double booking
or lost updates.
• Example:
• A student enrolls in a course while an administrator updates the course schedule—
both actions occur smoothly without data errors.
•The database approach is more efficient, consistent, and scalable

compared to traditional file processing.

•It reduces redundancy, improves data security, and allows multiple users to

access data simultaneously.

•DBMS ensures structured data management, making applications more

flexible and maintainable.

• Self-Describing Nature of a Database System
• A key characteristic of the database approach is that it stores both
the actual data and its definition (metadata) within the system. This
allows the DBMS (Database Management System) to manage
different databases efficiently.
• What is a Self-Describing Database System?
• In a DBMS, the database is accompanied by a complete definition of
its structure and constraints.
• This definition is stored in a special place called the DBMS catalog.
• The DBMS catalog contains metadata, which includes:
• The structure of each file (e.g., tables, columns, relationships).
• The type and format of each data item (e.g., text, number, date).
• Constraints that control data integrity (e.g., "Age must be greater than 18").
• Role of the DBMS Catalog (Metadata Storage)
• Metadata = "Data about data" (describes the structure of the main
database).
• The DBMS software and users refer to the catalog to understand how
the database is organized.
• A DBMS is general-purpose and can work with multiple databases
(e.g., university, banking, company) as long as their structure is stored
in the catalog.
 Difference Between Traditional File Processing and DBMS

Feature Traditional File Processing DBMS Approach

Defined within application Stored separately in the DBMS

Data Definition
programs catalog

Programs work only with a specific Programs can work with multiple
Flexibility
database structure databases

File structure is hardcoded in Metadata is centrally stored in the

Metadata Storage
programs DBMS catalog

Modifications are easier, as

Modifying file structure requires
Changes programs access metadata
rewriting programs
dynamically
•In traditional file processing, a C++ program would use struct or class to define student records,

and a COBOL program would have DATA DIVISION statements.

•If the structure of the STUDENT file changes, all related programs must be rewritten.

•In DBMS, the metadata is stored in the catalog, and any changes in structure are automatically

handled by the system without rewriting programs.

• How the DBMS Uses the Catalog?
• Before accessing data, the DBMS software checks the catalog to retrieve
information about:
• The file structure (e.g., what fields are in a STUDENT record).
• The position and size of a specific data item (e.g., where the "Name" field is stored).
• This dynamic data retrieval makes databases more flexible than traditional
file systems.
• Advantages of a Self-Describing Database System
• ✔ Flexibility – Works with multiple databases without program changes.
✔ Easier Maintenance – Modifications to data structure do not require
rewriting programs.
✔ Data Consistency – Centralized metadata prevents errors in data
interpretation.
✔ Better Security & Access Control – Metadata helps manage user access
and permissions.
 • Insulation Between Programs and Data & Data Abstraction
• The database approach provides insulation between programs and
data, which makes databases more flexible and easier to maintain
compared to traditional file-based systems. It also allows data
abstraction, where users interact with a simplified view of data
without worrying about how it is stored or implemented.

1.Insulation Between Programs and Data (Program-Data

Independence)
Traditional File Processing (Problems)
•In file-based systems, the structure of data is hardcoded into programs.
•Example:
•A program is designed to access STUDENT records with fields: ID, Name, Major.
•If a new field Birth_date is added, the program will stop working unless it is updated.

•If the file structure changes, all programs that access the file must be modified.
 Database Approach (Solution)
•The database structure is stored separately in the DBMS catalog (metadata).
•Programs do not need to change when the database structure is modified.
•Example:
•If Birth_date is added to the STUDENT table, only the metadata in the catalog is updated.
•Programs automatically adapt to the new structure without modification.

Program-Operation Independence
•In some database systems (e.g., object-oriented databases), users can define operations (functions)
on data.
•Operations consist of:
1.Interface (Signature) – Defines the operation name and input/output data types.
2.Implementation (Method) – Defines how the operation is performed.
•Programs call the operation using the interface, without knowing how it is implemented.
•Example:
•Operation: CALCULATE_GPA(Student_ID)
•Users call the function to calculate GPA but do not need to know the formula or implementation
details.
Data Abstraction

•Data abstraction means hiding unnecessary details and showing only relevant data.
•A DBMS provides a conceptual view of data, so users do not need to know:
•How data is stored physically (e.g., file structure, memory locations).
•How operations are implemented (e.g., query optimization, indexing).
Example of Data Abstraction

•Low-level Storage (Hidden Details):

•Data is stored as bytes, memory addresses, and access paths.
•Each record has a specific position and length in memory.

•High-level Conceptual View (What Users See):

•Users see tables with columns like Student_ID, Name, Major.
•A query like SELECT Name FROM STUDENT WHERE Student_ID = 101
•returns the correct result without exposing
•internal storage details.

A data model provides this conceptual representation of data.

How DBMS Uses Data Abstraction?
•Users and applications refer to the conceptual representation of data.
•DBMS retrieves the actual data storage details from the catalog when needed.
•Example:
•A student searches for their name using SELECT Name FROM STUDENT;
•The DBMS fetches the storage details (e.g., file location, byte position) internally,
but the user only sees the result.

Advantages of Insulation & Data Abstraction

✔ Flexibility – Databases can evolve without affecting programs.
✔ Easier Maintenance – No need to rewrite applications when the data
structure changes.
✔ Data Security – Users access only the relevant parts of the database.
✔ Better Performance – DBMS handles optimization without user intervention.
✔ Simplifies Development – Programmers can work with high-level data
models instead of dealing with storage details.
• Support of Multiple Views of the Data
• A database system allows multiple users to access data, but each user
may need to see different parts of the data. A view is a way to present
data that is relevant to a specific user or application while hiding
unnecessary details.
• What is a View?
• A view is a customized representation of data for a specific user or
group.
• Views can be:
• A subset of data (showing only selected columns or rows).
• Derived data (data generated using calculations or joins from multiple tables).
• Restricted access (hiding confidential or irrelevant data from certain users).
• Users may not know whether the data they see is stored directly or
generated dynamically.
• Why Are Views Important?
• ✔ Data Security – Sensitive data (e.g., salaries, grades) can be hidden from unauthorized
users.
✔ Simplifies User Interaction – Users see only the relevant data they need.
✔ Data Independence – If the actual database structure changes, views can remain the
same.
✔ Different Perspectives – Different departments (e.g., students, faculty, administration)
can have different views of the same data.
• Example of Multiple Views
• Consider a University Database with student information, courses, grades, and
prerequisites.
• View 1: Student Transcript View (Figure 1.5a)
• User: A student who wants to see their transcript.
• View Includes:
• Student Name
• Courses Taken
• Grades Received
• Hidden Data: Prerequisites, fees, and payment details are not shown.
• Example SQL View:
CREATE VIEW Student_Transcript
AS SELECT Student_ID, Name, Course_ID, Grade
FROM STUDENT NATURAL JOIN GRADE_REPORT;
• View 2: Prerequisite Check View (Figure 1.5b)
• User: University administrator checking if students meet course
prerequisites.
• View Includes:
• Student ID
• Courses Enrolled
• Prerequisites Completed
• Hidden Data: Student name, grades, and fees are not shown.
• Example SQL View:
CREATE VIEW Prerequisite_Check AS
SELECT Student_ID, Course_ID, Prerequisite_ID
FROM PREREQUISITE NATURAL JOIN ENROLLMENT;
• How Views Work in a DBMS?
• Views do not store data directly.
• When a user queries a view, the DBMS fetches the required data
dynamically.
• If the original data changes, the view automatically reflects the
updated information.
• Advantages of Multiple Views
• ✔ Custom Data Access – Each user sees only relevant data.
✔ Improves Security – Prevents unauthorized users from accessing
sensitive data.
✔ Enhances Performance – Simplifies queries by hiding complexity.
✔ Maintains Consistency – Users access a uniform version of data.

A multiuser DBMS allows multiple users to access the same database

but with different views based on their needs. Views help in security,
simplicity, and efficiency by presenting customized and restricted
versions of the data without altering the original database.
• Sharing of Data and Multiuser Transaction Processing
• A multiuser DBMS allows multiple users to access and update data at
the same time. This is important for organizations that need to
manage data for multiple applications in a single, shared database.

• Why is Multiuser Access Important?

• ✔ Data Sharing – Different users and applications can access the
same data.
✔ Efficiency – Users don’t have to wait for others to finish their work.
✔ Real-Time Updates – Changes made by one user are immediately
available to others.
✔ Data Consistency – Ensures that shared data remains accurate and
up to date.
• Challenges in Multiuser Databases
• When multiple users access the same database, problems can occur,
such as:
Concurrency Issues (When multiple users update data at the same
time)
• Example:
• Two users try to book the same airline seat at the same time.
• Without proper control, both users might get the same seat, causing a
conflict.
• Solution:
✔ Concurrency Control – The DBMS ensures that only one user can
update a specific data item at a time.
• Transactions and Their Importance
• A transaction is a set of operations that must be executed as a single
unit.
• A transaction may involve multiple database operations, such as:
• Reading a record
• Updating a value
• Writing back to the database
• Example:
A bank transfer transaction consists of:
✔ Step 1: Withdraw money from Account A
✔ Step 2: Deposit money into Account B
• ✔ Both steps must succeed together, or neither should happen.
• Key Properties of Transactions (ACID Properties)
• A DBMS ensures that transactions follow ACID properties to maintain data
integrity:
• A - Atomicity (All or Nothing)
• A transaction must complete fully or not at all.
• If a failure occurs in the middle, the database undoes any changes.
• 🔹 Example:
If a power outage happens after withdrawing from Account A but before
depositing into Account B, the withdrawal is canceled to prevent data loss.
• C - Consistency (Valid Data State)
• A transaction must leave the database in a valid state.
• Data integrity rules must be maintained.
• 🔹 Example:
• If a student enrolls in a course, the system must ensure that they have completed
all prerequisites before confirming enrollment.
• I - Isolation (No Interference from Other Transactions)
• Transactions run independently even if they execute at the same
time.
• Users should not see intermediate, unfinished updates.
• 🔹 Example:
If two users update the same bank account, the system processes
one update first before allowing the second one to proceed.
• D - Durability (Permanent Changes)
• Once a transaction is successfully completed, its changes are
permanently saved even if the system crashes.
• 🔹 Example:
• If an online order is confirmed, the purchase details remain saved,
even if the system crashes immediately afterward.
• Online Transaction Processing (OLTP)
• OLTP systems handle a large number of short, fast transactions in real-
time.
• Used in banks, airlines, online shopping, and stock trading.
• Example OLTP Operations:
✔ Banking: Withdrawals, deposits, transfers
✔ E-commerce: Placing an order, adding items to a cart
✔ Airline: Booking or canceling a ticket
• How DBMS Manages Multiuser Transactions?
• A DBMS ensures safe multiuser transactions using:
• ✔ Locking Mechanisms: Prevent multiple users from modifying the same
data at the same time.
✔ Timestamps: Determine the order of transactions to avoid conflicts.
✔ Rollback Mechanisms: Undo incomplete transactions if errors occur.
✔ Commit Operations: Save successful transactions permanently.
• 1. Actors on the Scene
• These individuals work directly with the database system on a daily
basis.
• 1) Database Administrators (DBA) 🔧
• ✔ The chief person responsible for the database.
✔ Manages access control, security, performance, and backup.
✔ Ensures smooth operation and troubleshoots problems.
• Example:
• If a company database crashes, the DBA restores it from a backup.
• The DBA sets permissions so that only HR staff can see employee
salaries.
• 2) Database Designers
• ✔ Identify what data needs to be stored.
✔ Choose the best structure for storing and retrieving data.
✔ Work before the database is created to plan everything properly.
• Example:
• In a university database, they design tables for students, courses,
professors.
• They decide how student records should be linked to grades and
courses.
• 3) End Users
• End users are people who use the database for daily operations. They are divided into
four types:
• a) Casual End Users 🔍
• ✔ Occasionally access the database for different types of information.
✔ Use query languages to fetch data.
✔ Usually managers or executives.
• Example:
• A marketing manager checks customer purchase trends.
• b) Naive or Parametric End Users 🏦
• ✔ Use the database regularly but with predefined transactions.
✔ Do not create new queries; instead, they use ready-made forms.
• Examples:
• Bank tellers check balances and process deposits.
• Hotel reservation agents check room availability.
• Shipping employees scan barcode data into the system.
• c) Sophisticated End Users 💡
• ✔ Experts like engineers, scientists, and analysts.
✔ Create their own complex database queries and reports.
• Example:
• A data analyst writes custom SQL queries to analyze sales trends.
• d) Standalone Users 💻
• ✔ Manage personal databases using software tools.
✔ Use ready-made programs with menu-based interfaces.
• Example:
• A freelancer uses a tax software to manage personal finances.
• 4) System Analysts & Application Programmers (Software Engineers)
💾
• ✔ System Analysts: Understand end-user needs and create
transaction requirements.
✔ Application Programmers: Write code to implement these
transactions.
• Example:
• A system analyst designs a flight booking system, and a programmer
codes it.
• Workers Behind the Scene
• These individuals maintain the database system but do not interact
with the database content daily.
• Roles:
• ✔ System Administrators – Manage hardware and system software.
✔ Network Administrators – Ensure network connections work
smoothly.
✔ Software Developers – Build DBMS software and optimization
tools.
• 3. Why is This Structure Important?
• ✔ Ensures smooth database operation in large organizations.
✔ Divides responsibilities for better efficiency.
✔ Protects data integrity and prevents unauthorized access.
• Advantages & Capabilities of a DBMS
1. Controlling Redundancy
• Traditional file-based systems store the same data in multiple places, causing duplication.
• Leads to unnecessary storage usage and inconsistent data when updates are not applied
everywhere.
• DBMS allows data normalization, ensuring each data item is stored only once.
• Controlled redundancy (denormalization) is allowed for performance optimization, but
consistency is maintained automatically.
2. Restricting Unauthorized Access
• Not all users should have access to all data (e.g., financial records are
confidential).
• DBMS provides user authentication with account numbers and
passwords.
• Access control defines who can read or update data.
• Special privileges are assigned to different user roles (e.g., only DBAs
can create accounts).
3. Providing Persistent Storage for Program Objects
• Traditional programming languages discard data after execution.
• Object-oriented DBMSs store complex program objects permanently.
• No need for manual conversion between file formats and program
variables.
3. Providing Persistent Storage for Program Objects
• Traditional programming languages discard data after execution.
• Object-oriented DBMSs store complex program objects permanently.
• No need for manual conversion between file formats and program variables.
4. Efficient Query Processing & Search Techniques
• Databases are stored on disks, requiring efficient retrieval methods.
• Indexing (using trees or hash structures) speeds up searches.
• Buffering and caching store frequently used data in memory to improve
performance.
• The DBMS query optimizer selects the best way to execute queries.
5. Backup & Recovery
• Prevents data loss due to system failures.
• If a transaction fails, the DBMS ensures data remains in a consistent state.
• Automatic backups protect against hardware failures (e.g., disk crashes).
6. Multiple User Interfaces
• Supports different user types:
• Casual users: Query languages
• Programmers: Language interfaces
• General users: GUI, forms, and menu-based interfaces
• Web users: Web-based interfaces
7. Representing Complex Relationships
• Databases store interrelated data (e.g., student-course relationships).
• Relationships between tables (foreign keys) help maintain logical connections.
• The DBMS allows easy retrieval and updates across multiple related records.
8. Enforcing Integrity Constraints
• Ensures data accuracy and consistency.
• Examples of constraints:
• Data type constraints (e.g., student name must be a string).
• Referential integrity (e.g., every course section must be linked to a valid course).
• Uniqueness constraint (e.g., each student has a unique ID).
• Prevents invalid data entry (e.g., rejecting an invalid grade ‘Z’).
9. Inferencing & Rule-Based Actions
• Some databases allow automatic rule-based decisions.
• Deductive databases infer new information based on rules.
• Triggers and stored procedures automatically execute when certain conditions occur.
• Example: If a student's GPA drops below a threshold, a rule can flag them as "on
probation."
10. Additional Benefits
• Enforcing Standards: Centralized control helps maintain consistency across
departments.
• Faster Application Development: Creating new applications is quicker with DBMS
tools.
• Flexibility: Can modify database structures without affecting stored data.
• Up-to-Date Information: Real-time updates are available to all users.
• Economies of Scale: Centralized databases reduce redundant storage and processing
costs.
• Evolution of DBMS Architecture
• Monolithic Systems:
• Early DBMS were tightly integrated software packages.
• Everything (data storage, processing, user interface) was in one system.
• Modern Modular Design:
• Uses client/server architecture for efficiency.
• Inspired by distributed computing (PCs & workstations connected via
networks).
• Client/Server DBMS Architecture:
• Client Module: Runs on user computers, handles UI and application
programs.
• Server Module: Manages database storage, retrieval, and processing.
• Data Abstraction in DBMS
• Definition:
• Hides complex storage details and presents essential features.
• Users can see data at different levels of detail.
• Data Model:
• Defines database structure (data types, relationships, constraints).
• Includes operations for retrieval & updates.
• User-Defined Operations:
• Example: COMPUTE_GPA for STUDENT objects.
• Standard operations: Insert, delete, modify, retrieve.
• Categories of Data Models
• Conceptual Data Models (High-Level):
• User-friendly and close to real-world concepts.
• Uses entities, attributes, and relationships.
• Example: Entity-Relationship (ER) Model.
• Representational Data Models (Middle-Level):
• Used in commercial DBMS (relational model, network model, hierarchical
model).
• Hides storage details but still implementable on a computer.
• Example: Relational Model (SQL).
• Physical Data Models (Low-Level):
• Focuses on data storage techniques.
• Defines file structures, record formats, access paths (indexes).
• Used by database administrators and system developers.
• Types of Data Models
• Entity-Relationship (ER) Model:
• Represents real-world objects as entities.
• Attributes describe entities (e.g., Name, Salary).
• Relationships connect entities (e.g., Employee works-on Project).
• Relational Model:
• Uses tables (relations) to store data.
• Based on rows (tuples) and columns (attributes).
• Uses SQL for data retrieval & manipulation.
• Object-Oriented Model:
• Extends relational models with behavior & methods.
• Uses Object Database Management Systems (ODMG standard).
• Physical Storage & Access Paths
• Physical Storage:
• Defines how data is stored in files (record formats, order, access paths).
• Access Paths:
• Structures that improve search efficiency.
• Example: Indexes allow direct access to records (similar to a book index).
• Types of Indexes:
• Linear: Simple list.
• Hierarchical (Tree-Structured): Organizes data in levels for faster search.
• Schemas, Instances, and Database State
• In any database system, we need to differentiate between the description of
the database and the actual data in it.
1. Database Schema (Structure of the Database)
• Definition: A schema is the blueprint or design of the database.
• It defines tables, attributes, relationships, and constraints.
• Created during database design and does not change frequently.
• Stored in the DBMS catalog (metadata).
• Schema Diagram
• A visual representation of a schema.
• Shows record types, attributes, and constraints.
• Example: A STUDENT schema might include attributes like ID, Name, and Major.
• Does not include actual data.
2. Database Instance (Current Data in the Database)
• Definition: The actual data stored in the database at a specific moment in time.
• Also called database state or snapshot.
• Changes frequently as users insert, update, or delete data.
• Each schema construct (e.g., STUDENT) has its own set of instances (actual records).
• Example:
• Schema: STUDENT(ID, Name, Major, Age)
• Current instance (database state):

ID Name Major Age

1 John CS 20
2 Sarah Math 22
3 David Biology 21

When we add, delete, or update records, the database state changes.

 Difference Between Schema and Instance

Aspect Schema Instance (State)

Actual data stored at a specific

Definition Structure/blueprint of the database
moment

Frequency of Change Rarely changes Changes frequently

Actual student records stored in

Example STUDENT table definition
the table

Storage Stored in DBMS catalog Stored in database tables

4. Database State and Validity
• Initial State: The database is empty when first created.
• Valid State: A database must always satisfy constraints (e.g., primary keys must be
unique).
• Invalid State: If constraints are violated, the database enters an invalid state.
• State Transitions:
• Every insert, delete, or update changes the database state.
• The DBMS ensures that every new state is valid.
5. Schema Evolution (Changing the Schema)
• Although schemas do not change frequently, sometimes modifications are
needed.
• Example of Schema Evolution:
• Adding a new attribute: Adding Date_of_Birth to STUDENT.
• Removing an attribute: Removing Age from STUDENT.
• Changing data types or constraints.
• Modern DBMSs support schema evolution while the database is running.
• Three-Schema Architecture in DBMS
• The Three-Schema Architecture was developed to separate user applications
from the physical database, making database systems more flexible and
independent.
1. Key Characteristics of the Database Approach
• The three-schema architecture helps achieve three important database
characteristics:
• Self-Describing Nature:
• The database stores its own structure (schema) in a catalog.
• This makes it self-describing and easier to manage.
• Program-Data and Program-Operation Independence:
• Changes in data structure do not require changes in programs.
• This makes maintenance easier and cheaper.
• Multiple User Views:
• Different users see only the data relevant to them.
• This improves security and ease of use.
2. Three Levels of the Three-Schema Architecture
• The architecture has three levels to separate data storage, structure, and user
views.
1. Internal Level (Physical Level)
• Describes how data is stored in the database.
• Uses physical storage details, like file structures, indexing, and access paths.
• Uses physical data models (e.g., B-Trees, Hash Indexing).
• Example:
• Data is stored as files on disk, using tables and indexes.
2. Conceptual Level (Logical Level)
• Describes the overall database structure without focusing on storage details.
• Defines entities, relationships, constraints, and data types.
• Uses representational data models like the relational model.
• Example:
• Defines a STUDENT table with columns: ID, Name, Major, Age.
3. External Level (User View Level)
• Defines multiple user views, hiding unnecessary details.
• Each user group sees only relevant data.
• Uses representational or high-level models.
• Example:
• Admin View: Sees all student details.
• Student View: Sees only their own grades.
• How the Three-Schema Architecture Works
• A user query is made at the external level.
• The DBMS converts the query to match the conceptual level.
• The query is further converted to the internal level for execution.
• The retrieved data is reformatted to match the user's view.
• This process is called "mapping", and it ensures data independence.
4. Benefits of the Three-Schema Architecture
• ✔ Data Independence: Programs do not need to change if the data storage
changes.
✔ Security: Users see only relevant data, improving data privacy.
✔ Simplifies Management: Different user views can be defined easily.
✔ Flexibility: The conceptual schema can change without affecting the user
views.
6. Limitations of the Three-Schema Architecture
• Mapping between levels can be slow and complex.
• Not all DBMSs fully implement the architecture.
• Some small databases may skip the external level to improve speed.

•The Three-Schema Architecture separates physical storage, logical structure, and user views.

•It provides data independence, security, and flexibility.

•DBMSs use mappings to convert queries between levels.

•Though not all DBMSs fully implement it, the concept remains important in modern database design.
• Data Independence in DBMS
• Data independence is the ability to change the database schema at
one level without affecting the schema at the next higher level. It
helps in making databases flexible and easy to maintain.
• The Three-Schema Architecture helps in achieving data
independence by separating the physical storage, logical structure,
and user views.
1. Types of Data Independence
• There are two types of data independence:
• Logical Data Independence
• Physical Data Independence
1. Logical Data Independence
• Definition:
• The ability to change the conceptual schema without affecting external schemas or application
programs.
• Changes to the logical structure do not require changes in user views or programs.
• Example:
• Suppose we add a new attribute "Date_of_birth" to the STUDENT table.
• The user views that do not use "Date_of_birth" remain unchanged.
• Only the mappings between the conceptual and external levels need to be updated.
• Other Possible Changes:
• Adding new tables or attributes (e.g., adding a Phone_number field to STUDENT).
• Removing tables or attributes (e.g., removing GRADE_REPORT if not needed).
• Modifying constraints (e.g., enforcing students must have a unique email ID).
• Importance:
✔ Helps in database expansion or restructuring without modifying applications.
✔ Improves maintainability and flexibility.
✖ Harder to achieve because it requires programs to remain unaffected by schema changes.
2. Physical Data Independence
• Definition:
• The ability to change the internal schema without affecting the conceptual schema.
• Changes in data storage techniques, indexing, or compression should not impact logical
structure or user views.
• Example:
• Suppose we reorganize files on the disk for faster access.
• The conceptual schema (tables, attributes, relationships) remains unchanged.
• Queries like "list all students with GPA > 3.0" will work the same way but execute faster.
• Other Possible Changes:
• Changing file organization (e.g., switching from sequential to indexed storage).
• Adding new indexes (e.g., adding an index on Student_ID for faster search).
• Changing storage locations (e.g., moving data from one disk to another).
• Importance:
✔ Makes performance improvements easier without affecting users.
✔ Helps in adapting to new storage technologies (e.g., SSDs, cloud storage).
✔ Most modern DBMSs support good physical data independence.
 How Data Independence is Achieved
• DBMS Catalog & Mappings
• A DBMS catalog stores information about schemas and mappings between
schema levels.
• Mappings ensure that schema changes do not affect higher levels.
• Whenever a schema change happens, only the mapping needs to be updated.
• Example: Mapping Process
• User makes a query at the external level (e.g., "Get student names").
• DBMS converts the query to match the conceptual schema.
• DBMS accesses data from the physical level.
• Results are transformed back to match the external schema.
• This process ensures data independence, as changes in storage or structure do
not affect user queries.
✔ Data Independence allows schema changes without affecting users or applications.
✔ Logical Data Independence deals with changing database structure without affecting programs.
✔ Physical Data Independence deals with changing storage details without affecting the logical schema.
✔ The Three-Schema Architecture helps in achieving data independence, but mapping overhead can
cause performance issues.
Despite the challenges, data independence is essential for scalability, flexibility, and efficient database
management.
• DBMS Languages
• In a Database Management System (DBMS), different types of
languages are used to define, manipulate, and manage data. These
languages help in designing and using the database effectively.
• 1. Types of DBMS Languages
1. Data Definition Language (DDL)
• Purpose: Defines the structure of the database.
• Used by: Database Administrators (DBA) and database designers.
• Functions:
• Defines schemas (conceptual & internal).
• Creates, modifies, and deletes tables, indexes, and views.
• Stores schema details in the DBMS catalog.
• Example (SQL DDL Statements):
CREATE TABLE Students (
Student_ID INT PRIMARY KEY,
Name VARCHAR(50),
Age INT
);
Some DBMSs separate DDL into two types:

1.View Definition Language (VDL): Defines external schema (user views).

2.Storage Definition Language (SDL): Defines internal schema (storage details).
•Most modern DBMSs combine SDL and VDL into DDL.
2. Data Manipulation Language (DML)
• Purpose: Used to retrieve, insert, delete, and update data in the
database.
• Functions:
• Retrieves data from the database.
• Modifies data (insert, update, delete).
• Example (SQL DML Statements)

SELECT Name, Age FROM Students WHERE Age > 18;

INSERT INTO Students (Student_ID, Name, Age) VALUES (101, 'Alice',
20);
UPDATE Students SET Age = 21 WHERE Student_ID = 101;
DELETE FROM Students WHERE Student_ID = 101;
• Types of DML:High-Level (Non-Procedural) DML:
• Works on sets of data at a time.
• Declarative (describes what data to retrieve, not how).
• Example: SQL
• Low-Level (Procedural) DML:
• Works on one record at a time (record-at-a-time).
• Requires programming constructs (loops, conditions).
• Example: DL/1 (used in hierarchical databases).
3. Query Language
•A high-level DML used interactively is called a query language.
•Example: SQL queries executed in a database console.
•Used by: Casual users, analysts, and developers.
•Example Query:
SELECT * FROM Students WHERE Age > 18;

4. Embedded DBMS Languages

•Sometimes, DML commands are embedded in programming languages
like Python, Java, C++.
•The host language is the main programming language.
•The DML inside it is called the data sublanguage.
DBMS Interfaces

• A Database Management System (DBMS) provides different user-

friendly interfaces to help users interact with the database efficiently.
Here are the main types of DBMS interfaces:
1. Menu-Based Interfaces
• Displays a list of options (menus) for users to choose from.
• Users select options step-by-step to perform tasks without needing to
know query languages.
• Commonly used in web applications and browsing interfaces for
exploring data easily.
• Example: A website where users navigate through categories to find
products.
2. Forms-Based Interfaces
• Shows a form with fields where users can enter data.
• Users can fill in all fields to insert new data or leave some blank to search for
existing data.
• Designed for naive users (people with little database knowledge).
• Used in many business applications like banking systems.
• Example: A login form where users enter a username and password to access
an account.
3. Graphical User Interfaces (GUI)
• Displays the database structure as diagrams or visual elements.
• Users interact with diagrams, menus, and forms to create queries.
• Uses a pointing device (like a mouse) for selecting database elements.
• Example: Microsoft Access allows users to create and modify databases using
a GUI.
4. Natural Language Interfaces
• Users enter commands in English or another natural language.
• The system tries to understand the command and convert it into a database
query.
• If the system does not understand, it asks for clarification.
• Example: Google Search allows users to enter queries in normal language
(e.g., "show me the nearest restaurants").
5. Speech Input and Output Interfaces
• Allows users to speak instead of typing queries.
• Uses predefined words to understand commands.
• Converts database results into speech for output.
• Commonly used in telephone-based systems (like customer service
helplines).
• Example: Voice assistants like Siri or Google Assistant answering user queries.
6. Interfaces for Parametric Users
• Designed for users who perform repetitive tasks (e.g., bank tellers, store
cashiers).
• Uses function keys or shortcut commands for quick access to routine
operations.
• Example: A bank teller pressing a single button to check a customer’s account
balance.
7. Interfaces for Database Administrators (DBA)
• Includes privileged commands that only database administrators can use.
• Used for managing database accounts, granting permissions, changing
database structure, and optimizing performance.
• Example: A DBA using SQL commands to create a new user account or modify
a database schema.
• These interfaces help different types of users interact with databases
efficiently based on their needs and expertise.
DBMS Component Modules
• A Database Management System (DBMS) is a complex software system
made up of multiple components. It interacts with various users, operating
systems, and other software.
1. DBMS Components Overview
• A DBMS consists of two main parts:
• User Interfaces – Used by database administrators, programmers, and end-users.
• Internal DBMS Modules – Handle storage, query processing, and transaction
management.
• The database and DBMS catalog are stored on disk and accessed by the
DBMS when needed.
• The operating system (OS) helps manage disk read/write operations, but
many DBMSs have their own buffer management system to improve
performance.
2. User Interfaces and Their Roles
• Users interact with the DBMS in different ways:
• Database Administrators (DBAs)
• Define and manage the database.
• Use Data Definition Language (DDL) to create and modify database structures.
• Monitor and optimize database performance.
• Casual Users
• Use interactive query interfaces to search for information.
• Queries are checked for errors by a query compiler, which converts them into an optimized format
for execution.
• Application Programmers
• Write programs in languages like Java, C, or C++.
• Use Data Manipulation Language (DML) commands to interact with the database.
• A precompiler extracts these commands and sends them to the DML compiler for execution.
• Parametric Users
• Perform repetitive transactions like bank deposits or ticket bookings.
• Use canned transactions (predefined programs) and enter only required parameters (e.g., account
number and amount for withdrawal).
3. Internal DBMS Modules
• A. Query Processing & Optimization
• When a user submits a query, it goes through multiple steps:
• Query Parsing – Checks for syntax errors.
• Query Compilation – Converts query into an internal form.
• Query Optimization – Improves performance by selecting the best execution
method (e.g., using indexes or rearranging operations).
• Execution – Executes the optimized query and retrieves results.
• B. Runtime Database Processor
• Executes three main tasks:
• Privileged commands (used by DBAs).
• Optimized queries from the query processor.
• Canned transactions with user-supplied parameters.
• Works with the system catalog to retrieve metadata and update statistics.
• Uses the stored data manager to manage disk read/write operations.
C. Storage Management & Buffering
• The stored data manager interacts with the operating system to
perform input/output (I/O) operations.
• The buffer manager handles memory storage, reducing disk access to
improve performance.
• Some DBMSs use their own buffer management instead of relying on
the OS.
D. Transaction Management (Concurrency & Recovery)
• Ensures multiple users can access the database without conflicts.
• Includes concurrency control to manage simultaneous transactions.
• Implements backup and recovery mechanisms to prevent data loss.
4. Client-Server Architecture
• Modern databases use a client-server model, where:
• The client program runs on a user’s computer and accesses the DBMS.
• The database server stores and manages the actual data.
• An optional application server can act as a middle layer between the client and database
server.
5. Interaction with Other System Software
• A DBMS interacts with:
• Operating System (OS) – Manages disk access and memory allocation.
• Compilers – For executing database commands written in host programming
languages (Java, C, etc.).
• Network Interface – Supports connections between client programs, application
servers, and the database server.
A DBMS is a multi-layered system that provides user-friendly interfaces, manages data
efficiently, and ensures smooth transaction processing. Its components work together
to optimize performance, maintain security, and support multiple users
simultaneously.
• Database System Utilities
• A Database Management System (DBMS) includes database utilities that help the
Database Administrator (DBA) manage, optimize, and maintain the database system.
These utilities perform tasks like loading data, backing up the database, reorganizing
storage, and monitoring performance.
1. Common Database Utilities
Loading Utility (Data Import & Conversion)
• Used to import data from external files (e.g., text files, spreadsheets) into the database.
• Converts data from source format (original file format) to target format (database
structure).
• Often used when migrating data from one DBMS to another.
• Special conversion tools are available for different DBMS types. Example:
• IBM’s IMS (Hierarchical DBMS).
• IDMS (Network DBMS by Computer Associates).
• SUPRA (Cincom).
• IMAGE (HP).
• Backup Utility (Data Protection & Recovery)
• Creates a backup copy of the entire database to protect against data loss.
• Two types of backups:
• Full Backup – Copies the entire database.
• Incremental Backup – Saves only new changes since the last backup (saves storage
space).
• Backups help restore data in case of:
• Disk failure or corruption.
• System crashes or cyberattacks.
• Database Storage Reorganization Utility (Performance Optimization)
• Helps rearrange database files to improve performance.
• Can change file organization (e.g., from sequential to indexed).
• Creates new access paths for faster retrieval of data.
• Reduces fragmentation and optimizes disk space usage.
• Performance Monitoring Utility
• Tracks database usage and performance.
• Provides statistics about:
• Query execution times.
• Storage usage.
• User activity.
• Helps the DBA decide when to:
• Reorganize files.
• Add or remove indexes.
• Optimize queries for better performance.
• Other Database Utilities
• Sorting Utility – Arranges records in a specific order.
• Data Compression Utility – Reduces the size of stored data to save space.
• User Access Monitoring – Tracks who accesses the database and what changes they
make.
• Network Interface Utility – Manages connections between users and the database over a
network.
2. Tools, Application Environments, and Communication Facilities
• CASE Tools (Computer-Aided Software Engineering)
• Used for designing and developing database systems.
• Helps create database schemas, relationships, and constraints.
• Automates parts of the database design process.
• Data Dictionary (Information Repository)
• Stores detailed metadata (data about data).
• Contains information such as:
• Database schema (structure).
• Constraints and relationships.
• Application program descriptions.
• User information and access permissions.
• Difference from DBMS catalog:
• The DBMS catalog is mainly used by the DBMS itself.
• The Data Dictionary is wider in scope and can be accessed by users and the DBA.
• Application Development Environments
• Provide tools for developing database applications.
• Examples of popular tools:
• PowerBuilder (Sybase).
• JBuilder (Borland).
• Features:
• Graphical User Interface (GUI) development.
• Querying and updating data.
• Application programming with database connectivity.
• Communication Software & Distributed DBMS
• Allows remote users to access the database from different locations.
• Uses communication hardware and networks, such as:
• Internet routers.
• Phone lines.
• Local Area Networks (LANs).
• Satellite communication.
• Some Distributed DBMSs store data across multiple machines connected by a network.
• The integrated system that connects DBMS and communication software is called a DB/DC system.
Database Architectures
• Database Management Systems (DBMSs) have evolved over time,
following trends in computer system architectures. Different
architectures determine how database components are distributed
between users and servers.
1. Centralized DBMS Architecture
What is a Centralized DBMS?
In early computer systems, all database functions (processing, storage,
user interface) were handled by a single central computer (mainframe).
• Users accessed the database through simple terminals that could only
display information but had no processing power.
• The central computer processed everything, while terminals only sent
input and displayed results.
• How Centralized DBMS Works
• All users are connected to a single machine that performs:
• Data storage & retrieval.
• User authentication & security.
• Query processing.
• Application execution.
• The entire database is stored in one location, making it easy to manage but harder to
scale.
• Limitations of Centralized DBMS
• Single point of failure – If the central computer crashes, the entire system goes
down.
• High processing load – As the number of users grows, the central computer struggles
to handle all tasks.
• Limited scalability – Expanding the system requires upgrading a single machine,
which can be expensive.
• With the decline in hardware costs, companies started using PCs and workstations,
leading to client/server architectures.
2. Client/Server DBMS Architecture
• What is Client/Server Architecture?
• This architecture splits database functions between clients (user machines) and
servers (database machines).
• Clients handle user interfaces and some processing, while servers handle data storage
and query processing.
• Clients and servers communicate over a network (LAN, WAN, or Internet).
• How Client/Server Architecture Works
• Clients (PCs, Workstations)
• Handle user interfaces (GUI).
• Run local applications (e.g., web browsers, ERP software).
• Send requests to the server when database access is needed.
• Servers (Database Machines)
• Handle data storage and management.
• Process SQL queries and transactions.
• Respond to client requests with query results.
• Specialized servers can also be used, such as:
• File servers – Manage file storage.
• Printer servers – Handle print jobs.
• Web servers – Host websites
• Advantages of Client/Server DBMS
• ✔ Better performance – Processing is split between client and server.
✔ Easier maintenance – Updates can be applied to the server
without changing client software.
✔ Scalability – Can add more clients without overloading a single
machine.
• Two common client/server architectures are:
• Two-Tier Architecture
• Three-Tier (or n-Tier) Architecture
3. Two-Tier Client/Server Architecture
• What is Two-Tier Architecture?
• Database functions are split into two layers:
• Client Tier – User interface and application programs.
• Server Tier – Database storage and query processing.
• The client connects to the database server via SQL queries.
• How Two-Tier Architecture Works
• The client runs an application and needs database access.
• It sends an SQL query to the database server.
• The server processes the query and sends results back to the client.
• The client displays the data to the user.
• Key Technologies Used in Two-Tier Architecture
• SQL Server – The server processes SQL queries.
• Open Database Connectivity (ODBC) – A standard API that allows clients to
connect to different databases.
• Java Database Connectivity (JDBC) – A Java API for database connections.
• Advantages of Two-Tier Architecture
• ✔ Simple and easy to implement.
✔ Efficient for small to medium-scale applications.
✔ Compatible with existing systems.
• Disadvantages of Two-Tier Architecture
• ❌ Limited scalability – Adding too many clients can overload the database server.
❌ Security risks – Clients connect directly to the database, increasing vulnerability.
• To solve these issues, Three-Tier and n-Tier architectures were introduced.
4. Three-Tier and n-Tier Architectures
• What is Three-Tier Architecture?
• Adds a middle layer (Application Server) between the Client and Database Server.
• The three layers are:
• Client Layer (GUI) – User interface and input.
• Application Layer (Middle Tier) – Handles business rules, application logic, and security
checks.
• Database Layer – Stores and processes data.
• How Three-Tier Architecture Works
• The client sends a request (e.g., login, data retrieval) to the application server.
• The application server processes the request, checks security, and sends a query
to the database.
• The database server processes the query and sends the results back to the
application server.
• The application server formats the results and sends them to the client.
• Benefits of Three-Tier Architecture
• ✔ Improved security – The client doesn’t directly access the database.
✔ Better performance – The application server reduces the load on the database.
✔ Scalability – New clients and servers can be added easily.
✔ Flexible business logic – The application server can enforce business rules before accessing
data.
• n-Tier Architecture (Beyond Three-Tier)
• Some systems further divide the middle tier into multiple layers, creating 4-tier or 5-tier
architectures.
• Example of additional layers:
• Presentation Layer – Manages user interface.
• Business Logic Layer – Handles application rules.
• Data Access Layer – Manages communication with the database.
• Used in Enterprise Resource Planning (ERP) and Customer Relationship Management (CRM)
systems.
• Security and Data Transfer in Multi-Tier Architectures
• Data is transferred between layers securely using encryption.
• Middleware helps in managing communication between different tiers.
• Data compression is used to speed up data transfer over networks.
• Centralized DBMSs were used in early systems but had scalability
issues.
• Client/Server DBMSs distributed processing between client machines
and database servers.
• Two-Tier Architecture worked well but had security and scalability
issues.
• Three-Tier Architecture solved these issues by adding an Application
Server.
• n-Tier Architectures are used in large enterprise applications for
better scalability and security.
• With advancements in networking, cloud computing, and security,
multi-tier architectures continue to evolve, supporting large-scale
web and enterprise applications.
Classification of DBMS
1. Centralized DBMS Architecture
• Early databases ran on mainframe computers, which handled all
processing.
• Users accessed these databases using terminals (simple screens with
no processing power).
• As PCs and workstations became popular, they replaced terminals.
• However, the DBMS remained centralized, meaning all database
functions were handled by a single machine.
2. Client/Server DBMS Architecture
• Client/server architecture emerged as computers became more powerful.
• Instead of one central machine, tasks were split between clients and servers.
• Clients (PCs, workstations) handled user interfaces and some processing.
• Servers handled database storage and heavy processing.
• Basic Client/Server Concept
• Clients request data from servers via a network.
• Specialized servers (e.g., file servers, printer servers, web servers) handle different tasks.
• DBMSs adopted this approach, leading to two-tier and three-tier architectures.
3. Two-Tier Client/Server DBMS Architecture
• Client side: Runs user interface and application logic.
• Server side: Runs the DBMS and processes SQL queries.
• Clients connect using ODBC (for general applications) or JDBC (for Java applications).
• The database server executes queries and returns results to the client.
• Some object-oriented DBMSs distribute processing between client and server more
efficiently.
4. Three-Tier and n-Tier Architectures
• Three-tier architecture adds an application server between the client
and database.
• The application server handles:
• Business logic
• Security checks
• Processing requests before sending them to the database
• This improves security and efficiency.
• n-Tier architecture expands this further (e.g., separate layers for
security, business logic, etc.).
• Used in ERP (Enterprise Resource Planning) and CRM (Customer
Relationship Management) systems.
5. Heterogeneous Distributed DBMS (DDBMS)
• Different sites can use different DBMS software.
• Middleware can be used to connect them.
• This is called a federated DBMS (or multidatabase system).
• Each system maintains some level of autonomy.
6. DBMS Cost Classification
• Open-source DBMS: Free (e.g., MySQL, PostgreSQL).
• Commercial DBMS: Can be very expensive, often modular.
• License types:
• Site license: Unlimited use at a location.
• User-based license: Limits number of users.
• Single-user license: For personal or standalone use.
• Additional costs for features like:
• Distribution
• Parallel processing
• Data replication
• Data warehousing
7. DBMS Classification by Data Model
(a) Relational Model
• Represents data as tables (relations).
• Uses SQL for queries.
• Supports views for different users.
• Most modern DBMSs are relational.
(b) Object-Oriented Model
• Defines objects with properties and methods.
• Objects are grouped into classes.
• Uses hierarchies to structure data.
• Object-Relational DBMS combines relational and object-oriented
models.
(c) XML Model
•Uses hierarchical tree structures.
•Data is represented using tags (like HTML).
•Useful for web-based applications.
•Many DBMSs have XML support.

(d) Legacy Models

Network Model
•Uses record types linked with pointers.
•Set types define relationships (one-to-many).
•Commands:
•FIND ANY (to locate records)
•GET OWNER (to traverse relationships)
•Examples: IDMS, IMAGE, SUPRA DBMSs
Hierarchical Model
•Uses tree structures (parent-child relationships).
•Each parent can have multiple children, but a child has only one parent.
•Commands:
•GET NEXT (to navigate)
•INSERT (to add records)
•Example: IBM IMS (used in banking, government, healthcare).

•Centralized DBMSs used mainframes.

•Client/Server DBMSs split processing between client and server.
•Three-tier/n-tier architectures add layers for security and business logic.
•Heterogeneous DDBMSs can run different DBMS software at different sites.
•DBMSs vary in cost, from free to multimillion-dollar systems.
•DBMSs are classified by data model:
•Relational (tables, SQL)
•Object-Oriented (objects, classes)
•XML (hierarchical, tag-based)
•Legacy (Network, Hierarchical)