<jupyter_start><jupyter_text>Normalising SQL Tables© Explore Data Science Academy Learning ObjectivesIn this train, you will learn:- The differences between database normal forms; and- How to use SQL queries to transform a table to different normal forms.  OutlineIn this train, we'll go through the following: - What is normalisation?- First Normal Form (1 NF).- Second Normal Form (2 NF).- Third Normal Form (3 NF).- Case Study: Dam levels data- Denormalization  1. What is normalisation?Normalization is a database technique for evaluating and reorganizing table structures to minimize redundancies, improve data integrity, improve storage efficiency, and reduce the need to re-design the database if new data is introduced. Database normalization removes inconsistencies which may cause the analysis of our data to be more complicated. These inconsistencies could come from updating records, inserting, or deleting records. It also includes the removal of duplicate records which saves on storage, and is a step toward fulfilling the requirement of records having unique identifiers called keys.Normalization is segmented into ordered categories: 1NF, 2NF, 3NF, BCNF, and 4NF.2NF is preferred to 1NF, 3NF to 2NF, etc:  2. First Normal Form (1 NF) A table is in 1NF if it contains no repeating groups. To convert an unnormalised table to 1NF either:- Flatten the table and change the primary key- Decompose the table into smaller tables, one for the repeating groups and one for the non-repeating groups.For example:  To resolve the repeating group anomaly, we can split into multiple rows or multiple tables:   3. Second Normal Form (2 NF) A table is in 2NF if it contains no repeating groups and no partial functional dependencies. To convert a relation with partial functional dependencies to 2NF, create a set of new relations:- One relation for the attributes that are fully dependent upon the key.- One relation for each part of the key that has partially dependent attributes.   4. Third Normal Form (3 NF)A table is in 3NF if it contains no repeating groups, no partial functional dependencies, and no transitive functional dependencies. To convert a relation with transitive functional dependencies to 3NF:- Remove the attributes involved in the transitive dependency and put them in a new relation.- Ensure that in a normalised relation a non-key field must provide a fact about the key, the whole key and nothing but the key.  Relations in 3NF are sufficient for most practical database design problems. However, 3NF does not guarantee that all anomalies have been removed. In this regard, delving into [Boyce-Codd normal form](https://en.wikipedia.org/wiki/Boyce%E2%80%93Codd_normal_form) (BCNF) and [4th normal form](https://en.wikipedia.org/wiki/Fourth_normal_form) (4NF) is beyond the scope of this course, but is recommended for eager learners who have an interest in data architecting.     5. Case Study: Dam levels data In this section, we will look at normalising the dams database - a [flat-file database](https://en.wikipedia.org/wiki/Flat-file_database) containing information about Cape Town dam water levels leading up to the 2018 Cape Town water crisis. Note: Make sure that you download the dam_levels.db file from Athena, before continuing with the train. Since the queries here will modify the database, you will have to get a fresh copy to redo the following code cells.  Load SQL magics and database:<jupyter_code>%load_ext sql
# Load SQLite database
%sql sqlite:///dam_levels.db<jupyter_output>The sql extension is already loaded. To reload it, use:
  %reload_ext sql<jupyter_text>Before we proceed, let's first take a closer look at the dam_levels table:<jupyter_code>%%sql

SELECT *
FROM dam_levels
LIMIT 10<jupyter_output>* sqlite:///dam_levels.db
Done.<jupyter_text>The table seems to contain recurring assessments and related information for each dam. The table also has some data anomalies. Let's try to resolve these using normalisation.  5.1. First Normal Form (1 NF)To get the table in **1 NF** we need to ensure that:- 1. Each column contains the same datatype &x2611;   - 2. There are no repeating groups &9746; - this can be resolved by separating the grouped data into multiple rows- 3. Each row is uniquely identifiable &9746; - this can be resolved by adding a primary key columnTo resolve the repeating groups, we first isolate the offending rows of data:<jupyter_code>%%sql
SELECT * 
FROM dam_levels
WHERE Assessment_Officer = "<NAME>"<jupyter_output>* sqlite:///dam_levels.db
Done.<jupyter_text>It seems that data for two dams has been lumped together in the same rows.We'll copy this data, delete these rows in the table, and re-insert them (properly this time). Deleting the repeating group rows:<jupyter_code>%%sql
DELETE 
FROM dam_levels
WHERE Assessment_Officer = "<NAME>";<jupyter_output>* sqlite:///dam_levels.db
3 rows affected.<jupyter_text>Re-insert deleted rows of data:<jupyter_code>%%sql

