Entity Relationship Diagram

Entities

The Entity Relationship Diagram (ERD) for the Sales Routing System is presented above. The purpose of the ERD is to illustrate the basic data abstraction of the system. For the Sales Routing System, there are four major objects that correspond to a user, a customer, a product and an order. These four objects correspond to the entities User, Customer, Order, and Product. Each entity in the above diagram is described as a rectangular box. Within the implementation, an instance of an entity will represent a line in a table corresponding to that entity. For example, the User entity corresponds to the list of users in the database.

An instance of the entity is composed of many data items. These data items are fields or columns in the table of the entity. To reduce storage costs, only data which cannot be computed at runtime will be stored. Each data items represents an attribute of the entity. Each entity contains one or more attributes which uniquely identifies an instance of the entity, these attributes are called the key attributes or primary keys. The primary keys can be used to retrieve or delete information from the entity table.

User Entity

The User Entity table contains the following data items:

• a unique user ID
• user's name
• username corresponding the a unique login identification string
• password to gain access to the system
• security level to limit access to specific commands in the system
• territory to which the user is assigned
• office telephone number
• cellular number
• pager number
• email address
• the maximum number of customers that the user can visit in one day (maximum visits per day)
• comments on user

The primary key of the User entity is the user ID.

Customer Entity

The Customer Entity table contains the following data items:

• a unique customer ID
• position in route list corresponding to current position in data flow diagram 1.B
• company name
• street address
• city
• province
• postal code
• company's telephone number
• company's FAX number
• company's email address
• contact's name
• contact's telephone number
• contact's email address
• date last contacted
• estimated days till next contact
• comments on customer

The primary key of the Customer entity is the customer ID.

Product Entity

The Product Entity table contains the following data items:

• product number
• motif number
• product name
• size of product
• number of items per unit
• cost per unit
• comments on product

The primary keys of the Product entity are the product number AND the motif number.

Order Entity

The Order Entity table contains the following data items:

• purchase order number
• whether or not the order has been fill (order filled)
• date of purchase
• customer ID
• user ID
• product and motif numbers describing products ordered
• quantity ordered for each product and motif number pair

The primary key of the Order entity is the purchase order number.

Relationships

For the Sales Routing System, there are three relationships between the objects. The relationships are:

1. A customer is served by a user.
2. A customer places an order.
3. An order contains one or more products.

In the ERD above, relationships are described in the diamonds. Each diamond corresponds to a table that relates some number of instances of one object to some number of instances of the other. A relationship may be one to one, one to many or many to many. Each line in the table has two or more fields containing the primary key(s) of the instances of the related entities. For example, customer 123 is served by user 321.

is served by Relation

The properties of the relationship is served by include:

• relates the entities Customer and User
• the relationship is Mc:1 (many (possibly zero) customers to one user)
• each customer is served by exactly one user
• each user can serve zero or more customers
• related by the key attributes user ID and customer ID

places Relation

The properties of the relationship places include:

• relates the entities Customer and Order
• the relationship is 1:Mc (one customer to many (possibly zero) orders)
• each customer may place many orders
• each order is related to only one customer, hence every order is unique
• related by the key attributes customer ID and purchase order number

contains Relation

The properties of the relationship contains include:

• relates the entities Order and Product
• the relationship is 1:M (one order to one or more products)
• each order must contain at least one product
• each product can be on many (possibly zero) orders
• related by the key attributes purchase order number and product number and motif number

Weak Entity: Territory

The double rectangular box labeled Territory is a weak entity. The weak entity divides the customers into the territories in which they reside. This will ensure that each customer is assigned to a salesperson in that territory. When a new customer is added to the system, the user must explicitly specify the territory in which a customer resides.