CREATE TABLE IF NOT EXISTS employees(
    id INT(4) NOT NULL, 
    surname VARCHAR(20) NOT NULL, 
    first_name VARCHAR(20) NOT NULL, 
    skill VARCHAR(20), 
    CONSTRAINT employees_pk PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS departments(
    name VARCHAR(12), 
    nb_machines INT(3) NOT NULL, 
    nb_employees INT(4) NOT NULL, 
    admin_id INT(4) NOT NULL, 
    CONSTRAINT departments_pk PRIMARY KEY (name), 
    CONSTRAINT departments_fk FOREIGN KEY (admin_id) REFERENCES employees(id)
);

CREATE TABLE IF NOT EXISTS computers(
    dpt_name VARCHAR(12), 
    id INT(3),
    os VARCHAR(7) NOT NULL,
    CONSTRAINT computers_pk PRIMARY KEY (dpt_name, id),
    CONSTRAINT computers_fk FOREIGN KEY (dpt_name) REFERENCES departments(name)
);

CREATE TABLE IF NOT EXISTS assignments(
    dpt_name VARCHAR(12), 
    employee_id INT(4), 
    year INT(4),
    CONSTRAINT assignments_pk PRIMARY KEY (dpt_name, employee_id),
    CONSTRAINT assignments_fk1 FOREIGN KEY (dpt_name) REFERENCES departments(name), 
    CONSTRAINT assignments_fk2 FOREIGN KEY (employee_id) REFERENCES employees(id)
);

