Database Systems-Lecture-03
Database Systems-Lecture-03
Database Systems-Lecture-03
Instructor:
Sadiq Shah
(Lecture 03)
Modeling Data in the Organization
THE E-R MODEL
PRODUCT A type of furniture made by Pine Valley Furniture that may be ordered by
customers.
ORDER The transaction associated with the sale of one or more products to a
customer and identified by a transaction number from sales or
accounting.
ITEM A type of component that goes into making one or more products and
can be supplied by one or more suppliers.
SUPPLIER Another company that may provide items to Pine Valley Furniture.
SHIPMENT The transaction associated with items received in the same package by
Pine Valley Furniture from a supplier. All items in a shipment appear on
one bill document. Example: The receipt
The symbols at the end of each line on an ERD specify
relationship cardinalities, which represent how many entities of
one kind relate to how many entities of another kind.
Business rules:
1. A SUPPLIER may supply many ITEMs (by “may supply,” we mean the supplier
may not supply any items). Each ITEM is supplied by any number of SUPPLIERs
(by “is supplied,” we mean that the item must be supplied by at least one supplier).
2. Each ITEM must be used in the assembly of at least one PRODUCT and may be
used in many products. Conversely, each PRODUCT must use one or more ITEMs
3. A SUPPLIER may send many SHIPMENTs. However, each shipment must be sent
by exactly one SUPPLIER. Notice that sends and supplies are separate concepts.
4. A SUPPLIER may be able to supply an item but may not yet have sent any
shipments of that item.
Business rules:
6. A SHIPMENT must include one (or more) ITEMs. An ITEM may be included
on several SHIPMENTs.
7. A CUSTOMER may submit any number of ORDERs. However, each ORDER
must be submitted by exactly one CUSTOMER. Given that a CUSTOMER may
not have submitted any ORDERs, some CUSTOMERs must be potential,
inactive, or some other customer possibly without any related ORDERs.
8. An ORDER must request one (or more) PRODUCTs. A given PRODUCT
may not be requested on any ORDER or may be requested on one or more
orders.
E-R Model Notation