Test Your Oracle Third Normal Form Knowledge with This Quiz

Progress

True / False

In Oracle, a table must be in Second Normal Form (2NF) before it can be considered in Third Normal Form (3NF).

True False

A table must first meet all the requirements of 2NF before it can be evaluated for 3NF compliance.

True / False

In Oracle, a table is in Third Normal Form (3NF) if it has no transitive dependencies.

True False

3NF requires that there are no transitive dependencies, meaning non-key attributes cannot depend on other non-key attributes.

True / False

In Oracle, a table in Third Normal Form (3NF) can still have partial dependencies.

True False

To be in 3NF, a table must already be in 2NF, which means it cannot have any partial dependencies.

True / False

In Oracle, a transitive dependency occurs when a non-key attribute depends on another non-key attribute.

True False

A transitive dependency exists when one non-key attribute depends on another non-key attribute instead of depending directly on the primary key.

True / False

In Oracle, normalizing a table to Third Normal Form (3NF) helps reduce data redundancy and update anomalies.

True False

3NF reduces data redundancy and update anomalies by ensuring that all non-key attributes depend only on the primary key.

True / False

In Oracle, to achieve Third Normal Form (3NF), it may be necessary to create additional tables and establish foreign key relationships.

True False

Achieving 3NF often involves decomposing tables to eliminate transitive dependencies and establishing foreign key relationships to maintain data integrity.

True / False

In Oracle, a table in Third Normal Form (3NF) will always have fewer columns than a table in First Normal Form (1NF).

True False

While normalizing to 3NF often involves decomposing tables, it does not necessarily mean the resulting tables will have fewer columns. The focus is on reducing redundancy and dependencies, not necessarily reducing the number of columns.

True / False

In Oracle, achieving Third Normal Form (3NF) can sometimes lead to performance trade-offs due to the need for additional joins.

True False

Normalizing to 3NF can lead to performance trade-offs, as the need for additional joins to retrieve data from decomposed tables can impact query performance.

True / False

In Oracle, all tables in Third Normal Form (3NF) are also in Boyce-Codd Normal Form (BCNF).

True False

While all tables in BCNF are in 3NF, not all tables in 3NF are necessarily in BCNF. BCNF is a stricter form of 3NF that deals with certain types of anomalies not addressed by 3NF.

Q10

True / False

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

True False

The OCP certification covers advanced topics, including the principles of normalization such as 3NF, to ensure candidates can design efficient and normalized database schemas.

Q11

Multiple Choice

Which SQL code snippet demonstrates creating a table that satisfies Third Normal Form (3NF) in Oracle?

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 );

Options A, B, and C demonstrate creating a table in 3NF by ensuring that every non-key attribute is fully functionally dependent on the primary key. Option D is incorrect because it contains a transitive dependency.

Q12

Multiple Choice

Which SQL code snippet violates the Third Normal Form (3NF) by including a transitive dependency?

SQL Code

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

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

Options A, B, and C violate 3NF because the `department_name` attribute is transitively dependent on the primary key `employee_id` through the `department_id`. Option D is correct because it removes the transitive dependency.

Q13

Multiple Choice

Which SQL code snippet correctly normalizes a table to Third Normal Form (3NF) by eliminating partial dependencies?

SQL Code

CREATE TABLE order_details (
order_id NUMBER,
product_id NUMBER,
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) ); CREATE TABLE order_details ( order_id NUMBER, product_id NUMBER, 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) );CREATE TABLE order_details ( order_id NUMBER, product_id NUMBER, quantity NUMBER, product_name VARCHAR2(100), PRIMARY KEY (order_id, product_id) );

Options A, B, and C correctly normalize the table by ensuring all non-key attributes are fully dependent on the entire composite primary key. Option D is incorrect because it still contains a partial dependency.

Q14

Multiple Choice

Which SQL code snippet demonstrates an advanced scenario ensuring a table is in Third Normal Form (3NF) by eliminating transitive dependencies?

SQL Code

CREATE TABLE sales (
sale_id NUMBER PRIMARY KEY,
product_id NUMBER,
sale_date DATE,
FOREIGN KEY (product_id) REFERENCES products(product_id)
);

