Business Data Analysis, Entity Identification and E R Diagram

Learning Objectives

• Model Business Data
• Complete E-R Diagram

Microsoft Visio

• Required Software: Microsoft Visio  (Links to an external site.)
• Visio Resources: A beginner’s guide to Visio (Links to an external site.),  (Links to an external site.)Microsoft Visio Tutorials (Links to an external site.)

Instructions

Lab homework:

Sarahs Shortcakes

Before attempting this lab, review the previous zoom session.

In this lab, analyze the above case study to determine the entities required for your database, the attributes of each entity, and the relationships required between the entities.  Then construct a data dictionary describing each entity and the relationships between entities.  After the data dictionary is complete, use it to create an E-R diagram in Microsoft Visio. 

Mimic the previous zoom session to analyze Sarah’s Shortcakes and create a data dictionary and E-R diagram. 

You will not be able to complete the next lab without finishing this one. 

Deliverables

1. Complete your data dictionary in Microsoft Word.
2. Complete your E-R diagram in Microsoft Visio then export to PDF.
3. To complete this lab, upload these two files

Previous

Next

ATTACHMENT 2 IS JUST FOR REFERENCE SO THAT U DONT MISS ON THOSE POINTS 

I NEED DICTIONARY AND ER DIAGRAM FOR 2 POEPLE. BOTH NEEDS TO BE DIFFERENT

Journalof the International Academy for Case Studies Volume 23, Number 1, 2017

25

SYSTEMS ANALYSIS, DESIGN, AND DEVELOPMENT

CASE STUDY:

SARAH’S SHORT CAKES – SALES ORDER SYSTEM

Terry L. Fox, University of Mary Hardin-Baylor

CASE DESCRIPTION

The primary purpose of this case study is for Systems Analysis and Design, Systems

Development, and Database courses. Students examine realistic dialog and Interview Notes, as

well as existing documents. For Systems Analysis and Design courses, the students should be

able to follow this realistic and fairly common case study of a small business and conduct the

planning, analysis, and design phases of the System Development Life Cycle (SDLC), using

either a traditional or object-oriented approach. Deliverables would include process and data

diagrams and modeling, and user interface designs, and should require approximately 12-15

hours to complete, outside normal class time. In System Development courses, e.g., capstone

courses for a computer information systems major, students can use this case study to not only

analyze and design a solution, but actually develop the solution using various windows or web-

based tools. The entire project should require approximately 20-25 hours to complete. For

Database courses, this case could be used to illustrate database design techniques, resulting in

the creation of appropriate data models and physical database designs. This should require

approximately 10-12 hours to complete. The case study is of moderate difficulty – ranging from a

three to five, and is designed for junior and senior level students, but could also be used for

graduate courses.

CASE SYNOPSIS

Dr. Thomas Waggoner, an information systems professor at the local university, is at a

small bakery waiting to pick up cupcakes for his daughter’s birthday party. The lengthy and

unorganized approach to waiting on customers presented Dr. Waggoner with an idea which he

shared with the owner of the bakery. His students could design and build a system to help track

sales orders, and hopefully help the business become more efficient. Sarah, the owner of the

bakery, was very excited about the possibilities, and they decided to meet later in the week to

discuss the details.

INTRODUCTION

Dr. Thomas Waggoner was number 43. The sign said currently serving number 34. All he

needed to do was pick up the three dozen cupcakes his wife had ordered for their daughter’s

birthday party, which was going to start in just under an hour. As Dr. Waggoner waited semi-

patiently, fortunately (for him, but unfortunately for the store) one by one several potential

customers got tired of waiting and left. Unfortunately, as tasty and expertly designed as Sarah’s

cupcakes were, there was a significant problem with keeping everything organized. Ten minutes

later, Dr. Waggoner’s number was finally called. After the clerk retrieved his cupcakes, Dr.

Waggoner decided to ask if the seeming disorganization was normal.

Journal of the International Academy for Case Studies Volume 23, Number 1, 2017

26

“Is it always this busy?”

“Most days, yes. When the owner, Sarah, started the business about a year ago, she was

worried whether we would have enough customers to make it work. Now, we are worried about

whether we will be able to handle all the customers we have. We currently bake and sell between

80 and 100 dozen cupcakes each day. We can handle the daily sales – no problem. Every

morning we make several batches of cupcakes in different flavors and with different colors of

icing. These go in the display cases. We almost always sell these by the end of the day. The

bigger problem is trying to keep track of the different custom orders, such as yours. And, on top

