1.

Data Model
A Data Model is a set of concepts that can be used to describe the structure
of the database, the different relationship and constraints which hold the
data in the database.
Note: Data model emphasizes on what data is needed and how it should be
organized instead of what operations need to be performed on the data. Data
Model is like architect's building plan which helps to build a conceptual model
and set the relationship between data items.
A Data Model has three components:
i)A structural part, consisting of a set of rules according to which databases
can be constructed.
ii)A manipulative part, defining the types of operations that are allowed on
the data such as, updating, retrieving the data from the database and
changing the structure of the database.
iii)A set of integrity rules, which ensure that the data is accurate.
The purpose of the Data Model is to represent the data and to make the data
understandable.
2. Data Abstraction
Data abstraction generally refers to the suppression (hiding) of details of data
organization and storage, and the highlighting of only the essential features
for an improved understanding of data. One of the main characteristics of
the database approach is to support data abstraction so that different users
can perceive data at their preferred level of detail.
Note: (Data Abstraction refers to hiding the details of data storage and
represent the user with a conceptual view of the database.)
[Link] of Data model
Data Models are categorized into three types:
1)Representational /Implementation data model
2)High level/Conceptual/Logical Data model
3)Physical Data model

A Data Model is simply a way to describe how data is stored, organized, and
related inside a database.
1. Hierarchical Data Model
A hierarchical data model was one of the earliest data models. It was
developed by IBM in [Link] model was a file based model build like a tree.
In this tree, parent could be associated to multiple child nodes, but a child
node could have only one parent.
In Hierarchical database model records are logically organized into a hierarchy
of relationship. In this model of database, each node is related to the others in
a parent-child relationship.
 Data is organized like a tree (parent-child relationship).
 The records of hierarchy are called nodes.
 The topmost node is called the root node.
 Each node has exactly one parent.
 One parent may have many child.
🔹 Examples:
1. A company database → Department (parent) → Employees (children).
2. Library system → Category (parent) → Books (children).

 University Database → Department (Parent) → Professors (Children).

o Dept. of CS → Prof. A, Prof. B
o Dept. of Math → Prof. C

Advantages

 It is organised into ‘One-to-Many’ relationship.

 Fast for searching (tree traversal is efficient).
 Clear parent-child relationship (easy to understand).

Disadvantages

 Difficult to modify structure (adding a new relationship is complex).

 Can’t handle many-to-many relationships well (e.g., a professor teaching
in multiple departments).
 When the parent node is deleted, all the child nodes are deleted
automatically.
 In the case of directories and files, it could be said that a single directory
further contained multiple files or directories, those directories then
contained more files and so on.
2. Network Data Model
It is a modified version of the hierarchical database (extension of the
Hierarchical database model).
Data is organized using nodes and links (like a graph).
 A child can have multiple parents (more flexible than hierarchy).
  It accept ‘many-to-many’ relationship.

In the above figure, Project is the root node which has two children i.e.
Project 1 and Project 2. Project 1 has 3 children and Project 2 has 2 children.
Total there are 5 children i.e Department A, Department B and Department
C, they are network related children as we said that this model can have
more than one parent. So, for the Department B and Department C have
two parents i.e. Project 1 and Project 2.
🔹 Examples:
1. Student-Course database → Students can enroll in many courses, and
courses have many students.
2. Airline database → A flight can connect to many airports, and airports
can have many flights.
Advantages:
  Handles complex relationships well (many-to-many).
 Searching is faster than hierarchical database model.
 More flexible than hierarchical model.
Disadvantages:
 Complex structure, harder to design and maintain.
 Requires understanding of pointers and links.

Difference between Hierarchical Data Model and Network Data Model :

Hierarchical Data Model Network Data Model

In this model, you could create a network

In this model, to store data hierarchy method that shows how data is related to each
is used. other.

It implements 1:1, 1:n and also many to

It implements 1:1 and 1:n relations. many relations.

To organize records, it uses tree structure. To organize records, it uses graphs.

Records are linked with the help of linked

Records are linked with the help of pointers. list.

Insertion anomaly exits in this model i.e. There is no insertion anomaly.

child node cannot be inserted without the
 Hierarchical Data Model Network Data Model

