Question

Budget Logistic Service operates a number of delivery vans. Each van is distinguished by its registration number, and characterized by the number of passenger seats and the maximal load weight. Budget Logistic operates various permanent delivery lines among the businesses in the neighborhood. Each line is assigned a unique number. A line has at least two stops: the origin and the destination. For a given line, a sequence of stops may exist between the origin and the destination. These stops also need to be recorded clearly as they will be used for scheduling purposes.That is, we need to be able to retrieve the origin, destination and all in-between stops if any. All stops are identified by street addresses. Some stops are shared by several lines. For each stop on each line, a specific time interval (i.e. waiting time) is prescribed for the driver to wait while the customers are bringing their packages. To avoid confusing customers, the waiting time does not depend on the day of the week or the hour. The management team of Budget Logistic keeps a schedule of lines, which fixes all the departure times (from the origin) of each line, on every day of the week. According to the load expectations, the dispatcher assigns one or more vans to each scheduled line. If no delivery jobs are expected on a certain line at a given time, then no vans are dispatched to depart on that line at that time. For each scheduled departure, the dispatcher records a brief advice to the driver, which is a short text.

Question: Construct the relational schema for the Budget Logistic database, based on the above description. Start an ER model then map it to the relational model. Your relational schema should have an adequate normalization up to Fourth normal form (4NF). For each relation you construct, define the primary key and indicate foreign keys, if any.

Answer 1