Database Management Systems (CS-502) Unit 5 Notes

How unit 5 is examined

Oracle architecture, security, SQL joins and special operators, and PL/SQL programming; cursors and exception handling carry the most marks, then the SQL query programs (Employee table, EXISTS, joins), architecture and privileges.

Architecture, physical files, memory structures, background process

Low weight

Definition. An Oracle server is an instance (memory structures plus background processes) that operates on a database (the physical files 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 474 252" width="474" height="252" role="img" aria-label="Oracle architecture. User process, Server Process (SP), memory (SGA, PGA), background processes (BG) writing Data Files (DF), Control File (CF) and Redo Log files (RL)"><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="ah12" 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="ahh12" 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="M66,40 L148,40" marker-end="url(#ah12)"/><path class="e" d="M188,40 L277,40" marker-end="url(#ah12)"/><path class="e" d="M169,59 L169,150"/><path class="e" d="M298,59 L298,150"/><path class="e" d="M311.4,155.6 L412.2,54.8" marker-end="url(#ah12)"/><path class="e" d="M316,163 L407.1,132.6" marker-end="url(#ah12)"/><path class="e" d="M316,175 L407.1,205.4" marker-end="url(#ah12)"/><rect class="n" x="15" y="25" width="50" height="30" rx="15"/><text class="t" x="40" y="40" dy=".35em" text-anchor="middle">User</text><circle class="n" cx="169" cy="40" r="18"/><text class="t" x="169" y="40" dy=".35em" text-anchor="middle">SP</text><circle class="n" cx="298" cy="40" r="18"/><text class="t" x="298" y="40" dy=".35em" text-anchor="middle">SGA</text><circle class="n" cx="169" cy="169" r="18"/><text class="t" x="169" y="169" dy=".35em" text-anchor="middle">PGA</text><circle class="n" cx="298" cy="169" r="18"/><text class="t" x="298" y="169" dy=".35em" text-anchor="middle">BG</text><circle class="n" cx="427" cy="40" r="18"/><text class="t" x="427" y="40" dy=".35em" text-anchor="middle">DF</text><circle class="n" cx="427" cy="126" r="18"/><text class="t" x="427" y="126" dy=".35em" text-anchor="middle">CF</text><circle class="n" cx="427" cy="212" r="18"/><text class="t" x="427" y="212" dy=".35em" text-anchor="middle">RL</text></svg><figcaption style="font-size:.82em;opacity:.72;margin-top:.45rem">Oracle architecture. User process, Server Process (SP), memory (SGA, PGA), background processes (BG) writing Data Files (DF), Control File (CF) and Redo Log files (RL)</figcaption></figure>

Key points.

The instance is the set of memory structures and background processes; the database is the set of physical files, and one instance opens one database.
The SGA (System Global Area) is shared memory holding the database buffer cache (recently used data blocks), the redo log buffer (changes not yet written to disk) and the shared pool (parsed SQL and the data dictionary cache).
The PGA (Program Global Area) is private memory of each server process, holding its sort area, session data and cursor state.
The physical files are data files (the actual data), redo log files (a record of every change, used for recovery) and control files (database name, file locations and log sequence).
DBWR writes modified buffers to data files, LGWR writes the redo log buffer to the redo log files, CKPT signals checkpoints, SMON does instance recovery, and PMON cleans up failed user processes.
MySQL follows the same idea in layers: a connection layer, a SQL layer (parser, optimizer, cache), and a pluggable storage-engine layer (InnoDB, MyISAM) over files on disk, with the InnoDB buffer pool as its main memory area.

Answer frame. Open with the instance/database definition; draw the diagram; develop points 2-5 in the order SGA, PGA, files, background processes; close with one line on MySQL's layered equivalent.

Asked: [7 marks] (Dec 2025) Explain Oracle/MySQL architecture and main memory structures.

Concept of table spaces, segments, extents and block

Definition. A tablespace is a logical storage unit made of one or more data files, and it is divided into segments, extents and data blocks.

Key points.

A data block is the smallest unit of I/O, typically 8 KB, made of one or more OS blocks.
An extent is a set of contiguous data blocks allocated together to one segment.
A segment is all the extents of one object, such as a table, index or undo, so hierarchy is tablespace > segment > extent > block.
Tablespaces such as SYSTEM, SYSAUX, USERS and TEMP let the DBA place data on different disks and control quotas.

Dedicated server, multi threaded server

Definition. In the dedicated server model each user process gets its own server process, whereas in the multi threaded (shared) server model many user processes share a pool of server processes through dispatchers.

Key points.

Dedicated server gives one-to-one mapping, so it is simple and fast for long or batch work but uses memory per connection.
In MTS a dispatcher places each request on a queue, and any free shared server picks it up and returns the reply through the dispatcher.
MTS supports many more users with less memory, and suits short transactions from many clients.
In MTS the session data lives in the SGA instead of the PGA.

Distributed database, database links, and snapshot

Definition. A distributed database is a collection of databases on different sites that appear to the user as one logical database; a database link is a named connection to a remote database, and a snapshot is a local read-only copy of remote data refreshed periodically.

Key points.

A link is created with CREATE DATABASE LINK hq CONNECT TO scott IDENTIFIED BY tiger USING 'hqdb';.
Remote tables are then queried as SELECT * FROM emp@hq;, which gives location transparency when wrapped in a synonym.
A snapshot (now a materialized view) stores the result of a query on the remote table and is refreshed at set intervals, for example CREATE SNAPSHOT s AS SELECT * FROM emp@hq;.
Snapshots cut network traffic and give fast local reads, at the cost of possibly stale data.

Data dictionary, dynamic performance view

Definition. The data dictionary is a set of read-only tables and views, owned by SYS, that store metadata about the database; dynamic performance views (V$ views) expose live instance statistics.

Key points.

Dictionary views come in three families: USER_ (objects you own), ALL_ (objects you can access) and DBA_ (all objects), for example USER_TABLES.
Oracle updates the dictionary automatically on every DDL statement; users can only query it.
V$ views such as V$SESSION, V$SGA and V$DATABASE read from memory and control files, so their content resets at instance restart.
The dictionary stores structure and privileges, while V$ views show current performance and activity.

Security, role management, privilege management, profiles, invoker defined security model

Definition. A privilege is the right to execute a particular SQL statement or access an object, and a role is a named group of privileges that is granted to users as a unit.

Key points.

System privileges permit an action such as CREATE TABLE or CREATE SESSION; object privileges permit an operation on one object such as SELECT, INSERT or EXECUTE on emp.
GRANT gives privileges and REVOKE removes them; WITH GRANT OPTION lets the receiver pass an object privilege on, and WITH ADMIN OPTION does so for system privileges and roles.
A role is created with CREATE ROLE, given privileges with GRANT ... TO role, and then assigned to users, so a role can also be granted to another role, which forms a role hierarchy.
Steps: create the role, grant privileges to the role, grant the role to users, and revoke or drop it when it is no longer needed.
A profile limits resources and password rules (SESSIONS_PER_USER, CPU_PER_SESSION, FAILED_LOGIN_ATTEMPTS, PASSWORD_LIFE_TIME).
Under the invoker-rights model (AUTHID CURRENT_USER) a stored procedure runs with the privileges of the caller; the default definer-rights model uses the owner's privileges.

Example.

CREATE ROLE clerk;
GRANT SELECT, INSERT ON emp TO clerk;
GRANT clerk TO ravi;
REVOKE INSERT ON emp FROM clerk;

Answer frame. Open by defining privilege and role; then system versus object privileges, GRANT and REVOKE, role creation steps in order, and the SQL example; close by stating that roles simplify privilege management.

Asked: [7 marks] (Nov 2019) Explain privilege and role management process.

SQL queries: single table, equi-join, non equi-join, self-join, outer join

Definition. A join combines rows of two or more tables on a related condition, and an outer join also keeps the rows that have no match.

Key points.

An equi-join uses the equality operator between columns of two tables.
A non equi-join uses any other operator, such as BETWEEN or <, for example matching salary to a grade range.
A self-join joins a table to itself using two aliases, for example employee with manager.
A LEFT, RIGHT or FULL outer join keeps unmatched rows of the left, right or both tables, with NULL in the missing columns.
EXISTS is true when the subquery returns at least one row, and it is used in a correlated nested query.

Example. Tables emp(eid, ename, did) and dept(did, dname).

-- (i) equi-join
SELECT e.ename, d.dname FROM emp e JOIN dept d ON e.did = d.did;
-- (ii) outer join: departments with no employee also appear
SELECT d.dname, e.ename FROM dept d LEFT OUTER JOIN emp e ON d.did = e.did;
-- (iii) nested query using EXISTS
SELECT d.dname FROM dept d
WHERE EXISTS (SELECT 1 FROM emp e WHERE e.did = d.did);
-- self-join: employee and manager
SELECT w.E_Name, m.E_Name FROM Employee w JOIN Employee m ON w.Manager_ID = m.E_ID;

Answer frame. Give the schema first, write the three queries in the order asked with one line of meaning each, and close by noting what each returns.

Asked: [7 marks] (Dec 2025) Write SQL queries to demonstrate: (i) equi-join, (ii) outer join, and (iii) a nested query using EXISTS.

Usage of like, any, all, exists, in special operators

Definition. Special operators compare a value with a pattern or with the set returned by a subquery: LIKE matches patterns, IN tests membership, ANY and ALL compare with some or every value, and EXISTS tests for a non-empty result.

Key points.

LIKE uses % for any string and _ for one character, so LIKE 'A%' finds names starting with A.
IN is true if the value equals any member of the list, so = ANY is the same as IN.
> ANY means greater than the minimum of the set, and > ALL means greater than the maximum.
EXISTS returns true when the subquery has at least one row, and NOT EXISTS when it has none.

Example. Employee(E_ID, E_Name, Salary, Manager_ID, Dept_ID) from Nov 2022; answers were checked against the given rows.

-- i) higher than P (4500): A, R
SELECT E_ID, E_Name FROM Employee WHERE Salary > (SELECT Salary FROM Employee WHERE E_Name='P');
-- ii) higher than N (4000): Dept 101, 101, 103
SELECT Dept_ID FROM Employee WHERE Salary > (SELECT Salary FROM Employee WHERE E_Name='N');
-- iii) higher than anyone in 102 (above min 2000): A, R, N, P, H
SELECT E_ID, E_Name FROM Employee WHERE Salary > ANY (SELECT Salary FROM Employee WHERE Dept_ID=102);
-- iv) higher than all in 103 (above max 4500): A, R
SELECT E_ID, E_Name FROM Employee WHERE Salary > ALL (SELECT Salary FROM Employee WHERE Dept_ID=103);
-- v) same as Dept 103 (2000, 4500): T, M, S, P
SELECT E_ID, E_Name FROM Employee WHERE Salary IN (SELECT Salary FROM Employee WHERE Dept_ID=103);
-- vi) second highest: R, 5000, 101
SELECT E_Name, Salary, Dept_ID FROM Employee
WHERE Salary = (SELECT MAX(Salary) FROM Employee WHERE Salary < (SELECT MAX(Salary) FROM Employee));
-- vii) average above 3000: 101 (5000), 103 (3250)
SELECT Dept_ID FROM Employee GROUP BY Dept_ID HAVING AVG(Salary) > 3000;

