Conceptual Design (75 pts)
For this assignment, you have been asked to design a database using only the written problem description provided by the client. In reviewing the description, you may find that information may be missing or that not all information described needs to be stored or represented in the database. One skill of the designer is determining what needs to be represented in the database model and what does not need to be included.
Your task is to design the entity-relationship diagram for the conceptual design of the FastCabs company database. The description of FastCabs is given below.
Instructions
1. The diagram must be prepared using a UML class diagram. The use of Visio is encouraged but any drawing program that can produce the required diagram is allowed.
2. The diagram must contain the following:
a. Entities with appropriate names.
b. Attributes for the entities with the primary key of each entity identified.
c. Relationships with multiplicity shown in the [min .. max] format (as discussed in class).
d. Attributes for relationships as needed.
e. Higher-degree relationships as needed.
f. Superclass/subclass hierarchies as needed.
3. You may add relevant attributes beyond those listed in the problem description, but do not attempt to model the "real world" in your diagram. This is a simplified problem to enable you to complete the assignment within the time constraints of the course.
4. State any assumptions you make about the problem, especially those required for correct interpretation of your diagram.
Hints & Tips
• Remember that there are many correct ways to build the diagram that models the problem. Your diagram is not expected to be identical to those of other learners.
• The problem is somewhat "under-specified," and your interpretation and assumptions may be different from those of other learners. Just be sure to state your assumptions to assist/clarify the interpretation of your diagram.
• Remember that your diagram should not contain any foreign key attributes within an entity. You will designate foreign keys when you build the logical (relational) data model.
What to Submit
Options: Choose one
• Submit the Visio file (.vsd) readable by Visio 2007. Provide a separate document with the list of assumptions.
• Copy the diagram into a Word document that also contains your list of assumptions.
• Submit both a Word document with a copy of the diagram and your list if assumptions plus the Visio file.
FastCabs Company Description
Adapted from Connolly, T., Begg, C. and Holowczak, R. (2008). Business Database Systems. Harlow, England: Addison-Wesley. P. 206.
Each office of FastCabs has a manager, several taxi owners, drivers and administrative staff. The manager is responsible for the day-to-day operation of the office. An owner provides one or more taxis to FastCabs, and each taxi is allocated for use to number of drivers. The majority of owners are also drivers. FastCabs taxis are not available for hire by the public hailing a taxi in the street but must be requested by first phoning the company to request a taxi at a given address at a designated day and time. There are two kinds of clients, namely private clients and businesses. Private clients request a taxi whenever one is needed on an ad hoc basis. The details of private clients are collected when the first booking (or reservation) is made. The business clients agree to a contract of work between the business and FastCabs. This is a formal contract the specifies the number of jobs (trips) that FastCabs will provide for a fixed fee. The fee may be determined as a single fee for a given number of trips with a fixed fee per trip for each trip after the minimum number of trips have completed. When a job comes into FastCabs the name, phone number, and contract number (when appropriate) of the client are taken. The number of passengers and any request for a specific type of vehicle are also recorded. The pick-up date/time and pick-up/drop-off addresses are noted. Each job/trip is allocated a unique jobID. The nearest driver to the pick-up address is called by radio and is informed of the details of the job. When a job is completed, the driver notes the mileage used. For private clients, the fee to be charged for the trip is also noted. If a job is not completed, the reason for the failed job is noted. FastCabs provides several types of vehicles for clients: limousines for large groups, vans for groups with luggage, and passenger cars. Vehicles used for trips into the mountains are equipped with 4-wheel-drive.
Logical Design (75pts)
Prepare a document containing the logical design based on your FastCabs conceptual database design.
Instructions:
1. The logical design must be presented in a list format using MSExcel
2. Each table must have a name; provide a meaningful name of each new table you create as part of the logical design process.
3. Underline the primary key for each table.
4. Use the * to identify each foreign key attribute.
5. If you have used a superclass/subclass hierarchy, choose an appropriate option for mapping the hierarchy to tables.
6. Submit a copy of your logical design. You may make changes to your conceptual design if desired. Submit your logical design to the assignment.
Your logical design will be evaluated based upon how correctly your logical design reflects your ER diagram.