1Z0-071 Exam Prep
Chapter 1
Volume 1
Chapter 1 covers the relational model — the foundation everything else in this exam sits on. These questions look conceptual but the exam is precise about terminology. Knowing the difference between a row and a record, a column and a field, a table and a relation, and exactly what referential integrity enforces is what separates candidates who get these right from those who guess.
This is also where the exam tests why certain table designs are bad — understanding update anomalies, insert anomalies, and delete anomalies explains why normalisation exists and why foreign keys matter.
In this post
- Relational model terminology
- Tables, rows and columns — the building blocks
- Primary keys — uniqueness and identity
- Foreign keys and referential integrity
- What referential integrity actually enforces
- One-to-many and many-to-many relationships
- Data anomalies — why bad design causes problems
- Normalisation — what it solves
- NULL in the relational model
- Schema and ownership in Oracle
- Quick reference
- Practice questions
01 — Relational model terminology
The relational model uses formal terms that have everyday equivalents. The exam uses both sets of terms and expects you to match them correctly.
| Formal term | Common equivalent | Oracle term |
|---|---|---|
| Relation | Table | Table |
| Tuple | Row | Row |
| Attribute | Column | Column |
| Domain | Set of valid values for a column | Data type + constraint |
| Degree | Number of columns in a table | — |
| Cardinality | Number of rows in a table | — |
Cardinality in two contexts: In the relational model, cardinality means the number of rows in a table. In the context of relationships between tables (one-to-many, many-to-many), cardinality describes the nature of the relationship. The exam uses cardinality in both senses — read the context carefully.
02 — Tables, rows and columns — the building blocks
A relational table has a fixed structure defined by its columns. Each row represents one instance of the subject the table describes. Every row has the same columns — the value in each column may differ, but the column set is the same for all rows.
-- A table represents one subject -- Each row = one employee -- Each column = one fact about an employee EMPLOYEES table: ┌─────────────┬───────────┬──────────┬───────────────┐ │ EMPLOYEE_ID │ LAST_NAME │ SALARY │ DEPARTMENT_ID │ ├─────────────┼───────────┼──────────┼───────────────┤ │ 100 │ King │ 24000 │ 90 │ │ 101 │ Kochhar │ 17000 │ 90 │ │ 102 │ De Haan │ 17000 │ 90 │ └─────────────┴───────────┴──────────┴───────────────┘ -- Rules that always apply: -- Each column holds one type of information only -- Row order has no meaning — the table is a set, not a list -- Column order has no meaning — only column names matter -- Every row is unique (enforced by a primary key)
Row order is not guaranteed: A relational table is a mathematical set — sets have no inherent order. Without ORDER BY, Oracle can return rows in any sequence. Two identical queries run at different times may return rows in different order. The exam tests this with questions asking whether a query without ORDER BY has predictable row ordering — it does not.
03 — Primary keys — uniqueness and identity
A primary key uniquely identifies each row in a table. No two rows can share the same primary key value, and no primary key value can be NULL. These two rules together define what a primary key is.
-- Primary key rules: -- 1. Must be unique across all rows in the table -- 2. Cannot contain NULL values -- 3. A table can have only ONE primary key -- 4. The primary key can span multiple columns (composite key) -- Single-column primary key CREATE TABLE employees ( employee_id NUMBER(6) PRIMARY KEY, -- unique + NOT NULL last_name VARCHAR2(25) ); -- Composite primary key — combination must be unique CREATE TABLE order_items ( order_id NUMBER(6), item_id NUMBER(6), quantity NUMBER(4), CONSTRAINT oi_pk PRIMARY KEY (order_id, item_id) );
| Requirement | Primary Key | Unique Key |
|---|---|---|
| Values must be unique | Yes | Yes |
| NULL allowed? | No | Yes — multiple NULLs allowed |
| How many per table? | One only | Multiple allowed |
| Used as FK target? | Yes — most common | Yes — also valid |
04 — Foreign keys and referential integrity
A foreign key creates a link between two tables. It says: "the value in this column must exist as a primary key (or unique key) value in that other table." The table with the foreign key is called the child table. The table being referenced is the parent table.
-- DEPARTMENTS is the parent table (referenced) CREATE TABLE departments ( department_id NUMBER(4) PRIMARY KEY, department_name VARCHAR2(30) NOT NULL ); -- EMPLOYEES is the child table (has the foreign key) CREATE TABLE employees ( employee_id NUMBER(6) PRIMARY KEY, last_name VARCHAR2(25) NOT NULL, department_id NUMBER(4), CONSTRAINT emp_dept_fk FOREIGN KEY (department_id) REFERENCES departments(department_id) );
Parent table must exist first: When creating tables with foreign keys, the parent table must be created before the child table. You cannot reference a table that does not yet exist. Similarly, you cannot drop a parent table while child rows exist (without CASCADE).
05 — What referential integrity actually enforces
Referential integrity is the rule that a foreign key value must either match an existing primary key value in the parent table, or be NULL. Oracle enforces this automatically on every INSERT and UPDATE to the child table, and on every DELETE from the parent table.
| Operation | What Oracle checks | Failure result |
|---|---|---|
| INSERT into child table | FK value exists in parent PK column (or is NULL) | ORA-02291: integrity constraint violated — parent key not found |
| UPDATE FK value in child | New FK value exists in parent PK column (or is NULL) | ORA-02291 |
| DELETE from parent table | No child rows reference the deleted PK value | ORA-02292: integrity constraint violated — child record found |
| UPDATE PK value in parent | No child rows reference the old PK value | ORA-02292 |
-- This INSERT fails — department 999 does not exist INSERT INTO employees (employee_id, last_name, department_id) VALUES (300, 'Smith', 999); -- ORA-02291: parent key not found -- This INSERT succeeds — NULL FK is always allowed INSERT INTO employees (employee_id, last_name, department_id) VALUES (301, 'Jones', NULL); -- NULL means "no department assigned" — this is valid -- This DELETE fails — employees in dept 90 would be orphaned DELETE FROM departments WHERE department_id = 90; -- ORA-02292: child record found
06 — One-to-many and many-to-many relationships
Relationships between tables have a cardinality — how many rows on one side relate to how many on the other. The exam tests whether you can identify the relationship type from a table design.
| Relationship type | Description | How implemented |
|---|---|---|
| One-to-one | Each row in table A relates to at most one row in table B | FK with UNIQUE constraint on the child side |
| One-to-many | One row in table A relates to many rows in table B | FK in the child table — the most common relationship |
| Many-to-many | Many rows in A relate to many rows in B | Junction table with two FKs — cannot be stored with just two tables |
-- One-to-many: one department has many employees -- DEPARTMENTS (one) → EMPLOYEES (many) -- FK is in EMPLOYEES (the "many" side) -- Many-to-many: students enrol in many courses, courses have many students -- Cannot link STUDENTS directly to COURSES with one FK -- Need a junction table: ENROLMENTS (student_id FK + course_id FK) CREATE TABLE enrolments ( student_id NUMBER REFERENCES students(student_id), course_id NUMBER REFERENCES courses(course_id), enrol_date DATE, PRIMARY KEY (student_id, course_id) );
The FK is always on the "many" side: In a one-to-many relationship, the foreign key column lives in the child table — the "many" side. The parent table (the "one" side) has the primary key. The exam tests this with questions asking which table the FK belongs in.
07 — Data anomalies — why bad design causes problems
The exam tests the three classic data anomalies that occur when a table tries to represent more than one subject. Understanding these explains why normalisation exists.
| Anomaly | What it means | Example |
|---|---|---|
| Update anomaly | The same fact is stored in multiple rows — updating one and missing another creates inconsistency | Department name stored in every employee row — changing the name requires updating all employee rows for that department |
| Insert anomaly | Cannot store a fact without also storing unrelated data | Cannot add a new department unless at least one employee is hired into it — the department info has nowhere to go |
| Delete anomaly | Deleting one fact unintentionally destroys another | Deleting the last employee in a department removes all record of that department existing |
-- BAD DESIGN: employee and department data in one table ┌─────────────┬───────────┬─────────────────┬──────────────────┐ │ EMPLOYEE_ID │ LAST_NAME │ DEPARTMENT_ID │ DEPARTMENT_NAME │ ├─────────────┼───────────┼─────────────────┼──────────────────┤ │ 100 │ King │ 90 │ Executive │ ← dept stored here │ 101 │ Kochhar │ 90 │ Executive │ ← duplicated here │ 200 │ Whalen │ 10 │ Administration │ └─────────────┴───────────┴─────────────────┴──────────────────┘ -- Update anomaly: rename 'Executive' to 'Management' -- Must update EVERY row in dept 90 — miss one = inconsistency -- GOOD DESIGN: separate tables — each table represents one subject -- EMPLOYEES table: employee facts only -- DEPARTMENTS table: department facts only -- JOIN them when you need combined data
08 — Normalisation — what it solves
Normalisation is the process of structuring tables so that each table represents exactly one subject and each fact is stored in exactly one place. The exam touches on the first three normal forms at a conceptual level.
| Normal form | Rule | What it fixes |
|---|---|---|
| First (1NF) | All column values are atomic — no repeating groups or multi-valued columns | Eliminates columns like PHONE1, PHONE2, PHONE3 — use a separate PHONES table instead |
| Second (2NF) | Every non-key column depends on the whole primary key (applies to composite keys) | Eliminates partial dependencies — if a column only depends on part of a composite PK, move it to a separate table |
| Third (3NF) | No non-key column depends on another non-key column (no transitive dependencies) | Eliminates facts about facts — department name depends on department_id, not on employee_id |
Normalisation on the exam: The 1Z0-071 exam does not ask you to normalise a table to a specific form — it tests whether you can identify which design problem an anomaly represents, and whether you understand that separating data into related tables (with FKs) is the solution. The three normal forms are context, not an exam task.
09 — NULL in the relational model
NULL in the relational model means a value is unknown, not applicable, or not yet provided. It is not zero, not an empty string, and not a special marker — it represents the absence of a value.
-- Three reasons a value might be NULL: -- 1. Unknown: birth_date NULL — the date exists but we don't know it -- 2. Not applicable: commission_pct NULL — this employee type earns no commission -- 3. Not yet entered: end_date NULL — the employee is still employed -- All three situations store identical NULL values -- There is no way to distinguish "unknown" from "not applicable" in the data -- NULL is not equal to anything — not even NULL -- NULL = NULL evaluates to UNKNOWN, not TRUE -- The only correct test for NULL is IS NULL / IS NOT NULL
NULL in primary keys is forbidden: A primary key value must be known and unique — NULL (unknown) is not permitted. This is because you cannot reliably identify a row if its key is unknown. Foreign key values, however, can be NULL — a NULL FK simply means "no relationship to the parent table" for that row.
10 — Schema and ownership in Oracle
In Oracle, a schema is a named collection of database objects that belongs to a specific database user. When you create a table, it belongs to your schema. The schema name is your username.
-- Every object belongs to a schema (owner) -- When HR user creates a table, it lives in the HR schema CREATE TABLE employees (...); -- creates HR.EMPLOYEES -- Querying your own objects — no schema prefix needed SELECT * FROM employees; -- Oracle looks in HR schema first -- Querying another user's objects — schema prefix required SELECT * FROM scott.emp; -- explicit schema prefix SELECT * FROM hr.departments; -- must have SELECT privilege on it -- Synonym hides the schema prefix CREATE SYNONYM emp FOR scott.emp; SELECT * FROM emp; -- no prefix needed — synonym handles it
Schema = username in Oracle: Unlike some other databases, Oracle does not have a separate "schema" concept distinct from user accounts. When a user is created, their schema is created automatically. Schema name and username are the same.
11 — Quick reference
| Rule | Detail |
|---|---|
| Relation = table, tuple = row, attribute = column | Formal relational model terms the exam uses |
| Row order is not guaranteed | Without ORDER BY, result order is unpredictable |
| Primary key: unique + NOT NULL | One per table — can span multiple columns |
| Unique key: unique, NULLs allowed | Multiple NULLs permitted — multiple per table |
| FK must match parent PK or be NULL | ORA-02291 if parent key not found |
| NULL FK is always valid | Means "no relationship" — not a violation |
| Cannot delete parent row with child rows | ORA-02292 — use CASCADE or delete children first |
| FK is on the "many" side | One-to-many: FK lives in the child (many) table |
| Many-to-many needs junction table | Two tables with FKs pointing to both parents |
| Update anomaly | Same fact in multiple rows — one update misses a row |
| Insert anomaly | Cannot add a fact without unrelated data |
| Delete anomaly | Deleting one fact accidentally removes another |
| Schema = username in Oracle | Objects created by HR user are in the HR schema |
12 — Practice questions
Q1 — A table stores employee data including department_name in every employee row. When the Marketing department is renamed to Brand Management, a developer updates only some of the employee rows before stopping. What kind of problem has occurred?
Q2 — An EMPLOYEES table has a DEPARTMENT_ID column defined as a FOREIGN KEY referencing DEPARTMENTS(DEPARTMENT_ID). A developer tries to insert an employee with DEPARTMENT_ID = NULL. What happens?
Q3 — A STUDENTS table and a COURSES table need to represent the fact that each student can enrol in many courses and each course can have many students. What is the correct relational design?
Oracle SQL 1Z0-071 Exam Prep Guide — Complete 4-Volume Set
19 chapters · 1,480 practice questions · 1,249 SQL examples
Chapter 1 covers the full relational model foundation — with practice sets for terminology, key types, relationship cardinality, anomalies, and normalisation. Instant PDF download.
Get the full guide — $25 →
Chapter 1 covers the full relational model foundation — with practice sets for terminology, key types, relationship cardinality, anomalies, and normalisation. Instant PDF download.
No comments:
Post a Comment