queries.sql

CREATE TABLE Ethiopian_universities (
    id int PRIMARY KEY,
    locationMain text,
    establishmentYear text,
    president_id int UNIQUE,
    FOREIGN KEY (president_id) REFERENCES president(id)
);

CREATE TABLE president (
    id int PRIMARY KEY,
    name text,
    startYear text,
    endYear text
);

CREATE TABLE college (
    id int PRIMARY KEY,
    name text,
    campus text,
    establishedYear int, 
    dean_id int UNIQUE,
    university_id int,
    FOREIGN KEY (dean_id) REFERENCES staff(id),
    FOREIGN KEY (university_id) REFERENCES mekelle_university(id)
);

CREATE TABLE department (
    id int PRIMARY KEY,
    name text,
    head_id int UNIQUE,
    college_id int,
    FOREIGN KEY (head_id) REFERENCES staff(id),
    FOREIGN KEY (college_id) REFERENCES college(id)
);

CREATE TABLE staff (
    id int PRIMARY KEY,
    firstName text,
    lastName text,
    birthdate int,
    sex text,
    employmentYear int,
    department_id int,
    educationLevel text,
    isExpatrate boolean,
    isResearcher boolean
);

CREATE TABLE student (
    id int PRIMARY KEY,
    name text,
    sex text,
    birthdate int,
    department_id int,
    startYear int,
    endYear int,
    isInternational boolean,
    FOREIGN KEY (department_id) REFERENCES department(id)
);

CREATE TABLE alumni (
    id int PRIMARY KEY,
    name text,
    sex text,
    birthdate int,
    department_id int,
    startYear int,
    endYear int,
    isInternational boolean,
    FOREIGN KEY (department_id) REFERENCES department(id)
);

CREATE TABLE research_project (
    id int PRIMARY KEY,
    projectTitle text,
    coordinator text,
    startYear int,
    endYear int,
    sponsor text
);

CREATE TABLE publication (
    id int PRIMARY KEY,
    title text,
    publicationYear int,
    type text
);

CREATE TABLE authors (
    id int PRIMARY KEY,
    name text,
    affiliation text
);

CREATE TABLE research_project_staff (
    project_id int,
    staff_id int,
    PRIMARY KEY (project_id, staff_id),
    FOREIGN KEY (project_id) REFERENCES research_project(id),
    FOREIGN KEY (staff_id) REFERENCES staff(id)
);

CREATE TABLE research_project_student (
    project_id int,
    student_id int,
    PRIMARY KEY (project_id, student_id),
    FOREIGN KEY (project_id) REFERENCES research_project(id),
    FOREIGN KEY (student_id) REFERENCES student(id)
);

CREATE TABLE publication_staff (
    publication_id int,
    staff_id int,
    PRIMARY KEY (publication_id, staff_id),
    FOREIGN KEY (publication_id) REFERENCES publication(id),
    FOREIGN KEY (staff_id) REFERENCES staff(id)
);

CREATE TABLE publication_student (
    publication_id int,
    student_id int,
    PRIMARY KEY (publication_id, student_id),
    FOREIGN KEY (publication_id) REFERENCES publication(id),
    FOREIGN KEY (student_id) REFERENCES student(id)
);

drop table publication_student;

CREATE TABLE publication_alumni (
    publication_id int,
    alumni_id int,
    PRIMARY KEY (publication_id, alumni_id),
    FOREIGN KEY (publication_id) REFERENCES publication(id),
    FOREIGN KEY (alumni_id) REFERENCES alumni(id)
);

CREATE TABLE authors_staff (
    author_id int,
    staff_id int,
    PRIMARY KEY (author_id, staff_id),
    FOREIGN KEY (author_id) REFERENCES authors(id),
    FOREIGN KEY (staff_id) REFERENCES staff(id)
);

CREATE TABLE authors_student (
    author_id int,
    student_id int,
    PRIMARY KEY (author_id, student_id),
    FOREIGN KEY (author_id) REFERENCES authors(id),
    FOREIGN KEY (student_id) REFERENCES student(id)
);

CREATE TABLE authors_alumni (
    author_id int,
    alumni_id int,
    PRIMARY KEY (author_id, alumni_id),
    FOREIGN KEY (author_id) REFERENCES authors(id),
    FOREIGN KEY (alumni_id) REFERENCES alumni(id)
);