Answer frame. Open with one line on subqueries; write each query with a short comment naming the operator used (ANY, ALL, IN, MAX, HAVING); close by stating the result rows as above.

Pitfall: In Oracle, LIMIT does not exist; use the MAX subquery or FETCH FIRST, and remember > ANY is the minimum, not the maximum.

Asked: [7 marks] (Nov 2022) Employee relation: (i) salary higher than employee 'P'; (ii) D_ID of employees with salary higher than 'N'; (iii) higher than anyone in Dept 102; (iv) higher than all in Dept 103; (v) same salary as Dept 103; (vi) second highest salary (name, salary, Dept_ID); (vii) Dept_ID with average salary greater than 3000.

Hierarchical queries, inline queries, flashback queries

Definition. A hierarchical query walks tree-structured rows with START WITH ... CONNECT BY PRIOR, an inline query is a subquery in the FROM clause, and a flashback query shows data as it was at an earlier time.

Key points.

SELECT E_Name, LEVEL FROM Employee START WITH Manager_ID IS NULL CONNECT BY PRIOR E_ID = Manager_ID; lists the employee tree from the top, with LEVEL as the depth.
An inline view such as SELECT * FROM (SELECT Dept_ID, AVG(Salary) a FROM Employee GROUP BY Dept_ID) WHERE a > 3000; treats the subquery as a temporary table.
A flashback query is SELECT * FROM emp AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '10' MINUTE); and it reads old data from undo.
Flashback helps to recover from wrong updates or deletes without a full restore.

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

