Test Your MySQL Third Normal Form Knowledge with Our SQL Quiz

Progress

True / False

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

True False

Third Normal Form requires that a table be in Second Normal Form and that all its attributes are only dependent on the primary key.

True / False

A table in MySQL with non-prime attributes that are transitively dependent on the primary key is considered to be in Third Normal Form (3NF).

True False

Third Normal Form eliminates transitive dependencies, meaning non-prime attributes must not be dependent on other non-prime attributes.

True / False

If a MySQL table is in Third Normal Form (3NF), it automatically means it is also in Second Normal Form (2NF).

True False

Third Normal Form builds upon the requirements of Second Normal Form, so a table in 3NF is also in 2NF.

True / False

In MySQL, achieving Third Normal Form (3NF) involves ensuring that no column is transitively dependent on the primary key.

True False

Third Normal Form requires that all attributes are directly dependent on the primary key and not on any other non-prime attribute.

True / False

A table in MySQL can be in Third Normal Form (3NF) even if it has partial dependencies.

True False

Partial dependencies are addressed in the Second Normal Form, which is a prerequisite for the Third Normal Form.

True / False

In MySQL, if a table is in Third Normal Form (3NF), it can still have redundancy issues.

True False

While Third Normal Form reduces certain types of redundancy, it does not eliminate all possible redundancies.

True / False

In MySQL, converting a table from Second Normal Form (2NF) to Third Normal Form (3NF) may require splitting the table into multiple tables.

True False

To eliminate transitive dependencies, it is often necessary to decompose a table into smaller tables.

True / False

In MySQL, a table in Third Normal Form (3NF) should not have any non-key attributes that depend on other non-key attributes.

True False

This is a requirement of Third Normal Form to ensure no transitive dependencies exist.

True / False

A MySQL table in Third Normal Form (3NF) might need additional foreign keys to maintain data integrity after decomposition.

True False

Decomposing tables to achieve 3NF can result in the creation of new tables with foreign keys to maintain relationships and data integrity.

Q10

True / False

In MySQL, normalization to Third Normal Form (3NF) always improves query performance.

True False

While 3NF reduces redundancy and improves data integrity, it can sometimes lead to more complex queries, which may negatively impact performance in certain scenarios.

Q11

Single Choice

Which of the following is a requirement for a table to be in the Third Normal Form (3NF) in MySQL?

Every non-prime attribute is fully functionally dependent on every key. Every non-prime attribute is non-transitively dependent on every key. Every attribute must be atomic.Every non-key attribute is functionally dependent on the primary key.

In 3NF, a table must be in 2NF and all its non-prime attributes must be non-transitively dependent on every key, meaning no transitive dependency should exist.

Q12

Single Choice

In MySQL, which dependency is removed to achieve Third Normal Form (3NF)?

Partial dependency Transitive dependency Functional dependencyMultivalued dependency

To achieve 3NF, transitive dependencies, where a non-key attribute depends on another non-key attribute, must be removed.

Q13

Single Choice

What is the primary benefit of ensuring a MySQL database is in Third Normal Form (3NF)?

Improved query performance Elimination of all types of redundancy Prevention of update anomaliesEnsuring atomicity of attributes

3NF helps in removing transitive dependencies, thus preventing update, insertion, and deletion anomalies

Q14

Single Choice

Which of the following statements is true about a MySQL table that is in Third Normal Form (3NF)?

It can have partial dependencies It can have transitive dependencies It is in First Normal Form (1NF) but not in Second Normal Form (2NF)It does not have transitive dependencies.

A table in 3NF is free from transitive dependencies, meaning non-prime attributes do not depend on other non-prime attributes.

Q15

Single Choice

Consider a MySQL table with columns student_id, course_id, and instructor_name. If the table is in 3NF, which of the following must be true?

instructor_name should depend only on student_id. instructor_name should depend only on course_id instructor_name should not depend on course_id and student_id together.student_id and course_id should be in a separate table.

