Database System Design Report - Salary Management System at University

This report details the design and implementation of a database system for managing employee salaries within an organization. The report begins with an overview of the business rules governing the system, including the storage of employee and department details, salary information, and payment records. An EER diagram visually represents the database structure. The report then presents the database tables, including Departments, Employees, Salary, and Payment. Data population examples are provided for each table. Subsequently, the report provides SQL queries designed to retrieve specific information from the database, such as the highest salary, unpaid salaries, employee names and salaries exceeding a threshold, employee counts per department, the sum of unpaid salaries, and the salary of a specific employee. The report concludes with a bibliography of relevant resources.

Running head: DATABASE SYSTEM AND DESIGN
Database system and design
Name of the Student:
Name of the University:
Author Note

1
DATABASE SYSTEM AND DESIGN
Task 1.2
EER Diagram
Assumption
The report is concerned with the salary management system for an organization. The
organization has been assumed here to be an arbitrary organization and the employees and the
departments within the organization are also arbitrary. The database has been created so that
the employees’ details can be recorded in the system and the payments and the salary
information about the employees have also been stored in the system. The system would be
helpful for the management to check if the salary of any employee of the system remains
unpaid and the other related information can also be obtained from the system.
Business Rules
The main business rules of the system which have used for the database
implementation are:

2
DATABASE SYSTEM AND DESIGN
 The system should be able to store the details of the employees who are currently
working for the organization who are to be identified by their unique employee id.
 The details of the departments of the system are also added to the system which
identified by the unique department id.
 The system should also store the details of the salaries of each of the employees of the
organization.
 The system should be able to store the details of the payment made against each of the
salaries of the employees.
Task 2.2
Database tables
Departments
Employees
Salary

3
DATABASE SYSTEM AND DESIGN
Payment
Data Population
Department
Employee

4
DATABASE SYSTEM AND DESIGN
Salary
Payment

5
DATABASE SYSTEM AND DESIGN
SQL Queries
Query 1
Select MAX(P17241341SALARY.AMOUNT)
From P17241341SALARY;
Query 2
Select P17241341EMPLOYEES.EMPLOYEENAME
From P17241341EMPLOYEES
Inner Join P17241341SALARY on P17241341SALARY.EMPLOYEEID =
P17241341EMPLOYEES.EMPLOYEEID

6
DATABASE SYSTEM AND DESIGN
Where P17241341SALARY.STATUS = 'Unpaid';
Query 3
Select P17241341EMPLOYEES.EMPLOYEENAME, P17241341SALARY.AMOUNT
From P17241341EMPLOYEES
Inner Join P17241341SALARY on P17241341SALARY.EMPLOYEEID =
P17241341EMPLOYEES.EMPLOYEEID
Where P17241341SALARY.AMOUNT > 5000;
Query 4
Select P17241341DEPARTMENT.DEPARTMENTNAME,
COUNT(P17241341EMPLOYEES.EMPLOYEEID)
From P17241341EMPLOYEES
Inner Join P17241341DEPARTMENT on P17241341DEPARTMENT.DEPARTMENTID =
P17241341EMPLOYEES.DEPARTMENTID

7
DATABASE SYSTEM AND DESIGN
Group by P17241341DEPARTMENT.DEPARTMENTNAME;
Query 5
Select SUM(P17241341SALARY.AMOUNT)
From P17241341SALARY
Where P17241341SALARY.STATUS = 'Unpaid';
Query 6
Select P17241341SALARY.AMOUNT
From P17241341SALARY
Inner Join P17241341EMPLOYEES On P17241341EMPLOYEES.EMPLOYEEID =
P17241341SALARY.EMPLOYEEID
Where P17241341EMPLOYEES.EMPLOYEENAME = 'Kevin Smith';

8
DATABASE SYSTEM AND DESIGN
Bibliography
Coronel, C. and Morris, S., 2016. Database systems: design, implementation, & management.
Cengage Learning.
Lee, S., Tewolde, G. and Kwon, J., 2014, March. Design and implementation of vehicle
tracking system using GPS/GSM/GPRS technology and smartphone application. In Internet
of Things (WF-IoT), 2014 IEEE World Forum on (pp. 353-358). IEEE.