Database Systems

1. Use the following business rules to create a Crow’s Foot ERD.

• ABC Construction does custom construction projects.
• Each project has a foreman and a customer.
• Multiple projects can be done for the same customer over time.
• Each foreman can run multiple projects over time.
• When a project is started, the total time and cost is estimated.
• Each project has multiple activities that are required to complete project work.
• Each activity has an estimated start date, estimated end date, estimated cost, actual start date, actual end date, percent complete and actual cost.
• Each week a progress report is created for a project.
• Each progress report communicates the objectives completed, work planned for the next week, and lessons learned.
• When a project is completed, the actual cost is recorded. It is calculated by adding the actual costs from all of the activities.
• Each project is performed at a location owned by the customer.
• Each customer can own multiple locations.

2.     Use the following business rules to create a Crow’s Foot ERD.

• A bus company wants to track bus and facility usage using the RFID chips that are in students’ ID cards (this is a fictional scenario – the university is not actually doing this).
• Each bus is equipped with an RFID reader that reads a student’s ID number when a bus or facility is entered or exited.
• Directional antennas are used to determine if the read is from an entry or exit.
• Each bus is driven by a bus driver.
• Bus drivers drive the buses according to a work schedule.
• Buses make multiple stops, but they always visit the stops in the same order.
• Administrators want to know aggregate information about entries by time by location, exits by time by location, and usage (in between entry and exit) by time by location.
• Each bus driver has a personal vehicle that is authorized to park in the parking lot.
• The bus driver’s vehicle is identified by a license plate number.