Test Your PostgreSQL Second Normal Form Knowledge with this Quiz

Progress

True / False

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

True False

A table must first satisfy all the requirements of 1NF before it can be normalized to 2NF.

True / False

In PostgreSQL, a table is in Second Normal Form (2NF) if all non-key attributes are fully functionally dependent on the primary key.

True False

2NF requires that all non-key attributes are fully dependent on the entire primary key, not just part of it.

True / False

In PostgreSQL, a table with no composite primary key and no partial dependencies is automatically in Second Normal Form (2NF).

True False

If a table has a single-column primary key, it cannot have partial dependencies, and if it meets 1NF, it is automatically in 2NF.

True / False

In PostgreSQL, converting a table to Second Normal Form (2NF) may involve removing columns that depend on only part of a composite primary key.

True False

To achieve 2NF, columns that depend only on part of a composite primary key must be moved to another table where they are fully dependent on the new primary key.

True / False

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

True False

2NF does not address transitive dependencies; it only eliminates partial dependencies.

True / False

In PostgreSQL, a table with a composite primary key is in Second Normal Form (2NF) if every non-key attribute depends on the entire composite primary key.

True False

2NF requires that non-key attributes depend on the whole composite primary key, not just a part of it.

True / False

In PostgreSQL, a table that is in Second Normal Form (2NF) must also be in Third Normal Form (3NF).

True False

A table in 2NF is not necessarily in 3NF. 2NF eliminates partial dependencies, while 3NF also eliminates transitive dependencies.

True / False

In PostgreSQL, achieving Second Normal Form (2NF) often involves creating additional tables to store data that was originally in a single table.

True False

Normalizing to 2NF may require splitting the original table into multiple tables to eliminate partial dependencies.

True / False

In PostgreSQL, a table with a single-column primary key and no non-key attributes can be considered to be in Second Normal Form (2NF).

True False

If a table has a single-column primary key and no non-key attributes, it meets the requirements for 2NF by default.

Q10

True / False

In PostgreSQL, a table that is in First Normal Form (1NF) but has partial dependencies is in violation of Second Normal Form (2NF).

True False

2NF requires that all non-key attributes are fully functionally dependent on the entire primary key. Partial dependencies violate this requirement.

Q11

Single Choice

In PostgreSQL, which of the following conditions must a table satisfy to be in Second Normal Form (2NF)?

The table must have a primary key All non-key attributes must be fully functionally dependent on the primary key. The table must have no duplicate rowsThe table must have no partial dependencies on any key

To be in 2NF, a table must be in 1NF and all non-key attributes must be fully dependent on the entire primary key.

Q12

Single Choice

Consider the following PostgreSQL table orders:

SQL Code

CREATE TABLE orders (
 order_id SERIAL PRIMARY KEY,
 customer_id INT,
 order_date DATE,
 total_amount NUMERIC
);
Is this table in Second Normal Form (2NF)?

Yes, because it has a primary key. Yes, because there are no partial dependencies No, because it lacks a composite keyNo, because the total_amount is not dependent on the primary key.

The table is in 2NF as it has a single primary key and no partial dependencies.

Q13

Single Choice

Which of the following is a characteristic of a PostgreSQL table in Second Normal Form?

It contains redundant data. It contains transitive dependencies. It eliminates partial dependencies.It does not have a primary key.

2NF ensures that there are no partial dependencies, meaning every non-key attribute is fully dependent on the entire primary key.

Q14

Single Choice

Given the following PostgreSQL table student_courses:

SQL Code

CREATE TABLE student_courses (
 student_id INT,
 course_id INT,
 grade CHAR(2),
 PRIMARY KEY (student_id, course_id)
);
Which of the following modifications would ensure the table is in 2NF?

Adding a composite key Removing grade as it is not part of the key. Removing partial dependencies.Adding a surrogate key.

To achieve 2NF, you must ensure all non-key attributes are fully dependent on the entire primary key, eliminating any partial dependencies.

Q15

Single Choice

In PostgreSQL, which of the following tables violates the Second Normal Form (2NF) principle?

A table where a non-key attribute is dependent only on part of a composite primary key. A table where all non-key attributes are dependent on the entire primary key. A table with no transitive dependencies.A table where each column is atomic.

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

Q16

Single Choice

Consider the following PostgreSQL table employees_projects:

SQL Code

