Logical Database Design for HR management System

Logical Database designing for HR focusing System proletariat 1.1 The background information of the organization and operation that would support.In an organization a HR department is responsible for record each employee. Where the employees have an realisation number, job acknowledgment code, e-mail address, manager as well as salary. They besides track those employees earn incentive or flushs in do-gooder to their salary.However, the confederacy also tracks their role in the organization. Each job also recorded according to the characteristics. Moreover, ever jobs have job title, identification code, utmost and minimum salary of the job. There argon few employees work for a long time with the company and they have held different department at bottom the company. If any employee resigns, then the job identification number and department are recorded. The company also track the location of its departments and warehouses. Every employee must narrow down with a department wh ere departments are identified by the unique identification number. Those departments are associated with different locations. The company need to store the location such(prenominal) 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 entropybase supports a remedy employee forethought plan as well as their departments, location and associated jobs. However, the company would have a better bodily social organisation to store their confidential information. This database pull up stakes provide a better extracted information to developed their insufficiency. This efficient data mental synthesis entirelyows them increases their storage as well as it exclude the tautology in data.Task 1.2 a conceptual database design and list of enterprisingness rulesFigure 1 EER-diagram showing all enterprise rules (Source Created by author)Task2.1 A Logical Database throw for HR management SystemFigur e 2 logical database design(Source Created by author)Task2.2 Create the accedes using Oracle database management governing body - parry organize for COUNTRIES - spend confuse MYDB.COUNTRIES make believe flurry MYDB.COUNTRIES (country_id VARCHAR2(30 BYTE) non naught ,country_name VARCHAR2(30 BYTE) profitless ,region_id VARCHAR2(30 BYTE) nada)LOGGINGNOCOMPRESSNOCACHE - gameboard social structure for DEPARTMENTS - spew tabularise MYDB.DEPARTMENTSCREATE TABLE MYDB.DEPARTMENTS (department_id VARCHAR2(30 BYTE) NOT unimportant ,department_name VARCHAR2(30 BYTE) invalid ,manager_id VARCHAR2(30 BYTE) visionary ,location_id VARCHAR2(30 BYTE) NULL)LOGGINGNOCOMPRESSNOCACHE - put back structure for EMPLOYEES - disgrace 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 REGIO NS -DROP TABLE MYDB.REGIONSCREATE TABLE MYDB.REGIONS (region_id VARCHAR2(30 BYTE) NOT NULL ,region_name VARCHAR2(30 BYTE) NULL)LOGGINGNOCOMPRESSNOCACHETask2.3 Create the four most useful indexes - Indexes structure for turn off COUNTRIES - - Checks structure for table COUNTRIES - demasculinise TABLE MYDB.COUNTRIES bestow stigmatise (country_id IS NOT NULL) - Primary primordial structure for table COUNTRIES - fake TABLE MYDB.COUNTRIES add up PRIMARY identify (country_id) - Indexes structure for table DEPARTMENTS - - Checks structure for table DEPARTMENTS - misrepresent TABLE MYDB.DEPARTMENTS rack up notice (department_id IS NOT NULL) - Primary primal structure for table DEPARTMENTS - garble TABLE MYDB.DEPARTMENTS ADD PRIMARY lynchpin (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) - conflicting Key structure for table MYDB.COUNTRIES -ALTER TABLE MYDB.COUNTRIES ADD outside(prenominal) KEY (region_id) REFERENCES MYDB.REGIONS (region_id) ON DELETE cascade down - 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 doubtfulness writing interrogative sentence 1 filmMYDB.COUNTRIES.country_nameFROMMYDB.COUNTRIESQuery 2SELECTMYDB.REGIONS.region_name,MYDB.COUNTRIES.country_nameFROMMYDB.COUNTRIESINNER give way 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 bring together 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 BYMY DB.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.commissionFROMMYDB.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 mana gement 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 abbreviation of Plant Genomes in a Database care 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 stand by 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), p p.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 Driving the delivery through Innovation and Entrepreneurship (pp. 869-877). Springer India.Nidzwetzki, J.K. and Gting, R.H., 2016. DISTRIBUTED SECONDO An extensible highly operational 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 base 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 caustic Tool Matching and Management System. In Advanced Materials Research (Vol . 981, pp. 546-550). Trans Tech Publications.