To achieve 3NF, non-key attributes should only depend on the candidate key directly, not through another non-key attribute.

Q16

Single Choice

In MySQL, if a table is in 2NF but not in 3NF, which of the following issues might still be present?

Partial dependency Functional dependency Transitive dependencyMultivalued dependency

A table in 2NF can still have transitive dependencies, which are removed in 3NF.

Q17

Single Choice

Given a MySQL table Orders with columns order_id, customer_id, customer_name, and order_date, which of the following transformations would help achieve 3NF?

Split the table into Orders(order_id, order_date) and Customers(customer_id, customer_name) Add a composite key on customer_id and order_date Ensure customer_name depends only on customer_id and not order_date.Create a new table with order_id and customer_name.

This transformation removes the transitive dependency of customer_name on order_id through customer_id.

Q18

Single Choice

If a MySQL table Employees has the columns emp_id, dept_id, dept_name, salary, and is not in 3NF, which step would normalize it to 3NF?

Remove dept_id column Remove dept_name and create a new table Departments(dept_id, dept_name). Add a composite key on emp_id and dept_id.Add an index on salary.

By creating a separate Departments table, you eliminate the transitive dependency of dept_name on emp_id through dept_id.

Q19

Single Choice

Which of the following MySQL queries would help in checking if a table is in Third Normal Form (3NF)?

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'table_name SELECT * FROM table_name WHERE column_name IS NULL; EXPLAIN SELECT * FROM table_name;No single query can determine 3NF; it requires analyzing table design and dependencies.

Verifying 3NF involves examining the structure and dependencies of the table, which cannot be determined by a single query.

Q20

Single Choice

In a MySQL certification exam, you are asked to normalize a table Books with columns book_id, author_id, author_name, and publisher_name. To ensure the table is in Third Normal Form (3NF), which of the following is the correct approach?

Ensure author_name depends only on book_id. Split the table into Books(book_id, author_id, publisher_name) and Authors(author_id, author_name). Create a composite key on author_id and publisher_name.Remove author_name and publisher_name from the Books table.

This approach removes the transitive dependency of author_name on book_id through author_id, ensuring the table is in 3NF.

Q21

Multiple Choice

Which SQL statement demonstrates the concept of Third Normal Form (3NF) by eliminating transitive dependencies in a table design?

SQL Code

CREATE TABLE employees (
 employee_id INT PRIMARY KEY,
 employee_name VARCHAR(100),
 department_id INT,
 salary DECIMAL(10, 2),
 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, employee_name VARCHAR(100), department_name VARCHAR(100), salary DECIMAL(10, 2) ); 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, salary DECIMAL(10, 2), FOREIGN KEY (department_id) REFERENCES departments(department_id) );CREATE TABLE employees ( employee_id INT, employee_name VARCHAR(100), department_name VARCHAR(100) );

Third Normal Form (3NF) is achieved by ensuring that all attributes in a table are dependent only on the primary key. This is done by removing transitive dependencies, which occur when non-key attributes depend on other non-key attributes.

Q22

Multiple Choice

Which SQL code demonstrates moving from Second Normal Form (2NF) to Third Normal Form (3NF) by organizing data into related tables?

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),
 customer_address VARCHAR(255)
);
CREATE TABLE order_items (
 order_id INT,
 product_id INT,
 quantity INT,
 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 INT PRIMARY KEY, customer_name VARCHAR(100), order_date DATE ); CREATE TABLE customers ( customer_id INT PRIMARY KEY, customer_name VARCHAR(100), customer_address VARCHAR(255) ); CREATE TABLE order_items ( order_id INT, product_id INT, quantity INT, 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 INT PRIMARY KEY, customer_id INT, order_date DATE );

Third Normal Form (3NF) removes transitive dependencies by ensuring that non-key attributes are only dependent on the primary key, and not on other non-key attributes. The provided SQL code separates customer, order, and product data into related tables.

