DICT Database Assignment Feb 2025 PSB : Database Design for Human Resource – Project Management, Singapore

Individual Assignment Assignment Requirement

Overview

In this assignment, students will demonstrate their understanding of relational database design and modeling by completing an assigned project. They will also implement the designed database, perform data entries, retrieval, and manipulation using Structured Query Language (SQL).

Assignment Requirement

Project: Database Design for Human Resource – Project Management

As a Database Administrator at Harvest Consulting, your first task is to advise the client on human resource management for projects. You need to:

• Analyze logical database design
• Create a physical data model
• Establish relationships between entities
• Create tables
• Manipulate records using Data Definition and Data Manipulation Language (DDL & DML)

Research human resource data related to project management using reference books and internet resources.

Project Management Report

Employee Data

Tasks & Marking Scheme

Task 1 – Normalization (12 Marks)

Convert the given un-normalized report into third normal form (3NF). Provide proper names for entities and attributes and identify:

• Primary Keys
• Foreign Keys
• Composite Keys

Steps to Include:

1. Identify Un-Normalized Form (UNF) data
2. Convert UNF → 1NF → 2NF → 3NF
3. Explain each normalization step
4. Use an appropriate tool for database design

Task 2 – SQL Implementation (25 Marks)

1. Insert at least 20 records in the Employee table and other tables as required. (3 marks)
2. Display Employee Names working in IT or Finance. (3 marks)
3. Count the number of employees with an Hourly Rate above $50. (2 marks)
4. Find employees whose names start with “S”. (2 marks)
5. Display Department Name and Total Hourly Rate per department. (2 marks)

Sample Output:

1. Display departments with fewer than 5 employees. (2 marks)
2. Find the Employee Name and Department Name for employees with the highest and lowest salaries. (2 marks)
3. Count total employees per department. (2 marks)
4. Create an SQL procedure to display Employee Name and Department Name for those earning above $100 per hour. (3 marks)
5. Count the number of employees assigned to each project. (3 marks)

Note:

• Use SQL software for query execution.
• Joins should be used where necessary.
• Provide all tables, data, SQL queries, and output in a Word document.

Submission Guidelines

• Submit via Blackboard > Assignment Submission Link before the due date.
• File format: Word document (e.g., StudentName-Assignment.docx).
• Include the following in the submission:
  1. Cover Page – Module Name, Code, Student Name, Lecturer Name, Due Date.
  2. Assignment Questions – All pages included.
  3. Assignment Answers – Clearly labeled Task Numbers with responses.

Late Submission Policy:

• 5% penalty per day for late submissions without a valid reason.
• Plagiarism checks will be conducted. Copied work from other students or the internet will receive zero marks.

Marking Guide