parent node.

Deletion anomaly exists in this model i.e. it is

difficult to delete the parent node. There is no deletion anomaly.

It is used to access the data which is complex It is used to access the data which is
and asymmetric. complex and symmetric.

There is partial data independence in this

This model lacks data independence. model.

3. Relational Data Model (Most Popular)

This model was Proposed by E.F. Codd in 1969. The database model which is
arranged in two-dimensional tables is known as relational database model. It is
very easy for a user to develop and understand. The basic structure of data in
the relational model is tables. In this database model, tables are known as
relations. All the information related to particular subject is stored in rows of
the table. Normalization of the database is possible. It has very less data
redundancy rate. It is more complex than other database models. No
processing can be done without establishing the data relationships.
 Data is stored in tables (rows and columns).
 Relationships are represented using keys.
Table 1: Table 2:
Roll no. First Last name Id_st Subject Teacher
name
1 Peter Buffet 111 Math Mark

2 Harry Parker 222 Science Dre

3 Sanjay Dhakal 333 English Denial

Relational Table

Roll no. Id_st Marks

1 111 80

2 222 65

3 333 95

Relational database model

Characteristics of Relational Data Model

The Relational Data Model organizes data into tables (relations) made up of
rows (tuples) and columns (attributes).

Main Characteristics:

1. Data is stored in tables → Each table has rows and columns.

Example: Student table with columns (ID, Name, Age).
2. Unique rows (tuples) → No two rows are exactly the same.
Example: Two students cannot have the same ID.
3. Columns have unique names → Each attribute (column) must have a
distinct name.
4. Order of rows and columns does not matter → The way data is
arranged in a table does not affect its meaning.
5. Keys are used → Primary key, foreign key, etc., are used to maintain
uniqueness and relationships.
6. Relationships between tables → Established using keys (e.g., Student ID
links Student table and Enrollment table).
7. Data Integrity rules → Enforces constraints (Primary Key, Foreign Key,
Not NULL, etc.) to ensure correctness.

Three Components of Relational Data Model

 1. Structural Component
o Defines how data is stored.
o Data is organized into relations (tables) with rows and columns.
o Example: EMPLOYEE (EmpID, Name, Salary).
2. Integrity Component
o Defines rules to keep data accurate and consistent.
o Includes Primary Key, Foreign Key, Constraints (Not NULL,
Unique, Check).
o Example: Salary > 0, EmpID must be unique.
3. Manipulative Component
o Defines operations to access and manipulate data.
o Relational Algebra and SQL (Structured Query Language) are
used.
o Example: SELECT Name FROM EMPLOYEE WHERE Salary > 30000;

Examples:
1. Banking system → Customers table, Accounts table, linked by
CustomerID.
2. E-commerce → Products table, Orders table, Customers table.
Advantages:
 Very easy to use and understand (tables).
 SQL makes querying simple.
  Supports data integrity using keys and constraints.
 It has very less data redundancy rate.
 Normalization of the database is possible.
 Rapid processing of database is possible.
 It provides security as only authorized users can access the data.
Disadvantages:
 Can be slower for very complex queries.
 Needs good hardware for very large databases.
 It is more complex than other models
 It is very hard to understand so it is not user-friendly.
 It is expensive while setting and maintaining the database system.
 Maintenance problem as it is difficult to handle large data

4. Entity-Relationship (ER) Model

 A high-level conceptual model used for database design.
 Uses entities (rectangles), attributes (ovals), and relationships
(diamonds).
Examples:
1. University ER Model → Entities: Student, Course, Professor.
2. Hospital ER Model → Entities: Patient, Doctor, Appointment.
Advantages:
 Very easy to design and visualize.
 Great for communication between designers and users.
Disadvantages:
 Cannot be directly implemented (needs conversion to relational model).
  Limited for very complex systems.

5. Object-Oriented Data Model

Represents data as objects (like in Object-Oriented Programming).

Each object has attributes (properties) and methods (functions/behavior).

 Key idea: The database directly stores objects, their state, and behavior.

Example (Library System – OODM):

 Class: Book
o Attributes: title, author, ISBN
o Methods: borrow(), returnBook()
 Class: Member
