System Management Problem

Objective: Solve a problem in the operations of a small business by creating a small database.
Design, Model (using ERD) and implement a simple relational database related to the solution of the business problem and implement it in MS-Access.
Get familiar with the various components (such as query, forms, reports, etc.) of the MS-Access software by actually developing each one of them in the implemented database
Provide a 2-3 page memo justifying the use of database in solving the business problem. The memo should list at least 4 managerial issues to pay attention to in implementing database solution to the problem.

Assignment: Based on the scenario described in the case CaRent Inc. create a relational database using MS-Access that will help the owners in keeping track of the business information easily and answer the following queries:
a. Rental history of the customers that should include the first and last name of each customer, his/her driver license numbers, the license plate of the car that was rented, the date of rent, date of return, rental rate and total amount of revenue from each rental.
b. Rental history of each car including the rental dates, miles driven, daily rate and the total rental amount from each car.
c. The amount of business (in sales) provided by each type of car (compact, sub-compact, etc.).
d. The amount of business provided by each type of referral agencies (Hotels, Travel Agencies, Tour Operators, etc.).
e. A list of customers with their preferred car type, frequent flyer number, addresses and the direction for drop-off and pick-ups.
f. The maintenance history of each of the cars.
Determine the business advantage for the company in using the database. Can Internet be of any help to CaRent? How and what is the role of database in that area?

An ERD of the database design and a relational database in MS-Access through MyLMUConnect. The database should have at least the following:
1. Tables that can be used in finding the information asked above. Each Primary Table must contain at least 3-5 records (not fields, records).
2. There should be multiple records in the joining tables
3. Input Form for each of the Tables.
4. Queries that will provide the information asked above using the data in the Tables.
5. Two Reports created in Access based on the Queries, one for the customers’ account that will show the summary of their rental history including the total business (in dollars) obtained from them and the coupons that they used (to be used later for targeting promotions), and another one that would provide a list of customers with their preferred car type, frequent flyer number, and addresses for drop-offs and pick-ups.
6. An ERD with all the entities, their attributes and the relationships between them along with the cardinalities and modalities of the relationships.

You also have to turn in the following:
i. A written memo (separate from the above reports) addressed to Peter that details the business advantage that the company can gain from using the database, and the business and technical issues that the company may face.
· You should explain the advantages of the database and
· The hardware, software, personnel and other requirements for the implementation of the database.
· The security issues associated with database implementation
ii. Based on your analysis of the case in Assignment 1 and the SIPOC chart that you created, suggest other tables/queries that you think should go into the database to help Peter and improvement on the design of the database (you do not have to create the tables, just suggest). Include those suggestions in your memo.

Guidelines: Try to keep the design of the database simple! Create the ERD first and decide on the attributes of each of the entities. To determine the attributes, think of the data items that may be required for getting the information requested by the assignment and then design the database. Remember that some of these data items may belong to a single entity (such as customer name, address, etc.) while some may belong to multiple entities (e.g. the rental record that belongs to a customer and the car he/she rents).
Proofread your memo and make sure it is free of any typographical and grammatical errors. Lack of quality will cost you valuable points in the assignment. Memo is worth 3% of the grade and the database is 4% (total 7%).
Suggestions: Start early and start first by designing the ERD on paper. Input some dummy information and see how you would have used the Tables for answering the questions asked in the assignment. Start building the database in MS-Access only after you get a good understanding of the steps and the processes involved.

· Start with the design of the database first. Do not start with the queries, you will get confused.

· Identify the entities. Some examples are: Customers, Cars, Referral Agencies, etc.

· Identify the attributes of each of the entities. Now you should look at the final query requirement and make sure that you are including the attributes that will be required in the final queries. DO NOT INCLUDE ANY ATTRIBUTE THAT CAN BE CALCULATED FROM OTEHRS. YOU WILL CREATE THEM IN THE QUERIES USING THE EXPRESSION BUILDER.

· Identify the relationship between the entities. For example, what is the relationship between the customers and the cars? Can one customer rent multiple cars (on different rental dates)? Can one car be rented by multiple customers?

· Follow the design principles to create the links and the intermediate tables required to link the tables in the database. This may be a good time to also include the joint fields in the intermediate tables. A joint field is something that is decided by multiple entities. For example, the rental date on a car is decided by a customer and a car (a rental date is the date when a CUSTOMER rents a CAR).

· Find the answer to the following questions:

o Can one car be rented by multiple customers at different point of time?

o Can one car go through multiple maintenances? If yes, then how would you capture that?

o Each car has only one assigned maintenance agency, but each maintenance agency can service multiple cars. What does that mean in terms of placing the foreign key?

o Can the miles driven be calculated from rental records? How?

o If you need multiple intermediate tables to connect various entities, can one intermediate table with multiple connections serve that purpose. For example, rental table can not only connect the cars and the customers, it can also connect coupons used by the customers because coupons are used only during rentals.

o What determines the rental rate of a car, is it the car itself (identified by the License Plate Number) or the type (such as compact, full size, etc.)? If the answer is the later, then what does it mean in terms of your design (may be you would need a separate table to keep your car types and their corresponding rates!).

