UNIVERSITY OF WEST ATTICA
SCHOOL OF ENGINEERING
DEPARTMENT OF COMPUTER ENGINEERING AND INFORMATICS

University of West Attica · Department of Computer Engineering and Informatics

---

Databases II

Creation of Database personnel

Vasileios Evangelos Athanasiou
Student ID: 19390005

GitHub · LinkedIn

---

Supervision

Supervisor: Periklis Andritsos, Associate Professor

UNIWA Profile · LinkedIn

Co-supervisor: Rania Garofalaki, Laboratory Teaching Staff

UNIWA Profile · LinkedIn

Athens, December 2023

---

---

README

Creation of Database personnel

The laboratory exercise involves the creation of three primary tables and performing SQL operations to manage and analyze employee data.

---

Table of Contents

Section	Folder / File	Description
1	assign/	Laboratory / Assignment material
1.1	assign/laboratory_1.pdf	Laboratory instructions (English)
1.2	assign/εργαστήριο_1.pdf	Laboratory instructions (Greek)
2	docs/	Theoretical documentation
2.1	docs/Create-Database.pdf	Database creation theory (English)
2.2	docs/Δημιουργία-ΒΔ.pdf	Database creation theory (Greek)
3	queries/	Visual query examples
3.1	queries/insertDept*.png	Insert Department queries
3.2	queries/insertEmp*.png	Insert Employee queries
3.3	queries/insertJob*.png	Insert Job queries
3.4	queries/query*.png	Select / complex queries
3.5	queries/select*.png	Select specific tables
3.6	queries/Step1.png	Step-by-step illustration
4	src/	SQL scripts and related images
4.1	src/personnel.sql	SQL script for personnel database
4.2	src/personnel.png	ER diagram / model image
5	README.md	Project documentation
6	INSTALL.md	Usage instructions

---

1. DEPT (Departments)

Stores information about the organization's departments.

• Primary Key: DEPTNO
• Fields:
  • DEPTNO (int)
  • DNAME (varchar)
  • LOC (varchar)

---

2. JOB (Job Roles)

Defines job descriptions and associated base salaries.

• Primary Key: JOBCODE
• Fields:
  • JOBCODE (int)
  • JOB_DESCR (varchar)
  • SAL (int)

---

3. EMP (Employees)

Stores detailed employee records and links employees to departments and job roles.

• Primary Key: EMPNO
• Foreign Keys:
  • DEPTNO references DEPT
  • JOBNO references JOB
• Fields:
  • EMPNO (int)
  • NAME (varchar)
  • JOBNO (int)
  • DEPTNO (int)
  • COMM (int)

---

4. Laboratory Activities

The lab guides students through the following operations:

1. System Connection
   Connecting to the MySQL monitor via the command prompt.

2. Database Creation
   Checking for and creating the personnel database if it does not exist.

3. Table Initialization
   Using CREATE TABLE statements with proper primary key, foreign key, and other constraints.

4. Data Entry
   Populating tables using INSERT INTO with sample data for departments such as Sales, Account, and Payroll.

5. Data Analysis (Queries)

   • Filtering employees by job title (e.g., Salesmen or Analysts).
   • Using aggregate functions: total employees, minimum/average salaries, and commission counts.
   • Sorting data by department and salary levels.
   • Calculating departmental statistics, such as average salary per department.