Databases

    Metropolitan Geniuses (MG) is an SME sized company which specialises in providing skilled contractors for organisations wishing to recruit IS/IT type personnel for various projects/contracts in the financial service sector.Because of an increase in demand for suitably skilled workers in the City of London, MG has decided to create a suitable database as part of their overall Information System (IS) architecture to monitor the placement of their contractors with their client base. Before continuing with the assignment you should consider the following proposed field/report requirements that have been extracted by an analyst working within MG:Analyst ReportAccording to the analyst, MG has identified eight named “Contractor Types” along with an extra type labelled “Other” to cover the skill set of the contractors supplied to their client base: Database SpecialistsProgrammersWebsite SpecialistsNetwork EngineersData Security OfficersDesignersAnalysts System TestersOtherThe analyst has also identified the following two table typesClient Table - FieldsContract ID, Project Name, Project Description, Company Name, Company Address, Contact Name, Telephone Number, Mobile Number, Email AddressPage | 3Contractor Table - FieldsContract ID, Contractor Name, Contractor Type, (Calculated Fields: Hours Worked, Rate per Hours, Gross Pay)     1) A relevant database table outlining the details of the company ‘Clients’. The table should contain at least 15 relevant fields. Include the ‘Client’ fields outlined in the analyst report as part of these 15 fields. The design schema should contain relevant ‘Meta’ data for each individual field.10 marks2) A relevant database table containing the details of the various ‘Contractors’ that are available to hire. The table should contain at least 10 relevant fields. Include the ‘Contractor’ fields outlined in the analyst report on as part of these 10 fields. The design schema should contain relevant ‘Meta’ data for each individual field.10 marksHaving constructed these two tables you will:3) Link these two tables via the database tools option on the Access database menu in order to enforce referential integrity. 5 marks4) Produce an appropriate database form for the ‘Clients’ table. This form will include the ‘Contractor’ table as a sub table. The form should include a query calculating the total wage bill incurred by individual clients. Your form should include a refresh button. You will then add 10 clients to your client table. You should include at least one contractor for every client. 5 marks5) Produce an appropriate ‘Report’ form which facilitates the following report/query list: i) Client contact details 1 markii) The average contractor fee for each project 1 markPage | 4iii) The maximum fee earned by a contractor on a project 1 markiv) The minimum fee earned by a contractor on a project 1 mark v) The total fees earned by each contractor on a project 1 markWith Excel you will:6) Export the ‘Contractor table’ to Excel in order to create a new spread sheet to analyse the wage costs. You should then produce an associated pivot table and associated pivot chart to display/manipulate the data. Include some screenshots inside the spread sheet demonstrating how you used the ‘Slicer’ tool with the pivot table.10 marks7) Produce an appropriate “Look up table” inside this spreadsheet to evaluate the relative value of the ‘Contractor’ costs. 5 marks8) Produce a suitable “Goal Seek” example in the spreadsheet demonstrating a projected budget cost situation.5 marks 9) Produce an appropriate “Scenario spread sheet” and “Scenario report” using the Scenario Manager for the proposed situation costs outlined in Figure 1. 15 marksPage | 5Contractor types Contracted Hourly Rate Number proposed for project CostSystem Testers £55 10 £550Database Specialists £65 8 £520Programmers £60 5 £300Total Costs £1,370Labour Budget £2,500Balance £1,130Scenarios Database Specialists Programmers System TestersSafe Numbers 8 5 10Usual Numbers 7 4 8Efficient Numbers 6 3 6Figure 1And finally:You are required to write a word report explaining to the management at MG as to the various methods that you employed in order to make the ‘Database’ and the associated ‘Spreadsheet(s)’ satisfy organisational needs. Your report should be at least a thousand words long and should focus on the critical theory underlying the development of successful Information Systems.You are also required to ensure that the report is written whereby the business nature of the setting is accommodated.