IMAT5103 Database Systems and Design: EER Modeling and SQL Assignment

This assignment solution provides a comprehensive approach to EER modeling and SQL implementation for a database system, likely completed for the IMAT5103 Database Systems and Design module. It includes the creation of an EER diagram followed by the logical database design, which is then translated into SQL CREATE TABLE statements for various entities such as Academic Qualifications, Staff Approval, Employment Experience, Paper Publications, Publications, Teaching Staff, Admin Staff, and Teaching Experience. The solution also includes ALTER TABLE statements to define foreign key relationships between tables, ensuring referential integrity. Furthermore, it demonstrates the creation of indexes for optimized query performance and provides INSERT statements to populate the tables with sample data. Finally, the assignment includes several SQL SELECT queries demonstrating data retrieval and manipulation, including joins, subqueries, and aggregate functions to address specific data requirements. This resource is ideal for students seeking to understand database design principles and SQL implementation.

EER Diagram:

Logical Database Design:
Create:
CREATE TABLE P17244547_AQ (
staffID char(1) NOT NULL,
fulNameOfAward varchar2(120) NOT NULL,
Subject varchar2(50) NOT NULL,
University varchar2(120) NOT NULL,

PRIMARY KEY (staffID,fulNameOfAward,Subject)
)
CREATE TABLE P17244547_StaffApproval (
staffID char(1) NOT NULL,
aqfLevel varchar2(120) NOT NULL,
Discipline varchar2(180) NOT NULL,
approved_by char(1) NOT NULL,
location varchar2(120) NOT NULL,
approvalDate date NOT NULL,
reviewDate date DEFAULT NULL,
notes varchar2(180) NOT NULL,
PRIMARY KEY (staffID)
)
CREATE TABLE P17244547_EE (
staffID char(1) NOT NULL,
startDate date NOT NULL,
endDate date NOT NULL,
FTE varchar2(120) NOT NULL,

nameOfEmployer varchar2(120) NOT NULL,
positionTitle varchar2(120) NOT NULL,
relevantDuties varchar2(120) NOT NULL,
PRIMARY KEY (staffID,startDate)
)
CREATE TABLE P17244547_Paperpublications (
staffID char(1) NOT NULL,
publishedId char(1) NOT NULL,
priority char(1) NOT NULL,
PRIMARY KEY (staffID,publishedId)
)
CREATE TABLE P17244547_publications (
publishedId char(1) NOT NULL,
yearOfPublication number(4) NOT NULL,
titleOfPublication varchar2(180) NOT NULL,
Journal varchar2(120) NOT NULL,

volume varchar2(120) NOT NULL,
typeOfPublication varchar2(120) NOT NULL,
reviewed varchar2(120) NOT NULL,
researchClassification varchar2(120) NOT NULL,
fieldOfEducation varchar2(120) NOT NULL,
PRIMARY KEY (publishedId)
)
CREATE TABLE P17244547_TeachingStaff (
staffID char(1) NOT NULL,
Title varchar2(5) NOT NULL,
firstName varchar2(120) NOT NULL,
lastName varchar2(120) NOT NULL,
Address varchar2(120) NOT NULL,
Email varchar2(120) NOT NULL,
Phone varchar2(20) NOT NULL,
PRIMARY KEY (staffID)
)

CREATE TABLE P17244547_AdminStaff (
adminStaffID char(1) NOT NULL,
Title varchar2(5) NOT NULL,
firstName varchar2(120) NOT NULL,
lastName varchar2(120) NOT NULL,
Address varchar2(120) NOT NULL,
Email varchar2(120) NOT NULL,
Phone varchar2(20) NOT NULL,
PRIMARY KEY (adminStaffID)
)
CREATE TABLE P17244547_TeachingExperience (
staffID char(1) NOT NULL,
Teaching_period varchar2(120) NOT NULL,
course varchar2(120) NOT NULL,
institution varchar2(120) NOT NULL,
Role varchar2(120) NOT NULL,
PRIMARY KEY (staffID,Teaching_period,course,institution)

)
ALTER TABLE P17244547_AQ
ADD CONSTRAINT academic_qualifications_ibfk_1 FOREIGN KEY (staffID)
REFERENCES P17244547_TeachingStaff (staffID);
ALTER TABLE P17244547_StaffApproval
ADD CONSTRAINT approval_ibfk_1 FOREIGN KEY (staffID) REFERENCES
P17244547_TeachingStaff (staffID);
ALTER TABLE P17244547_StaffApproval
ADD CONSTRAINT approval_ibfk_2 FOREIGN KEY (approved_by) REFERENCES
P17244547_AdminStaff (adminStaffID);
ALTER TABLE P17244547_EE
ADD CONSTRAINT EmploymentExperience_ibfk_1 FOREIGN KEY (staffID)
REFERENCES P17244547_TeachingStaff (staffID);
ALTER TABLE P17244547_Paperpublications
ADD CONSTRAINT PaperPublished_ibfk_1 FOREIGN KEY (staffID)
REFERENCES P17244547_TeachingStaff (staffID);

