 Database System Architecture is the overall design of how different components of

a DBMS interact to store, process, and retrieve data efficiently.

 A relational DBMS architecture is divided into layers:
1. Users & Tools (Top Layer)
2. Query Processor (Middle Layer 1)
3. Storage Manager (Middle Layer 2)
4. Disk Storage (Bottom Layer)
1) Users & Tools Layer (Top Layer)
o The topmost part of DBMS where humans or applications interact with the
database.
o Allows different types of users to send commands or queries to the
database using suitable tools or interfaces.
Components:
 Naive Users – Use predefined forms or screens without knowing SQL. Example:
Bank clerk entering data into a deposit form.
  Application Programmers – Write code (Java, Python, etc.) that sends
database requests.
 Sophisticated Users – Directly write SQL queries using query tools like SQL
console.
 Database Administrator (DBA) – Manages database setup, security, backups,
performance.
 Tools/Interfaces:
o Application Interfaces – Screens or APIs through which apps talk to
DBMS.
o Query Tools – SQL editors, report writers.
o Administration Tools – User/role management, system monitoring tools.

2) Query Processor Layer

o The part of DBMS that understands queries (SQL) and decides how to run
them.
o Parses, optimizes, and converts queries into step-by-step execution plans.
o Ensures queries run efficiently.
Components:
 DDL Interpreter
o DDL = Data Definition Language (CREATE, ALTER, DROP).
o Reads DDL commands and updates the Data Dictionary (metadata
storage).
o Example: When you create a table, it saves the table’s structure in the
catalog.
 DML Compiler & Organizer
o DML = Data Manipulation Language (SELECT, INSERT, UPDATE, DELETE).
o Checks query syntax, chooses the fastest way to execute (optimization),
and generates an execution plan.
o Example: Decides whether to use an index or scan the whole table.
 Query Evaluation Engine
o Runs the instructions in the execution plan step by step.
o Interacts with the Storage Manager to fetch the required data.
3) Storage Manager Layer
o Storage manager is the component of database system that provides
interface between the low level data stored in the database and the
application programs and queries submitted to the system.
 o The storage manager is responsible for storing, retrieving, and updating
data in the database.
o Manages storage, retrieval, updates, and ensures security, integrity, and
transaction safety.
Components:
 Authorization & Integrity Manager
o Controls who can access what (user permissions).
o Enforces rules like PRIMARY KEY, FOREIGN KEY, NOT NULL, etc.
 Transaction Manager
o Makes sure multiple users can work at the same time without issues.
o Maintains ACID properties:
 Atomicity – all steps of a transaction happen or none happen.
 Consistency – rules of the database are always followed.
 Isolation – multiple transactions don’t interfere.
 Durability – committed data is never lost, even after crashes.
 File Manager
o Organizes how data and indexes are stored on disk.
o Allocates space and manages file structures.
 Buffer Manager
o Moves data between disk and RAM.
o Keeps frequently used data in memory to speed up queries.
4) Disk Storage Layer (Bottom Layer)
o The physical place where all data and related information is stored
permanently.
o Stores not only data but also indexes, metadata, and statistics for
optimization.
Components:
 Data Files – Store the actual rows of tables.
 Indexes – Speed up searches (like an index in a book).
 Data Dictionary (Catalog) – Metadata about database structure, constraints,
users.
 Statistical Data – Information like table sizes, row counts, index selectivity,
used by optimizer.
— A view in SQL is a saved SQL query that acts as a virtual table.
— Unlike regular tables, views do not store data themselves. Instead, they
dynamically generate data by executing the SQL query defined in the view each
time it is accessed.
— It can fetch data from one or more tables and present it in a customized format,
allowing developers to:
 Simplify Complex Queries: Encapsulate complex joins and conditions into a
single object.
 Enhance Security: Restrict access to specific columns or rows.
 Present Data Flexibly: Provide tailored data views for different users.

