Database System and Design: HR Database Design and SQL Queries

This assignment details the design and implementation of a database system for an HR department, encompassing both conceptual and logical database design phases. The initial stage focuses on selecting a relevant case study, specifically an HR database, and defining its requirements. The conceptual design outlines the entities, attributes, and relationships, along with the enterprise rules governing the database. The subsequent stage involves logical database design, including table creation using Oracle DBMS, index creation for efficiency, and data population. Finally, the assignment demonstrates SQL query writing to retrieve and manipulate data, including queries to display employee information, filter unpaid salaries, retrieve training details, count employees, and sort salaries. The assignment demonstrates the practical application of database design principles and SQL querying in a real-world HR context.

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
Table of Contents
Stage 1: Scenario and Conceptual Database Design Task.........................................................2
1.1: Selection of the case upon which the database design and implementation is to be
based.......................................................................................................................................2
The HR Database...............................................................................................................2
Task 1.2: Provide a conceptual database design for your scenario & the list of enterprise
rules being modelled..............................................................................................................3
Conceptual Design.............................................................................................................3
Enterprise Rules.....................................................................................................................3
Stage 2: Logical Database Design and Oracle SQL Implementation/querying.........................4
Task 2.1: Provide a Logical Database Design for your scenario...........................................4
Task 2.2 Create the tables using Oracle DBMS.........................................................................4
Database tables...................................................................................................................4
Task 2.3: Create the four most useful indexes on your tables...............................................6
Task 2.5: SQL Query writing.................................................................................................9
Bibliography.............................................................................................................................14

2
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 HR Database
A HR Database management system has been considered in the report and description
for the same has been included in this report. An HR database is very efficient for any type of
organization as the Human resource department handles a number of important tasks for any
type of organization and the HR department handles various other necessary functions of the
business of a certain organizations. Hence, there is an urgent requirement of implementation
of a HR database system for any organization. For the particular scenario, it has been
assumed that the HR database system would only be concerned about the training of the
employees in the organization and maintain the salary structure within the system. Hence
only the salaries and the training data has been considered here for database designed in this
report. The HR administrator has been considered here as the primary user for the system and
there would be a number of users who would be handling the different departments within the
organization. The employee data would be stored in the database who would be stored under
different departments in the organization. The employees would be getting the salaries and
attend the training that were available within the organization.

3
DATABASE SYSTEM AND DESIGN
Task 1.2: Provide a conceptual database design for your scenario & the list of
enterprise rules being modelled
Conceptual Design
Enterprise Rules
The business rules that were being considered during the development of the database
are provided below:
 The database should be able to store the details of the employees in the database and
their unique identity key would identify the employees.
 The database should also be able to store the details of the users who are enrolled in
the system and their passwords are also included in the databases that would be used
for the verification and control validation methods.

4
DATABASE SYSTEM AND DESIGN
 The database should be able to store the details of the departments their names and the
users who are currently handling the procedures in the departments.
 The database should be able to store the details of the salaries of the employees in the
system and the status of the payment is also to be stored along with it.
 The database should also be able to store the details of the training sessions held
within the organization. The date of training and the time period for training should
also be 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
Users (UserID (pk), Username, Password)
Department (DepartmentID (pk), DepartmentName, UserID (fk))
Employee (EmployeeID (pk), EmployeeName, EmployeeAddress, EmployeeContact,
DepartmentID (fk))
Training (TrainingID (pk), TrainingDate, NumberOfHours, EmployeeID (fk))
Salary (SalaryID (pk), EmployeeID (fk), Amount, Status)
Task 2.2 Create the tables using Oracle DBMS
Database tables
Users

5
DATABASE SYSTEM AND DESIGN
Departments
Employees
Training
Salary

6
DATABASE SYSTEM AND DESIGN
Task 2.3: Create the four most useful indexes on your tables
1. ALTER TABLE "P16237430TRAINING" ADD CONSTRAINT
"P16237430TRAINING_FK2"
FOREIGN KEY ("EMPLOYEEID")
REFERENCES "P16237430EMPLOYEES" ("EMPLOYEEID")
ON DELETE SET NULL
2. ALTER TABLE "P16237430SALARY" ADD CONSTRAINT
"P16237430SALARY_FK"
FOREIGN KEY ("EMPLOYEEID")
REFERENCES "P16237430EMPLOYEES" ("EMPLOYEEID")
ON DELETE SET NULL
3. Alter table "P16237430DEPARTMENT" add constraint
"P16237430DEPARTMENT_CON" foreign key ("USERID") references "P16237430USER"
("USERID") on delete set null
4. ALTER TABLE "P16237430EMPLOYEES" ADD CONSTRAINT
"P16237430EMPLOYEES_FK"
FOREIGN KEY ("DEPARTMENTID")

