1. Construct an E-R diagram representing the conceptual design on the database. Be sure to identify PRIMARY KEYS, RELATIONSHIP CARDINALITIES, ETC.
2. Create a relational schema based on your E-R design, refine it based on the principles of relational design.
3. Create indicies and restraints.
- vehicles: Each vehicle as a vehicle identification number (VIN). Lots of stuff is encode d in real VINs (they are well described on Wikipedia), but you can want just make them up if you * brands: E ach company may have several brands (for example, GM has Chevrolet, Pontiac, uick, Cadillac, GMC, Saturn, Hummer, Saab, Daewoo, Holden, Vauxhall, and Opel and olkswagen has Volkswagen, Audi, Lamborghini, Bentley, Bugatti, Skoda, and SEAT) ach brand offers several models (for example, Buick's models are the Enclave, LaCrosse, and Lucerne, and Mercury's models are the Mariner, Milan, Sable, and Grand Marquis). Each model may come in a variety of body styles (4-door, wagon, etc.) options: We'll stick to color, and maybe engine and transmission - dealers and customers: dealers buy vehicles from the manufacturer and sell them to customers. We'll keep track of sales by date, brand, model, and color; and also by dealer. This will allow us to use SQL's OLAP tools. Note that a dealer may not sell some of the car company's brands. Dealer's keep some cars in inventory. Some of course, are already sold but the dealer still keeps track of that fact . suppliers: suppliers supply certain parts for certain models company-owned manufacturing plants: Some plants supply certain parts for certain models; others do final assembly of actual cars - customers: In reality, lots of demographic data are gathered. We'll stick to name, address phone, gender, and annual income for individual buyers. The customer may also be a company (e.g. Hertz, Avis, or other companies that maintain corporate fleets, but we'll skip that) - vehicles: Each vehicle as a vehicle identification number (VIN). Lots of stuff is encode d in real VINs (they are well described on Wikipedia), but you can want just make them up if you * brands: E ach company may have several brands (for example, GM has Chevrolet, Pontiac, uick, Cadillac, GMC, Saturn, Hummer, Saab, Daewoo, Holden, Vauxhall, and Opel and olkswagen has Volkswagen, Audi, Lamborghini, Bentley, Bugatti, Skoda, and SEAT) ach brand offers several models (for example, Buick's models are the Enclave, LaCrosse, and Lucerne, and Mercury's models are the Mariner, Milan, Sable, and Grand Marquis). Each model may come in a variety of body styles (4-door, wagon, etc.) options: We'll stick to color, and maybe engine and transmission - dealers and customers: dealers buy vehicles from the manufacturer and sell them to customers. We'll keep track of sales by date, brand, model, and color; and also by dealer. This will allow us to use SQL's OLAP tools. Note that a dealer may not sell some of the car company's brands. Dealer's keep some cars in inventory. Some of course, are already sold but the dealer still keeps track of that fact . suppliers: suppliers supply certain parts for certain models company-owned manufacturing plants: Some plants supply certain parts for certain models; others do final assembly of actual cars - customers: In reality, lots of demographic data are gathered. We'll stick to name, address phone, gender, and annual income for individual buyers. The customer may also be a company (e.g. Hertz, Avis, or other companies that maintain corporate fleets, but we'll skip that)
