CIS 381 Database Systems ENTITY RELATIONSHIP DIAGRAM ERD

CIS 381 – Database Systems ENTITY RELATIONSHIP DIAGRAM (ERD) FUNCTIONAL DECOMPOSTION

Business Rules ERD Business Rules Business Entities Rules for Drawing Diagram Business Rules Example Business ERD Example Shipment-Supplier Example Shipment-Item Example There are several protocols to the way business rules are written. Not every protocol has to be followed, but in general, a wellwritten set of business rules consist of having a unique identifier, describes one and only one concept, are written in plain language, are written, and are from a single source. In terms of a unique identifier, business rules should come with an identifier that may consist of the rule number and the department it affects. Supplier-Item Example Item-Product Example Some examples of simple business rules: Product-Order Example Customer-Order Example Department ------ offers ----- Courses Course ----- generates ------ Class Professor ------- teaches ---- Class 2

Business Entities ERD Business Rules Business Entities Rules for Drawing Diagram Business Rules Example Business ERD Example Shipment-Supplier Example Shipment-Item Example Supplier-Item Example Item-Product Example Product-Order Example Customer-Order Example CUSTOMER A person or organization who has ordered or might order products. PRODUCT A type of furniture made by Pine Valley Furniture that may be ordered by customers. Note that a product is not a specific bookcase, because individual bookcases do not need to be tracked. 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-of-lading document. 3

Rules for Drawing an E-R Diagram ERD Business Rules Business Entities Rules for Drawing Diagram Business Rules Example Business ERD Example Shipment-Supplier Example Shipment-Item Example Supplier-Item Example Item-Product Example Product-Order Example Customer-Order Example 4

Business Rules Example ERD Business Rules Business Entities Rules for Drawing Diagram Business Rules Example Note that these business rules roughly follows a certain grammar: <entity> <minimum cardinality> <relationship> <maximum cardinality> <entity> For example Business ERD Example Shipment-Supplier Example Shipment-Item Example <A SHIPMENT> <must> < include>< one or more> < ITEMs> OR Supplier-Item Example Item-Product Example Product-Order Example Customer-Order Example <ITEM> <may> <be included><on several><SHIPMENTs> This grammar gives you a standard way to put each relationship into a natural English business rule statement. 5

Business ERD Example ERD Business Rules Business Entities Rules for Drawing Diagram Business Rules Example Business ERD Example Shipment-Supplier Example Shipment-Item Example Supplier-Item Example Item-Product Example Product-Order Example Customer-Order Example 6

Shipment-Supplier Example ERD Business Rules Business Entities Rules for Drawing Diagram Business Rules Example Business ERD Example Shipment-Supplier Example A SUPPLIER may send many SHIPMENTs. Shipment-Item Example Supplier-Item Example Item-Product Example Product-Order Example Customer-Order Example Each shipment must be sent by exactly one SUPPLIER. (Notice that sends and supplies are separate concepts. A SUPPLIER may be able to supply an item, but may not yet have sent any shipments of that item). 7

Shipment-Item Example ERD Business Rules Business Entities Rules for Drawing Diagram Business Rules Example Business ERD Example Shipment-Supplier Example Shipment-Item Example A SHIPMENT must include one (or more) ITEMs. Supplier-Item Example Item-Product Example Product-Order Example Customer-Order Example An ITEM may be included on several SHIPMENTs. 8

Supplier-Item Example ERD Business Rules Business Entities Rules for Drawing Diagram Business Rules Example Business ERD Example A SUPPLIER may supply many ITEMs (by "may supply" we mean the supplier may not supply any items). Shipment-Supplier Example Shipment-Item Example Supplier-Item Example Item-Product Example Product-Order Example Customer-Order Example 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). 9

Item-Product Example ERD Business Rules Business Entities Rules for Drawing Diagram Business Rules Example Business ERD Example Shipment-Supplier Example Each ITEM must be used in the assembly of at least one PRODUCT, and may be used in many products. Shipment-Item Example Supplier-Item Example Item-Product Example Product-Order Example Customer-Order Example Each PRODUCT must use one or more ITEMs. 10

Product-Order Example ERD Business Rules Business Entities Rules for Drawing Diagram Business Rules Example Business ERD Example Shipment-Supplier Example Shipment-Item Example Supplier-Item Example Item-Product Example Product-Order Example A given PRODUCT may not be requested on any ORDER, or may be requested on one or more orders. Customer-Order Example An ORDER must request one (or more) PRODUCTs. 11

Customer-Order Example ERD Business Rules Business Entities Rules for Drawing Diagram Business Rules Example Business ERD Example A CUSTOMER may submit any number of ORDERs. Shipment-Supplier Example Shipment-Item Example Supplier-Item Example Item-Product Example Product-Order Example Customer-Order Example Each ORDER must be submitted by exactly one CUSTOMER. 12