代做DDI '24 Coursework代做留学生SQL 程序

DDI '24 Coursework

Contribution to module: 100%

Word limit: 3,000

Submission deadline: 3pm, 1st May.

This work should be completed in a group of 3 or 4 people. Organise yourselves into groups. Students not in a group of appropriate size may be awarded zero for the coursework. Students who report that they have been unable to join a group of appropriate size, or who leave a group and are unable to join another may receive zero for this coursework. Each member of a group will receive the same mark for the group project unless representations are received in writing from all group members requesting an unequal mark allocation. Whilst any case for an unequal allocation of marks may be submitted with a group project, no case for an unequal allocation of marks from any group member can be considered once a project has been marked. Group project marks are final once they have been signed off by the External Examiner. Notwithstanding any of the above, unforeseen situations may arise with project groups that the module convenor will deal with as circumstances dictate and they in a manner which they deem most appropriate. Eventually student services will ask for details of group membership. After this, it will not be possible to change group. I will answer any general question you have about the coursework but I will not look at any any draft solutions or give specific advice about your work. Feel free to ask any questions at the end of the lecture and feel free to post both general questions and answers to the moodle discussion forum.

Plastic Solutions

Plastic Solutions human resources department need a database to manage information about their employees, the departments in which they work, the roles they do and have done since they joined the company, their scores and other notes from their annual evaluation  since they joined, their physical location in the company (Plastic Solutions occupy a number of floors in a number of buildings), and any training courses they have attended since they joined (which could be anything from a first aid course to a course on how to write a business proposal).

1. Data model

Create a data model for Plastic Solutions. This is not straightforward and you will probably work through several design iterations. Do not include early designs in your documentation. Use the approach and notation we learned in class. Fully document your design process in a manner you would use if you were a professional database administrator – you will lose marks for the inclusion of extraneous information and unclear designs.

Note that you have not been given any attribute lists, nor have you been given a complete list of entities. You should therefore feel free to include anything you think will be useful, but check what data you will need to fulfil the queries that you are being asked to create in Part 3.

2. Implementation

2a. Write SQL statements to implement your design and print these out.

2b. Implement your design using a suitable technology. Input enough data into your tables (however much you decide) so that you can fully test your design and the queries you will write for Part 3. You do not need to submit anything for Part 2b.

3. Queries

Create a short report to answer the following questions that Plastic Solutions managers have. For each, include your SQL statements along with some sample output suitably formatted and if necessary a brief & clear explanation of how your query works in bullet point format.

a. What is our typical retirement age?

b. What is our youngest age for promotion?

c. Do we have at least one first aider for each of our locations?

d. Whose evaluations are on the decline?

e. Who should be considered for promotion in 2018?

f. Is the training budget being shared fairly among the departments?

g. Is there any evidence of sexism in our organisation that we should investigate further?

h. Does our training course on writing a CV help people get promoted?

i. Do our staff development courses work?

If you want to include any charts or graphs in your reports, feel free to export your data into Excel and use that to create them, or you could produce them in R.

4. Who's going to leave us?

Over the past few years the HR department has been keeping an anonymised  spreadsheet on employees which includes whether they have left the company. The data contains the following for each employee:

.   Satisfaction level as a number between 0 and 1 where 1 is perfectly satisfied

.   Last evaluation as a number between 0 and 1 where 1 is a perfect evaluation

.   The number of projects they have worked on

.  Average hours working each month

.   Time spent at the company in years

.  Whether they have had an accident at work

.  Whether they have had a promotion in the last 5 years

.   Their salary (1 is low pay, 2 is medium pay, 3 is high pay)

.  Whether the employee has left

The data is stored as a comma-separated values file called HRDataforcoursework.csv. Data about current employees is stored in WillTheyLeave.csv. For each current employee, use KNN to predict whether they will stay or leave.

Coursework Boilerplate

Your coursework needs to be submitted electronically to Moodle. Submit a single pdf file. See the Student Services website for further details of this process. The deadline for coursework submission is 3pm on Wed 1st May 2024. Late submission will attract a mark penalty unless an extension has been approved by Student Services. Please familiarise yourself with the extenuating circumstances policy and the process for submitting a claim.

A maximum word count will be set by the module convenor and must be adhered to. The penalty for exceeding this limit is a five mark deduction.

The actual word count of the assignment must be  stated by the student on the first page (cover sheet) of the assignment.

The overall word count does include citations and quotations.

The  overall word count does not include the references or bibliography at the end of the coursework.

The word count does not include figures and tables.

Students should prepare and submit their coursework assessments in the following format:

Font: Verdana 11 point

Spacing: 1.5 spaced

Margins: Normal (2.5cm)

Referencing: Harvard citation style.