Scenario Overview

In this course, you will follow along as an employee for an online

bookseller named JustLee Books. The company that you are employed

with sells books via the internet with a nationally based clientele. In

your role as a part-time database analyst, you will be responsible for

providing data when a new customer places an order with JustLee

Books. You are new in your role, but JustLee Books owner, Michael

Fenick, is very appreciative to have you aboard since his previous fulltime

data analyst recently retired. Your responsibilities involve handling

and retrieving customer data such as name, billing, and shipping

addresses, and items ordered. Also, you will use a database for all books

in the inventory. When you access the data required for operating

JustLee Books, you will rely on a database management system

(DBMS). You will use the DBMS to create and maintain the structure of

a database as well as to enter, manipulate, and retrieve the data it

stores. Michael is willing to teach you so that you can come aboard fulltime

once you learn how to use the database management system.

Michael plans to give you small projects to help you learn about

creating an efficient database design, which is key to using the database

to support JustLee Books business operations.

Work Project Details

In your first small project, Michael introduces you to basic database

terminology and the process of how JustLee Books is designed as a

database. He interprets Figure 1.5 (will be a file attachment in D2L),

offering that a customer can place one or more orders. Each order might

have multiple items within it. He explains that a book might be ordered

multiple times on an order. Also, a book might have multiple authors, and

an author might write one or many books. In addition, a publisher might

publish one or more books. For this small project, Michael has asked that

you work through these tasks the days you are in the office this week:

1. Think of an existing business OR a business you would like to

start. Write a paragraph about what the business does. Use the

JustLee Books Entity Relationship Diagram (Figure 1-5) and its

interpretation above in planning out your paragraph.

2. Use the ERD for JustLees bookstore: Which tables and fields

would you access to determine which book titles have been

purchased by a customer and when the order shipped?

3. How would you determine (which tables and fields would you use)

to find which orders have not yet been shipped to the customer?

4. If management needed to determine which book category

generated the most sales in April 2009, which tables and fields

would they consult to derive this information?

5. Explain how you would determine how much profit was generated

from orders placed in April 2009.

6. If a customer inquired about a book written in 2003 by an author

named Thompson, which access path (tables and fields) would you

need to follow to find the list of books meeting the customers

request?

7. Every employee in a company is assigned to one department.

Every department can contain many employees.

8. A movie megaplex needs to collect movie attendance data. The

company maintains sixteen theaters in a single location. Each

movie offered can be shown in one or more of the available

theaters and is typically scheduled for three to six showings in a

day. The movies are rotated through the theaters to ensure that

each is shown in one of the stadium-seating theaters at least

once.

9. An online retailer of coffee beans maintains a long list of unique

coffee flavors. The company purchases beans from a number of

suppliers; however, each specific flavor of coffee is purchased

from only a single supplier. Many of the customers are

repeat purchasers and typically order at least five flavors of beans

in each order.

CUSTOMERS Customer# Lastname Firstname Email Address City State Zip Referred Region ORDERS Order# Customer# Orderdate Shipdate Shipstreet Shipcity Shipstate Shipzip Shipcost ORDERITEMS Order# Item# ISBN Quantity Paideach BOOKAUTHOR AUTHOR BOOKS ISBN Authorld ISBN Authorld Lname Title Fname Pubdate PublD Cost Retail Discount PUBLISHER Category PubID Name PROMOTION Contact Phone Gift Minretail Maxretail