Test Your Oracle Second Normal Form Knowledge with This Quiz

Progress

True / False

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

True False

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

True / False

In Oracle, a table is in Second Normal Form (2NF) if it does not have any partial dependencies.

True False

2NF requires that there are no partial dependencies, meaning no non-key attribute depends only on part of a composite primary key.

True / False

In Oracle, a table with a single-column primary key is automatically in Second Normal Form (2NF) if it is already in First Normal Form (1NF).

True False

If a table is in 1NF and has a single-column primary key, it is automatically in 2NF because there can be no partial dependencies with a single-column key.

True / False

In Oracle, a table with a composite primary key is in Second Normal Form (2NF) if all non-key columns are fully dependent on the entire primary key.

True False

A table with a composite primary key must have all non-key columns fully dependent on the entire key for it to be in 2NF.

True / False

In Oracle, to achieve Second Normal Form (2NF), it may be necessary to decompose a table into multiple tables.

True False

To eliminate partial dependencies, a table may need to be decomposed into multiple tables, each with its own primary key.

True / False

In Oracle, normalization to Second Normal Form (2NF) can help reduce data redundancy.

True False

Normalizing to 2NF reduces data redundancy by ensuring that non-key attributes are only dependent on the whole primary key, not just part of it.

True / False

In Oracle, a table that is in Second Normal Form (2NF) can still have transitive dependencies.

True False

A table in 2NF can still have transitive dependencies, where non-key attributes depend on other non-key attributes. These are addressed in Third Normal Form (3NF).

True / False

In Oracle, a table that includes a foreign key must also ensure that all non-key attributes are fully dependent on the entire primary key to be in Second Normal Form (2NF).

True False

Even if a table has foreign keys, it must ensure that all non-key attributes are fully dependent on the entire primary key to meet 2NF requirements.

True / False

In Oracle, converting a table to Second Normal Form (2NF) can involve creating additional tables and establishing foreign key relationships between them.

True False

Converting to 2NF often involves decomposing tables and creating new tables with foreign key relationships to eliminate partial dependencies.

Q10

True / False

The Oracle Certified Professional (OCP) exam includes knowledge on the principles of normalization, including Second Normal Form (2NF), 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 2NF, to ensure candidates can design efficient and normalized database schemas.

Q11

Multiple Choice

Which of the following SQL tables adheres to the Second Normal Form (2NF) in Microsoft SQL Server?

SQL Code

Consider the following table definition:
CREATE TABLE Employees (
 EmployeeID INT,
 EmployeeName VARCHAR(100),
 DepartmentID INT,
 DepartmentName VARCHAR(100)
);
Which of these options ensure the table meets 2NF?

Separate the Department details into a new table with DepartmentID as the primary key. Add a composite primary key on EmployeeID and DepartmentID. Create a unique constraint on EmployeeName.Leave the table as it is.

For a table to be in 2NF, it must first be in 1NF and all non-key attributes must be fully functionally dependent on the primary key, removing partial dependencies. Option A is correct as it ensures that EmployeeName and DepartmentID depend on EmployeeID, and DepartmentName depends on DepartmentID.

Q12

Multiple Choice

Which modification would violate Second Normal Form (2NF) in the following table?

SQL Code

CREATE TABLE Orders (
 OrderID INT,
 ProductID INT,
 OrderDate DATE,
 ProductName VARCHAR(100),
 PRIMARY KEY (OrderID, ProductID)
);

Add a column for ProductCategory in the Orders table. Move ProductName to a separate Product table linked by ProductID. Create an index on OrderDate.Add a foreign key constraint between OrderID and another table.

Adding a non-key attribute (like ProductName) that does not fully depend on the whole primary key (OrderID, ProductID) violates 2NF. Instead, ProductName should be moved to a separate table with ProductID as the key.

Q13

Multiple Choice

Which SQL table design does not adhere to Second Normal Form (2NF)?

SQL Code

CREATE TABLE Enrollment (
 StudentID INT,
 CourseID INT,
 EnrollmentDate DATE,
 StudentName VARCHAR(100),
 CourseName VARCHAR(100)
);

