D426 Normal Forms

Second Normal Form requires:

A. No partial dependency of non-key columns on part of a composite primary key.

B. Elimination of transitive dependencies.

C. That every column be atomic.

D. That primary keys are composite.

A. No partial dependency of non-key columns on part of a composite primary key. 2NF removes partial dependencies; B (transitive) is 3NF, C is about 1NF, D is not required.

What is normalization?

Normalization is the process of organizing a database so that data is stored in a way that reduces redundancy and avoids update anomalies.

____ is the process of organizing a database so that data is stored in a way that reduces redundancy and avoids update anomalies.

Normalization

What is denormalization?

The process of intentionally adding redundancy to a database design in order to make queries faster or simpler. When you combine tables or duplicate data to reduce the need for joins, usually to improve read performance. Used when speed matters more than perfect structure.

____ is the process of intentionally adding redundancy to a database design in order to make queries faster or simpler.

Denormalization

What is used when speed matters more than perfect structure?

Denormalization

Normalization

1NF

2NF

Denormalization

Third Normal Form requires that:

A. Every non-key column depends only on the primary key and not transitively on another non-key column.

B. There are no composite keys.

C. All columns are unique.

D. The table must be partitioned.

A. Every non-key column depends only on the primary key and not transitively on another non-key column.

3NF removes transitive dependencies so non-key columns are directly dependent on the primary key only.

A table is in 1NF if:

A. It has no NULLs anywhere.

B. Every cell contains exactly one value and the table has a primary key.

C. No non-key column depends on part of a composite key.

D. It has no repeated columns.

B. Every cell contains exactly one value and the table has a primary key.

1NF requires atomic (single) values per cell and a primary key; A and D are incomplete, C describes 2NF/3NF concerns.

What normal form has the fewest requirements and is the most basic level of normalization?

1NF

2NF

3NF

4NF

1NF

A table that is in 2NF and contains no transitive dependencies is said to be in ____.

A. 1NF
B. 2NF
C. 3NF
D. 4NF

C. 2NF is a more strict form of normalization than 1NF.

A relation is not in 1NF if _______.

A. it has multiple candidate keys
B. all the key attributes are defined
C. there are repeating groups in the table
D. all attributes are dependent on the primary key

C. 1NF requires each column to have a unique name and each cell to only contain a single value.

Which of the following is a valid reason to denormalize a design?

A. To make the logical design more elegant.

B. To improve query performance for read-heavy workloads when justified.

C. To avoid using primary keys.

D. To eliminate the need for indexes.

B. To improve query performance for read-heavy workloads when justified.

Denormalization trades redundancy for read performance in specific cases; it’s not done for elegance, and it doesn’t remove index needs.

What is boyce codd normal form?

Every attribute that determines another attribute must be a key (something that uniquely identifies rows).

Which normal form eliminates join dependencies and associated redundancy?

5th normal form

Another name for 5NF is what?

Project-Join Normal Form (PJNF)

A table is in 5NF if it is in 4NF with join dependencies and what condition holds?

Every join dependency is implied by candidate keys.

All 4NF tables have dependencies that rely on the same superkey. This means they have trivial lossless joins, which are implied by the candidate keys. Trivial lossless joins are when you combine a decomposed table, you lose no data. The data ALL depended on the superkey.
One entity → one key → all attributes depend on that key

Which normal form eliminates multivalued dependencies and associated redundancy?

4th normal form

A table is in 4NF if it is in 3NF/BCNF and what additional condition is met?

It has no non-trivial multi-valued dependencies except those where the determinant is a superkey.

Denormalization never results in second-normal-form tables.T/F?

False.

Denormalization can introduce a dependency of a column on another non-key column, resulting in a second-normal-form table that is no longer in third normal form.

What normalization is this table in?

1NF.

The PK is (OrderID, ProductID). Because one order can have multiple products on it, that means OrderID will be listed several times and ProductID will be listed several times. So the only way to make the table unique is to combine them both.

But ProductName relies only on ProductID, which is a partial dependency. That violates 2NF.

What normalization is this table in?

2NF.

The PK of Employee is EmpID. There are no partial dependencies.

It is not 3NF because not all columns depend on the primary key.

What normalization is this table in?

1NF

One student can take many courses so StudentID will be repeated, so it cannot be a PK by itself. So the PK is (StudentID, CourseID).

