LOW BUDGET RENTALS DATA MODELING ASSIGNMENT

MGMT 4013

Your assignment is to create a data model based on the scenario and data below. For your convenience, I have included the steps in the process of creating a data model at the end of this document.

Roger is the manager of a car rental company in an area of the country that has many tourist destinations and attracts many visitors who fly into the area at a nearby regional airport. He has a large fleet of vehicles with a wide variety of models of cars, vans, and SUVs to rent to those visitors. He thinks his customers typically rent a vehicle for a long weekend stay, week-long conferences, and two-week vacations. He has been keeping records for his business in Word documents and Excel spreadsheets. He has heard that he could learn more about the patterns in his business if these records were stored in a database. You are interning with his company, and he thinks your up-to-date technology skills would be very useful on this project.

Roger tells you that he will be making his fleet composition decisions soon. He wonders whether he should adjust the mix of models that he has to offer customers. His comment and question causes you realize that he needs to keep track of rental transactions as well as vehicles. You understand you need to determine which models are rented most often and which models result in the most profit overall. Calculating profit means that you will need some cost figures.

Roger is also considering some sort of loyalty plan to increase repeat business and thereby increase the company’s revenue. He would like to know which customers he should offer this deal. You realize he needs to be keeping track of his customers, too.

Roger also expresses some curiosity about how productive his employees are. He is aware that some of them process more rental agreements than others and some are more persuasive at convincing customers to buy additional insurance with their rental. You realize he needs to keep track of employees.

You remember from your MIS course that whenever you see multiple themes, you need a database rather than a spreadsheet. Your task in this project is to create a data model. (Eventually, you will use this model to implement a database that will enable Roger to easily produce the reports to answer his questions. But creating a database will be for the next project.)

Currently, Roger has records of vehicles, customer, and rentals in a spreadsheet. He has information about employees in a Word document. See the attached spreadsheet and screen shot sample below. You can use this data to help you discover what your data model needs to include.

You know you will need certain business rules and management policies to complete a good data model. So, you ask him several questions about how his business operates. He tells you that sometimes a customer will rent more than one vehicle at a time. This usually happens when the customer has a large family or the customer is a company sending several employees to a conference in the area. His employees have double booked a vehicle on several occasions, resulting in very angry customers. Obviously, a vehicle should not be rented to more than one customer at a time. This has happened because he does not have centralized vehicle rental database. He is determined to keep this from happening again.

Roger does generally have an average cost per mile value for most vehicles in his spreadsheet. This cost reflects maintenance, aging, insurance, and other operational costs for every mile driven on that vehicle. He can use this value to calculate the cost to his company for each rental. He is hoping you can use that ratio to help estimate profit on each rental.

Sample Data

See attached spreadsheet for vehicle, customer, and rental data.

Project Requirements

Use this scenario, material in your textbook, and the Tegrity videos MySongs Parts 1 and 2 to guide you in creating a data model that incorporates E/R diagramming techniques. The textbook and the videos have a different scenario, but the principles are the same. You will need to adapt those examples to the data model for your scenario and complete the process for all of the entities, attributes, and relationships your scenario requires.

You have three options listed below for recording your data model in a digital format to submit at a link the Assignments area of Blackboard. My advice is to create your first model in sloppy form with paper and pencil. When you have it worked out, then transfer your data model into one of the formats below.

1. Use paper and pencil. Your diagram must be neatly drawn, well labeled, and easy to read. Take a very clear photo of the data model or two photos if your model is on two pages. If my assistant and I can’t read something, you won’t get credit for that part of the diagram. Save the photo as a graphic file (.jpg, .png, etc.) with a file name following the rules for this course (see example below).

2. Use Word (five bonus points). I recommend use of Shapes. You will find typical blocks and lines in the Shapes area of the menu under the Insert ribbon tab. Add the object attributes and their properties (data type, length, required or not) inside the block designated for each object (don’t forget to label the primary key attribute).

