Database System and Design

This document provides a scenario and conceptual database design for a salary management system, including enterprise rules, logical database design, Oracle SQL implementation, and querying.

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 description of the report is based on the development of the database on a salary
management system of an arbitrary company. The system consists of the development of a
system that would be helpful for the organization in maintaining a management system that
would be helpful for them in managing the salaries of the employees of the organization. The
data related to the departments in organization are also to be included in the database of the
system. In addition to this, the data of all the employees are also required to be stored in the
database system for the organization. The salary and the payment for each of the employees
in the organization are to be stored within the database of the organization.

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 business rules for the implementation of the salary management system has been
described below:
 The system should be able to store the details of the departments with their unique
DepartmentID.

3
DATABASE SYSTEM AND DESIGN
 The system should be able to store the details of the Employees are to be stored in the
system with their unique EmployeeID.
 The system should be able to 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 of salaries for the
employees for the organization.
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
Employee
Salary
Payment

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

7
DATABASE SYSTEM AND DESIGN
Salary
Payment

8
DATABASE SYSTEM AND DESIGN
Task 2.5: SQL Query writing
Query 1
Select * From P17242239PAYMENT Where PMODE = 'Cash';
Query 2
Select P17242239EMPLOYEES.EMPLOYEENAME
From P17242239EMPLOYEES

9
DATABASE SYSTEM AND DESIGN
Inner Join P17242239SALARY On P17242239SALARY.EMPLOYEEID =
P17242239EMPLOYEES.EMPLOYEEID
Inner Join P17242239PAYMENT On P17242239PAYMENT.SALARYID =
P17242239SALARY.SALARYID
Where P17242239PAYMENT.PMODE = 'Online';
Query 3
Select P17242239EMPLOYEES.*, P17242239SALARY.*
From P17242239EMPLOYEES
Inner Join P17242239SALARY On P17242239SALARY.EMPLOYEEID =
P17242239EMPLOYEES.EMPLOYEEID
Order By P17242239SALARY.AMOUNT DESC;

10
DATABASE SYSTEM AND DESIGN
Query 4
Select MAX(P17242239SALARY.AMOUNT)
From P17242239SALARY;
Query 5
Select P17242239EMPLOYEES.EMPLOYEENAME, P17242239SALARY.*,
P17242239PAYMENT.*
From P17242239EMPLOYEES
Inner Join P17242239SALARY On P17242239SALARY.EMPLOYEEID =
P17242239EMPLOYEES.EMPLOYEEID
Inner Join P17242239PAYMENT On P17242239PAYMENT.SALARYID =
P17242239SALARY.SALARYID;
Query 6
Select SUM(P17242239SALARY. AMOUNT)
From P17242239SALARY;

11
DATABASE SYSTEM AND DESIGN

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.