Split the table into two: Students and Courses, with foreign keys linking them to Enrollment. Add a composite primary key on StudentID and CourseID. Move StudentName and CourseName to separate tables.Leave the table as it is.

For the table to be in 2NF, it should be split into two tables: one for Student information and another for Course information. This removes the partial dependencies on the composite key (StudentID, CourseID).

Q14

Multiple Choice

Fill in the blank: To achieve Second Normal Form (2NF), which SQL operation would you perform on the following table?

SQL Code

CREATE TABLE BookOrders (
 OrderID INT,
 BookID INT,
 OrderDate DATE,
 BookTitle VARCHAR(100),
 PRIMARY KEY (OrderID, BookID)
);

Move Keep RemoveAdd

In 2NF, attributes that do not fully depend on the composite primary key should be moved to a different table. The correct operation is to move BookTitle to a new table with BookID as the primary key.

Q15

Multiple Choice

Given the table below, which modification will ensure the table is in Second Normal Form (2NF)?

SQL Code

CREATE TABLE Sales (
 SaleID INT,
 SaleDate DATE,
 CustomerID INT,
 CustomerName VARCHAR(100),
 ProductID INT,
 ProductName VARCHAR(100)
);

Move CustomerName and ProductName to separate Customer and Product tables. Create a composite primary key on SaleID and CustomerID. Add an index on SaleDate.Leave the table structure as is.

To satisfy 2NF, CustomerName and ProductName should be moved to separate tables (Customer and Product) to eliminate partial dependencies. This will ensure all non-key attributes fully depend on the whole primary key.

Q16

Multiple Choice

Which SQL operation will move the following table to Second Normal Form (2NF)?

SQL Code

CREATE TABLE Invoices (
 InvoiceID INT,
 CustomerID INT,
 InvoiceDate DATE,
 CustomerName VARCHAR(100),
 CustomerAddress VARCHAR(255)
);

Move CustomerName and CustomerAddress to a new Customer table. Add a composite primary key on InvoiceID and CustomerID. Create a unique constraint on CustomerName.Leave the table as is.

To comply with 2NF, CustomerName and CustomerAddress should be moved to a separate Customer table, with CustomerID as the foreign key.

Q17

Multiple Choice

Which SQL query would you use to verify that a table adheres to Second Normal Form (2NF)?

SQL Code

Assume you have the following table:
CREATE TABLE Transactions (
 TransactionID INT,
 ProductID INT,
 TransactionDate DATE,
 ProductName VARCHAR(100)
);

SELECT * FROM Transactions WHERE ProductID IS NULL; SELECT COUNT(*) FROM Transactions WHERE ProductName IS NULL; SELECT DISTINCT ProductName FROM Transactions;Check if ProductName is fully dependent on ProductID.

To verify 2NF, you would ensure that all non-key attributes (like ProductName) are fully functionally dependent on the whole primary key (TransactionID, ProductID) and not just a part of it.

Q18

Multiple Choice

Which SQL code snippet demonstrates a table that violates the Second Normal Form (2NF) due to partial dependency?

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 correctly demonstrate a table that violates 2NF because `product_name` is dependent only on `product_id`, not on the entire composite key. Option D is incorrect because it does not include `product_name` and thus does not violate 2NF.

Q19

Multiple Choice

Which SQL code snippet demonstrates a table correctly normalized to the Second Normal Form (2NF)?

SQL Code

CREATE TABLE orders (
order_id NUMBER PRIMARY KEY,
order_date DATE
);

CREATE TABLE products (
product_id NUMBER PRIMARY KEY,
product_name VARCHAR2(100)
);

CREATE TABLE order_details (
order_id NUMBER,
product_id NUMBER,
quantity NUMBER,
PRIMARY KEY (order_id, product_id),
FOREIGN KEY (order_id) REFERENCES orders(order_id),
FOREIGN KEY (product_id) REFERENCES products(product_id)
);

