Entity Relationship Diagram

Description

The purpose of the Entity Relationship Diagram is to illustrate the basic data abstraction and structure of the system. Each box in the diagram represents a single table of data that must be maintained by the system. This box is referred to as an "entity". The diagram above has have four entities which are Salesperson, Customer, Orders, and Products.

The information for each entity will be stored in a table where there is a column for each field. Each line in the table will represent an individual instance of the entity. For example, in the Salesperson table each line will represent one salesperson.

The Salesperson Entity table contains:

• one column for Salesperson ID *
• one column for Name
• one column for Territory
• one column for Home phone #
• one column for Cellular #
• one column for Pager #
• one column for e-mail
• one column for Maximum visits/day
• one column for comments on Salesperson

The "Customer" Entity table contains:

• one column for Customer ID *
• one column for Company Name
• one column for Street address
• one column for City
• one column for Province
• one column for Postal Code
• one column for Telephone #
• one column for Fax #
• one column for Company's E-mail
• one column for Contact's Name
• one column for Contact's Number
• one column for Contact's E-mail
• one column for Date Last contacted
• one column for Next expected Contact
• one column for Comments on Customer

The "Product" Entity table contains:

• one column for Product # *
• one column for Motif # *
• one column for Size
• one column for Cost
• one column for Description of Product

The "Order" Entity table contains:

• one column for Order # *
• one column for Date
• one column for Customer ID
• one column for Salesperson ID
• one column for Product #'s
• one column for Quantity

Each column represents an attribute in the entity. The italicized attributes with a "*" beside them are called key attributes. A key attribute is unique to each row and can be used to retrieve or delete information in an entity. Information can also be accessed by other attributes in each entity; however, the key attributes are unique and refer to only one instance of the entity. Non-key attributes can refer to one or many instances of the entity.

In the above ERD, the diamonds linking the entities are called "Relationships". The purpose of a relationship is to connect two or more entities in the diagram. All relationships in the above diagram link exactly two entities. The three relationships are served by, places order and contains.

The relationship "served by":

• Connects the two entities Customer and Salesperson.
• The relationship is Mc:1 (many to one).
• Each customer has exactly one salesperson.
• Each salesperson can have one or many customers.
• The key attributes Salesperson ID and Customer ID relate the two entities together.

The relationship "places order":

• Connects the two Entities Customer and Order.
• The relationship is Mc:1 (many to one).
• Each customer can place many different orders.
• Each order can pertain to only one customer, so every order is unique.
• The key attribute Customer ID and Order ID relate the two entities together.

The relationship "contains":

• Connects the two entities Order and Product.
• The relationship is Mc:Mc (many to many).
• Each order can contain many products.
• Each product can be on many orders.
• The key attributes Order ID and Product ID relate the two entities together.

The box labeled Territory with the double bars around it is "Weak Entity". The weak entity divides the customers into their respective territories. This will ensure that each customer is assigned to the proper salesperson. The administrator will be forced to explicitly specify the territory in which a customer resides.