Oracle 1Z0-071: Relational Database Concepts

Topic snapshot

Sample questions

These questions are original IT Mastery practice items aligned to this topic area. They are designed for self-assessment and are not official exam questions.

Question 1

Topic: Relational Database Concepts

Which requirement refers to logical database design rather than physical implementation details in an Oracle database?

Options:

• A. Use a sequence to generate employee numbers starting at 1000.

• B. Create an index on EMPLOYEES.LAST_NAME to improve search performance.

• C. Define DEPARTMENTS.DEPARTMENT_ID as a primary key and reference it from EMPLOYEES.DEPARTMENT_ID.

• D. Store the EMPLOYEES table in the USERS tablespace.

Best answer: C

Explanation: Logical design defines entities, attributes, and relationships. Declaring a primary key and a foreign key models how rows relate to each other, while indexes, tablespaces, and sequences are implementation choices used for storage, performance, or value generation.

The core distinction is that logical database design describes what data exists and how business data is related, while physical implementation describes how Oracle stores, accesses, or generates that data. A primary key uniquely identifies rows, and a foreign key defines the relationship between parent and child tables. Those are logical design decisions because they model business rules and relational structure.

By contrast, an index is a performance object, a tablespace is a storage placement detail, and a sequence is a mechanism for generating values. Those choices affect implementation, not the logical relationship among tables. A good shortcut is: if the requirement defines entities, keys, or relationships, it is logical design; if it defines storage, access path, or object configuration, it is physical implementation.

• Index choice: creating an index is a physical performance decision, not a relational modeling rule.
• Tablespace placement: assigning a table to a tablespace concerns storage location and administration.
• Sequence usage: generating numbers with a sequence is an implementation mechanism, even if the values are later used as keys.

Question 2

Topic: Relational Database Concepts

In this relational model, DEPARTMENTS.department_id is the parent primary key, and EMPLOYEES.department_id is a foreign key to it.

You must return each department name and the last name of any employees in that department. Departments with no employees must still appear, with NULL for the employee name.

Which SQL statement best meets the requirement?

Options:

• A. SELECT d.department_name, e.last_name FROM employees e LEFT OUTER JOIN departments d ON d.department_id = e.department_id

• B. SELECT d.department_name, e.last_name FROM departments d JOIN employees e ON d.department_id = e.department_id

• C. SELECT d.department_name, e.last_name FROM departments d LEFT OUTER JOIN employees e ON d.department_id = e.department_id

• D. SELECT d.department_name, e.last_name FROM departments d LEFT OUTER JOIN employees e ON d.department_id = e.employee_id

Best answer: C

Explanation: A parent-child join uses the parent primary key matched to the child foreign key. Because all departments must appear even when no employees exist, the parent table must be on the preserved side of a LEFT OUTER JOIN.

The core idea is mapping the relationship correctly in SQL: join the parent key to the child foreign key, then choose the join type based on which side must be preserved. Here, DEPARTMENTS.department_id is the parent primary key, and EMPLOYEES.department_id is the child foreign key, so those are the columns that must be compared.

Since the requirement says departments with no employees must still be returned, DEPARTMENTS must be the preserved table. A LEFT OUTER JOIN from DEPARTMENTS to EMPLOYEES does exactly that: matched employees appear when they exist, and unmatched departments still appear with NULL in employee columns.

The closest wrong pattern is using the child table on the preserved side, which keeps all employees instead of all departments.

• The inner join on the correct columns fails because it removes departments that have no matching employee rows.
• The outer join starting from EMPLOYEES preserves child rows, not parent rows, so empty departments are still missing.
• The join comparing department_id to employee_id uses unrelated keys and can return incorrect matches.

Question 3

Topic: Relational Database Concepts

A developer says: “To store PRODUCTS, I will create an index because indexes contain the rows, and the table can just define the columns.” In Oracle Database, which statement correctly applies the relational rule for storing data?

Options:

• A. A sequence stores one row for each product and supplies its values.

• B. A data file is the logical structure queried for rows and columns.

• C. A table stores data as rows, and columns define each row’s attributes.

• D. An index stores the rows, and the table stores only column names.

Best answer: C

Explanation: In Oracle relational databases, a table is the logical structure that stores data. Each row represents one occurrence of an entity, and each column represents an attribute of that occurrence.

