FOO1HILLS CONSTRUCTION PROJECT DETAIL PROJECT amp;AME ASSIGN DATE EMPLAST NAME EAIP FIRST NAME JOB DESCRiPTION A596amp; OUR CHARGEI'HOIIR...

9EDITION

USING RELATIONAL TECHNOLOGY TO TRACK PROJECTS

FOOTHILLS CONSTRUCTION

Foothills Construction Company is a Denver-based construction company that specializes in subcontracting the development of single family homes. In business since 1993, Foothills Construction Company has maintained a talented pool of certified staff and independent consultants allowing 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 Foothills Construction is responsible for as it relates to building include 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 below).

Figure GP.1 (below) shows a basic report that Foothills Construction managers would like to see every week concerning what projects are being assigned as well as a summary of assignment hours and changes. Foothills 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.

(Note: This figure is also attached with your Project document as a picture. If you have trouble viewing this image, you may view the details in the picture.)

Because of the boom in home sales over the last several years, Foothills 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, Foothills 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. Foothills Construction has asked you to take over and complete the development of the database.

The entity classes and primary keys for the database have been identified as the following:

The following business rules have also 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.

Your job is to be completed in the following phases:

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 GP.1 .

SOME PARTICULARS YOU SHOULD KNOW

1. You may not be able to develop a report that looks exactly like the one in Figure GP.1 .

However, your report should include the same information.

2. Complete personnel information is tracked by another database. For this application, include only the minimum employee number, last name, and first name.

3. Information concerning all projects, employees, and jobs is not readily available. You should, however, create information for several fictitious systems to include in your database.

4. File: Not applicable.