7. Create an E-R model and define its table structures for the following requirements.

 

¨      An INVOICE is written by a SALESREP. Each sales representative can write many invoices, but each invoice is written by a single sales representative.

 

¨      The INVOICE is written for a single CUSTOMER. However, each customer may have many invoices.

 

¨      An INVOICE may include many detail lines (LINE) which describe the products bought by the customer.

 

¨      The product information is stored in a PRODUCT entity.

 

¨      The product's vendor information is found in a VENDOR entity.

 

 

Note: The E-R model reflects business rules that you are (within reason!) free

to define. Make sure that your E-R model reflects the conditions you require.

 

 

Figure P4.7  The ERD for Problem 7

 

 

 

Teacher's note: Keep in mind that the preceding E-R diagram reflects

a set of business rules that may easily be modified. For example,

if customers are supplied via a commercial customer list, many of

the customers on that list will not (yet!) have bought anything, so

INVOICE would be optional to CUSTOMER. We are assuming here

that a product can be supplied by many vendors and that each

vendor can supply many products. The PRODUCT may be optional

to VENDOR if the vendor list includes potential vendors from which

you have not (yet) ordered anything. Some products may never sell,

so LINE is optional to PRODUCT... because an unsold product will

never appear in an invoice line. You may also want to show the

students how the composite entities may be represented at the

final implementation level. For example, LINE is shown as weak to

INVOICE, because it borrows the invoice number as part of its primary

key and it is existence-dependent on INVOICE. The modified E-R

diagram is shown next. The point of this exercise is that the design's

final iteration depends on the exact nature of the business rules and

the desired level of implementation detail.

 

 

Figure P4.7A  The Revised ERD for Problem 7