o What are the relationships between the various marketing channels (outside referrals, e-mail and yellow pages) and the customers? You can assume that a customer can use the same marketing channel multiple times and a customer may decide to use no marketing channel at all.

o Coupons are connected to which entity? Are they connected to customers directly? Can customer use the coupons without actually renting a car?

· Please complete the design on paper first before going to Access. You can send me the ERD (MyLMUConnect, e-mail, fax, etc.) before you begin so that I can suggest corrections. That would save you a lot of time and headache.

· Cars and Customers have many to many relationship because each car can be rented by multiple customers (at different point of time) and each customer can rent many cars at different point of time. Thus they will need an intermediate table. If this is the rental table, then what is the relationship between this table and the coupons? Can a rental use multiple coupons (answer is no)? Can a coupon be used by multiple rentals (yes)?

· Assume that coupons are just percentage discount provided and there are no restrictions on using them (i.e. no minimum rentals, no blackout dates, etc.). That would make it easier for you to calculate the final rental amount.

· In your coupon table, you have to create a coupon with 0% discount to make sure that your queries return the correct values when no coupon is applied to a rental. Similarly, you have to have a marketing channel called “none” to accommodate rentals that did not come through any marketing channel.

· If you do the design correctly, you should be able to implement the database with 7 tables (including the intermediate ones. One of the possible queries that you should be able get from the database is shown below.

· You have to make up the records.

· Once satisfied with the design, go to Access, implement the tables, connect all the tables, enter the data, create the queries and the reports, write your memos, and you are done!

Have Fun!!

Example of a Query.

Customers and their Rentals

First Name
Last Name
License Plate
Date Rented
Date Returned
Rental Rate
2VHN 717
E 320
5-MAR, 2016
8-MAR, 2016
2KXC 818
2-JAN, 2016
10-JAN, 2016

CaRent Inc.[1]

Peter relaxed by his swimming pool sipping the ice-cold frapp. Earlier in the day, he had accepted the position of Vice President of Sales and Marketing at CaRent Inc. He is pleased with his decision because he now has the opportunity to manage a small business to grow into a national chain. Peter knows that it will not be easy, but he is ready to face the challenge. As he watched a couple of squirrels play on a tree next to the pool, he began to think about the challenges at CaRent and what he would have to accomplish as a new VP.

CaRent is a small car rental company in Los Angeles that specializes in renting within a city. It is modeled after Enterprise Rent a Car, where the customer is picked up and dropped off between their offices/homes and the renal office free of charge. The rate is very competitive and the cars, though small in numbers, are kept in excellent condition. Since its modest beginning, the company has seen the business growing steadily. Its customers vary from individual renters to large corporations.

One of the critical success factors of CaRent is its excellent customer service and its ability to keep its vehicles in top-notch condition. CaRent sees itself as a serious future competitor of Enterprise Rent a Car by satisfying the “Customers’ Moment of Value” by delivering the exact product at the correct place at the time it is needed. So far things are working out, but some problems have started to show their ugly heads. As the customer base is growing, the company is facing a new problem, and that is of keeping information and inventory in order. Customers are being wrongly informed about the availability of a particular car type (which results in unnecessary free upgrades to a better car and thus loss of revenue), promotions are not targeted well, repeat customers are being asked the same questions about their addresses for drop-off/pick-up locations, and many customers are not getting their frequent flyer miles that they are supposed to get for renting with CaRent (CaRent has an agreement with Ameta Airlines for crediting 500 miles to the frequent flyer account for every day of rental). The maintenance records on the cars are getting mixed up as well resulting in delays in meeting the regular maintenance schedules on many cars.

The promotional department of CaRent works with the local hotels, travel agencies and tour operators to get the business and a fixed percentage commission (5% of the revenue) is paid for each referral. CaRent also gets a lot of business through their e-mail marketing and yellow page advertising. CaRent also does mass-mailing of coupons containing various discounts (5%, 10% and 15%). These promotions are not targeted at all and CaRent feels that the ROI on the promotions are rather low. It needs a system to identify the business coming from each marketing effort and better target the promotions.

Currently, the entire record keeping is done on paper at CaRent. Given the size of the company and the operation, it is manageable, though the signs of strains have started showing. Peter is afraid that the future growth of the company will make the operation completely chaotic if an electronic record keeping system is not put into place. He ponders about the various avenues as he puts on some more suntan lotion and suddenly remembers the conversation that he had the other day with the new intern, an AIMS major from LMU, and realizes that help may come from that direction. Needless to say, you are that person and you have to show your brilliance to Peter to help him and secure a position in this new growing company! How would you go about doing that?

Assume that the company currently has only two computers, one is used by the company secretary and the other one is used at the rental desk for printing the rental agreements. The computers are networked through a basic home networking Netgear router. The office has high speed connection for accessing the Internet. The computers are relatively new and are quite powerful in terms of processing, memory, storage etc. Peter wants to have a plan from you that can help the company. The plan should present a prototype of the proposed solution, explain the advantages of it, should include a rough budget, and should discuss the managerial issues that should be kept in mind in implementation of the plan including the future changes that may be required.

Leave a Reply