1.1 Relational Database Architecture & Terminology

1.1 Relational Database Architecture & Terminology

To master SQL and pass the Oracle Database SQL Certified Associate (1Z0-071) examination, you must first understand the theoretical and architectural foundations upon which relational database management systems (RDBMS) are built. While day-to-day SQL programming involves issuing declarative statements (SELECT, INSERT, UPDATE, DELETE), Oracle Database executes these operations according to rigorous relational rules first established by Dr. Edgar F. Codd in 1970.

---

The Foundations of the Relational Model

In his landmark 1970 paper, "A Relational Model of Data for Large Shared Data Banks", Dr. E.F. Codd proposed shifting away from hierarchical and network database systems—which required applications to navigate hard-coded physical pointers—toward a mathematically sound model based on first-order predicate logic and mathematical set theory.

In the relational model:

1. Data is perceived by the user as two-dimensional tables (relations).
2. Relationships between data elements are maintained purely by data values stored within the tables, not by physical disk addresses or memory pointers.
3. Operations on data produce new relations (a property known as relational closure).

Relational Theory vs. Commercial SQL / Oracle Terminology

When studying for the 1Z0-071 exam, you must recognize both theoretical relational terms and their commercial SQL counterparts:

Exam Tip: Degree refers to the width of the table (number of columns), while Cardinality refers to the length or height of the table (number of rows). As records are inserted or deleted, Cardinality changes constantly, whereas Degree remains static unless structural DDL (ALTER TABLE) is executed.

---

The Relationship Between the Database and SQL

A relational database stores data; SQL (Structured Query Language) is the declarative, non-procedural language used to define, query, change, and secure it. You state what result you want and the Oracle optimizer decides how to obtain it — the application never navigates data blocks, indexes, or pointers itself. Every statement is sent to the server, parsed, optimized, executed, and answered with either a result set or a status.

The 1Z0-071 exam expects you to classify any statement you are shown into one of five SQL sub-languages:

SELECT last_name FROM employees;              -- DQL: reads a relation
UPDATE employees SET salary = salary * 1.03;  -- DML: changes rows (uncommitted)
COMMIT;                                       -- TCL: makes those row changes permanent
ALTER TABLE employees ADD (nickname VARCHAR2(20));  -- DDL: changes the schema, auto-commits
GRANT SELECT ON employees TO scott;           -- DCL: changes who may read it

Exam Tip: TRUNCATE reads like a data operation but is classified as DDL — it auto-commits and cannot be rolled back. That single classification answers an entire family of exam questions; Chapter 10 works through the full DELETE versus TRUNCATE comparison.

Because SQL is set-oriented, one statement operates on an entire set of rows at once instead of looping row by row. This follows directly from the relational model: an operation on a relation yields another relation (relational closure), which is exactly why a SELECT result can itself be joined, filtered, grouped, or nested inside another query as an inline view or subquery.

---

The Relational Key Taxonomy

Keys are attributes or sets of attributes used to identify rows, enforce integrity, and establish links between tables. Understanding the precise distinctions between key types is essential for relational design and 1Z0-071 exam questions.

+-------------------------------------------------------------+
|                        SUPERKEYS                            |
|  (Any set of attributes that uniquely identifies a row)     |
|                                                             |
|        +-------------------------------------------+        |
|        |              CANDIDATE KEYS               |        |
|        |     (Minimal superkeys with no subsets)   |        |
|        |                                           |        |
|        |   +---------------+   +---------------+   |        |
|        |   |  PRIMARY KEY  |   | ALTERNATE KEY |   |        |
|        |   | (Chosen key)  |   | (Unchosen CK) |   |        |
|        |   +---------------+   +---------------+   |        |
|        +-------------------------------------------+        |
+-------------------------------------------------------------+

1. Superkey

Any set of one or more attributes that collectively allows you to uniquely identify a tuple within a relation. For example, in an EMPLOYEES table, {EMPLOYEE_ID}, {EMPLOYEE_ID, EMAIL}, and {EMPLOYEE_ID, FIRST_NAME, HIRE_DATE} are all superkeys because knowing those values uniquely identifies a single employee.

2. Candidate Key

A minimal superkey—meaning a superkey from which no attribute can be removed without destroying the uniqueness property. If you remove FIRST_NAME from {EMPLOYEE_ID, FIRST_NAME}, the remaining {EMPLOYEE_ID} is still unique; therefore, {EMPLOYEE_ID, FIRST_NAME} was not a candidate key. A relation may have multiple candidate keys (e.g., EMPLOYEE_ID, EMAIL, and GOVT_ID).

