Based on the ERD, answer the following questions.

ISYS463 Individual Assignment #4

Database: Sharetea

This assignment is an extension of the in-class exercise about the Sharetea case. To simplify the

data modeling, let’s narrow the scope to a franchised store and focus on modeling customers’

orders. You are going to use the provided ERD to answer questions and build a database in

Microsoft Access. If you completed the in-class exercise for Access, you could make changes to

your file to complete the assignment. DO NOT LET ANYONE COPY YOUR FILE OR COPY

SOMEONE’S FILE!! If caught, you will receive a zero.

Background
As Sharetea Menu shown below, each customer can place minimum “zero” to maximum “many”

orders of customized drinks at a Sharetea store. In each order, the customer chooses a specific

drink listed in one of the six categories. After a customer chooses his/her drink, he/she can

customize the drink by selecting the ice level and sweetness level. Then, the customer can decide

whether to pay extra for adding toppings and order a different quantity of the same drink. To

help customers choose their drinks, Sharetea marks “Recommended,” “Hot Available,” and

“Non-Caffeinated” on the menu. The price of a customized drink is based on a base-price and

extra costs for toppings. Sharetea sells drinks in one size. In this assignment, the Split Cup

service is not considered. Therefore, there is no need to model drink size in the ERD.

Source: http://shareteahercules.com/order.html

Assuming Sharetea hires you to create a database for building an online ordering system, use the

Sharetea Menu and the ERD show below to help you create a database in Microsoft Access.

Tasks

1. Based on the ERD, answer the following questions.

1) Use the format shown below to list the primary key in each entity.

i. Entity name (Primary key)
2) In some entities, you can find a foreign key(s), which is (are) used to create a

logical relationship(s) with another entity. List all of the foreign keys if you see

them in an entity.

i. Entity name (Foreign key, foreign key 1, foreign key 2)

3) Read the ERD, explain why there are many entities connected to the Orderline
entity.

4) There is an entity called “ToppingDetail.” What is this entity created? Why is it
connected to “Orderline” and “Topping”?

2. Use the ERD as a blueprint to create a database with the required tables and relationships in
Microsoft Access

1) If you did the in-class exercise, you could make changes to the database for this
assignment (You need to make some changes when using the Lookup Wizard).

2) Follow the steps shown below to complete the tasks.
i. Create a table for each entity

a) Tips: It is a good practice to create tables on the one-side of a One-
to-Many relationship first. For example, create Customer,

DrinkType, IceLevel, SugarLevel, Topping tables first, and then

create Order, Drink, Orderline, and ToppingDetail tables.

b) For each table, you need to choose a proper data type for each
attribute

c) You must assign a primary key for each table (you probably find
that MS Access automatically sets it)

ii. Populate data in Access tables
a) Switch to the table view in each table except these three tables–

Order, Orderline, and ToppingDetail tables, and use the data you

can find on the Sharetea Menu (e.g., drink type, drink, ice level,

sweetness level, and toppings.) to populate some required data in

those tables. Make up at least five customers in Customer table.

iii. Use Lookup Wizard to lookup foreign key values
a) For each foreign key you can find in Drink, Order, Orderline and

ToppingDetail tables, use the Lookup Wizard to choose the data

type

b) You can enter the “design view” of a table in Access to make
changes on the foreign keys (if you want to make changes on the

database you created in the class)

c) Before you make any changes in your table design view, you may
need to first delete the relationships you created in the in-class

exercise.

d) Follow the instructions listed in Appendix A to complete the tasks
e) For each foreign key (e.g., the Customer_ID in Order table or the

Drink_ID, Order_ID, IceLevel_ID,and SugarLevel_ID in

Orderline table), use the same way instructed in Appendix A to set

up the lookup feature for the foreign key fields

f) The lookup wizard can help the users to lookup the matched
primary key value when they are entering a foreign key. For

example, in the Order table, it is easier for a user to select a

customer from a lookup list to enter the value in the Customer_ID

field.

iv. Create relationships
a) In MS Access, go to Database Tools→ Relationships
b) Add all tables you created earlier
c) In a One-to-Many relationship, drag the primary key and drop it on

the foreign key

d) You will see a window pops up as shown below

e) Check “Enforce Referential Integrity” and click on “OK”
f) Repeat the steps to create all relationships

v. Create a form to enter customer data
a) Follow Appendix B to create a customer form, so you can use it to

add new customer

vi. Create an Order form that includes subforms to show the orderline and
topping detail.

a) Follow Appendix C to create an order form with subforms
vii. Place five orders on the

Order Form

a) In each order, specify the customer, drink and toppings
b) Check the Order, Orderline and ToppingDetail tables to see if the

database successfully stores the data you entered on the order form

and subforms

Submission

1. Answer the questions listed in Task 1 and save as a pdf file
2. Save the Access file and exit the application
3. Click on Individual Assignment #4
4. Attach both the pdf file and the Access file and submit them

Appendix A

In the “Order” table, the “Customer_ID” is a foreign key. At the Data Type field, click on

Lookup Wizard.

Appendix B

Create a Customer Form

Go to images.google.com and search “Customer” to find customer images. Extend the form header to

get more space. Select a customer image, and then copy and paste it in the header area. Adjust the size

to fit in the header.

Appendix C

Order Form

Add all fields from “Order” table

Select anther table, Orderline, in Form Wizard

Add all fields on the left to the selected fields except “Order_ID” from “Orderline” table

Select anther table, ToppingDetail, in From Wizard

Add all fields on the left to the selected fields except “Orderline_ID” from “ToppingDetail” table

Click on Finish button and switch to Form view to preview the form. You can see the form look like the

one shown below.

You can enter the design view of the Orderline subform to adjust the field positions.