Q23

Multiple Choice

Which SQL snippet correctly represents a database design that adheres to Third Normal Form (3NF) by eliminating transitive dependencies?

SQL Code

CREATE TABLE books (
 book_id INT PRIMARY KEY,
 title VARCHAR(100),
 author_id INT,
 FOREIGN KEY (author_id) REFERENCES authors(author_id)
);
CREATE TABLE authors (
 author_id INT PRIMARY KEY,
 author_name VARCHAR(100)
);
CREATE TABLE publishers (
 publisher_id INT PRIMARY KEY,
 publisher_name VARCHAR(100)
);

CREATE TABLE books ( book_id INT PRIMARY KEY, title VARCHAR(100), author_name VARCHAR(100) ); CREATE TABLE authors ( author_id INT PRIMARY KEY, author_name VARCHAR(100) ); CREATE TABLE books ( book_id INT PRIMARY KEY, title VARCHAR(100), author_id INT, FOREIGN KEY (author_id) REFERENCES authors(author_id) );CREATE TABLE publishers ( publisher_id INT PRIMARY KEY, publisher_name VARCHAR(100) );

Third Normal Form (3NF) eliminates transitive dependencies by ensuring that all non-key attributes are dependent only on the primary key, not on other non-key attributes. This is achieved by separating related data into distinct tables.

Q24

Multiple Choice

Choose the SQL code that demonstrates achieving Third Normal Form (3NF) by eliminating all non-key dependencies in a table design.

SQL Code

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

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

Third Normal Form (3NF) ensures that all non-key attributes are directly dependent only on the primary key. The provided SQL design separates product information from category information, adhering to 3NF principles.

Q25

Multiple Choice

Which SQL code best exemplifies the application of Third Normal Form (3NF) by organizing data into related tables and eliminating transitive dependencies?

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 (
 enrollment_id INT PRIMARY KEY,
 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 ( enrollment_id INT PRIMARY KEY, 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) );

Third Normal Form (3NF) is achieved by organizing data into related tables and ensuring that all attributes are dependent only on the primary key. This design effectively demonstrates 3NF by separating students, courses, and enrollments.

Q26

Multiple Choice

Which SQL code snippet effectively eliminates transitive dependencies to achieve Third Normal Form (3NF) in a relational table?

SQL Code

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

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), customer_address VARCHAR(255) ); CREATE TABLE orders ( order_id INT PRIMARY KEY, customer_id INT, order_date DATE, FOREIGN KEY (customer_id) REFERENCES customers(customer_id) );CREATE TABLE orders ( order_id INT, customer_name VARCHAR(100) );

Third Normal Form (3NF) eliminates transitive dependencies by ensuring that all attributes are dependent only on the primary key. The design provided separates order and customer data into distinct tables, adhering to 3NF.

Q27

Multiple Choice

Which SQL statement best demonstrates moving a table design to Third Normal Form (3NF) by addressing transitive dependencies?

SQL Code

CREATE TABLE employees (
 employee_id INT PRIMARY KEY,
 employee_name VARCHAR(100),
 department_id INT,
 salary DECIMAL(10, 2),
 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, 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, salary DECIMAL(10, 2), FOREIGN KEY (department_id) REFERENCES departments(department_id) );CREATE TABLE employees ( employee_id INT, employee_name VARCHAR(100), department_name VARCHAR(100) );

Third Normal Form (3NF) removes transitive dependencies, which occur when non-key attributes depend on other non-key attributes. This design organizes data to ensure that all attributes are directly dependent on the primary key.

Q28

Multiple Choice

Choose the SQL code that correctly illustrates the transition from 2NF to 3NF by removing transitive dependencies in a table.

SQL Code

