Database Management System (AD-402) Unit 5 Notes

How unit 5 is examined

This unit covers Oracle architecture, SQL and PL/SQL, and NoSQL; SQL joins and NoSQL carry the marks (medium), then architecture, data dictionary, special operators, ANSI SQL and stored procedures.

Architecture

<span style="display:inline-block;padding:.16em .6em;border:1.5px solid currentColor;border-radius:999px;font-size:.68em;font-weight:700;letter-spacing:.06em;text-transform:uppercase;opacity:.75">Low weight</span>

Definition. An RDBMS architecture is a layered design in which clients send SQL to a server that parses, optimises and executes it, manages transactions and stores data on disk.

Diagram. <figure class="ds-fig" style="margin:1.4rem 0;overflow-x:auto"><svg xmlns="http://www.w3.org/2000/svg" id="dsfig-u5-01" viewBox="0 0 596 80" width="596" height="80" role="img" aria-label="Usr = user/client, QP = query processor (parser, optimiser), TM = transaction manager, SM = storage manager (buffer, file manager), DB = database storage (data files, data dictionary)"><style>#dsfig-u5-01 .e{stroke:#454C5A;stroke-width:1.4;fill:none}#dsfig-u5-01 .e.hi{stroke:#2340B8;stroke-width:2.6}#dsfig-u5-01 .n{fill:#FFFFFF;stroke:#16181D;stroke-width:1.4}#dsfig-u5-01 .n.hi{fill:#E3E9FC;stroke:#2340B8;stroke-width:2.2}#dsfig-u5-01 .n.rb-b{fill:#16181D;stroke:#16181D}#dsfig-u5-01 .n.rb-r{fill:#BD3227;stroke:#BD3227}#dsfig-u5-01 text{font-family:"JetBrains Mono",ui-monospace,Menlo,Consolas,monospace;font-size:13px}#dsfig-u5-01 .t{fill:#16181D;font-weight:500}#dsfig-u5-01 .t.inv{fill:#FFFFFF;font-weight:700}#dsfig-u5-01 .kd{stroke:#16181D;stroke-width:1.2}#dsfig-u5-01 .dot{fill:#16181D}#dsfig-u5-01 .ann{fill:#2340B8;font-size:11px;font-weight:700}#dsfig-u5-01 .lbl{fill:#6F7787;font-family:system-ui,-apple-system,sans-serif;font-size:12px;font-weight:700}#dsfig-u5-01 .ptr{fill:#2340B8;font-size:12px;font-weight:700}#dsfig-u5-01 .ah{fill:#454C5A}#dsfig-u5-01 .ah.hi{fill:#2340B8}#dsfig-u5-01 .wl rect{fill:#FFFFFF;stroke:#DCE0E7}#dsfig-u5-01 .wl .t{font-size:12px;font-weight:700}#dsfig-u5-01 .wl.hi rect{fill:#2340B8;stroke:#2340B8}#dsfig-u5-01 .wl.hi .t{fill:#FFFFFF}html.dark #dsfig-u5-01 .e{stroke:#B1B7C3}html.dark #dsfig-u5-01 .e.hi{stroke:#8FA3FF}html.dark #dsfig-u5-01 .n{fill:#161920;stroke:#E6E8ED}html.dark #dsfig-u5-01 .n.hi{fill:#1E2748;stroke:#8FA3FF}html.dark #dsfig-u5-01 .n.rb-b{fill:#E6E8ED;stroke:#E6E8ED}html.dark #dsfig-u5-01 .n.rb-r{fill:#FF7E71;stroke:#FF7E71}html.dark #dsfig-u5-01 .t{fill:#E6E8ED}html.dark #dsfig-u5-01 .t.inv{fill:#0F1115}html.dark #dsfig-u5-01 .kd{stroke:#E6E8ED}html.dark #dsfig-u5-01 .dot{fill:#E6E8ED}html.dark #dsfig-u5-01 .ann{fill:#8FA3FF}html.dark #dsfig-u5-01 .lbl{fill:#858D9C}html.dark #dsfig-u5-01 .ptr{fill:#8FA3FF}html.dark #dsfig-u5-01 .ah{fill:#B1B7C3}html.dark #dsfig-u5-01 .ah.hi{fill:#8FA3FF}html.dark #dsfig-u5-01 .wl rect{fill:#161920;stroke:#2A2E37}html.dark #dsfig-u5-01 .wl.hi rect{fill:#8FA3FF;stroke:#8FA3FF}html.dark #dsfig-u5-01 .wl.hi .t{fill:#0F1115}</style><defs><marker id="ah5" viewBox="0 0 10 10" refX="9" refY="5" markerWidth="7" markerHeight="7" orient="auto-start-reverse"><path class="ah" d="M0,1 L9,5 L0,9 z"/></marker><marker id="ahh5" viewBox="0 0 10 10" refX="9" refY="5" markerWidth="7" markerHeight="7" orient="auto-start-reverse"><path class="ah hi" d="M0,1 L9,5 L0,9 z"/></marker></defs><path class="e" d="M59,40 L148,40" marker-end="url(#ah5)"/><path class="e" d="M188,40 L277,40" marker-end="url(#ah5)"/><path class="e" d="M317,40 L406,40" marker-end="url(#ah5)"/><path class="e" d="M446,40 L535,40" marker-end="url(#ah5)"/><circle class="n" cx="40" cy="40" r="18"/><text class="t" x="40" y="40" dy=".35em" text-anchor="middle">Usr</text><circle class="n" cx="169" cy="40" r="18"/><text class="t" x="169" y="40" dy=".35em" text-anchor="middle">QP</text><circle class="n" cx="298" cy="40" r="18"/><text class="t" x="298" y="40" dy=".35em" text-anchor="middle">TM</text><circle class="n" cx="427" cy="40" r="18"/><text class="t" x="427" y="40" dy=".35em" text-anchor="middle">SM</text><circle class="n" cx="556" cy="40" r="18"/><text class="t" x="556" y="40" dy=".35em" text-anchor="middle">DB</text></svg><figcaption style="font-size:.82em;opacity:.72;margin-top:.45rem">Usr = user/client, QP = query processor (parser, optimiser), TM = transaction manager, SM = storage manager (buffer, file manager), DB = database storage (data files, data dictionary)</figcaption></figure>

