Test Your Oracle Normalization Knowledge with This Quiz

Progress

True / False

In Oracle, a table is in First Normal Form (1NF) if all its columns contain atomic (indivisible) values.

True False

A table is in 1NF if each column contains only atomic values, meaning there are no repeating groups or arrays.

True / False

In Oracle, a table that contains duplicate rows is considered to be in First Normal Form (1NF).

True False

1NF requires that each table has a primary key to ensure that each row is unique. Duplicate rows violate this principle.

True / False

In Oracle, having a column with multiple values (such as a comma-separated list) violates First Normal Form (1NF).

True False

1NF requires that each column contain only a single value, so a column with multiple values violates this rule.

True / False

In Oracle, a table can be in First Normal Form (1NF) even if it contains NULL values.

True False

A table can be in 1NF if it contains NULL values, as long as each column contains atomic values and there are no repeating groups.

True / False

In Oracle, the process of normalizing a table to First Normal Form (1NF) may involve creating additional tables.

True False

To achieve 1NF, it may be necessary to decompose a table into multiple tables to eliminate repeating groups and ensure atomic values.

True / False

In Oracle, a table in First Normal Form (1NF) must have a primary key.

True False

A primary key ensures that each row is unique, which is a requirement for a table to be in 1NF.

True / False

In Oracle, if a table has nested tables or object types, it is considered to be in First Normal Form (1NF).

True False

Nested tables and object types can contain multiple values or complex structures, violating the atomicity requirement of 1NF.

True / False

In Oracle, the use of composite keys can help achieve First Normal Form (1NF) by ensuring uniqueness of rows.

True False

Composite keys, which consist of multiple columns, can be used to uniquely identify rows and help achieve 1NF by ensuring no duplicate rows.

True / False

In Oracle, a table that includes JSON data types can still be in First Normal Form (1NF) as long as the JSON data is not queried in a way that breaks atomicity.

True False

If JSON data is stored but not queried in a way that breaks atomicity, the table can still be considered in 1NF.

Q10

True / False

The Oracle Certified Professional (OCP) exam includes knowledge on the principles of normalization, including First Normal Form (1NF), and how to apply these principles to design efficient database schemas.

True False

The OCP certification covers database design principles, including normalization, ensuring that candidates understand how to apply 1NF and other normalization forms to create efficient and reliable database schemas.

Q11

Single Choice

What is the primary goal of normalization in an Oracle database?

To reduce the number of tables To minimize redundancy and avoid data anomalies To increase the complexity of queriesTo combine multiple tables into one

The main purpose of normalization is to organize data in a database to reduce redundancy and prevent data anomalies.

Q12

Single Choice

Which normal form (NF) ensures that each table in Oracle has a primary key and that all non-key attributes are dependent only on the primary key?

First Normal Form (1NF) Second Normal Form (2NF) Third Normal Form (3NF)Boyce-Codd Normal Form (BCNF)

Third Normal Form (3NF) requires that a table be in 2NF and that all non-key attributes depend only on the primary key.

Q13

Single Choice

Which of the following is a characteristic of a table in First Normal Form (1NF) in Oracle?

Each column contains atomic values The table contains repeating groups The table has no primary keyThe table is not indexed

First Normal Form (1NF) requires that each column in a table contains atomic (indivisible) values, meaning there are no repeating groups or arrays.

Q14

Single Choice

Consider the following Oracle SQL scenario:Which normal form does this table structure likely represent?

1NF 2NF 3NFBCNF

The table is in 1NF because each column contains atomic values, and there are no repeating groups. However, without analyzing functional dependencies, we cannot determine higher normal forms.

Q15

Single Choice

Which of the following statements about Second Normal Form (2NF) is true in the context of Oracle databases?

A table in 2NF may have partial dependencies. A table in 2NF must have a composite key. A table in 2NF must be in 1NF and have no partial dependencies.A table in 2NF must have a primary key.

