BMGT402 - Normalization

What is normalization? (Slide 3)

A design process for evaluating and correcting data structures to reduce data anomalies.

What two problems does normalization minimize? (Slide 3)

Data redundancies and data inconsistencies.

What is normalization’s role in operational database modeling? (Slide 3)

It converts an ER schema into relational tables ready for DBMS implementation.

What are the first three normal forms? (Slide 3)

First Normal Form (1NF), Second Normal Form (2NF), Third Normal Form (3NF).

Name four higher normal forms listed in the slides. (Slide 3)

BCNF, 4NF, 5NF, and Domain-Key Normal Form (DKNF).

How does moving to a higher normal form affect redundancy? (Slide 4)

Generally reduces data redundancies and inconsistencies.

For most business database designs, how high is normalization usually needed? (Slide 4)

Third Normal Form (3NF).

Why is the highest normal form not always desirable? (Slide 4)

More normalization can reduce performance.

What is denormalization? (Slide 4)

Changing a relation from a higher-level normal form to a lower-level normal form.

What is the main denormalization trade-off? (Slide 4)

Increased performance at the cost of greater data redundancy.

Where is denormalization often used? (Slide 4)

Data warehousing and historical databases.

Scenario (Slide 5): A building-project company bills clients for hours worked. What determines hourly billing rate?

The employee’s position or job class.

Scenario (Slide 5): A star (*) appears beside an employee on a project report. What does it mean?

The employee is the project manager.

Scenario (Slides 6–7): PROJ_NUM is intended as a PK but is blank in some rows. What integrity rule is violated?

Entity integrity; a primary key cannot contain NULL values.

What problems can redundant data cause? (Slide 7)

Inconsistencies, repeated entry time, and wasted storage space.

What is an insertion anomaly? (Slide 7)

Being unable to add a fact without adding unrelated data.

Scenario (Slide 7): A new employee cannot be added unless assigned to a project. What anomaly is this?

Insertion anomaly.

What is an update anomaly? (Slide 7)

A change must be made in multiple rows, risking inconsistent values.

Scenario (Slide 7): Changing an employee’s last name requires editing several records. Which anomaly?

Update anomaly.

What is a deletion anomaly? (Slide 7)

Deleting a record unintentionally removes other needed information.

Scenario (Slide 7): Deleting the only row for EMP_NUM 103 also removes vital employee details. Which anomaly?

Deletion anomaly.

What should each table represent in a successfully normalized database? (Slide 8)

A single subject or theme.

How many values should appear at each row-column intersection? (Slide 8)

One value, not a group of values.

What should nonprime attributes depend on in a normalized table? (Slide 8)

The primary key.

What three anomalies should normalized tables avoid? (Slide 8)

Insertion, update, and deletion anomalies.

Give two benefits of normalization. (Slide 9)

Easier data maintenance and reduced storage needs.

Why does normalization improve query information? (Slide 9)

It removes anomalies and reduces inconsistencies.

How does normalization transform relations? (Slide 12)

It progressively decomposes a table into new relations based on dependencies.

What are normal forms? (Slide 12)

Standardized table structures that meet specific characteristics.

What characterizes 1NF? (Slide 12)

Table format, no repeating groups, and an identified primary key.

What characterizes 2NF? (Slide 12)

1NF plus no partial dependencies.

What characterizes 3NF? (Slide 12)

2NF plus no transitive dependencies.

What is a repeating group? (Slide 13)

Multiple entries associated with one key-attribute occurrence.

Scenario (Slide 13): One car stores multiple top, interior, and trim colors as grouped values. What problem?

A repeating group; it must be eliminated for 1NF.

What is Step 1 in converting a table to 1NF? (Slide 14)

Eliminate repeating groups so each row-column intersection contains one value.

What is Step 2 in converting a table to 1NF? (Slide 14)

Identify a primary key that uniquely identifies each record and is irreducible.

What is Step 3 in converting a table to 1NF? (Slide 14)

