Test Your MySQL Second Normal Form Knowledge with Our SQL Quiz

Progress

True / False

MySQL, a table is in Second Normal Form if it is in First Normal Form and all non-key attributes are fully functionally dependent on the primary key.

True False

Second Normal Form (2NF) requires the table to be in First Normal Form (1NF) and that all non-key attributes must be fully functionally dependent on the primary key.

True / False

table with a single-column primary key is always in Second Normal Form in MySQL.

True False

With a single-column primary key, there cannot be any partial dependencies, so the table is automatically in 2NF if it meets the criteria of 1NF.

True / False

achieve Second Normal Form in MySQL, you must remove all repeating groups from the table.

True False

Removing repeating groups is a requirement for First Normal Form (1NF), not Second Normal Form (2NF).

True / False

MySQL, if a table is in Second Normal Form, there can be transitive dependencies.

True False

Second Normal Form addresses partial dependencies, but transitive dependencies are addressed in Third Normal Form (3NF).

True / False

MySQL, to convert a table to Second Normal Form, you may need to decompose the table into smaller tables.

True False

Decomposition is often necessary to eliminate partial dependencies and ensure that non-key attributes are fully functionally dependent on the entire primary key.

True / False

MySQL, Second Normal Form only applies to tables with composite primary keys.

True False

While 2NF mainly addresses issues that arise with composite primary keys, it is still applicable to tables with single-column primary keys to confirm they meet the criteria.

True / False

MySQL, a table in Second Normal Form cannot have any multi-valued attributes.

True False

Multi-valued attributes violate First Normal Form (1NF), which is a prerequisite for Second Normal Form (2NF).

True / False

MySQL, a table in Second Normal Form is free of update anomalies related to partial dependencies.

True False

Since 2NF eliminates partial dependencies, update anomalies related to such dependencies are resolved.

True / False

MySQL, Second Normal Form ensures that every non-prime attribute is non-transitively dependent on the primary key.

True False

This is the requirement for Third Normal Form (3NF), not Second Normal Form (2NF).

Q10

True / False

MySQL database, ensuring a table is in Second Normal Form is sufficient to prevent all types of data anomalies.

True False

Second Normal Form (2NF) only eliminates partial dependencies. To fully prevent all types of data anomalies, a table must be in Third Normal Form (3NF) or even higher normal forms, addressing transitive dependencies and other issues.

Q11

Single Choice

What is the primary requirement for a MySQL table to be in Second Normal Form (2NF)?

It must be in First Normal Form (1NF) and have no repeating groups. It must be in First Normal Form (1NF) and all non-key attributes must be fully functionally dependent on the primary key. It must be in Third Normal Form (3NF).It must have a single column as the primary key.

For a table to be in 2NF, it must first meet all the requirements of 1NF and then all non-key attributes must be fully functionally dependent on the primary key.

Q12

Single Choice

Which of the following statements about 2NF is true in MySQL?

A table is automatically in 2NF if it has a composite key. A table in 2NF can have partial dependency. A table is in 2NF if it is in 1NF and has no partial dependency.A table in 2NF can have transitive dependency.

A table in 2NF should be in 1NF and must not have any partial dependency (where non-key attributes depend only on a part of the primary key).

Q13

Single Choice

Which type of dependency must be eliminated to achieve 2NF in MySQL?

Transitive dependency Partial dependency Functional dependencyMultivalued dependency

To achieve 2NF, partial dependencies (where non-key attributes depend on part of a composite primary key) must be eliminated.

Q14

Single Choice

Consider a MySQL table Orders with columns OrderID, ProductID, ProductName, and OrderDate. If OrderID and ProductID together form the primary key, which of the following would violate 2NF?

Having ProductName depend only on ProductID. Having OrderDate depend only on OrderID. Both A and B.Neither A nor B.

Both dependencies violate 2NF because they create partial dependencies on the composite primary key.

Q15

Single Choice

A MySQL table Students has columns StudentID, CourseID, CourseName, and Instructor. StudentID and CourseID together form the primary key. Which of the following redesigns would achieve 2NF?

Split into Students(StudentID, CourseID) and Courses(CourseID, CourseName, Instructor). Split into Students(StudentID, CourseID, CourseName) and Instructors(Instructor) Split into Students(StudentID, CourseID, Instructor) and Courses(CourseID, CourseName).Keep as is.

