01

First create the Employee table https://www.giraffeacademy.com/databases/sql/creating-company-database/

The Primary Keys are in red and the Foreign keys are green

02

Create the Employee table first,

Make sure to drop the previous student table

DROP TABLE student;

The emp_id is the primary key for this table

CREATE TABLE employee (
    emp_id INT PRIMARY KEY,
    first_name VARCHAR(40),
    last_name VARCHAR(40),
    birth_date DATE,
    sex VARCHAR(1),
    salary INT,
    super_id INT,
    branch_id INT
);

Initially super_id and branch_id my be given an int value, they are Foriegn keys but the tables have not been created yet

03

Create the Branch table

The branch_id is the primary key for this table, BUT, the mgr_id is a foreign key from the employee table.

Just like in the employee table we must give it a INT value, then in the last line, assign it as a foreign key.

04

create table branch (
    branch_id INT PRIMARY KEY,
    branch_name VARCHAR(40),
    mgr_id INT,
    mgr_start_date DATE,
    FOREIGN KEY(mgr_id) REFERENCES employee(emp_id) ON DELETE SET NULL
);

When assigining a foreign key, we must reference the table already created with FOREIGH KEY() and at the end ON DELETE SET NULL

05

We can now ALTER the employee table with the mrg_id and branch_id from the branch table

ALTER TABLE employee
ADD FOREIGN KEY(branch_id)
REFERENCES branch(branch_id)
ON DELETE SET NULL;

ALTER TABLE employee
ADD FOREIGN KEY(super_id)
REFERENCES employee(emp_id)
ON DELETE SET NULL;

06

Create client table

CREATE TABLE client (
    client_id INT PRIMARY KEY,
    client_name VARCHAR(20),
    branch_id INT,
    FOREIGN KEY(branch_id) REFERENCES branch(branch_id) ON DELETE SET NULL
);

07

Create the works_with table, this one has two Primary keys which are also Foreign keys from other tables

CREATE TABLE works_with(
    emp_id INT,
    client_id INT,
    total_sales INT,
    PRIMARY KEY(emp_id,client_id),
    FOREIGN KEY(emp_id) REFERENCES employee(emp_id) ON DELETE CASCADE,
    FOREIGN KEY(client_id) REFERENCES client(client_id) ON DELETE CASCADE
);

On the branch_id foreign key ON DELETE is set to CASCADE which will be explained later

08

Create the branch_supplier table

CREATE TABLE branch_supplier (
    branch_id INT,
    supplier_name VARCHAR(20),
    supply_type VARCHAR(20),
    PRIMARY KEY(branch_id, supplier_name),
    FOREIGN KEY(branch_id) REFERENCES branch(branch_id) ON DELETE CASCADE
);