supervisor_id INT level ENUM(…) utas_start DATE

researcher id INT type ENUM(…) given_name VARCHAR(20) family_name VARCHAR(20) title VARCHAR(10) unit VARCHAR(64) campus ENUM(…) email VARCHAR(50) photo VARCHAR(512) degree VARCHAR(16) supervisor_id INT level ENUM(…) utas_start DATE current_start DATE Indexes position id INT level ENUM(…) start DATE end DATE Indexes publication doi VARCHAR(256) title VARCHAR(256) authors VARCHAR(256) year YEAR type ENUM(…) cite_as VARCHAR(1024) available DATE Indexes researcher_publication researcher_id INT doi VARCHAR(256) Indexes A (student) researcher has a supervisor who is a (staff) researcher: suprvisor_id is a nullable column containing the ID of the student’s supervisor To improve query performance the current employment level (if staff), earliest position’s start date and current position’s start date are duplicated in the researcher table, although they could be derived from the position table’s contents Enum-valued columns: researcher.type is one of ‘Staff’, ‘Student’ researcher.campus is one of ‘Hobart’, ‘Launceston’, ‘Cradle Coast’ researcher.level and position.level are one of ‘A’, ‘B’, ‘C’, ‘D’, ‘E’ publication.type is one of ‘Conference’, ‘Journal’, ‘Other’ Foreign key details: researcher_publication.researcher_id = researcher.id researcher_publication.doi = publication.doi researcher.supervisor_id = researcher.id (the ID of the supervising staff member) position.id = researcher.id Employment and Research Database EER Diagram

Leave a Reply

Your email address will not be published.