Database Design Exercise - Lecture Notes - CIS 211 - Management Information Systems
Today we will engage in hands-on activity of database design relating to CRM (Customer Relationship Management). The instructor will assign groups of two or three people. Once you are sitting with your teammates, work on the following problem.
Your team is in charge of designing a database to help a car dealership engage in CRM. Your database should store data about each vehicle purchase and each service appointment. You must store enough data that an analyst can answer the following questions. It is important to note that your database does not have to answer these questions directly. Instead, your database should store enough data that can be imported into Excel for the analyst to answer the questions.
- How recently did each customer purchase a vehicle?
- How many vehicles has each customer purchased?
- How much money has each customer spent in total on all vehicle purchases?
- How much in profit has each customer provided in total on all vehicle purchases?
- How recently did each customer make a service appointment?
- How long has each service appointment taken (from car drop-off to car pick-up)?
- How can I contact a customer of interest (for prospective marketing campaigns for vehicle purchases or vehicle maintenance)?
It is suggested that you use the following steps.
- Write down all of the data fields you will need to store, including the data type and data size. Use Microsoft Word or plain ol' pen-and-paper for this part.
- Organize the fields into tables (again, using Microsoft Word or paper)
- Determine the relationships among the tables
- Create the database in Access (it is probably a good idea for only one person to work at the computer while others offer feedback). Note any changes that you make from what was originally written down on paper.
If time remains after the exercise, one group will be asked to volunteer (or otherwise be chosen at random) to demonstrate their solution. As teams work, the instructor will move about the classroom to offer guidance and answer questions as teams complete this exercise. Please do not hesitate to ask questions.