ALTER TABLE P17244547_Paperpublications
ADD CONSTRAINT PaperPublished_ibfk_2 FOREIGN KEY (publishedId)
REFERENCES P17244547_publications (publishedId);
ALTER TABLE P17244547_TeachingExperience
ADD CONSTRAINT TeachingExperience_ibfk_1 FOREIGN KEY (staffID)
REFERENCES P17244547_TeachingStaff (staffID);
Index:
CREATE INDEX idx_teacher on P17244547_TeachingStaff (firstName, lastName)
TABLESPACE Users;
CREATE INDEX idx_StaffApproval on P17244547_StaffApproval (notes, aqfLevel)
TABLESPACE Users;
CREATE INDEX idx_publications on P17244547_publications (fieldOfEducation,
yearOfPublication) TABLESPACE Users;
CREATE INDEX idx_AdminStaff on P17244547_AdminStaff (firstName, lastName)
TABLESPACE Users;
Insert:
INSERT INTO AcademicQualifications (staffID, fulNameOfAward, Subject, University)
VALUES
('4', 'Bachelors Honours', 'ICT', 'University of Tasmania');

INSERT INTO AcademicQualifications (staffID, fulNameOfAward, Subject, University)
VALUES
('3', 'Bachelors Honours', 'ICT', 'University of Tasmania');
INSERT INTO AcademicQualifications (staffID, fulNameOfAward, Subject, University)
VALUES
('2', 'Bachelors Honours', 'ICT', 'University of Tasmania');
INSERT INTO AcademicQualifications (staffID, fulNameOfAward, Subject, University)
VALUES
('1', 'Masters of IT', 'Database Programming', 'University of the Sunshine Coast');
INSERT INTO AcademicQualifications (staffID, fulNameOfAward, Subject, University)
VALUES
('5', 'Masters of IT', 'Database Programming', 'University of the Sunshine Coast');
INSERT INTO StaffApproval (staffID, aqfLevel, Discipline, approved_by, location,
approvalDate, reviewDate, notes) VALUES
('2', 'Level 8', 'Bachelor Honours Degree Graduate Certificate or Graduate Diploma', '1',
'Brisbane', TO_DATE('2018-09-10', 'yyyy/mm/dd'), TO_DATE('2019-09-10', 'yyyy/mm/dd'),
'Terminate');
INSERT INTO StaffApproval (staffID, aqfLevel, Discipline, approved_by, location,
approvalDate, reviewDate, notes) VALUES

('3', 'Level 9', 'Masters', '2', 'Sydney', TO_DATE('2017-07-27', 'yyyy/mm/dd'),
TO_DATE('2018-03-19', 'yyyy/mm/dd'), 'Continue');
INSERT INTO StaffApproval (staffID, aqfLevel, Discipline, approved_by, location,
approvalDate, reviewDate, notes) VALUES
('1', 'Level 10', 'PhD', '1', 'Brisbane', TO_DATE('2018-09-10', 'yyyy/mm/dd'),
TO_DATE('2019-09-10', 'yyyy/mm/dd'), 'Warning');
INSERT INTO StaffApproval (staffID, aqfLevel, Discipline, approved_by, location,
approvalDate, reviewDate, notes) VALUES
('5', 'Level 9', 'Masters', '2', 'Sydney', TO_DATE('2017-07-27', 'yyyy/mm/dd'),
TO_DATE('2018-03-19', 'yyyy/mm/dd'), 'Warning');
INSERT INTO StaffApproval (staffID, aqfLevel, Discipline, approved_by, location,
approvalDate, reviewDate, notes) VALUES
('4', 'Level 9', 'Masters', '1', 'Sydney', TO_DATE('2017-07-27', 'yyyy/mm/dd'),
TO_DATE('2018-03-19', 'yyyy/mm/dd'), 'Terminate');
INSERT INTO EmploymentExperience (staffID, startDate, endDate, FTE,
nameOfEmployer, positionTitle, relevantDuties) VALUES
('3', TO_DATE('2017-11-11', 'yyyy/mm/dd'), TO_DATE('2018-07-11', 'yyyy/mm/dd'),
'casual', 'ABC', 'ABC', 'asdasd');
INSERT INTO EmploymentExperience (staffID, startDate, endDate, FTE,
nameOfEmployer, positionTitle, relevantDuties) VALUES

