1 - Constraints and Normalization.ipynb

{
    "metadata": {
        "kernelspec": {
            "name": "SQL",
            "display_name": "SQL",
            "language": "sql"
        },
        "language_info": {
            "name": "sql",
            "version": ""
        }
    },
    "nbformat_minor": 2,
    "nbformat": 4,
    "cells": [
        {
            "cell_type": "markdown",
            "source": "# Clean Data in SQL:  Constraints and Normalization\r\n\r\nIn this notebook, we will look at a data set created from a publically available CSV file.  We will then clean up this data set to make it easier for people to use.\r\n\r\nFirst, let's take a look at the schema.\r\n",
            "metadata": {}
        },
        {
            "cell_type": "code",
            "source": "SELECT\r\n    sc.column_id,\r\n    sc.name,\r\n    t.name AS DataType,\r\n    sc.max_length\r\nFROM sys.columns sc\r\n    INNER JOIN sys.types t\r\n        ON sc.system_type_id = t.system_type_id\r\nWHERE\r\n    sc.object_id = OBJECT_ID('dbo.Police')\r\nORDER BY\r\n    sc.column_id;",
            "metadata": {},
            "outputs": [],
            "execution_count": 1
        },
        {
            "cell_type": "markdown",
            "source": "<img src=\"https://raw.githubusercontent.com/feaselkl/TidyData/master/Images/PoliceSchema.png\" width=\"1000\" />",
            "metadata": {}
        },
        {
            "cell_type": "markdown",
            "source": "\r\n\r\n## Goals\r\n1. Normalize the tables.  This means breaking out Incident Code (LCR) and Incident Description (LCR DESC) into their own table because LCR --> LCR DESC\r\n2. Create primary and unique keys to guarantee uniqueness on tables and prevent duplicate rows from interfering with analysis.\r\n3. Fix names and data types to make analysis easier.\r\n4. Create foreign key constraints to give people an idea of how to link data together.\r\n5. Create check constraints to ensure that invalid data cannot sneak in.\r\n\r\n### Create incident Code",
            "metadata": {}
        },
        {
            "cell_type": "code",
            "source": "IF NOT EXISTS\r\n(\r\n    SELECT 1\r\n    FROM sys.tables t\r\n    WHERE\r\n        t.name = N'IncidentCode'\r\n)\r\nBEGIN\r\n    CREATE TABLE dbo.IncidentCode\r\n    (\r\n        IncidentCode VARCHAR(5) NOT NULL,\r\n        IncidentDescription VARCHAR(55) NOT NULL\r\n    );\r\n    ALTER TABLE dbo.IncidentCode ADD CONSTRAINT [PK_IncidentCode]\r\n        PRIMARY KEY CLUSTERED(IncidentCode);\r\nEND\r\nGO",
            "metadata": {},
            "outputs": [],
            "execution_count": 2
        },
        {
            "cell_type": "markdown",
            "source": "### Populate Incident Code",
            "metadata": {}
        },
        {
            "cell_type": "code",
            "source": "INSERT INTO dbo.IncidentCode\r\n(\r\n\tIncidentCode,\r\n\tIncidentDescription\r\n)\r\nSELECT DISTINCT\r\n\tp.LCR,\r\n\tp.[LCR DESC]\r\nFROM dbo.Police p\r\n    LEFT OUTER JOIN dbo.IncidentCode ic\r\n        ON p.LCR = ic.IncidentCode\r\nWHERE\r\n    ic.IncidentCode IS NULL;\r\nGO",
            "metadata": {},
            "outputs": [],
            "execution_count": 3
        },
        {
            "cell_type": "code",
            "source": "SELECT TOP(15)\r\n    ic.IncidentCode,\r\n    ic.IncidentDescription\r\nFROM dbo.IncidentCode ic;",
            "metadata": {},
            "outputs": [],
            "execution_count": 4
        },
        {
            "cell_type": "markdown",
            "source": "### Create a New Incident Table\r\n\r\nThis `Incident` table will replace the `Police` table as the \"fact\" table in our data set.  It includes critical information around incidents which occur, when they happen, and where they happen.\r\n\r\nWe add an `IncidentID` to give us an internal integer value for clustering, but we want to keep `INC NO` (now renamed to `IncidentNumber`) so we can tie back to our source system.",
            "metadata": {}
        },
        {
            "cell_type": "code",
            "source": "IF NOT EXISTS\r\n(\r\n\tSELECT 1\r\n\tFROM sys.tables t\r\n\tWHERE\r\n\t\tt.name = N'Incident'\r\n)\r\nBEGIN\r\n\tCREATE TABLE dbo.Incident\r\n\t(\r\n\t\tIncidentID INT IDENTITY(1,1) NOT NULL,\r\n\t\tIncidentCode VARCHAR(5) NOT NULL,\r\n\t\tIncidentDate DATETIME,\r\n\t\tBeatID INT NOT NULL,\r\n\t\tIncidentNumber VARCHAR(19) NOT NULL,\r\n\t\tIncidentLocation GEOGRAPHY NULL\r\n\t);\r\n\tALTER TABLE dbo.Incident ADD CONSTRAINT [PK_Incident]\r\n\t\tPRIMARY KEY CLUSTERED(IncidentID);\r\n\tALTER TABLE dbo.Incident ADD CONSTRAINT [FK_Incident_IncidentCode]\r\n\t\tFOREIGN KEY(IncidentCode)\r\n\t\tREFERENCES dbo.IncidentCode(IncidentCode);\r\n\tALTER TABLE dbo.Incident ADD CONSTRAINT [UKC_Incident]\r\n\t\tUNIQUE(IncidentNumber);\r\n\tALTER TABLE dbo.Incident ADD CONSTRAINT [CK_Incident_BeatID]\r\n\t\tCHECK(BeatID >= 0);\r\n\tALTER TABLE dbo.Incident ADD CONSTRAINT [CK_Incident_IncidentDate]\r\n\t\tCHECK(IncidentDate >= '2005-01-01' AND IncidentDate < '2015-01-01');\r\nEND\r\nGO",
            "metadata": {},
            "outputs": [],
            "execution_count": 5
        },
        {
            "cell_type": "markdown",
            "source": "### Populate Incident\r\n\r\nNot all incident dates are legitimate dates, so we will use `TRY_PARSE` to perform this conversion.  Note that the `TRY_PARSE` function is quite slow in comparison to `TRY_CAST` or `TRY_CONVERT`, but it will work easily with our US-based data set and this is an operation we only need to do once.\r\n\r\nFor geographic conversions, we need to take care to check if the location field actually has data in it before attempting to convert it to a latitude and longitude pairing.",
            "metadata": {}
        },
        {
            "cell_type": "code",
            "source": "IF NOT EXISTS\r\n(\r\n    SELECT 1\r\n    FROM dbo.Incident i\r\n)\r\nBEGIN\r\n    INSERT INTO dbo.Incident\r\n    (\r\n        IncidentCode,\r\n        IncidentDate,\r\n        BeatID,\r\n        IncidentNumber,\r\n        IncidentLocation\r\n    )\r\n    SELECT\r\n        p.LCR,\r\n        TRY_PARSE(p.[INC DATETIME] AS DATETIME USING 'en-us') AS IncidentDate,\r\n        p.BEAT,\r\n        p.[INC NO],\r\n        CASE\r\n            WHEN NULLIF(p.[LOCATION], '') IS NULL THEN NULL\r\n            ELSE GEOGRAPHY::STPointFromText('POINT' + REPLACE(p.[LOCATION], ',', ''), 4326)\r\n        END AS IncidentLocation\r\n    FROM dbo.Police p;\r\nEND\r\nGO",
            "metadata": {},
            "outputs": [],
            "execution_count": 6
        },
        {
            "cell_type": "markdown",
            "source": "## End Results\r\n\r\nAfter performing these changes, our end result is a much clearer picture.  Querying the data is pretty straightforward and we also have protections built in to ensure that none of our data rules get violated.  Below is a sample query of the cleaned-up data set.",
            "metadata": {}
        },
        {
            "cell_type": "code",
            "source": "SELECT TOP(10)\r\n    i.IncidentID,\r\n    i.IncidentCode,\r\n    ic.IncidentDescription,\r\n    i.IncidentDate,\r\n    i.BeatID\r\nFROM dbo.Incident i\r\n    INNER JOIN dbo.IncidentCode ic\r\n        ON i.IncidentCode = ic.IncidentCode;",
            "metadata": {},
            "outputs": [],
            "execution_count": 7
        }
    ]
}