Logical Database Design for HR management System

Coherent Database Design for HR the board System Assignment 1.1 The foundation data of the association and activity that would bolster. In an association a HR office is answerable for record every worker. Where the workers have an ID number, work ID code, email address, supervisor just as pay. They additionally track those workers procure motivator or commissions notwithstanding their pay. In any case, the organization additionally tracks their job in the association. Each activity likewise recorded by the attributes. Additionally, ever employments have work title, recognizable proof code, greatest and least pay of the activity. There are barely any representatives work for quite a while with the organization and they include held diverse office inside the organization. In the event that any worker leaves, at that point the activity recognizable proof number and division are recorded. The organization additionally track the area of its specializations and distribution centers. Each worker must allocate with an office where divisions are recognized by the one of a kind distinguishing proof number. Those offices are related with various areas. The organization need to store the area, for example, the state, city, postal code, road name just as district code. The organization additionally record the area name, money name and the locale. This database bolsters a superior worker the board plan just as their areas of expertise, area and related employments. Be that as it may, the organization would have a superior structure to store their secret data. This database will give a superior removed data to built up their deficiency. This proficient information structure permits them expands their capacity just as it prohibit the excess in information. Assignment 1.2 a theoretical database plan and rundown of big business rules Figure 1: EER-outline demonstrating all undertaking rules (Source: Created by creator) Task2.1: A Logical Database Design for HR the board System Figure 2: sensible database structure (Source: Created by creator) Task2.2: Create the tables utilizing Oracle DBMS - Table structure for COUNTRIES - DROP TABLE MYDB.COUNTRIES; Make TABLE MYDB.COUNTRIES ( country_id VARCHAR2(30 BYTE) NOT NULL , country_name VARCHAR2(30 BYTE) NULL , region_id VARCHAR2(30 BYTE) NULL ) LOGGING NOCOMPRESS NOCACHE ; - Table structure for DEPARTMENTS - DROP TABLE MYDB.DEPARTMENTS; Make 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 ) LOGGING NOCOMPRESS NOCACHE ; - Table structure for EMPLOYEES - DROP TABLE MYDB.EMPLOYEES; Make 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 , compensation NUMBER(10,2) NULL , commission NUMBER(10,2) NULL , manager_id VARCHAR2(30 BYTE) NULL , department_id VARCHAR2(30 BYTE) NULL ) LOGGING NOCOMPRESS NOCACHE ; - Table structure for JOB_HISTORY - DROP TABLE MYDB.JOB_HISTORY; Make 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 ) LOGGING NOCOMPRESS NOCACHE ; - Table structure for JOBS - DROP TABLE MYDB.JOBS; Make 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 ) LOGGING NOCOMPRESS NOCACHE ; - Table structure for LOCATIONS - DROP TABLE MYDB.LOCATIONS; Make 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 ) LOGGING NOCOMPRESS NOCACHE ; - Table structure for REGIONS - DROP TABLE MYDB.REGIONS; Make TABLE MYDB.REGIONS ( region_id VARCHAR2(30 BYTE) NOT NULL , region_name VARCHAR2(30 BYTE) NULL ) LOGGING NOCOMPRESS NOCACHE ; Task2.3: Create the four most valuable lists - Files structure for table COUNTRIES - - Checks structure for table COUNTRIES - Adjust TABLE MYDB.COUNTRIES ADD CHECK (country_id IS NOT NULL); - Essential Key structure for table COUNTRIES - Adjust TABLE MYDB.COUNTRIES ADD PRIMARY KEY (country_id); - Files structure for table DEPARTMENTS - - Checks structure for table DEPARTMENTS - Adjust TABLE MYDB.DEPARTMENTS ADD CHECK (department_id IS NOT NULL); - Essential Key structure for table DEPARTMENTS - Adjust TABLE MYDB.DEPARTMENTS ADD PRIMARY KEY (department_id); - Files structure for table EMPLOYEES - - Checks structure for table EMPLOYEES - Adjust TABLE MYDB.EMPLOYEES ADD CHECK (employee_id IS NOT NULL); - Essential Key structure for table EMPLOYEES - Adjust TABLE MYDB.EMPLOYEES ADD PRIMARY KEY (employee_id); - Lists structure for table JOB_HISTORY - - Checks structure for table JOB_HISTORY - Change TABLE MYDB.JOB_HISTORY ADD CHECK (employee_id IS NOT NULL); Change TABLE MYDB.JOB_HISTORY ADD CHECK (department_id IS NOT NULL); - Essential Key structure for table JOB_HISTORY - Change TABLE MYDB.JOB_HISTORY ADD PRIMARY KEY (employee_id); - Lists structure for table JOBS - - Checks structure for table JOBS - Change TABLE MYDB.JOBS ADD CHECK (job_id IS NOT NULL); - Essential Key structure for table JOBS - Change TABLE MYDB.JOBS ADD PRIMARY KEY (job_id); - Lists structure for table LOCATIONS - - Checks structure for table LOCATIONS - Change TABLE MYDB.LOCATIONS ADD CHECK (location_id IS NOT NULL); - Essential Key structure for table LOCATIONS - Change TABLE MYDB.LOCATIONS ADD PRIMARY KEY (location_id); - Lists structure for table REGIONS - - Checks structure for table REGIONS - Change TABLE MYDB.REGIONS ADD CHECK (region_id IS NOT NULL); - Essential Key structure for table REGIONS - Change TABLE MYDB.REGIONS ADD PRIMARY KEY (region_id); - Remote Key structure for table MYDB.COUNTRIES - Change TABLE MYDB.COUNTRIES ADD FOREIGN KEY (region_id) REFERENCES MYDB.REGIONS (region_id) ON DELETE CASCADE; - Remote Key structure for table MYDB.DEPARTMENTS - Change TABLE MYDB.DEPARTMENTS ADD FOREIGN KEY (location_id) REFERENCES MYDB.LOCATIONS (location_id) ON DELETE CASCADE; - Remote Key structure for table MYDB.EMPLOYEES - Change TABLE MYDB.EMPLOYEES ADD FOREIGN KEY (job_id) REFERENCES MYDB.JOBS (job_id) ON DELETE CASCADE; Change TABLE MYDB.EMPLOYEES ADD FOREIGN KEY (department_id) REFERENCES MYDB.DEPARTMENTS (department_id) ON DELETE CASCADE; - Remote Key structure for table MYDB.JOB_HISTORY - Change TABLE MYDB.JOB_HISTORY ADD FOREIGN KEY (employee_id) REFERENCES MYDB.EMPLOYEES (employee_id) ON DELETE CASCADE; - Remote Key structure for table MYDB.LOCATIONS - Change TABLE MYDB.LOCATIONS ADD FOREIGN KEY (country_id) REFERENCES MYDB.COUNTRIES (country_id) ON DELETE CASCADE; Task2.4: Data Population The underneath figures demonstrating all information in each table: Table nations: Table offices: Table workers: Table job_history: Table employments: Table areas: Table districts: Task2.5: SQL Query composing Inquiry 1 SELECT MYDB.COUNTRIES.country_name FROM MYDB.COUNTRIES Inquiry 2 SELECT MYDB.REGIONS.region_name, MYDB.COUNTRIES.country_name FROM MYDB.COUNTRIES Internal JOIN MYDB.REGIONS ON MYDB.COUNTRIES.region_id = MYDB.REGIONS.region_id Inquiry 3 SELECT MYDB.JOB_HISTORY.start_date, MYDB.JOB_HISTORY.end_date, MYDB.EMPLOYEES.first_name, MYDB.EMPLOYEES.last_name, MYDB.EMPLOYEES.email FROM MYDB.EMPLOYEES FULL OUTER JOIN MYDB.JOB_HISTORY ON MYDB.JOB_HISTORY.employee_id = MYDB.EMPLOYEES.employee_id Inquiry 4 SELECT Count(MYDB.EMPLOYEES.employee_id) AS Number Of Employee FROM MYDB.EMPLOYEES Inquiry 5 SELECT MYDB.EMPLOYEES.first_name, MYDB.EMPLOYEES.last_name, MYDB.EMPLOYEES.email, MYDB.EMPLOYEES.phone_number, MYDB.EMPLOYEES.hire_date, MYDB.EMPLOYEES.salary, MYDB.EMPLOYEES.commission FROM MYDB.EMPLOYEES Request BY MYDB.EMPLOYEES.first_name ASC Inquiry 6 SELECT MYDB.EMPLOYEES.first_name, MYDB.EMPLOYEES.last_name, MYDB.EMPLOYEES.email, MYDB.EMPLOYEES.phone_number, MYDB.EMPLOYEES.hire_date, MYDB.EMPLOYEES.salary, MYDB.EMPLOYEES.commission FROM MYDB.EMPLOYEES WHERE MYDB.EMPLOYEES.email LIKE %gmail% Inquiry 7 SELECT MYDB.EMPLOYEES.first_name, MYDB.EMPLOYEES.last_name, MYDB.EMPLOYEES.email, MYDB.EMPLOYEES.phone_number FROM MYDB.EMPLOYEES Internal JOIN MYDB.JOB_HISTORY ON MYDB.JOB_HISTORY.employee_id = MYDB.EMPLOYEES.employee_id WHERE MYDB.JOB_HISTORY.employee_id IN (MYDB.EMPLOYEES.employee_id) Inquiry 8 MYDB.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_id FROM MYDB.EMPLOYEES, (SELECT MYDB.JOB_HISTORY.employee_id fromã‚â MYDB.JOB_HISTORY) subquery1 WHERE subquery1.employee_id=MYDB.EMPLOYEES.employee_id Asabe, S.A., Oye, N.D. also, Goji, M., 2013. Emergency clinic quiet database the executives framework: A contextual investigation of general medical clinic north-bank makurdi-nigeria. Compusoft, 2(3), p.65. Coronel, C. what's more, Morris, S., 2016. Database frameworks: structure, usage, the executives. Cengage Learning. Dorok, S., Breãÿ, S., Teubner, J. what's more, Saake, G., 2015. Adaptable Analysis of Plant Genomes in a Database Management System. In EDBT (pp. 509-512). Hussain, M., Pandey, A.C. what's more, Pachauri, S., 2013. Performanc Tuning of Database Management System by Fuzzy Controlled Architecture. Pragyaan: Journal of Information Technology, p.30. Jahn, M., Schill, E. what's more, Breunig, M., 2013. Towards a 4D database administration framework for geothermal activities: a test