Question

1. Use the SQL Developer to create the four tables EMP, DEPT, PROJ, EMP_PROJ with the appropriate constraints except FOREIGN KEY constraints. For filename, use CREATE.sql accordingly.
2. Import data from the csv files to EMP, DEPT, and PROJ tables.
3. Check that all data for EMP, DEPT, and PROJ tables has been imported.

__________________________

employees.csv

empNo,fname,lname,address,sex,salary,position,deptNo

1000,Steven,King,"731 Fondren, Houston, TX",M,30000,Programmer,60

1007,Diana,Lorentz,"638 Voss, Bellaire, TX",F,24000,Clerk,20

2002,Pat,Fay,"3321 Castle, Spring, TX",F,15000,Sales Representative,80

1760,Jonathan,Taylor,"561 Rice, Houston, TX",M,60000,Manager,20

1740,Ellen,Abel,"890 Stone, Houston, TX",F,65000,Manager,60

2060,William,Gietz,"450 Berry, Bellaire, TX",M,65000,Manager,80

2000,Jennifer,Whalen,"980 Fire Oak, Humble, TX",F,28000,Clerk,60

1444,Peter,Vargas,"975 Dallas, Houston, TX",M,20000,Sales Representative,80

_________________________________

Departments.csv

deptNumber,deptName,Mgr

20,Marketing,1760

60,IT,1740

80,Sales,2060

_________________________

Projects.csv

projNumber,projName,deptNum

10,Product X,20

20,Product Y,20

30,Computerization,60

40,Product Z,80

50,Mobile Apps,60

Answer 1

MySQL code for create tables.

create Table DEPT(
        deptNumber INT NOT NULL,
        deptName VARCHAR(200),
        Mgr INT,
        PRIMARY KEY(deptNumber)
);

create Table PROJ(
        projNumber INT NOT NULL,
        prohName VARCHAR(200),
        deptNum INT,
        PRIMARY KEY(projNumber),
        FOREIGN KEY(deptNum) REFERENCES DEPT(deptNumber)
);


create Table EMP(
        empNO INT NOT NULL,
        fname VARCHAR(200),
        lname VARCHAR(200),
        address VARCHAR(200),
        sex VARCHAR(1),
        salary REAL,
        position VARCHAR(200),
        depNo INT,
        PRIMARY KEY(empNo),
        FOREIGN KEY(depNo) REFERENCES DEPT(deptNumber)
);

create Table EMP_PROJ(
        projNumber INT NOT NULL,
        empNO INT NOT NULL,
        PRIMARY KEY(projNumber, empNO),
        FOREIGN KEY(projNumber) REFERENCES PROJ(projNumber),
        FOREIGN KEY(empNO) REFERENCES EMP(empNO)
);
        

Load CSV file into databse.

LOAD DATA INFILE '/home/daenerys/Desktop/Departments.csv'
INTO TABLE DEPT
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS


LOAD DATA INFILE '/home/daenerys/Desktop/Projects.csv'
INTO TABLE PROJ
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS


LOAD DATA INFILE '/home/daenerys/Desktop/employees.csv'
INTO TABLE EMP
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS

Select data from database

SELECT * FROM EMP;

SELECT * FROM DEPT;

SELECT * FROM PROJ;