Create a databse for the below application using MySQL and upload the sql file t

Question

Create a databse for the below application using MySQL and upload the sql file that shows create and insert statements for all the tables:

Please see the hint before answering which says to create 7 tables not just one Employee table.

A small accounting firm wants a simple HR application that will help it to keep track of its employees, their positions, office locations, salary scales, and which company vehicles their employees drive. The application must keep track of all the positions at the firm, the employees filling these positions, the offices for these positions which may have more than one employee, the salary scales for these positions, and the company vehicles assigned to these positions.

Hint: There are seven tables in the solution. For each entity, there is a table. Take a look at the last sentence for 5 entities. when there are many to many relationships, we need bridge tables.

Explanation / Answer

The 7 Tables Identified are:-

CREATE TABLE POSITION (

POSITION_NAME VARCHAR(100),

POSITION_DESCRIPTION VARCHAR(1000),

POSITION_PRIORITY INTEGER,

CONSTRAINT POSITION_PK PRIMARY KEY (POSITION_NAME));

CREATE TABLE SALARY (

SCALE_ID INTEGER,

UPPER_SCALE INTEGER,

LOWER_SCALE INTEGER,

CONSTRAINT SALARY_PK PRIMARY KEY (SCALE_ID));

CREATE TABLE LOCATION (

LOCATION_ID VARCHAR(100),

LOCATION_NAME VARCHAR(100),

ADDRESS VARCHAR(1000),

LANDMARK VARCHAR(100),

CONSTRAINT LOCATION_PK PRIMARY KEY (LOCATION_ID));

CREATE TABLE VEHICLE (

VEHICLE_ID VARCHAR(10),

NAME VARCHAR(100),

MODEL_NO VARCHAR(100),

CONSTRAINT VEHICLE_PK PRIMARY KEY (VEHICLE_ID));

CREATE TABLE EMPLOYEE (

EMPID INTEGER,

FNAME VARCHAR(100),

LNAME VARCHAR(100),

DOB DATE,

POSITION_NAME VARCHAR(100),

SCALE_ID INTEGER,

CONSTRAINT EMPLOYEE_PK PRIMARY KEY (EMPID),

CONSTRAINT EMPLOYEE_FK1 FOREIGN KEY (POSITION_NAME) REFERENCES POSITION (POSITION_NAME),
CONSTRAINT EMPLOYEE_FK2 FOREIGN KEY (SCALE_ID) REFERENCES SALARY (SCALE_ID));

CREATE TABLE WORKS(

EMPID INTEGER,

LOCATION_ID VARCHAR(100),

CONSTRAINT WORKS_FK1 FOREIGN KEY (EMPID) REFERENCES EMPLOYEE (EMPID),

CONSTRAINT WORKS_FK2 FOREIGN KEY (LOCATION_ID) REFERENCES LOCATION (LOCATION_ID));

CREATE TABLE DRIVES (

EMPID INTEGER,

VEHICLE_ID VARCHAR(10),

CONSTRAINT DRIVES_FK1 FOREIGN KEY (EMPID) REFERENCES EMPLOYEE (EMPID),

CONSTRAINT DRIVES_FK2 FOREIGN KEY (VEHICLE_ID) REFERENCES VEHICLE (VEHICLE_ID));

INSERT COMMANDS:-

INSERT INTO POSITION VALUES ('CEO','CHIEF OPERATING OFFICER', 1);

INSERT INTO POSITION VALUES ('CTO','CHIEF TECHNICAL OFFICER', 5);

INSERT INTO POSITION VALUES ('MANAGER','PROJECT MANAGER', 10);

INSERT INTO SALARY VALUES (1, 1000000000, 20000000);

INSERT INTO SALARY VALUES (2, 1000000, 100000);

INSERT INTO SALARY VALUES (3, 10000, 1000);

INSERT INTO LOCATION VALUES ('LOC-1', 'NAME1','ADDRESS1','LANDMARK1');

INSERT INTO LOCATION VALUES ('LOC-2', 'NAME2','ADDRESS2','LANDMARK2');

INSERT INTO VEHICLE VALUES ('VEH-001','TATA SUMO','MODEL-001');

INSERT INTO VEHICLE VALUES ('VEH-002','LIMMO','MODEL-002');

INSERT INTO VEHICLE VALUES ('VEH-003','RANGE ROVER','MODEL-003');

INSERT INTO EMPLOYEE VALUES (12345, 'GOPAL','MALAKER',TO_DATE('08-AUG-1990','DD-MON-YYYY'),'CEO',1);

INSERT INTO EMPLOYEE VALUES (54321, 'JACK','SPARROW',TO_DATE('08-DEC-1995','DD-MON-YYYY'),'CTO',2);

INSERT INTO WORKS VALUES (12345, 'LOC-1');

INSERT INTO WORKS VALUES (54321, 'LOC-2');

INSERT INTO DRIVES VALUES (12345, 'VEH-002');

INSERT INTO DRIVES VALUES (12345, 'VEH-003');

INSERT INTO DRIVES VALUES (54321, 'VEH-002');

COMMIT;

Please let me know in case of any clarifications required. Thanks!

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.