What are E-R diagrams?
Ch2 pp 91-93
Developed by P Chen in 1976, the E-R model conceptually describes the relationships between entities. This model can be represented using entity-relationship diagrams (E-R diagrams).
The sample E-R diagram and next textbook reading, The E-R Model: An Overview give an introduction to E-R diagrams. Review these two pages.
Ch2 pp 93-95
E-R Model Notation
There is no standard notation for E-R modelling. This unit
will use the notation from the text, which is a combination of Chen and
Crow's Foot. The following textbook reading, E-R Model Notation
outlines this E-R notation.
Your text uses notation to describe cardinality and relationships that will not be used in this unit. Take particular note. The next reading explains what notation will be used.
E-R model constructs
Ch2 pp 100-117
The next two readings Modelling Entities and Attributes and Naming and Defining Relationships cover the basic E-R model constructs that we will be using.
Ch2 pp 126-127
As you read, note the following important aspects:
- Types and Instances: Pay particular attention
to the difference
between types and instances. - Strong versus Weak entity types
- Cardinality constraints: As mentioned
previously, cardinality
constraints will not be used. - Naming constructs:
- An entity is named with a singular noun
- A relationship is named with a verb-phrase
- An attribute is named with a noun name that can have multiple words
Creating E-R diagrams
A step-by-step approach to building E-R diagrams based on business rules is outlined in the next short reading. This will help you get started drawing E-R diagrams.
When creating E-R diagrams:
- Always label your diagrams with a title
date and author. A database
designer may end up creating a number of drawings to properly
represent the system. - Tools
- You can use MS Visio if you wish
- Diagrams can be hand drawn
- Created in MS Word or any other drawing package you with to use
- Diagrams must be readable
Create an E-R diagram for the Honest Bob's Discount Warehouse Scenario
Over the next 6 tabs, you are going to draw an E-R diagram. To begin, review the E-R diagram development process.
1. Arrange the following tasks in a logical order to create an E-R diagram:
- Associative Entities
- Assumptions
- Attributes
- Entities
- Primary Key
- Relationships
1. Entities
2. Relationships
3. Associative Entities
4. Attributes
5. Primary Key
6. Assumptions
2. Read the scenario to see how these tasks could occur.
Identify all of the entities involved
1. From the scenario, identify the entities (HINT: there are 3)
Type your ideas here
The entities are:
1. CUSTOMER (The organisation has a number of customers...
)
2. INVOICE (When a customer purchases products they are
given an invoice...)
3. ITEM ( ... that lists the items
purchased)
2. Draw the E-R diagram.
On a piece of paper draw the entities that were identified as the first
step in creating your E-R diagram.
Determine the relationships between each entity
1. Review the business rules you identified for the scenario.
Rule 1. A customer can receive many
invoices
Rule 2. Each invoice is for one customer
only
Rule 3. An invoice can contain more than
one item
Rule 4. An item can appear on an invoice
more than once
Rule 5. An item can appear on many
invoices
Each rule represents part of a relationship
2. Add the relationship that each rule specifies to your E-R diagram.
Rule 1 A customer can receive many invoices
Rule 2 Each invoice is for one customer only
Rule 3 An invoice can contain more than one item
Rule 4 An item can appear on an invoice more than once
This rule does not concern us at the moment, but
we will return to it later.
Rule 5 An item can appear on many invoices
Decide if any of the relationships are candidates to be associative entities
1. Review your E-R diagram so far
There is an issue with this diagram:
- what is it? and,
- what are the two possible options to deal with this issue?
There is a many-to-many binary relationship between INVOICE and ITEM. Many-to-many relationships are not directly implemented into tables in a relational database.
They should be:
- Either converted to associative entities
- Or, leave the many-to-many relationship until the E-R diagram is mapped to tables (in other words do nothing at this stage).
Regardless of the options taken, at the end of the process the resulting tables will be the same.
In the rest of this E-R diagram process you will create both options.
2. Using an associative entity.
This is where Rule 4 is considered
Rule 4 An item can appear on an invoice more than once.
• An INVOICE can have many INVOICEITEMs
• An ITEM can be in many INVOICEITEMs
This obeys business rules 3 and 5.
3. Using the many-to-many relationship
If the designer chooses not to convert to an associative entity, the many-to-many relationship remains; business Rule 4 will apply in the mapping process.
Redraw the E-R diagram with the many-to-many relationship.
Now you should have 2 E-R diagrams that you will complete over the remaining steps.
Identify the attributes for each entity
For each entity in turn, identify the attributes paying particular attention to:
- Multi-value attributes
- Derived attributes
- Composite attributes do not need to be broken down to their atomic form at this point
CustABN).
1. Review the scenario
- Open the word file Scenario
- Using your mouse/pen, identify and highlight all the data (i.e. attributes)
Honest Bob’s Discount Warehouse of 23 Dubious Lane Cheapside NT 8023 needs a computerised invoicing system. Because Honest Bob is a cheapskate they are still using a paper-based system.
The organisation has a number of customers, which are all businesses (Honest Bob’s does not deal with the general public, which avoids all the problems with GST). Information kept on customers includes their ABN (CustABN), business name and address (which includes the street, suburb, state and postcode). Because Honest Bob’s is keen on keeping their customers and having them return time after time, the prices there are suspiciously cheap.
When a customer purchases products they are given an invoice that lists the items purchased. Generally customers purchase a number of different items of varying quantities each time. If a customer decides that they haven’t got enough of a particular item, instead of increasing the quantity for that item, Honest Bob’s staff just add it again to the bottom of the invoice and give the new, updated copy to the customer.
Details on the invoice include the customer details, an invoice number, a date, an item number, its description, price, quantity ordered, subtotals for each item line and an invoice total.
2. Using an associative entity
Place each item of data you highlighted, in one of the tables below. Give each item of data an alias (name) you will use in your database.
|
|
||||||||||||||||||||
|
|
||||||||||||||||||||
|
|
||||||||||||||||||||
|
|
||||||||||||||||||||
Clearly your names are unlikely to be identical to the example.
Points to note:
- Address is a composite attribute.
InvTotalandSubTotalare calculated, therefore theyy are derived attributes.- Business rule 4 states "An item can appear on an invoice more
than once.", therefore, an artificial unique identifier (
LineNo) needs to be created. - Quantity ordered is also placed in
INVOICEITEM. If quantity was placed in theINVOICEentity, the organisation could only have one quantity per invoice; therefore only one item could be sold for each invoice. This contravenes business Rule 3. An invoice can contain more than one item. If quantity were placed in the ITEM entity, this would mean that you could only ever sell an item once. So, the only logical place for quantity is theINVOICEITEMassociative entity. LineSubtotaluses Quantity in its calculation, therefore it must be placed in the same place as Quantity. Because is calculated, it is a derived attributes.
Add the attributes to the E-R diagram
3. Using the many-to-many relationship
Both versions of the E-R diagram (regardless of the associative
entity) have the CUSTOMER, INVOICE and ITEM
entities. These entities remain the same in the E-R diagram.
What happens to the information in the INVOICEITEM?
Where would it go on the E-R diagram?
Represent this on the E-R diagram.
In the many-to-many version, the same issues arise with quantity. As
we know relationships can have attributes, so quantity and LineSubtotal
are placed in the relationship Contains rather than the
entities INVOICE or ITEM.
Identify the primary key for each individual instance of an entity
1. Identify the primary keys
What is the primary key for each of the 3 entities
| Table | Primary Key |
|---|---|
CUSTOMER |
|
INVOICE |
|
ITEM |
What is the primary key for the associative entity if used?
INVOICEITEM |
| Table | Primary Key | |
|---|---|---|
CUSTOMER |
CustABN |
It's the Australian Business Number supplied by the federal government |
INVOICE |
InvoiceNo |
Each invoice has its own number |
ITEM |
ItemNo |
Just about all products have their own number (product number, part number, etc). |
INVOICEITEM |
LineNo |
The artifical unique identifier |
2. Add the primary keys to the E-R diagram
a) Using an associative entity.
b) Using a many-to-many relationship
Include any assumptions made (this can also contain data definitions).
1. Using an associative entity.
What are the assumptions?
Assumptions
- Business name and address have been prefixed with cust in
CUSTOMERto keep the attribute names unique - The same applies for invoice number and date in
INVOICEand item number, description and price inITEM - To follow the business rules LineNo is placed in the associative
entity
INVOICEITEMas a primary key - Quantity is placed in
INVOICEITEMto allow a separate quantity for each item on the invoice. - LineSubtotal in
INVOICEITEMis calculated by:Quantity * ItemPrice. - InvTotal in
INVOICEis calculated by: sum ofLineSubtotal
2. Using a many-to-many relationship
Which of your assumptions will change?
Assumption 3 is not included
Assumption 4 becomes:
Quantity and LineSubtotal are placed in Contains to allow a
separate quantity and subtotal for each item on the invoice
The final E-R diagram would b represented as follows:
Assumptions
- Business name and address have been prefixed with cust in
CUSTOMERto keep the attribute names unique - The same applies for invoice number and date in
INVOICEand item number, description and price inITEM -
QuantityandLineSubtotalare placed in Contains to allow a separate quantity and subtotal for each item on the invoice - LineSubtotal in
INVOICEITEMis calculated by:Quantity * ItemPrice. -
InvTotalinINVOICEis calculated by: sum ofLineSubtotal
3. Which of the two E-R diagrams are better?
Neither, it is up to the designer. Both options will produce the same tables in a relational database after they have been mapped. This is the next topic.
Scenarios
For each of the Scenarios you identified the business rules for in the last topic, draw the E-R diagram.
Company projects
Review the scenario 
A company has a number of employees. The attributes of employee include employee_id (identifier), name, address, and birthdate. The company also has several projects. attributes of project include project_id (identifier), project_name, and start_date. Each employee may be assigned to one or more projects, or may not be assigned to a project. A project must have at least one employee assigned and may have any number of employees assigned. An employee’s billing rate may vary by project, and the company wishes to record the applicable billing rate (billing_rate) for each employee when assigned to a particular project.
Review the business rules you found
Rule 1: An employee can be assigned to many projects
Rule 2: A project can have many employees
Rule 3: An employee's billing rate can vary for each
project
1. Draw the E-R diagram for the Company project
Grade Report
Review the scenario
| MILLENIUM COLLEGE GRADE REPORT FALL SEMESTER 200X |
||||
| NAME: | Emily Williams | ID: 268300458 | ||
| CAMPUS ADDRESS: | 208 Brooks Hall | |||
| MAJOR: | Information Systems | |||
| COURSE | TITLE | INSTRUCTOR NAME | INSTRUCTOR LOCATION | GRADE |
|
|
||||
| IS350 | Database Mgt | Codd | B104 | A |
| IS465 | System Analysis | Parsons | B317 | B |
Review the business rules you found
Rule 1: A student can enrol in many courses (units)
Rule 2: A student receives a grade for each course
Rule 3: A course is taught by only one instructor
Rule 4: A course can have many students
Rule 5: An instructor can teach many courses
2. Draw the E-R diagram for the Grade report
Real Estate Firm
Review the scenario
- The firm has a number of sales offices in several states. Attributes of sales office include Office_Number (identifier) and Location.
- Each sales office is assigned one or more employees. Attributes of employee include Employee_ID (identifier) and Employee_Name. An employee must be assigned to only one sales office.
- For each sales office, there is always one employee assigned to manage that office. An employee may manage only the sales office to which he or she is assigned.
- The firm lists property for sale. Attributes of property include Property_ID (identifier) and Location. Components of Location include Address, City, State and Zip_Code.
- Each unit of property must be listed with one (and only one) of the sales offices. A sales office may have any number of properties listed or may have no properties listed.
- Each unit of property has one or more owners. Attributes of owners are Owner_ID (identifier) and Owner_Name. An owner may own one or more units of property. Owners own a percentage of a property.
Review the business rules you found
Rule 1: A sales office has many employees
Rule 2: An employee is assigned to one sales office
Rule 3: A sales office has one employee who is a
manager
Rule 4: A manager must be an employee assigned to a
sales office
Rule 5: A sales office has many properties listed
Rule 6: A property is only listed with one sales
office
Rule 7: A property can have many owners
Rule 8: An owner can own many properties
Rule 9: Each owner owns a percentage of a property
3. Draw the E-R diagram for the Real Estate firm
Courts
Review the scenario
- An attorney is retained by one or more clients for each case.
- Attributes of attorney are: attorney_id, name, address, city, state, zip_code, specialty (may be more than one), bar (may be more than one).
- A client may have more than one attorney for each case.
- Attributes of client are client_id, name, address, city, state, zip_code, telephone, date of birth.
- A client may have more than one case.
- Attributes of case are case_id, case_description, case_type.
- An attorney may have more than one case.
- Each case is assigned to one and only one court.
- Attributes of court are court_id, court_name, city, state, zip_code.
- Each court has one or more judges assigned to it.
- Attributes of judge are judge_id, name, years in practice,
- Each judge is assigned exactly to one court.
Review the business rules you found
Rule 1: An attorney can have many clients for a
case
Rule 2: A client can have many attorneys for a case
Rule 3: A client can have many cases
Rule 4: An attorney can have many cases
Rule 5: A case is assigned to one court
Rule 6: (Implied) A court can have many cases
Rule 7: A court can have many judges
Rule 8: A judge is appointed to one court
4. Draw the E-R diagram for the Courts
