Test Your MySQL Normalization Knowledge with Our SQL Quiz

Progress

True / False

MySQL, normalization involves organizing data to minimize redundancy.

True False

Normalization is the process of structuring a relational database in a way that reduces redundancy and improves data integrity.

True / False

MySQL, the first normal form (1NF) requires that all columns in a table have unique values.

True False

The first normal form (1NF) requires that each column contains only atomic (indivisible) values and that each column contains values of a single type, not necessarily unique values.

True / False

MySQL, a table is in the second normal form (2NF) if it is already in the first normal form (1NF) and has no partial dependencies

True False

Second normal form (2NF) eliminates partial dependencies, meaning all non-key attributes must depend on the entire primary key.

True / False

MySQL, the third normal form (3NF) is achieved when a table is in second normal form (2NF) and there are no transitive dependencies.

True False

Third normal form (3NF) requires that all the attributes in a table are dependent only on the primary key and not on any other non-key attribute.

True / False

MySQL, denormalization is the process of applying normalization principles to a database design.

True False

Denormalization is the process of combining normalized tables to improve read performance at the cost of write performance and increased redundancy.

True / False

MySQL, normalization to the Boyce-Codd Normal Form (BCNF) can sometimes require decomposing tables into smaller tables.

True False

BCNF is a stricter version of 3NF, and achieving BCNF often requires further decomposing tables to eliminate anomalies.

True / False

MySQL, a table in fourth normal form (4NF) must also be in Boyce-Codd Normal Form (BCNF).

True False

Fourth normal form (4NF) requires that the table is already in BCNF and that it eliminates multi-valued dependencies.

True / False

MySQL, a table in fifth normal form (5NF) eliminates all join dependencies and is also known as project-join normal form (PJNF).

True False

Fifth normal form (5NF), or project-join normal form (PJNF), ensures that a table is decomposed to the point where it cannot be further decomposed without losing data integrity

True / False

MySQL, tables in domain-key normal form (DKNF) can have redundancy if domain constraints are not enforced.

True False

Domain-key normal form (DKNF) ensures that all constraints and rules are enforced by domain and key constraints, leaving no room for redundancy.

Q10

True / False

MySQL, achieving sixth normal form (6NF) is essential for all database designs to ensure minimal redundancy and maximum data integrity.

True False

Sixth normal form (6NF) is not commonly used in practice. It is typically applicable only in very specific scenarios involving temporal data. Most practical database designs aim for 3NF or BCNF to balance normalization and performance.

Q11

Single Choice

Which of the following best describes normalization in MySQL?

The process of duplicating data to increase redundancy. The process of organizing data to reduce redundancy. The process of deleting unused data.The process of backing up data.

Normalization in MySQL is the process of structuring a relational database to minimize redundancy and dependency by organizing fields and table relationships.

Q12

Single Choice

What is the primary purpose of the first normal form (1NF) in MySQL?

To ensure that all non-key attributes are fully functional dependent on the primary key. To remove duplicate rows from tables. To ensure that each table has a primary key.To remove repeating groups and ensure atomicity.

The first normal form (1NF) ensures that the values in each column of a table are atomic (indivisible) and that there are no repeating groups of columns.

Q13

Single Choice

Which of the following is a benefit of normalizing a MySQL database?

Increased data redundancy. Easier data manipulation. Improved data consistency.Larger database size.

Normalizing a MySQL database helps improve data consistency by reducing redundancy and ensuring that data is logically stored.

Q14

Single Choice

In MySQL, which normal form deals with transitive dependencies?

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

The third normal form (3NF) eliminates transitive dependencies, ensuring that non-key attributes are only dependent on the primary key

Q15

Single Choice

What is the key requirement for a table to be in the second normal form (2NF) in MySQL?

The table must be in 1NF and all non-key attributes must be fully dependent on the primary key. The table must have no repeating groups. The table must be in 3NF.The table must have a composite key.

A table is in the second normal form (2NF) if it is in the first normal form (1NF) and all non-key attributes are fully functional dependent on the entire primary key.

Q16

Single Choice

Which of the following statements is true about Boyce-Codd Normal Form (BCNF) in MySQL?

Every BCNF table is also in 1NF. BCNF is less strict than 3NF. BCNF allows partial dependencies.A table in BCNF can have transitive dependencies.

BCNF is a stricter form of 3NF. Every BCNF table is in 3NF and, consequently, also in 1NF, but not every 3NF table is in BCNF.

Q17

Single Choice

What is a potential drawback of over-normalizing a MySQL database?

Increased data redundancy. Reduced query performance due to excessive joins. Greater risk of data anomalies.Increased database size.

Over-normalization can lead to excessive joins between tables, which may reduce query performance and complicate SQL queries.

Q18

Single Choice

In MySQL, what does the term "de-normalization" refer to?

The process of reverting a normalized database to an unnormalized state. The process of optimizing database design by adding redundancy. The process of converting tables to 1NF.The process of backing up data.

De-normalization involves adding redundancy to a database design to improve read performance at the cost of write performance and storage space.

Q19

Single Choice

In MySQL, if a table is in BCNF, which of the following must be true?

The table is also in 4NF. The table is in 3NF, and every determinant is a candidate key. The table is in 2NF but not necessarily in 3NF.The table has no partial dependencies.

