Reference no: EM132800809

Advanced Modeling ~ Bob's Bikes

General Instructions

Read the Case Description below. Once you understand the case start formulating your entities and attributes. You can draw these out on paper as a rough draft and then transfer them to LucidChart. Decide on a primary key for each entity. Determine how your entities relate to one another and draw the relationship lines. If there is something about the case that needs further explanation, please post your questions in the weekly discussion forum. As this case is a little more complicated than some of the cases completed previously, read through the case and ask questions!

Case Description
You have recently taken up the hobby of trail bike riding. You absolutely love to do this. A good friend of yours also loves this sport and works at a local bike shop called Bob's Bikes. This bike shop is unique in that not only does it sell bikes but it also sells repair services. The store doesn't only cater to those who do trail bike riding. They also sell recreational, all terrain bikes as well as racing bikes. Additionally they sell bike accessories. With regards to the services, these range from tire changes to beginning of the year tuning.

Your friend mentions that they currently has a POS (Point Of Sale) system but it is really old and not mobile. Often the inventory levels are not calculated correctly and they run out of products to sell. Customers call wanting a specific product, are told that the product is in stock but the product ends up not being there. This causes frustration for all that are involved.

Your friend arranges for a meeting between you and Bob to discuss the redesign of the current system and or the possible development of a new system. Bob is pretty excited about this and you all agree to a time to meet. Realizing that this project will probably be bigger than you can handle, you contact a few of your friends from school to see if they would be interested in producing a system that Bob can use.

At your meeting Bob explains what they sell and the types of customers they cater to. Bob's customers range from novice to advanced. He says that he has people who come in looking for a quality bike to ride around the neighborhood and occasionally on a trail to those who race in competitions throughout the state. Additionally, once people buy a bike from him, they often come back year to year for various maintenance tasks, such as checking the tire pressure, filling the tires, checking the gears and brakes and oiling the chains. These services constitute a third of his overall sales. Bob then mentions how he would like the application to be available on Samsung tablets so that his employees can reference stock data and the service schedule while they are actively talking with a customer rather than going to the cashier counter.

As with any new project, you ask Bob for Invoice information. On the invoice you notice that he lists the products he sells (bikes, tires, helmets, etc.) and the services. With regards to services, the invoice details, who performed the service and the cost. You also notice that the invoice contains the date, customer name and phone number and the name of the employee who helped the customer out.

You then begin discussing the particulars about the products and services and what specifically Bob wants to keep track of. He summarizes the data requirements to be the following:
• Customers: contact data (first name, last name, address, city, etc.)
• Products: This includes bikes, accessories and parts. The database needs to store the name of the product, its description, retail price and wholesale price. It also needs to track who the vendor is.
• Vendors: contact data and the main person who is called or consulted.
• Inventory: the product, how much is currently in stock and the reorder number.
• Orders: order date, employee who took the order, the customer and items on the order. If the item on the order is a service, Bob wants to list who did the service and the promise date on when that service was to be performed.
• Employees: contact data
• Services: the name of the service, the number of hours it should take to perform the service and the per hour rate.
You end the meeting with a discussion of reports. Bob would like the following:
• The ability to run daily, weekly, monthly, quarterly and yearly sales reports, broken down by products and services.
• A weekly report showing who performed what services, the time it took to perform the service versus the time it should have taken to perform the service. Bob wants to make sure that when they quote a customer for how long it will take to finish a service is accurate.
• A daily inventory report that shows quantity on hand, reorder amount and the difference between inventory on hand and reorder amount.
• A report that shows the products they offer and the vendors who supply those products.
• A report that shows products in the store that have not sold.

After your meeting you gather your materials and your notes and head off to the local Starbucks to have a meeting with your team. You then decide on who is doing what and agree to when you will next get together to discuss the project.

Finishing Up

1) Now that you have read the case, start composing your entities and the attributes.

2) Assign a primary key to each entity.

3) Determine which entities relate to one another and place your foreign keys appropriately.

4) Determine the type of relationship between your entities and draw those in.

5) Read the rubric to make sure you have included everything. Go back and double check your model. Make sure all many to many relationships are properly broken down to one to many. Make you're your attributes are attributes and not data.

6) This diagram needs to be readable. What do I mean by ‘readable'? The normal lay person should be able to understand what you are trying to do. Are you lines convoluted? Is it hard to trace how one entity relates to the other? Have you adequately described each entity? Does the diagram fit on one page? Does your diagram have your name on it?

7) Make sure your diagram can fit on one page. Export your diagram to a PDF file.

8) Upload your PDF file to to Canvas.

9) Points will be deducted for models that are sloppy, do not have a name on them or do not fit on one page.

Attachment:- Advanced Modeling.rar