Explore Data Science Academy
Set Theory, SQL Joins and 3rd Normal Tutorial
Setting up the initial database
1. Create a new database in PostgreSQL – called “Class Lists”
2. Restore the database from the file “[Link]”
3. Take a closer look at the data and ER diagram
• The database contains 6 tables
• Each table contains marks for 5 subjects for a particular month
• Each table contains 100 records for the following fields
• Month (January 2017 or February 2017)
• Class
• Student Details (Name, Gender and Date of Birth)
• Accounting Marks
• Biology Marks
• Science Marks
• Mathematics Mark
• Computer Science Mark
2
Left Join showing full set of A
• Let us define set A as the January Cormorant Science Marks
• Let us define Set B as the February Cormorant Science Marks
• Let us now write a query to show the full set of January Cormorant Science and also all the
February Cormorant Science Marks where the students performed better than in January
(You should get 100 entries. Note also that PostgreSQL returns [null] values when the Feb
mark is lower)
3
Left Join where B is Null
• Let us define set A as the January Cormorant Mathematics Marks
• Let us define Set B as the February Cormorant Mathematics Marks
• Let us now write a query to show the set of students who got a lower mathematics mark in
January than in February
(You should get 54 Entries)
4
Right Join showing full set of B
• Let us define set A as the January Cormorant Biology Marks
• Let us define Set B as the February Cormorant Biology Marks
• Let us now write a query to show the full set of February Cormorant Biology Marks and also
all the January Cormorant Biology Marks where the students got a lower mark in January
5
Right Join where A is Null
• Let us define set A as the January Cormorant Computer Science Marks
• Let us define Set B as the February Cormorant Computer Science Marks
• Let us now write a query to show the set of students who got a lower computer science mark
in February than in January
(You should get 52 Entries)
6
Full Outer Join
• Let us define set A as the January Cormorant Accounting Marks
• Let us define Set B as the February Cormorant Accounting Marks
• Let us now write a query to show Accounting marks in both January and February
7
Full Outer Join where A is not null or B is not null
• Let us define set A as the January Cormorant Accounting Marks
• Let us define Set B as the February Cormorant Accounting Marks
• Let us now write a query to show the set of students who got the different marks in January
and February
(You should get 98 Entries)
8
Inner Join
• Let us define set A as the January Cormorant Accounting Marks
• Let us define Set B as the February Cormorant Accounting Marks
• Let us now write a query to show the set of students who got the same mark in both January
and February
(You should get 2 Entries)
9
rd
Creating a 3 Normal Form Database
1. We want to combine the 6 tables and create a database that is in a 3rd normal form
2. Write some SQL code to join the 6 tables into 1 table using unions
3. Let us create a table with the distinct class list names with a Uuid key
10
4. Let us create a table with the distinct student details with a Uuid key (You should get a table
with 300 records)
5. Let us create a table with the distinct months with a Uuid key
11
6. Let’s now create a table with the list of marks that joins to the tables just created
7. Let’s create foreign keys on the MarkSetRecord table
8. Let’s have a look at the final ER Diagram and check that the database is in the 3rd normal
form
12