('2', TO_DATE('2017-11-11', 'yyyy/mm/dd'), TO_DATE('2018-07-11', 'yyyy/mm/dd'),
'casual', 'DEF', 'DEF', 'asddsd');
INSERT INTO EmploymentExperience (staffID, startDate, endDate, FTE,
nameOfEmployer, positionTitle, relevantDuties) VALUES
('2', TO_DATE('2017-09-11', 'yyyy/mm/dd'), TO_DATE('2018-07-11', 'yyyy/mm/dd'),
'Full Time', 'GHI', 'GHI', 'sdsdsd');
INSERT INTO EmploymentExperience (staffID, startDate, endDate, FTE,
nameOfEmployer, positionTitle, relevantDuties) VALUES
('5', TO_DATE('2018-08-11', 'yyyy/mm/dd'), TO_DATE('2018-10-01', 'yyyy/mm/dd'),
'Part-time', 'ZXY', 'ZXY', 'qweqwe');
INSERT INTO EmploymentExperience (staffID, startDate, endDate, FTE,
nameOfEmployer, positionTitle, relevantDuties) VALUES
('1', TO_DATE('2018-08-11', 'yyyy/mm/dd'), TO_DATE('2018-10-01', 'yyyy/mm/dd'),
'Part-time', 'ZXY', 'ZXY', 'qweqwe');
INSERT INTO EmploymentExperience (staffID, startDate, endDate, FTE,
nameOfEmployer, positionTitle, relevantDuties) VALUES
('4', TO_DATE('2018-08-11', 'yyyy/mm/dd'), TO_DATE('2018-10-01', 'yyyy/mm/dd'),
'Full Time', 'ZXY', 'ZXY', 'qweqwe');
INSERT INTO Paperpublications (staffID, publishedId, priority) VALUES
('5', '1', '3');