3. Primary Key (PK)

The candidate key explicitly chosen by the database designer as the principal mechanism for identifying tuples within a relation.

• Rules: A primary key must be unique across all rows and cannot contain NULL values (NOT NULL).
• Oracle Implementation: When you define a PRIMARY KEY constraint in Oracle, the database automatically enforces uniqueness and non-nullability, creating a unique B-tree index behind the scenes.

4. Alternate Key (Secondary Key)

Any candidate key that was not selected as the primary key. In Oracle SQL, alternate keys are implemented using a UNIQUE constraint on a column (or group of columns) defined with NOT NULL.

5. Foreign Key (FK)

An attribute (or combination of attributes) in one table (the child table) that refers to a candidate key (usually the primary key or a unique key) of another table (or the same table in a recursive relationship, the parent table). Foreign keys enforce referential integrity.

6. Natural Key vs. Surrogate Key

• Natural Key: A key formed from real-world business attributes that already possess intrinsic meaning (e.g., a Vehicle Identification Number VIN, Social Security Number SSN, or International Standard Book Number ISBN).
• Surrogate Key: An artificially created, system-generated identifier with no business meaning (e.g., an auto-incrementing integer or UUID). In Oracle Database 12c and later, surrogate keys are commonly generated using IDENTITY columns or database SEQUENCE objects.

---

Relational Integrity Constraints

Integrity constraints are declarative rules defined on database schemas to guarantee that data entry, modifications, and deletions cannot corrupt data consistency.

                       +-------------------------------+
                       |     RELATIONAL INTEGRITY      |
                       +-------------------------------+
                                       |
         +-----------------------------+-----------------------------+
         |                             |                             |
+------------------+         +--------------------+        +-------------------+
| ENTITY INTEGRITY |         | REFERENTIAL INTEG. |        | DOMAIN INTEGRITY  |
| (Primary Keys)   |         | (Foreign Keys)     |        | (Data types &     |
| - Unique         |         | - Must match PK/UK |        |  CHECK rules)     |
| - NOT NULL       |         |   or be NULL       |        | - Valid range     |
+------------------+         +--------------------+        +-------------------+

1. Entity Integrity

• Rule: Every relation must have a primary key, and no attribute participating in the primary key may contain a NULL value.
• Rationale: A NULL signifies missing, unknown, or inapplicable information. If a primary key component contained NULL, the RDBMS could not guarantee unambiguous tuple identification.

2. Referential Integrity

• Rule: A foreign key value in a child relation must either:
  1. Exactly match an existing candidate key (primary key or unique key) value in the referenced parent relation, OR
  2. Be entirely NULL (unless the foreign key column is explicitly defined with a NOT NULL constraint).
• Oracle Actions on Delete: When a parent row is deleted, Oracle SQL allows developers to specify referential actions:
  • ON DELETE CASCADE: Deletes all matching child rows automatically.
  • ON DELETE SET NULL: Sets the foreign key columns in matching child rows to NULL.
  • Default (no clause): Restricts deletion, raising an ORA-02292 error if child rows exist.

3. Domain Integrity

• Rule: Every column must hold only valid, atomic values drawn from its predefined domain.
• Enforcement: Enforced by column datatypes (e.g., NUMBER, DATE), field lengths, character set definitions, and CHECK constraints (e.g., CHECK (salary > 0)).

4. User-Defined Integrity

• Rule: Specific business rules and constraints defined by the organization that do not fall strictly into entity, referential, or domain categories (e.g., "An employee's commission percentage cannot exceed 50% of their base salary"). Enforced using complex CHECK constraints or database triggers.

---

Schema vs. Instance

In relational database theory and administration, a strict distinction is drawn between the structural definition and the actual data content:

+-------------------------------------------------------------------------+
| SCHEMA (Intension / Metadata)                                           |
| CREATE TABLE employees (                                                |
|     emp_id     NUMBER(6) PRIMARY KEY,                                   |
|     first_name VARCHAR2(20),                                            |
|     salary     NUMBER(8,2)                                              |
| );                                                                      |
+-------------------------------------------------------------------------+
                                     |
                                     | Populated with DML over time
                                     v
+-------------------------------------------------------------------------+
| INSTANCE (Extension / Data State at Time T1)                            |
| EMP_ID | FIRST_NAME | SALARY                                            |
| ------ | ---------- | -------                                           |
| 100    | Steven     | 24000.00                                          |
| 101    | Neena      | 17000.00                                          |
| 102    | Lex        | 17000.00                                          |
+-------------------------------------------------------------------------+

