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.