CREATE TABLE employees_projects (
 employee_id INT,
 project_id INT,
 project_name VARCHAR(255),
 PRIMARY KEY (employee_id, project_id)
);
Is this table in 2NF, and why?

Yes, because project_name is dependent on both employee_id and project_id No, because project_name should be in a separate table. Yes, because there are no partial dependenciesNo, because project_name is only dependent on project_id

The table violates 2NF because project_name is only dependent on project_id, not the full composite key.

Q17

Single Choice

Given the following PostgreSQL table sales_data:

SQL Code

CREATE TABLE sales_data (
 product_id INT,
 region_id INT,
 sales_amount NUMERIC,
 region_name VARCHAR(255),
 PRIMARY KEY (product_id, region_id)
);
What should be done to bring this table into 2NF?

Remove region_name from the table. Create a new table for region_name. Make sales_amount part of the primary key.Split the table into multiple tables.

To satisfy 2NF, region_name should be moved to a separate table since it is dependent on region_id only and not the full composite key.

Q18

Single Choice

In PostgreSQL, if a table is already in 1NF but not in 2NF, which of the following actions would typically be required?

Add more columns to the primary key. Eliminate transitive dependencies. Break down the table into smaller tablesEnsure all non-key attributes depend on the entire primary key

Moving to 2NF from 1NF generally requires ensuring that all non-key attributes are fully functionally dependent on the entire primary key.

Q19

Single Choice

Consider a PostgreSQL table product_orders:

SQL Code

CREATE TABLE product_orders (
 order_id INT,
 product_id INT,
 order_date DATE,
 product_name VARCHAR(255),
 PRIMARY KEY (order_id, product_id)
);
Why is this table not in 2NF?

Because order_date is not dependent on product_id. Because product_name is dependent only on product_id. Because the primary key is not a composite key.Because product_name should be a part of the primary key.

The table violates 2NF because product_name is dependent on only part of the composite primary key (product_id), not the entire key.

Q20

Single Choice

In PostgreSQL, consider a table inventory with the following structure:

SQL Code

CREATE TABLE inventory (
 warehouse_id INT,
 product_id INT,
 product_name VARCHAR(255),
 quantity INT,
 PRIMARY KEY (warehouse_id, product_id)
);
Which of the following changes should be made to ensure this table is in 2NF?

Remove product_name and create a separate table for product details. Add a composite key that includes product_name Change the primary key to include quantityMerge warehouse_id and product_id into a single key.

To satisfy 2NF, product_name should be removed from the inventory table and placed in a separate table since it is only dependent on product_id and not on the composite primary key.

Q21

Multiple Choice

Which of the following best describes Second Normal Form (2NF) in PostgreSQL?

SQL Code

Consider a table 'student_courses' with columns 'student_id', 'student_name', 'course_id', and 'course_name'. Normalize it to 2NF by ensuring that non-prime attributes are fully dependent on the whole composite primary key.

CREATE TABLE students (
 student_id SERIAL PRIMARY KEY,
 student_name VARCHAR(100)
);

CREATE TABLE courses (
 course_id SERIAL PRIMARY KEY,
 course_name VARCHAR(100)
);

CREATE TABLE student_courses (
 student_id INT REFERENCES students(student_id),
 course_id INT REFERENCES courses(course_id),
 PRIMARY KEY (student_id, course_id)
);

Eliminates partial dependencies. Ensures every attribute is dependent on the entire primary key. Removes transitive dependencies.Ensures no multivalued dependencies exist.

Second Normal Form (2NF) requires that all non-prime attributes are fully functionally dependent on the entire primary key, not just part of it. This eliminates partial dependencies, ensuring better data integrity.

Q22

Multiple Choice

Which scenario violates Second Normal Form (2NF) in PostgreSQL?

SQL Code

Consider a table 'employee_projects' with columns 'employee_id', 'project_id', 'project_name', and 'employee_name'. Normalize it to 2NF by ensuring non-prime attributes do not depend on only part of a composite key.

CREATE TABLE employees (
 employee_id SERIAL PRIMARY KEY,
 employee_name VARCHAR(100)
);

CREATE TABLE projects (
 project_id SERIAL PRIMARY KEY,
 project_name VARCHAR(100)
);

CREATE TABLE employee_projects (
 employee_id INT REFERENCES employees(employee_id),
 project_id INT REFERENCES projects(project_id),
 PRIMARY KEY (employee_id, project_id)
);

