CS 5614 Homework #2

1. Having a multiway relationship is better when (a) we would like the diagram to bring out more naturally the fact that the relationship extends to more than two entity sets (it will be more difficult to enforce this via participation constraints in the other design; the reason being schema is abstracted as data in the new connecting entity set) or (b) if we would not like weak sets in our design (since they increase the number of attributes); notice that connecting entity sets obtained from multiway relationships are likely to be weak. We might choose to add a defining attribute which will further complicate the design. A third elegant reason has been brought to my attention by Batul Mirza. (c) The idea is as follows: Since the "push-out" construction models the original multiway concept by the actual data in the new connecting entity set, there is an implicit assumption that the data will be consistent with any "-one" constraints that were present in the original multiway design. Notice that this is an issue because irrespective of the -one constraints in the original multiway, the final E/R diagram obtained by pushing-out is the same! :-) Thus, it is conceivable that the pushed-out design could potentially invalidate the -one constraints by allowing inconsistent data! The only case when you could never invalidate it by placing spurious data is if there were no arrows in the multiway. Thus, the third reason is that the original design is better if there are any "-one" constraints in the multiway design.
   
   Replacing it via a connecting entity set is better when (a) the relationship has an "existence"/"identity" in the real world (in addition to serving as a relationship). This occurs in relationships such as "contracts" etc., (b) when the connecting entity set could potentially relate (to) many combinations of entities from the other sets (contracts again), or (c) when implementation considerations insist on either binary relationships or many-one constraints (we saw this earlier in the context of the network model).
   
   
2. <your answer here> Here's a good solution by Robert Capra. Notice that some of the constraints not modeled by the diagram (and which go in the notes) are (i) that people cannot be both men and women, (ii) a car can have only one registration plate and the "registered" plate must be the same as the plate considered in the "ok to drive" relationship. (iii) People can only have a driver's license if their birthday is before the driving age threshold for the state issuing the license. The assumptions made in the diagram are: (i) Driving licenses are a weak set because the numbers are not unique across states, (ii) for any given car and owner, there is a title declaring that ownership, (iii) local license branches register vehicles for a particular state. If the state were to go away, the registration process would probably change.
   
   
   
   
3. The question itself indicates that there is a hierarchy of weak sets. Here's a solution (courtesy Ullman).
4. <your answer here> Here's a solution outlining a database model for the Ebay auctions site (courtesy Capra again).