CS 5614 Homework #2

1. (5+5 = 10 points) Assume that an E/R diagram has a multiway relationship and we mechanically replace it with many-one binary relationships. Under what circumstances is the first design better than the second? Under what circumstances is the second design better than the first?
   
   
2. (40 points) Design an E/R diagram for the following scenario: The specifications are jumbled up (intentionally) so that you have to first spend some time reading through it before modeling. ;-)
   
   We wish to model people, cars, licenses, and insurance policies on cars etc.. People are either men or women. They are uniquely identified by their SSN. In addition, they have a name, address, driver's license number and birthdate. The driver's license number is also unique. A person can own one or more cars (the ownership is declared in the title deed for the car). In addition, the same car can be owned by more than one person. Cars are identified by their VIN (Vehicle Identification Number). No two cars have the same VIN. Each car has to be registered with the local license branch. The license branch issues a registration plate number for each car. No two cars have the same registration plate number. A registration plate number consists of three parts - the state that issued it, the actual number (which might contain letters), and what type of plate (is it a vanity plate, if so what kind, etc.). You might be surprised to note that Virginia has > 100 types of vanity plates! Each car, to be driven, must first be registered in a state and also possess a valid insurance policy. It is illegal to drive a car without either of these. An insurance policy consists of the car VIN, the car registration number, an expiration date, and also lists the acceptable drivers (for the moment assume that only those people who are listed on the policy are eligible to drive it). In addition, only people who are older than a certain age are eligible to get a license and drive a car (don't worry about learner permits). This age threshold varies from state to state.
   
   Your E/R diagram should model as much of the information above as possible. If you are unable to model a certain aspect, identify and explain what the problem is. Use the notation that we developed in class and that is described in the lecture notes (do not use notations from other textbooks, since it would be difficult for the TA to grade these).
   
   
3. (10 points) Consider the following situation involving weak entity sets: Draw an E/R diagram for it. There are three entity sets - Leagues, Teams and Players. League names are unique. No league has two teams with the same name. No team has two players with the same number. However, there can be players with the same number on different teams, and there can be teams with the same name in different leagues.
   
   
4. (40 points) Find a site on the Web for which there is obviously an underlying database system. Attempt to view it in the light of what we have learnt in CS5614 so far. Present the site as an application of database systems.
   
   Your answer should contain (i) the URL of the site you found, (ii) 2-paragraph description of what the database functionality is (i.e, what does it model, what does it not model, who are the users, what kinds of queries it supports etc.), (iii) your version of what their E/R diagram probably looks like. Your answer should provide *at least* (a) 4-5 entity sets, (b) a similar number of relationships, (c) one example of inheritance and (d) (preferably) one example of weak entity sets. (a) and (b) are a must but if it is too difficult to do (c) and (d), don't worry about them. But do state in your response, why you think it is too "unnatural" for your application to think of inheritance/weak sets. So, your example of inheritance should not be a "cooked-up" one, just to satisfy the requirements. Also, your relationships should provide a variety of possiblities: many-many, many-one, one-one etc. Your attributes should also come from a mix of backgrounds (strings, composite structures (like addresses, dates), integers, floats etc.) In short, your design should be "rich" in all these goodies! Don't forget to underline key attributes, include arrowheads, specify any domain-specific constraints or double-border any weak sets and their connections.
   
   Example sites are:
   imdb.com,
   http://www.nobel.se/prize/database.html,
   http://www.acm.org/awards/,
   http://www.barnesandnoble.com/,
   http://www.ptiweb.com/movies/RCMall.html,
   http://www.apt-guide.com/ and so on. You get the idea!