What's going on guys,
So for the past few weeks as you know I have been working with a Entity Relationship diagram mapping the risks of COVID-19. In my most recent video, I coded my tables using SQL and phpMyAdmin. This week I will be 'normalizing' my data.
Basically normalization is a technique for organizing your data in a database. This process is used to minimize redundancy in your data and ensure your data relates in a logical way. Technically, there are six stages to this process known as normal forms, but we'll only be focusing on the first three, as they are the most relevant. These forms continue to build off of each other.
First Normal Form (1NF)
This step ensures that the data stored in the tables are identified with a primary key, there are no repeating groups, and data is stored in columns in its most reduced form.
Second Normal Form (2NF)
The second step of the normalization process holds the same requirements as 1NF and adds that only data relating to a table's primary key is stored in each table.
Third Normal Form (3NF)
The third and final step we'll discuss includes everything from 2NF and adds that there are no in-table dependencies between the columns in each table.
Applying Our Knowledge:
To start we'll be looking at the Location table. Off the bat, I can see that this is in violation of 1NF. As you can see, the table's primary key is associated with the zip code. Because the zip code is a unique identifier, it will always yield a country of origin, in this case, USA. This means the country column in this table is redundant and can therefore be removed. With this the removal of this column, our table now meets the 1NF requirements.
Next, we'll have a look at the Hospital table. This table meets the 1NF requirements, but when we get to the 2NF, it fails. We see there is a column whose data doesn't relate to the table's primary key. The primary key in this case is the HospitalID, a unique identifier for a particular hospital. The column in violation is 'Quantity'. Since the primary key is a unique identifier, asking how many hospitals in the area does not make sense. Instead, we can either remove the column completely or move it under the Location table.
That about sums up the work for this week. I posted the link to my video below where I quickly reviewed the three normalization steps and used the ALTER function in SQL to my the changes necessary to meet these requirements. Thank you and I'll see ya next week :)
Top comments (0)