toets opdracht 1

Flamefever · updated June 11, 2021 · SQL
CREATE TABLE Branch (
    city CHAR(40),
    streetname CHAR(40),
    house_nr INTEGER,
    PRIMARY KEY (city,streetname)
);

CREATE TABLE duration (
    contractid INTEGER PRIMARY KEY,
    ending date,
    start date NOT null
);

CREATE TABLE employee (
    bsn INTEGER PRIMARY KEY,
    name CHAR(40),
    Passportid INTEGER UNIQUE
);

CREATE TABLE child (
    age INTEGER,
    name CHAR(40) PRIMARY KEY
);

CREATE TABLE hourly_emp (
    bsn INTEGER PRIMARY KEY,
    hourly_salary INTEGER,
    FOREIGN KEY (bsn) references employee ON DELETE CASCADE
);

CREATE TABLE regular_emp (
    bsn INTEGER PRIMARY KEY,
    montly_salary INTEGER,
    FOREIGN KEY (bsn) references employee ON DELETE CASCADE
);


CREATE TABLE works_at (
    bsn INTEGER,
    contractid INTEGER,
    streetname CHAR(40),
    PRIMARY KEY (bsn, contractid, streetname),
    FOREIGN KEY (bsn) references employee,
    FOREIGN KEY (contractid) references duration,
    FOREIGN KEY (streetname) references Branch
);

CREATE TABLE supported_by (
    name CHAR(40),
    bsn Integer,
    FOREIGN KEY (name) references child ON DELETE CASCADE,
    FOREIGN KEY (bsn) references employee ON DELETE CASCADE
);

Comments

Please sign up or log in to contribute to the discussion.