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