Key points.

The client layer (user, application, SQL tool) submits SQL queries to the server.
The query processor parses the query, checks it against the data dictionary, optimises it and produces an execution plan.
The transaction manager provides concurrency control, locking and recovery so that transactions keep the ACID properties.
The storage manager, through the buffer manager and file manager, reads and writes disk blocks.
The database storage layer holds the data files, indexes, logs and the data dictionary.

<mark>An RDBMS is layered: client, query processor, transaction manager, storage manager and database storage.</mark>

Answer frame. Open with the layered definition; draw the block diagram with arrows top to bottom; develop points 1-5 in order; close with one line on how a query flows down and the result flows back.

Asked: [7 marks] (Jun 2025) Explain RDBMS architecture with all components.

Physical files

Definition. Physical files are the operating-system files that make up an Oracle database.

Key points.

Data files store the actual tables and indexes.
Redo log files record every change so that committed work can be recovered after a crash.
Control files hold the database name, the locations of all files and the current log sequence number; without them the database cannot mount.

Memory structures

Definition. Oracle memory has the SGA (System Global Area), shared by all processes, and the PGA (Program Global Area), private to each server process.

Key points.

The SGA holds the database buffer cache (data blocks), the redo log buffer and the shared pool (parsed SQL and dictionary cache).
The PGA holds a session's sort area, cursor state and private variables.
Together the SGA and background processes form an Oracle instance.

Background processes

Definition. Background processes are Oracle server processes started with the instance that perform housekeeping for all users.

Key points.

DBWn writes dirty buffers from the buffer cache to the data files.
LGWR writes the redo log buffer to the redo log files on commit.
CKPT signals a checkpoint and updates the file headers.
SMON performs instance recovery at startup; PMON cleans up failed user processes and releases their locks.

Table spaces, segments, extents and blocks

Definition. They are Oracle's logical storage units, from largest to smallest: tablespace, segment, extent, data block.

Key points.

A tablespace is a logical container made of one or more data files.
A segment is the space used by one object, such as a table or an index.
An extent is a set of contiguous blocks allocated to a segment.
A data block is the smallest unit of I/O, a multiple of the OS block size.

Dedicated server, multi threaded server

Definition. They are the two ways a user process is connected to Oracle: a dedicated server gives each user its own server process, and a multi threaded (shared) server lets many users share a pool of server processes.

Key points.