Why use Views?

 Hide complexity – show only needed columns/rows, hide joins and long conditions.

 Security – expose limited data; hide sensitive columns.

 Logical independence – if base tables change, keep same view name/shape for apps.

 Reusable business rules – put filters and joins in one place.

 Aggregation/reporting – present summaries (read-only).

Creating a View:
A view is created using the CREATE VIEW statement. The basic syntax is:
Code
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
For example, to create a view named ActiveCustomers that shows only customers
with an 'Active' status from a Customers table:
Code
CREATE VIEW ActiveCustomers AS
SELECT CustomerID, CustomerName, Email
FROM Customers
WHERE Status = 'Active';

Updating a View:
 Yes, a view can be updated, but with certain restrictions.
 If a view is updatable, you can use INSERT, UPDATE, and DELETE statements on
it, and these operations will be reflected in the underlying base table(s).
Conditions for an Updatable View:
A view is generally updatable if:
 It is based on a single table.
 The view must include the primary key of the base table it's based on.
 The view must include all columns from the base table that are defined as NOT
NULL.
 Its defining query does not contain:
o GROUP BY clause or aggregate functions (e.g., SUM, COUNT).
o DISTINCT keyword.
o UNION, UNION ALL, INTERSECT, or EXCEPT clauses.
o Subqueries in the SELECT list.
o JOIN operations that make the view non-key-preserved (i.e., rows in the
view don't uniquely map to rows in the base table).
How to Update an Updatable View:
If a view meets the updatability criteria, you can update it just like a regular table:
Code
UPDATE ActiveCustomers
SET CustomerName = 'New Name'
WHERE CustomerID = 123;
Why a View Might Not Be Updatable:
If a view's definition violates the conditions mentioned above, it cannot be directly
updated.
Because the database system cannot unambiguously determine how to translate the
update operation on the view to the underlying base table(s).
For instance, if a view aggregates data, updating an aggregated value in the view
doesn't clearly map to updating specific rows in the base table.
Similarly, if a view joins multiple tables, an update might affect columns from different
tables, making the operation ambiguous without specific rules.
 Database Management System
Parameter File Processing System
(DBMS)
Data redundancy is less because data is
1. Data Data redundancy is more because each
stored centrally and shared among
Redundancy application stores its own data separately.
applications.
Data is integrated in a central database,
Data is scattered in separate files, making
2. Data Isolation making it easy to access across
access and integration difficult.
applications.
Data inconsistency is more because the same
3. Data Data inconsistency is less because the
data may exist in multiple files and not be
Inconsistency same data is updated centrally.
updated everywhere.
Data integrity is high because of Data integrity is low because there are no
4. Data Integrity constraints (primary key, foreign key, enforced rules; consistency depends on
etc.) and rules in DBMS. application logic.
Security is high — access control and Security is low — basic file permissions
5. Security
authentication are managed at DB level. only; less control over data access.
Data is stored in a structured manner Data is stored in an unstructured manner,
6. Data Structure
with relationships between tables. often in isolated files.
User is unknown to the physical address
7. Physical Data User must know the physical address or file
of the data; DBMS handles storage
Independence path to access data.
details.
Can retrieve data in any desired format Cannot retrieve data in any desired format
8. Data Retrieval
using queries. without complex programming.
9. Concurrent Multiple users can access data No or very limited ability for concurrent
Access concurrently with transaction control. access; may cause conflicts.
10. Backup & DBMS provides automatic backup and Backup and recovery must be done
Recovery recovery features. manually.
DBMS is costlier due to software, File system is cheaper but less efficient in
11. Cost
hardware, and maintenance needs. long term.
Easier to maintain and scale; changes to Difficult to maintain; structure changes
12. Maintenance
structure are easier. require rewriting applications.

Consider following schema

Hotels(hotel_no,hotel_name.city)
Rooms(Room_no,hotel_no,price,type)
[5]
Write a PL/SQL procedure to list the price & type of all rooms at the hotel ‘TAJ’