Splitting into Students(StudentID, CourseID) and Courses(CourseID, CourseName, Instructor) removes partial dependencies by separating data into related tables.

Q16

Single Choice

In a MySQL database, which of the following scenarios indicates that a table is not in 2NF?

A non-key attribute depends on the entire primary key. A non-key attribute depends on part of a composite primary key. The table has a single-column primary key.All non-key attributes depend on the primary key.

A table is not in 2NF if there are partial dependencies, meaning some non-key attributes depend on only part of a composite primary key.

Q17

Single Choice

Consider a MySQL table Projects with columns ProjectID, TaskID, TaskDescription, and TaskDueDate. ProjectID and TaskID form the composite primary key. To convert this table into 2NF, which of the following would be the correct approach?

Create a new table Tasks(TaskID, TaskDescription, TaskDueDate) and keep Projects(ProjectID, TaskID). Create a new table Projects(ProjectID, TaskDescription, TaskDueDate) and keep Tasks(TaskID). Create a new table ProjectTasks(ProjectID, TaskID, TaskDueDate) and Tasks(TaskID, TaskDescription).No changes are needed as it is already in 2NF.

Creating separate tables ProjectTasks and Tasks eliminates partial dependencies and achieves 2NF.

Q18

Single Choice

A MySQL table EmployeeAssignments has columns EmployeeID, ProjectID, EmployeeName, and ProjectName. The primary key is a composite key consisting of EmployeeID and ProjectID. How would you redesign this table to be in 2NF?

Split into Employees(EmployeeID, EmployeeName) and Projects(ProjectID, ProjectName). Split into Employees(EmployeeID, EmployeeName) and EmployeeAssignments(EmployeeID, ProjectID). Split into EmployeeAssignments(EmployeeID, ProjectID, EmployeeName) and Projects(ProjectID, ProjectName).Keep as is.

Splitting into Employees and Projects removes partial dependencies, as EmployeeName and ProjectName are dependent on their respective primary keys.

Q19

Single Choice

In a MySQL database design, why might achieving 2NF be particularly important for tables with composite primary keys?

To reduce the number of tables in the database. To eliminate redundancy and update anomalies. To simplify queries involving the table.To ensure every table has a single primary key.

Achieving 2NF eliminates redundancy and update anomalies, especially in tables with composite primary keys where partial dependencies are common.

Q20

Single Choice

A MySQL certification exam question states: Given a table Sales with columns SaleID, ProductID, ProductName, and SaleDate, with SaleID and ProductID forming the composite primary key, which normal form is violated if ProductName is dependent on ProductID?

1NF 2NF 3NFBCNF

The table violates 2NF because ProductName depends on ProductID, which is only a part of the composite primary key (SaleID, ProductID), creating a partial dependency.

Q21

Multiple Choice

Which SQL statement illustrates moving a database design from first normal form (1NF) to second normal form (2NF) by removing partial dependencies?

SQL Code

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

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

Second Normal Form (2NF) removes partial dependencies by ensuring that non-key attributes are fully dependent on the primary key. The code provided shows how to separate department data into its own table.

Q22

Multiple Choice

Which SQL code demonstrates a table design that adheres to the principles of second normal form (2NF) by eliminating partial dependencies?

SQL Code

CREATE TABLE orders (
 order_id INT PRIMARY KEY,
 customer_id INT,
 order_date DATE
);
CREATE TABLE customers (
 customer_id INT PRIMARY KEY,
 customer_name VARCHAR(100)
);

CREATE TABLE orders ( order_id INT PRIMARY KEY, customer_name VARCHAR(100), order_date DATE ); CREATE TABLE customers ( customer_id INT PRIMARY KEY, customer_name VARCHAR(100) ); CREATE TABLE orders ( order_id INT PRIMARY KEY, customer_id INT, order_date DATE );CREATE TABLE orders ( order_id INT, customer_name VARCHAR(100) );

In second normal form (2NF), partial dependencies are removed by ensuring that all non-key attributes are fully functionally dependent on the primary key. This is accomplished by separating related data into distinct tables.

Q23

Multiple Choice

