An online used-car trading dot-com hires you to design a database for its Web site. The database will store information about used 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., 1999), 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 each dealer, the database stores name, address, phone number. For each individual, only phone number and email address are recorded.
• In addition, the Web site maintains reviews about automobiles. Each review is about one particular model, make, and year. Each review has an author. The same author may write several reviews about the same model, make, and year. The database should connect each car with the reviews about the make, model, and year of that car,although it need not make a direct connection.
(a) Give an E/R design for this database. Very briefly explain the intuitive meaning of any entity and relationship sets. Do not forget to indicate any keys, multiplicity of relationships, and weak entity sets in their appropriate ways.
(b) Design a relational schema for this database. (You can start by translating the E/R design.) You may ignore attribute types, and you do not need to show any sample data. Indicate all keys and non-trivial functional dependencies in the schema. Check if the schema is in BCNF. If not, decompose the schema into BCNF.