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 Themainbusinessrulesofthesystemwhichhaveusedforthedatabase 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
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
5 DATABASE SYSTEM AND DESIGN SQL Queries Query 1 Select MAX(P17241341SALARY.AMOUNT) From P17241341SALARY; Query 2 Select P17241341EMPLOYEES.EMPLOYEENAME From P17241341EMPLOYEES InnerJoinP17241341SALARYonP17241341SALARY.EMPLOYEEID= P17241341EMPLOYEES.EMPLOYEEID
6 DATABASE SYSTEM AND DESIGN Where P17241341SALARY.STATUS = 'Unpaid'; Query 3 Select P17241341EMPLOYEES.EMPLOYEENAME, P17241341SALARY.AMOUNT From P17241341EMPLOYEES InnerJoinP17241341SALARYonP17241341SALARY.EMPLOYEEID= P17241341EMPLOYEES.EMPLOYEEID Where P17241341SALARY.AMOUNT > 5000; Query 4 SelectP17241341DEPARTMENT.DEPARTMENTNAME, COUNT(P17241341EMPLOYEES.EMPLOYEEID) From P17241341EMPLOYEES Inner Join P17241341DEPARTMENT on P17241341DEPARTMENT.DEPARTMENTID = P17241341EMPLOYEES.DEPARTMENTID
Secure Best Marks with AI Grader
Need help grading? Try our AI Grader for instant feedback on your assignments.
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.