Second Normal Form (2NF) requires that the table first be in 1NF and that there are no partial dependencies, meaning all non-key attributes must be fully dependent on the entire primary key.

Q16

Single Choice

Given the following Oracle SQL query:What normalization issue might this table have?

SQL Code

CREATE TABLE employees (
 employee_id NUMBER PRIMARY KEY,
 department_id NUMBER,
 salary NUMBER,
 department_name VARCHAR2(50)
);

It is not in 1NF because it has a primary key. It may have a partial dependency if department_name depends on department_id rather than employee_id. It is in BCNF because it has a primary key.It is not in any normal form because it has too many columns.

If department_name depends on department_id and not on the employee_id, the table may not be in 2NF, indicating a potential partial dependency.

Q17

Single Choice

In Oracle databases, what does Third Normal Form (3NF) specifically eliminate?

Redundant tables Partial dependencies Transitive dependenciesComposite keys

Third Normal Form (3NF) eliminates transitive dependencies, which occur when a non-key attribute depends on another non-key attribute

Q18

Single Choice

Consider the following Oracle SQL scenario:What normalization issue might this table have, assuming each course is associated with one department?

SQL Code

CREATE TABLE student_grades (
 student_id NUMBER,
 course_id NUMBER,
 grade CHAR(1),
 course_name VARCHAR2(50),
 department_id NUMBER,
 department_name VARCHAR2(50),
 PRIMARY KEY (student_id, course_id)
);

It has no normalization issues and is in 3NF. It likely has a transitive dependency because department_name depends on department_id rather than the primary key. It is not in 1NF because it has a composite key.It is in 2NF but not in 3NF because of partial dependencies.

The department_name attribute likely depends on department_id rather than on the primary key (student_id, course_id), indicating a transitive dependency, which violates 3NF.

Q19

Single Choice

Which Oracle SQL command would you use to normalize the student_grades table by removing the transitive dependency identified in the previous question?

CREATE TABLE departments AS SELECT DISTINCT department_id, department_name FROM student_grades; ALTER TABLE student_grades DROP COLUMN department_name; CREATE VIEW student_grades_view AS SELECT * FROM student_grades;CREATE INDEX idx_department ON student_grades(department_id);

Creating a new departments table with department_id and department_name removes the transitive dependency from the student_grades table, moving it to 3NF.

Q20

Single Choice

Consider the following Oracle SQL scenario:What steps would you take to normalize this table to 3NF?

SQL Code

CREATE TABLE employees (
 employee_id NUMBER PRIMARY KEY,
 first_name VARCHAR2(50),
 last_name VARCHAR2(50),
 manager_id NUMBER,
 hire_date DATE,
 department_id NUMBER,
 salary NUMBER,
 department_name VARCHAR2(50)
);

No action needed; it is already in 3NF. Remove department_name from the employees table and create a new departments table with department_id and department_name. Remove manager_id from the employees table.Remove salary from the employees table.

To normalize the table to 3NF, department_name should be removed and placed in a new departments table, eliminating any transitive dependency on department_id.

Q21

Multiple Choice

Which SQL code snippet demonstrates a table that satisfies First Normal Form (1NF) in Oracle?

SQL Code

CREATE TABLE customers (
customer_id NUMBER PRIMARY KEY,
customer_name VARCHAR2(100),
customer_phone VARCHAR2(15)
);

CREATE TABLE customers ( customer_id NUMBER PRIMARY KEY, customer_name VARCHAR2(100), customer_phone VARCHAR2(15) ); CREATE TABLE customers ( customer_id NUMBER PRIMARY KEY, customer_name VARCHAR2(100), customer_phone VARCHAR2(15) ); CREATE TABLE customers ( customer_id NUMBER PRIMARY KEY, customer_name VARCHAR2(100), customer_phone VARCHAR2(15) );CREATE TABLE customers ( customer_id NUMBER PRIMARY KEY, customer_name VARCHAR2(100), customer_phone1 VARCHAR2(15), customer_phone2 VARCHAR2(15) );

