You are junior member of an information technology consultancy team – Tiger Technology. Your team has been engaged by a medium-sized firm called Century Engineering. Century Engineering has engaged Tiger Technology to develop a database application to record the entry, approval and payment of workers’ timesheet data and some related human resources data. Your team leader has asked you to develop an entity-relationship model to capture the data requirements of this application as well as a subsequent database design/implementation. You are not required to develop an application or any other model – just the ER model and subsequent database design. After interviewing the payroll staff, you have come up with the following data requirements: • Must be able to record details of employees, including name, address, email address, multiple phone numbers, date of birth, commencement date and an externally assigned employee code. • Must able to record supervisor/employee relationships. That is, an employee can supervise zero, one or many other employees. The Director (the top-most employee in the company) does not have a supervisor. An employee can have at most one supervisor at any given time. Not all employees supervise other employees. • Must be able to record details of employees’ position history. That is, an employee might be an apprentice fitter & turner from 1/3/2018 through to 30/9/2019, and then might be a fitter & turner from 1/10/2019 onwards. • Must be able to record details of Departments, including department name, the general enquiries phone number, general enquiries email address, mailing address, and a link to the employee who is the current head of department. • Must be able to record which employee belongs to which department. An employee can belong to at most one department at any given time. An employee may change departments over time, but for the purposes of this assignment you do not need to record those historical changes; so just record which employee belongs to which department. • Must be able to record details of timesheets. Each employee must submit a timesheet for each day that they work, so that they can be paid at the end of the fortnight for their hours. Each timesheet entry must contain: o Start time o Finish time o Total break time o Worked hours total. o Status – which is restricted to open, saved, approved, paid. o Approval timestamp (when it was approved) o Paid timestamp (when it was paid) • Timesheets are approved by each employee’s supervisor. However, if that supervisor is on leave, then another supervisor higher up the tree will do the approval. You do not need to worry about the mechanics of working out who needs to do the approval; you merely need to ensure that it is possible to record which employee approved the timesheet. Therefore, it must be possible to record which employee approved each timesheet. • Timesheets fall into a pay period, and you must be able to record details of pay periods, including: o Start date o Finish date o Status – which is restricted open, in processing, closed. as well capturing which timesheets fall into which pay periods. You are to develop an entity-relationship model to capture the data requirements described above. For any information that is not specified or is unclear, please state a reasonable assumption and model accordingly. (Note that in real life, you would clarify this information with the client/users; you would never simply assume. For the purposes of this assessment only, you will make an assumption). For example, must an employee belong to a department? Do you have to retain a history of supervisor/supervisee relationships? Can a timesheet belong to more than one pay period? Note that in reality, a good technology consultant would strongly recommend the evaluation of commercial off-the-shelf products to fulfil this sort of application need. Payroll/HR is an extremely common requirement for businesses, and as such, there are many software products that can be commercially purchased and installed to fulfil this need. However, such products would need to be evaluated for fit, and the skills required to design and build those products are very similar to the skills required to evaluate such products. Thus, by developing your design skills, you are also developing your evaluation skills. 1 Draw an appropriate ER diagram Use the symbols as prescribed in your unit-textbook to draw the ER diagram for the above case study. 2 List your assumptions and justify the relationship constraints in your ER diagram. Your answers should be relevant to your ER diagram for the given case study only. For example: It is assumed that each employee must be long to a department; therefore Employee to Department has a minimum cardinality of 1.

Place New Order
It's Free, Fast & Safe

"Looking for a Similar Assignment? Order now and Get a Discount!