PART 2.

Entity Relationship Diagram

Create an ERD

for the RMO web ordering system that properly represents all of the information in

the narrative below. Deviations from this narrative

will result in point deductions.

The database must be able to record information about

ORDERS

placed by

CUSTOMERS

. A

pa

rticular customer may place many orders, and it is assumed that every customer

has placed

at

least one order. A particular order is associated with one an

d only one customer. I

nformation

that must be captured about each order includes the unique identifier

for that order, the ID

for

the customer that placed it, the date and time of the order, the total dollar amount of the order,

the dollar amount for shipping & handling, and the dollar amount for sales tax.

Information that

must be captured about customers

includes their name, address, and contact information, as well

as information for their preferred credit card (type, number, and expiration date).

Each order includes one or more

PRODUCTS

, and must include at least one product. A

particular product (such

as Size 12 Leather Hiking Boots) may be included in many orders, but

it is possible that a particular product is

never ordered at all. I

nformation that must be tracked

about each product inclu

des its unique identifier,

the product name, description, gender

(M, F,

or U for unisex), size, color, price, and quantity on hand (for purposes of inventory tracking).

Since orders can include many products, and products can be included in many orders, it is

necessary to track information on each individual

LINE ITEM

that makes up the order. Each

line item record must be

able to be traced back

to the associated order and product. In addition,

information must be

captured regarding

the quantity ordered for that item, the shipment that the

item went out in (once this

inf

ormation

is known), and the items backorder status (Y or N).

If

an item ends up being returned by the customer, the database must be able to record the reason

for the return.

Line items in an order may be shipped out at different times. Thus, it is import

ant to keep track

of individual

SHIPMENTS

. A particular shipment may

include many line items from a

particular

order, and obviously must include at least one

line

item. A particular line item in an

order will be associated with only one

shipment

, but may

b

e associated with no

shipment

s

at all

(if the item is permanently out of stock, discontinued, or the order is canceled). Information that

must be captured about each shipment includes

its

unique identifier, the carrier responsible for

the shipment (e.g., U

SPS, UPS, FedEx), the date and time of the shipment, and the shipment

tracking number

provided by the carrier

.

As alluded to above, t

he

database

also needs to be able to keep track of customer

RETURNS

.

The customer must call RMO before returning any items,

and get an authorization code

for the

return shipment

. They must ship the item(s) ba

ck to RMO using a carrier

(e.g., USPS, UPS,

FedEx) that has a package tracking service, and they must provide RMO with the

carrier name

and

tracking number.

A customer may

potentially return more than one

line

item

from an order

at a time, but they must postmark

all

returned items within 30 days of the original shipment

date. The cu

stomer must also g

ive a reason for returning each item (for example, they might

return one it

em for being damaged, and another

item for being the wrong size).

Y

ou must do all of the following f

or

the ERD

portion

of the assignment:

S

how all entities that are necessary to carry out the functionality described

on the previous

page

.

List

all

critic

al attributes for each

entity, based on the information provided in the narrative.

Choose an appropriate data type for each attribute (e.g., integer,

decimal,

time

stamp

,

char

,

varchar

)

, and indicate which attributes are required (i.e., not null)

Identify

a

n appropriate

unique identifier (

primary key

) for each

entity

, and label it

accordingly

.

Show all relationships between entities that are necessary for the system to function as

described.

Do not show relationships that serve no business purpose.

Show th

e proper cardinality (multiplicity) for each relationship based on the information

provided in the narrative. Relationship lines must be labeled with an appropriate descriptive

word or phrase describing the relationship

.

Resolve any many

-

to

-

many relationsh

ips as taught in class. Keep in mind that the

associative entities formed in resolving a many

-

to

-

many relationship may have unique

attributes of their own.

There is one many

-

to

-

many relationship in the narrative. I have

given you the information you need f

or the

attributes associated with the new

associative

entity