Your first database

Introduction to Relational Databases in SQL

Timo Grossenbacher

Data Journalist

Investigating universities in Switzerland

interactive vis of professors

database schema

A relational database:

real-life entities become tables
reduced redundancy
data integrity by relationships

e.g. professors, universities, companies
e.g. only one entry in companies for the bank "Credit Suisse"
e.g. a professor can work at multiple universities and companies, a company can employ multiple professors

Throughout this course you will:

work with the data I used for my investigation
create a relational database from scratch
learn three concepts:
- constraints
- keys
- referential integrity

You'll need: Basic understanding of SQL, as taught in Introduction to SQL.

Your first duty: Have a look at the PostgreSQL database

SELECT table_schema, table_name 
FROM information_schema.tables;

    table_schema    |              table_name
 -------------------+------------------------------
 pg_catalog         | pg_statistic
 pg_catalog         | pg_type
 pg_catalog         | pg_policy
 pg_catalog         | pg_authid
 pg_catalog         | pg_shadow
 public             | university_professors
 pg_catalog         | pg_settings
...

Have a look at the columns of a certain table

SELECT table_name, column_name, data_type
FROM information_schema.columns 
WHERE table_name = 'pg_config';

 table_name | column_name | data_type
 -----------+-------------+-----------
 pg_config  | name        | text
 pg_config  | setting     | text

Let's do this.

Introduction to Relational Databases in SQL