o Attributes: memberID, name
o Methods: register(), payFine()
 Relationship: A Member can borrow multiple Book objects.

Here the data + operations are stored together.

Examples:
1. Multimedia Database → Objects like Audio, Video, Images with methods
like Play(), Pause().
2. CAD/CAM Database → Object for MachinePart with attributes (size,
material) and methods (assemble(), test()).
Advantages:
 Best for applications like multimedia, engineering, simulations.
 Reusability of objects.
 Real-world modeling: Represents real-world entities as objects (with
attributes & methods), making it easier to understand.
Example: A Car object can have attributes (color, model) and methods
(start(), stop()).
 Encapsulation: Data + behavior are stored together, improving security
and reusability.
  Inheritance: Allows reuse of code through class hierarchies.
Example: A Truck can inherit properties from Vehicle.
 Complex data handling: Can store multimedia, CAD/CAM data, maps,
etc.
 Better performance for complex applications: Useful in AI, engineering,
simulations.
Disadvantages:
 Not widely adopted: Pure OODBMSs are less popular compared to
relational systems.
 Complex to design and implement: Requires learning object concepts
deeply.
 Lack of standardization: Different OODBMS vendors use different
approaches.
 Query language is less developed: Not as standardized as SQL.
2. Object-Relational Data Model (ORDM)
A hybrid model that extends the Relational Model (tables, rows, columns) by
adding object-oriented features such as:
o User-defined types
o Inheritance
o Complex data types (arrays, multimedia, JSON, XML)
o Methods (but still queryable using SQL)
Example (Library System – ORDM):
 Table Book:
o Columns: ISBN, title, author
o Type: author could be a User-Defined Type (UDT) with fields
firstName, lastName.
 Table Member:
o Columns: memberID, name, address
  Relationship: A borrowedBooks column in Member could store an array
of ISBNs.
Still table-based, but can handle objects and complex types inside tables.

Advantages:

 Bridges relational & object worlds: Combines tables (RDBMS) + objects

(OOP).
Example: A Customer table can store normal attributes (Name, Email)
and also complex objects like an Address type.
 Backward compatibility: Supports relational features + new object
features, so existing RDBMS users can upgrade easily.
 Supports complex data types: Can handle multimedia, spatial, and user-
defined types better than pure relational DB.
 SQL support extended: Uses SQL with object extensions (e.g., ORACLE,
PostgreSQL).

Disadvantages:

 More complex than RDBMS: Harder to design and maintain than simple
relational DB.
 Performance overhead: Object features may slow down execution
compared to pure RDBMS.
 Limited vendor support: Some DBMS support OR features only partially.
 Learning curve: Requires understanding both relational & object
concepts.

 In OODM, a Student object directly stores Name, Marks, and method

calculateGrade().
 In ORDM, a Student table stores relational fields, but Marks could be a
user-defined object type, and queries are done using SQL + object
extensions.

3. Key Differences Between OODM and ORDM

 Object-Oriented Data Model Object-Relational Data Model
Feature
(OODM) (ORDM)

Stores real objects directly (data Stores data in tables but supports
Storage
+ methods together). objects as complex datatypes.

Uses OQL (Object Query Uses extended SQL (with object

Querying
Language). features).

Full object orientation –

Mainly relational but extended
Focus encapsulation, inheritance,
with object-oriented features.
polymorphism.

Very flexible, but less More practical since it builds on

Flexibility
standardized. relational DBs.

Storing Book in a table, but author

Storing a Book object with
Example as a structured type (firstName,
methods like borrow().
lastName).

 OODM → Like storing real-world objects directly in the database.

 ORDM → Like storing objects inside relational tables with extra power.
1. Database Schema

A database schema is the blueprint or structure of the database. It defines

how the data is organized (tables, fields, relationships, constraints).

Example:
Imagine a school database schema:

 Student Table → (RollNo, Name, Class, Age)

 Teacher Table → (TID, Name, Subject)
 Course Table → (CourseID, CourseName, Credits)

Here, schema tells us what tables exist, their attributes, and how they are
connected.

Schema is like the design/plan of a building before construction.

2. Database Instance

