Verification of ER Diagram and Attributes

Provide a verification of the reports, forms, and/or processes in terms of the E-R diagram.  The process is designed to verify that all of the data items that appear on a form or report (or that are used in a process) are available in the entities, and that the entities are correctly linked. 

 

When performing the validation, determine the primary entity from which the form or report is derived, and then show how the attributes, including those from other entities, are obtained through foreign keys, composite keys, etc.

 

For each report, form, and/or process provide the following: 

report/form/process:     <report name/form name/process name>
            This involves <x> entities:
                        <entity 1>
                        <entity 2>
                              :
                        <entity n>

            and <y> composite entities:
                        <composite entity 1>
                        <composite entity 2>
                                :
                        <composite entity n>

The primary entity is <entity primary>

 

The data items are accounted for in the E-R Diagram as follows: 

Trace each data item as follows, depending on the particular case.  The possible cases are

 

Case: data item is an attribute in a non-primary entity, which is not linked directly to <entity primary>

 

Format:
            <data item>: attribute <attribute n> of entity <entity n>

  • <entity n> is linked to<entity m> through the <attribute n> foreign key in <entity m>

  • <entity m> is linked to <entity primary> through the <entity m primary key> foreign key in <entity primary >

 

Example: Customer No.

Case: data item is an attribute of <entity primary>

Format:
            <data item>: attribute <attribute n> of entity <entity primary >

 

Example: Invoice No.

Case: data item is an attribute that is both the primary key of <entity n> and foreign key of <entity primary>

Format:
            <data item>: attribute <attribute n> of entity <entity primary > and entity <entity n>

  • <entity n> is linked to <entity primary> through the <attribute n> foreign key in <entity primary>

 

Example: Order No.

Case: data item is an attribute of <entity n>, which is linked to <entity primary> through <entity composite>

Format:
            <data item>: attribute <attribute n> of entity <entity n>

  • <entity n> is linked to <entity primary> through the (<entity n primary key>, <entity primary primary key>) composite primary key in composite entity <entity composite>

 

Example: Product No.

Case: data item is an attribute of <entity composite>, which is not linked directly to <entity primary>

Format:
            <data item>: attribute <attribute n> of composite entity <entity composite>

  •  <entity composite> is linked to <entity n> through the <entity n primary key> portion of the composite primary key in <entity composite>

  • <entity n> is linked to <entity primary > through etc.

 

Example: Qty Ordered

Case: data item is an attribute of a <entity composite>, which is linked directly to <entity primary>

 

Format:
            <data item>: attribute <attribute n> of composite entity <entity composite> 

  •   <entity composite> is linked to <entity primary > through the <entity n primary key> portion of the composite primary key in <entity composite>

 

Example: Qty Shipped
 

Case: data item is derived from other data items

Format:
            <data item>: obtained from the calculation <data item x> <op> <data item y>

 

Example: Total Price

Case: data item is derived from attributes in a non-primary entity

 

Format:
            <data item>: obtained from the calculation <attribute n> of entity <entity n> <op> <operand2>

  • <entity n> is linked to <entity primary > through etc.

 

Example: Unit Price

 Case: data item is obtained from system resources

 

Format:
            <data item>: obtained from system <time/date/etc.>

 

Example: Date

Example of Verification of the Customer Invoice (This verification is associated with this E-R Diagram.)

Report: Customer Invoice

The Customer Invoice involves four entities,

  • CUSTOMER
  • ORDER
  • ORDER INCLUDES
  • PRODUCT 
  • INVOICE
  • SHIPMENT INCLUDES 

The primary entity is INVOICE.

The data items are accounted for in the E-R Diagram as follows:

  • CUSTOMER NO: attribute C# of entity CUSTOMER.  
    • CUSTOMER is linked to ORDER via the C# foreign key in ORDER.   
    • ORDER is linked to INVOICE through the O# foreign key in INVOICE.
  • CUSTOMER NAME: attribute C_NAME of entity CUSTOMER.
    • CUSTOMER is linked to ORDER via the C# foreign key in ORDER.   
    • ORDER is linked to INVOICE through the O# foreign key in INVOICE.
  • CUSTOMER ADDRESS: attribute C_ADDR of entity CUSTOMER.
    • CUSTOMER is linked to ORDER via the C# foreign key in ORDER.   
    • ORDER is linked to INVOICE through the O# foreign key in INVOICE.
  • INVOICE NO: attribute I# entity INVOICE.
  • DATE: obtained from system date.
  • ORDER NO: attribute O# of entity INVOICE and entity ORDER 
    • ORDER is linked to INVOICE through the O# foreign key in INVOICE.
  • PRODUCT NO: attribute P# of entity PRODUCT.
    • PRODUCT is linked to INVOICE through the P#, I# composite primary key in composite entity SHIPMENT INCLUDES.
  • DESCRIPTION: attribute P_DESCR of entity PRODUCT.
    •  PRODUCT is linked to INVOICE through the P#, I# composite primary key in composite entity SHIPMENT INCLUDES.
  • QTY ORDERED: attribute ORDER_PRODUCT_QTY of composite entity ORDER INCLUDES.
    • ORDER INCLUDES is linked to PRODUCT through the P# portion of the composite primary key in ORDER INCLUDES.
    • PRODUCT is linked to INVOICE through the P#, I# composite primary key in composite entity SHIPMENT INCLUDES.
  • QTY SHIPPED: attribute SHIPMENT_PRODUCT_QTY of composite entity SHIPMENT INCLUDES.
    • SHIPMENT INCLUDES is linked to INVOICE through the I# portion of the composite primary key in SHIPMENT INCLUDES.
  • QTY BACKORDERED: obtained from the calculation ORDER_PRODUCT_QTY  minus SHIPMENT_PRODUCT_QTY (see previous two items to trace links).
  • UNIT PRICE: attribute P_PRICE of entity PRODUCT.
    • PRODUCT is linked to INVOICE through the P#, I# composite primary key in composite entity SHIPMENT INCLUDES.
  • TOTAL PRICE: obtained by multiplying QTY SHIPPED by P_PRICE.
  • TOTAL AMOUNT: obtained by summing the TOTAL PRICE column.
  • % DISCOUNT: attribute C_DISCOUNT of entity CUSTOMER multiplied by TOTAL AMOUNT.
    • CUSTOMER is linked to ORDER via the C# foreign key in ORDER.   
    • ORDER is linked to INVOICE through the O# foreign key in INVOICE.
  • AMOUNT DUE: obtained by subtracting % DISCOUNT from TOTAL AMOUNT.