Assignment 3 Entity Relationship Diagramming

Assignment 3
Entity Relationship Diagramming
The goal of this assignment is to create an entity-relationship model for
the system described below and to create a use case for ordering an ice
cream cone.
You must prepare your ER model using some electronic drawing program
(there are many programs, like Microsoft Visio, that have ER capabilities
built in; but it is also possible to draw a nice ER model using something
like PowerPoint or Google drawings).
This is a 2-3 person assignment. You may fully collaborate with your partners, and all names should be included on the submission. EACH person
should submit an electronic copy of the document, but only one hard copy
should be submitted. Everyone will receive the same grade on the assignment.
You must use the Chen notation given in class, or something very close to
it (note any exceptions due to drawing program limitations). Make sure
to show the cardinalities, primary keys for all entities, and everything else
we talked about in class.
Take care when building this model, as we will be using it for future
assignments.
What to turn in
You must upload a pdf of your final model to Canvas and submit a hard
copy. The hard copy is due by NOON of the first school day after the
assignment deadline. The hard copy must match what you submitted online. Drawings can be dropped off at Duncan Hall 2062 (slide under the
1
door if no one is there).
Grading
The model is worth 80 points. Points will be assigned based on the following guidelines:
• 0 points: Model not attempted.
• 10 points: All entities are present.
• 25 points: All entities and attributes are present and correctly assigned.
• 60 points: All entities and attributes are present, correctly assigned,
and most of the relationships are present and correct.
• 80 points: The model is correct and includes all stated elements and
relationships.
• Deductions: For missing attributes, incorrect use of notation or errors
in, missing relationships, vague relationship names, etc.
The Use Case is worth 15 points. Points will be assigned based on the
following guidelines:
• 0 points: Use Case not submitted.
• 5 points: Basic components are present.
• 10 points: The Use Case is mostly complete, but some key steps are
missing or only one exception is described.
• 15 points: The Use Case is complete, includes all components and
covers at least two exception circumstances.
• Final 5 points: questions submitted for business requirements session.
• Deductions: For missing attributes, incorrect use of notation or errors
in, missing relationships, vague relationship names, etc.
The Final 5 points are based on your submission of at least two questions
based on the assignment. These are to be submitted on Canvas by 9 AM
on the class day when we will have this exercise.
2

