根据提供的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.