Management and information system help

LOW BUDGET RENTALS DATA MODELING ASSIGNMENT

MGMT 4013 TC1, Spring 2017

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. You can also use the examples from the textbook and the MySongsNow Tegrity videos in Blackboard to help you build this data model.

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 to a nearby regional airport. He has a large fleet of vehicles with a wide variety of models of cars, vans, and SUVs that visitors rent while they are in the area and seeing the sights. 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. You realize he needs to keep track of rental transactions as well as keep track of the vehicles his company rents. 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 considering some sort of loyalty plan to increase repeat business and hopefully thereby increase the company’s profit. He would like to know which customers he should offer this deal. You realize from this that he needs to be keeping track of his customers, too.

Roger also expressed 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 remember from your MIS course that whenever you see multiple themes, you need a database rather than a spreadsheet. In other words, if you want to keep track of several different types of things, some of which are related to each other, you really need a database rather than a spreadsheet. Your task is to create a simple data model for a database that will enable Roger to easily produce the reports that will answer his questions. This approach will also allow him to create other useful queries in the future.

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 of the Word document below. You can use this data to help you create the data model, but you will need to correct its structure.

Because of how the data is stored, Roger has trouble producing a complete list of vehicles and their current condition. He does have a number of vehicles that are the same model and year. He would also like to produce a list of customers, their contact information, and their preferred vehicle models.

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. 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.

Use this scenario, material in your textbook, and the Tegrity videos MySongs Parts 1 and 2 to guide you. For your convenience, I have included the steps in the process of creating a data model at the end of this document. 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 and relationships your scenario requires. (MySongs Part 2 has some tips for converting the data model into a database, but that information pertains to the next assignment.)

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. I recommend use of shapes if you go this route. 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. 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; [email protected]).

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 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.

Save your file with a name that follows the conventions for this course (examples: TroboyKimLBR.jpg, or other graphic file type, TroboyKimLBR.docx, or TroboyKimLBR.vsdx). Submit your file at the link in this folder in the Assignments area of Blackboard by the due date listed on the Course Schedule.

Sample Data

This data is meant to give you an idea of what attributes the objects in your data model need and what the properties those attributes should have. Sample employee data is below. Check the attached spreadsheet for information the company keeps on vehicles, customers, and rental transactions.

NOTE: This assignment requires you to create a data model only. Do not create or submit an Access database for this assignment.

Data Model Process

This is a summary of the process for creating a data model. See the MySongsNow Parts 1 and 2 Tegrity videos, in which I walk you through this process. The PowerPoint file developed in the videos is also posted in this folder. A data model is a representation of real things the organization needs to track.

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 by creating 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 (number of characters for text attributes, the number type for numeric fields like integer, long integer, single precision, double precision, currency, etc.)

· Required value … whether a value in the attribute is required or optional when entering a new record for this object. This is a common sense technique used to make sure necessary data gets recorded in the database and thereby help to ensure the integrity of the database. For example, the ‘required’ property for the student T number field in the ATU student database is set to ‘yes’. That means we cannot create a record for a new student if the T number field is blank. For an example closer to your scenario, say that the Customer Last Name attribute of the Customer object has the ‘required’ property set to ‘yes’. In that case once the data model is implemented in a database, they would not be able to add a record for a new customer without entering in a value for last name.

4. Identify an attribute in each entity, which can act as a unique identifier (eventually a primary key field 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 must have a field in common. If a common field does not exist, place an identifier attribute of one entity into the other entity as an additional attribute. In a database, this means put the primary key field of one table into the other table where it is referred to as a foreign key.

7. Describe the properties of each relationship. These properties address minimum/maximum cardinality: mandatory/optional for minimum and one-to-one (1:1), one-to-many (1:M), or many-to-many (N:M) for maximum.

8. Normalize the data model.

· Remove any many-to-many (N:M) relationships by adding an intersection entity. This intersection entity consists of at least the identifier attributes of each of the related entities. In a database, this would mean an intersection table consisting of at least the primary keys of the two tables that have the many-to-many relationship. It may make sense to include other attributes in this intersection entity. Consider whether this intersection entity represents something in the real world that you really do want to keep track of. If so, then the intersection entity will likely have a more meaningful name and more than those two attributes. You will easily see what other attributes need to be included based on the real world item you realize the intersection entity represents. Remember that you need to designate a unique identifier for each entity (what will become a primary key in a database table). In this case, you may need more than one attribute. That is, you may need to combine two or more attributes to get a unique value for each instance of this entity. To state this idea in terms of a database, you may need to combine two or more fields to create a primary key that will uniquely identify each record in the table. We call this a composite key in a database. I will explain in the next assignment how to accomplish this in Access.

· Remove any derived attributes. Derived attributes are those whose value can be figured or otherwise established from the values of other attributes. For example, in a sales transaction, customers may by several of a particular item. If they buy three tubes of the same toothpaste, the total due for that item can be calculated by multiplying the number of tubes by the price of each tube. In the sales transaction table, we just record one tube, its price, and the number of tubes bought. We don’t include the total due for the three tubes of toothpaste in the sales transaction record, as that can be calculated any time we need it. GPA is another example. We do not store your GPA in your student record. We store the total earned hours (3 earned hours for each 3-credit course, for example) and total earned quality points (4 for each ‘A’, 3 for each ‘B’, and so forth). If we need to display or print your GPA, the system just calculates it on the spot from those two fields.

· Make sure all attributes are in the right entity. Sometimes, in the heat of designing the data model, an attribute is mistakenly placed in the wrong entity. Consider whether each attribute belongs directly to its entity or really belongs to another one. One good clue is to consider whether the attribute relates directly to its entity key. 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 entity. Really, the vendor should be in the product inventory entity, as vendor better describes the product than it does the sales transaction.