A database instance is the actual data stored in the database at a particular

moment in time.
Using the same school database schema:

 Student Table

RollNo Name Class Age

101 Ravi 10 15
102 Priya 12 17

Teacher Table

TID Name Subject

T01 Meena Maths
T02 Suresh Science

This current set of data is the database instance.

Instance is like the actual furniture and people inside the building at a given
time.

3. Schema Diagram

A displayed schema is called a Schema diagram. Each object in the schema is

called Schema construct.

A schema diagram is a visual representation of the database schema. It shows

tables (entities), columns (attributes), and how they are related (keys &
relationships).

Example (School Database Schema Diagram):

 Student Table

RollNo Name Class Age

STUDENT (RollNo, Name, Class, Age)
|
| Enrolled_in
|
COURSE (CourseID, CourseName, Credits)
|
| Taught_by
|
TEACHER (TID, Name, Subject)

Schema diagram is like the architectural drawing of a building.

DBMS Architecture

There are two different types of DBMS Architecture

1. Logical DBMS Architecture ( Three schema architecture) : It deals with the
way the data is stored and presented to the user.
2. Physical DBMS Architecture: It is concerned with the software components
that make up a DBMS.
A database schema can be divided broadly into two categories −
 Physical Database Schema − This schema pertains to the actual storage
of data and its form of storage like files, indices, etc. It defines how the
data will be stored in a secondary storage.
 Logical Database Schema − This schema defines all the logical
constraints that need to be applied on the data stored. It defines tables,
views, and integrity constraints.
Important terms

1. Empty State

When a database is created but has no data in it. Only the structure (tables,
attributes) exists.
Example:
If we create a Student Table:

CREATE TABLE Student(RollNo INT, Name VARCHAR(20), Class INT);

At this point, the table exists but has 0 rows → this is an Empty State.
2. Initial State

The first time data is inserted after creating the schema, the database is in its
initial state.
Example:
If we insert:

RollNo Name Class

101 Ravi 10

102 Priya 12

This first set of records after creation is the Initial State.

3. Valid State

A database state that follows all constraints and rules defined in the schema.
Example:
If RollNo is a Primary Key (must be unique), then:
Valid:

RollNo Name Class

101 Ravi 10

102 Priya 12

Invalid:

RollNo Name Class

101 Ravi 10

101 Priya 12

So only the first is a valid state.

4. Metadata

Metadata is “data about data” – it describes the structure of the database.

Example:
For Student Table, the metadata is:

 RollNo → Integer, Primary Key

 Name → Varchar(20), Not Null
 Class → Integer

This information is stored in the DBMS catalog.

5. Intension of Schema

The permanent description/definition of the database (schema). It does not

change frequently.
Example:
Schema definition of Student:

Student(RollNo, Name, Class)

This structure is called the Intension of the schema.

6. Extension of Schema

The actual data/records stored in a database at a particular time. It changes

frequently as rows are inserted/deleted.
Example:
The data in Student Table at some point in time:

RollNo Name Class

101 Ravi 10

102 Priya 12

This is the Extension of the schema.

DBMS ArchitectureDBMS Architecture describes how the database system is
designed, structured, and how its different components interact with each
other to manage data.

 There are mainly two views of DBMS architecture:

Logical vs Physical DBMS Architecture

Logical Architecture

 Describes how data is organized and viewed by users.

 Focuses on abstraction (what data is stored, not how it’s stored).
 Based on Three-Schema Architecture (external, conceptual, internal).

Example:
You know there’s a Student table with RollNo, Name, Class, but you don’t care
about how or where it’s stored on disk.

Physical Architecture

 Describes how data is actually stored in the computer system.

 Focuses on implementation details – file organization, indexing, data
blocks, memory, access paths, etc.

Example:
The DBMS stores Student data in a file with B+ tree index on RollNo and keeps
it in disk blocks/pages.

Logical Architecture or Three-Schema Architecture

It divides database into 3 levels:

(a) External Schema (View Level)

 Closest to the end users.

 Defines different views of the database for different users.
 Each user doesn’t see the whole database → only what they need.