CREATE TABLE sales ( sale_id NUMBER PRIMARY KEY, product_id NUMBER, sale_date DATE, FOREIGN KEY (product_id) REFERENCES products(product_id) ); CREATE TABLE sales ( sale_id NUMBER PRIMARY KEY, product_id NUMBER, sale_date DATE, FOREIGN KEY (product_id) REFERENCES products(product_id) ); CREATE TABLE sales ( sale_id NUMBER PRIMARY KEY, product_id NUMBER, sale_date DATE, FOREIGN KEY (product_id) REFERENCES products(product_id) );CREATE TABLE sales ( sale_id NUMBER PRIMARY KEY, product_id NUMBER, product_name VARCHAR2(100), sale_date DATE, FOREIGN KEY (product_id) REFERENCES products(product_id) );

Options A, B, and C demonstrate eliminating transitive dependencies by ensuring that `sale_date` is directly related to the primary key `sale_id`. Option D is incorrect because it introduces a transitive dependency.

Q15

Multiple Choice

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

SQL Code

CREATE TABLE student_courses (
student_id NUMBER,
course_id NUMBER,
student_name VARCHAR2(100),
course_name VARCHAR2(100),
PRIMARY KEY (student_id, course_id)
);

CREATE TABLE student_courses ( student_id NUMBER, course_id NUMBER, student_name VARCHAR2(100), course_name VARCHAR2(100), PRIMARY KEY (student_id, course_id) ); CREATE TABLE student_courses ( student_id NUMBER, course_id NUMBER, student_name VARCHAR2(100), course_name VARCHAR2(100), PRIMARY KEY (student_id, course_id) ); CREATE TABLE student_courses ( student_id NUMBER, course_id NUMBER, student_name VARCHAR2(100), course_name VARCHAR2(100), PRIMARY KEY (student_id, course_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 need further normalization because `student_name` and `course_name` introduce transitive dependencies. Option D is correctly normalized to 3NF.

Q16

Multiple Choice

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

SQL Code

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

CREATE TABLE customer_orders ( order_id NUMBER PRIMARY KEY, customer_id NUMBER, order_date DATE, FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ); CREATE TABLE customer_orders ( order_id NUMBER PRIMARY KEY, customer_id NUMBER, order_date DATE, FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ); CREATE TABLE customer_orders ( order_id NUMBER PRIMARY KEY, customer_id NUMBER, order_date DATE, FOREIGN KEY (customer_id) REFERENCES customers(customer_id) );CREATE TABLE customer_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 every non-key attribute is fully functionally dependent on the primary key without any transitive dependencies. Option D is incorrect because it includes a transitive dependency.

Q17

Multiple Choice

Which SQL code snippet correctly removes a transitive dependency to ensure Third Normal Form (3NF)?

SQL Code

CREATE TABLE invoices (
invoice_id NUMBER PRIMARY KEY,
customer_id NUMBER,
invoice_date DATE,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

CREATE TABLE invoices ( invoice_id NUMBER PRIMARY KEY, customer_id NUMBER, invoice_date DATE, FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ); CREATE TABLE invoices ( invoice_id NUMBER PRIMARY KEY, customer_id NUMBER, invoice_date DATE, FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ); CREATE TABLE invoices ( invoice_id NUMBER PRIMARY KEY, customer_id NUMBER, invoice_date DATE, FOREIGN KEY (customer_id) REFERENCES customers(customer_id) );CREATE TABLE invoices ( invoice_id NUMBER PRIMARY KEY, customer_id NUMBER, customer_name VARCHAR2(100), invoice_date DATE, FOREIGN KEY (customer_id) REFERENCES customers(customer_id) );

Options A, B, and C correctly remove transitive dependencies by ensuring that `invoice_date` is directly related to the primary key `invoice_id`. Option D is incorrect because it introduces a transitive dependency.

Q18

Multiple Choice

Which SQL code snippet demonstrates splitting a table to meet Third Normal Form (3NF) requirements?

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_name VARCHAR2(100), manager_id NUMBER, FOREIGN KEY (manager_id) REFERENCES managers(manager_id) );

Options A, B, and C demonstrate correctly splitting tables to remove transitive dependencies, ensuring the table is in 3NF. Option D is incorrect because it introduces a transitive dependency.

Q19

Multiple Choice

Which SQL code snippet would be considered properly normalized to Third Normal Form (3NF)?

SQL Code

CREATE TABLE departments (
department_id NUMBER PRIMARY KEY,
department_name VARCHAR2(100),
manager_id NUMBER,
FOREIGN KEY (manager_id) REFERENCES managers(manager_id)
);

CREATE TABLE departments ( department_id NUMBER PRIMARY KEY, department_name VARCHAR2(100), manager_id NUMBER, FOREIGN KEY (manager_id) REFERENCES managers(manager_id) ); CREATE TABLE departments ( department_id NUMBER PRIMARY KEY, department_name VARCHAR2(100), manager_id NUMBER, FOREIGN KEY (manager_id) REFERENCES managers(manager_id) ); CREATE TABLE departments ( department_id NUMBER PRIMARY KEY, department_name VARCHAR2(100), manager_id NUMBER, FOREIGN KEY (manager_id) REFERENCES managers(manager_id) );CREATE TABLE departments ( department_id NUMBER PRIMARY KEY, department_name VARCHAR2(100), manager_name VARCHAR2(100), manager_id NUMBER, FOREIGN KEY (manager_id) REFERENCES managers(manager_id) );

Options A, B, and C are correctly normalized to 3NF by ensuring all non-key attributes are fully dependent on the primary key. Option D is incorrect because it introduces a transitive dependency.

Q20

Multiple Choice

Which SQL code snippet demonstrates a certification-level scenario where Third Normal Form (3NF) is enforced?

SQL Code

CREATE TABLE employees (
employee_id NUMBER PRIMARY KEY,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
department_id NUMBER,
FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

CREATE TABLE employees ( employee_id NUMBER PRIMARY KEY, first_name VARCHAR2(50), last_name VARCHAR2(50), department_id NUMBER, FOREIGN KEY (department_id) REFERENCES departments(department_id) ); CREATE TABLE employees ( employee_id NUMBER PRIMARY KEY, first_name VARCHAR2(50), last_name VARCHAR2(50), department_id NUMBER, FOREIGN KEY (department_id) REFERENCES departments(department_id) ); CREATE TABLE employees ( employee_id NUMBER PRIMARY KEY, first_name VARCHAR2(50), last_name VARCHAR2(50), department_id NUMBER, FOREIGN KEY (department_id) REFERENCES departments(department_id) );CREATE TABLE employees ( employee_id NUMBER PRIMARY KEY, first_name VARCHAR2(50), last_name VARCHAR2(50), department_name VARCHAR2(100), department_id NUMBER, FOREIGN KEY (department_id) REFERENCES departments(department_id) );

Options A, B, and C correctly demonstrate enforcing 3NF by ensuring no transitive dependencies exist. Option D is incorrect because it introduces a transitive dependency.

Q21

Single Choice

Which of the following correctly describes Third Normal Form (3NF) in Oracle databases?

A table is in 3NF if it is in 2NF and has no transitive dependencies. A table is in 3NF if it has a primary key. A table is in 3NF if it has no repeating groups.A table is in 3NF if it has no composite keys.

Third Normal Form requires that the table be in Second Normal Form (2NF) and that there are no transitive dependencies, meaning no non-key attribute depends on another non-key attribute.

Q22

Single Choice

Consider the following Oracle table:Why is this table not in Third Normal Form (3NF)?

SQL Code

CREATE TABLE employee (
 emp_id NUMBER,
 emp_name VARCHAR2(50),
 dept_id NUMBER,
 dept_name VARCHAR2(50),
 dept_location VARCHAR2(50),
 PRIMARY KEY (emp_id)
);

emp_name is dependent on dept_id. dept_name is dependent on emp_id. dept_location is dependent on dept_name.emp_id is not a composite key.

This table is not in 3NF because dept_location is transitively dependent on dept_name, which is not a key attribute. This violates 3NF as non-key attributes should not depend on other non-key attributes.

Q23

Single Choice

In Oracle, what action is necessary to bring a table from Second Normal Form (2NF) to Third Normal Form (3NF)?

Remove composite keys. Eliminate transitive dependencies. Ensure all columns are non-nullable.Add a surrogate key.

To achieve 3NF, any transitive dependencies (where a non-key attribute depends on another non-key attribute) must be eliminated.

Q24

Single Choice

Given the following Oracle table structure:Which of the following changes would normalize this table to Third Normal Form (3NF)?

SQL Code

CREATE TABLE orders (
 order_id NUMBER,
 customer_id NUMBER,
 customer_name VARCHAR2(50),
 order_date DATE,
 customer_address VARCHAR2(100),
 PRIMARY KEY (order_id)
);

Remove customer_name and customer_address from the orders table. Create a separate customers table with customer_id as the primary key. Combine customer_id and order_date into a composite key.Remove order_date from the table.

To achieve 3NF, you should move customer_name and customer_address to a separate customers table since they are dependent on customer_id, not on the primary key order_id.

Q25

Single Choice

Which of the following SQL scripts creates an Oracle table that adheres to Third Normal Form (3NF)?

CREATE TABLE employee_details ( emp_id NUMBER, emp_name VARCHAR2(50), dept_id NUMBER, dept_name VARCHAR2(50), PRIMARY KEY (emp_id) ); CREATE TABLE department ( dept_id NUMBER, dept_name VARCHAR2(50), dept_location VARCHAR2(50), PRIMARY KEY (dept_id) ); CREATE TABLE employee_details ( emp_id NUMBER, emp_name VARCHAR2(50), dept_id NUMBER, dept_location VARCHAR2(50), PRIMARY KEY (emp_id) );CREATE TABLE employee_details ( emp_id NUMBER, emp_name VARCHAR2(50), dept_id NUMBER, PRIMARY KEY (emp_id) );

This table structure adheres to 3NF because it does not have any transitive dependencies. dept_id can be linked to a department table separately.

Q26

Single Choice

Analyze the following Oracle SQL code:What modification is needed for this table to comply with Third Normal Form (3NF)?

SQL Code

CREATE TABLE employee_projects (
 emp_id NUMBER,
 proj_id NUMBER,
 proj_name VARCHAR2(50),
 proj_budget NUMBER,
 PRIMARY KEY (emp_id, proj_id)
);

Remove proj_name and proj_budget from employee_projects. Add a foreign key constraint on emp_id. Move proj_name and proj_budget to a separate projects table.Combine proj_id and proj_budget into a composite key.

proj_name and proj_budget depend on proj_id rather than the entire composite key. To achieve 3NF, these should be placed in a projects table.

Q27

Single Choice

You have the following Oracle table definition:How would you restructure this table to bring it into Third Normal Form (3NF)?

SQL Code

CREATE TABLE sales_data (
 sales_id NUMBER,
 product_id NUMBER,
 product_name VARCHAR2(100),
 category_name VARCHAR2(50),
 sales_amount NUMBER,
 PRIMARY KEY (sales_id)
);

Split the table into two: sales_data and product_data. Remove product_name and keep only category_name. Add category_id and make it part of the primary key.Combine sales_id and product_id into a composite key.

To achieve 3NF, you would move product_name and category_name into a product_data table since they are dependent on product_id and not directly on sales_id.

Q28

Single Choice

Consider the following Oracle table:What must be done to ensure this table is in Third Normal Form (3NF)?

SQL Code

CREATE TABLE student_enrollments (
 student_id NUMBER,
 course_id NUMBER,
 instructor_id NUMBER,
 course_name VARCHAR2(50),
 instructor_name VARCHAR2(50),
 PRIMARY KEY (student_id, course_id)
);

Remove instructor_id from the table. Create separate tables for courses and instructors. Add student_name to the primary key.No action is needed as the table is already in 3NF.

The attributes course_name and instructor_name are transitively dependent on course_id and instructor_id, respectively, rather than the composite primary key. To achieve 3NF, you should create separate tables for courses and instructors.

Q29

Single Choice

Examine the Oracle table below:Which action would normalize this table to Third Normal Form (3NF)?

SQL Code

CREATE TABLE purchase_orders (
 order_id NUMBER,
 product_id NUMBER,
 supplier_id NUMBER,
 supplier_name VARCHAR2(100),
 product_name VARCHAR2(100),
 order_date DATE,
 PRIMARY KEY (order_id)
);

create separate suppliers and products tables. Remove supplier_id and product_id. Make supplier_name and product_name part of the primary key.Merge order_id and supplier_id into a composite key.

To achieve 3NF, you should move supplier_name and product_name to separate tables (suppliers and products) because these attributes are transitively dependent on supplier_id and product_id, not on order_id.

Q30

Single Choice

In Oracle, you have the following table:To bring this table into Third Normal Form (3NF), what should you do?

SQL Code

CREATE TABLE department (
 dept_id NUMBER,
 dept_name VARCHAR2(50),
 manager_id NUMBER,
 manager_name VARCHAR2(50),
 PRIMARY KEY (dept_id)
);

Create a separate managers table and reference manager_id. Add manager_name to the primary key. Remove manager_id from the table.Make dept_name a composite key.

manager_name is transitively dependent on manager_id, not on dept_id. To normalize this table to 3NF, manager_name should be moved to a separate managers table, where manager_id is the primary key.

Oracle Database Quiz Questions