Test Your PostgreSQL Normalization Knowledge with this Quiz

Progress

True / False

In PostgreSQL, a table is in First Normal Form (1NF) if it contains duplicate rows.

True False

A table is in 1NF if it does not contain duplicate rows and each row is unique.

True / False

In PostgreSQL, a table must have a primary key to be in First Normal Form (1NF).

True False

Having a primary key ensures that each row is unique, which is a requirement for 1NF.

True / False

In PostgreSQL, a table is in First Normal Form (1NF) if it contains only atomic (indivisible) values.

True False

1NF requires that all columns contain atomic values, meaning each value is indivisible.

True / False

In PostgreSQL, a table with repeating groups or arrays in a column is not in First Normal Form (1NF).

True False

Repeating groups or arrays violate the 1NF requirement of having atomic values in each column.

True / False

In PostgreSQL, a table with multi-valued attributes in a single column is considered normalized to First Normal Form (1NF).

True False

Multi-valued attributes in a single column violate the principle of atomicity required by 1NF.

True / False

In PostgreSQL, a table that meets the requirements of First Normal Form (1NF) can still have redundancy issues.

True False

1NF focuses on the atomicity of values and uniqueness of rows but does not address redundancy or other normalization issues which are dealt with in higher normal forms.

True / False

In PostgreSQL, converting a table to First Normal Form (1NF) may require splitting a column into multiple columns.

True False

To achieve 1NF, a table might need to be restructured by splitting columns containing multiple values into separate columns each containing a single value.

True / False

In PostgreSQL, tables that are not in First Normal Form (1NF) can still be used efficiently in certain applications.

True False

While not ideal for relational database design, non-1NF tables can still be used efficiently in some applications, though they may complicate querying and data integrity.

True / False

In PostgreSQL, a composite key can violate the First Normal Form (1NF).

True False

A composite key does not violate 1NF as long as the composite key itself is atomic and the table meets other 1NF requirements.

Q10

True / False

In PostgreSQL, ensuring a table is in First Normal Form (1NF) includes making sure that each column contains only scalar values, such as integers or strings, but not arrays or records.

True False

1NF requires that each column in a table contains only scalar values (atomic values), meaning no arrays or records should be present in any column.

Q11

Single Choice

What is the primary purpose of database normalization in PostgreSQL?

To increase redundancy To organize data efficiently by reducing redundancy To make database queries slowerTo eliminate the need for indexes

The primary goal of normalization is to reduce redundancy and dependency by organizing fields and table relations. This helps to maintain data integrity and improve efficiency in PostgreSQL databases.

Q12

Single Choice

Which of the following is the first step in the normalization process?

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

The first step in normalization is to ensure that the database is in First Normal Form (1NF), which involves eliminating repeating groups and ensuring that each column contains atomic values.

Q13

Single Choice

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

The table must be in First Normal Form (1NF) All non-key attributes must be fully dependent on the primary key The table must be in Third Normal Form (3NF)The table must eliminate transitive dependencies

For a table to be in Second Normal Form (2NF), it must first be in 1NF, and all non-key attributes must be fully functionally dependent on the primary key, which reduces partial dependency.

Q14

Single Choice

Consider the following PostgreSQL table orders:

SQL Code

CREATE TABLE orders (
 order_id SERIAL PRIMARY KEY,
 customer_id INT,
 product_id INT,
 order_date DATE,
 customer_name VARCHAR(255)
);
Which normal form is this table violating, assuming customer_name should be part of a separate table?

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

The table violates Third Normal Form (3NF) because customer_name depends on customer_id, not order_id. This creates a transitive dependency, which should be eliminated by moving customer_name to a separate customers table.

Q15

Single Choice

Which of the following PostgreSQL SQL queries demonstrates converting a table to Second Normal Form (2NF)?

Splitting a table with partial dependencies into two tables Creating a new table to store transitive dependencies Adding a unique constraint to a columnRemoving all foreign key constraints

Converting a table to 2NF involves removing partial dependencies by splitting the table into two or more tables, ensuring that each non-key attribute is fully dependent on the primary key.

Q16

Single Choice

In PostgreSQL, which normalization form requires that a table should not have any partial dependency on a composite primary key?

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

Second Normal Form (2NF) requires that there be no partial dependency on a composite primary key, meaning all non-key attributes must be fully dependent on the entire primary key.

Q17

Single Choice

Given the following PostgreSQL table students:

SQL Code

CREATE TABLE students (
 student_id SERIAL PRIMARY KEY,
 student_name VARCHAR(255),
 course_id INT,
 course_name VARCHAR(255),
 instructor_name VARCHAR(255)
);
What is the highest normal form this table can be converted to, without any changes?

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