INSERT INTO Paperpublications (staffID, publishedId, priority) VALUES
('1', '1', '1');
INSERT INTO Paperpublications (staffID, publishedId, priority) VALUES
('3', '3', '2');
INSERT INTO Paperpublications (staffID, publishedId, priority) VALUES
('4', '2', '1');
INSERT INTO Paperpublications (staffID, publishedId, priority) VALUES
('1', '2', '2');
INSERT INTO Paperpublications (staffID, publishedId, priority) VALUES
('2', '1', '1');
INSERT INTO Paperpublications (staffID, publishedId, priority) VALUES
('3', '4', '1');
INSERT INTO Paperpublications (staffID, publishedId, priority) VALUES
('2', '5', '1');
INSERT INTO publications (publishedId, yearOfPublication, titleOfPublication, Journal,
volume, typeOfPublication, reviewed, researchClassification, fieldOfEducation) VALUES
('1', '2016', 'Stream: The stanford data stream management system', 'In Data Stream
Management', 'Vol 11', 'Journal article', 'No', 'Scholarship', 'ICT');

INSERT INTO publications (publishedId, yearOfPublication, titleOfPublication, Journal,
volume, typeOfPublication, reviewed, researchClassification, fieldOfEducation) VALUES
('2', '2016', 'Fundamentals of database systems', 'London: Pearson', 'Vol 2', 'Book', 'No',
'Research', 'Database');
INSERT INTO publications (publishedId, yearOfPublication, titleOfPublication, Journal,
volume, typeOfPublication, reviewed, researchClassification, fieldOfEducation) VALUES
('3', '2014', 'PALEOMAGIA: A PHP/MYSQL database of the Precambrian
paleomagnetic data', 'Studia Geophysica et Geodaetica', 'Vol 3', 'Journal article', 'Yes', 'Research',
'Database');
INSERT INTO publications (publishedId, yearOfPublication, titleOfPublication, Journal,
volume, typeOfPublication, reviewed, researchClassification, fieldOfEducation) VALUES
('4', '2017', 'Database Management System', 'International Journal of Engineering
Science', 'Vol 23', 'Book', 'No', 'Scholarship', 'ICT');
INSERT INTO publications (publishedId, yearOfPublication, titleOfPublication, Journal,
volume, typeOfPublication, reviewed, researchClassification, fieldOfEducation) VALUES
('5', '2014', 'The Genomes OnLine Database (GOLD) v. 5: a metadata management
system based on a four level (meta) genome project classification',
'Nucleic acids research', 'Vol 13', 'Journal article', 'Yes', 'Research', 'Database');
INSERT INTO AdminStaff (adminStaffID, Title, firstName, lastName, Address, Email,
Phone) VALUES
('1', 'Mr.', 'Gabriel', 'Cutlack', '92 Campbells River Road

QUANDA NSW 2828', '[e-mail]', '[phone]');
INSERT INTO AdminStaff (adminStaffID, Title, firstName, lastName, Address, Email,
Phone) VALUES
('2', 'Mr.', 'Cody', 'Ellis', '80 Gloucester Avenue
CROYDON PARK SOUTH SA 5008', '[e-mail]', '[phone]');
INSERT INTO TeachingStaff (staffID, Title, firstName, lastName, Address, Email,
Phone) VALUES
('1', 'Mrs.', 'Alicia', 'Rusconi', '40 Ugoa Street
WHOOTA NSW 2428', '[e-mail]', '[phone]');
INSERT INTO TeachingStaff (staffID, Title, firstName, lastName, Address, Email,
Phone) VALUES
('2', 'Mr.', 'Lincoln', 'Bowden', '69 Point Walter Road
ATWELL WA 6164', '[e-mail]', '[phone]');
INSERT INTO TeachingStaff (staffID, Title, firstName, lastName, Address, Email,
Phone) VALUES
('3', 'Ms.', 'Annabelle', 'Bateson', '64 Paradise Falls Road
BOWSER VIC 3678', '[e-mail]', '[phone]');
INSERT INTO TeachingStaff (staffID, Title, firstName, lastName, Address, Email,
Phone) VALUES

('4', 'Mrs.', 'Eva', 'Mirams', '20 Ronald Crescent
HERON ISLAND QLD 4680', '[e-mail]', '[phone]');
INSERT INTO TeachingStaff (staffID, Title, firstName, lastName, Address, Email,
Phone) VALUES
('5', 'Mr.', 'Matthew', 'Stonehaven', '3 Jacabina Court
WARRAWONG NSW 2502', '[e-mail]', '[phone]');
INSERT INTO TeachingExperience (staffID, Teaching_period, course, institution, Role)
VALUES
('1', 'January 2017 – Novenmer 2017', 'ICT', 'Victoria University', 'Tutor');
INSERT INTO TeachingExperience (staffID, Teaching_period, course, institution, Role)
VALUES
('5', 'February 2018 – Novenmer 2017', 'ICT', 'USQ', 'Lecturer');
INSERT INTO TeachingExperience (staffID, Teaching_period, course, institution, Role)
VALUES
('5', 'January 2017 – December 2017', 'ICT', 'Victoria University', 'Lecturer');
INSERT INTO TeachingExperience (staffID, Teaching_period, course, institution, Role)
VALUES
('4', 'January 2017 – Novenmer 2017', 'ICT', 'USQ', 'Tutor');

SQL Query:
Select s.staffID, Title, firstName || ' ' || lastName AS Full_Name, fulNameOfAward as
Full_Name_Of_Award, Subject From P17244547_TeachingStaff s inner join P17244547_AQ
On s.staffID = P17244547_AQ.staffID ORDER By s.lastName;
SELECT s.staffID, Title, firstName || ' ' || lastName AS Full_Name, ap.notes From
P17244547_TeachingStaff s left join P17244547_StaffApproval ap ON s.staffID = ap.staffID
WHERE ap.notes Like '%Ter%';
SELECT s.staffID, Title, firstName || ' ' || lastName AS Full_Name From
P17244547_TeachingStaff s WHERE s.staffID NOT IN (SELECT staffID FROM
P17244547_TeachingExperience);
SELECT aqfLevel, COUNT(staffID) AS TotalP17244547_TeachingStaff FROM
P17244547_StaffApproval GROUP BY aqfLevel;
Select P.titleOfPublication, firstName || ' ' || lastName AS Full_Name from
P17244547_publications P left outer join P17244547_Paperpublications PP on P.publishedId =
PP.publishedId left outer join P17244547_TeachingStaff T on PP.staffID = T.staffID Where
P.fieldOfEducation Like 'Database';