Example Information Systems Essay

Fraud Order - 341178

This essay/coursework/dissertation was stolen from UK Essays, call 0115 966 7955 to speak to a Fraud Officer now for more details. We have made it available for use as a study resource.

Employee HR database

Unnormalised:

Doc 1: Cinema and showings are together (incl what is showing) with manager name and how much made in each showing and screen shown on
Doc 2: list of employees with their qualifications, job types and institutions and whether superviser or superivisee.

1st Normal Form:

Cinema table: Cinema with manager id. Employees table: With supervisor/supervisee. Qualifications table: As could have multiple qualifications per employee. Institution and Qualification / Type can remain on this table. Cinema table has manager ID in it. Although a manager can manage only one cinema, it could be that one cinema has multiple managers on shift, so this is moved to another table called Managers. Showings table: This can include the auditorium, cinema, and the film shown.

2nd Normal Form:

Showings table: Cinema for showing is key, incl .what is showing, screen, and how much is taken. These are not functionally dependent on Showings so also need an auditorium/screen and Film table. Employees table is keyed on Cinema too. Whether Supervisor or Supervisee is functionally dependent on who the employee is, so these can stay in the table. Qualifications table is keyed on Cinema and Employee ID with the qualifications obtained and where obtained being functionally dependent on this so these can remain in this table.

3rd Normal Form:

Although an employee can be a supervisor or supervisee, the details are mixed together, such that you could mark an employee as no longer a supervisee when they are the only supervisee of a supervisor, and leave the supervisor as being marked as a supervisor when they are no longer supervising anyone. To maintain integrity, the Supervisor / Supervisee relationship is moved to another database Supervisor

An employee job type description is transitively dependent on the job type code – the descriptive name could change (e.g. from Counter Staff to Popcorn Facilitator) and would have to be updated in all employee records if it changed, so is moved to another table Job Types.

Similar reasoning for breaking out Qualifications (description field transitively dependent), Institution (what if name changes?), and Qualification Types (e.g. might be several qualifications that give you security badge).

It is possible for an employee to work at different cinemas, so a Cinemas Employees table is necessary to avoid problem of having the same employee appearing for different cinemas, potentially with different qualification info etc.

Checks:

A) Yes B) Yes C) Yes D) Yes E) Yes F) Yes G) Yes - All attributes and relationships required for queries are available in model.

Using Enhanced Entity-Relationship Diagram

Use of this would have facilitated changes in the following areas:

1. Subclassing Employees into Managers and Supervisors as specialisations of employees
2. Generalization for Employee Qualification (aggregate of Employee, Institution, Qualification and Qualification Type)
3. Generalization for Showing (aggregate of Auditorium, Film, and takings / timing info)
4. A Manager cannot be a Supervisor, so Managers and Supervisors are disjoint constraint on Employee class.
5. However, a Qualification could also be subclassed into those that give a security pass and those that give a health & safety cert. It could be that a course covers both of these topics, in which case that course would require a non-disjoint constraint.
6. An employee is a partial specialization as it does not define either manager or supervisor status – but it is valid to have an employee that is neither subclasses, and does not include the subclass attributes.