Example:

 Teacher’s View: Student(RollNo, Name, Class)

 Admin’s View: Student(RollNo, Name, Class, Fees, Address)

(b) Conceptual Schema (Logical Level)

 Describes the whole database logically.

 Defines all entities, attributes, and relationships.
 Independent of physical storage.

Example:
Student(RollNo, Name, Class, Fees, Address)

This is the complete logical structure seen by the DBMS.

(c) Internal Schema (Physical Level)

 Closest to storage.
 Defines how the data is physically stored in files, indexes, blocks.
 Includes record formats, access paths, storage size.

Example:

 Student data stored in a file [Link]

 RollNo indexed using a B+ Tree
 Data stored in 4KB disk blocks.

Together, this is called 3-Schema Architecture. It provides Data Independence:

 Logical Data Independence → change conceptual schema without

affecting external views.
 Physical Data Independence → change storage without affecting
conceptual schema.

What is Mapping?

The processes of transforming the request and results between different levels
of the schema is called Mappings. Mapping connects different schema levels in
the 3-schema architecture to ensure smooth communication.

Types of Mapping:

(a) External / Conceptual Mapping

 Maps user views (external schema) to the conceptual schema.

 Ensures that whatever the user requests is fetched from the logical
database.
Example:
If a teacher only sees (RollNo, Name, Class) but conceptual schema has
(RollNo, Name, Class, Fees, Address) → Mapping hides Fees and Address.

(b) Conceptual / Internal Mapping

 Maps conceptual schema to the internal schema.

 Ensures that logical structure is correctly translated into physical
storage.

Example:
Conceptual Schema:
Student(RollNo, Name, Class, Fees, Address)
Internal Schema:

 Stored in [Link] file

 Indexed on RollNo

This mapping ensures queries on RollNo use index, not full scan.

(c) External / Internal Mapping (indirect)

 When user requests data, the DBMS goes through:

External → Conceptual → Internal
and while storing:
Internal → Conceptual → External.

What is Data Independence?

Data independence means the ability to change the database structure

(schema) at one level without affecting the schema at the next higher level.

It’s one of the most important features of DBMS, because it separates data
storage from data usage.
Types of Data Independence

There are two types:

1. Logical Data Independence

 Ability to change the conceptual (logical) schema without changing the

external (user view) schema or application programs.

✅ Example:

 Suppose you have a Student table:

(RollNo, Name, Class)
 Later, you add a new attribute Email.
 Applications that only access (RollNo, Name, Class) should still work fine,
because their view hasn’t changed.

This is hard to achieve, but very important because user applications don’t
need to change when database design changes.

2. Physical Data Independence

 Ability to change the internal schema (physical storage) without

changing the conceptual schema.
 That means: how data is stored on disk can change without affecting
how data is logically organized.

Example:

 Student table is stored in Heap file (unsorted).

 Later, DB Admin changes it to a B+ Tree index for faster search.
 The Conceptual Schema (Student table definition) remains the same →
so application queries (SELECT Name FROM Student WHERE RollNo=101)
will work the same.

This is easier to achieve because DBMS hides storage details from users.
 What is
Type What changes? Example
unaffected?

Adding "Email" field

Conceptual schema External schema
Logical Data to Student table
(tables, attributes, (user views,
Independence without affecting old
relationships) applications)
programs

Internal schema
Conceptual Changing file
Physical Data (indexes, storage
schema (logical organization from
Independence format, access
design of tables) heap to B+ tree
paths)

Why Important?

 Reduces application maintenance cost.

 Makes databases more flexible.
 Allows DBAs to optimize storage and performance without bothering
developers.

Database Languages

In a DBMS (Database Management System), different languages are used to

create, manage, and manipulate the database.

The main database languages are:

1. DDL (Data Definition Language)

2. DML (Data Manipulation Language)
3. DCL (Data Control Language)
4. TCL (Transaction Control Language)

1. DDL (Data Definition Language)

Used to define and manage the structure of the database (schemas, tables,
views, indexes).

Commands:
  CREATE → to create a new database object (table, schema, view, index).
 ALTER → to modify an existing object.
 DROP → to delete a database object.
 TRUNCATE → to remove all records from a table (but keep structure).

Example:

