Database Term Project: SQL Implementation, Queries, and Design

This document presents a term project focusing on SQL implementation for database management. It begins with a draft ER diagram and a translated relational schema, followed by the creation of tables including OFFICE, EMPLOYEE, DEPARTMENT, and ISSUES. The project includes record insertion into these tables, populating them with sample data. The core of the project involves several SQL queries designed to retrieve specific information, such as employee details, issue counts, salary updates, and department-wise employee counts. The queries address different requirements, demonstrating the practical application of SQL for data retrieval, manipulation, and reporting. The project showcases the implementation of database design and SQL for efficient data management and analysis.

0 | P a g e
TERM PROJECT: DRAFT SQL IMPLEMENTATION
STUDENT NAME:
15 October 2016

Contents
LATEST DRAFT ER DIAGRAM..................................................................................................................2
TRANSLATED RELATIONAL SCHEMA......................................................................................................2
TABLE CREATION...................................................................................................................................2
RECORD INSERTION...............................................................................................................................5
SQL QUERIES..........................................................................................................................................9

LATEST DRAFT ER DIAGRAM
TRANSLATED RELATIONAL SCHEMA
A. EMPLOYEE (EMP_NO , FNAME, LNAME, SALARY, PHONE, DEP_NO*)
B. DEPARTMENT(DEP_NO , DEPT_NAME, EMP_NO*, OFFICE_ID*)
C. OFFICE (OFFICE_ID, OFFICE_NAME, ADDRESS, PHONE)
D. ISSUES (ISSUE_ID, ISSUE_DATE, STATUS, DETAILS, REMARKS, EMP_NO*)
TABLE CREATION
CREATE TABLE OFFICE
(
OFFICE_ID VARCHAR(4) PRIMARY KEY,
OFFICE_NAME VARCHAR(30) NOT NULL,
ADDRESS VARCHAR(50) NOT NULL,
PHONE NUMBER(10,0)

);
CREATE SEQUENCE seq_emp
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 10;
CREATE TABLE EMPLOYEE
(
EMP_NO INT,
FNAME VARCHAR(20),
LNAME VARCHAR(20),
SALARY NUMBER(10,2),
PHONE NUMBER(10,0),
DEP_NO VARCHAR(4),
PRIMARY KEY (EMP_NO)
);

CREATE TABLE DEPARTMENT
(
DEP_NO varchar(4) primary key,
DEPT_NAME varchar(30),
EMP_NO int,
OFFICE_ID varchar(4),
FOREIGN KEY(EMP_NO) REFERENCES EMPLOYEE(EMP_NO),
FOREIGN KEY(OFFICE_ID) REFERENCES OFFICE(OFFICE_ID));
ALTER TABLE EMPLOYEE ADD CONSTRAINT FK1 FOREIGN KEY(DEP_NO) REFERENCES
DEPARTMENT(DEP_NO);
CREATE SEQUENCE SEQ_ISUE
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 10;

