Gain insights into fundamental database concepts, relational databases, and entity-relationship diagrams. Learn about normalization techniques and basics of SQL to enhance database efficiency.

Databases are universal - they underlie the technology you use every day. They're a crucial component of everything from telecommunications systems, banking systems, and video games to just about any other software system that maintains some amount of persistent data.

In this course, you'll learn about the fundamental concepts of databases, why and when they're used, what relational databases are, and entity-relationship diagrams. You will also be exposed to techniques like normalization that help to increase the efficiency of databases. You'll wrap up with a look at the basics of Structured Query Language (SQL). 

After completing this course, you will be able to move onto more advanced courses involving database systems and SQL.

Database Design Fundamentals for Software Engineers

# Third normal form (3NF)

For a table to be in the third normal form:

1. It should be in the second normal form.

2. It should not have [transitive dependency](https://www.educative.io/courses/database-design-fundamentals/rules-of-functional-dependencies).

## Example

**SCORE Table** 

|  Std_Id   |  Subject_Id   |  Marks_obtained   | Exam_Type    |  Total_Marks   |
| --- | --- | --- | --- | --- |
|  1   |  CS-100   |  50   |  Final   |  100   |
|  2   |  CS-100   |  70   |  Final   |  100   |
|  3   |  CS-100   |  85   |  Final   |   100  |
|  1   |  Math-101   |  30   | Mid-term    |  50 |
|  1   |  PHY-100   |  10   |  Practical   |  30   |
|  2   |  CHEM-100   |  20   |  Practical   |  30   |
|  3   |  PHY-120   |  40   | Mid-term    | 50    |


From the table, we can see that the primary key for our SCORE table is a composite key, which means it's made up of two attributes (columns): {`Std_Id`, `subject_Id`}.

The column `Exam_Type` depends on both `Std_Id` and `Subject-Id`. For example, a student taking a chemistry course will have a practical lab exam but a student in a mathematics course will not. So we can say that `Exam_Type` is dependent on the whole composite key, thus there is no partial dependency, so the table is in 2NF.

But what about the column `Total_Marks`? Does it depend on our SCORE table's primary key?

Well, the column `Total_Marks` depends on `Exam_Type` since the type of exam the total score changes. For example, practicals are worth fewer marks while theory exams are worth more marks.

This results in a transitive dependency because a non-prime attribute depends on other non-prime attributes rather than depending upon the prime attributes or primary key.

So, in order to convert this table into 3NF, we take out the attributes `Exam_Type` and `Total_Marks` from the SCORE table and put them in their own table called the EXAM table. We will also add another column called `Exam_Id` in the EXAM table to act as the primary key. This column will also be added to the SCORE as a foreign key, so now we have a link between the two tables.

This is illustrated below:

In this lesson, we will discuss the concept behind 3NF using an example.

Introduction

Fundamental Concepts

Data Modeling

Entity-Relationship Data Model

The Relational Data Model

Functional Dependencies

Normalization

Structured Query Language

Conclusion

Third Normal Form

Third normal form (3NF)

Example