Assessment details for ALL students
Assessment item 2
Due date: Friday of Week 10 (11:45 pm AEST)
Weighting: 30%
Length: N/A
Objectives
• To develop a conceptual data model diagram
• To perform logical design and physical design
• To implement a database
Introduction
In this assessment, you are required to perform the following tasks for the case study given below:
• Design and draw an Entity Relationship diagram
• Perform logical and physical design
• Create a Database in MS-Access
Case Study
Century Engineering – Timesheet system
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.
3 Create Logical Design
Map your Entity Relationship model into relations and make sure that all the relations are in BCNF. Provide all the relations in the following format:
Student (StudentID, StudentName, Street, Suburb, State, PostCode, Email, ContactNumber)
Unit (UnitID, UnitName)
UnitStudent (UnitID, StudentID, grade)
foreign key (UnitID) references Unit.UnitID
foreign key (StudentId) references Student.StudentID
4 Provide Physical Design
Complete a physical design for the timesheet table. For each column in the timesheet table, specify the name of the column, type/length, whether or not it is an identifier, whether or not it should be required and any suitable default value. An example is given below.
Column name Type/length Identifier Required Default value
TimesheetID Autonumber Yes Yes n/a
etc
Refer to page 304ff of the textbook (9th edn).
5 Implement Database
Create a Microsoft Access database. Implement all tables and relationships as per your design in the previous steps. Create all columns in each table and choose appropriate data types. Ensure that the physical design you completed in the previous step is also implemented in your database. All the relationships should have appropriate referential integrity and cascade options applied. You do not need to create any form, query or report. You do not need to insert any records in the tables.
How and what to submit:
You must make an electronic submission for this assessment using the appropriate assessment submission link in the unit website. Your submission must be a single ZIP file which contains your assessment work in the following two files:
A Microsoft Word document containing
1. Entity Relationship Diagram
2. Assumptions and Business Rules
3. BCNF relations from your logical design
4. Your physical design as per part 4.
A Microsoft Access database containing
1. All tables and their relationships
2. Implementation of integrity constraints as per part 4.
--------------------------------------------------------------------------------------------------------------
Marking Criteria:
Item Maximum Marks
Entities have been identified and drawn correctly. 5
Provided appropriate attributes including identifying attribute(s) for each entity. 5
Drawn correct relationships; used proper symbols and shown min and max cardinalities. 5
Provided relevant assumptions 2
Correct BCNF Relations including foreign key references. 5
Column Specifications for the selected Table under section 4 (Provide Physical Design). 2
Database with Tables (2 marks) and Relationships (2 marks). Correct data type and properties (2 marks) for all columns/attributes of the specified table under section 4 (Provide Physical Design). 6
Penalty due to plagiarism/copying
Late Penalty: Less 5% of available marks per calendar day.
Total 30