The core relational concept is that a table holds related data in a two-dimensional form: rows and columns. A row represents a single record, such as one product, and columns represent attributes of that record, such as product name or price. An index is a separate structure used to improve data access, but it does not replace the table as the logical store of relational data. Likewise, a data file is a physical storage component, not the logical object users query, and a sequence only generates numeric values. The key takeaway is to separate logical relational objects, like tables, from physical storage structures and supporting objects.

• Index confusion fails because an index supports faster lookup; it is not the logical container for table rows.
• Physical storage mix-up fails because a data file belongs to physical storage, not the relational row-and-column model.
• Sequence misuse fails because a sequence generates numbers, typically for keys, but does not store records.

Question 4

Topic: Relational Database Concepts

Consider this relational model:

DEPARTMENTS (department_id PK, department_name)
EMPLOYEES   (employee_id PK, last_name, department_id FK
             references DEPARTMENTS.department_id)

Which join condition correctly matches each EMPLOYEES row to its parent DEPARTMENTS row?

Options:

• A. e.employee_id = d.department_name

• B. e.employee_id = d.department_id

• C. e.department_id = d.department_name

• D. e.department_id = d.department_id

Best answer: D

Explanation: In a parent-child relationship, the join condition uses the foreign key in the child table and the referenced key in the parent table. Here, EMPLOYEES.department_id references DEPARTMENTS.department_id, so those two columns must be joined.

The core rule is: join the child table’s foreign key to the parent table’s primary key or referenced unique key. In this model, EMPLOYEES is the child because it contains department_id as a foreign key, and DEPARTMENTS is the parent because department_id is its primary key.

A correct Oracle join therefore matches e.department_id to d.department_id. That condition maps each employee to the department row it references. Using employee_id instead would compare unrelated keys, and using department_name would not follow the declared key relationship at all. The key takeaway is to trace the FK-to-PK reference shown in the relational model and join those columns.

• Wrong key The option using employee_id to match department_id compares unrelated identifiers.
• Wrong column type The option matching department_id to department_name ignores the FK-to-PK relationship and compares different kinds of data.
• Both mismatched The option using employee_id and department_name breaks the relationship on both sides of the join.

Question 5

Topic: Relational Database Concepts

Examine the exhibit:

CREATE TABLE departments (
  dept_id   NUMBER CONSTRAINT dept_pk PRIMARY KEY,
  dept_name VARCHAR2(30)
);

CREATE TABLE employees (
  emp_id    NUMBER CONSTRAINT emp_pk PRIMARY KEY,
  dept_id   NUMBER CONSTRAINT emp_dept_fk
           REFERENCES departments(dept_id),
  last_name VARCHAR2(30)
);

CREATE INDEX emp_dept_ix ON employees(dept_id);
CREATE SYNONYM staff FOR employees;

Which statement best interprets these objects in Oracle Database?

Options:

• A. The synonym defines the relationship to DEPARTMENTS, and the foreign key mainly improves query performance.

• B. The index stores employee rows in department order, and the synonym stores another copy of EMPLOYEES.

• C. The index and synonym are both relational structures because applications can query them like tables.

• D. EMPLOYEES and DEPARTMENTS store relational data; the index supports access performance, and the synonym is only an alternate name.

Best answer: D

Explanation: The tables and their primary-key and foreign-key constraints represent the relational structure because they hold data and define relationships. The index exists to improve access performance, and the synonym only provides another name for the table.

In Oracle, relational data structures are the objects that store rows and define how those rows relate to one another. Here, DEPARTMENTS and EMPLOYEES are tables, and the primary key and foreign key constraints define entity identity and the relationship between the two tables.

The other objects serve different purposes:

• EMP_DEPT_IX is an index on employees(dept_id) to help row access efficiency.
• STAFF is a synonym that lets users reference EMPLOYEES by another name.

An index does not become the relationship itself, and a synonym does not create a second copy of the data. The key takeaway is that tables and constraints model the data; indexes and synonyms support performance or access.

• Index as storage copy fails because an index does not store a separate ordered table of employee rows for business use.
• Synonym as relationship fails because the foreign key defines the table relationship, not the synonym.
• Foreign key as performance tool fails because its main role is referential integrity, even if indexes may help related queries.
• Queryable means relational fails because being usable in SQL does not make an object a row-storing relational structure.

