Reference no: EM132849189 , Length: word count:2000

4104COMP Data Modelling - Liverpool John Moores University

Data Modelling Scenario

Learning Outcome 1: Construct a spreadsheet-based application from a given set of requirements.
Learning Outcome 2: Create a logical and physical entity relationship model from a given set of requirements.
Learning Outcome 3: Construct a database-based application from a logical and physical entity relationship model.

Details of the task

The management of "The Printers", a large local printing company, need to report on various aspects of their system, using both spreadsheets and databases. Your task involves implementing a spreadsheet application, modelling their internal recruitment records system and implementing that as a database. You are then required to produce a professional report that details these activities.

Part 1

Firstly, you are asked to produce a spreadsheet that models the company's sales for it's leaflet, poster and flyer production division over a period of 5 years. The company is well established and provides leaflet, poster and flyer printing in multiples of 1000 sheets for customers in all paper sizes from A0 to A7, and in paper thicknesses (also known as ‘weights') that range from the maximum of 250g/m2 to the minimum of 70g/m2. The division prints in one colour (usually black) and they refer to this as ‘Monochrome', however, a full colour printing service is available to its customers as a paid for option.

All orders have a ‘Base Price'. The ‘Base Price' is determined by charging the customer; for the number of sheets printed, for the size and thickness (weight) of the paper selected and then applying the charge for ‘Monochrome' print.

If the customer requires ‘Monochrome' print, then the calculation of the ‘Base Price' is equal to the ‘Total Sales (ex.VAT)' value. If the customer requires ‘Colour' print, the ‘Monochrome' calculation (for the ‘Base Price') is still done, but then an additional value is added to that to cover the charge for the selected ‘Colour' print option. As such, the ‘Monochrome' price + the ‘Colour' print charge = Total Sales (ex. VAT), when ‘Colour' print is specified by the customer. The division offers an option to its customers to have their print work folded in one of two types (single folded or double folded) with each option increasing the price of the customer's print order (hence the ‘Total Sales (ex. VAT)) accordingly.

The company does not accept orders for print of any type where the ordered quantity is below 1000 and it also does not accept orders that are not in whole multiples of 1000. The division operates a printing machine and this is an asset that is depreciated. This depreciation is recorded in their system as an expense and is deducted from their monthly sales alongside the other additional monthly expenses to compute monthly Net Profit. The value of the depreciation expense is fixed for each month in any given year, but the fixed amount charged per month changes year by year. The sales of various volumes of paper of particular sizes and thicknesses (weights) are provided for each trading month from January 2016 to December 2020, together with an indication of whether print was in ‘Monochrome' or in ‘Colour'. The sales volumes sold per month also indicate what type of folding had been applied. The monthly expenses have been provided alongside the corresponding depreciation value for that month also.

Using the Coursework1_part1.xlsx document, complete this spreadsheet so that sales and net profit/loss over the course of 5 years can be tracked and reported. The spreadsheet will need to include the following functionality:

1. Based on the requirements, and using appropriate Excel functionality to locate specific values within specific cells, use suitable formulas to reference the content of the ‘Pricing Matrix' to obtain the ‘Price per 1000 sheets' for each of the ‘Paper Size' and ‘Paper Weight' values in each row of the spreadsheet (row 13 to row 492)

2. In column H, determine the ‘Base Price' for the customer order that is based upon the quantity that they have ordered (per Column E) and the value you have obtained from (1) above.
a. If Column F contains ‘Colour' calculate ‘Charge for Colour Output (£)' for each Row and display that value in Column I.
b. If Column G contains either ‘Double' or ‘Single' calculate the corresponding ‘Charge for Folding (£) value for each Row and display that in Column J.
c. Calculate the ‘Total Sales (ex. VAT) (£)' value for each Row and display that in Column K.
d. Calculate the ‘Total Expenses (£)' value for each Row and display that in Column N.
e. Calculate the ‘Net Profit/Loss (£) value for each Row and display that in Column O (Net Profit/Loss is the Total Sales Value (ex.VAT) (£) after deducting monthly expenditures)
f. Cumulative profit/loss: defined as the amount you are left with after combining the previous month's cumulative profit/loss with the current month's sales (monthly income) and then deducting your monthly expenditures.

3. Starting from when trading began, using appropriate formulas/functions, calculate the following statistics and display the results in columns J, L and M (rows 2,3,4 and 5) respectively:
a. Sales Grand Total (2016-2020) (£)
b. Maximum Sale (2016-2020) (£)
c. Minimum Sale (2016-2020) (£)
d. Average Sale (2016-2020) (£)
e. Expenses Grand Total (2016-2020) (£)
f. Maximum Expense (2016-2020) (£)
g. Minimum Expense (2016-2020) (£)
h. Average Expense (2016-2020) (£)
i. Net Profit / Loss Grand Total (2016-2020) (£)
j. Maximum Net Profit (2016-2020) (£)
k. Maximum Loss (2016-2020) (£)
l. Average Net Profit / Loss (2016-2020) (£)

4. Apply Conditional Formatting to the Net Profit/Net Loss (£) column columns to satisfy the following condition:
a. Instances that depict a loss should be red.

5. The entire spreadsheet should also be suitably formatted to depict the correct units. All numeric values must be rounded to zero decimal places and any monetary values must be formatted using the ‘Accounting' numerical formatting (with zero decimal places).

6. BONUS MARKS: Using Pivot Charts / Tables, determine the quantity sold per year (2016 - 2020) of each paper size (A0 to A7) and produce a Stacked Bar Chart detailing your findings. The graph must be correctly formatted with a Title, Axis labels and a suitable legend. The pivot table that accompanies your chart must be conditionally formatted in a suitably informative manner and both the pivot table and the pivot chart must be presented just below the horizontal (purple) line per row 496 of your coursework submission.

Part 2

Secondly, the managers at national head office need to plan their internal staff recruitment by recording aspects related to when current staff members apply for different jobs within the same company. You can assume that:
• Applicants for Jobs can apply for zero, one or more Jobs.
• If an Applicant's application proves successful, they will be invited to attend an Interview.
• Each Applicant currently works in one department only and, if they are successful at interview and get the job, they would only work in one department only.
• Each interview that is attended by an Applicant is also attended by one member of Management.
• If any other staff attend an interview with the Manager, the group of staff attending represents an Interview Panel.
• Each Interview Panel must have one Manager.
• A given advertised Job will receive many applications.
• A given job may require an Applicant to attend more than one interview.
•

Your task includes:

1. Using the information and raw sample data in Appendix A, and the un-normalised relational schema (ONF) in Appendix B, derive a set of relational schemas that are (where appropriate) in 3NF. State any assumptions/justifications for the design decisions that you make.

2. Using all of your normalised data from 2.1), produce a Conceptual Entity Relationship Diagram
(ERD), using Crow's Foot Notation, which illustrates appropriate entities and relationships.

3. Using your conceptual ERD from 2.2), produce a Logical ERD, using Crow's Foot Notation, which illustrates appropriate entities, attributes, relationships and primary/foreign keys.

4. Using your logical ERD from 2.3, transform this diagram into a Physical ERD, using Crow's Foot Notation, which specifies appropriate table names, column names, relationships, primary/foreign keys, data types, length and nullability.

5. Using your physical ERD from 2.4 as the basis for your implementation, implement the database for the Personnel system using Microsoft Access. The database should be populated with the raw sample data from Appendix A.

Attachment:- Data Modelling.rar