Entity relationship modelling: example

Simple e-commerce site

The purpose of an e-commerce site is to allow customers to buy products. A customer can place one or more orders, and each order may include one or more products. During the checkout process, the customer is presented with an invoice for the order. Each product is supplied to the e-commerce company by a vendor. One employee of the e-commerce company processes each order, and one employee (who may or may not be the same person) prepares a shipment. A shipment contains only items for a single customer, but because there may be ordering delays, it may contain incomplete orders. Because a customer may make several orders, one shipment may also contain items from several orders.

On the basis of a scenario like this, the job of the database designer is to follow the process outlined above, starting with the identification of significant entities.

Entities

Entity	Explanation
Customer	The e-commerce company needs detail of each customer to be able to send orders to the right place, and for billing
Order	For obvious legal and commercial purposes, orders need to be recorded
Order detail lines	Because an order may consist of several items, each of which has its own price and quantity, each line must be treated as a separate entity
Invoice	An invoice is an important financial document, and records the payment due for an order
Product	Product details need to be shown on the site, and customers must be able to identify the products they want
Vendor	The e-commerce company needs to be able to contact vendors for more stock when a product starts to run low
Employee	As well as setting staffing rotas and making salary payments, it is important to know who processed an order and who prepared a shipment for quality assurance reasons
Shipment	The details of a shipment need to be recorded so that the customer can track deliveries.

Remove duplicate entities

If customers were allowed to make many orders and to pay for them all in one go, it would be necessary to keep orders and invoices as separate entities because there would be a one-to-many relationship between them. In this case, however, there is a one-to-one relationship between them, and so we should consider combining them. In fact the only additional attribute required would be the date that the order was paid for.

List attributes

customer	order	detail_line	product	vendor	employee	shipment
first_name	reference_no	line_number	product_id	name	first_name	shipment_date
last_name	order_date	product_id	name	address	last_name	delivery_date
address	discount	quantity	description	phone_no	address
phone_no	subtotal	unit_price	price	contact_first_name	phone_no
email	delivery		quantity_on_hand	contact_last_name	email
		tax		minimum_stock	vat_no	ni_no
		total
		paid_date

The quantity_on_hand attribute in the PRODUCT entity is for recording the current stock level. A value of 100, for example, would mean that there were 100 units of the product currently available. The minimum_stock_level value is another stock control feature. Once the quantity_on_hand drops below the minimum_stock_level, more product needs to be ordered from the vendor.

If the meaning and use of any of the other attributes are not clear, please be sure to ask for an explanation from the tutor.

Identify primary keys

Every entity needs a primary key in order to guarantee entity integrity (each individual entity or row in a table must be uniquely identifiable). Primary keys can be single attributes or composite keys, made up of several attributes. If no viable key is available, an easy solution is to introduce an id number. This new attribute carries no information, and only serves as a unique key for each entity. It is known as a synthetic key and will be discussed further later.

In the tables below, primary keys are shown underlined, and you can see that most of them seem to be synthetic keys. A primary key must satisfy three criteria: it must always have a value, that value must always be unique and the value must not change. Can you see which of these apply here? Notice too that ORDER has a composite primary key.

One tricky case is the ni_no attribute in EMPLOYEE - surely all National Insurance numbers are unique? One reason for using a synthetic key for employee is purely pragmatic in that where the employee record is first set up, that person's NI number may not be known. The new employee might supply it some time after being taken on. In that case, we don't want to create a bureaucratic delay just because we don't have a particular piece of data. Instead, we want to create the new record and add the missing data later.

customer	order	detail_line	product	vendor	employee	shipment
customer_id	reference_no	reference_no	product_id	vendor_id	employee_id	shipment_id
first_name	order_date	line_number	name	name	first_name	shipment_date
last_name	discount	product_id	description	address	last_name	delivery_date
address	subtotal	quantity	price	phone_no	address
phone_no	delivery	unit_price	quantity	contact_first_name	phone_no
email	tax		minimum_stock	contact_last_name	email
		total			vat_no	ni_no
		paid_date

Define relationships

The relationships between entities can be extracted from the description of the business domain:

One customer may make many orders
One order must consist of one or more detail lines
One product may appear in one or more detail lines
One employee may process one or more orders
One shipment must contain one or more detail line items
One shipment is prepared by one employee
One vendor may supply one or more products

Describe multiplicities

Remember that multiplicity is composed of cardinality and optionality. Cardinality is the easier of the two, but it still needs to be worked out carefully to avoid mistakes. Taking cardinality first, most of the relationships are obvious from the relationships that have been identified. What about the relationship between SHIPMENT and EMPLOYEE though? From the wording above it looks like a one-to-one relationship; however, if you check it in both directions you will see that it is actually one-to-many:

One shipment is prepared by one employee
One employee may prepare one or more shipments

To be sure that your cardinalities are correct, you need to check each relationship like this.

Now thinking about optionality, the key question for each relationship is whether one of the related entities can exist without the other. If so, the relationship is optional in one direction. If each entity may exist without the other, it is optional in both directions. Optionality can be represented in words by careful use of may to indicate an optional relationship and must to indicate a mandatory one. As with cardinality, you need to check every relationship in both directions to avoid mistakes. The final version of the relationships in words looks like this:

One customer may make many orders
One order must be made by one customer
One order must consist of one or more detail lines
One detail line must be related to one order
One product may appear in one or more detail lines
One detail line must refer to one product
One employee may process one or more orders
One order must be processed by one employee
One shipment must contain one or more detail line items
One detail line item may be part of one shipment
One shipment must be prepared by one employee
One employee may prepare one or more shipments
One vendor may supply one or more products
One product must be supplied by one vendor

Each relationship must be represented by a pair of keys. We have already identified the primary keys, but some of the foreign keys are missing and we need to add them now. In the tables below, foreign keys are shown in italic. Remember that the foreign key goes at the many end of the relationship.

customer	order	detail_line	product	vendor	employee	shipment
customer_id	reference_no	reference_no	product_id	vendor_id	employee_id	shipment_id
first_name	order_date	line_number	name	name	first_name	shipment_date
last_name	customer_id	product_id	description	address	last_name	customer_id
address	discount	quantity	price	phone_no	address	delivery_date
phone_no	subtotal	unit_price	quantity	contact_first_name	phone_no
email	delivery	shipment_date	minimum_stock	contact_last_name	email
		tax		vendor_id	vat_no	manager_id
		total				ni_no
		paid_date

Draw E-R Diagram

Final question: can you explain why the relationship from detail_line to shipment is optional?