INSERT INTO dam_levels (year,dam_name,Assessment_Officer,Officer_Reg,water_level,dam_latitude,dam_longitude)
VALUES 
    ( 2012,"STEENBRAS LOWER","V. Mokere",201124,20.3,-34.180527,18.866688),
    ( 2012,"STEENBRAS UPPER","V. Mokere",201124,24.2,-34.166702,18.90976),
    ( 2013,"STEENBRAS LOWER","V. Mokere",201124,22.4,-34.180527,18.866688),
    ( 2013,"STEENBRAS UPPER","V. Mokere",201124,24.6,-34.166702,18.90976),
    ( 2015,"STEENBRAS LOWER","V. Mokere",201124,22.7,-34.180527,18.866688),
    ( 2015,"STEENBRAS UPPER","V. Mokere",201124,24.6,-34.16670,18.90976);<jupyter_output>* sqlite:///dam_levels.db
6 rows affected.<jupyter_text>Let's see if this worked:<jupyter_code>%%sql
SELECT * 
FROM dam_levels<jupyter_output>* sqlite:///dam_levels.db
Done.<jupyter_text>Success! Next, we need to make sure that the rows are uniquely identifiable. The easiest way to achieve this is to add an ID column to the table. However, SQLite does not allow for the addition of constrained columns to existing tables. As such, we are better off creating a new table with the new ID column, copying the old columns across, and deleting the old table.Create the new table structure:<jupyter_code>%%sql

CREATE TABLE dam_levels_1nf (
    AssessmentId INTEGER PRIMARY KEY AUTOINCREMENT,
    year INTEGER,
    dam_name VARCHAR(100),
    Assessment_Officer VARCHAR(100),
    Officer_Reg INTEGER,
    water_level NUMERIC(10,1),
    dam_latitude NUMERIC(3,6),
    dam_longitude NUMERIC(3,6)
);<jupyter_output>* sqlite:///dam_levels.db
Done.<jupyter_text>Next, insert data from the old `dam_levels` table into the new `dam_levels_1nf` table:<jupyter_code>%%sql 

INSERT INTO dam_levels_1nf(year, dam_name, Assessment_Officer, Officer_Reg, water_level, dam_latitude, dam_longitude)
SELECT year, dam_name, Assessment_Officer, Officer_Reg, water_level, dam_latitude, dam_longitude
FROM dam_levels
ORDER BY year;<jupyter_output>* sqlite:///dam_levels.db
18 rows affected.<jupyter_text>Let's do a sanity check:<jupyter_code>%%sql

SELECT *
FROM dam_levels_1nf;<jupyter_output>* sqlite:///dam_levels.db
Done.<jupyter_text>Finally, let's delete the redundant table:<jupyter_code>%%sql