Which SQL snippet correctly illustrates a design that moves a database from 1NF to 2NF by organizing data into separate tables?

SQL Code

CREATE TABLE projects (
 project_id INT PRIMARY KEY,
 project_name VARCHAR(100)
);
CREATE TABLE employees (
 employee_id INT PRIMARY KEY,
 employee_name VARCHAR(100),
 project_id INT,
 FOREIGN KEY (project_id) REFERENCES projects(project_id)
);

CREATE TABLE employees ( employee_id INT PRIMARY KEY, employee_name VARCHAR(100), project_name VARCHAR(100) ); CREATE TABLE projects ( project_id INT PRIMARY KEY, project_name VARCHAR(100) ); CREATE TABLE employees ( employee_id INT PRIMARY KEY, employee_name VARCHAR(100), project_id INT, FOREIGN KEY (project_id) REFERENCES projects(project_id) );CREATE TABLE projects ( project_id INT, project_name VARCHAR(100) );

Second Normal Form (2NF) is achieved by organizing data into separate tables to ensure that each non-key attribute is fully dependent on the primary key and not just part of it. This design adheres to 2NF.

Q24

Multiple Choice

Choose the SQL code that best demonstrates the transition from 1NF to 2NF by creating separate tables for related data.

SQL Code

CREATE TABLE students (
 student_id INT PRIMARY KEY,
 student_name VARCHAR(100)
);
CREATE TABLE courses (
 course_id INT PRIMARY KEY,
 course_name VARCHAR(100)
);
CREATE TABLE enrollments (
 student_id INT,
 course_id INT,
 FOREIGN KEY (student_id) REFERENCES students(student_id),
 FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

CREATE TABLE students ( student_id INT PRIMARY KEY, student_name VARCHAR(100), course_name VARCHAR(100) ); CREATE TABLE courses ( course_id INT PRIMARY KEY, course_name VARCHAR(100) ); CREATE TABLE enrollments ( student_id INT, course_id INT, FOREIGN KEY (student_id) REFERENCES students(student_id), FOREIGN KEY (course_id) REFERENCES courses(course_id) );CREATE TABLE students ( student_id INT PRIMARY KEY, student_name VARCHAR(100) );

Second Normal Form (2NF) is about eliminating partial dependencies by organizing data into separate tables for each related entity. The provided design effectively demonstrates this principle.

Q25

Multiple Choice

Which SQL code snippet properly moves a database design to 2NF by removing partial dependencies?

SQL Code

CREATE TABLE departments (
 department_id INT PRIMARY KEY,
 department_name VARCHAR(100)
);
CREATE TABLE employees (
 employee_id INT PRIMARY KEY,
 employee_name VARCHAR(100),
 department_id INT,
 FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

CREATE TABLE employees ( employee_id INT PRIMARY KEY, employee_name VARCHAR(100), department_name VARCHAR(100) ); CREATE TABLE departments ( department_id INT PRIMARY KEY, department_name VARCHAR(100) ); CREATE TABLE employees ( employee_id INT PRIMARY KEY, employee_name VARCHAR(100), department_id INT, FOREIGN KEY (department_id) REFERENCES departments(department_id) );CREATE TABLE departments ( department_id INT, department_name VARCHAR(100) );

Second Normal Form (2NF) is achieved by ensuring that all non-key attributes in a table are fully dependent on the entire primary key. This design separates department data into its own table to comply with 2NF.

Q26

Multiple Choice

Which SQL statement effectively demonstrates the normalization process from 1NF to 2NF by addressing partial dependencies in a relational table?

SQL Code

CREATE TABLE orders (
 order_id INT PRIMARY KEY,
 customer_id INT,
 order_date DATE
);
CREATE TABLE customers (
 customer_id INT PRIMARY KEY,
 customer_name VARCHAR(100)
);

Second Normal Form (2NF) removes partial dependencies by ensuring that non-key attributes are fully dependent on the primary key. This is achieved by separating customer and order data into distinct tables.

Q27

Multiple Choice

Which SQL snippet best illustrates achieving 2NF by eliminating partial dependencies from a table design?

SQL Code

CREATE TABLE products (
 product_id INT PRIMARY KEY,
 product_name VARCHAR(100)
);
CREATE TABLE orders (
 order_id INT PRIMARY KEY,
 product_id INT,
 order_date DATE,
 FOREIGN KEY (product_id) REFERENCES products(product_id)
);

CREATE TABLE orders ( order_id INT PRIMARY KEY, product_name VARCHAR(100), order_date DATE ); CREATE TABLE products ( product_id INT PRIMARY KEY, product_name VARCHAR(100) ); CREATE TABLE orders ( order_id INT PRIMARY KEY, product_id INT, order_date DATE, FOREIGN KEY (product_id) REFERENCES products(product_id) );CREATE TABLE orders ( order_id INT, product_name VARCHAR(100) );

Second Normal Form (2NF) eliminates partial dependencies by ensuring that all non-key attributes are fully dependent on the primary key. This design separates product and order data into distinct tables.

Q28

Multiple Choice

Which SQL code effectively moves a database table design to 2NF by addressing partial dependencies and creating separate tables?

SQL Code

CREATE TABLE authors (
 author_id INT PRIMARY KEY,
 author_name VARCHAR(100)
);
CREATE TABLE books (
 book_id INT PRIMARY KEY,
 author_id INT,
 book_title VARCHAR(100),
 FOREIGN KEY (author_id) REFERENCES authors(author_id)
);

CREATE TABLE books ( book_id INT PRIMARY KEY, author_name VARCHAR(100), book_title VARCHAR(100) ); CREATE TABLE authors ( author_id INT PRIMARY KEY, author_name VARCHAR(100) ); CREATE TABLE books ( book_id INT PRIMARY KEY, author_id INT, book_title VARCHAR(100), FOREIGN KEY (author_id) REFERENCES authors(author_id) );CREATE TABLE authors ( author_id INT, author_name VARCHAR(100) );

Second Normal Form (2NF) is achieved by separating data into related tables, ensuring that all non-key attributes are fully dependent on the primary key and not on just part of it.

Q29

Multiple Choice

Which SQL code snippet is an example of achieving second normal form (2NF) by eliminating partial dependencies?

SQL Code

CREATE TABLE categories (
 category_id INT PRIMARY KEY,
 category_name VARCHAR(100)
);
CREATE TABLE products (
 product_id INT PRIMARY KEY,
 category_id INT,
 product_name VARCHAR(100),
 FOREIGN KEY (category_id) REFERENCES categories(category_id)
);

CREATE TABLE products ( product_id INT PRIMARY KEY, category_name VARCHAR(100), product_name VARCHAR(100) ); CREATE TABLE categories ( category_id INT PRIMARY KEY, category_name VARCHAR(100) ); CREATE TABLE products ( product_id INT PRIMARY KEY, category_id INT, product_name VARCHAR(100), FOREIGN KEY (category_id) REFERENCES categories(category_id) );CREATE TABLE products ( product_id INT, category_name VARCHAR(100) );

In second normal form (2NF), partial dependencies are eliminated by ensuring that all non-key attributes are fully dependent on the entire primary key. This is achieved by separating category and product data into distinct tables.

Q30

Multiple Choice

Which SQL code properly achieves 2NF by eliminating partial dependencies and organizing data into related tables?

SQL Code

CREATE TABLE publishers (
 publisher_id INT PRIMARY KEY,
 publisher_name VARCHAR(100)
);
CREATE TABLE books (
 book_id INT PRIMARY KEY,
 publisher_id INT,
 book_title VARCHAR(100),
 FOREIGN KEY (publisher_id) REFERENCES publishers(publisher_id)
);

CREATE TABLE books ( book_id INT PRIMARY KEY, publisher_name VARCHAR(100), book_title VARCHAR(100) ); CREATE TABLE publishers ( publisher_id INT PRIMARY KEY, publisher_name VARCHAR(100) ); CREATE TABLE books ( book_id INT PRIMARY KEY, publisher_id INT, book_title VARCHAR(100), FOREIGN KEY (publisher_id) REFERENCES publishers(publisher_id) );CREATE TABLE books ( book_id INT, publisher_name VARCHAR(100) );

Second Normal Form (2NF) is achieved by organizing data into related tables and ensuring that all non-key attributes are fully dependent on the primary key. This design separates publisher and book data.

MySQL Database Quiz Questions