Imagine that you have been asked to create a database that the fictional UPS (United Package Service) company will use to keep track of and schedule the packages that it needs to ship around the country. The system that you need to model is as follows. Each UPS package is uniquely identified by a tracking number. It has a weight and a time that it was received, as well as a time it was delivered, and a digital copy of the recipients signature. Each package is either picked up by UPS as the result of a scheduled pickup, or else it is dropped off at a specific UPS drop-off location. Each dropoff location is located in a city that UPS serves (a city is identified by a name and a state), and has an address. A drop-off location is uniquely identified by a location ID. Every package has a delivery address and a delivery recipient who is uniquely identified by the delivery address and the recipients name; the recipient also has a phone number. Every recipient is located in a UPS city. Every package is sent by a UPS customer, for which the same information is known (though the customer need not live in a UPS city). Some UPS customers also have one or more payment accounts. Each account is uniquely identified by an account number. Each account has a credit card associated with it. The delivery address for a package must be in a city that UPS serves. In order to travel from the drop-off city to the arrival city, a package is scheduled on a number of legs, each of which travels from one UPS city to another. UPS has a number of regularly-scheduled hops from one city to another. A leg is nothing more than a hop that occurs on a specific day. Each hop is identified by an ID number, and has a scheduled departure and a scheduled arrival time. Each hop 1 also has a maximum capacity in terms of the total weight of the packages that it may accommodate. Each leg has an actual arrival time, an actual departure time. While each UPS package is scheduled to take a specific set of legs from its drop-off city to its arrival city, the scheduled set of legs may not match the actual set for many reasons; for example, the package may misconnect when it transfers among legs. Thus, UPS also keeps track of the actual set of legs that a package takes. When a package is loaded onto a leg and when it is taken off of a leg, a bar code on the package is scanned and the time is recorded. When a package arrives at its destination, it will have one or more delivery attempts associated with it. Each delivery attempt is made by a UPS delivery person, at a certain day and time.

Task I

(50% of the grade) The first thing that you need to do is to build an ER model of the relevant part of UPSs business. You can draw your ER model on a piece of paper and scan or photograph it. Make sure your scan/photograph is legible. Alternatively, you can draw the ER diagram on a computer. Make sure you capture all constraints that are relevant for your problem on the diagram. If you so desire, you can add notes in the separate writeup.

Task II

(50% of grade) Your task in this second part of the assignment is to convert your ER model into an equivalent instance of the relational model. The correctness of this part will be judged based on faithfulness to your ER diagram not in absolute terms. In other words, you cannot fix problems into the relational model to cover issues in the ER diagram (hint: feel free to change your ER diagram if you discover problems in your relational modeling but do so consistently). The solution that you turn in for this part should include two things:

1. A list of relations (40% of your grade). Simply list all of the resulting relations, one at a time. Each relation should be of the form: RELATION NAME (att1, att2, att3 (fk: references REL2.att6), att4, att5). Note that each relation should have its key attribute(s) underlined, and all foreign keys clearly indicated along with the referenced relation and attribute.

2. A list of semantic integrity constraints, specified in English (10% of your grade). You only need to list the semantic integrity constraints that are directly specified by the ER model, but are not enforced by the keys and foreign keys given along with the relations.

Most integrity constraints will have to do with existence dependencies and inheritance hierarchies that appear in the ER model. Dont include integrity 2 constraints that you think should be there, but are not directly specified by the ER model. Dont include semantic integrity constraints that are already enforced by the relations you have specified.