A dedicated server is simple and fast for each user, but it uses memory for every connected session.
A shared server uses a dispatcher, request and response queues and a pool of shared server processes.
Shared server suits many light, short sessions; dedicated server suits batch and long jobs.

Distributed database, database links, and snapshot

Definition. A distributed database stores data at several sites that users see as one database; a database link is a named connection to a remote database, and a snapshot (materialized view) is a local copy of remote data refreshed periodically.

Key points.

A link is created with CREATE DATABASE LINK name CONNECT TO user IDENTIFIED BY pwd USING 'service'.
Remote tables are queried as SELECT * FROM emp@name.
A snapshot stores the query result locally, so it cuts network traffic and gives fast reads.
It is refreshed on demand or at a set interval, so its data can be slightly stale.

Data dictionary, dynamic performance view

Definition. The data dictionary (system catalog) is a set of read-only tables and views that store metadata, that is, data about the database: schemas, tables, columns, constraints, users and privileges.

Key points.

It is of two types: an active (integrated) dictionary is maintained automatically by the DBMS, while a passive (standalone) dictionary is a separate tool the DBA updates by hand.
Oracle views come in the USER_, ALL_ and DBA_ families, such as USER_TABLES.
The DBA uses it to manage users and storage; the query optimiser uses it to find indexes and statistics.
Dynamic performance views (V$SESSION, V$SGA) are virtual views over memory that show live activity.

<mark>A data dictionary stores metadata; it is active if the DBMS maintains it and passive if the DBA maintains it.</mark>

Answer frame. Open with the definition; list contents; explain the two types with one line of contrast; close with DBA and optimiser uses.

Asked: [7 marks] (Jun 2026) What is the data dictionary in DBMS? Explain its type.

Security, roles, privileges, profiles

Definition. Oracle security controls who may connect and what each user may do, using privileges, roles and profiles.

Key points.

A privilege is the right to run an action; GRANT SELECT ON emp TO ravi gives it and REVOKE takes it back.
A role is a named bundle of privileges granted to many users at once.
A profile limits resources and passwords, such as sessions per user and idle time.
In the invoker rights model (AUTHID CURRENT_USER) a procedure runs with the caller's privileges; the default definer rights use the owner's.

SQL queries, data extraction from single, multiple tables, equi-join, non equi-join, self-join, outer join

Definition. Data extraction uses SELECT ... FROM ... WHERE, and a join combines rows of two or more tables on a condition.

Key points.

Single table: SELECT cols FROM t WHERE cond, with ORDER BY for sorting, and GROUP BY and HAVING with COUNT/SUM/AVG for aggregation.
An equi-join matches with =, a non equi-join uses <, > or BETWEEN, and a self-join joins a table to itself with two aliases.
An inner join returns only matching rows; a left join also keeps unmatched left rows with NULLs; right and full outer joins work the same way.
Subqueries and set operations (UNION, INTERSECT, MINUS) also extract data from multiple tables.

Example. emp(1 Asha d10, 2 Ravi d20, 3 Neha NULL); dept(10 Sales, 20 HR, 30 IT).

SELECT e.ename, d.dname FROM emp e INNER JOIN dept d ON e.did = d.did;
-> Asha Sales | Ravi HR
SELECT e.ename, d.dname FROM emp e LEFT JOIN dept d ON e.did = d.did;
-> Asha Sales | Ravi HR | Neha NULL

Inner join drops Neha because she has no department; left join keeps her with NULL.

<mark>An inner join returns only matching rows, whereas a left join returns all left rows and NULL for missing matches.</mark>

Answer frame. Open with the SELECT-FROM-WHERE syntax; show the two sample tables; give the join queries with their results; close with the difference in output.

Asked: [7 marks] (Jun 2023) Explain in brief the procedure for data extraction from single and multiple tables. Asked: [7 marks] (Jun 2026) Write SQL queries using: INNER JOIN, LEFT JOIN

Special operators: LIKE, ANY, ALL, EXISTS, IN

Definition. Special operators are SQL predicates used in WHERE for pattern matching and for comparison with lists and subqueries.

Key points.

LIKE matches patterns, where % is any string and _ one character: WHERE name LIKE 'A%'.
IN is true if the value is in a list or subquery: WHERE did IN (10,20).
ANY is true if the comparison holds for at least one subquery value: sal > ANY (SELECT sal FROM emp WHERE did=10).
ALL needs the comparison to hold for every value: sal > ALL (...).
EXISTS is true if the subquery returns any row and stops at the first match, so it is often faster than IN on big tables.