Definition. PL/SQL is Oracle's procedural extension of SQL, and an anonymous block is an unnamed, unstored block of DECLARE, BEGIN, EXCEPTION and END sections.

Key points.

Only BEGIN ... END is compulsory; DECLARE holds variables and cursors, and EXCEPTION holds handlers.
A nested anonymous block is a full block placed inside the executable or exception section of another block, and it has its own scope and its own handlers.
Branching uses IF ... THEN ... ELSIF ... ELSE ... END IF; and CASE (simple or searched).
Looping uses basic LOOP ... EXIT WHEN ... END LOOP;, WHILE cond LOOP, and FOR i IN 1..5 LOOP.
Statements end with a semicolon, and the block ends with / in SQL*Plus.

DECLARE n NUMBER := 0;
BEGIN
  FOR i IN 1..5 LOOP n := n + i; END LOOP;
  IF n > 10 THEN DBMS_OUTPUT.PUT_LINE('Sum ' || n); END IF;   -- Sum 15
END;
/

Asked: [7 marks] (Dec 2024) Branching and looping constructs in ANSI SQL (part of a short-note question, see the next topic).

Cursor management: nested and parameterized cursors, Oracle exception handling mechanism

Definition. A cursor is a pointer to the private SQL work area (context area) that holds the rows returned by a query so that PL/SQL can process them one row at a time; an exception is a runtime error that transfers control to the EXCEPTION section of the block.

