Table of Content
Chapter 1 - Project Overview
Chapter 2 - Import data
Chapter 3 - Create tables and populate them
Chapter 4 - Set constraints and relationship
Chapter 5 - Final two tips
Chapter 1 Project Overview
Normalisation is a process to store data while reducing data redundancy and keeping data consistency at the same time, it follows the common 3 Normal Forms and 2 Strict Normal Forms plus the Boyce-Codd Normal Form. This project shows how to split a complete table into a database with appropriate relationships and constraints in SQL Server.
This project mainly focuses on logic and techniques. There are two aspects this project show:
- The method of extending the dimension tables (Snowflake Schema)
- The achievement of referential integrity (Build primary key and foreign key relationship, Choose actions on update or delete)
You can find my complete SQL codes on my GitHub.
The data comes from Trenton Mckinney's GitHub.
Chapter 2 Import data
SQL Server 2019 Import and Export Data Wizard imported an Excel table into my SQL Server Database. Then, have a look at the original table.
It has 8 attributes:
To achieve the map of tables below, the project will use a snowflake schema to organise these attributes and add two auto-incrementing ids for dim_professors and fact_affiliations, respectively.
-- Example to add surrgate key while setting it as primary key ALTER TABLE dim_professors ADD id INT IDENTITY(1,1) PRIMARY KEY;
Chapter 3 Create tables and populate them
This database mainly records what role professors play in each organisation, a professor can have different functions in an organisation of one university for the fact table, and professors, universities and organisations have their own dimension tables.
So, it has 1 fact table and 3 dimension tables
affiliations (fact table)
professors (dimension table)
organisations (dimension table)
universities (dimension table)
There are 3 things to consider when creating tables
Most attributes are varchar(), the maximum should be decided by the maximum length of the current record but should leave some space.
The column university_shortname should follow the fixed format and use char(3).
Most attributes should not be null except the [function] attribute.
For affiliations and professors, the primary key can use created surrogate key.
For organisations and universities, the names of organisations and universities are good choices to identify each row.
With CREATE, VARCHAR(), and PRIMARY KEY, the four empty tables are created in SQL Server.
With INSERT INTO, SELECT DISTINCT columns FROM the original table, they are populated.
Chapter 4 Set constraints and relationships
The final relationships should be
M:1 relationship from professors to universities with university_shortname column
N: M relationship from professors to organisations with affiliations table, divide it into 2 steps:
1:N relationship from professors to affiliations with professor_id column
1:M relationship from organisations to affiliations with organisation column
The key part of this chapter is to talk about how to achieve the connection between organisations and professors with affiliations. Because a professor can have many functions in an organisation while an organisation can have many professors, there is a many-to-many relationship. A method is to use an intermediate table to become the bridge.
Step 1 a. Add a professor_id column in the affiliations
b. Reference the professor_id from professors to affiliations;
c. Set when a deletion is attempted in the referenced table (i.e., professors), it will not be allowed if there are any dependent rows in the referencing table (i.e., affiliations)
ALTER TABLE affiliations ADD professor_id INT, CONSTRAINT fk_affiliations_professors FOREIGN KEY (professor_id) REFERENCES professors(id) ON DELETE NO ACTION;
Step 2 Populate professor_id using the professors table
UPDATE affiliations SET professor_id = professors.id FROM affiliations JOIN professors ON affiliations.firstname = professors.firstname AND affiliations.lastname = professors.lastname;
Step 3 Drop firstname and lastname
ALTER TABLE affiliations DROP COLUMN firstname; ALTER TABLE affiliations DROP COLUMN lastname;
Step 4 1:M relationship from organisations to affiliations with referential integrity
ALTER TABLE affiliations ADD CONSTRAINT fk_affiliations_organisations FOREIGN KEY (organisation) REFERENCES organisations(organisation) ON DELETE NO ACTION;
The final diagram shows the complete database
Chapter 5 Final Two tips
Tip 1 Referential Integrity: ON DELETE NO ACTION
To keep data consistent, there are some options for referenced tables when a row is deleted. The project chose NO ACTION, it prevents the deletion of rows if it is referenced. Test it with
Tip 2 INFORMATION_SCHEMA
The INFORMATION_SCHEMA can check tables, columns, constraints and other metadata.
SELECT constraint_name, table_name, constraint_type FROM INFORMATION_SCHEMA.table_constraints;
The constraints will be extracted in a table, super convenient, right?