Asked: [7 marks] (Jun 2024) Discuss the usage of special operators such as LIKE, ANY, ALL, EXISTS, and IN in SQL queries.

Hierarchical, inline and flashback queries

Definition. They are three Oracle query forms: hierarchical for tree data, inline for a subquery in FROM, and flashback for past data.

Key points.

A hierarchical query uses START WITH ... CONNECT BY PRIOR empid = mgr to walk a manager tree.
An inline view is a subquery in the FROM clause that acts as a temporary table.
A flashback query reads old data: SELECT * FROM emp AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '10' MINUTE).

Introduction of ANSI SQL, anonymous block, nested anonymous block, branching and looping constructs

Definition. ANSI SQL is the standard SQL language defined by ANSI and ISO so that one query works on every compliant DBMS.

Key points.

Portability: standard SQL runs on different vendors with few changes.
Standard DDL (CREATE, ALTER, DROP) and DML (SELECT, INSERT, UPDATE, DELETE) give a common syntax.
Data integrity is enforced through constraints such as primary key, foreign key and check.
It is declarative: the user states what is wanted and the DBMS decides how.
An anonymous PL/SQL block is DECLARE ... BEGIN ... EXCEPTION ... END; with no name; blocks can be nested, and it supports IF, CASE, LOOP, WHILE and FOR.

Asked: [7 marks] (Jun 2025) What are the key characteristics of ANSI SQL? Explain in detail.

Cursor management

Definition. A cursor is a pointer to the private work area holding a query's result rows.

Key points.

An implicit cursor is created by Oracle for each DML statement, with attributes such as SQL%ROWCOUNT.
An explicit cursor is declared, then OPEN, FETCH and CLOSE are run.
A parameterized cursor takes arguments: CURSOR c(d NUMBER) IS SELECT * FROM emp WHERE did = d;.
Nested cursors place one cursor loop inside another, for example departments and their employees.

Oracle exception handling

Definition. Exception handling traps run-time errors in the EXCEPTION section of a PL/SQL block so that the program does not stop abruptly.

Key points.

Predefined exceptions include NO_DATA_FOUND, TOO_MANY_ROWS and ZERO_DIVIDE.
A user-defined exception is declared, raised with RAISE and handled in WHEN ... THEN.
WHEN OTHERS THEN catches every other error.

Stored procedures

Definition. A stored procedure is a named PL/SQL block stored in the database that performs an action and is run with EXECUTE or called from another block.

Key points.

Parameters have modes: IN passes a value in (default), OUT returns a value, and IN OUT does both.
It is created with CREATE OR REPLACE PROCEDURE.
It allows control flow (IF, loops) and error handling through an EXCEPTION section.
Advantages are reuse, less network traffic, better security through GRANT EXECUTE and easy maintenance.

CREATE OR REPLACE PROCEDURE raise_sal(p_id IN NUMBER, p_pct IN NUMBER) AS
BEGIN
  UPDATE emp SET sal = sal + sal * p_pct / 100 WHERE eid = p_id;
END;
EXECUTE raise_sal(1, 10);

Asked: [7 marks] (Jun 2023) Discuss in detail stored procedures.

User defined functions

Definition. A user-defined function is a stored PL/SQL subprogram that must return a single value with RETURN.

Key points.

It can be called inside SQL, unlike a procedure.
When called from SQL it should only take IN parameters and may not do DML or COMMIT.
It returns exactly one value, so it cannot replace a procedure that changes data.

Triggers, mutating errors, INSTEAD OF triggers

Definition. A trigger is a stored block that fires automatically on an INSERT, UPDATE or DELETE event.

Key points.

It can be BEFORE or AFTER, and row-level (FOR EACH ROW) or statement-level.
:OLD and :NEW refer to the old and new column values.
A mutating table error (ORA-04091) occurs when a row-level trigger reads or changes the same table that is being modified.
An INSTEAD OF trigger runs in place of DML on a view, making complex views updatable.

Introduction to NoSQL Database

Definition. NoSQL ("not only SQL") databases are non-relational systems that store data without a fixed schema and scale out across many servers.

Key points.