CREATE TABLE books (
 book_id INT PRIMARY KEY,
 title VARCHAR(100),
 author_id INT,
 publisher_id INT,
 FOREIGN KEY (author_id) REFERENCES authors(author_id),
 FOREIGN KEY (publisher_id) REFERENCES publishers(publisher_id)
);
CREATE TABLE authors (
 author_id INT PRIMARY KEY,
 author_name VARCHAR(100)
);
CREATE TABLE publishers (
 publisher_id INT PRIMARY KEY,
 publisher_name VARCHAR(100)
);

CREATE TABLE books ( book_id INT PRIMARY KEY, title VARCHAR(100), author_name VARCHAR(100), publisher_name VARCHAR(100) ); CREATE TABLE authors ( author_id INT PRIMARY KEY, author_name VARCHAR(100) ); CREATE TABLE books ( book_id INT PRIMARY KEY, title VARCHAR(100), author_id INT, publisher_id INT, FOREIGN KEY (author_id) REFERENCES authors(author_id), FOREIGN KEY (publisher_id) REFERENCES publishers(publisher_id) );CREATE TABLE publishers ( publisher_id INT PRIMARY KEY, publisher_name VARCHAR(100) );

Third Normal Form (3NF) is achieved by eliminating transitive dependencies, which are dependencies between non-key attributes. The SQL code provided demonstrates how to separate book, author, and publisher data into related tables.

Q29

Multiple Choice

Which SQL code effectively organizes a database design into Third Normal Form (3NF) by eliminating all non-key dependencies?

SQL Code

CREATE TABLE students (
 student_id INT PRIMARY KEY,
 student_name VARCHAR(100)
);
CREATE TABLE addresses (
 address_id INT PRIMARY KEY,
 student_id INT,
 address VARCHAR(255),
 FOREIGN KEY (student_id) REFERENCES students(student_id)
);

CREATE TABLE students ( student_id INT PRIMARY KEY, student_name VARCHAR(100), address VARCHAR(255) ); CREATE TABLE addresses ( address_id INT PRIMARY KEY, student_id INT, address VARCHAR(255), FOREIGN KEY (student_id) REFERENCES students(student_id) ); CREATE TABLE students ( student_id INT PRIMARY KEY, student_name VARCHAR(100) );CREATE TABLE addresses ( address_id INT PRIMARY KEY, address VARCHAR(255) );

Third Normal Form (3NF) eliminates non-key dependencies by ensuring that all attributes are dependent only on the primary key. The SQL code provided demonstrates how to separate student and address data into distinct tables, adhering to 3NF.

Q30

Multiple Choice

Which SQL snippet best illustrates achieving Third Normal Form (3NF) by organizing data into related tables and removing transitive dependencies?

SQL Code

CREATE TABLE projects (
 project_id INT PRIMARY KEY,
 project_name VARCHAR(100)
);
CREATE TABLE teams (
 team_id INT PRIMARY KEY,
 team_name VARCHAR(100)
);
CREATE TABLE project_teams (
 project_id INT,
 team_id INT,
 PRIMARY KEY (project_id, team_id),
 FOREIGN KEY (project_id) REFERENCES projects(project_id),
 FOREIGN KEY (team_id) REFERENCES teams(team_id)
);

CREATE TABLE projects ( project_id INT PRIMARY KEY, project_name VARCHAR(100), team_name VARCHAR(100) ); CREATE TABLE teams ( team_id INT PRIMARY KEY, team_name VARCHAR(100) ); CREATE TABLE project_teams ( project_id INT, team_id INT, PRIMARY KEY (project_id, team_id), FOREIGN KEY (project_id) REFERENCES projects(project_id), FOREIGN KEY (team_id) REFERENCES teams(team_id) );CREATE TABLE projects ( project_id INT PRIMARY KEY, project_name VARCHAR(100) );

Third Normal Form (3NF) is achieved by removing transitive dependencies and organizing data into related tables. This SQL snippet effectively separates project and team data, ensuring all attributes are directly dependent on the primary key.

MySQL Database Quiz Questions