Database design with information of employees, their jobs, salaries and projects

I am doing a database design I want to create a database with information of employees, their jobs, salaries and projects.

I want to keep information of the cost of a project (real value of project and the days a employee invested).

For Employee and Project, each Employee has one role on the Project through the PK constraint, and allows for the addition of a new role type ("Tertiary" perhaps) in the future. To get the total amount of the cost of a project and have it for future Work quote, I would just sum the working days of each job for each employee in an aggregate query.

To sum all working days knowing the employees involved in a particular project to know the cost generated for their work I did:

select projectid, sum(total_salary) as total_salaries
from project_pay
group by projectid

Here is the sqlfiddle

There must be a way to improve this a lot:

``
CREATE TABLE Employee(
EmployeeID INTEGER NOT NULL PRIMARY KEY,
Name VARCHAR(30) NOT NULL,
Sex CHAR(1) NOT NULL,
Address VARCHAR(80) NOT NULL,
Security VARCHAR(15) NOT NULL
);

CREATE TABLE Departments (
DeptID INTEGER NOT NULL PRIMARY KEY,
DeptName VARCHAR(30) NOT NULL
);

CREATE TABLE Dept-Employee(
EmployeeID INTEGER NOT NULL,
DeptID INTEGER NOT NULL,
CONSTRAINT fk_DeptID FOREIGN KEY (DeptID) REFERENCES Departments(DeptID),
CONSTRAINT fk_EmployeeID FOREIGN KEY (EmployeeID) REFERENCES Employee(EmployeeID)
);

CREATE TABLE Dept-Manager`(
EmployeeID INTEGER NOT NULL,
DeptID INTEGER NOT NULL,
CONSTRAINT fk_DeptIDs FOREIGN KEY (DeptID) REFERENCES Departments(DeptID),
CONSTRAINT fk_EmployeeIDs FOREIGN KEY (EmployeeID) REFERENCES Employee(EmployeeID)
);

CREATE TABLE Jobs (
JobID INTEGER NOT NULL PRIMARY KEY,
JobName VARCHAR(30) NOT NULL,
JobSalary DOUBLE(15,3) NOT NULL default '0.000',
JobSalaryperDay DOUBLE(15,3) NOT

A little addition to Jens Schauder's answer:

You shouldn't abbreviate a field name, especially if the field name is a key to a table whose name isn't abbreviated.

DeptId primary key and foreign keys should be renamed to DepartmentId.

This assures consistency and helps avoid problems if using some ORMs.

StackExchange Code Review Q#18299, answer score: 4