Modelagem de Dados - Normalização - Segunda Forma Normal

Introduction and Second Normal Form

In this section, the instructor introduces the topic of the second normal form in data modeling, building upon the concepts covered in previous videos regarding normalization.

Understanding Second Normal Form

• The second normal form is based on the concept of total functional dependency within a relation or table.

• For a table to be in the second normal form, every non-key attribute must be functionally dependent on the entire primary key.

• To achieve the second normal form, a table must first be in the first normal form; skipping normalization steps is not permissible.

Application and Analysis of Second Normal Form

This section delves into practical applications and analysis related to achieving and verifying compliance with the second normal form through examples and detailed explanations.

Practical Application

• All attributes not part of the primary key must be functionally dependent on all parts of that key to meet second normal form requirements.

• If partial dependencies exist, a new table needs to be created to address them by extracting relevant information.

Example Analysis

• Moving attributes that are not fully functionally dependent on the primary key to a new table helps achieve second normal form.

• An example scenario with a table containing various fields like piece code, supplier code, etc., is used for practical demonstration.

Normalization Process and Detailed Analysis

This segment focuses on analyzing specific columns within a given table to determine their functional dependencies as part of the normalization process towards achieving second normal form.

Column Dependency Analysis

• Analyzing each column's dependency on different parts of the primary key is crucial for identifying potential violations of second normal form criteria.

Detailed Database Normalization Process

In this section, the speaker discusses the process of normalizing a database by splitting tables to remove partial dependencies and achieve second normal form.

Normalization Process

• Two columns, supplier location, and supplier phone number were identified as not depending on the part code. These columns were moved to a separate table to eliminate partial dependencies.

• The supplier code was also separated into another table along with the previously mentioned columns. This new table, "supplier," now contains the primary key for these attributes.

• Three codes, locations, and phone numbers are now in the "supplier" table while the "parts" table only contains part codes. The quantity in stock remains in the "parts" table due to its dependency on a combination of factors.

• The column for box quantity could be replaced with a calculated field to determine box quantities based on stock levels and piece numbers. This eliminates redundancy and simplifies data retrieval.

• Redundant columns like location and phone number are removed from the "parts" table and placed in a new "supplier" table. This restructuring ensures second normal form compliance by eliminating partial dependencies.

Benefits of Second Normal Form

Exploring how achieving second normal form resolves issues related to partial dependencies within a database structure.

Benefits of Second Normal Form

• Achieving second normal form eliminates problems associated with partial dependencies by ensuring total dependency between attributes.

• By adhering to second normal form principles, databases maintain structural integrity through complete attribute dependence.