Business Driven Technology 6th Edition by Paige Baltzan

Exercise 191

Billing Data
On-The-Level Construction Company is a Denver-based construction company that specializes in subcontracting the development of single-family homes. In business since 1998, On-The-Level Construction has maintained a talented pool of certified staff and independent consultants providing the flexibility and combined experience required to meet the needs of its nearly 300 completed projects in the Denver metropolitan area. The field of operation methods that On-The-Level Construction is responsible for includes structural development, heating and cooling, plumbing, and electricity.
The company charges its clients by billing the hours spent on each contract. The hourly billing rate is dependent on the employee's position according to the field of operations (as noted above). Figure AYK.5 shows a basic report that On-The-Level Construction foremen would like to see every week concerning what projects are being assigned, the overall assignment hours, and the charges for the assignment. On-The-Level Construction organizes its internal structure in four different operations-Structure (500), Plumbing (501), Electrical (502), and Heating and Ventilation (503). Each of these operational departments can and should have many subcontractors who specialize in that area. On-The-Level Construction has decided to implement a relational database model to track project details according to project name, hours assigned, and charges per hour for each job description. Originally, On-The-Level Construction decided to let one of its employees handle the construction of the database. However, that employee has not had the time to completely implement the project. On-The-Level Construction has asked you to take over and complete the development of the database.
The entities and primary keys for the database have been identified in Figure AYK.6.
The following business rules have been identified:
1. A job can have many employees assigned but must have at least one.
2. An employee must be assigned to one and only one job number.
3. An employee can be assigned to work on one or more projects.
4. A project can be assigned to only one employee but need not be assigned to any employee.
Project Focus
Your job is to complete the following tasks:
1. Develop and describe the entity relationship diagram.
2. Use normalization to assure the correctness of the tables (relations).
3. Create the database using a personal DBMS package (preferably Microsoft Access).
4. Use the DBMS package to create the basic report in Figure AYK.5.
5. You may not be able to develop a report that looks exactly like the one in Figure AYK.5. However, your report should include the same information.
6. Complete personnel information is tracked by another database. For this application, include only the minimum: employee number, last name, and first name.
7. Information concerning all projects, employees, and jobs is not readily available. You should create information for several fictitious projects, employees, and jobs to include in your database.
FIGURE AYK.5 On-The-Level-Construction Detail Report

FIGURE AYK.6 Entity Classes and Primary Keys for On-The-Level Construction