of that, Sarah wants to open a second location sometime next year. I am not sure how we are

going to manage all that, but fortunately that is Sarah’s concern, not mine!”

Dr. Waggoner decided to probe a little deeper, although he was also aware of the time

and pending party. Since the store was actually void of customers at the moment, he asked,

“Do you have a system to help you track the orders? I actually teach several computer

systems courses at the university, and I might be able to help.”

At that moment, the owner of Sarah’s Short Cakes walked in from the back room and

answered Dr. Waggoner’s question.

“The answer to your question is, ‘Not really’. The answer to your offer is ‘Great!’”

interjected Sarah. “I suppose have a ‘system’ but it is stored in my head. We write down the

details on a sales order form so we get the orders correct, but we sometimes have trouble keeping

track of how many orders we have promised for a particular day, and also in having enough of

the right ingredients on hand.”

“I have to get these cupcakes home, but would you like to get together later this week so I

can get a better understanding of your business? This could make a good project for my

students”

“That would be great! Thanks, and tell your daughter Happy Birthday!”

Dr. Waggoner met with Sarah later that week to start understanding how her business

operated (and enjoy a free cupcake!). He then asked a group of his students to contact Sarah to

meet with her and develop a list of detailed requirements for a new system to help keep track of

custom orders and ingredients inventory. The detailed requirements are listed below.

Journal of the International Academy for Case Studies Volume 23, Number 1, 2017

27

Sarah’s Short Cakes: Detailed Requirements

Custom Sales Orders

When a customer places a custom cupcake order, Sarah or one of her employees will take the

customer’s name (first and last), email address, and cell phone number. For an additional fee, cupcakes

can be delivered. For those orders, the street address and city is also recorded. Once a customer is in the

system, they will not be required to submit the information again, unless something has changed. A

customer can (and hopefully will!) place many orders over time.

On the sales order, the number of cupcakes required, the flavor of the cupcake, the flavor of the

icing, and the color of the cupcake and icing (depending on flavor) are all recorded. In addition, if there

are any special decorations requested (e.g., princesses, horses), they will be noted on the order. Customers

can order as many cupcakes as they would like, and each cupcake can be different (flavor, color,

decoration). Thus, a one-to-many relationship must exist between the order and the cupcake(s) line item.

Currently, Sarah’s Shortcakes offer the following flavors of cupcakes and icing, but are regularly adding

more flavors:

Cupcakes Icing

Vanilla Vanilla

French Vanilla Butter Cream

Chocolate Lemon

White Chocolate Cream Cheese

Double Chocolate Cookie Crumbs

S’mores Strawberry

Strawberry Peanut Butter

Peanut Butter Oreo

Pumpkin Spice Champagne

Blueberry Mint Chocolate

Apple Cinnamon German Chocolate

Cookies and Cream Espresso

Champagne Pink Lemonade

Chocolate Chip Cheesecake Maple Bacon

Mississippi Mud Dark Chocolate

Red Velvet Milk Chocolate

Coconut Coconut Pecan

If the customer chooses vanilla, white chocolate, or champagne flavored cupcakes, they can also

select a custom color, such as blue, purple, green, red, orange, or yellow for their cupcake. If the customer

chooses vanilla, butter cream, cream cheese, or champagne icing, they can also select a custom color

(same as cupcake colors) for their icing.

The sales order will also note the date of the order, whether the order is to be delivered or whether

the customer will pick it up, the date and time the order is needed, and any notes that might be needed to

help explain the order. If the cupcakes are to be delivered, the time needed is the time the cupcakes are to

be delivered. If the cupcakes are to be picked up, the time needed is when the customer expects to arrive

to pick up their order.

The system must provide appropriate and user-friendly data entry forms to maintain customer

information and order information. In addition, the system needs to generate a report of custom orders

needed to be processed each day. For delivery orders, the system needs to generate a report for the driver

showing the order information and customer information, including the address.

Journal of the International Academy for Case Studies Volume 23, Number 1, 2017

28

Dr. Waggoner explained to his analysis and design students what Sarah really needed was a

database system which would maintain information about each custom order. The system should

also generate daily reports on what orders need to be processed.

After gathering the detailed requirements for the system, Dr. Waggoner assigned his

students the requirements shown below. His SAD students began developing data and process

models and designing the user interfaces. As the semester progressed and the analysis and design

phases were completed, Dr. Waggoner had his system development class use the specifications

and start creating the working system. By the end of the semester the system was completed and

implemented, and Sarah’s Short Cakes was able to keep track of their orders much more

efficiently.