The table is not 2NF because of the partial dependency from CourseTitle and CourseID.

What normalization is this table in?

2NF.

One student has one advisor. PK is StudentID. There are no partial dependencies.

Each teacher is associated with exactly one campus. The address in the table is the address for the campus where the teacher teaches. What changes would make this third normal form (3NF)? 

Combine the FirstName and LastName columns into a single column.

Combine the Campus, Address, City, State, Country, and PostalCode columns into a single column.

Create a separate table for campus address information. Use the Campus as the primary key. Add a TeacherID column to the table and relate it to the Teacher ID column in the Teachers table.

Create a separate table for campus address information. Use Campus as the primary key for the table. Create a foreign key in the Teachers table that relates teacher to the campus.

Create a separate table for campus address information. Use Campus as the primary key for the table. Create a foreign key in the Teachers table that relates teacher to the campus.

The PK is TeacherID. Campus depends on Teacher, but the location info depends on only Campus. That’s a transitive dependency which violates 3NF. So the location info needs to be made its own table. Teacher will have Campus as a foreign key referencing Location Campus which will be Location’s primary key.

What normalization is this table in?

UNF because of repeating groups. Activity Name and Activity ID.

What normalization is this table in?

UNF.

It has repeating groups in it (Course) which violates 1NF.

What normalization is this table in?

2NF.

The PK is Activity ID. There are no partial dependencies.

It is not 3NF because not all attributes depend on a primary key or are candidate keys.

What normalization is this table in?

1NF

The PK is Invoice Number and Item Number. The Item Name depends on the Item Number which is a partial dependency and violates 2NF.

What normalization is this table in?

An employee can have multiple skills and one skill can belong to any employee. The PK is (EmpID, Skill).

CertificationDate doesn’t rely on only EmpID, or only Skill. It relies on both (EmpID, Skill). There are no partial dependencies.

There are no transitive dependencies where a non-key depends on a non-key so it satisfies 3NF.

For BCNF, the left side must be a superkey. (EmpID, Skill) → CertificationDate satisfies BCNF.

Denormalization intentionally introduces redundancy, resulting in first- or second-normal-form tables. T/F?

True.

Denormalization improves performance of all SELECT queries. T/F?

False.

Denormalization typically improves performance of join queries.

Is this 4NF?: A table where StudentName determines multiple independent values of Courses and Hobbies.

No. A student can take multiple courses and a student can have many hobbies. StudentName is not assumed to be a superkey because two students can have the same name.

Is this 4NF?: A table where StudentID determines multiple independent values of Courses and Hobbies.

Yes. While a student can take multiple courses and have multiple hobbies, studentIDs are unique to each student. That makes it a superkey. If the left side of the determination is a superkey, the multiple independent values are fine.

Which normal form allows the most redundancy of any normal form?

First normal form

• The table has a primary key.

• The table cannot have duplicate rows.

• Every cell contains exactly one value.

This describes what normal form?

First normal form.

In a first normal form table, non-key columns depend only on the primary key. T/F?

False.

In a first normal form table, non-key columns may depend on other non-key columns.

Ex: The Department table has primary key DepartmentCode and non-key columns ManagerID and ManagerName. Department has a primary key and therefore is in first normal form. But ManagerName depends on ManagerID, which is a non-key column.

In relational theory, all tables are in first normal form. T/F?

True. As defined by E. F. Codd, the originator of the relational model, every table has a primary key. So, in principle, every table is in first normal form.

In practice, however, most database systems allow tables without a primary key and with duplicate rows.

A table in second normal form is also in first normal form. T/F?

True.

A table is in 2NF if:

1. It is in 1NF

2. There are no partial dependencies

Which column must be removed to achieve second normal form?

EmailAddress.

EmailAddress is a non-key column and depends on StudentNumber only, not the whole primary key (StudentNumber+CourseNumber). EmailAddress must be moved to another table to achieve second normal form.

A table with a simple primary key must be in second normal form or higher. T/F?

True.

Simple primary keys are keys with ONE attribute (one column). So because there is 1 attribute, partial dependencies aren’t possible. So 2NF and beyond are possible, since 2NF means there are NO partial dependencies.

Which column must be removed to achieve third normal form?

GradeLetter.