CREATE TABLE ISSUES
(
ISSUE_ID INT PRIMARY KEY,
ISSUE_DATE DATE,
STATUS VARCHAR(50),
DETAILS VARCHAR(100),
REMARKS VARCHAR(100),
EMP_NO INT,
FOREIGN KEY(EMP_NO) REFERENCES EMPLOYEE(EMP_NO),
CONSTRAINT CHK1 CHECK (STATUS IN ('OPEN', 'CLOSED', 'PENDING') )
);
RECORD INSERTION
INSERT INTO OFFICE (OFFICE_ID, OFFICE_NAME, ADDRESS, PHONE) VALUES ('O1' , 'HEAD OFFICE' ,
'21 HILL VIEW ROAD, LONG ISLAND, USA', 9999999983);
INSERT INTO OFFICE (OFFICE_ID, OFFICE_NAME, ADDRESS, PHONE) VALUES ('O2' , 'SATELLITE OFFICE
1' , '222 PARK STREET, NYC, USA', 9999998983);
INSERT INTO OFFICE (OFFICE_ID, OFFICE_NAME, ADDRESS, PHONE) VALUES ('O3' , 'SATELLITE OFFICE
2' , '55/455 DENISE ROAD, LA, USA', 9999997983);
SELECT * FROM OFFICE;

INSERT INTO EMPLOYEE (EMP_NO , FNAME, LNAME, SALARY, PHONE, DEP_NO) VALUES
(SEQ_EMP.NEXTVAL, 'JOHN', 'LAMES', 8900.90, 9981234567, 'D1');
INSERT INTO EMPLOYEE (EMP_NO , FNAME, LNAME, SALARY, PHONE, DEP_NO) VALUES
(SEQ_EMP.NEXTVAL, 'DIA', 'STEVEN', 99900.90, 777789876, 'D1');
INSERT INTO EMPLOYEE (EMP_NO , FNAME, LNAME, SALARY, PHONE, DEP_NO) VALUES
(SEQ_EMP.NEXTVAL, 'DAVE', 'LANISTER', 7900.90, 771178876, 'D2');
INSERT INTO EMPLOYEE (EMP_NO , FNAME, LNAME, SALARY, PHONE, DEP_NO) VALUES
(SEQ_EMP.NEXTVAL, 'DAVID', 'STONES', 199900.90, 722778876, 'D2');
INSERT INTO EMPLOYEE (EMP_NO , FNAME, LNAME, SALARY, PHONE, DEP_NO) VALUES
(SEQ_EMP.NEXTVAL, 'JAMES', 'JOHNSON', 69000.90, 73778876, 'D3');
INSERT INTO EMPLOYEE (EMP_NO , FNAME, LNAME, SALARY, PHONE, DEP_NO) VALUES
(SEQ_EMP.NEXTVAL, 'JAMIE', 'BELE', 8900.90, 977778876, 'D3');
INSERT INTO EMPLOYEE (EMP_NO , FNAME, LNAME, SALARY, PHONE, DEP_NO) VALUES
(SEQ_EMP.NEXTVAL, 'JULIE', 'STEPHEN', 89900.90, 177778876, 'D4');
INSERT INTO EMPLOYEE (EMP_NO , FNAME, LNAME, SALARY, PHONE, DEP_NO) VALUES
(SEQ_EMP.NEXTVAL, 'MIA', 'DRAKES', 8900.90, 577778876, 'D4');

INSERT INTO DEPARTMENT(DEP_NO , DEPT_NAME, EMP_NO, OFFICE_ID) VALUES ('D1', 'SALES', 10,
'O1');
INSERT INTO DEPARTMENT(DEP_NO , DEPT_NAME, EMP_NO, OFFICE_ID) VALUES ('D2', 'ADMIN', 12,
'O2');
INSERT INTO DEPARTMENT(DEP_NO , DEPT_NAME, EMP_NO, OFFICE_ID) VALUES ('D3', 'TECHNICAL',
13, 'O3');
INSERT INTO DEPARTMENT(DEP_NO , DEPT_NAME, EMP_NO, OFFICE_ID) VALUES ('D4', 'SUPPORT',
15, 'O3');
SELECT * FROM DEPARTMENT;
INSERT INTO ISSUES (ISSUE_ID, ISSUE_DATE, STATUS, DETAILS, REMARKS, EMP_NO) VALUES (1, '13-
OCT-16', 'OPEN', 'SOFTWARE FAILURE', '', 10);

INSERT INTO ISSUES (ISSUE_ID, ISSUE_DATE, STATUS, DETAILS, REMARKS, EMP_NO) VALUES (2, '12-
OCT-16', 'OPEN', 'HARDWARE FAILURE', '', 12);
INSERT INTO ISSUES (ISSUE_ID, ISSUE_DATE, STATUS, DETAILS, REMARKS, EMP_NO) VALUES (3, '10-
OCT-16', 'PENDING', 'HARDWARE REPAIR', '', 14);
INSERT INTO ISSUES (ISSUE_ID, ISSUE_DATE, STATUS, DETAILS, REMARKS, EMP_NO) VALUES (4, '09-
OCT-16', 'CLOSED', 'BACKUP ISSUES', '', 12);
INSERT INTO ISSUES (ISSUE_ID, ISSUE_DATE, STATUS, DETAILS, REMARKS, EMP_NO) VALUES (5, '08-
OCT-16', 'CLOSED', 'SOFTWARE UPDATE', '', 14);
SELECT * FROM ISSUES;

SQL QUERIES
Question 1: Director needs the name of all employees who are head of department along with office
and department details for a meeting.
Sql: select e.fname as head, d.dept_name as department_name, o.office_id as offices from
employee e, department d, office o where e.emp_no = d.emp_no and o.office_id = d.office_id;

Question 2: Manager needs to how many hardware issues were there till date.
Sql: select count(issue_id) AS NO_OF_HARDWARE_ISSUES from issues where details like
'%HARDWARE%';
Question 3: Director wants to update salary of all the employee by 10% who are head of
departments.
SQL: UPDATE EMPLOYEE SET SALARY = SALARY + (0.1 * SALARY) WHERE EMP_NO IN (SELECT
EMP_NO FROM DEPARTMENT);
Question 4: Manager needs to know the number of open issues and their registered dates.
SQL: SELECT ISSUE_ID, ISSUE_DATE FROM ISSUES WHERE STATUS = 'OPEN';
Question 5: Director needs to know the number of employees (including heads) in each department
of an office so as to decide the number of employees that have to be hired in future.
SQL: SELECT COUNT(e.EMP_NO) as NO_OF_EMPLOYEES, D.DEPT_NAME, O.OFFICE_NAME from
employee e, department d, office o where e.dep_no = d.dep_no and o.office_id = d.office_id GROUP
BY D.DEPT_NAME, O.OFFICE_NAME;