Customer

1
Version 1 - ERD
Customer Cust Name Address State
Catalog Catalog Description Color Size Price
Order Order Date Cust
OrderLine Order Line Catalog Qty
(weak entity)
2
Version 1 DB schema
Customer (Cust, Name, Address, State, ) Catalog
(Catalog, Description, Color, Size, Price) Order
( Order, Date, Cust(FK), Paid) OrderLine
(Order, Line, Catalog (FK), Qty)
Notes The description in Catalog may seem
somewhat redundant, but we will assume that it
includes references on color and size. Color and
size fields are also included to simplify
querying . In a real-world case we would
probably include tables for color, size, state
and any other attribute that can be
coded. Regarding the OrderLine Table, another
possibility would be to eliminate the line
attribute and keep Catalog as part of the
primary key. This would leave a composite primary
key of the type Order, Catalog. We should need
to be sure, though, that an order does not
contain more than 1 reference to any Catalog
(we may have duplicates without this
restriction) Price has not been included in the
order because it is functionally dependent on
Catalog. We can always retrieve prices from the
Catalog table by joining Catalog and Orderline.
This may be unrealistic in a practical setting,
as prices may change as time passes. This would
justify including price in the Orderline, but its
purpose should need to be described. Same thing
applies to InvoiceAmount. It may be retrieved
through an aggregate function on price and a
systematic calculation on taxes, but it may be
handy to include the total amount in the Order
table, although it does not strictly respond to
3NF
3
Version 2 - ERD
CatInv
Customer Cust Name Address State
Inventory Inv Prod Color Size Qty
Cat Inv
(associative entity)
Order Order Date Cust
OrderLine Order Line Inv Qty
(weak entity)
4
Version 2 DB schema
Customer (Cust, Name, Address, State,
) Inventory (Inv, Prod(FK), Color, Size,
Qty) Product (Prod, Description,Vendor,
Price) Catalog (Cat, Description, ) Order (
Order, Date, Cust(FK), Paid) OrderLine (Order,
Line, Inv (FK), Qty) CatInv(Cat,Inv,
any-additional-attrib-belonging-to-the-assoc-relat
ionship)
Notes The associative entity is the only
available way to implement an mn relationship in
the relational model You need this entity in
order to determine which inventory items are
included in each catalog Product and Inventory
should be joined through a 1 0,n relationship,
given the fact that there may be some products
not present in the inventory. Anyway, this is a
secondary and rather unrealistic case, so I have
decided to eliminate the 0 in the relationship