Key points (cursors).

An implicit cursor is created automatically for each DML statement and SELECT INTO, and its attributes are SQL%FOUND, SQL%NOTFOUND and SQL%ROWCOUNT.
An explicit cursor is declared by the programmer for a query returning many rows, and it is needed because a plain SELECT INTO fails if more than one row is returned.
Explicit cursor steps are DECLARE, OPEN, FETCH (in a loop) and CLOSE, with attributes %FOUND, %NOTFOUND, %ISOPEN and %ROWCOUNT.
A parameterized cursor takes formal parameters that are supplied at OPEN time, so the same cursor is reused with different values.
A nested cursor is an inner cursor opened inside the loop of an outer cursor, and it is usually parameterized with the outer row's key, as in department-wise employee listing.

DECLARE
  CURSOR d IS SELECT Dept_ID FROM dept;                       -- outer
  CURSOR e(p NUMBER) IS SELECT E_Name FROM Employee WHERE Dept_ID = p;  -- inner, parameterized
BEGIN
  FOR dr IN d LOOP
    FOR er IN e(dr.Dept_ID) LOOP DBMS_OUTPUT.PUT_LINE(dr.Dept_ID || ' ' || er.E_Name); END LOOP;
  END LOOP;
END;
/

Comparison.

Basis	Implicit	Explicit	Parameterized	Nested
Created by	Oracle	Programmer	Programmer	Programmer
Rows	One	Many	Many, filtered by parameter	Many per outer row
Control	Automatic	OPEN/FETCH/CLOSE	Values passed at OPEN	Outer loop drives inner
Reuse	No	Limited	High	Uses parameter per outer row

