Entity-Relationship Diagram Assignment
Draw the associated E-R Diagram and signify which components need to be included to meet the defined needs.
1) The user is a director of research for a large foundation. Approximately three hundred facilities are staffed by the foundation’s twelve-hundred employees. Over seventeen hundred projects are currently underway, with some three-hundred and fifty waiting to be worked on in the future.
The system is responsible for information regarding the current state of affairs only. Requests for information are:
· List all current employees in name sequence
· List all employees who work at a particular facility
· Determine which facility has the most employees; which has the fewest
· Display the names of all employees currently working on a particular project
· Display all projects an employee is currently working on
· Locate inactive (no employee assigned) project
· Determine the ratio of active to inactive projects
2) The user is responsible for all shared hardware devices in the building. Approximately five-hundred and twenty-five devices are installed in three-hundred offices. Each device can be shared by any number of users.
The system is responsible for keeping track of:
· The office in which each device is installed
· The date the device was installed
· The users authorized to employ the device
· The ten devices that have the largest number of users; and the ten witch have the fewest
· The total number of devices currently installed
· The average number of devices per office; the office with the most devices; and the office with the fewest
· Users who are authorized to use devices in more than one office
· A list of all offices without a device of some sort
3) The user is a manager of a growing air freight service. Seven-hundred routes are traveled by some of the firm’s eight-hundred and fifty planes each day. Customer packages are assigned to a particular route. Each route is assigned to a particular plane each day.
The system is responsible for the following operations:
· Display the plane for a given route
· Display the route for a given plane
· Determining how many packages are on each route, the average number of packages per route, the route with the most packages and the route with the fewest
· Keeping track of the total number of unavailable (route assigned) and available (no route assigned) planes
· Displaying all the packages given a customer name
· Locating the customer for a particular package
· Calculating the percentage of currently available planes
4) The user is a manager of a large taxi cab service. Seven hundred cabs are manned by about fourteen-hundred drivers on two shifts. The city of operation is divided into nine-hundred square areas, each consisting of a number of streets. All streets are straight and run either east/west or north/south. A street may be in more than one area. Street names are unique. Each driver is assigned to a particular cab and area when they arrive for work. The driver reports to the dispatcher by radio upon pick-up (“cab 47, in use”) and delivery of customers at their destination (“cab 47, available”). The driver also reports area changes (“cab 47, area 13”).
The system is responsible for the following operations:
· Locating the area, given the names of two intersecting streets
· Locating an available cab in a given area
· Determining how many cabs are in each area, the average number of cabs per area, the area with the most cabs and the area with the fewest cabs
· Keep track of the total “in use” and “available” cabs
· Locating a driver given his name
· Calculating the percentage of currently “available” cabs.