Options A, B, and C satisfy 1NF by ensuring that each column contains atomic values, and there are no repeating groups. Option D violates 1NF due to the presence of repeating groups.

Q22

Multiple Choice

Which SQL code snippet correctly removes partial dependencies to satisfy Second Normal Form (2NF)?

SQL Code

CREATE TABLE order_items (
order_id NUMBER,
product_id NUMBER,
quantity NUMBER,
PRIMARY KEY (order_id, product_id)
);

CREATE TABLE order_items ( order_id NUMBER, product_id NUMBER, quantity NUMBER, PRIMARY KEY (order_id, product_id) ); CREATE TABLE order_items ( order_id NUMBER, product_id NUMBER, quantity NUMBER, PRIMARY KEY (order_id, product_id) ); CREATE TABLE order_items ( order_id NUMBER, product_id NUMBER, quantity NUMBER, PRIMARY KEY (order_id, product_id) );CREATE TABLE order_items ( order_id NUMBER, product_id NUMBER, quantity NUMBER, product_name VARCHAR2(100), PRIMARY KEY (order_id, product_id) );

Options A, B, and C correctly satisfy 2NF by ensuring all non-key attributes are fully functionally dependent on the composite primary key. Option D violates 2NF by having a partial dependency on the primary key.

Q23

Multiple Choice

Which SQL code snippet eliminates transitive dependencies to achieve Third Normal Form (3NF)?

SQL Code