Key points (exceptions). 6. Predefined exceptions are raised by Oracle and named, such as NO_DATA_FOUND, TOO_MANY_ROWS, ZERO_DIVIDE, DUP_VAL_ON_INDEX and INVALID_CURSOR. 7. User-defined exceptions are declared as name EXCEPTION;, raised with RAISE name;, and can be bound to an Oracle error number using PRAGMA EXCEPTION_INIT; RAISE_APPLICATION_ERROR(-20001, 'msg') raises an error with a custom message. 8. Handlers are written as WHEN name THEN ..., with WHEN OTHERS as the catch-all, and SQLCODE and SQLERRM report the error. 9. An unhandled exception propagates: it leaves the current block, is looked for in the enclosing block, and finally reaches the caller; an exception raised in the declaration section or inside a handler propagates outwards at once.

DECLARE
  low_sal EXCEPTION; s NUMBER;
BEGIN
  SELECT Salary INTO s FROM Employee WHERE E_ID = 9;
  IF s < 2500 THEN RAISE low_sal; END IF;
EXCEPTION
  WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('No such employee');   -- printed, E_ID 9 absent
  WHEN low_sal THEN DBMS_OUTPUT.PUT_LINE('Salary too low');
  WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/

Answer frame.

Cursor question: open by defining cursor management (declare, open, fetch, close a context area for row-by-row processing); state why it is needed; explain parameterized then nested cursors with the code; close with the comparison table.
Exception question: open by defining an exception; draw or write the block layout (DECLARE, BEGIN, EXCEPTION, END); develop predefined versus user-defined, handlers, propagation and the example; close by stating that handling keeps the program from aborting.
Short note with branching and looping: give the exception note above and the IF, CASE, LOOP, WHILE and FOR forms from the previous topic in a line each.

Pitfall: WHEN OTHERS placed before a specific handler is a compile error, so always put it last.

Asked: [7 marks] (Dec 2024) What is Cursor Management? Explain nested and parameterized cursors.

Asked: [7 marks] (Nov 2022) Discuss the various exception handling mechanisms in database platforms.

Asked: [14 marks] (Nov 2023) Write short notes on (any two): a) Oracle exception handling mechanism b) Functions of DBA c) SQL Aggregate Functions d) Distributed databases

Asked: [7 marks] (Dec 2024) Write short notes: i) Oracle Exception Handling Mechanism ii) Branching and looping Constructs in ANSI SQL.

Stored procedures, in, out, in out type parameters

Definition. A stored procedure is a named PL/SQL block stored in the database that performs an action and is called by name.

Key points.

It is created with CREATE OR REPLACE PROCEDURE name (params) IS ... BEGIN ... END; and run with EXEC name(args);.
An IN parameter passes a value in and is read-only; it is the default.
An OUT parameter returns a value to the caller, and an IN OUT parameter carries a value in and sends a changed value back.
Procedures may have no parameters, are compiled once, and can be reused and secured through GRANT EXECUTE.

CREATE OR REPLACE PROCEDURE raise_sal (id IN NUMBER, pct IN NUMBER, newsal OUT NUMBER) IS
BEGIN
  UPDATE Employee SET Salary = Salary * (1 + pct/100) WHERE E_ID = id RETURNING Salary INTO newsal;
END;

User defined functions their limitations

Definition. A user-defined function is a stored PL/SQL subprogram that must return exactly one value through a RETURN clause and can be used inside SQL expressions.

Key points.

Syntax is CREATE FUNCTION f(x NUMBER) RETURN NUMBER IS BEGIN RETURN x*2; END;, and it is called as SELECT f(Salary) FROM Employee;.
A function called from SQL can take only IN parameters and must return a SQL datatype.
A function called from SQL cannot perform DML or COMMIT or ROLLBACK, and cannot change the table it is queried from.
A procedure differs in that it may return many values, or none, through OUT parameters and is invoked as a statement.

Triggers, mutating errors, instead of triggers

Definition. A trigger is a stored PL/SQL block that fires automatically on a DML, DDL or system event on a table or view.