Question 6

Topic: Relational Database Concepts

A developer reviews this model:

DEPARTMENTS
- department_id (PK)
- department_name
- location_id

EMPLOYEES
- employee_id (PK)
- last_name
- department_id (FK to DEPARTMENTS.department_id)
- manager_id

Using aliases d for DEPARTMENTS and e for EMPLOYEES, which join condition correctly returns each employee with the matching department name?

Options:

• A. e.manager_id = d.department_id

• B. e.department_id = d.department_id

• C. e.department_id = d.location_id

• D. e.employee_id = d.department_id

Best answer: B

Explanation: The correct join uses the child table’s foreign key and the parent table’s referenced primary key. Here, EMPLOYEES.department_id points to DEPARTMENTS.department_id, so that equality condition returns the matching department for each employee.

In a parent-child relationship, the child table contains the foreign key that references the parent table’s primary key. To join the tables correctly, match the foreign key column in EMPLOYEES to the referenced key column in DEPARTMENTS.

• Parent table: DEPARTMENTS
• Parent key: department_id
• Child table: EMPLOYEES
• Child foreign key: department_id

That makes the correct join condition e.department_id = d.department_id. Other numeric columns may look joinable, but they represent different facts, so they would produce incorrect matches or meaningless results.

• Matching employee_id to department_id compares two unrelated keys.
• Matching manager_id to department_id uses a different relationship than the department lookup.
• Matching department_id to location_id compares the foreign key to a nonreferenced department column.

Question 7

Topic: Relational Database Concepts

Examine the table definitions:

CREATE TABLE customers (
  customer_id   NUMBER PRIMARY KEY,
  sales_rep_id  NUMBER,
  customer_name VARCHAR2(30)
);

CREATE TABLE orders (
  order_id      NUMBER PRIMARY KEY,
  customer_id   NUMBER REFERENCES customers(customer_id),
  sales_rep_id  NUMBER,
  order_total   NUMBER
);

Which join condition correctly matches each orders row to its related customers row?

Options:

• A. ON o.sales_rep_id = c.sales_rep_id

• B. ON o.order_id = c.customer_id

• C. ON o.order_id = c.sales_rep_id

• D. ON o.customer_id = c.customer_id

Best answer: D

Explanation: A foreign key stores values from the related parent table’s primary key. Here, orders.customer_id references customers.customer_id, so that is the column pair used to match child rows to parent rows.

The core concept is parent-child row matching. A primary key uniquely identifies each row in the parent table, and a foreign key in the child table stores that parent key value. In the exhibit, CUSTOMERS.CUSTOMER_ID is the primary key, and ORDERS.CUSTOMER_ID is explicitly defined to reference it.

So the correct join uses those two columns:

FROM orders o
JOIN customers c
  ON o.customer_id = c.customer_id

Columns such as ORDER_ID identify orders, not customers, and SALES_REP_ID is not the declared relationship column. The key takeaway is to join the child table’s foreign key to the parent table’s primary key.

• Order identifier mismatch fails because order_id identifies an order row, not the related customer row.
• Shared salesperson fails because matching on sales_rep_id uses a non-key attribute that can repeat across many rows.
• Mixed unrelated columns fails because order_id and sales_rep_id do not represent the declared table relationship.

Question 8

Topic: Relational Database Concepts

Which statement correctly distinguishes an Oracle table from objects created mainly to support access or performance?

Options:

• A. A table stores business data in rows and columns, while an index is an access structure used to speed row retrieval.

• B. A synonym stores a second copy of table rows so users can query them faster.

• C. A view stores its own data separately from the base tables referenced by its query.

• D. An index defines the logical columns and datatypes for a table.

Best answer: A

Explanation: In Oracle, a table is the primary relational object that stores data. Objects such as indexes and synonyms support access or usability, but they do not replace the table as the data-storing structure.

The core distinction is between logical data structures and supporting objects. A table stores the actual relational data as rows and columns. An index is a separate structure built on one or more columns to improve access speed; it helps Oracle find rows more efficiently but is not the business-data container. A synonym is only an alternate name for another object, and a view stores a query definition rather than a separate copy of table data. This is why the statement about tables storing data and indexes supporting retrieval is the only accurate one.

