IMAT5103: Database System and Design Report - Salary Management

This report presents a comprehensive database design for a salary management system within an organization. It begins with a scenario analysis, identifying data requirements and business rules to be modeled. The report then details the conceptual database design, followed by the logical database design, including table structures for branches, employees, salaries, and payments. The implementation uses Oracle DBMS, with SQL code provided for table creation, index creation, and data population. The report also includes a series of SQL queries demonstrating data retrieval and manipulation, such as selecting payment details by mode, retrieving employee names based on payment status, and retrieving salary information. The report concludes with a bibliography of relevant sources.

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 report has been used here for the discussions on the salary management system
for an arbitrary organization. The organization would be benefitted by the implementation of
the system as this would provide them with the option to fetch the salary and payment
information form the system very easily. In addition to this, the system would also help the
management in storing the details of the departments and the names of the employees of the
organizations. The salary status for each of the employees in the organization would be
efficiently stored and displayed with the use of the database for the system.

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
The main business rules on which the development of the database is based on are
described below:
 The system should be able to store the details of all the branches within the
organization.

3
DATABASE SYSTEM AND DESIGN
 The system should be able to store the details of all the employees within the
organization and each of the employee would belong to a certain branch and they
would be identified by a unique employee id.
 The system should be able to store the details of salary for each month for each of the
employee.
 The details of the payment of the salary for each of the employees within the
organization is stored in the system.
Stage 2: Logical Database Design and Oracle SQL Implementation/querying
Task 2.1: Provide a Logical Database Design for your scenario
Branch (BranchID (pk), BranchName)
Employee (EmployeeID (pk), EmployeeName, EmployeeAddress, EmployeeContact,
BranchID (fk))
Salary (SalaryID (pk), EmployeeID (fk), Amount, Month)
Payment (PaymentID (pk), SalaryID (fk), Mode)
Task 2.2 Create the tables using Oracle DBMS
Database tables
Branch

4
DATABASE SYSTEM AND DESIGN
Employees
Payment

5
DATABASE SYSTEM AND DESIGN
Sala
Task 2.3: Create the four most useful indexes on your tables
constraint "P2501165BRANCH_PK" primary key ("BRANCHID")
constraint "P2501165EMPLOYEES_PK" primary key ("EMPLOYEEID")
constraint "P2501165SALARY_PK" primary key ("SALARYID")
constraint "P2501165PAYMENT_PK" primary key ("PAYMENTID")
Task 2.4: Data Population
Branch

6
DATABASE SYSTEM AND DESIGN
Employees

7
DATABASE SYSTEM AND DESIGN
Salary
Payment

8
DATABASE SYSTEM AND DESIGN
Task 2.5: SQL Query writing
Query 1
Select * From P2501165PAYMENT Where PMODE = 'Cash';
Query 2
Select P2501165EMPLOYEES.EMPLOYEENAME
From P2501165EMPLOYEES
Inner Join P2501165SALARY On P2501165SALARY.EMPLOYEEID =
P2501165EMPLOYEES.EMPLOYEEID
Inner Join P2501165PAYMENT On P2501165PAYMENT.SALARYID =
P2501165SALARY.SALARYID
Where P2501165PAYMENT.STATUS = 'Unpaid';
Query 3
Select P2501165EMPLOYEES.EMPLOYEENAME

9
DATABASE SYSTEM AND DESIGN
From P2501165EMPLOYEES
Inner Join P2501165SALARY On P2501165SALARY.EMPLOYEEID =
P2501165EMPLOYEES.EMPLOYEEID
Inner Join P2501165PAYMENT On P2501165PAYMENT.SALARYID =
P2501165SALARY.SALARYID
Where P2501165PAYMENT.PMODE = 'Online Transaction';
Query 4
Select P2501165EMPLOYEES.*, P2501165SALARY.*
From P2501165EMPLOYEES
Inner Join P2501165SALARY On P2501165SALARY.EMPLOYEEID =
P2501165EMPLOYEES.EMPLOYEEID
Order By P2501165SALARY.AMOUNT DESC;

10
DATABASE SYSTEM AND DESIGN
Query 5
Select MAX(P2501165SALARY.AMOUNT)
From P2501165SALARY;
Query 6
Select P2501165EMPLOYEES.EMPLOYEENAME, P2501165SALARY.*,
P2501165PAYMENT.*
From P2501165EMPLOYEES
Inner Join P2501165SALARY On P2501165SALARY.EMPLOYEEID =
P2501165EMPLOYEES.EMPLOYEEID
Inner Join P2501165PAYMENT On P2501165PAYMENT.SALARYID =
P2501165SALARY.SALARYID;

11
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.
Kervick, A., Summerville, S., Dix, D., Walters, T., Sweeney, R. and Sarma, K., 2016. 1015
Design and evaluation of novel irish drowning data taxonomies for a fatalities database.
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.