FI-AE 05 Datenbankdesign und Normalformen (EN)

FI-AE 05 Database Design and Normal Forms

In this module, you will learn the fundamentals of relational database design. You will understand how to create Entity-Relationship models, what normal forms exist, and how they reduce redundancies. You will recognize different types of keys and know when indexes are useful and when intentional denormalization is appropriate.

Concepts and Background

Entity-Relationship Model (ERM)

A conceptual model for representing data and their relationships in a database. It consists of entities, attributes, and relationships.

Normal Forms

Levels of database normalization that systematically improve the structure of tables. The first three normal forms (1NF, 2NF, 3NF) are the most basic rules for reducing redundancies.

Primary Key

One or more attributes that uniquely identify each record in a table. They must be unique and not null.

Foreign Key

An attribute or combination of attributes that references the primary key of another table and serves to establish relationships.

Index

A data structure that improves query speed on specific columns by creating a sorted reference to the data.

Practical Steps

1. Identify all entities in your domain model and define their attributes. Each entity will later become a table.
2. Determine the relationships between entities (1:1, 1:n, n:m) and model them in the ER diagram.
3. Create tables for each entity and select an appropriate primary key.
4. Apply the first normal form: Ensure that each attribute is atomic and there are no repeating groups.
5. Apply the second normal form: Eliminate partial dependencies by moving them to separate tables with foreign keys.
6. Apply the third normal form: Eliminate transitive dependencies by moving attributes that do not depend directly on the primary key to their own tables.
7. Create indexes for columns that are frequently used in WHERE clauses, JOINs, or ORDER BY statements.
8. Check the performance of your queries and adjust indexes or normalization as needed.

Common Pitfalls

Further Resources

• MySQL Documentation on Normalization
• PostgreSQL Tutorial: Database Design
• Database Answers: Normalisation Explained
• SQL Shack: Database Normalization Explained
• Lucidchart: Database Design Guide

Knowledge Check

Four questions for self-assessment. Click on each question to see the correct answer and explanation.

What is the main purpose of normal forms in database design?

• A) To maximize the speed of database queries
• B) To reduce redundancies in the database and ensure data integrity
• C) To minimize the storage requirements of a database
• D) To reduce the complexity of SQL queries

Correct Answer: B. Normal forms reduce redundancies and ensure data integrity, not primarily speed or storage space.

Which of the following statements correctly describes a foreign key?

• A) An attribute that uniquely identifies a record
• B) An attribute that references the primary key of another table
• C) An attribute that must be not null and unique
• D) An attribute that serves to improve query speed

Correct Answer: B. A foreign key references the primary key of another table and serves to establish relationships.

What is a key characteristic of the first normal form (1NF)?

• A) All attributes must be dependent on all other attributes
• B) All attributes must be atomic and contain no repeating groups
• C) All non-key attributes must be dependent on the entire primary key
• D) There must be no transitive dependencies

Correct Answer: B. 1NF requires atomic attributes and the absence of repeating groups in tables.

When is intentional denormalization useful in database design?

• A) When the database is fully normalized and no redundancies remain
• B) When query performance is affected by too many JOINs
• C) When the database has not achieved the third normal form
• D) When the database contains no foreign keys

Correct Answer: B. Denormalization can be useful when performance suffers from too many JOINs, although it introduces redundancies.