Key points.

It is defined with timing (BEFORE or AFTER), event (INSERT, UPDATE, DELETE) and level (statement or FOR EACH ROW), and row triggers use :OLD and :NEW.
A mutating table error (ORA-04091) occurs when a row-level trigger reads or modifies the table that is currently being changed by the triggering statement.
It is avoided by using a statement-level trigger, a compound trigger, or by keeping the values in a package variable.
An INSTEAD OF trigger is defined on a view and runs in place of the DML, so a complex (join) view can be made updatable.

Last-minute revision

An Oracle instance is SGA plus background processes; the database is the data, redo and control files.
SGA is shared (buffer cache, redo buffer, shared pool); PGA is private to each server process.
DBWR writes data files, LGWR writes redo logs, CKPT does checkpoints, SMON recovers, PMON cleans up.
Storage hierarchy is tablespace > segment > extent > data block.
USER_, ALL_ and DBA_ are dictionary views; V$ views are dynamic performance views.
System privileges allow actions and object privileges allow operations on an object; a role groups privileges.
> ANY means above the minimum, > ALL above the maximum, = ANY equals IN.
Nov 2022 Employee answers: (i) A, R; (iii) A, R, N, P, H; (iv) A, R; (v) T, M, S, P; (vi) R 5000 101; (vii) 101, 103.
Explicit cursor steps are DECLARE, OPEN, FETCH, CLOSE; a parameterized cursor takes values at OPEN.
Common exceptions are NO_DATA_FOUND, TOO_MANY_ROWS, ZERO_DIVIDE; put WHEN OTHERS last.
IN is read-only, OUT returns a value, IN OUT does both; a SQL function cannot do DML.

Memory hooks

SGA = Shared, PGA = Private.
DBWR writes Data, LGWR writes Log.
Tablespace, Segment, Extent, Block: "Their Space Exceeds Bytes" from big to small.
Cursor cycle: DOFC, that is Declare, Open, Fetch, Close.
ANY = at least beat the weakest; ALL = beat the strongest.

Coverage checklist

Architecture, physical files, memory structures, background process: Dec 2025 architecture question.
Concept of table spaces, segments, extents and block: no past question.
Dedicated server, multi threaded server: no past question.
Distributed database, database links, and snapshot: no past question (also a Nov 2023 short-note option, see Nov 2023 question).
Data dictionary, dynamic performance view: no past question.
Security, role management, privilege management, profiles, invoker defined security model: Nov 2019 privilege and role question.
SQL queries, Data extraction from single, multiple tables equi- join, non equi-join, self -join, outer join: Dec 2025 join and EXISTS question.
Usage of like, any, all, exists, in Special operators: Nov 2022 Employee queries.
Hierarchical quires, inline queries, flashback queries: no past question.
Introduction of ANSI SQL, anonymous block, nested anonymous block, branching and looping constructs in ANSI SQL: Dec 2024 branching and looping note.
Cursor management: nested and parameterized cursors, Oracle exception handling mechanism: Nov 2022, Nov 2023, Dec 2024 (two).
Stored procedures, in, out, in out type parameters, usage of parameters in procedures: no past question.
User defined functions their limitations: no past question.
Triggers, mutating errors, instead of triggers: no past question.

Account

Database Management Systems (CS-502) - Unit 5 Short Notes

How unit 5 is examined

Architecture, physical files, memory structures, background process

Concept of table spaces, segments, extents and block

Dedicated server, multi threaded server

Distributed database, database links, and snapshot

Data dictionary, dynamic performance view

Security, role management, privilege management, profiles, invoker defined security model

SQL queries: single table, equi-join, non equi-join, self-join, outer join

Usage of like, any, all, exists, in special operators

Hierarchical queries, inline queries, flashback queries

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

Cursor management: nested and parameterized cursors, Oracle exception handling mechanism

Stored procedures, in, out, in out type parameters

User defined functions their limitations

Triggers, mutating errors, instead of triggers

Last-minute revision

Memory hooks

Coverage checklist