A non-prime attribute depends only on a part of a composite primary key. A non-prime attribute depends on the entire primary key. The table has no partial dependencies.The table is in First Normal Form (1NF) but not in 2NF.

A table violates 2NF if it has partial dependencies, where a non-prime attribute is dependent on only part of a composite primary key. This means that the attribute does not rely on the entire key, leading to redundancy.

Q23

Multiple Choice

How would you convert a table from 1NF to 2NF in PostgreSQL?

SQL Code

Given a table 'order_items' with columns 'order_id', 'product_id', 'product_name', and 'order_date', normalize it to 2NF by removing any partial dependencies.

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

CREATE TABLE products (
 product_id SERIAL PRIMARY KEY,
 product_name VARCHAR(100)
);

CREATE TABLE order_items (
 order_id INT REFERENCES orders(order_id),
 product_id INT REFERENCES products(product_id),
 PRIMARY KEY (order_id, product_id)
);

Remove any partial dependencies by splitting the table. Create separate tables for attributes that are dependent on only part of the composite key. Ensure that every non-prime attribute depends on the whole primary key.Ensure that no non-prime attribute is dependent on only part of a composite key.

To convert a table from 1NF to 2NF, you must remove partial dependencies by ensuring that all non-prime attributes are fully dependent on the entire composite key, not just part of it.

Q24

Multiple Choice

Which of the following is a characteristic of a table in 2NF in PostgreSQL?

SQL Code

Consider a table 'course_enrollments' with columns 'student_id', 'course_id', 'enrollment_date', and 'course_instructor'. Normalize it to 2NF by ensuring all non-prime attributes are dependent on the entire primary key.

CREATE TABLE courses (
 course_id SERIAL PRIMARY KEY,
 course_instructor VARCHAR(100)
);

CREATE TABLE students (
 student_id SERIAL PRIMARY KEY,
 student_name VARCHAR(100)
);

CREATE TABLE course_enrollments (
 student_id INT REFERENCES students(student_id),
 course_id INT REFERENCES courses(course_id),
 enrollment_date DATE,
 PRIMARY KEY (student_id, course_id)
);

No partial dependencies exist. All non-prime attributes are fully dependent on the entire primary key. The table must be in 1NF first.The table must not have any multivalued dependencies.

A table in 2NF has no partial dependencies, meaning that every non-prime attribute is fully dependent on the whole primary key. This ensures that data is stored in a more structured and efficient manner.

Q25

Multiple Choice

What are the steps to achieve Second Normal Form (2NF) in a PostgreSQL database?

SQL Code

Normalize a table 'sales_orders' with columns 'order_id', 'product_id', 'product_description', and 'order_quantity' to 2NF by eliminating partial dependencies.

CREATE TABLE products (
 product_id SERIAL PRIMARY KEY,
 product_description TEXT
);

CREATE TABLE orders (
 order_id SERIAL PRIMARY KEY,
 order_quantity INT
);

CREATE TABLE sales_orders (
 order_id INT REFERENCES orders(order_id),
 product_id INT REFERENCES products(product_id),
 PRIMARY KEY (order_id, product_id)
);

Ensure the table is in 1NF. Remove any partial dependencies. Split the table into smaller tables where necessary.Ensure that all non-prime attributes depend on the entire primary key.

To achieve 2NF, first ensure that the table is in 1NF, then remove any partial dependencies by splitting the table into smaller tables where non-prime attributes depend on the entire primary key.

Q26

Multiple Choice

How does 2NF improve data integrity in PostgreSQL?

SQL Code

Normalize a table 'teacher_assignments' with columns 'teacher_id', 'subject_id', 'teacher_name', and 'subject_name' to 2NF by removing any partial dependencies.

CREATE TABLE teachers (
 teacher_id SERIAL PRIMARY KEY,
 teacher_name VARCHAR(100)
);

CREATE TABLE subjects (
 subject_id SERIAL PRIMARY KEY,
 subject_name VARCHAR(100)
);

CREATE TABLE teacher_assignments (
 teacher_id INT REFERENCES teachers(teacher_id),
 subject_id INT REFERENCES subjects(subject_id),
 PRIMARY KEY (teacher_id, subject_id)
);

