Creating Entity Relationship

Diagrams

Identify data stores

• The data stores are easy to identify if you have already created a data flow diagram

• If you are working from a document list the data stores are usually identified by; “filed into”, “from the …. file” etc.

Name of data storeM

Add the data stores to the ERD diagram

• For each data store draw a rectangle with the name of the data store inside

Product

Sales

Customer

Salesmen

Look at the data in each store (entity)to find fields (attributes) they have in common by which they can be

linked

ProductProductID

Product Stock levelPrice

SalesInv NoCustIDDate

ProductIDQuantity

CustomerCustIDName

Address

SalesmenPayNoName

AddressTel

In this system: -Customer links to Sales via CustIDProduct links to Sales via ProductIDSalesmen has no connection

Just looking at the linking fields

• If you found a link value in one table, how many records with the same link value at the opposite end might be found over time?

• If “many” is possible then the connecting line (relation) should have a “Crows foot” connector at that end. Repeat this in the opposite direction

N-Ary Relations

SalesInv NoCustIDDate

ProductIDQuantity

CustomerCustIDName

Address

CustID Name Address

1 Fred The house

2 Jane The castle

InvNo CustID Date ProdID Quantity

4 1 01/01/14 3 3

6 1 02/01/14 7 4

8 2 01/03/14 3 9

A CustID only ever appears one in the Customer table but the same CustID may appear many times in the Sales table.

The Final Entity Relationship Table

• One customer may be involved in many sales but one sale only involves one customer. One product may be involved in many sales invoices but one Sales invoice product may only identify one product in the Product table.

ProductProductID

Product Stock levelPrice

SalesInv NoCustIDDate

ProductIDQuantity

CustomerCustIDName

Address