CSC Database tables

DROP TABLE BugComponent;
DROP TABLE Component;
DROP TABLE Project;
DROP TABLE Bug;
DROP TABLE BugStatus;
DROP TABLE qaengineer;
DROP TABLE ProjectManager;
DROP TABLE Employee;

CREATE TABLE Employee(
        id INT PRIMARY KEY,
        email character varying(256) UNIQUE,
        name character varying(256));

CREATE TABLE qaengineer (
        id INT PRIMARY KEY,
        FOREIGN KEY(id) REFERENCES Employee
);

CREATE TABLE ProjectManager (
        id INT PRIMARY KEY,
        FOREIGN KEY(id) REFERENCES Employee
);

CREATE TABLE BugStatus(id INT PRIMARY KEY, value VARCHAR(20) NOT NULL);


CREATE TABLE bug (
        num integer PRIMARY KEY,
        title character varying(256),
        submission_date DATE,
        owner_id INT,
        status_id INT,
        FOREIGN KEY(owner_id) REFERENCES qaengineer,
        FOREIGN KEY(status_id) REFERENCES BugStatus);


CREATE TABLE Project (
        id INT PRIMARY KEY,
        title VARCHAR(256),
        manager_id INT,
        FOREIGN KEY(manager_id) REFERENCES ProjectManager
    );


CREATE TABLE component (
        id INT PRIMARY KEY,
        title character varying(256),
        project_id INT,
        FOREIGN KEY(project_id) REFERENCES Project
    );


CREATE TABLE BugComponent(
        bug_num INT,
        component_id INT,
        UNIQUE(bug_num, component_id),
        FOREIGN KEY (bug_num) REFERENCES Bug,
        FOREIGN KEY (component_id) REFERENCES Component
    );


INSERT INTO BugStatus(id, value) VALUES (1, 'new'), (2, 'assigned'), (3, 'fixed'), (4, 'verified'), (5, 'not reproducible');
INSERT INTO Project(id, title) VALUES (1001, 'Тетрис 2014'), (1002, 'Windows XXX'), (1003, 'Instagraph'), (1004, 'чебурашка'), (1005, 'skynet');