Logical Database Design for HR management System

Logical Database Design for HR management SystemTask 1.1 The background information of the nerve and operation that would support.In an organization a HR department is responsible for record each employee. Where the employees have an identification number, job identification code, e-mail address, omnibus as surface as salary. They likewise track those employees earn incentive or commissions in addition to their salary.However, the company also tracks their role in the organization. severally job also recorded according to the characteristics. Moreover, ever jobs have job title, identification code, maximum and minimum salary of the job. There argon few employees depart for a long time with the company and they have held different department within the company. If any employee resigns, then the job identification number and department ar recorded. The company also track the location of its departments and warehouses. Every employee must assign with a department where department s are identified by the unique identification number. Those departments are associated with different locations. The company need to store the location such as the state, city, postal code, street name as well as county code. The company also record the county name, currency name and the region.This database supports a discontinue employee management plan as well as their departments, location and associated jobs. However, the company would have a better coordinate to store their confidential information. This database will provide a better extracted information to developed their insufficiency. This efficient data structure allows them increases their storage as well as it exclude the redundancy in data.Task 1.2 a conceptual database design and list of opening move rulesFigure 1 EER-diagram showing all enterprise rules (Source Created by author)Task2.1 A Logical Database Design for HR management SystemFigure 2 logical database design(Source Created by author)Task2.2 Create the dodges using vaticinator DBMS - carry over structure for COUNTRIES - push aside confuse MYDB.COUNTRIESCREATE knock back MYDB.COUNTRIES (country_id VARCHAR2(30 BYTE) non nugatory ,country_name VARCHAR2(30 BYTE) zip fastener ,region_id VARCHAR2(30 BYTE) trifling) enterNOCOMPRESSNOCACHE - Table structure for DEPARTMENTS -DROP display board MYDB.DEPARTMENTSCREATE TABLE MYDB.DEPARTMENTS (department_id VARCHAR2(30 BYTE) NOT NULL ,department_name VARCHAR2(30 BYTE) NULL ,manager_id VARCHAR2(30 BYTE) NULL ,location_id VARCHAR2(30 BYTE) NULL)LOGGINGNOCOMPRESSNOCACHE - Table structure for EMPLOYEES -DROP TABLE MYDB.EMPLOYEESCREATE TABLE MYDB.EMPLOYEES (employee_id VARCHAR2(30 BYTE) NOT NULL ,first_name VARCHAR2(30 BYTE) NULL ,last_name VARCHAR2(30 BYTE) NULL ,email VARCHAR2(30 BYTE) NULL ,phone_number NUMBER(12) NULL ,hire_date DATE NULL ,job_id VARCHAR2(30 BYTE) NULL ,salary NUMBER(10,2) NULL ,commission NUMBER(10,2) NULL ,manager_id VARCHAR2(30 BYTE) NULL ,department_id VARCHAR2(30 BYTE) NULL)LOGGINGNOCOMPRESSNOCACHE - Table structure for JOB_HISTORY -DROP TABLE MYDB.JOB_HISTORYCREATE TABLE MYDB.JOB_HISTORY (employee_id VARCHAR2(30 BYTE) NOT NULL ,start_date DATE NULL ,end_date DATE NULL ,job_id VARCHAR2(30 BYTE) NULL ,department_id VARCHAR2(30 BYTE) NOT NULL)LOGGINGNOCOMPRESSNOCACHE - Table structure for JOBS -DROP TABLE MYDB.JOBSCREATE TABLE MYDB.JOBS (job_id VARCHAR2(30 BYTE) NOT NULL ,job_title VARCHAR2(30 BYTE) NULL ,min_salary NUMBER(10,2) NULL ,max_salary NUMBER(10,2) NULL)LOGGINGNOCOMPRESSNOCACHE - Table structure for LOCATIONS -DROP TABLE MYDB.LOCATIONSCREATE TABLE MYDB.LOCATIONS (location_id VARCHAR2(30 BYTE) NOT NULL ,street_address VARCHAR2(30 BYTE) NULL ,postal_code NUMBER(10) NULL ,city VARCHAR2(30 BYTE) NULL ,state VARCHAR2(30 BYTE) NULL ,country_id VARCHAR2(30 BYTE) NULL)LOGGINGNOCOMPRESSNOCACHE - Table structure for REGIONS -DROP TABLE MYDB.REGIONSCREATE TABLE MYDB.REGIONS (region_id VARCHAR2(30 BYTE) NOT NULL ,region_name VARCHAR2(30 BYTE) N ULL)LOGGINGNOCOMPRESSNOCACHETask2.3 Create the four most serviceable indexes - Indexes structure for board COUNTRIES - - Checks structure for table COUNTRIES - wangle TABLE MYDB.COUNTRIES cast up CHECK (country_id IS NOT NULL) - patriarchal Key structure for table COUNTRIES -ALTER TABLE MYDB.COUNTRIES sum up PRIMARY KEY (country_id) - Indexes structure for table DEPARTMENTS - - Checks structure for table DEPARTMENTS -ALTER TABLE MYDB.DEPARTMENTS bestow CHECK (department_id IS NOT NULL) - Primary Key structure for table DEPARTMENTS -ALTER TABLE MYDB.DEPARTMENTS ADD PRIMARY KEY (department_id) - Indexes structure for table EMPLOYEES - - Checks structure for table EMPLOYEES -ALTER TABLE MYDB.EMPLOYEES ADD CHECK (employee_id IS NOT NULL) - Primary Key structure for table EMPLOYEES -ALTER TABLE MYDB.EMPLOYEES ADD PRIMARY KEY (employee_id) - Indexes structure for table JOB_HISTORY - - Checks structure for table JOB_HISTORY -ALTER TABLE MYDB.JOB_HISTORY ADD CHECK (employee_id IS NOT NULL)ALTER TABLE MYDB.JOB_HISTORY ADD CHECK (department_id IS NOT NULL) - Primary Key structure for table JOB_HISTORY -ALTER TABLE MYDB.JOB_HISTORY ADD PRIMARY KEY (employee_id) - Indexes structure for table JOBS - - Checks structure for table JOBS -ALTER TABLE MYDB.JOBS ADD CHECK (job_id IS NOT NULL) - Primary Key structure for table JOBS -ALTER TABLE MYDB.JOBS ADD PRIMARY KEY (job_id) - Indexes structure for table LOCATIONS - - Checks structure for table LOCATIONS -ALTER TABLE MYDB.LOCATIONS ADD CHECK (location_id IS NOT NULL) - Primary Key structure for table LOCATIONS -ALTER TABLE MYDB.LOCATIONS ADD PRIMARY KEY (location_id) - Indexes structure for table REGIONS - - Checks structure for table REGIONS -ALTER TABLE MYDB.REGIONS ADD CHECK (region_id IS NOT NULL) - Primary Key structure for table REGIONS -ALTER TABLE MYDB.REGIONS ADD PRIMARY KEY (region_id) - Foreign Key structure for table MYDB.COUNTRIES -ALTER TABLE MYDB.COUNTRIES ADD irrelevant KEY (region_id) REFERENCES MYDB.RE GIONS (region_id) ON DELETE CASCADE - Foreign Key structure for table MYDB.DEPARTMENTS -ALTER TABLE MYDB.DEPARTMENTS ADD FOREIGN KEY (location_id) REFERENCES MYDB.LOCATIONS (location_id) ON DELETE CASCADE - Foreign Key structure for table MYDB.EMPLOYEES -ALTER TABLE MYDB.EMPLOYEES ADD FOREIGN KEY (job_id) REFERENCES MYDB.JOBS (job_id) ON DELETE CASCADEALTER TABLE MYDB.EMPLOYEES ADD FOREIGN KEY (department_id) REFERENCES MYDB.DEPARTMENTS (department_id) ON DELETE CASCADE - Foreign Key structure for table MYDB.JOB_HISTORY -ALTER TABLE MYDB.JOB_HISTORY ADD FOREIGN KEY (employee_id) REFERENCES MYDB.EMPLOYEES (employee_id) ON DELETE CASCADE - Foreign Key structure for table MYDB.LOCATIONS -ALTER TABLE MYDB.LOCATIONS ADD FOREIGN KEY (country_id) REFERENCES MYDB.COUNTRIES (country_id) ON DELETE CASCADETask2.4 Data PopulationThe below figures showing all data in each tableTable countriesTable departmentsTable employeesTable job_historyTable jobsTable locationsTable regionsTask2.5 SQL inqui ry writingQuery 1SELECTMYDB.COUNTRIES.country_nameFROMMYDB.COUNTRIESQuery 2SELECTMYDB.REGIONS.region_name,MYDB.COUNTRIES.country_nameFROMMYDB.COUNTRIESINNER summation MYDB.REGIONS ON MYDB.COUNTRIES.region_id = MYDB.REGIONS.region_idQuery 3SELECTMYDB.JOB_HISTORY.start_date,MYDB.JOB_HISTORY.end_date,MYDB.EMPLOYEES.first_name,MYDB.EMPLOYEES.last_name,MYDB.EMPLOYEES.emailFROMMYDB.EMPLOYEESFULL OUTER JOIN MYDB.JOB_HISTORY ON MYDB.JOB_HISTORY.employee_id = MYDB.EMPLOYEES.employee_idQuery 4SELECTCount(MYDB.EMPLOYEES.employee_id) AS Number Of EmployeeFROMMYDB.EMPLOYEESQuery 5SELECTMYDB.EMPLOYEES.first_name,MYDB.EMPLOYEES.last_name,MYDB.EMPLOYEES.email,MYDB.EMPLOYEES.phone_number,MYDB.EMPLOYEES.hire_date,MYDB.EMPLOYEES.salary,MYDB.EMPLOYEES.commissionFROMMYDB.EMPLOYEESORDER BYMYDB.EMPLOYEES.first_name ASCQuery 6SELECTMYDB.EMPLOYEES.first_name,MYDB.EMPLOYEES.last_name,MYDB.EMPLOYEES.email,MYDB.EMPLOYEES.phone_number,MYDB.EMPLOYEES.hire_date,MYDB.EMPLOYEES.salary,MYDB.EMPLOYEES.commissionFROM MYDB.EMPLOYEESWHEREMYDB.EMPLOYEES.email LIKE %gmail%Query 7SELECTMYDB.EMPLOYEES.first_name,MYDB.EMPLOYEES.last_name,MYDB.EMPLOYEES.email,MYDB.EMPLOYEES.phone_numberFROMMYDB.EMPLOYEESINNER JOIN MYDB.JOB_HISTORY ON MYDB.JOB_HISTORY.employee_id = MYDB.EMPLOYEES.employee_idWHEREMYDB.JOB_HISTORY.employee_id IN (MYDB.EMPLOYEES.employee_id)Query 8MYDB.EMPLOYEES.email,MYDB.EMPLOYEES.phone_number,MYDB.EMPLOYEES.hire_date,MYDB.EMPLOYEES.job_id,MYDB.EMPLOYEES.salary,MYDB.EMPLOYEES.commission,MYDB.EMPLOYEES.manager_id,MYDB.EMPLOYEES.department_id,MYDB.EMPLOYEES.employee_idFROMMYDB.EMPLOYEES,(SELECT MYDB.JOB_HISTORY.employee_id from MYDB.JOB_HISTORY) subquery1WHEREsubquery1.employee_id=MYDB.EMPLOYEES.employee_idAsabe, S.A., Oye, N.D. and Goji, M., 2013. Hospital patient database management system A case study of general hospital north-bank makurdi-nigeria. Compusoft, 2(3), p.65.Coronel, C. and Morris, S., 2016. Database systems design, implementation, management. Cengage Learning.Dorok, S., Bre , S., Teubner, J. and Saake, G., 2015. Flexible Analysis of Plant Genomes in a Database Management System. In EDBT (pp. 509-512).Hussain, M., Pandey, A.C. and Pachauri, S., 2013. Performanc Tuning of Database Management System by Fuzzy Controlled Architecture. Pragyaan Journal of Information Technology, p.30.Jahn, M., Schill, E. and Breunig, M., 2013. Towards a 4D database management system for geothermal projects an example of the hydraulic data of Soultz. In Second European Geothermal Workshop.Lee, H., Chapiro, J., Schernthaner, R., Duran, R., Wang, Z., Gorodetski, B., Geschwind, J.F. and Lin, M., 2015. How I do it a practical database management system to assist clinical research teams with data collection, organization, and reporting. Academic radiology, 22(4), pp.527-533.Li, Z. and Shen, H., 2016. Database Design on Teaching Management System Based on SQL Server.Mohamed, A.R., Kumar, P.V., Abhilash, S., Ravishankar, C.N. and Edwin, L., 2013. Design and Development of an Online Database Management System (AGRI-TECHBASE) For Agricultural Technologies of ICAR. In cause the Economy through Innovation and Entrepreneurship (pp. 869-877). Springer India.Nidzwetzki, J.K. and Gting, R.H., 2016. DISTRIBUTED SECONDO An extensible highly available and scalable database management system.Reddy, T.B.K., Thomas, A.D., Stamatis, D., Bertsch, J., Isbandi, M., Jansson, J., Mallajosyula, J., Pagani, I., Lobos, E.A. and Kyrpides, N.C., 2014. The Genomes OnLine Database (GOLD) v. 5 a metadata management system establish on a four level (meta) genome project classification. Nucleic acids research, p.gku950.Sui, X.L., Wang, D., Liu, X.Y. and Teng, Y., 2014. Database Design of NC Cutting Tool Matching and Management System. In Advanced Materials Research (Vol. 981, pp. 546-550). Trans Tech Publications.