Title: DBS101 Unit 5

categories: [DBS101, Unit 5] tags: [DBS101]

Topic: Relational Database Design

Introduction

Before this lesson, I thought making tables and adding foreign keys was enough for database design. But now I understand designing properly is very important. Without good design there can be lot of problems like repeating data and wrong updates.

At first I was confused but after teacher gave examples, slowly I understood. Still sometimes I mix up normal forms but doing practice helped me lot.

Important Lessons from Unit 5

Functional Dependencies and Decomposition

Functional dependency means if you know one attribute you can find another attribute.
Example if you know student_id, you can find student_name.

Decomposition means breaking table into small tables to remove problems but without losing any data or rules.

Example:
Student table can be broken into Student and Department tables.

Normal Forms to Fix Bad Designs

Normal forms are steps that help to make good database design.

First Normal Form (1NF)

• All attributes must have atomic simple values.
• No multi-valued columns allowed.

Bad:

Good:

Second Normal Form (2NF)

• Be in 1NF
• No partial dependency (no attribute depend only part of primary key)

Third Normal Form (3NF)

• Be in 2NF
• No transitive dependency (non-key depending on non-key)

Boyce-Codd Normal Form (BCNF)

• Stricter than 3NF
• Every determinant must be a superkey.

At first I thought BCNF and 3NF same but later I understood BCNF is little stricter.

Multivalued Dependencies and Fourth Normal Form (4NF)

Sometimes one attribute can have many independent values.

Example: A teacher can have many addresses and many departments.

So table need to be broken into two smaller tables.

One table for teacher and address.
One table for teacher and department.

Fifth Normal Form (5NF) and DKNF

5NF deals with join dependencies.
DKNF is most strict but very hard to use in real life.

Most people stop design at 3NF or BCNF because higher forms are too complex.

Atomic Domains and First Normal Form

Atomic domain means value must be simple indivisible.

Bad atomicity example:

Good atomicity:

If not atomic, data will be messy and hard to update.

Database Design Process

Good design can be made by:

• Using good E-R diagram
• Making big table and then normalize
• Making ad-hoc design then fix by normalization

Naming things properly and planning early is important.

Sometimes people denormalize to make query faster but need to be very careful to keep data correct.

Modeling Temporal Data

Temporal data means data that changes over time.

Example: Teacher address history with start and end dates.

We can add start_date and end_date columns to keep track.

Example:

Handling temporal data is little complex because we need to avoid overlap periods and keep primary keys correct.

My Experience and Reflections

When I first saw normal forms I was very confused.
I mixed up 2NF and 3NF. Also multivalued dependency was hard at start.

After doing examples and practice, slowly I understand better.

Temporal data part was interesting because it showed how to track changes over time in database.

Now I know why designing database properly is so important.

Conclusion

This unit taught me why making tables is not enough.

Good design means less problems and easy updates.

I still make some mistakes in normal form steps but now I feel more confident than before.

I want to use these skills when I design real world projects in future.