Curriculum

1. Databases

We are going to start by learning about relational databases.

In this module, you will learn some SQL basics and an approach for running a database locally.

1.1. SQL schema

The following is an SQL schema. What do you think this schema represents?
CREATE TABLE Industry (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

CREATE TABLE JobFamily (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    industry_id INT,
    FOREIGN KEY (industry_id) REFERENCES Industry(id)
);

CREATE TABLE JobRole (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    job_family_id INT,
    FOREIGN KEY (job_family_id) REFERENCES JobFamily(id)
);

-- Create the enum type
CREATE TYPE SkillCategory AS ENUM ('Technology', 'Human', 'General', 'Expertises');

-- Create the Skill table
CREATE TABLE Skills (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    category SkillCategory NOT NULL
);

CREATE TABLE JobRoleSkill (
    job_role_id INT,
    skill_id INT,
    PRIMARY KEY (job_role_id, skill_id),
    FOREIGN KEY (job_role_id) REFERENCES JobRole(id),
    FOREIGN KEY (skill_id) REFERENCES Skills(id)
);

Answers

  • This database schema represents the associated skills against a job role
  • Each job role has a job family
  • Each job family is part of an industry
  • A job role can have multiple skills
  • Skills can be categorised according to Technology, Human, General & Expertises

1.2. Choosing our database

There are many different types of databases.

Which database are you going to use to load the schema above?

Answers

  • Only PosgreSQL can run the above.
  • This is because it's using SQL features that MySQL won't support.

1.3. Let's run the following locally

Docker is popular tool for running software inside a container.

How might you run PostgreSQL locally and create a database for our job roles schema?


Hint:

version: '3.1'

services:

  db:
    image: postgres
    restart: always
    environment:
      POSTGRES_PASSWORD: example
    ports:
      - "5432:5432"

  adminer:
    image: adminer
    restart: always
    ports:
      - 8080:8080
Feedback