CS 235 Practice Problems 1

In this exercise, you will design a database for used cars, as might be used by an on-line trading site. The database will store automobiles for sale. Each automobile has a VIN (vehicle identification number), a model (e.g., Camero), a make (e.g., Chevrolet), a year (e.g., 1996), a color (e.g., red), a mileage (e.g., 50,000 miles), and a body style (e.g., coupe). Each automobile has a seller, which may be either a dealer or an individual. For dealers, the database has a name, address, and phone number. For individuals, only a phone number and email address are recorded.
In addition, articles about cars are maintained. Each article is about one model, make, and year of automobile, and each article has an author. The same author may write several articles about one make and model for one year. The database should connect cars with the articles about the make, model, and year of that car, although it need not make a direct connection.

Give an E/R design for this database. Briefly explain your reasoning, including the intuitive meaning of any relationships and any entity sets you use. Do not forget to indicate keys, many-one relationships, and weak entity sets in the appropriate ways.
Begin with the following two entity sets:
- States has key attribute name, and other attributes that need not concern us.
- Cities is a weak entity set related by a many-one relationship In to States. A city has a name, which is unique within a state, but not unique among all states (e.g., there are 24 states with a city named Springfield, but no state has two ``Springfields'').
Add the following information, and draw the entire E/R diagram:
1. There are bus companies. Each company operates within a single state, and no two bus companies in a single state can have the same name (although two different companies in different states could have the same name). Bus companies have addresses and phone numbers, in addition to a name.
2. There are bus routes. Each route has a number and operates within a single city. Route numbers are unique within a bus company, but two companies can have routes with the same number in the same city.
It is not always possible to replace a three-way relationship by two (or even three) two-way (binary) relationships. Suppose there is a three-way relationship among entity sets A, B, and C. Suppose also that there is some relationship set S representing the ``current value'' of this relationship. If we replace this relationship by relationships A-B, A-C, and B-C, we form the relationship sets for these new relationships by ``projecting'' each triple (a,b,c) in S onto its A and B components for A-B, and similarly for the other two binary relationships. Thus, for instance, (a,b) becomes a pair in the relationship set for A-B.
Give an example of two relationship sets S1 and S2 (for the three-way relationship) that are different, and yet their projections onto the three binary relationship sets are each the same. Note that this situation demonstrates that the three-way relationship contains more information than the three two-way relationships.