GradeLetter is a non-key column that depends on ScoreNumber, another non-key column. To convert from second to third normal form, all non-key columns that depend on other non-key columns must be removed.

Each department has one chair, but the same person occasionally chairs several departments. Course names can be repeated in different departments, but never within the same department. What dependency violates the definition of third normal form?

DepartmentChair depends on DepartmentCode

DepartmentChair depends on CourseName

CourseName depends on CourseCode

DepartmentChair depends on DepartmentCode

DepartmentChair is a non-key column and DepartmentCode is not unique. This dependency creates redundancy—the fact 'Azim Rafiq is chair of the Math department' is repeated.

❌ Discrete mathematics is taught in two departments with different chairs, so DepartmentChair does not depend on CourseName.

Each department has one chair, but the same person occasionally chairs several departments. Course names can be repeated in different departments, but never within the same department. Which column should be removed to achieve third normal form?

DepartmentChair

CourseCode

DepartmentCode

DepartmentChair

When DepartmentChair is removed, the table has no non-key columns and therefore necessarily conforms to the definition of third normal form. Removing DepartmentChair eliminates the redundancy.

❌ It says “each department has one chair” implying the DepartmentChair relies on DepartmentCode. Removing DepartmentCode also results in third normal form, but it relies on the primary key so it wasn’t really a big issue. It was DepartmentChair that posed the biggest issue because its a non-key relying on another non-key (DepartmentCode) where DepartmentCode is a non-key that relies on a primary key.

Each postal code, in United States zip+4 format, is located in one state only.

Employee is in Boyce-Codd normal form. T/F?

False.

Since DriversLicenseState depends on a non-unique column (zipcode), Employee is not in Boyce-Codd normal form and contains redundancy.

A table has:

• columns A, B, C, D

• candidate key D

• primary key D

• dependency C → A (in addition to dependencies on candidate keys)

What is the normal form of the table?

2NF. A is a non-key that depends on a non-key.

3NF means that a non-key cannot depend on a non-key.

A table has:

• columns A, B, C, D

• candidate keys B and D

• primary key D

• dependency C → A (in addition to dependencies on candidate keys)

What is the normal form of the table?

2NF. A is a non-key that depends on C another non-key.

3NF means non-keys cannot depend on another non-key.

A table has:

• columns A, B, C, D

• candidate keys (A, C) and B

• primary key (A, C)

• dependencies on (A, C) and B only

What is the normal form of the table?

BCNF.

When the candidate keys match the dependencies, it is BCNF.

A table has:

• columns A, B, C, D

• candidate keys (B, D) and C

• primary key (B, D)

• dependency A → B (in addition to dependencies on candidate keys)

What is the normal form of the table?

3NF.

For 3NF, a non-key cannot depend on a non-key. But B is not a non-key, it’s part of a candidate key. Prime attributes (part of a candidate key) are allowed to depend on a non-key.

A non-key can also depend on a super key (C) where the columns are unique. So C→A is also 3NF. or (B, D) → A.

A table has:

• columns A, B, C, D

• candidate keys B and D

• primary key D

• dependency A → C (in addition to dependencies on candidate keys)

What is the normal form of the table?

2NF. There are no partial dependencies.

It cannot be 3NF because a non-key depends on a non-key.

A table has:

• columns A, B, C, D

• candidate keys C and (A, B)

• primary key (A, B)

• dependency A → D (in addition to dependencies on candidate keys)

What is the normal form of the table?

1NF.

It cannot be 2NF because D depends on part of a candidate key A. 2NF means no partial dependencies.

A table has:

• columns A, B, C, D

• candidate keys C and (A, B)

• primary key (A, B)

• dependency C → D (in addition to dependencies on candidate keys)

BCNF.

It satisfies 3NF because D (a non-key) is allowed to depend on a superkey (C).

It satisfies BCNF because C → D has a superkey (C) on the left side (C).

BCNF requires that all non-keys depend on a unique key.

A table has:

• columns A, B, C, D

• candidate keys C and (A, B)

• primary key (A, B)

• dependency (A, B) → D (in addition to dependencies on candidate keys)

BCNF

It satisfies 3NF because a non-key depends on a superkey (A, B).

It satisfies BCNF because all non-keys depend on a unique key. (A, B) is unique.

Every attribute that determines another attribute must be a key (something that uniquely identifies rows) is an example of what normal form?

BCNF