Database Assignment: Employee Database Design and Queries

This document presents a complete solution for an employee database assignment. It begins with an ER diagram illustrating the database's structure, followed by the necessary CREATE TABLE commands for the Department, Employee, Project, and WorksOn tables. The solution includes screenshots of sample data populated within these tables, demonstrating the database's functionality. Furthermore, the assignment provides a series of SQL queries designed to retrieve specific information from the database, such as listing employees alphabetically, filtering by gender or department, and generating reports on employee hours and project participation. The queries cover a range of data retrieval tasks, from simple selections to more complex aggregations and joins, providing a comprehensive example of database design and querying techniques. This assignment is designed to aid students with their database coursework and can be found on Desklib, a platform offering study tools.

EMPLOYEE DATABASE
Student Name:
Student ID:
Course Name:
Course ID:
Faculty Name:
University Name:

Contents
A) ER DIAGRAM.....................................................................................................................2
B) CREATE TABLE COMMANDS..............................................................................................3
Deparment Table.........................................................................................................................3
Employee Table...........................................................................................................................3
Project Table................................................................................................................................4
WorksOn Table............................................................................................................................4
C) SCREEN SHOTS OF SAMPLE DATA....................................................................................4
SQL QUERIES................................................................................................................................7

A) ER DIAGRAM

B) CREATE TABLE COMMANDS
CREATE DATABASE EMP_DB_SYSTEM;
USE EMP_DB_SYSTEM;
Deparment Table
CREATE TABLE Department(
deptNo INT PRIMARY KEY,
deptName VARCHAR(40) NOT NULL,
mgrEmpID INT UNIQUE DEFAULT NULL
);
Employee Table
CREATE TABLE Employee(
empID INT PRIMARY KEY,
fName VARCHAR(10) NOT NULL,
lName VARCHAR(10) NOT NULL,
address VARCHAR(100) NOT NULL,
dob DATE NOT NULL,
sex ENUM('M','F') NOT NULL,
position VARCHAR(40) NOT NULL,
deptNo INT NOT NULL,
FOREIGN KEY (deptNo) REFERENCES Department(deptNo)
);
ALTER TABLE Department ADD FOREIGN KEY (mgrEmpID) REFERENCES
Employee(empID);

Project Table
CREATE TABLE Project(
projNo INT,
projName VARCHAR(50) NOT NULL,
deptNo INT REFERENCES Department(deptNo),
PRIMARY KEY(projNo,deptNo)
);
WorksOn Table
CREATE TABLE WorksOn(
empID INT REFERENCES Employee(empID),
porjNo INT REFERENCES Project(projNo),
hoursWorked DOUBLE NOT NULL
);
C) SCREEN SHOTS OF SAMPLE DATA
Insert (at least) 10 sample Employee rows, 6 sample Departments rows, 4 sample project rows
and WorksOn rows

SQL QUERIES
1. List all employees in alphabetical order of surname, and then first name.
SELECT * FROM Employee ORDER BY lName, fName;
2. List all the details of employees who are female.
SELECT * FROM Employee WHERE sex = ‘F’;
3. List the names and addresses of all employees who are Managers.

SELECT fName, lName, address FROM Employee WHERE position = 'manager';
4. Produce a list of the names and addresses of all employees who work for the IT
department.
SELECT fName, lName, address from Employee e, Department d
WHERE e.deptNo = d.deptNo
AND d.deptName = ‘IT’;

5. Produce a complete list of all managers who are due to retire this year, in alphabetical
order of surname.
SELECT lName,dob, (extract(year from DATE(CURRENT_DATE))) - (extract(year from dob)) as age
FROM Employee e, Department d
WHERE e.empID = d.mgrEmpID AND
(extract(year from DATE(CURRENT_DATE))) - (extract(year from dob)) >= 65
ORDER BY lName;

6. Find out how many employees are managed by ‘James Adams’.
SELECT COUNT(empID) FROM Employee
WHERE deptNo = (SELECT deptNo FROM Employee
WHERE fName = 'James' AND lName = 'Adams')
AND (fName <> 'James' AND lName <> 'Adams');
7. Produce a report of the total hours worked by each employee, arranged in order of
department number and within department, alphabetically by employee surname.
SELECT d.deptNo,e.lName, e.fName, hoursWorked
FROM WorksOn w, Employee e, Department d
WHERE e.deptNo = d.deptNo
AND e.empID = w.empID
ORDER by d.deptNo, e.lName;

8. For each project on which more than two employees worked, list the project number,
project name, and the number of employees who work on that project.
SELECT p.projNo, projName, count(empID)
FROM Project p, WorksOn w
WHERE p.projNo = w.projNo
GROUP BY p.projNo
HAVING COUNT(empID) > 2;

9. List the total number of employees in each department for those departments with
more than 10 employees. Create an appropriate heading for the columns of the results
table.
SELECT deptNo,COUNT(empID) as empCount
FROM Employee
GROUP BY deptNo;
HAVING COUNT(empID) > 10;