BCNF is a stricter version of 3NF, where for every functional dependency (X -> Y), X must be a candidate key.

Q20

Single Choice

Which of the following best explains why a MySQL database designer might choose to use denormalization?

To reduce the complexity of SQL queries. To ensure the database is in BCNF. To improve the performance of read-heavy applications.To decrease storage requirements.

Denormalization is often used in MySQL to improve the performance of read-heavy applications by reducing the number of joins required in queries, thereby speeding up read operations.

Q21

Multiple Choice

Which SQL statement demonstrates normalization by splitting a table into two to eliminate redundancy?

SQL Code

CREATE TABLE customers (
 customer_id INT PRIMARY KEY,
 name VARCHAR(100)
);
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, name VARCHAR(100), order_id INT, order_date DATE ); CREATE TABLE customers ( customer_id INT PRIMARY KEY, name VARCHAR(100) ); 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 PRIMARY KEY, customer_id INT, order_date DATE );CREATE TABLE customers ( customer_id INT PRIMARY KEY, name VARCHAR(100) );

Normalization involves structuring a database to reduce redundancy and dependency by organizing data into separate related tables.

Q22

Multiple Choice

Which SQL code illustrates the concept of first normal form (1NF) in a table design?

SQL Code

CREATE TABLE employees (
 employee_id INT PRIMARY KEY,
 first_name VARCHAR(50),
 last_name VARCHAR(50),
 address VARCHAR(255)
);

CREATE TABLE employees ( employee_id INT PRIMARY KEY, name VARCHAR(100), addresses VARCHAR(255) ); CREATE TABLE employees ( employee_id INT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50), address VARCHAR(255) ); CREATE TABLE employees ( employee_id INT, first_name VARCHAR(50), last_name VARCHAR(50) );CREATE TABLE employees ( employee_id INT PRIMARY KEY, name VARCHAR(100), address_line1 VARCHAR(255), address_line2 VARCHAR(255) );

First Normal Form (1NF) ensures that each column contains atomic values, and each record is unique. The table design shown is compliant with 1NF principles.

Q23

Multiple Choice

Which SQL statement correctly moves a database design from first normal form (1NF) to second normal form (2NF)?

SQL Code

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_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_id INT, FOREIGN KEY (department_id) REFERENCES departments(department_id) ); CREATE TABLE employees ( employee_id INT PRIMARY KEY, first_name VARCHAR(50), department_id INT );CREATE TABLE departments ( department_id INT PRIMARY KEY, department_name VARCHAR(100), employee_id INT, FOREIGN KEY (employee_id) REFERENCES employees(employee_id) );

Second Normal Form (2NF) eliminates partial dependencies by ensuring that non-primary key attributes are fully dependent on the primary key. This is achieved by creating separate tables for related data.

Q24

Multiple Choice

Which SQL code snippet is an example of achieving third normal form (3NF) 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 categories ( category_id INT PRIMARY KEY, 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 products ( product_id INT, product_name VARCHAR(100), category_id INT, category_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 adheres to 3NF by separating product information from category information.

Q25

Multiple Choice

Select the SQL code that demonstrates normalization by breaking down a table to eliminate redundancy and ensure referential integrity.

SQL Code

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,
 FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

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

Normalization reduces data redundancy by organizing data into multiple related tables, each with a unique primary key and foreign key relationships to ensure referential integrity.

Q26

Multiple Choice

Which SQL statement best demonstrates the transition from second normal form (2NF) to third normal form (3NF) by eliminating 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), 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 departments ( department_id INT, department_name VARCHAR(100) );

Third Normal Form (3NF) is achieved by removing transitive dependencies, ensuring that non-primary key attributes are dependent only on the primary key. This is done by creating a separate table for departments.

Q27

Multiple Choice

Which SQL code effectively demonstrates the normalization process by removing partial dependencies in a relational table?

SQL Code

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

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

Normalization, particularly moving from 1NF to 2NF, involves removing partial dependencies, which means ensuring that all non-primary key attributes are fully functionally dependent on the primary key.

Q28

Multiple Choice

Which SQL code demonstrates how normalization helps in eliminating data anomalies by organizing data into related tables?

SQL Code

CREATE TABLE students (
 student_id INT PRIMARY KEY,
 student_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)
);

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

Normalization eliminates data anomalies such as insertion, update, and deletion anomalies by organizing data into related tables, ensuring that each table stores data relevant only to a specific entity.

Q29

Multiple Choice

Choose the SQL code that effectively demonstrates third normal form (3NF) by eliminating all non-key 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 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 departments ( department_id INT, department_name VARCHAR(100) );

Third Normal Form (3NF) eliminates non-key dependencies, ensuring that attributes in a table are dependent only on the primary key. The design provided follows this principle by separating employee and department data.

Q30

Multiple Choice

Which SQL code snippet best illustrates the normalization process to achieve higher normal forms by removing 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) ); 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_id INT, FOREIGN KEY (course_id) REFERENCES courses(course_id) );

Higher normal forms, such as 3NF, are achieved by eliminating transitive dependencies and organizing data into related tables. This design shows how separate tables are used for students, courses, and enrollments, linking them through foreign keys.

MySQL Database Quiz Questions