Database Modelling
In this assignment, you are asked to design a database to support the recruitment of casual staff for a school. The major business requirements are summarised below in the Mini Case: Instant Recruitment. You are asked to develop a detailed Entity-Relationship model for this mini case. Your ER model should consist of a detailed ER diagram integrated with itemised discussions on the features of the entities and relationships and all the assumptions you made. The ER diagram and the accompanying document should identify keys, constraints, entity types, relationship types, specialisation/generalisation, etc. The ER diagram should in general have more than 5 entity or relationship types and no more than 15. This means that students will have to selectively identify those most important entity types and relationship types for their work and for the fulfillment of the key business functionalities. More concretely, your deliverables should include
1. Executive Summary on the enterprise need in succinct terms, and on what has actually been done in your assignment and to what extent. It must be concise and right to the point. (1 mark)
2. A list of supplementary business rules or assumptions that underpin your particular database design. When pertinent, link them to the part of your database design with short comments. (1 mark)
6. A list of 5 most important (and mutually dissimilar as much as possible) queries in English (not SQL) the Instant Recruitment as a business will typically make through this database system, and an indication on which of these queries are feasible (i.e. achievable) under your current design. (1 mark)
TIPS: It is often a good idea to start your design by initially igoring the less “critical” business components so that the design problem becomes much simpler and manageable, and then have your initial design gradually extended or refined to cater for more and more requirements. For the mini case in this assignment, you may for instance initially concentrate your design around the Casual staff and the Job available, temporarily ignoring things such as the type of jobs, staff availability, different hourly rates for the repeated deliveries etc. Of course, you are welcome to choose whichever way that would suit you best.
Mini Case: Instant Recruitment
Instant Recruitment is a system to be designed to recruit casual staff under a very short notice, as well as on a more regular basis. For simplicity, this system will be limited to serving a large school at a university where casual staff need to be recruited for lecturing, tutoring, and marking etc.
Casual staff: Within the school’s recruitment database, each candidate for casual staff must provide their contact details in terms of home address, email address and mobile phone number, and must provide an estimated travel time to reach the school from home. These will be utilized or taken into consideration when the Instant Recruitment system suggests who to send first the request to stand in for a regular staff who can’t make it due to for instance a sudden sickness. Each candidate has to specify a list of subjects they can teach, their relevant years of experience there, and their self-rated competitiveness or preference with the subject in the range of 0 to 10. Each candidate also needs to enlist their qualifications and the positions they are interested for the casual work.
Positions: There can be different type of positions made available to the casuals, according to whether a position is for lecturing, tutoring or marking etc. The casuals are paid on hourly basis according to their positions, and the hourly rate for a repeated teaching activity may be slightly lower than that for the first delivery within the same semester.
Staff Request: When a casual staff is being requested or sought, it will be associated with a particular position for the payment rate, and will specify certain expertise or subjects the casual staff should be familiar with.
Recruitment: Each casual staff is recruited to a specific academic position for a period of time to conduct the teaching activity at a given venue, and will be assigned to a relevant academic supervisor for the academic liaison. For example, a casual staff may be urgently recruited to conduct a 2 hour lecture at a certain time on the day under a very short notice (hence an express recruitment), or may be employed to do 4 hours at class tutoring each week during the semester (hence a regular recruitment). For a regular recruitment, a casual staff will be assigned a specific class venue and the number of hours to work there starting from an allocated beginning time. The recruitment will also specify the number of weeks the appointed casual is to conduct the same activity at the same weekly time and venue. For an express recruitment, the system will first search the available casual staff and rank them according to their past response time and their estimated travel time to reach the school. A school’s administrative staff will usually select one of the top recommendations to contact via phone or SMS or email. A staff request (i.e. a job offer) sent via an email or SMS to a casual staff will expire after a pre-selected amount of response time associated with this particular job, so that the admin can select the next candidate to contact for the casual position. For any express recruitment, all the relevant communications between the school and the casual staff will be recorded and will be later analysed to calculate his or her average response time. In other words, the recruitment system needs to be able to track when a casual job is sent to whom, whether it is accepted or rejected, and at what time etc.
Availability: Each casual staff may be available only on certain days of the week, and may also be unavailable for certain specific periods of days.