DROP TABLE dam_levels;<jupyter_output>* sqlite:///dam_levels.db
(sqlite3.OperationalError) no such table: dam_levels
[SQL: DROP TABLE dam_levels;]
(Background on this error at: https://sqlalche.me/e/14/e3q8)<jupyter_text>and with that, we finally have a table in **1 NF**!  5.2. Second Normal Form (2 NF)To get the table in **2 NF** we need to ensure that:- 1. The table is in **1 NF** &x2611;   - 2. All partial functional dependencies should be removed &9746; - all non-key columns should depend on the key column, anything else should be placed in a separate table. Looking at the table, we can notice two distinct entities, assessments and dam information. As such, we need to create two tables:  1. **assessments** - with columns: `AssessmentId`, `year`, `Assessment_Officer`, `Officer_Reg`, `water_level`  2. **dams** - with columns: `dam_name`, `dam_latitude`, `dam_longitude`Let's go ahead and create the assessments table:<jupyter_code>%%sql 

CREATE TABLE assessments (
    AssessmentId INTEGER PRIMARY KEY AUTOINCREMENT,
    year INTEGER,
    Assessment_Officer VARCHAR(100),
    Officer_Reg INTEGER,
    water_level NUMERIC(10,1)
);<jupyter_output>* sqlite:///dam_levels.db
Done.<jupyter_text>Copy relevant data into the `assessments` table from `dam_levels_1nf`:<jupyter_code>%%sql

INSERT INTO assessments(AssessmentId, year, Assessment_Officer, Officer_Reg, water_level)
SELECT AssessmentId, year, Assessment_Officer, Officer_Reg, water_level
FROM dam_levels_1nf
ORDER BY year;<jupyter_output>* sqlite:///dam_levels.db
18 rows affected.<jupyter_text>With that completed, we now create the dams table:<jupyter_code>%%sql

CREATE TABLE dams (
    dam_name VARCHAR(100),
    dam_latitude NUMERIC(3,6),
    dam_longitude NUMERIC(3,6)
);<jupyter_output>* sqlite:///dam_levels.db
Done.<jupyter_text>Copy relevant data into the `dams` table from `dam_levels_1nf`:<jupyter_code>%%sql

INSERT INTO dams(dam_name, dam_latitude, dam_longitude)
SELECT dam_name, dam_latitude, dam_longitude
FROM dam_levels_1nf
GROUP BY dam_name;<jupyter_output>* sqlite:///dam_levels.db
6 rows affected.<jupyter_text>At this point, we've created two tables, the *assessments*, and the *dams* tables. However, the assessments and dam information are no longer linked, i.e. we don't know which assessment belongs to which dam. As such, we'll need to create a junction table (i.e. a linking table, in this case, a table that connects an assessment to a dam). To make a valid junction table, we'll need primary keys from both tables:We'll use the `AssessmentId` from the assessments table, and the `dam_name` from the dams table.Create junction table:<jupyter_code>%%sql 

CREATE TABLE dam_assessments (
    AssessmentId INTEGER PRIMARY KEY AUTOINCREMENT,
    dam_name VARCHAR(100)
);<jupyter_output>* sqlite:///dam_levels.db
Done.<jupyter_text>Insert data into the dam_assessments table we just created:<jupyter_code>%%sql

INSERT INTO dam_assessments (AssessmentId,dam_name)
VALUES 

    (1 	, "WEMMERSHOEK"), 
    (2 	, "VOËLVLEI"),
    (3 	, "HELY-HUTCHINSON"),
    (4 	, "WOODHEAD"), 
    (5 	, "<NAME>"), 
    (6 	, "STEENBRAS UPPER"), 
    (7 	, "WEMMERSHOEK"), 
    (8 	, "VOËLVLEI"), 
    (9 	, "HELY-HUTCHINSON"), 
    (10 , "WOODHEAD"), 
    (11 , "STEENBRAS LOWER"), 
    (12 , "STEENBRAS UPPER"), 
    (13 , "WEMMERSHOEK"), 
    (14 , "VOËLVLEI"), 
    (15 , "HELY-HUTCHINSON"),
    (16 , "WOODHEAD"), 
    (17 , "STEENBRAS LOWER"), 
    (18 , "STEENBRAS UPPER");<jupyter_output>* sqlite:///dam_levels.db
18 rows affected.<jupyter_text>Let's check to see if our query worked as expected:<jupyter_code>%%sql

SELECT * 
FROM dam_assessments
LIMIT 10; -- Remove this line to see the full query output<jupyter_output>* sqlite:///dam_levels.db
Done.<jupyter_text>With all our hard work of preparing the 2NF relations, we can go ahead and delete the now redundant `dam_levels_1nf` table:<jupyter_code>%%sql

DROP TABLE dam_levels_1nf;<jupyter_output>* sqlite:///dam_levels.db
Done.<jupyter_text>At this point, we have split the *dam_levels_1nf* table into the following tables:<jupyter_code>%%sql
SELECT name FROM sqlite_master WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%' ORDER BY 1<jupyter_output>* sqlite:///dam_levels.db
Done.<jupyter_text>Since these tables do are in **1 NF**, and do not have any partial functional dependencies, we have successfully normalised our tables into the second normal form!  5.3. Third Normal Form (3 NF)To get the tables in **3 NF** we need to ensure that:- 1. The tables are in **2 NF** &x2611;   - 2. All transitive dependencies should be removed &9746; - for 3 NF, non-key fields must provide a fact about the key, the whole key and nothing but the key, anything else should be placed in a separate table. Let's take a look at the assessments table:<jupyter_code>%%sql

SELECT * 
FROM assessments
LIMIT 10; -- Remove this line to see the full query output<jupyter_output>* sqlite:///dam_levels.db
Done.<jupyter_text>Evidently, the `year`, `water_level`, and `Assessment_Officer` columns provide a fact about the key (i.e. AssessmentId). However, `Officer_Reg` column only provides information about the `Assessment_Officer` column. As such, let's break down the Assessments table and place all officers in their own separate table.To do this, let's create a *water_level_assessments* table:<jupyter_code>%%sql 

CREATE TABLE water_level_assessments (
    AssessmentId INTEGER PRIMARY KEY AUTOINCREMENT,
    year INTEGER,
    Officer_Reg INTEGER,
    water_level NUMERIC(10,1)
);<jupyter_output>* sqlite:///dam_levels.db
Done.<jupyter_text>As an important note, observe that we choose to keep the *Officer_Reg* field within our table instead of *Assessment_Officer*. We do this because the *Officer_Reg* field will need to serve as a [foreign key](https://www.w3schools.com/sql/sql_foreignkey.asp) to a new table we create which will contain all our officer information. Foreign keys are also the primary keys within their respective home tables, and as such need to be unique. In this way, we could have used *Assessment_Officer* as our linking field (foreign key), but we would then run the risk of having two officers with the same name (our key is non-unique) - making our linking relationship invalid. With our new table created, let's copy data into the water_level_assessments table:<jupyter_code>%%sql

INSERT INTO water_level_assessments(AssessmentId, year, Officer_Reg, water_level)
SELECT AssessmentId, year, Officer_Reg, water_level
FROM assessments
ORDER BY year;<jupyter_output>* sqlite:///dam_levels.db
18 rows affected.<jupyter_text>Again, it's a good habit to do a sanity check:<jupyter_code>%%sql

SELECT *
FROM water_level_assessments
LIMIT 10; -- Remove this line to see the full query output<jupyter_output>* sqlite:///dam_levels.db
Done.<jupyter_text>Next, we create an *officers* table to contain the officer data which we removed from the assessments table:<jupyter_code>%%sql 

CREATE TABLE officers (
    Officer_Reg INTEGER PRIMARY KEY,
    Officer_Name VARCHAR(100)    
);<jupyter_output>* sqlite:///dam_levels.db
Done.<jupyter_text>Next, let's add data into the officers table:<jupyter_code>%%sql

INSERT INTO officers(Officer_Name,Officer_Reg)
SELECT Assessment_Officer,Officer_Reg
FROM assessments
GROUP BY Assessment_Officer;<jupyter_output>* sqlite:///dam_levels.db
3 rows affected.<jupyter_text>Checking to see if the data was added correctly:<jupyter_code>%%sql

SELECT * 
FROM officers;<jupyter_output>* sqlite:///dam_levels.db
Done.<jupyter_text>The Officer information (i.e. name and registration number) have been correctly captured. Now we can delete the old assessments table:<jupyter_code>%%sql

DROP table assessments;<jupyter_output>* sqlite:///dam_levels.db
Done.<jupyter_text>With that, we have transformed a table from being unnormalised to the 3rd normal form! We started with a single dam_levels table and ended up with 4 separate table, i.e.:<jupyter_code>%%sql

SELECT name FROM sqlite_master WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%' ORDER BY 1<jupyter_output>* sqlite:///dam_levels.db
Done.
{'fraction_non_alphanumeric': 0.08622502628811778, 'fraction_numerical': 0.024053627760252366, 'mean_word_length': 5.126006441223833, 'pattern_counts': {'":': 0, '<': 88, '<?xml version=': 0, '>': 88, 'https://': 5, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '28', 'dir': 'jupyter-structured-clean-dedup', 'id': '530951', 'license': 'MIT', 'n_tokens_mistral': 5096, 'n_tokens_neox': 4606, 'n_words': 1817, 'path': 'SQL/Normalising_Tables-data_and_notebook-a-1969/8_Normalising_tables.ipynb', 'repo_name': 'George-Kibe/Data-Science', 'size': '15231'}