Identify all dependencies, often using a dependency diagram.

What does irreducible mean for a primary key? (Slide 14)

No component can be removed without losing unique identification.

What is a functional dependency? (Slide 15)

A relationship where one or more attributes determine one or more other attributes.

What does A → B mean? (Slide 15)

A functionally determines B; B is functionally dependent on A.

What is the determinant in A → B? (Slide 15)

A, the attribute on the left-hand side of the arrow.

Scenario (Slide 16): Each staff number identifies one position, but many staff members share a position. Which dependency holds?

staffNo → position; position does not determine staffNo.

Why does position not functionally determine staffNo? (Slide 16)

Multiple staff members can have the same position.

What is a partial dependency? (Slide 17)

A nonkey attribute depends on only a subset of a composite primary key.

When can a partial dependency occur? (Slide 17)

Only when the primary key is composite.

Scenario (Slide 17): PK = (PROJ_NUM, EMP_NUM); PROJ_NAME depends only on PROJ_NUM. What dependency?

Partial dependency: PROJ_NUM → PROJ_NAME.

Scenario (Slide 17): PK = (PROJ_NUM, EMP_NUM); EMP_NAME depends only on EMP_NUM. What dependency?

Partial dependency: EMP_NUM → EMP_NAME.

What is a transitive dependency? (Slide 18)

A nonkey attribute depends on another nonkey attribute.

Scenario (Slide 18): Employee job class determines CHG_HOUR; neither is part of composite PK. What dependency?

Transitive dependency: JOB_CLASS → CHG_HOUR.

Where might designers learn about transitive dependencies? (Slide 18)

Business rules, user discussions, documentation, and domain experience.

What is a dependency diagram? (Slide 19)

A diagram of full functional, partial, and transitive dependencies among attributes.

Where are good and problematic dependencies drawn on the slide’s diagram? (Slide 19)

Good dependencies above; problematic dependencies below.

Scenario (Slides 20–21): Project work report includes PROJ_NUM and EMP_NUM. What composite PK is chosen for 1NF?

(PROJ_NUM, EMP_NUM).

Scenario (Slide 21): REPORT_DATA has PROJ_NUM, EMP_NUM, PROJ_NAME, EMP_NAME, JOB_CLASS, CHG_HOUR, HOURS. What depends on full composite key?

HOURS (assignment hours).

Does reaching 1NF guarantee no insertion, update, or deletion anomalies? (Slide 20)

No; 1NF can still have anomalies.

What two conditions define 2NF? (Slide 22)

Already in 1NF and no partial dependencies.

Scenario (Slide 22): EMP_NUM 107 occurs ten times with repeated EMP_NAME and JOB_CLASS. What issue is illustrated?

Redundancy and potential anomalies caused by partial dependencies.

If a 1NF table has a single-attribute primary key, is it automatically in 2NF? (Slide 22)

Yes, because partial dependencies require a composite key.

What is the first conversion step from 1NF to 2NF? (Slide 23)

Create new relations to eliminate partial dependencies.

How are key components handled during 2NF conversion? (Slide 23)

Put each composite-key component on its own line and the original composite key on the last line.

What is Step 2 of conversion to 2NF? (Slide 23)

Reassign dependent attributes to the appropriate new relations.

Scenario (Slide 24): PROJ_NAME depends on PROJ_NUM. Which 2NF table stores it?

PROJECT (PROJ_NUM, PROJ_NAME).

Scenario (Slide 24): EMP_NAME and JOB_CLASS depend on EMP_NUM. Which 2NF table stores them?

EMPLOYEE (EMP_NUM, EMP_NAME, JOB_CLASS, CHG_HOUR).

Scenario (Slide 24): Hours depend on both project and employee. Which 2NF relation stores them?

ASSIGNMENT (PROJ_NUM, EMP_NUM, ASSIGN_HOURS).

Scenario (Slide 24): After removing partial dependencies, JOB_CLASS still determines CHG_HOUR. Is the design 3NF?