Types: document (MongoDB), key-value (Redis), column-family (Cassandra) and graph (Neo4j).
They are schema-less, so records can have different fields.
They scale horizontally by adding commodity servers, while an RDBMS scales vertically.
They follow BASE (Basically Available, Soft state, Eventually consistent) instead of ACID.
Advantages are high speed, flexible schema and big-data handling; limitations are weak joins, weaker consistency and less standard tooling.
Applications include social media, real-time analytics, IoT and caching.

Feature	RDBMS (MySQL)	NoSQL (MongoDB)
Structure	Tables with fixed schema	Documents, key-values, graphs; flexible
Scalability	Vertical	Horizontal
Consistency	ACID, strong	BASE, eventual
Query language	SQL	Varies by product
Joins	Strong support	Limited
Best for	Structured, transactional data	Big, changing, distributed data

<mark>NoSQL databases are schema-less, horizontally scalable and follow BASE, while an RDBMS has a fixed schema, scales vertically and follows ACID.</mark>

Answer frame. Open with the definition and the four types; develop points 2-4; give the comparison table; then advantages, limitations and applications; close with when to choose each.

Asked: [7 marks] (Jun 2024, Jun 2026) Introduce the concept of NoSQL databases and discuss their characteristics and advantages compared to traditional RDBMS. Also: compare features, advantages, limitations and applications of NoSQL with RDBMS.

Last-minute revision

RDBMS layers: client, query processor, transaction manager, storage manager, database storage.
Physical files: data, redo log, control.
SGA is shared; PGA is private; SGA plus background processes is the instance.
DBWn writes data, LGWR writes redo, CKPT checkpoints, SMON recovers, PMON cleans up.
Storage: tablespace > segment > extent > block.
Data dictionary is metadata; active means the DBMS maintains it, passive means the DBA does.
Inner join keeps only matches; left join keeps all left rows.
LIKE uses %, _; ANY means at least one, ALL means every one; EXISTS stops at the first row.
Parameter modes: IN, OUT, IN OUT.
A function returns one value and can be used in SQL.
Mutating table error is ORA-04091.
NoSQL: BASE, schema-less, horizontal; RDBMS: ACID, fixed schema, vertical.

Memory hooks

Architecture: "Clients Query Transactions Stored" for client, query, transaction, storage.
Background processes: D writes Data, L writes Log, C Checks, S Saves (recovers), P Polices.
Storage size: "Tablespace Sits Everywhere, Big" for tablespace, segment, extent, block.
BASE versus ACID: base of a pile is loose (eventual), acid is strict.
Join: inner is the overlap of two circles; left is the whole left circle.

Coverage checklist

Architecture: Q1 (RDBMS architecture).
physical files: revision only.
memory structures: revision only.
background process: revision only.
Concept of table spaces, segments, extents and block: revision only.
Dedicated server, multi threaded server: revision only.
Distributed database, database links, and snapshot: revision only.
Data dictionary, dynamic performance view: Q2.
Security, role management, privilege management, profiles, invoker defined security model: revision only.
SQL queries, Data extraction from single, multiple tables equi- join, non equi-join, self -join, outer join: Q5, Q6.
Usage of like, any, all, exists, in Special operators: Q8.
Hierarchical quires, inline queries, flashback queries: revision only.
Introduction of ANSI SQL, anonymous block, nested anonymous block, branching and looping constructs in ANSI SQL: Q3.
Cursor management: nested and parameterized cursors: revision only.
Oracle exception handling mechanism: revision only.
Stored procedures, in, out, in out type parameters, usage of parameters in procedures: Q7.
User defined functions their limitations: revision only.
Triggers, mutating errors, instead of triggers: revision only.
Introduction to NoSql Database: Q4.

Account

Database Management System (AD-402) - Unit 5 Short Notes

How unit 5 is examined

Architecture

Physical files

Memory structures

Background processes

Table spaces, segments, extents and blocks

Dedicated server, multi threaded server

Distributed database, database links, and snapshot

Data dictionary, dynamic performance view

Security, roles, privileges, profiles

SQL queries, data extraction from single, multiple tables, equi-join, non equi-join, self-join, outer join

Special operators: LIKE, ANY, ALL, EXISTS, IN

Hierarchical, inline and flashback queries

Introduction of ANSI SQL, anonymous block, nested anonymous block, branching and looping constructs

Cursor management

Oracle exception handling

Stored procedures

User defined functions

Triggers, mutating errors, INSTEAD OF triggers

Introduction to NoSQL Database

Last-minute revision

Memory hooks

Coverage checklist