CREATE TABLE orders ( order_id NUMBER PRIMARY KEY, order_date DATE ); CREATE TABLE products ( product_id NUMBER PRIMARY KEY, product_name VARCHAR2(100) ); CREATE TABLE order_details ( order_id NUMBER, product_id NUMBER, quantity NUMBER, PRIMARY KEY (order_id, product_id), FOREIGN KEY (order_id) REFERENCES orders(order_id), FOREIGN KEY (product_id) REFERENCES products(product_id) ); CREATE TABLE orders ( order_id NUMBER PRIMARY KEY, order_date DATE ); CREATE TABLE products ( product_id NUMBER PRIMARY KEY, product_name VARCHAR2(100) ); CREATE TABLE order_details ( order_id NUMBER, product_id NUMBER, quantity NUMBER, PRIMARY KEY (order_id, product_id), FOREIGN KEY (order_id) REFERENCES orders(order_id), FOREIGN KEY (product_id) REFERENCES products(product_id) ); CREATE TABLE orders ( order_id NUMBER PRIMARY KEY, order_date DATE ); CREATE TABLE products ( product_id NUMBER PRIMARY KEY, product_name VARCHAR2(100) ); CREATE TABLE order_details ( order_id NUMBER, product_id NUMBER, quantity NUMBER, PRIMARY KEY (order_id, product_id), FOREIGN KEY (order_id) REFERENCES orders(order_id), FOREIGN KEY (product_id) REFERENCES products(product_id) );CREATE TABLE orders ( order_id NUMBER PRIMARY KEY, order_date DATE ); CREATE TABLE products ( product_id NUMBER PRIMARY KEY, product_name VARCHAR2(100) ); CREATE TABLE order_details ( order_id NUMBER, product_id NUMBER, quantity NUMBER, PRIMARY KEY (order_id, product_id) );

Options A, B, and C correctly demonstrate a table structure normalized to 2NF, where non-key attributes are dependent on the entire primary key. Option D is incorrect because it lacks the foreign key references necessary for 2NF.

Q20

Multiple Choice

Which SQL code snippet correctly identifies a partial dependency that should be removed to achieve Second Normal Form (2NF)?

SQL Code

CREATE TABLE project_assignments (
project_id NUMBER,
employee_id NUMBER,
project_name VARCHAR2(100),
start_date DATE,
PRIMARY KEY (project_id, employee_id)
);

CREATE TABLE project_assignments ( project_id NUMBER, employee_id NUMBER, project_name VARCHAR2(100), start_date DATE, PRIMARY KEY (project_id, employee_id) ); CREATE TABLE project_assignments ( project_id NUMBER, employee_id NUMBER, project_name VARCHAR2(100), start_date DATE, PRIMARY KEY (project_id, employee_id) ); CREATE TABLE project_assignments ( project_id NUMBER, employee_id NUMBER, project_name VARCHAR2(100), start_date DATE, PRIMARY KEY (project_id, employee_id) );CREATE TABLE project_assignments ( project_id NUMBER, employee_id NUMBER, start_date DATE, PRIMARY KEY (project_id, employee_id) );

Options A, B, and C correctly demonstrate a partial dependency where `project_name` depends only on `project_id`, which should be removed to achieve 2NF. Option D is incorrect because it does not include the partial dependency.

Q21

Single Choice

Which of the following statements correctly defines Second Normal Form (2NF) in Oracle databases?

Every non-key attribute is fully functionally dependent on the primary key. There are no transitive dependencies. Every table must have a primary key.A table must be in First Normal Form.

Second Normal Form (2NF) requires that all non-key attributes be fully functionally dependent on the entire primary key, which means partial dependencies (where an attribute is dependent on part of the primary key) should be eliminated.

Q22

Single Choice

Which of the following Oracle SQL operations might violate Second Normal Form?

SELECT * FROM employees WHERE department_id = 10; INSERT INTO sales (order_id, product_id, customer_name) VALUES (1, 101, 'John Doe'); UPDATE employees SET job_title = 'Manager' WHERE employee_id = 123;INSERT INTO orders (order_id, customer_id, order_date) VALUES (1001, 203, '2024-08-20');

