DaniWeb IT Discussion Community

DaniWeb IT Discussion Community (http://www.daniweb.com/forums/index.php)

- Database Design (http://www.daniweb.com/forums/forum142.html)

- - Little help (http://www.daniweb.com/forums/thread156069.html)

Hello everyone,
I'm a beginner with databases and I'm trying to do some homework. I'm writing down the exercise and I'll be putting what i've done so far replying this thread. I'd appreciate if someone could help me saying if I'm in the right way or how should i do it.

Thanks
DS

A construction company requires a database to record details about building projects. Each project has its own project number, name and employees assigned to it. Each employee has an employee number, name and job classification, such as engineer or computer technician.
The company charges its clients by billing the hours spent on each contract. The hourly billing rate is dependent upon the employee’s position. For example, one hour of a computer technician’s time is billed a different rate than one hour of an engineer’s time.
You can see below some example of data:
Proj No Proj Name Emp No Emp Name Job class Charge/Hour Hours billed Total Charge
15 Eveready 100 John Electrical Engineer 84.50 20 1690.00
101 Mary Database designer 100.00 15 1500.00
102 Mark Database designer 100.00 17 1700.00
103 David Programmer 37.75 12 453.00
104 Daniel System Analyst 90.00 14 1260.00
Sub Total 6603.00

Summary of operations:
• The company manages many projects
• Each project requires the service of many employees
• Some employees are not assigned and perform duties not specifically related to a project. Some employees are part of a labour pool, to be shared by all project team. For example, the company’s executive secretary would not be assigned to any one particular project.
• Each employee has a single primary job classification. This job classification determines the hourly billing rate.
• Many employees can have the same job classification. For example, the company employs more than one electrical engineer.

1)Draw an ERD for the database. Don't need to show attributes
2) Normalise data into tables at least 3rd normal form
3)Construct data dictionary containing:
- data stores
- data elements
4) From the fully normalised 'non key' attributes, determine the functional dependency on the primary key.

1)
COMPANY - 1 ---- N - PROJECT
PROJECT - 0N ---- N - EMPLOYEE
EMPLOYEE - 1N ---- 1 - JOB

2)
Unnormalised
(ProjNo, ProjName, (EmpNo, EmpName, JobClass, ChargeHour, HoursBilled))

1NF
((ProjNo, EmpNo, ProjName, EmpName, JobClass, ChargeHour), HoursBilled)

2NF
PROJECT - ProjNo, ProjName
EMPLOYEE - EmpNo, EmpName, JobClass, ChargeHour
PROJEMP - ProjNo, EmpNo, HoursBilled

3NF
PROJECT - ProjNo, ProjName
EMPLOYEE - EmpNo, EmpName, JobClass
JOB - JobClass, ChargeHour
PROJEMP - ProjNo, EmpNo, HoursBilled

What you have so far looks very good.

agreed, def looks very good

i don't know if you are wanting to keep track of this though

Quote:

Some employees are not assigned and perform duties not specifically related to a project. Some employees are part of a labour pool, to be shared by all project team. For example, the company’s executive secretary would not be assigned to any one particular project.

your design matches this statement, minus the labor pool part, do you have separate labor pools that some secretaries aren't part of and need to keep track of this? or just assume that based upon jobclass, whether they need to be assigned or not?

Yeah, i don't know about this part...
I really didn't understand completely.
But thanks anyway...