• Synonym confusion fails because a synonym is just an alias for another object, not a duplicate data store.
• View confusion fails because a standard view stores the SELECT definition, not independent table rows.
• Index definition fails because table columns and datatypes are defined in the table DDL, not in an index.

Question 9

Topic: Relational Database Concepts

Examine the exhibit.

CREATE TABLE departments (
  department_id   NUMBER PRIMARY KEY,
  department_name VARCHAR2(30) NOT NULL
);

CREATE TABLE employees (
  employee_id     NUMBER PRIMARY KEY,
  last_name       VARCHAR2(25) NOT NULL,
  department_id   NUMBER REFERENCES departments(department_id)
);

A data modeler says one pending project note belongs to logical database design, not physical implementation. Which note fits that description?

Options:

• A. Create an index on EMPLOYEES(department_id)

• B. Model each employee as related to at most one department

• C. Choose a larger database block size for employee rows

• D. Store EMPLOYEES in a separate tablespace

Best answer: B

Explanation: Logical database design describes entities, keys, and relationships. The employee-to-department relationship rule is a logical modeling decision, while indexes, tablespaces, and block size are physical implementation choices.

The core distinction is what the data means versus how the data is stored or accessed. Logical design defines entities, attributes, primary keys, foreign keys, and relationship rules such as cardinality and optionality. In the exhibit, the relationship between EMPLOYEES and DEPARTMENTS is part of that logical model, so a note about an employee being related to at most one department belongs to logical design.

Physical implementation decisions come later and focus on storage and performance. Creating an index affects access speed, choosing a tablespace affects storage placement, and selecting a block size affects physical storage behavior. Those choices do not change the business meaning of the data.

A good test is this: if the requirement changes relationships or rules in the model, it is logical; if it changes storage or tuning, it is physical.

• Index choice is physical because it improves access paths, not the business structure of the model.
• Tablespace placement is physical because it controls where database objects are stored.
• Block size setting is physical because it concerns storage characteristics, not entity relationships.

Question 10

Topic: Relational Database Concepts

Which statement correctly distinguishes a theoretical relational-model concept from an Oracle physical storage concept?

Options:

• A. A tablespace is a relational-model object, while a primary key is an Oracle storage structure.

• B. A relation is a logical table, while extents and segments are physical storage structures.

• C. A row is an Oracle physical storage unit, while a block belongs to the relational model.

• D. A foreign key is part of Oracle physical storage, while a segment is part of the relational model.

Best answer: B

Explanation: The relational model is about logical data organization: relations, rows, columns, and keys. Oracle blocks, extents, segments, and tablespaces belong to the physical storage layer, so the statement separating relation from extents and segments is the only correct one.

The key distinction is logical model versus physical implementation. In relational theory, a table is a relation, a column is an attribute, and a row is a tuple. Keys such as primary keys and foreign keys are also logical design concepts used to identify rows and define relationships.

Oracle physical storage uses structures such as:

• data blocks
• extents
• segments
• tablespaces

These describe how data is stored internally, not how the relational model defines data conceptually. A common exam trap is to mix logical terms like row or key with physical terms like block or segment. The correct choice keeps those layers separate.

• Tablespace confusion fails because a tablespace is physical storage, while a primary key is a logical constraint.
• Row versus block fails because a row is a logical data item, but a block is a physical storage unit.
• Foreign key confusion fails because a foreign key is a logical relationship constraint, while a segment is physical storage.

Continue with full practice

Use the Oracle 1Z0-071 Practice Test page for the full IT Mastery route, mixed-topic practice, timed mock exams, explanations, and web/mobile app access.

Try Oracle 1Z0-071 on Web View Oracle 1Z0-071 Practice Test

Related focused pages

• Free Oracle 1Z0-071 Full-Length Practice Exam
• SQL SELECT
• Restrict and Sort Data
• Single-Row Functions
• Conversion and CASE
• Group Functions
• Display Data from Multiple Tables
• Use Subqueries to Solve Queries
• Use Set Operators
• Manage Tables by Using DML Statements
• Indexes and Sequences
• DDL Tables and Relationships
• Manage Views
• Control User Access
• Data Dictionary Views
• Manage Data in Different Time Zones

Free review resource

Read the Oracle 1Z0-071 Cheat Sheet on Tech Exam Lexicon, then return to IT Mastery for timed practice.