Add connecting lines between objects that are directly related. Add the lines and circles for the E/R relationship attributes (mandatory/optional, cardinality) on top of the connection lines. It works best when you group the E/R symbols and place that group in the foreground and the connection line in the background.

3. Use Visio (10 bonus points). I will award up to 10 extra credit points for using Visio because you will probably be learning a software package that is new to you. (Students who took my Business Process Management course last spring will already know how to use it.)

Visio is available in all lab computers, remotely on the ATU Virtual Desktop (for Apple computers), and can be downloaded free from Microsoft Imagine (contact Susan Morris, the Dean’s secretary, to get ID/password credentials and the URL of ATU’s portal site; smorris@atu.edu).

Visio has stencils for E/R diagrams with all the blocks, connection lines, and E/R relationship attribute symbols needed. The object blocks have the ability to add all the attributes needed and the required properties needed for each attribute (name, data type, length, required or not). The connection lines have the crow’s-foot and other designations for recording relationship properties in an E/R diagram. There are some extra resources for using Visio in the Data Model folder in the Assignments area of Blackboard and in the Software Application Resources folder.

No matter which of these options you choose, make sure to put the usual identifying information (your name, course and section number, date) in a box in the upper right-hand or upper left-hand corner of the data model. If you use paper and pencil, write this the information clearly. In Word or Visio, insert a text box to add this information.

Data Model Process

For reference, here is the process to use in creating a data model. See the Tegrity video in Blackboard for an example. The PowerPoint file developed in the video is also posted in this folder.

1. Identify all entities the manager wants to keep track of. Typical entities are persons, things, places, and events (like transactions).

2. Describe each entity. Create a list of attributes that describe each entity. These attributes will become fields or columns in your database tables. Just include attributes necessary to this scenario (just the data you will want to keep track of).

3. Determine the properties for each attribute:

· Data type (text, numeric, date, etc.)

· Length or size. This is the number of characters for text attributes, the type of number for numeric fields (like integer or long integer for values without decimals, single or double precision for values with decimals, currency, etc.)

· Required. This refers to whether a value in the attribute is required or optional when a record is created. (That is, if you say a value is required, you cannot create a new record for that entity without entering a value for this attribute. For example, once the data model is implemented in a database, if the ‘required’ property is set to yes for the last name field, you would not be able to create a record for a new student without entering in a value for last name.)

4. Identify an attribute in each entity that can act as an identifier (eventually a primary key in a table in a database).

5. Identify any direct relationships that might exist between various pairs of entities.

6. Make sure you can actually link any related entities. This means they will have one attribute in common. Typically, we place the identifier attribute (primary key) of one entity into the other entity as an additional attribute (where it becomes a foreign key). In MySongsNow, the Sales Transaction entity has Customer ID as an attribute (who the song was sold to). Customer ID is the primary key of the Customer entity but it is a foreign key in the Sales Transaction entity. Having Customer ID in both entities will eventually allow the database to connect those two tables.

7. Describe the properties of each relationship. This involves establishing minimum/maximum cardinality; i.e., mandatory/optional and one-to-one (1:1), one-to-many (1:M), or many-to-many (N:M).

8. Normalize the data model.

· Remove any many-to-many (N:M) relationships by adding an intersection entity consisting of at least the primary keys of both original entities. (These two attributes combine into what is called a composite key.) Consider whether this intersection represents something in the real world that you do want to keep track of. If it does, name the intersection entity appropriately and add any necessary attributes.

· Remove any derived attributes. Those are attributes whose value can be figured or otherwise established from the values of other attributes. (For example, in a sales transaction, total price for an item can always be calculated by multiplying the number bought by the price. You can calculate your GPA by using total earned hours and total quality points.)

· Make sure all attributes are in the right entity. Sometimes, in the heat of designing the data model, an attribute that really belongs to a particular entity is thought of when considering a different attribute and gets added there. If an attribute does not directly describe the entity in which it is located, it needs to be moved to the attribute it does describe. (For example, you might have listed the vendor of a product in a sales transaction. Really, the vendor should be listed in the product inventory table, as it describes the product, not a sales transaction.)