1.1 Relational Database Architecture & Terminology

Oracle Database SQL Certified Associate 1Z0-071

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: Relational Database Concepts & Logical Modeling

1.1 Relational Database Architecture & Terminology 1.2 Entity Relationship Modeling & SQL Mapping 1.3 Database Normalization & Design Fundamentals

2 Chapter 2: Retrieving Data with SQL SELECT

2.1 SQL SELECT Fundamentals & Projection 2.2 Literal Values, Concatenation, and Quote Operators 2.3 Handling NULLs & Distinct Rows in Projection

3 Chapter 3: Restricting and Sorting Query Data

3.1 WHERE Clause Filtering & Comparison Operators 3.2 Logical Conditions & Operator Precedence 3.3 Sorting Data with ORDER BY 3.4 Substitution Variables & Session Directives

4 Chapter 4: Customizing Output with Single-Row Functions

4.1 Character Manipulation & Case Conversion 4.2 Numeric & Mathematical Functions 4.3 Date Arithmetic & Date Manipulation Functions 4.4 Nesting Single-Row Functions

5 Chapter 5: Conversion Functions and Conditional Expressions

5.1 Implicit vs Explicit Datatype Conversion 5.2 TO_CHAR, TO_NUMBER, and TO_DATE Functions 5.3 General NULL-Handling Functions 5.4 Conditional Expressions: CASE and DECODE

6 Chapter 6: Reporting Aggregated Data with Group Functions

6.1 Aggregate Functions & NULL Mechanics 6.2 GROUP BY Clause & Aggregation Rules 6.3 Filtering Groups with the HAVING Clause

7 Chapter 7: Displaying Data from Multiple Tables (Joins)

7.1 ANSI/ISO SQL Equijoins & Natural Joins 7.2 Non-Equijoins, Self Joins, and Cartesian Products 7.3 Outer Joins: LEFT, RIGHT, and FULL

8 Chapter 8: Solving Queries with Subqueries

8.1 Single-Row Subqueries 8.2 Multiple-Row Subqueries & Inline Views 8.3 Correlated Subqueries & EXISTS Logic 8.4 Subquery Cardinality & NULL Traps

9 Chapter 9: Set Operators

9.1 UNION and UNION ALL Operators 9.2 INTERSECT and MINUS Operators 9.3 Set Operator Guidelines & ORDER BY Rules

10 Chapter 10: Managing Tables with DML & Transaction Control

10.1 INSERT, UPDATE, and DELETE Statements 10.2 MERGE and Multitable Inserts 10.3 Transaction Management: COMMIT, ROLLBACK, and SAVEPOINT

11 Chapter 11: Managing Tables and Relationships with DDL

11.1 CREATE TABLE & Oracle Built-In Datatypes 11.2 Integrity Constraints 11.3 Modifying Tables & Managing Constraints 11.4 Global Temporary Tables & External Tables

12 Chapter 12: Views

12.1 Simple vs Complex Views 12.2 DML Operations on Views & View Constraints

13 Chapter 13: Indexes, Synonyms, and Sequences

13.1 Sequences 13.2 Indexes 13.3 Synonyms

14 Chapter 14: Controlling User Access & Privileges

14.1 System Privileges 14.2 Object Privileges 14.3 Roles & Privilege Management

15 Chapter 15: Oracle Data Dictionary Views

15.1 Data Dictionary Structure & View Prefixes 15.2 Querying Schema & Object Metadata 15.3 Inspecting Constraints, Privileges, and Comments

16 Chapter 16: Managing Data in Different Time Zones

16.1 Datetime Data Types & Time Zone Offsets 16.2 Interval Data Types & Arithmetic 16.3 Time Zone Functions & Session Environment

1.1 Relational Database Architecture & Terminology

Key Takeaways Explain This Topic AI

The relational model, introduced by Dr. E.F. Codd in 1970, is grounded in mathematical set theory and first-order predicate logic, organizing data into relations (tables), tuples (rows), and attributes (columns).

Keys form the backbone of relational identity and navigation: superkeys provide uniqueness, candidate keys are minimal superkeys, primary keys are designated candidate keys, and foreign keys establish cross-table referential relationships.

Relational integrity relies on three fundamental pillars: entity integrity (primary keys must be unique and non-null), referential integrity (foreign keys must match an existing parent key or be null), and domain integrity (column values must adhere to defined datatypes and constraints).

A relational database schema represents the static metadata blueprint (intension), whereas a database instance represents the dynamic data state at any given point in time (extension).

In Oracle Database architecture, the 'Instance' (volatile memory structures like SGA/PGA and background processes like PMON, SMON, DBWn, LGWR) is strictly separated from the 'Database' (persistent physical datafiles, control files, and redo log files on disk).

Last updated: August 2026

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:

Data is perceived by the user as two-dimensional tables (relations).

Relationships between data elements are maintained purely by data values stored within the tables, not by physical disk addresses or memory pointers.

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:

Relational Theory Term SQL / Oracle Term Definition & Characteristics

Relation Table A two-dimensional named structure consisting of rows and columns. In pure theory, a relation has no duplicate tuples and order does not matter.

Tuple Row / Record A single horizontal entry in a table representing an instance of the entity.

Attribute Column / Field A named vertical component of a table holding a specific fact about each tuple.

Domain Datatype & Constraints The set of permissible, atomic values from which attribute values are drawn (e.g., VARCHAR2(30) , NUMBER(6,2) , DATE ).

Degree (Arity) Column Count The number of attributes (columns) that comprise a relation.

Cardinality Row Count The number of tuples (rows) currently stored in a relation.

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:

Sub-Language Full Name Statements Relational Purpose Transaction Behavior

DQL Data Query Language SELECT Projection, selection, and joining — reads relations without altering them Read-consistent; starts no transaction (except SELECT ... FOR UPDATE )

DML Data Manipulation Language INSERT , UPDATE , DELETE , MERGE Changes the extension of a relation (its rows) Transactional — must be committed or rolled back

DDL Data Definition Language CREATE , ALTER , DROP , TRUNCATE , RENAME Changes the intension of the schema and updates the data dictionary Issues an implicit COMMIT before and after; cannot be rolled back

DCL Data Control Language GRANT , REVOKE Controls which users may perform which actions Issues an implicit COMMIT

TCL Transaction Control Language COMMIT , ROLLBACK , SAVEPOINT Makes DML changes permanent or discards them Defines the transaction boundary itself 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.

• Key Type Business Meaning Stability Performance
• Natural Key High (human-readable) Low (can change if business rules change) Can be wide composite strings
• Surrogate Key None (pure identifier) High (never changes once generated) Fast, narrow numeric joins
• 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:

Exactly match an existing candidate key (primary key or unique key) value in the referenced parent relation, OR

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 :

Client Tier: The user interface or client application (e.g., Oracle SQL Developer, SQL Plus, SQLcl, Java/Python backend service). The client prepares SQL statements, transmits them across the network via Oracle Net Services (SQL Net), and receives formatted result sets.

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.