Test Your PostgreSQL Third Normal Form Knowledge with this Quiz

Progress

True / False

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

True False

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

True / False

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

True False

3NF requires that there are no transitive dependencies, meaning non-key attributes must not depend on other non-key attributes.

True / False

In PostgreSQL, a table with a single-column primary key and no transitive dependencies is automatically in Third Normal Form (3NF).

True False

If there are no transitive dependencies and the table meets 2NF, it is automatically in 3NF.

True / False

In PostgreSQL, achieving Third Normal Form (3NF) often involves moving columns to new tables to eliminate transitive dependencies.

True False

To achieve 3NF, columns involved in transitive dependencies are moved to new tables where they can form direct relationships with primary keys.

True / False

In PostgreSQL, a table in Third Normal Form (3NF) cannot have any redundant data.

True False

While 3NF reduces redundancy by eliminating transitive dependencies, it does not necessarily eliminate all redundancy. Higher normal forms address other types of redundancy.

True / False

In PostgreSQL, if a non-key attribute depends on another non-key attribute, the table is not in Third Normal Form (3NF).

True False

3NF requires that non-key attributes do not depend on other non-key attributes, only on the primary key.

True / False

In PostgreSQL, a table that meets the requirements of Boyce-Codd Normal Form (BCNF) is also in Third Normal Form (3NF).

True False

BCNF is a stricter version of 3NF. Therefore, if a table is in BCNF, it also satisfies the requirements of 3NF.

True / False

In PostgreSQL, a table with a composite primary key can be in Third Normal Form (3NF) even if some non-key attributes are partially dependent on the primary key.

True False

Partial dependencies violate 2NF. Since 3NF requires 2NF compliance and no transitive dependencies, partial dependencies are not allowed in 3NF.

True / False

In PostgreSQL, achieving Third Normal Form (3NF) ensures that all non-key attributes are only dependent on the primary key.

True False

3NF ensures that non-key attributes are fully functionally dependent only on the primary key and not on any other non-key attributes.

Q10

True / False

In PostgreSQL, a table in Third Normal Form (3NF) can still have multi-valued attributes as long as they do not create transitive dependencies.

True False

Multi-valued attributes violate 1NF, which is a prerequisite for 3NF. Therefore, a table in 3NF cannot have multi-valued attributes.

Q11

Single Choice

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

It must be in First Normal Form (1NF). It must have no transitive dependencies. It must have a composite primary key.It must contain no foreign keys.

Third Normal Form (3NF) requires that the table is in 2NF and that there are no transitive dependencies, which means non-key attributes should not depend on other non-key attributes.

Q12

Single Choice

A table in PostgreSQL is in 3NF if it is in 2NF and:

Contains only foreign keys. Has a single primary key. There are no transitive dependencies.All columns are atomic.

To be in 3NF, the table must eliminate transitive dependencies in addition to being in 2NF.

Q13

Single Choice

In PostgreSQL, what is eliminated in a table to achieve 3NF?

Duplicate rows Partial dependencies Transitive dependenciesComposite keys

Third Normal Form (3NF) eliminates transitive dependencies, where a non-prime attribute is dependent on another non-prime attribute.

Q14

Single Choice

Consider the following PostgreSQL table schema:

SQL Code

CREATE TABLE Orders (
 OrderID INT PRIMARY KEY,
 CustomerID INT,
 OrderDate DATE,
 CustomerAddress VARCHAR(255)
);
Which of the following issues would prevent this table from being in 3NF?

OrderDate should be in a separate table. CustomerID and CustomerAddress should not be in the same table. OrderID is not a composite key.The table has no foreign key constraint.

The presence of CustomerAddress dependent on CustomerID indicates a transitive dependency, which must be removed to satisfy 3NF.

Q15

Single Choice

Given a PostgreSQL table Employees defined as:

SQL Code

CREATE TABLE Employees (
 EmployeeID SERIAL PRIMARY KEY,
 DepartmentID INT,
 DepartmentName VARCHAR(100),
 ManagerID INT
);
How can this table be brought into 3NF?

Split the table into Employees and Departments tables. Remove ManagerID from the table. Change EmployeeID to a composite key.Add a unique constraint on DepartmentName

DepartmentName is dependent on DepartmentID, which creates a transitive dependency. To achieve 3NF, these should be separated into different tables.

Q16

Single Choice

A PostgreSQL table Books contains columns BookID, AuthorID, AuthorName, and Title. To convert this table to 3NF, what step should be taken?

Remove AuthorName from the table. Create a separate Authors table. Add a foreign key constraint on AuthorIDUse a composite primary key for BookID and AuthorID.

AuthorName is dependent on AuthorID, leading to a transitive dependency. To achieve 3NF, AuthorName should be in a separate Authors table.

Q17

Single Choice

Given the following PostgreSQL schema, which is NOT in 3NF:

SQL Code

CREATE TABLE Courses (
 CourseID INT PRIMARY KEY,
 InstructorID INT,
 InstructorName VARCHAR(100),
 CourseName VARCHAR(100)
);
How would you normalize this schema to meet 3NF requirements?

Remove InstructorName from the table. Add a composite primary key of CourseID and InstructorID. Split into Courses and Instructors tables.Add a unique constraint on InstructorName.

InstructorName is dependent on InstructorID, creating a transitive dependency that violates 3NF. These should be separated into different tables.

Q18

Single Choice

Which of the following PostgreSQL table designs conforms to 3NF?

A table with a primary key and all other attributes directly dependent on it. A table with a composite primary key and one non-key attribute. A table where a non-key attribute is dependent on another non-key attributeA table with a single primary key and foreign keys dependent on it.

3NF requires that all non-key attributes must depend directly on the primary key, with no transitive dependencies.

Q19

Single Choice

In a PostgreSQL database, if you have a table with columns ProductID, ProductName, CategoryID, and CategoryName, what would be the correct approach to achieve 3NF?

Combine ProductID and CategoryID into a composite key. Remove CategoryName from the table and store it in a separate table. Ensure CategoryID is a foreign key.Normalize the table to include only atomic values.

CategoryName is dependent on CategoryID, not ProductID, creating a transitive dependency. To achieve 3NF, CategoryName should be stored in a separate Categories table.

Q20

Single Choice

In PostgreSQL, a table is in 3NF if:

It is in 2NF and every non-prime attribute is non-transitively dependent on the primary key. It is in 1NF and there are no duplicate rows It is in 2NF and there are no partial dependencies.It has a composite primary key.

3NF is achieved when a table is in 2NF and all non-prime attributes are non-transitively dependent on the primary key.

Q21

Multiple Choice

Which of the following best describes Third Normal Form (3NF) in PostgreSQL?

SQL Code

Consider a table 'employee_details' with columns 'employee_id', 'employee_name', 'department_id', 'department_name', and 'location'. Normalize it to 3NF by ensuring that all non-key attributes are non-transitively dependent on the primary key.

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

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

Eliminates transitive dependencies. Ensures every non-key attribute is directly dependent on the primary key. Removes partial dependencies.Ensures that all non-key attributes depend only on the primary key.

Third Normal Form (3NF) ensures that all non-key attributes are non-transitively dependent on the primary key. This means that attributes must depend directly on the primary key, rather than through another non-key attribute, reducing redundancy and eliminating transitive dependencies.

Q22

Multiple Choice

Which scenario violates Third Normal Form (3NF) in PostgreSQL?

SQL Code

Consider a table 'student_records' with columns 'student_id', 'student_name', 'advisor_id', 'advisor_name', and 'department_name'. Normalize it to 3NF by ensuring non-key attributes do not depend on other non-key attributes.

```sql
CREATE TABLE advisors (
 advisor_id SERIAL PRIMARY KEY,
 advisor_name VARCHAR(100),
 department_name VARCHAR(100)
);

CREATE TABLE students (
 student_id SERIAL PRIMARY KEY,
 student_name VARCHAR(100),
 advisor_id INT REFERENCES advisors(advisor_id)
);
```

A non-key attribute depends on another non-key attribute. A non-key attribute depends directly on the primary key. The table is in 2NF but not in 3NF.The table has no transitive dependencies.

A table violates 3NF if there is a transitive dependency, where a non-key attribute depends on another non-key attribute rather than directly on the primary key. This leads to redundancy and potential data anomalies.

Q23

Multiple Choice

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

SQL Code

Given a table 'project_assignments' with columns 'project_id', 'employee_id', 'employee_name', and 'department_name', normalize it to 3NF by removing any transitive dependencies.

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

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

CREATE TABLE projects (
 project_id SERIAL PRIMARY KEY
);

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

Remove any transitive dependencies. Ensure non-key attributes depend only on the primary key. Split the table into smaller tables if necessary.Ensure that no non-key attribute is dependent on another non-key attribute.

To convert a table from 2NF to 3NF, you must remove transitive dependencies by ensuring that non-key attributes depend directly on the primary key and not on another non-key attribute.

Q24

Multiple Choice

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

SQL Code

Consider a table 'supplier_orders' with columns 'order_id', 'supplier_id', 'supplier_name', and 'order_date'. Normalize it to 3NF by ensuring all non-key attributes are non-transitively dependent on the primary key.

```sql
CREATE TABLE suppliers (
 supplier_id SERIAL PRIMARY KEY,
 supplier_name VARCHAR(100)
);

CREATE TABLE orders (
 order_id SERIAL PRIMARY KEY,
 supplier_id INT REFERENCES suppliers(supplier_id),
 order_date DATE
);
```