This table is in 1NF because it has atomic columns. However, it violates 2NF due to partial dependency, as course_name and instructor_name depend on course_id rather than student_id. Therefore, it cannot be considered 2NF or higher without normalization.

Q18

Single Choice

Which SQL code snippet would convert the students table above into Third Normal Form (3NF) in PostgreSQL

CREATE TABLE courses ( course_id SERIAL PRIMARY KEY, course_name VARCHAR(255), instructor_name VARCHAR(255) ); ALTER TABLE students DROP COLUMN course_name, instructor_name; ALTER TABLE students ADD FOREIGN KEY (course_id) REFERENCES courses(course_id); ALTER TABLE students ADD UNIQUE (student_name); CREATE TABLE students_courses ( student_id INT, course_id INT, PRIMARY KEY (student_id, course_id) );CREATE TABLE instructors ( instructor_id SERIAL PRIMARY KEY, instructor_name VARCHAR(255) ); ALTER TABLE students DROP COLUMN instructor_name; ALTER TABLE students ADD FOREIGN KEY (instructor_name) REFERENCES instructors(instructor_name);

This SQL code snippet moves course_name and instructor_name to a separate courses table, removing the transitive dependency, thus converting the table into Third Normal Form (3NF).

Q19

Single Choice

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

Every relation in 2NF is also in BCNF Every relation in 3NF is also in BCNF Every relation in BCNF is also in 3NFBCNF is the same as 2NF

BCNF is a stronger version of 3NF, meaning that if a table is in BCNF, it is also in 3NF. BCNF addresses certain anomalies that 3NF does not by ensuring that every determinant is a candidate key.

Q20

Single Choice

In PostgreSQL, if a table is in Third Normal Form (3NF) but not in Boyce-Codd Normal Form (BCNF), which scenario is likely to occur?

A non-trivial functional dependency exists where the left side is not a superkey There are partial dependencies on a composite key There are multi-valued dependenciesThe table contains repeating groups

If a table is in 3NF but not in BCNF, it means that there is a non-trivial functional dependency where the determinant (left side) is not a superkey. BCNF requires that every determinant be a candidate key, addressing certain issues that 3NF does not cover.

Q21

Multiple Choice

What is the purpose of normalization in a PostgreSQL database?

SQL Code

Given a table 'orders' with redundant customer data, normalize it into separate tables for customers and orders.

CREATE TABLE customers (
 customer_id SERIAL PRIMARY KEY,
 customer_name VARCHAR(100),
 customer_email VARCHAR(100)
);

CREATE TABLE orders (
 order_id SERIAL PRIMARY KEY,
 order_date DATE,
 customer_id INT REFERENCES customers(customer_id)
);

INSERT INTO customers (customer_name, customer_email) VALUES ('John Doe', 'john.doe@example.com');
INSERT INTO orders (order_date, customer_id) VALUES ('2024-08-20', 1);

To reduce data redundancy. To improve data integrity. To simplify database queries.To speed up data retrieval.

Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. It typically involves dividing a database into two or more tables and defining relationships between them.

Q22

Multiple Choice

Which normalization form eliminates partial dependency in a PostgreSQL table?

SQL Code

Given a table 'order_details' with columns 'order_id', 'product_id', and 'product_price', normalize it to 2NF by creating separate tables for orders and products.

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

CREATE TABLE products (
 product_id SERIAL PRIMARY KEY,
 product_name VARCHAR(100),
 product_price NUMERIC(10, 2)
);

CREATE TABLE order_details (
 order_detail_id SERIAL PRIMARY KEY,
 order_id INT REFERENCES orders(order_id),
 product_id INT REFERENCES products(product_id)
);

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

The Second Normal Form (2NF) eliminates partial dependencies, meaning that no non-prime attribute should be functionally dependent on a part of a candidate key.

Q23

Multiple Choice

What is the main characteristic of a table in Third Normal Form (3NF) in PostgreSQL?

SQL Code

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)
);

All non-key attributes are fully dependent on the primary key. No transitive dependencies exist. All columns are dependent on the primary key.The table contains no partial dependencies.

A table is in Third Normal Form (3NF) if it is in 2NF and all of its attributes are functionally dependent only on the primary key, meaning there are no transitive dependencies.

Q24

Multiple Choice

What does Boyce-Codd Normal Form (BCNF) achieve that 3NF does not in PostgreSQL?

SQL Code

CREATE TABLE project_managers (
 project_manager_id SERIAL PRIMARY KEY,
 project_manager_name VARCHAR(100)
);

