E-R Design. You are provided two application descriptions at the end of this document. Design an Entity- Relationship model representing the conceptual design of the database, for one of these two applications –your choice which one.
At the minimum, include in your E-R diagram:
- All the entity sets mentioned in the application description,
- A primary key for each entity set and at least those attributes mentioned in the application description,
- All appropriate relationship sets,
- Cardinality and participation constraints of all relationship sets.
As a general rule, your design should have 5 or more entity sets, and a similar number of relationship sets.
Include in your design notes all the assumptions that you make.
IMPORTANT: You must use the E-R notations exactly how they were introduced in this class. Other notations will not be accepted.
What and where to turn in:
- You will turn in a document with your E-R diagram – if hand-written/-drawn, it must be very neat; you will need to submit a scan or picture on Canvas; and design notes.
- Create a Word document HW10_YourLastName.docx (substitute your last name), that includes your E-R diagram and design notes, and submit it using the link Assignment 10 in the module for Chapter 6 (E-R Design).
Application 1. Real-Estate Company
Application Description. The application consists of the operations of a real-estate company. The company needs to keep track of its offices, agents, buyers, sellers, properties on the market, and recently sold properties. This company focuses on homes rather than business real-estate. (To learn more about the application domain, look at some real-estate web sites.).
Offices. The real-estate company has multiple offices, and address and contact information should be recorded for each office.
Persons. For each person, either a buyer, seller, or agent, some basic information needs to be recorded (such as name, address, personal and work phone, etc.)
Agents. For each agent, the company needs to keep track of their license number, commission rate, the date when they became affiliated with the company, office they are associated with.
Buyers and sellers. You may choose to record other information (besides the basic person information) for buyers and sellers, such as required move date (buyers) or proposed vacancy date (sellers). Record who are the agents representing the buyers and sellers.
Properties. Record the address, type, number of rooms, square footage, lot size, number of bedrooms, number of bathrooms, etc. for each property. Keep track of the price the properties are listed for, at different times.
Purchases. Keep track of transaction information, such as the purchase date, the parts involved in the transaction.
Application 2. Automobile Sales
Application Description. The application consists of (some of) the operations of an automobile company, such as General Motors, Ford, Toyota, or Volkswagen. The company needs to keep track of brands, models, vehicles and their options, dealers and customers. Focus on the following aspects of corporate operations:
Brands. The company may have several brands (for example, GM has Chevrolet, Pontiac, Buick, Cadillac, GMC, Saturn, Hummer, Saab, Daewoo, Holden, Vauxhall, and Opel and Volkswagen has Volkswagen, Audi, Lamborghini, Bentley, Bugatti, Skoda, and SEAT). Each brand has at least a name and a logo (text).
Models. Each 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.)
Vehicles. Each vehicle has a vehicle identification number (VIN). Each vehicle has options: we’ll stick to color, engine and transmission. Also record the production date.
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. Dealers keep some cars in inventory. Some, of course, are already sold, but the dealer still keeps track of that fact. You must record the tag price and the price for which a vehicle is sold.
Customers. In reality, lots of demographic data are gathered. We’ll stick to name, address, phone, gender, and annual income for individual buyers. In reality, the customer may also be a company (e.g. Hertz, Avis), but this office focuses on individual clients rather than companies.