7
DATABASE SYSTEM AND DESIGN
REFERENCES "P16237430DEPARTMENT" ("DEPARTMENTID")
Task 2.4: Data Population
Users table
Departments Data
Employees Data

8
DATABASE SYSTEM AND DESIGN
Training Data
Salary Data

9
DATABASE SYSTEM AND DESIGN
Task 2.5: SQL Query writing
Query 1: Display the names of all the employees in the organization and salaries received
by them.
Select P16237430EMPLOYEES.EMPLOYEENAME, P16237430SALARY.AMOUNT
FROM P16237430EMPLOYEES
INNER JOIN P16237430SALARY ON P16237430SALARY.EMPLOYEEID =
P16237430EMPLOYEES.EMPLOYEEID;

10
DATABASE SYSTEM AND DESIGN
The query has been used for obtaining the name of all employees working in the
organization and the salary they receive.
Query 2: Display the names of all the employees in the organization and salaries received
by them who are unpaid.
Select P16237430EMPLOYEES.EMPLOYEENAME, P16237430SALARY.AMOUNT
FROM P16237430EMPLOYEES
INNER JOIN P16237430SALARY ON P16237430SALARY.EMPLOYEEID =
P16237430EMPLOYEES.EMPLOYEEID
WHERE P16237430SALARY.STATUS = 'Unpaid';
The name and salaries of employees who have not been paid yet are displayed here.

11
DATABASE SYSTEM AND DESIGN
Query 3: Provide the details of the employees and the departments that they belong to who
are currently in the training period.
Select P16237430EMPLOYEES.*
FROM P16237430EMPLOYEES
INNER JOIN P16237430TRAINING ON P16237430TRAINING.EMPLOYEEID =
P16237430EMPLOYEES.EMPLOYEEID;
All the details of the employees who are training currently have been displayed above.
Query 4: Count the number of employees whose salaries have not been paid.
Select COUNT(P16237430SALARY.SALARYID)
FROM P16237430SALARY
WHERE P16237430SALARY.STATUS = 'Paid';
The query displays the number of employees who have been paid.
Query 5: List the names of the employees of the organization and their salaries and sort
them in descending order with accordance to their salaries.

12
DATABASE SYSTEM AND DESIGN
Select P16237430EMPLOYEES.EMPLOYEENAME, P16237430SALARY.AMOUNT
FROM P16237430EMPLOYEES
INNER JOIN P16237430SALARY ON P16237430SALARY.EMPLOYEEID =
P16237430EMPLOYEES.EMPLOYEEID
ORDER BY P16237430SALARY.AMOUNT DESC;
The query has been used to display the salary of the employees from highest to
lowest.
Query 6: List the name of the employees and the salaries who have salary amount greater
than 5000.
Select P16237430EMPLOYEES.EMPLOYEENAME, P16237430SALARY.AMOUNT
FROM P16237430EMPLOYEES
INNER JOIN P16237430SALARY ON P16237430SALARY.EMPLOYEEID =
P16237430EMPLOYEES.EMPLOYEEID
WHERE P16237430SALARY.AMOUNT > 5000;

13
DATABASE SYSTEM AND DESIGN
The employees who are paid more than 5000 are displayed here in the above table.

14
DATABASE SYSTEM AND DESIGN
Bibliography
Coronel, C. and Morris, S., 2016. Database systems: design, implementation, & management.
Cengage Learning.
Hu, Y., 2016. Design and Implementation of Recruitment Management System Based on
Analysis of Advantages and Disadvantages of PHP Three-Tier. Romanian Review Precision
Mechanics, Optics & Mechatronics, (49), p.74.
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.
Pfaff, B., Pettit, J., Koponen, T., Jackson, E., Zhou, A., Rajahalme, J., Gross, J., Wang, A.,
Stringer, J., Shelar, P. and Amidon, K., 2015. The design and implementation of open
vswitch. In 12th {USENIX} Symposium on Networked Systems Design and Implementation
({NSDI} 15) (pp. 117-130).