CREATE TABLE projects (
 project_id SERIAL PRIMARY KEY,
 project_manager_id INT REFERENCES project_managers(project_manager_id)
);

Eliminates all redundancy based on functional dependencies. Ensures every determinant is a candidate key. Removes partial dependencies.Is always equivalent to 3NF.

Boyce-Codd Normal Form (BCNF) is a stricter version of 3NF. A table is in BCNF if it is in 3NF and every determinant is a candidate key, ensuring that there are no anomalies caused by dependencies.

Q25

Multiple Choice

Which normalization form eliminates transitive dependency in PostgreSQL?

SQL Code

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

CREATE TABLE student_grades (
 grade_id SERIAL PRIMARY KEY,
 student_id INT,
 course_id INT REFERENCES courses(course_id),
 grade VARCHAR(2)
);

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

Third Normal Form (3NF) eliminates transitive dependencies, which occur when a non-prime attribute depends on another non-prime attribute.

Q26

Multiple Choice

How would you normalize a table with repeating groups in PostgreSQL?

SQL Code

CREATE TABLE sales (
 sales_id SERIAL PRIMARY KEY,
 product_name VARCHAR(100)
);

INSERT INTO sales (sales_id, product_name) VALUES (1, 'Product A');
INSERT INTO sales (sales_id, product_name) VALUES (1, 'Product B');
INSERT INTO sales (sales_id, product_name) VALUES (1, 'Product C');

By creating separate rows for each repeating group. By creating separate tables for each repeating group. By combining the repeating groups into a single column.By ensuring each column has a unique value.

First Normal Form (1NF) removes repeating groups by ensuring that each column contains only atomic (indivisible) values and that each entry in the column is unique.

Q27

Multiple Choice

What does the process of denormalization involve in PostgreSQL?

SQL Code

CREATE TABLE order_summary (
 order_id SERIAL PRIMARY KEY,
 order_date DATE,
 customer_name VARCHAR(100),
 customer_email VARCHAR(100)
);

INSERT INTO order_summary (order_date, customer_name, customer_email)
SELECT o.order_date, c.customer_name, c.customer_email
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

Combining normalized tables or columns. Removing indexes from tables. Splitting tables to reduce redundancy.Optimizing queries by reducing the number of joins.

Denormalization is the process of combining tables or columns that have been separated through normalization to reduce the number of joins in queries, improving performance but at the cost of potential redundancy and update anomalies.

Q28

Multiple Choice

In PostgreSQL, how would you address a normalization issue where an attribute depends on only part of a composite key?

SQL Code

Normalize a table 'employee_projects' with columns 'employee_id', 'project_id', and 'project_lead' to 2NF by ensuring each non-prime attribute is fully functionally dependent on the whole key.

```sql
CREATE TABLE projects (
 project_id SERIAL PRIMARY KEY,
 project_lead VARCHAR(100)
);

CREATE TABLE employee_projects (
 employee_project_id SERIAL PRIMARY KEY,
 employee_id INT,
 project_id INT REFERENCES projects(project_id)
);
```

By splitting the table to eliminate partial dependencies. By adding more columns to the primary key. By creating a single table with all dependencies.By removing the composite key entirely.

To address partial dependency, which violates 2NF, you separate the dependent attributes into a different table where they depend on the entire primary key, not just part of it.

Q29

Multiple Choice

What is the main goal of Fourth Normal Form (4NF) in PostgreSQL?

SQL Code

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

CREATE TABLE stores (
 store_id SERIAL PRIMARY KEY,
 store_location VARCHAR(100)
);

CREATE TABLE product_sales (
 product_id INT REFERENCES products(product_id),
 store_id INT REFERENCES stores(store_id),
 sales_quantity INT,
 PRIMARY KEY (product_id, store_id)
);

Eliminating multi-valued dependencies. Ensuring that every determinant is a candidate key. Removing partial dependencies.Eliminating transitive dependencies.

Fourth Normal Form (4NF) deals with multi-valued dependencies. A table is in 4NF if it is in BCNF and it has no multi-valued dependencies.

Q30

Multiple Choice

What is a common trade-off when applying higher levels of normalization in PostgreSQL?

SQL Code

SELECT e.employee_name, d.department_name, s.salary_amount
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN salaries s ON e.employee_id = s.employee_id;

Increased query complexity due to more joins. Reduced data redundancy. Improved data integrity.Decreased query performance in some cases.

As you apply higher levels of normalization, the database becomes more efficient in terms of data integrity and storage. However, this can lead to more complex queries and potential performance overhead due to the increased number of joins.

PostgreSQL Database Quiz Questions