代写ITGD2023\ V0Hea13 DATABASE DESIGN AND MODELLING代写SQL语言

Individual Assignment

ASSIGNMENT REQUIREMENT

MODULE       : DATABASE DESIGN AND MODELLING

COURSE : DICT/DNDFC

CODE : ITGD2023\ V0Hea13

OVERVIEW

In this assignment student will demonstrate their understanding in relational database design and modeling by completing a project assigned. Students will also demonstrate their skills in implementing the design database and perform. data entries, date retrieval and data manipulation using with Structured Query Language (SQL).

ASSIGNMENT REQUIREMENT

Project: Database Design for Human Resource - Project Management

You’re appointed as  Database administrator to Harvest consulting. Your first assignment  is to advise your client on how to manage human resources for projects. Your ideas to include how to analyze the logical database design , physical data model, establish relationship, create tables, and manipulate records using Data definition and data manipulation language

You and your team are highly advised to conduct some research and to get better understanding on human resource data in relation to project management. You may refer to advised  reference books in  the course and internet  resources.

Below is the Project Management Report, which describes projects being worked upon by employees.

Project Management Report

Project Code: PR001

Project Title: Human Resource System

Project Manager: Alexander Jimskie                                               Project Budget: $200000

EmployeeID               Employee Name              Department No               Department              Hourly Rate

E0001                         Clara Miller                       1                                      IT                             $100

E0002                         Sarah Lim                         2                                      Finance                      $50

E0003                         Dorothy Jabines                1                                       IT                             $75

E0004                         Sham Moh                        3                                       HR                            $60

E0005                         Philp Kohler                      3                                        HR                            $50

E0006                         Nancy Vale                       2                                        Finance                      $150

E0007                         Christy John                      1                                       IT                              $120

E0008                         Mary Kone                        1                                        IT                              $200

Task 1 - Convert the Report from un-normal form into third normal form. Provide appropriate

name for each Entities and attributes. Find the primary, Foreign and composite keys. 12 Marks

Note:

.   Find Un-Normalized form data

.   Convert un-normalized set to 1NF

.   1NF to 2NF

.   2NF to 3NF

.   In between the process of normalization find out Primary, foreign and Composite keys.

.   Discuss the steps of normalization while carrying each NFs.

.   Use appropriate  tool to do the task

Task 2 25 Marks

1)   Key in appropriate records for all the tables. (Sample, take from the project report given and add further meaningful records in the derived tables. Note: Minimum 20 records in Employee table and other tables any number  of records)

2)   Display Employee Name belongs to department either IT or Finance

3)  Count No of  Employees whose Hourly rate above 50

4)   Find employee names starts with ‘S’

5) Display Department Name and Sum of Hourly Rate for each Department.

6)   Display department name with Number of employees less than 5 Numbers

7)   Display Employee name and Department name,  who has maximum and minimum salary

8)   Display No of employees , Department name in each department.

9)  Create a procedure to display Employee Name and Department Name whose Hourly rate is  above 100.

10) Count number of employees deployed for each Project

Note: Use SQL software, Do Joins in query wherever applicable. All the tables with data and SQL query along with its output for each task should be provided in word file.

Submission procedure:

. Submit your work to black board>Assignment  submission  link on or before the due date.

. Submit your final work in the word document by following the file name as Student name-Assginment.docx.

. Submission to include the  following in the same order:

1)    Cover page included  with Module Name, Module  code, Student Name, signature , Lecturer Name and Due Date

2)    Assignment Question  all pages

3)    Assignment  Answers , clearly indicating  Task Number and answer.

Note:

Late submission not  acceptable without valid reason.  Everyday late will reduce 5% of total  marks for each day.

Plagiarism check will be done by lecturer. If found that the work is copied work from other assignments and internet resources, will be given 0 marks

Marking guide

Task 1

3 marks for finding data set using Un-Normalized form(3*1=3 Marks)

3 marks for each normalization(1NF,2NF,3NF including Primary and composite keys, 3 * 3=9 Marks)

Task 2