No; the transitive dependency remains.

What two conditions define 3NF? (Slide 25)

Already in 2NF and no transitive dependencies.

Scenario (Slide 25): Job class appears as “ElecEng” in one row and “EE” in another. What issue is illustrated?

Data inconsistency from redundancy.

What is Step 1 of conversion to 3NF? (Slide 26)

Create new relations for transitive dependencies, using each determinant as the new PK.

What happens to a transitive determinant in the original relation? (Slide 26)

It remains as a foreign key.

What is Step 2 of conversion to 3NF? (Slide 26)

Reassign the corresponding dependent attributes.

Scenario (Slide 27): JOB_CLASS → CHG_HOUR. What new 3NF table is created?

JOB (JOB_CLASS, CHG_HOUR).

Scenario (Slide 27): After splitting JOB, what remains in EMPLOYEE?

EMPLOYEE (EMP_NUM, EMP_NAME, JOB_CLASS).

Scenario (Slide 27): What are all four final 3NF relations in the worked example?

PROJECT, EMPLOYEE, JOB, and ASSIGNMENT.

Does normalization alone guarantee good database design? (Slide 30)

No; other design issues must also be evaluated.

Why replace JOB_CLASS with JOB_CODE as a primary key? (Slide 31)

A surrogate code reduces inconsistent text entry and improves PK assignments.

Scenario (Slide 31): JOB_CODE → JOB_CLASS, CHG_HOUR and JOB_CLASS is a candidate key. Is JOB_CLASS → CHG_HOUR transitive here?

No, the slide says it is not transitive because JOB_CLASS is a candidate key.

Why use consistent attribute naming conventions? (Slide 31)

To show table attachment and clarify attribute purpose.

What are the improved names for JOB_CLASS, CHG_HOUR, and HOURS? (Slide 31)

JOB_DESCRIPTION, JOB_CHG_HOUR, and ASSIGN_HOURS.

What is an atomic attribute? (Slide 31)

An attribute that cannot be meaningfully subdivided further.

Scenario (Slide 31): EMP_NAME stores an employee’s full name. How can atomicity be improved?

Split into EMP_FNAME, EMP_INITIAL, and EMP_LNAME.

What benefit does refining attribute atomicity provide? (Slide 31)

Improves querying and indexing.

What new attribute captures an employee’s hiring date? (Slide 32)

EMP_HIREDATE.

Scenario (Slide 32): Each project must record its manager. What foreign key is added and where?

Add EMP_NUM as a foreign key in PROJECT.

What is data granularity? (Slide 32)

The level of detail represented by values in a table row.

Scenario (Slide 32): ASSIGN_HOURS has no time period. What attribute can clarify daily tracking?

ASSIGN_DATE.

Scenario (Slide 32): Employees may work on multiple projects on the same day. What surrogate PK is proposed?

ASSIGN_NUM.

Why maintain historical accuracy in assignment charges? (Slide 33)

Billing rates can change; audits need the charge used at the time.

Scenario (Slide 33): JOB_CHG_HOUR can change over time. Which attribute preserves the assignment’s historical rate?

ASSIGN_CHG_HOUR.

What is a derived attribute? (Slide 33)

An attribute whose value can be calculated from other stored values.

How is ASSIGN_CHARGE calculated? (Slide 33)

ASSIGN_HOURS × ASSIGN_CHG_HOUR.

Why might a database store a derived attribute? (Slide 33)

To speed processing, queries, or reporting.

What are the four tables in the completed design? (Slide 34)

EMPLOYEE, PROJECT, JOB, and ASSIGNMENT.

What is the primary key of ASSIGNMENT in the completed design? (Slide 34)

ASSIGN_NUM.

Which table contains JOB_CODE as a foreign key? (Slide 34)

EMPLOYEE.

Which table stores the project manager’s EMP_NUM? (Slide 34)

PROJECT.

What are four ways AI can help with normalization? (Slide 37)

Explain concepts, analyze relations, suggest decompositions, and review designs.