No transitive dependencies exist. All non-key attributes are directly dependent on the primary key. The table must be in 2NF first.The table must not have any multivalued dependencies.

A table in 3NF has no transitive dependencies, meaning that every non-key attribute is directly dependent on the primary key, ensuring better data integrity and minimizing redundancy.

Q25

Multiple Choice

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

SQL Code

Normalize a table 'customer_orders' with columns 'order_id', 'customer_id', 'customer_address', and 'order_total' to 3NF by eliminating transitive dependencies.

```sql
CREATE TABLE customers (
 customer_id SERIAL PRIMARY KEY,
 customer_address TEXT
);

CREATE TABLE orders (
 order_id SERIAL PRIMARY KEY,
 customer_id INT REFERENCES customers(customer_id),
 order_total NUMERIC(10, 2)
);
```

Ensure the table is in 2NF. Remove any transitive dependencies. Split the table into smaller tables where necessary.Ensure that all non-key attributes depend directly on the primary key.

To achieve 3NF, first ensure that the table is in 2NF, then remove any transitive dependencies by ensuring that all non-key attributes depend only on the primary key and not on another non-key attribute.

Q26

Multiple Choice

How does 3NF improve data integrity in PostgreSQL?

SQL Code

Normalize a table 'product_sales' with columns 'sale_id', 'product_id', 'product_name', and 'salesperson_name' to 3NF by removing any transitive dependencies.

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

CREATE TABLE salespersons (
 salesperson_id SERIAL PRIMARY KEY,
 salesperson_name VARCHAR(100)
);

CREATE TABLE product_sales (
 sale_id SERIAL PRIMARY KEY,
 product_id INT REFERENCES products(product_id),
 salesperson_id INT REFERENCES salespersons(salesperson_id)
);
```

Reduces redundancy by eliminating transitive 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.

3NF improves data integrity by eliminating transitive 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 3NF in PostgreSQL?

SQL Code

Consider a table 'invoice_details' with columns 'invoice_id', 'product_id', 'product_description', and 'supplier_name'. Normalize it to 3NF by removing transitive dependencies, but consider the potential increase in table joins.

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

CREATE TABLE suppliers (
 supplier_id SERIAL PRIMARY KEY,
 supplier_name VARCHAR(100)
);

CREATE TABLE invoices (
 invoice_id SERIAL PRIMARY KEY,
 product_id INT REFERENCES products(product_id),
 supplier_id INT REFERENCES suppliers(supplier_id)
);
```

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

While 3NF 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 3NF?

SQL Code

Consider a table 'book_inventory' with columns 'book_id', 'book_title', 'publisher_name', and 'publisher_address'. Identify the transitive dependency and normalize it to 3NF.

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

CREATE TABLE publishers (
 publisher_id SERIAL PRIMARY KEY,
 publisher_name VARCHAR(100),
 publisher_address TEXT
);

CREATE TABLE book_inventory (
 book_id INT REFERENCES books(book_id),
 publisher_id INT REFERENCES publishers(publisher_id),
 PRIMARY KEY (book_id, publisher_id)
);
```

A table with transitive dependencies. A table with only one primary key. A table in 2NF but not in 3NF.A table where non-key attributes are dependent on another non-key attribute.

A table is not in 3NF if it has transitive dependencies, where a non-key attribute depends on another non-key attribute instead of directly on the primary key, leading to redundancy and potential data anomalies.

Q29

Multiple Choice

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

SQL Code

Normalize a table 'course_enrollments' with columns 'course_id', 'student_id', 'course_name', and 'instructor_name' to 3NF to eliminate transitive dependencies.

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

CREATE TABLE instructors (
 instructor_id SERIAL PRIMARY KEY,
 instructor_name VARCHAR(100)
);

CREATE TABLE course_enrollments (
 course_id INT REFERENCES courses(course_id),
 student_id INT REFERENCES students(student_id),
 instructor_id INT REFERENCES instructors(instructor_id),
 PRIMARY KEY (course_id, student_id)
);
```

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

Not applying 3NF can lead to transitive 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 3NF improve database performance in PostgreSQL?

SQL Code

Normalize a table 'employee_salaries' with columns 'employee_id', 'salary', 'department_name', and 'manager_name' to 3NF by removing transitive dependencies.

```sql
CREATE TABLE employees (
 employee_id SERIAL PRIMARY KEY,
 salary NUMERIC(10, 2)
);

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

CREATE TABLE managers (
 manager_id SERIAL PRIMARY KEY,
 manager_name VARCHAR(100)
);

CREATE TABLE employee_salaries (
 employee_id INT REFERENCES employees(employee_id),
 department_id INT REFERENCES departments(department_id),
 manager_id INT REFERENCES managers(manager_id),
 PRIMARY KEY (employee_id, department_id)
);
```

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

By applying 3NF, 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