IMAT5103: Database Systems and Design - Salary Management System

This project focuses on the design and implementation of a database system for salary management. It includes conceptual database design, logical database design, and Oracle SQL implementation. The project covers the selection of a case study, definition of business rules, creation of database tables (Department, Employee, Salary, Payment), index creation, data population, and SQL query writing. The SQL queries demonstrate how to retrieve and manipulate data related to employees, salaries, and payments. This document provides a comprehensive example for students studying database systems and design, and Desklib offers similar resources for further learning.

Running head: DATABASE
Database
Name of the Student:
Name of the University:
Author Note

1
DATABASE SYSTEM AND DESIGN
Stage 1: Scenario and Conceptual Database Design Task
1.1: Selection of the case upon which the database design and implementation is to be
based
The aim of the report is to create a database development system based on a chosen
company. The salary management system will be prepared will help of a database. The
system developed includes development of the system that will be beneficial for the
organization maintaining the management system. This system will be helpful in managing
the salaries of all the employees present within the organization. Moreover data related to
each employees are needed to be stored properly. The dataset related to each departments
within the organization needs to be included within the database system. This will help in
organizing the details properly. Apart from this the database system will store the information
relate to salary and payment of each employee within the organization. This will help the
organization to manage and have a proper control over the employee’s information and data
sets.

2
DATABASE SYSTEM AND DESIGN
Task 1.2: Provide a conceptual database design for your scenario & the list of enterprise
rules being modelled
Business Rules
There are certain rules for business that are needed to be followed while managing the salary
system. The rules important for salary management system are implemented accordingly and
described below:
 The system should have the ability to store details of each department with the help of
unique DeaprtmentID.
 The details regarding to the employees are to be stored in the system with the help of
unique EmployeeID.

3
DATABASE SYSTEM AND DESIGN
 The system need to store details regarding the salary of each employee from different
departments working within the organization. This will help to sort the salary as per
the position of the employees.
 Apart from this, the system needs to have a proper record regarding the payment
details of each employees working within the organization. This will contain salary
details also.
Stage 2: Logical Database Design and Oracle SQL Implementation/querying
Task 2.1: Provide a Logical Database Design for your scenario
Department (DepartmentID (pk), DepartementName)
Employee (EmployeeID (pk), EmployeeName)
Salary (SalaryID (pk), EmployeeID (fk), Amount)
Payment (PaymentID (pk), SalaryID (fk), PMode)
Task 2.2 Create the tables using Oracle DBMS
Database tables
Department
Employee

4
DATABASE SYSTEM AND DESIGN
Salary
Payment

5
DATABASE SYSTEM AND DESIGN
Task 2.3: Create the four most useful indexes on your tables
Department

6
DATABASE SYSTEM AND DESIGN
Employee
Salary
Payment
Task 2.4: Data Population
Department

7
DATABASE SYSTEM AND DESIGN
Employee
Salary

8
DATABASE SYSTEM AND DESIGN
Payment

9
DATABASE SYSTEM AND DESIGN
Task 2.5: SQL Query writing
Query 1
Select * From P2437996PAYMENT Where PMODE = 'Cash';
Query 2
Select P2437996EMPLOYEES.EMPLOYEENAME
From P2437996EMPLOYEES
Inner Join P2437996SALARY On P2437996SALARY.EMPLOYEEID =
P2437996EMPLOYEES.EMPLOYEEID
Inner Join P2437996PAYMENT On P2437996PAYMENT.SALARYID =
P2437996SALARY.SALARYID
Where P2437996PAYMENT.PMODE = 'Online';
Query 3

10
DATABASE SYSTEM AND DESIGN
Select P2437996EMPLOYEES.*, P2437996SALARY.*
From P2437996EMPLOYEES
Inner Join P2437996SALARY On P2437996SALARY.EMPLOYEEID =
P2437996EMPLOYEES.EMPLOYEEID
Order By P2437996SALARY.AMOUNT DESC;
Query 4
Select AVG(P2437996SALARY.AMOUNT)
From P2437996SALARY;
Query 5
Select P2437996EMPLOYEES.EMPLOYEENAME, P2437996SALARY.*,
P2437996PAYMENT.*
From P2437996EMPLOYEES

11
DATABASE SYSTEM AND DESIGN
Inner Join P2437996SALARY On P2437996SALARY.EMPLOYEEID =
P2437996EMPLOYEES.EMPLOYEEID
Inner Join P2437996PAYMENT On P2437996PAYMENT.SALARYID =
P2437996SALARY.SALARYID;
Query 6
Select SUM(P2437996SALARY. AMOUNT)
From P2437996SALARY;

12
DATABASE SYSTEM AND DESIGN
Bibliography
Al-Brashdi, A., Butler, M., Rezazadeh, A. and Snook, C., 2016. Tool support for model-
based database design with Event-B.
Purohit, S.K. and Sharma, A.K., 2015. Database design for data mining driven forecasting
software tool for quality function deployment. International Journal of Information
Engineering and Electronic Business, 7(4), p.39.
Richter, K.S., Mottla, G.L., Kaplan, B., Hayward, B. and Mahony, M.C., 2016. Evaluating
pregnancy and live birth outcomes in a real-world analysis of a US database of 66,051
transfers over 6.5 years. Fertility and Sterility, 106(3), pp.e53-e54.