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

## Solution

A table is in third normal form when the following conditions are met:

* It is in the second normal form.

* All non-primary fields are dependent on the primary key.


**Customer table**

| Cust_Id | Cust_Name | DOB | Area | City | State | Zip |
| --- | --- | --- | --- | --- | --- | --- |
| 1 | Jack | 1996-01-13 | 777 Brockton Avenue | Abington | MA | 2351 |
| 2 | Bruce | 1995-09-22 | 3018 East Ave| Central Square | NY | 13036 |
| 3 | Amy | 1999-11-17 | 80 Town Line Rd| Rocky Hill | CT | 6067 |
| 4 | James | 1998-03-10 | 5710 Mcfarland Blvd | Northport | AL | 35476 |
| 5 | Veronica | 1990-06-09 | 2900 Pepperrell Pkwy | Opelika | AL | 36801 |


First, we can see that the table above is in the first normal form; it obeys all the rules of the first normal form. 

Secondly, the primary key consists of the `Cust_Id` as it uniquely identifies each record in the table.

Therefore the table is in second normal form as there is no composite primary key.

However, the table is not in the third normal form because the area, city, and state are unbreakably bound to their zip code. The dependency between the zip code and the address is called transitive dependency. To comply with the third normal form, all you need to do is to move the `Area`, `City`, and `State` fields into their own table. 

**Zip code table**

| Zip | Area | City | State |
| --- | --- | --- | --- | 
| 2351 | 777 Brockton Avenue | Abington | MA | 
| 13036 | 3018 East Ave| Central Square | NY | 
| 6067 | 80 Town Line Rd| Rocky Hill | CT | 
| 35476 | 5710 Mcfarland Blvd | Northport | AL 
| 36801 | 2900 Pepperrell Pkwy | Opelika | AL 

The next step is to alter the CUSTOMER table as shown below:

**Customer table**

| Cust_Id | Cust_Name | DOB | Zip |
| --- | --- | --- | --- |
| 1 | Jack | 1996-01-13 | 2351 |
| 2 | Bruce | 1995-09-22 | 13036 |
| 3 | Amy | 1999-11-17 | 6067 |
| 4 | James | 1998-03-10 | 35476 |
| 5 | Veronica | 1990-06-09 | 36801 |

The `Zip` field acts as a foreign key in the CUSTOMER table so that we can get the address details of the corresponding customer. It acts as a link between the two tables.

Now all the tables are in 3NF as there is no transitive dependency between any column.

# Solution

A table is in third normal form when the following conditions are met:

* It is in the second normal form.

* All non-primary fields are dependent on the primary key.


**Customer table**

| Cust_Id | Cust_Name | DOB | Area | City | State | Zip |
| --- | --- | --- | --- | --- | --- | --- |
| 1 | Jack | 1996-01-13 | 777 Brockton Avenue | Abington | MA | 2351 |
| 2 | Bruce | 1995-09-22 | 3018 East Ave| Central Square | NY | 13036 |
| 3 | Amy | 1999-11-17 | 80 Town Line Rd| Rocky Hill | CT | 6067 |
| 4 | James | 1998-03-10 | 5710 Mcfarland Blvd | Northport | AL | 35476 |
| 5 | Veronica | 1990-06-09 | 2900 Pepperrell Pkwy | Opelika | AL | 36801 |


First, we can see that the table above is in the first normal form; it obeys all the rules of the first normal form. 

Secondly, the primary key consists of the `Cust_Id` as it uniquely identifies each record in the table.

Therefore the table is in second normal form as there is no composite primary key.

However, the table is not in the third normal form because the area, city, and state are unbreakably bound to their zip code. The dependency between the zip code and the address is called transitive dependency. To comply with the third normal form, all you need to do is to move the `Area`, `City`, and `State` fields into their own table. 

**Zip code table**

| Zip | Area | City | State |
| --- | --- | --- | --- | 
| 2351 | 777 Brockton Avenue | Abington | MA | 
| 13036 | 3018 East Ave| Central Square | NY | 
| 6067 | 80 Town Line Rd| Rocky Hill | CT | 
| 35476 | 5710 Mcfarland Blvd | Northport | AL 
| 36801 | 2900 Pepperrell Pkwy | Opelika | AL 

The next step is to alter the CUSTOMER table as shown below:

**Customer table**

| Cust_Id | Cust_Name | DOB | Zip |
| --- | --- | --- | --- |
| 1 | Jack | 1996-01-13 | 2351 |
| 2 | Bruce | 1995-09-22 | 13036 |
| 3 | Amy | 1999-11-17 | 6067 |
| 4 | James | 1998-03-10 | 35476 |
| 5 | Veronica | 1990-06-09 | 36801 |

The `Zip` field acts as a foreign key in the CUSTOMER table so that we can get the address details of the corresponding customer. It acts as a link between the two tables.

Now all the tables are in 3NF as there is no transitive dependency between any column.

In this lesson we will discuss the solution to exercise 2.

Introduction

Fundamental Concepts

Data Modeling

Entity-Relationship Data Model

The Relational Data Model

Functional Dependencies

Normalization

Structured Query Language

Conclusion

Solution to Exercise 2

Solution

Cust_Id	Cust_Name	DOB	Area	City	State	Zip
1	Jack	1996-01-13	777 Brockton Avenue	Abington	MA	2351
2	Bruce	1995-09-22	3018 East Ave	Central Square