Session 5

Last time

We saw:

• OLAP vs OLTP databases
• ERD and how to generate it in pgAdmin, also generating the SQL that creates the schema
• insertion, update and deletion anomalies
• the problem with NULL values and why it's best to avoid them
• anomalies
• normalization
• normal forms: 1NF, 2NF, 3NF

We started working on the normalization of the trees table

---

recap normalization on the domain column

Taking the column domain as example, let's first make sure that the column is a good candidate for normalization

domain is a categorical column, with null values and many uniques. We have inserts, updates and deletion anomalies. normalization is a must.

The process has the following steps:

1. create the new table with correct values

• create a new domains table (id, domain)
• import UNIQUE and NOT NULL domain values from trees to the table domains.
  • Best if values are sorted alphabetically.
  • extra processing may be needed: lowercase, trim, remove special characters

2. reconcile the data in the main trees table

• add a domain_id key in the trees table
• reconcile the foreign key with the primary key: trees.domain_id with the domain.id
• add a constraint on that key so that it is a foreign key in the trees table referencing the domains table

3. check and clean

• check that all trees records have a valid domain_id
• drop the domain column in trees

Today and next time

Window functions and CTEs

We'll use the movies db dataset to work with:

• window functions : ROW_NUMBER(), RANK(), ... , OVER(Partition by ...)
• Common Table Expressions : WITH ... AS (sql) SELECT ....
• practice on the trees db

EOD

• you can write queries using window function and CTEs