-- Create a Student table

CREATE TABLE Student (
RollNo INT PRIMARY KEY,
Name VARCHAR(50),
Class VARCHAR(20)
);

-- Add a new column

ALTER TABLE Student ADD Email VARCHAR(100);

-- Delete the table

DROP TABLE Student;

2. DML (Data Manipulation Language)

Used to manipulate the data stored in database objects.

Commands:

 INSERT → add new records

 UPDATE → modify existing records
 DELETE → remove records
 SELECT → retrieve data (this is the most used command)

Example:

-- Insert a record
INSERT INTO Student VALUES (101, 'Rahul', 'BCA', 'rahul@[Link]');

-- Update a record
UPDATE Student SET Class = 'MCA' WHERE RollNo = 101;

-- Delete a record
DELETE FROM Student WHERE RollNo = 101;
-- Retrieve data
SELECT Name, Class FROM Student;

3. DCL (Data Control Language)

Used to control access and permissions in the database.

Commands:

 GRANT → give access/permission

 REVOKE → remove access/permission

Example:

-- Grant SELECT permission on Student table to user 'Ravi'

GRANT SELECT ON Student TO Ravi;

-- Remove permission
REVOKE SELECT ON Student FROM Ravi;

4. TCL (Transaction Control Language)

Used to manage transactions in the database.

A transaction = a group of SQL statements executed together (either fully done
or not at all).

Commands:

 COMMIT → save changes permanently

 ROLLBACK → undo changes
 SAVEPOINT → mark a point within a transaction to rollback later

Example:

-- Start transaction
INSERT INTO Student VALUES (102, 'Sneha', 'BCA', 'sneha@[Link]');

SAVEPOINT S1;

UPDATE Student SET Class = 'MCA' WHERE RollNo = 102;

-- Rollback to savepoint (undo update)
ROLLBACK TO S1;

-- Commit insert permanently

COMMIT;

Summary Table
Language Purpose Commands Example

Defines database CREATE, ALTER, DROP, CREATE TABLE Student

DDL
structure TRUNCATE (...)

INSERT, UPDATE,
DML Manipulates data INSERT INTO Student ...
DELETE, SELECT

GRANT SELECT ON
DCL Controls access GRANT, REVOKE
Student TO Ravi

Manages COMMIT, ROLLBACK,

TCL ROLLBACK TO S1
transactions SAVEPOINT

DBMS Interfaces

A DBMS Interface is the way users interact with the database.

Different users (like programmers, administrators, casual users, etc.) need
different types of interfaces.

1. Menu-based Interfaces

The user interacts with the database by choosing options from menus (lists of
choices).

 Simple and user-friendly.

 Common in applications for non-technical users.

Example:
  An ATM machine → You choose options like Withdraw, Balance Inquiry,
Deposit.
 Hotel reservation system → Choose Check Availability, Book Room,
Cancel Booking.

2. Forms-based Interfaces

Users enter data into forms (screens with text boxes, dropdowns, etc.).

 Best for inserting new records or updating existing data.

Example:

 Student admission form on a university website (fields like Name, Age,

Course).
 Online shopping checkout form (Address, Phone, Payment Details).

3. GUI-based Interfaces (Graphical User Interface)

Uses buttons, icons, windows, drag-and-drop to interact with the database.

 Easy to use, no need to write queries.

Example:

 MS Access → Create and view tables with just clicks.

 phpMyAdmin → Manage MySQL database with buttons and menus.

4. Natural Language Interfaces

User can type queries in natural language (English, Hindi, etc.), and the DBMS
interprets it.

 Uses NLP (Natural Language Processing).

Example:
  User types: “Show me all students in BCA class” → DBMS translates to
SQL and gives result.
 Voice assistants like Siri, Alexa when they access databases.

5. Speech-based Interfaces

User speaks instead of typing → DBMS processes speech.

 Helpful for physically challenged users.

Example:

 Voice search in Google → “Show me restaurants near me.”

 Voice-enabled banking apps.

6. Parametric Interfaces

Designed for specific tasks where users just enter parameters (input values).

 Widely used by bank clerks, ticket reservation agents, etc.

