根据提供的Casual Hire Recruitment System的材料，绘制 E-R
图，以及MySQL脚本。
The Tasmanian Institute of Technology and Research Science - Casual Hire
Recruitment System
The Tasmanian Institute of Technology and Research Science has need for a new
casual tutor hiring system. The TITRS hires a casual workforce of between 900
- 1500 casual and fixed term contract employees every year. TITRS has eight
faculties and over 140 departments. A department can only belong to one
faculty.
TITRS has four semesters in any calendar year: Summer; Autumn: Winter and
Spring. Summer and Winter semesters are 6 weeks of intensive study, and Autumn
and Spring are the standard 12 week semester with a mid semester break after
week 6.
TITRS has a number of different timetable and non timetable casual roles.
Timetabled roles are Tutors and Demonstrators. The non-timetabled roles
include Team Leader, Marker, Supervisor, and a catch all category for
meetings, invigilation, forum monitoring, industry liaison labelled as “Other
Academic Activity”. Tutors are paid to prepare tutorials. Demonstrators are
not paid any preparation time.
Therefore, if a tutor is delivering a one hour tutorial they are paid for one
hour to prepare, two hour tutorial, two hours and so on. This is only for the
first tutorial, they are not paid any preparation time for the second, and
subsequent tutorials they deliver for that subject (Please see the Appendix
for more information).
Any suitably qualified person can apply for these positions however most
Faculties and Departments expect candidates to hold the degree they wish to
tutor at. For example, you are expected to hold a Bachelor degree to tutor and
assess (mark) Bachelor subjects. Masters subjects expect applicants to hold a
Master’s degree and some Master’s tutors already hold a PhD. Applicants who
hold a PhD are entitled to a higher rate of pay (refer Appendix A).
Applicants can nominate up to a maximum of four subjects in each Department
each semester. Applicants must prioritise this list from 1 to 4. As part of
the process, applicants must provide their title, full name, personal email
address, Australian tax file number, Working With Children Check number
(1234567Z-A1), and nominate their availability from 7am - 10pm Monday to
Saturday in hourly increments. In nominating their available times to work,
applicants must specify a first and second preference as well as any times
they are unavailable to work. Applicants must submit a full academic
transcript of their last completed degree, a pdf of their Curriculum Vitae
(Resume), and their Australian visa/citizenship status.
The list of semester subjects and classes that will run is available 6 weeks
before the beginning of each semester. Each subject will have a combination of
some, or all of, lectures (L01), Seminars (S01), Tutorials (T01) and
Practicals (sometimes referred to as labs or demonstrations) P01, and
Workshops (W01) which are a 60 minute tutorial followed by a 60 minute
practical. All workshops are 120 minutes in duration.
For each tutorial, practical or workshop we store the venue, which includes
the room number, room name, floor number (G, 1,2,3 etc), building name and
building number, the venue capacity, it’s GPS coordinates (latitude and
longitude eg Centenary building is -42.902570 latitude and 147.327679
longitude), and a campus map of the building with a star indicating its
location. Each class runs a minimum of 60 minutes to a maximum of 6 hours in
30-minute increments. Classes can run a minimum of half the term length (e.g.
3 weeks for Winter, Summer; 6 weeks for Autumn, Spring). We need to know which
week of the calendar year the class is running, the day name and date. Venues
can change during the semester. For example, LAW20200 Consumer Law may run for
4 weeks (Week numbers 10-13) in one venue (e.g. Joannah Storey Lecture Theatre
Ground Floor Owen Dixon Law Building) and then change to another venue (e.g.
Upper Theatre, Simon Greenleaf building) for weeks 5 until 12 (Week numbers
14-15, 17-22) of the semester.
Each tutorial, practical or workshop must have at least one tutor allocated to
it, but some classes can have up to four tutors allocated to the same tutorial
at the same time. The Appendix provides some - but not all - information about
the venues, subject names, class names to help support your database design.
Every Subject has at least one or more subject coordinators. The subject
coordinator is responsible for selecting, rejecting and reviewing the
candidate applications. A subject coordinator can coordinate more than one
subject in the same semester. About subject coordinators, we record their
title, full name, TITRS employee number, email address, office location on
campus and an office contact phone number as well as the department and
faculty to which they belong.
Once a candidate application has been submitted it is reviewed by the subject
coordinator. The application is either rejected, or shortlisted. If a
shortlisted application is successful it then moves to either one of two
statuses, waitlisted, or successful. Waitlisted applications are applications
that may get hired if there are sufficient student enrolments. Only candidates
that are selected as ‘successful’ by the Subject Coordinator can be allocated
to a class (tutorial, practical, workshop) in that subject or be given non
timetabled roles (e.g. Marker, Supervisor, Head Tutor) for that subject. Non
timetabled roles must be specified in total hours across that teaching
semester.
Each subject coordinator can set up an email template to notify applicants of
their application outcome. Each subject can have a submitted, shortlisted,
unsuccessful, waitlisted and successful email template. The length of the
template cannot exceed 1500 characters, about the template we store the FROM
email address, Subject title and the body of the email. The recipient list is
derived from the candidate status for that subject for that year and semester.
TASK: Your group has been asked to provide:
I) A Conceptual model of the TITRS case study in Chen notation
II) A physical Entity Relationship model using Crows Foot notation suitable
for a MySQL relational database version 8.0 or higher. The physical E.R model
should be based on your Chen conceptual model.
Assignment Submission
- ONE GROUP MEMBER should submit the assignment via the CANVAS LMS
- GROUP AGREEMENT
- ONE PDF document named as your Group number id (e.g. Group20.pdf)
- Legible image of your Conceptual Model in Chen notation
- Legible image of your Physical ER Model in Crows foot notation
- Assumptions (maximum 100 words) - your models should speak for themselves.
- Work break down per team member (measured 1-100% per team member)
- Student name and Student Number of all students
N.B. If you fail to submit legible models you will be penalised 10% of your
total grade for this assignment.
ONE COPY of your team’s final MySQL Workbench modelling file (with an .mwb
extension) of the
Physical ER model on or before 0900H (9 a.m.) 4 th September 2020 AEST 2020.
Submit your .mwb file under the mwb Assignment 1 submission link.
Late Submissions
Assignments that are late without a formal granted deadline extension from the
subject coordinator will attract a penalty of 10% for each Academic Day as per
the School of Computing and Information Systems policy.
Subject Hurdle
To pass INFO90002 you must pass two hurdles. First you must obtain a mark of
15/30 or higher for both assignments (Assignment 1 20%, Assignment 2 10%) AND
obtain a mark of 35/70 or higher for the end of semester assessment (exam).
Examples
Alice’s team obtains 18/20 for assignments 1, and Alice obtained 3/10 for
Assignment 2, and Alice obtains 52/70 for the exam. Alice’s final grade is
73/100 H2B. Alice passed the Assignment hurdle (21/30) and the exam hurdle
(52/70).
Bob’s team obtained 19/20 for the assignment 1. Bob obtained 9/10 for
Assignment 2, and Bob obtained 33/70 for the exam. Bob’s final grade is NH 49
(hurdle fail). Despite the fact that his overall grade is 61/100. Bob did not
pass the exam hurdle (35/70).
Carol did not join a team for assignment 1 and obtained (0/20). Carol obtained
9/10 for Assignment 2 and obtained 63/70 for the exam. Carol fails the subject
with a NH 49 (hurdle fail) despite a final grade of 72. Carol did not pass the
Assignment hurdle (9/30)
Be sure you submit all assignments and attempt every question on the end of
semester assessment to optimise your chance of passing INFO90002
Frequently Asked Questions
- Do all images and links need to be stored in the database? A: YES. As a database designer you are required to store every attribute you think is required inside the database.
- How do I submit a high resolution image of our conceptual and physical design? A: We recommend using an A3 page size in MySQL Workbench and exporting the image as a PDF
- How do I join my four five different documents together? A: Save all your documents as PDFs and then merge each PDF into a master document. Another method is to upload all documents to a single Google doc and then download as a single PDF
- How do I make MySQL workbench show PFK’s (Primary Foreign Keys) A: You can’t. This was a bug introduced into 5.7 of Workbench on Mac which now affects all releases of 8.0.x of MySQL
Workbench. Markers are well aware of the issue and know what to look for. We
have logged a bug with the development team at MySQL
Group Work Advice
Industry expects our Masters graduates to be able to work and communicate
effectively in teams. This is why the University includes group work
assessment in the majority of graduate classes.
When you form your team immediately decide the following:
- How you will communicate to each other?
- How often you will communicate?
- How often you will meet as a group?
- Agree on a communication escalation path
* E.G. WhatsApp - then if no response, SMS then if no response, email then if no response phone call, then if no response speak to the Subject Coordinator - Work out each team member’s strengths and weaknesses. Assign tasks based on strengths.
- Agree on a timetable or gant chart of tasks and deadline dates.
- Pick someone to be the team leader/coordinator of your team. They will have responsibility to do their own work and follow up with other team members to make sure they are doing theirs.
- Although unlikely work out how you will break a deadlock before you need to break a deadlock.
- Teams from a variety of cultural, age, gender, socio-economic and educational backgrounds do better than homogenous teams. Mix it up to avoid group think and the same cognitive biases in team members.
- Team Problems? Escalate to the Subject Coordinator EARLY so it doesn’t mean anger, tears and regret later.
- Failure to plan is a plan to fail. Don’t fail. PLAN.
