Employee Database Management

Verified

Added on  2019/09/21

|15
|704
|88
Project
AI Summary
The assignment content is about creating an employee database using ER diagram and SQL queries. The database includes tables for Department, Employee, Project, and WorksOn. It also provides sample data and various SQL queries to retrieve specific information from the database.

Contribute Materials

Your contribution can guide someone’s learning journey. Share your documents today.
Document Page
EMPLOYEE DATABASE
Student Name:
Student ID:
Course Name:
Course ID:
Faculty Name:
University Name:

Secure Best Marks with AI Grader

Need help grading? Try our AI Grader for instant feedback on your assignments.
Document Page
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
Document Page
A) ER DIAGRAM
Document Page
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);

Secure Best Marks with AI Grader

Need help grading? Try our AI Grader for instant feedback on your assignments.
Document Page
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
Document Page
Document Page

Paraphrase This Document

Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
Document Page
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.
Document Page
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’;
Document Page
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;

Secure Best Marks with AI Grader

Need help grading? Try our AI Grader for instant feedback on your assignments.
Document Page
Document Page
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;
Document Page
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;

Paraphrase This Document

Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
Document Page
Document Page
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;
1 out of 15
circle_padding
hide_on_mobile
zoom_out_icon
[object Object]

Your All-in-One AI-Powered Toolkit for Academic Success.

Available 24*7 on WhatsApp / Email

[object Object]