• Database Schema (Intension): The overall design, logical structure, and metadata of the database. It defines tables, columns, constraints, views, and indexes. It changes infrequently via DDL commands (CREATE, ALTER, DROP).
• Database Instance / State (Extension): The actual collection of data stored in the database at a specific moment in time. The state changes dynamically whenever DML commands (INSERT, UPDATE, DELETE) are committed.

---

Client-Server RDBMS Architecture

Modern relational databases employ a two-tier or multi-tier client-server architecture:

1. Client Tier: The user interface or client application (e.g., Oracle SQL Developer, SQLPlus, SQLcl, Java/Python backend service). The client prepares SQL statements, transmits them across the network via Oracle Net Services (SQLNet), and receives formatted result sets.
2. Server Tier: The database server machine hosting the RDBMS engine. The server parses incoming SQL statements, checks syntax and semantics, optimizes execution plans, handles concurrent user locking, manages transactions, and reads/writes data to disk.

---

Oracle Database Architecture: Instance vs. Database

One of the most important architectural concepts tested in Oracle examinations is the clear separation between the Oracle Instance and the Oracle Database.

+-------------------------------------------------------------------------+
|                         ORACLE INSTANCE (Memory + Processes)            |
|                                                                         |
|  +-------------------------------------------------------------------+  |
|  |                     SYSTEM GLOBAL AREA (SGA)                      |  |
|  |  +-------------------+ +--------------------+ +----------------+  |  |
|  |  | Shared Pool       | | Database Buffer    | | Redo Log       |  |  |
|  |  | (Library/Dict Cache)| | Cache (Data Blocks)| | Buffer (Logs)  |  |  |
|  |  +-------------------+ +--------------------+ +----------------+  |  |
|  +-------------------------------------------------------------------+  |
|                                                                         |
|  +-------------------------------------------------------------------+  |
|  |                       BACKGROUND PROCESSES                        |  |
|  |      [DBWn]        [LGWR]        [CKPT]        [SMON]       [PMON]    |  |
|  +-------------------------------------------------------------------+  |
+-------------------------------------------------------------------------+
                                     |
                                     | Mounts & Opens
                                     v
+-------------------------------------------------------------------------+
|                     ORACLE DATABASE (Physical Storage Files)            |
|                                                                         |
|  +------------------+     +-------------------+     +----------------+  |
|  |    DATAFILES     |     |   CONTROL FILES   |     | REDO LOG FILES |  |
|  |  (Tables/Indexes)|     | (DB Structure/Sync) |   | (Change History) |  |
|  |  *.dbf           |     | *.ctl             |     | *.log          |  |
|  +------------------+     +-------------------+     +----------------+  |
+-------------------------------------------------------------------------+

1. The Oracle Instance (Volatile / Memory & Execution)

An Oracle Instance exists only in RAM and CPU. It comprises:

• System Global Area (SGA): Shared memory allocated at startup containing:
  • Database Buffer Cache: Stores copies of data blocks read from disk.
  • Shared Pool: Caches parsed SQL execution plans (Library Cache) and data dictionary metadata (Dictionary Cache).
  • Redo Log Buffer: Caches circular records of database changes before writing to disk.
• Program Global Area (PGA): Private memory allocated per dedicated server process for sorting, hash joins, and session state.
• Background Processes: Operating system processes executing maintenance tasks:
  • DBWn (Database Writer): Writes dirty data blocks from SGA to datafiles.
  • LGWR (Log Writer): Flushes redo log buffer entries to redo log files on disk upon commit.
  • CKPT (Checkpoint): Signals DBWn and updates datafile headers and control files with checkpoint info.
  • SMON (System Monitor): Performs crash recovery and cleans up temporary segments.
  • PMON (Process Monitor): Cleans up failed user connections and releases locks.

2. The Oracle Database (Persistent / Storage Files)

An Oracle Database is a collection of physical operating system files stored on disk:

• Datafiles (*.dbf): Store the physical data of tables, indexes, and system objects.
• Control Files (*.ctl): Store metadata describing the physical database structure, database name, and checkpoint timestamps required to open the database.
• Redo Log Files (*.log): Store sequential historical records of all changes made to the database for crash recovery and transaction rollbacks.

Key Principle: You can start an Oracle Instance without mounting a database (STARTUP NOMOUNT), mount a database without opening it (ALTER DATABASE MOUNT), and finally open it for user transactions (ALTER DATABASE OPEN). An instance is temporary; a database is permanent.