CREATE TABLE sales (
sale_id NUMBER PRIMARY KEY,
customer_id NUMBER,
sale_date DATE,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

CREATE TABLE sales ( sale_id NUMBER PRIMARY KEY, customer_id NUMBER, sale_date DATE, FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ); CREATE TABLE sales ( sale_id NUMBER PRIMARY KEY, customer_id NUMBER, sale_date DATE, FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ); CREATE TABLE sales ( sale_id NUMBER PRIMARY KEY, customer_id NUMBER, sale_date DATE, FOREIGN KEY (customer_id) REFERENCES customers(customer_id) );CREATE TABLE sales ( sale_id NUMBER PRIMARY KEY, customer_id NUMBER, customer_name VARCHAR2(100), sale_date DATE, FOREIGN KEY (customer_id) REFERENCES customers(customer_id) );

Options A, B, and C correctly satisfy 3NF by removing transitive dependencies, ensuring non-key attributes depend only on the primary key. Option D violates 3NF by including a transitive dependency.

Q24

Multiple Choice

Which SQL code snippet demonstrates a table design that complies with Boyce-Codd Normal Form (BCNF)?

SQL Code

CREATE TABLE employees (
employee_id NUMBER PRIMARY KEY,
department_id NUMBER,
job_title VARCHAR2(100),
FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

CREATE TABLE employees ( employee_id NUMBER PRIMARY KEY, department_id NUMBER, job_title VARCHAR2(100), FOREIGN KEY (department_id) REFERENCES departments(department_id) ); CREATE TABLE employees ( employee_id NUMBER PRIMARY KEY, department_id NUMBER, job_title VARCHAR2(100), FOREIGN KEY (department_id) REFERENCES departments(department_id) ); CREATE TABLE employees ( employee_id NUMBER PRIMARY KEY, department_id NUMBER, job_title VARCHAR2(100), FOREIGN KEY (department_id) REFERENCES departments(department_id) );CREATE TABLE employees ( employee_id NUMBER PRIMARY KEY, department_id NUMBER, manager_name VARCHAR2(100), job_title VARCHAR2(100), FOREIGN KEY (department_id) REFERENCES departments(department_id) );

Options A, B, and C comply with BCNF by ensuring that every determinant is a candidate key, removing any partial and transitive dependencies. Option D violates BCNF by having a non-candidate key determinant.

Q25

Multiple Choice

Which SQL code snippet is most likely to require further normalization to achieve Fourth Normal Form (4NF)?

SQL Code

CREATE TABLE student_courses (
student_id NUMBER,
course_id NUMBER,
instructor_id NUMBER,
PRIMARY KEY (student_id, course_id, instructor_id)
);

CREATE TABLE student_courses ( student_id NUMBER, course_id NUMBER, instructor_id NUMBER, PRIMARY KEY (student_id, course_id, instructor_id) ); CREATE TABLE student_courses ( student_id NUMBER, course_id NUMBER, instructor_id NUMBER, PRIMARY KEY (student_id, course_id, instructor_id) ); CREATE TABLE student_courses ( student_id NUMBER, course_id NUMBER, instructor_id NUMBER, PRIMARY KEY (student_id, course_id, instructor_id) );CREATE TABLE student_courses ( student_id NUMBER, course_id NUMBER, PRIMARY KEY (student_id, course_id) );

Options A, B, and C demonstrate tables that may require further normalization to remove multi-valued dependencies, ensuring 4NF compliance. Option D is correctly normalized to 4NF.

Q26

Multiple Choice

Which SQL code snippet demonstrates a table that correctly meets the requirements of Third Normal Form (3NF) by eliminating all transitive dependencies?

SQL Code

CREATE TABLE orders (
order_id NUMBER PRIMARY KEY,
customer_id NUMBER,
order_date DATE,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

CREATE TABLE orders ( order_id NUMBER PRIMARY KEY, customer_id NUMBER, order_date DATE, FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ); CREATE TABLE orders ( order_id NUMBER PRIMARY KEY, customer_id NUMBER, order_date DATE, FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ); CREATE TABLE orders ( order_id NUMBER PRIMARY KEY, customer_id NUMBER, order_date DATE, FOREIGN KEY (customer_id) REFERENCES customers(customer_id) );CREATE TABLE orders ( order_id NUMBER PRIMARY KEY, customer_id NUMBER, customer_name VARCHAR2(100), order_date DATE, FOREIGN KEY (customer_id) REFERENCES customers(customer_id) );

Options A, B, and C correctly demonstrate 3NF compliance by ensuring that all non-key attributes depend only on the primary key, without any transitive dependencies. Option D violates 3NF due to a transitive dependency.

Q27

Multiple Choice

Which SQL code snippet demonstrates the normalization of a table from Second Normal Form (2NF) to Third Normal Form (3NF)?

SQL Code

CREATE TABLE order_details (
order_id NUMBER,
product_id NUMBER,
product_name VARCHAR2(100),
quantity NUMBER,
PRIMARY KEY (order_id, product_id)
);

CREATE TABLE order_details ( order_id NUMBER, product_id NUMBER, product_name VARCHAR2(100), quantity NUMBER, PRIMARY KEY (order_id, product_id) ); CREATE TABLE order_details ( order_id NUMBER, product_id NUMBER, product_name VARCHAR2(100), quantity NUMBER, PRIMARY KEY (order_id, product_id) ); CREATE TABLE order_details ( order_id NUMBER, product_id NUMBER, product_name VARCHAR2(100), quantity NUMBER, PRIMARY KEY (order_id, product_id) );CREATE TABLE order_details ( order_id NUMBER, product_id NUMBER, quantity NUMBER, PRIMARY KEY (order_id, product_id) );

Options A, B, and C demonstrate normalizing the table to 3NF by removing the transitive dependency on `product_name`. Option D violates 3NF by retaining the transitive dependency.

Q28

Multiple Choice

Which SQL code snippet demonstrates a table that adheres to Fourth Normal Form (4NF) by eliminating multi-valued dependencies?

SQL Code

CREATE TABLE projects (
project_id NUMBER PRIMARY KEY,
project_name VARCHAR2(100),
manager_id NUMBER,
FOREIGN KEY (manager_id) REFERENCES managers(manager_id)
);

CREATE TABLE projects ( project_id NUMBER PRIMARY KEY, project_name VARCHAR2(100), manager_id NUMBER, FOREIGN KEY (manager_id) REFERENCES managers(manager_id) ); CREATE TABLE projects ( project_id NUMBER PRIMARY KEY, project_name VARCHAR2(100), manager_id NUMBER, FOREIGN KEY (manager_id) REFERENCES managers(manager_id) ); CREATE TABLE projects ( project_id NUMBER PRIMARY KEY, project_name VARCHAR2(100), manager_id NUMBER, FOREIGN KEY (manager_id) REFERENCES managers(manager_id) );CREATE TABLE projects ( project_id NUMBER PRIMARY KEY, project_name VARCHAR2(100), manager_id NUMBER, manager_name VARCHAR2(100), FOREIGN KEY (manager_id) REFERENCES managers(manager_id) );

Options A, B, and C demonstrate 4NF compliance by eliminating multi-valued dependencies. Option D violates 4NF by introducing multi-valued dependencies.

Q29

Multiple Choice

Which SQL code snippet shows a table design normalized to Fifth Normal Form (5NF) by decomposing complex relationships?

SQL Code

CREATE TABLE contracts (
contract_id NUMBER PRIMARY KEY,
client_id NUMBER,
service_id NUMBER,
FOREIGN KEY (client_id) REFERENCES clients(client_id),
FOREIGN KEY (service_id) REFERENCES services(service_id)
);

CREATE TABLE contracts ( contract_id NUMBER PRIMARY KEY, client_id NUMBER, service_id NUMBER, FOREIGN KEY (client_id) REFERENCES clients(client_id), FOREIGN KEY (service_id) REFERENCES services(service_id) ); CREATE TABLE contracts ( contract_id NUMBER PRIMARY KEY, client_id NUMBER, service_id NUMBER, FOREIGN KEY (client_id) REFERENCES clients(client_id), FOREIGN KEY (service_id) REFERENCES services(service_id) ); CREATE TABLE contracts ( contract_id NUMBER PRIMARY KEY, client_id NUMBER, service_id NUMBER, FOREIGN KEY (client_id) REFERENCES clients(client_id), FOREIGN KEY (service_id) REFERENCES services(service_id) );CREATE TABLE contracts ( contract_id NUMBER PRIMARY KEY, client_id NUMBER, service_id NUMBER, contract_date DATE, FOREIGN KEY (client_id) REFERENCES clients(client_id), FOREIGN KEY (service_id) REFERENCES services(service_id) );

Options A, B, and C demonstrate 5NF compliance by decomposing the table to eliminate redundancy in complex relationships. Option D does not fully decompose the relationships, violating 5NF.

Q30

Multiple Choice

Which SQL code snippet represents a certification-level scenario involving the normalization of a table to Third Normal Form (3NF)?

SQL Code

CREATE TABLE inventory (
product_id NUMBER PRIMARY KEY,
warehouse_id NUMBER,
quantity NUMBER,
FOREIGN KEY (warehouse_id) REFERENCES warehouses(warehouse_id)
);

CREATE TABLE inventory ( product_id NUMBER PRIMARY KEY, warehouse_id NUMBER, quantity NUMBER, FOREIGN KEY (warehouse_id) REFERENCES warehouses(warehouse_id) ); CREATE TABLE inventory ( product_id NUMBER PRIMARY KEY, warehouse_id NUMBER, quantity NUMBER, FOREIGN KEY (warehouse_id) REFERENCES warehouses(warehouse_id) ); CREATE TABLE inventory ( product_id NUMBER PRIMARY KEY, warehouse_id NUMBER, quantity NUMBER, FOREIGN KEY (warehouse_id) REFERENCES warehouses(warehouse_id) );CREATE TABLE inventory ( product_id NUMBER PRIMARY KEY, warehouse_id NUMBER, quantity NUMBER, warehouse_location VARCHAR2(100), FOREIGN KEY (warehouse_id) REFERENCES warehouses(warehouse_id) );

Options A, B, and C demonstrate correct 3NF normalization by ensuring that all non-key attributes depend only on the primary key. Option D violates 3NF by including a transitive dependency.

Oracle Database Quiz Questions