Reduces redundancy by eliminating partial dependencies. Ensures data consistency by storing each piece of information in only one place. Simplifies the database schema.Prevents update anomalies by ensuring that data is not duplicated.

2NF improves data integrity by eliminating partial dependencies, which reduces redundancy and ensures that each piece of data is stored in only one place, thereby reducing the risk of data anomalies.

Q27

Multiple Choice

What is a potential drawback of applying 2NF in PostgreSQL?

SQL Code

Consider a table 'product_reviews' with columns 'review_id', 'product_id', 'reviewer_name', and 'review_text'. Normalize it to 2NF by removing partial dependencies, but consider the potential increase in table joins.

CREATE TABLE products (
 product_id SERIAL PRIMARY KEY,
 product_name VARCHAR(100)
);

CREATE TABLE reviews (
 review_id SERIAL PRIMARY KEY,
 reviewer_name VARCHAR(100),
 review_text TEXT
);

CREATE TABLE product_reviews (
 product_id INT REFERENCES products(product_id),
 review_id INT REFERENCES reviews(review_id),
 PRIMARY KEY (product_id, review_id)
);

Increased complexity in queries due to more table joins. Possible performance degradation due to additional joins. Simplified database schema.Reduced data redundancy.

While 2NF reduces redundancy and improves data integrity, it can also lead to more complex queries due to an increased number of table joins. This can potentially affect database performance.

Q28

Multiple Choice

Which of the following is an example of a table that is not in 2NF?

SQL Code

Consider a table 'book_authors' with columns 'book_id', 'author_id', 'book_title', and 'author_name'. Identify the partial dependency and normalize it to 2NF.

CREATE TABLE books (
 book_id SERIAL PRIMARY KEY,
 book_title VARCHAR(100)
);

CREATE TABLE authors (
 author_id SERIAL PRIMARY KEY,
 author_name VARCHAR(100)
);

CREATE TABLE book_authors (
 book_id INT REFERENCES books(book_id),
 author_id INT REFERENCES authors(author_id),
 PRIMARY KEY (book_id, author_id)
);

A table with partial dependencies. A table with only one primary key. A table in 1NF but not in 2NF.A table where non-prime attributes are dependent on only a part of the composite key.

A table is not in 2NF if it has partial dependencies, where a non-prime attribute depends on only a part of the composite key, leading to redundancy and potential data anomalies.

Q29

Multiple Choice

What is the impact of not applying 2NF in a PostgreSQL database?

SQL Code

Normalize a table 'department_employees' with columns 'department_id', 'employee_id', 'department_name', and 'employee_name' to 2NF to eliminate partial dependencies.

CREATE TABLE departments (
 department_id SERIAL PRIMARY KEY,
 department_name VARCHAR(100)
);

CREATE TABLE employees (
 employee_id SERIAL PRIMARY KEY,
 employee_name VARCHAR(100)
);

CREATE TABLE department_employees (
 department_id INT REFERENCES departments(department_id),
 employee_id INT REFERENCES employees(employee_id),
 PRIMARY KEY (department_id, employee_id)
);

Increased redundancy and storage requirements. Increased likelihood of data anomalies. Simplified database schema.More complex queries due to additional table joins.

Not applying 2NF can lead to partial dependencies, which result in redundancy, increased storage requirements, and potential data anomalies during insert, update, or delete operations.

Q30

Multiple Choice

How can a table in 2NF improve database performance in PostgreSQL?

SQL Code

Normalize a table 'student_grades' with columns 'student_id', 'subject_id', 'grade', and 'student_name' to 2NF by removing partial dependencies.

CREATE TABLE students (
 student_id SERIAL PRIMARY KEY,
 student_name VARCHAR(100)
);

CREATE TABLE subjects (
 subject_id SERIAL PRIMARY KEY,
 subject_name VARCHAR(100)
);

CREATE TABLE student_grades (
 student_id INT REFERENCES students(student_id),
 subject_id INT REFERENCES subjects(subject_id),
 grade CHAR(1),
 PRIMARY KEY (student_id, subject_id)
);

Reduces data redundancy. Ensures efficient data storage. Simplifies database schema.Improves query performance by reducing unnecessary data duplication.

By applying 2NF, you reduce data redundancy and ensure that data is stored more efficiently, which can improve database performance, particularly in terms of data retrieval and update operations.

PostgreSQL Database Quiz Questions