The INSERT INTO sales operation might violate 2NF if customer_name is not fully dependent on the composite key of order_id and product_id, indicating a partial dependency.

Q23

Single Choice

In Oracle, which of the following is a sign that a table is not in Second Normal Form?

The table has a composite primary key. The table has no primary key. A non-key column is dependent on only part of a composite primary key.The table has no foreign key.

Second Normal Form requires that non-key columns be fully dependent on the entire primary key. If a non-key column is dependent on only part of a composite primary key, it violates 2NF.

Q24

Single Choice

Consider the following Oracle table definition:Which normalization issue does this table have that prevents it from being in Second Normal Form?

SQL Code

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

order_date is not fully dependent on the primary key. customer_name is not fully dependent on the primary key. order_id should not be part of the primary key.customer_id should be a foreign key.

In this case, customer_name is dependent only on customer_id, not on the composite key (order_id, customer_id), which indicates a violation of 2NF.

Q25

Single Choice

How can you modify the following Oracle table to ensure it complies with Second Normal Form?

SQL Code

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

Remove the course_name column. Create a separate table for courses. Add a composite foreign key.Add a surrogate key.

course_name should be in a separate courses table that references the course_id. This ensures that course_name is fully dependent on course_id alone, not on the composite key, thus complying with 2NF.

Q26

Single Choice

Given the following Oracle SQL table creation script, identify which column violates Second Normal Form:

SQL Code

CREATE TABLE project_assignments (
 employee_id NUMBER,
 project_id NUMBER,
 project_name VARCHAR2(100),
 assignment_date DATE,
 PRIMARY KEY (employee_id, project_id)
);

employee_id project_id project_nameassignment_date

project_name is dependent only on project_id, not on the composite key (employee_id, project_id). This partial dependency violates 2NF.

Q27

Single Choice

Which SQL operation would be used to normalize the following Oracle table to Second Normal Form?

SQL Code

CREATE TABLE employee_projects (
 emp_id NUMBER,
 proj_id NUMBER,
 proj_desc VARCHAR2(255),
 emp_role VARCHAR2(50),
 PRIMARY KEY (emp_id, proj_id)
);

INSERT INTO DELETE FROM ALTER TABLECREATE TABLE

To normalize this table to 2NF, you would likely create a separate table for projects with proj_id as the primary key and proj_desc as an attribute, removing the partial dependency.

Q28

Single Choice

Examine the following Oracle table structure:To achieve Second Normal Form, which of the following should be done?

SQL Code

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

product_name should be moved to a new products table with product_id as the primary key. sales_amount should be moved to a new sales table. sales_amount should be moved to a new sales table. C) sales_id should be removed as a primary key.The table is already in Second Normal Form.

product_name is dependent only on product_id and should be in a separate table to eliminate partial dependency and comply with 2NF.

Q29

Single Choice

In Oracle, a composite key consisting of emp_id and dept_id is used in a table. If dept_name is an attribute in the same table, what would be the result regarding Second Normal Form?

The table would violate 2NF because dept_name is only dependent on dept_id. The table would be in 2NF as long as emp_id is the main identifier. The table would violate 1NF instead of 2NF.The table would need to include dept_name in the primary key to comply with 2NF.

dept_name is dependent only on dept_id and not on the composite key, which violates 2NF. This should be normalized by creating a separate table for departments.

Q30

Single Choice

Consider the Oracle table below:What is the best way to normalize this table to Second Normal Form?

SQL Code

CREATE TABLE employee_data (
 emp_id NUMBER,
 dept_id NUMBER,
 dept_location VARCHAR2(100),
 emp_name VARCHAR2(100),
 PRIMARY KEY (emp_id, dept_id)
);

Split the table into employee_data and department_data with dept_id as a foreign key in employee_data. Add dept_location to the primary key. Remove dept_id from the primary key.No action is required; the table is already in Second Normal Form.

To achieve 2NF, dept_location should be in a separate department_data table because it is dependent only on dept_id and not on the composite key.

Oracle Database Quiz Questions