1 ER Model – Ice Cream Food Truck Business
1.1 Background
A local entrepreneur has recently purchased a food truck that serves softserve ice cream and related items. Seeing an opportunity, the business
owner has decided to focus on selling ice cream to the Rice University
community. We are going to design a database that will help the owner
and her staff run, manage, and grow the business. At a minimum, each
event is staffed by an order taker and an ice cream maker. While staffing
is an important part of a business, we are not going to include it in this
iteration of our data model.
1.2 What to Model
Model the database as described here.
The food truck sells products. Products are cups and cones (small, medium,
large), sundaes (brownie, hot fudge, etc.) and drinks, to name a few. Products are identified by a unique one - three letter alphanumeric code and
have unique names. The three letter code is just a convenient shorthand
for the product name.
Products have prices. Prices are in US dollars and cents. Prices can change
over time, but only one price can be in effect at any given time. Prices have
an effective start date. The current price is the tuple with the most recent
start date. Older product prices are maintained in the database, but they
3
will, by definition, have an older effective start date. A product price may
change because of a subsidized event (e.g. Rice University covers $1 of
every purchase during a study break, so all prices are reduced) or it may
change due to a rise in ingredient cost. Products have an “IsAvailable”
flag, indicating if the product is currently available for sale. If we run out
of ingredients for a product or are running a limited menu, some products
might not be available. A product’s price is computed from the cost of
the product’s ingredients, along with some level of markup. Determining a product’s price is dependent on many factors, including ingredient
cost, labor rates, target demographic, etc. Determining a product’s price
is difficult, and this database is intended to assist the owner with that
determination.
Each product is composed of a number of ingredients, some of which require choices. For example, the customer gets to pick the topping on their
ice cream (sprinkles!) or the flavor of their slushie (e.g. Bubblegum!) Each
ingredient has a name and a category. Together the name and category are
unique. For example, there may be Strawberry ice cream and Strawberry
fruit. In these cases, “Strawberry” is the name, and the other term is the
category. When an ingredient is included in a product as part of a recipe,
it has a quantity and a unit of measure (e.g. “cup”, “ounce”, or “item”
for indivisible ingredients.) We might have ingredients on hand that are
not yet part of a product, but are in stock in preparation. Recipes are
simply groups of ingredients, units, and quantities. At this time, we are
not tracking instructions on how to assemble the product. Everything that
we sell must be listed as a product. For example, we might have a product
called “Extra cone topping.”
Products are only sold during events. During each event, a log of all the
products sold, with the accompanying ingredient id, quantity and unit is
recorded. This is how we know what was actually sold. For example, we
would record that product c1 (a kid’s cone) was sold. As part of this sale,
we would also log 1 short napkin, 3 oz of chocolate ice cream, a #10 cone,
and 1 oz of oreo cooking topping.
Each event is identified by a name and start date/timestamp. For example
there may be multiple “Jones College Study Break” events, but each will
have a unique start date/time. Events also have a planned and an actual
4
end date and time and a total dollar sales. This information may be used
(later) to analyze product sales to determine the most popular products
and / or profitable events. Events have status. This includes values like
“Planned”, “Confirmed”, “Cancelled”, and ”Completed”.
Suppliers sell raw ingredients that get turned into products. Suppliers
have a unique name and a location, which includes a street address, city,
state (or province), postal code, and country. They also have any number
of phone numbers. There are two different types of suppliers - those who
deliver only locally (Local Suppliers) and those that deliver Nationally
(National Suppliers). National suppliers may be located outside of the
United States. Local suppliers have a per mileage cost based on distance
from the receiving address for the ice cream truck. National suppliers have
a fixed transportation fee. Suppliers provide quote(s) for ingredients. Each
quote has a unique id (quoteId), for that particular supplier. In addition,
each quote has an issue date, an expiration date, tax amount, fee amount,
and a total, which will be computed from the cost of the ingredients on the
quote, the taxes and the fees. Fees might include delivery charges, charges
to expedite an order, etc. The fee might also be a credit or discount
amount. Included on each quote are any number of ingredients. Each line
item on the quote must correspond to one of our ingredients and specify
a quantity, a unit, and a unit cost. Choosing which quote to purchase
is a complex business decision. Our system will help our business owner
decide. The owner may choose to purchase any number of quotes.
Our ice cream truck gets multiple quotes for ingredients from multiple
suppliers in order to compare costs. She chooses to purchase some of the
quotes. Each purchase results in a delivery. We will assume that all the
items on the quote are delivered at the same time and all ordered items are
delivered, and that we have sufficient storage for all deliveries. Deliveries
have an order date, and an arrival date. We assume that ingredients with
the same name from different suppliers are equivalent.
Once a delivery is received, we stock the delivered item(s) into our inventory. Inventory is tracked per ingredient and each inventory item has
a quantity, a quantity remaining, a unit, and an expiration date (which
may be NULL). Assume that all of a single ingredient in a delivery has the
same expiration date. For example, in a single delivery, we would NOT get
5
some strawberries that expire on October 10, 2018 and some that expire
on October 15, 2018. We may run out of ingredients and we may have
inventory items from multiple deliveries. One of the challenges of running
this type of business is using up ingredients with different expiration dates.
The ice cream truck has specialized equipment for making the ice cream.
Each piece of equipment has an unique name and an install date. There
may be more than one of each type of equipment. For example, there
may be 3 milkshake machines. Associated with each piece of equipment
is a list of required maintenance. Each required maintenance item has a
name, a timing trigger indicator (e.g. “before”, “after”, “as needed”, etc.),
a trigger quantity, a trigger unit and how long the maintenance takes, in
minutes. This is basically a set of routines for maintaining the equipment.
For example, the truck must be cleaned after every (quantity = 1) event
(the unit) and the cleaning takes 120 minutes. The generator must be
refueled after 40 hours of use. The maintenance name, along with the
equipment name, must be unique. Performed maintenance is recorded in
a log, where each log entry includes the equipment name, the maintenance
name, the date of the maintenance, how many minutes it actually took,
and any notes about the maintenance. This log will contain scheduled and
unscheduled maintenance records. If unscheduled maintenance is required,
and there is no existing description of the required maintenance, an entry
must be created
2 Use Case
Write-up a Use Case for the goal: Ordering an ice cream cone.
Your actors should include the customer, the order taker and the person
making the ice cream cone.
You must include at least two exceptions. For example, what if the truck
is out of the ice cream flavor requested?
6
3 Our Food Truck
Our ice cream truck business should be named:
7