Requirements for Systems Analysis and Design Students:

1. Prepare a system proposal that includes an executive summary, the requirements of the

system, and identification of your team members.

2. Develop appropriate process models (Use Case Descriptions/Diagram or Data Flow

Diagrams – context level, level 0, level 1) per

your professor’s instructions.

3. Develop the appropriate data model (Class Diagram or Entity-Relationship Diagram) per

your professor’s instructions.

4. Develop preliminary screen and report designs for each user interface identified above,

adhering to proper user interface design principles.

5. Prepare a one-page “pre-implementation review” outlining lessons learned – what went right

and what went wrong on this project.

Requirements for Systems Development Students:

1. Complete the above requirements, or refer to the packet of materials provided by your

professor.

2. Using Microsoft Access, Visual Basic, or other appropriate development tool, develop a

comprehensive, user-friendly, working system that will meet the requirements of Sarah’s

Short Cakes.

3. Prepare a user manual describing how to use the system.

4. Prepare a one-page “post-implementation review” outlining lessons learned – what went

right and what went wrong on this project.

Additional Resources:

The following are addresses to YouTube videos which provide a very useful inside view of the

operations of a bakery. These videos may help with your understanding of the business

processes.

Journal of the International Academy for Case Studies Volume 23, Number 1, 2017

29

APPENDIX

Sales Order

Sarah’s Short Cakes Logo

Sales Order # 2331 Sales Order Date 09-23-xx Date Needed

Customer Name Time Needed

Email Address Phone 443-6 6 0-0007

Street Address City C aldwe ll

Quantity Cake Flavor Cake Color Icing Flavor Icing Color Decoration Cost
6 Ch ocol ate N/A Bu tter Cr eam Wh i te r ed r os e 2 4. 00$

6 S tr awber r y N/A Bu tter Cr eam Red bl u e r os e 2 4. 00$

12 Vani l l a Bl u e Bu tter Cr eam Bl u e wh i te r os e 48. 00$

96 .00$ Total Cost

Sarah’s shortCakes

2231 M aple S t re e t

09-30-xx

Sales Order

bwhit e side @hmail.c o m

B rian Whit e side

Delivery or Pickup?

Pic k up

2:00pm

PatronsPatrons

SeatsSeats
Seat CategorySeat Category

ProductionProduction
PerformancePerformance

TicketTicket

Patron_IDPatron_IDPKPK

First_NameFirst_Name

Last_NameLast_Name

Addr1Addr1

Addr2Addr2

CityCity

StateState

ZipZip

PhonePhone

EmailEmail

Seat_NumSeat_NumPKPK

Seat_CatSeat_CatFKFK

SCat_IDSCat_IDPKPK

SCat_NameSCat_Name

SCat_PriceSCat_Price

Prod_IDProd_IDPKPK

Prod_TitleProd_Title

Prod_TypeProd_Type

Perf_IDPerf_IDPKPK

Perf_ProdPerf_ProdFKFK

Perf_DateTimePerf_DateTime

Perf_TypePerf_Type

Ti cket_IDTi cket_IDPKPK

Ti cket_PatronTi cket_PatronFKFK

Ti cket_PerfTi cket_PerfFKFK

Ti cket_SeatTi cket_SeatFKFK

• Drawing2

Page-1

PatronsEntity

Attribute Data Type

Patron_ID Int PK, auto increment

First_Name Varchar(50)

Last_Name Varchar(50)

Addr1 Varchar(50)

Addr2 Varchar(50)

City Varchar(50)

State Varchar(2)

Zip Varchar(5)

Phone Varchar(10)

Email Varchar(100)

Seats Entity

Attributes Data Type

Seat_Num Varchar(3) PK

Seat_Cat Int FK (Seat Category Scat_ID)

Seat Category Entity

Attributes Data Type

Scat_ID Int PK, auto increment

Scat_Name Varchar(50)

Scat_Price Decimal(5,2)

Production Entity

Attributes Data Type

Prod_ID Int PK, auto increment

Prod_title Varchar(100)

Prod_type Varchar(100)

Performance Entity

Attributes Data Type

Perf_ID Int PK, auto increment

Perf_Prod Int FK (Production Prod_ID)

Perf_Datetime Datetime

Perf_Type Varchar(20)

Ticket Entity

Attributes Data Type

Ticket_ID Int PK, auto increment

Ticket_Patron Int FK (Patrons Patron_ID)

Ticket_Perf Int FK (Performance Perf_ID)

Ticket_Seat Varchar(3) FK (Seats Seat_Num)