Question

In the table below, there is some data required to create and company database that can manage dif-

ferent projects within an organisation. The table includes information about;
The project numbers and project names
The employee names, their jobs classes and the projects that they are working on
The charge per hour, the billing hours and the total charges for employee in each job class
• The total charges for each project and the overall charge for all combined
●
●
A SAMPLE REPORT LAYOUT
PROJECT
NUMBER
15
18
22
25
PROJECT EMPLOYEE EMPLOYEE NAME
NAME
NUMBER
Evergreen
Amber Wave
Rolling Tide
Starflight
103
101
105
106
102
114
118
104
112
database
105
104
113
111
106
107
115
101
114
108
118
112
June E. Arbough
John G. News
Alice K. Johnson*
William Smithfield
David H. Senior
Annelise Jones
James J. Frommer
Anne K. Ramoras.
Darlene M. Smithson
Alice K. Johnson
Anne K. Ramoras
Delbert K. Joenbrood
Geoff B. Wabash
William Smithfield
Maria D. Alonzo
Travis B. Bawangi
John G. News
Annelise Jones
Ralph B. Washington
James J. Frommer
Darlene M. Smithson
Using the data in the table above;
JOB CLASS
Elec. Engineer
Database Designer
Database Designer
Programmer
Systems Analyst
Subtotal
Applications Designer
Compl
General Support
Systems Analyst
DSS Analyst
Subtotal
Subtotal
Database Designer
Com
Systems Analyst
Applications Designer
Appice
Clerical Support
Programmer
www
Subtotal
----
Programmer
Systems Analyst
Database Designer
Applications Designer
Systems Analyst
General Support
DSS Analyst
Subtotal
Total
CHARGE/
HOUR
$ 84.50
$105.00
$105.00
$ 35.75
$ 96.75
$ 48.10
$ 18.36
$ 96.75
$ 45.95
$105.00
$96.75
$48.10
$26.87
$35.75
$ 35.75
$ 96.75
$105.00
$ 48.10
$ 96.75
$ 18.36
$ 45.95
HOURS
BILLED
23.8
19.4
35.7
12.6
23.8
24.6
45.3
32.4
44.0
64.7
48.4
23.6
22.0
12.8
24.6
45.8
56.3
33.1
23.6
30.5
414
TOTAL
CHARGE
$ 2,011.10
$ 2,037.00
$ 3,748.50
S 450.45
$ 2,302.65
$10,549.70
$ 1,183.26
$ 831.71
$ 3,134.70
1031.90
$ 2,021.80
717147
$7,171.47
6703.60
6,793.50
CON
$ 4,682.70
Vezaro
$
$ 1,135.16
$ 591.14
$ 457.60
$13,660.10
$ 879.45
$ 4,431.15
borde
$ 5,911.50
$ 1,592.11
$ 2,283.30
$ 559.98
$ 1,902.33
$17,559.82
$48,941.09
1. Carry out data normalization up to Third Normal Form (3NF)
2. Explain each step of the process and the requirements needed to fill each Normal Form
3. Write out 2 different query's that can be used retrieve specific information on the normalized

Answer 1

First Normal Form (1NF): Ensure atomicity and eliminate repeating groups by breaking data into individual tables.

Create tables for Projects, Employees, Job Classes, and Employee-Project assignments.

Each table should have a primary key (unique identifier).

Remove repeating groups (such as multiple employee names in a single field).

Second Normal Form (2NF): Ensure all non-key attributes depend on the entire primary key.

Separate tables to establish relationships between entities.

Ensure all fields relate directly to the primary key of their respective tables.

Eliminate partial dependencies.

Third Normal Form (3NF): Remove transitive dependencies.

Ensure each non-prime attribute depends only on the primary key.

Remove any fields that depend on non-primary key fields.

Split tables to resolve dependencies between non-prime attributes.

To achieve 3NF:

Projects table (Project Number, Project Name)

Employees table (Employee Number, Employee Name)

Job Classes table (Job Class, Charge per Hour)

Employee-Project table (Employee Number, Project Number, Hours Billed)

Calculate Total Charges using formulas rather than storing redundant data.

Ensure proper relationships between tables using foreign keys.