Example:

 Bank clerk enters Account No. to check balance.

 Railway ticket booking agent enters Source, Destination, Date.

7. SQL / Query-based Interfaces

Users directly write queries in SQL (Structured Query Language).

 For technical users/programmers.

Example:

SELECT Name, Course FROM Student WHERE Course = 'BCA';

8. Interfaces for Database Administrators (DBA Interfaces)

Special administrative tools for DBAs to manage the database.

 Used for backup, recovery, user management, security, tuning.

Example:

 Oracle Enterprise Manager (DBA tool).

 SQL Server Management Studio (SSMS).

Summary Table
Type of Interface Who uses it? Example

Menu-based Casual users ATM, hotel booking menu

Forms-based Clerks, data entry Admission form, checkout form

GUI-based General users MS Access, phpMyAdmin

Natural Language Non-technical users “Show all employees in HR”

Speech-based Physically challenged Voice search, banking apps

Parametric Operators, clerks Bank transaction form

SQL-based Developers, analysts SELECT * FROM Student;

DBA Interface Database admins Oracle tools, SSMS

Database System Environment

A Database System Environment is everything that helps in collecting, storing,

managing, and using data.
It is made of five main components:
1. Hardware

 All the physical devices of the system.

 Includes: Computers, storage devices, network components, printers.
 Example: The server where the database is stored.

2. Software

Programs that make the database system work.

 Operating System (OS): Runs the computer and manages hardware. (Ex:
Windows, Linux).
 DBMS Software: Manages the database. (Ex: Oracle, MySQL, SQL
Server).
 Application Programs & Utilities: Used to access data, create reports, or
manage transactions. (Ex: Payroll system, Billing system).

3. People

All the users who interact with the database:

  System Administrators: Manage overall system operations.
 Database Administrators (DBA): Keep the database running properly.
 Database Designers: Design the structure of the database.
 System Analysts & Programmers: Build application programs and
reports.
 End Users: Use applications for daily tasks (Ex: cashier, student,
customer).

4. Procedures

 The rules and instructions for using and managing the database.
 Ensure consistency and standard practices.
 Example: Rules for entering customer data or making backups.

5. Data

 The facts stored in the database.

 It is the raw material used to generate useful information.
 Example: Student records, employee salaries, sales transactions.

1. Centralized DBMS Architecture

 In this setup, all data is stored in one central location (server).

 Users access the database through terminals or PCs connected to the
central computer.

Advantages:

 Easy to manage and secure (all data in one place).

 Simple backup and recovery.

Disadvantages:

 If the central server fails, the whole system stops.

 Performance issues if many users access at the same time.
2. Client-Server Architecture

Here the database system is divided between client (user machine) and server
(database machine).

(a) 2-Tier Architecture

 Client: Runs the application and sends requests (SQL queries).

 Server: Stores the database and processes queries.

Example: A desktop application (like MS Access + SQL Server).

Pros: Faster than centralized, easy communication.

Cons: Not very scalable, business logic is mixed into client applications.

(b) 3-Tier Architecture

 Client (Presentation Layer): User interface (web browser, mobile app).

 Application Server (Business Logic Layer): Processes business rules
(middleware).
 Database Server (Data Layer): Stores and manages the database.

Example: Online Banking system, E-commerce sites.

Pros:

 Scalable (supports many users).

 Better security (clients cannot directly access DB).
 Business logic is separate → easier maintenance.

Cons:

 More complex and costly to set up.

3. Distributed DBMS (DDBMS)

A Distributed DBMS is when the database is spread across multiple locations

(sites/servers) but looks like one single database to the user.
Example: A company with branches in Bangalore, Mumbai, and Delhi having
separate local databases but connected.

Advantages of DDBMS

1. Reliability: If one site fails, others still work.

2. Faster Access: Users can access local databases quickly.
3. Scalability: Easy to add new sites.
4. Transparency: Appears like one single database to the user.

Disadvantages of DDBMS

1. Complex Management: Harder to design and maintain.

2. High Cost: Needs expensive hardware and communication setup.
3. Security Issues: Multiple sites → more vulnerable to attacks.
4. Data Consistency Problems: Updating the same data in multiple
locations can cause conflicts.