Database Design Project

This assignment presents a practical exercise in database design. It starts by defining business rules for a scenario involving employees, projects, and job classifications. Then, it outlines the entities and their attributes, followed by an Entity-Relationship Diagram (ERD). A data dictionary is provided, detailing table structures, data types, and constraints. The solution further explains functional dependencies and normalization, demonstrating the process of achieving 3NF. Finally, it includes SQL DDL (Data Definition Language) statements to create tables with constraints and DML (Data Manipulation Language) statements for inserting and deleting data. The assignment comprehensively covers database design principles and their implementation using SQL.

Business Rule:
 Each employee has one job classification
 Many employees have one classification
 Each project needs to assign many employees
 One employee may assign to multiple project
 Some employee may not assign to project
A employee can work in one job at a time but in a one job designation we have multiple
employee, according to my assumption we should have one three or four entities, and every
employee , job designation and project must have one unique identification number and using
this we can identify the employee, job and projects.
Entities and Attributes:
Entities Attributes
Project ProjNum
ProjName
Assign AssignID
ProjNum
EmpNum
Hours
Employee EmpNum
EmpName
JobClass
Job JobClass
ChargeHour
Entity Relationship Diagram:
Data Dictionary:

Table
Name
Attribute Attribute
Descriptio
n
Data
Type
Data
Form
at
Ran
ge
Mandato
ry
Primary
Kay/Forei
gn Key
Foreign
Key
Referen
ce
Table
Project ProjNum Unique
Identifier
of Project
int 999 Y PK
ProjName Name of
Project
Char(4
0)
Aaaa
aa
Y
Assign AssignID Unique
Identifier
of assign
int 999 Y PK
ProjNum Project
identificati
on number
int 999 Y FK Project
EmpNum Employee
identificati
on number
int 999 Y FK Employ
ee
Hours Hours
work by
employee
Decim
al
99.9 Y
Employ
ee
EmpNum Unique
Identifier
of
Employee
int 999 Y PK
EmpNam
e
Employee
name
Char(4
0)
Aaaa
a
Y
JobClass Job class
of
employee
Char(4
0)
Aaaa
a
Y FK Job
Job JobClass Unique
Identifier
of job
Char(4
0)
Aaaa
a
Y PK
ChargeH
our
Charge
per hour
Decim
al
$999.
9
Y
Functional Dependence:
When one attribute uniquely determines another attribute in the table is called functional
dependency. In the given case study Project number is determine the project name in the
project table.
Normalisation:
Normalisation is a process of assigning the attributes into the entities, it reduces the data
redundancy, and normalisation helps to eliminate the data anomalies.

Dependency Diagram:
Table: Project
Primary Key: ProjNo
Foreign Key: None
Normal Form: 3NF
Table: Assign
Primary Key: AssignID
Foreign Key: ProjNo (To Table Project)
EmpNum ( To Table Employee)
Normal Form: 3NF
Table: Employee
Primary Key: EmpNum
Foreign Key: JobClass (To Table Job)
Normal Form: 3NF
Table: Job
Primary Key: JobClass
Foreign Key: None
Normal Form: 3NF
SQL Statement (DDL and DML):
Create Table with constraints:

create table Project
(ProjNo int format=99. length = 200 label = " ProjNo ",,
ProjName char(50) Not Null format=Aaaa. length = 200 label = " ProjName ",,
CONSTRAINT prim_key primary key(ProjNo)
);
create table JOB
(JobClass char(40),
ChargeHour decimal(5,2) Not Null,
CONSTRAINT prim_key primary key(JobClass)
);
create table EMPLOYEE
(EmpNum int,
EmpName char(50) Not Null,
CONSTRAINT prim_key primary key(EmpNum),
JobClass char(40),
CONSTRAINT foreign_key foreign key(JobClass) REFERENCES JOB(JobClass)
);
create table ASSIGN
(AssignID int,
Hours decimal(3,1) Not Null,
CONSTRAINT prim_key primary key(AssignID),
ProjNo int,
CONSTRAINT foreign_key foreign key(ProjNo) REFERENCES Project(ProjNo),
EmpNum int,
CONSTRAINT foreign_key foreign key(EmpNum) REFERENCES
EMPLOYEE(EmpNum)
);
Delete:
delete from EMPLOYEE;
delete from Project;
delete from ASSIGN;
delete from JOB;
Insert:
insert into Project values(6,"Evergreen");
insert into Project values(18,"Amber Wave");
insert into Project values(22,"Rolling Tide");
insert into JOB values("Database Designer",105.00);
insert into JOB values("Electrical Engineer",84.50);
insert into JOB values("Programmer",37.75);

insert into EMPLOYEE values(103,"june Arbough","Electrical Engineer");
insert into EMPLOYEE values(101,"John New", "database Designer");
insert into EMPLOYEE values(105,"Alice Johnson", "Programmer");
insert into ASSIGN values(303,23.8,6,103);
insert into ASSIGN values(301,19.4,18,101);
insert into ASSIGN values(305,35.7,22,105);