Conceptual model

Conceptual modelA conceptual model is a model of the system, represented as an entity-relationship (E-R) model. This topic will introduce you to:

  • What entity-relationship diagrams (E-R diagrams) are
  • Entity-Relationship Model constructs, and
  • How to create the entity-relationship diagrams (E-R diagrams)

 

What are E-R diagrams?

Textbook reading
Ch2 pp 97-99

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.

Textbook reading
Ch2 pp 99-100

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

Textbook reading
Ch2 pp 106-126

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.

Textbook reading
Ch2 pp 131-132

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
Tip
All names must be unique and descriptive.

 

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

Getting your hands dirty

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:

  1. Associative Entities
  2. Assumptions
  3. Attributes
  4. Entities
  5. Primary Key
  6. Relationships

View the answer

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)

Tip
Singular nouns is the naming convention used for entity names

 

 

Type your ideas here

View the answer

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.

View the answer

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

Tip
Relationships should be stated as verbs to follow naming conventions.

 

 

2. Add the relationship that each rule specifies to your E-R diagram.

Rule 1 A customer can receive many invoices
View the answer

Rule 2 Each invoice is for one customer only
View the answer

Rule 3 An invoice can contain more than one item
View the answer

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
View the answer

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?

View the answer

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.

Tip
Deciding to convert a many-to-many relationship to an associative entity will always be a judgement cal. Ternary or nary relationships should always be converted to associative entities.

 

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.
View the answer

Tip
To easily recognise what entities the associative entity is involved with, combine the names of the related entities (this is the only time the naming rule can be broken). Also, the direction of the connectivity has been reversed. This diagram states:
    • 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.
View the answer

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
Tip
An attribute name is a noun that can have multiple words. Also, a databasse designer may need to use aliases in order to keep data names unique. One alias has already been provided (CustABN).

 

1. Review the scenario

  • Open the word file Scenario
  • Using your mouse/pen, identify and highlight all the data (i.e. attributes)

View the answer

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.

CUSTOMER
Data item Alias
INVOICE
Data item Alias
INVOICEITEM
Data item Alias
ITEM
Data item Alias

View the answer

CUSTOMER
Data item Alias
ABN CustABN
Business Name CustName
Address CustAddress
INVOICE
Data item Alias
Invoice number InvoiceNo
Date InvDate
Invoice total InvTotal
INVOICEITEM
Data item Alias
LineNo LineNo
Quantity Quantity
Subtotal LineSubtotal
ITEM
Data item Alias
Item number ItemNo
Description ItemDesc
Price ItemPrice

Clearly your names are unlikely to be identical to the example.

Points to note:

  • Address is a composite attribute.
  • InvTotal and SubTotal are 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 the INVOICE entity, 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 the INVOICEITEM associative entity.
  • LineSubtotal uses 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
View the answer

ER 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.
View the answer

ER 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

View the answer

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

Tip
Primary keys are represented with an underline.

 

 

a) Using an associative entity.
View the answer

ER diagram

b) Using a many-to-many relationship
View the answer

ER diagram

Include any assumptions made (this can also contain data definitions).

1. Using an associative entity.
What are the assumptions?

View the answer

ER diagram

Assumptions

  1. Business name and address have been prefixed with cust in CUSTOMER to keep the attribute names unique
  2. The same applies for invoice number and date in INVOICE and item number, description and price in ITEM
  3. To follow the business rules LineNo is placed in the associative entity INVOICEITEM as a primary key
  4. Quantity is placed in INVOICEITEM to allow a separate quantity for each item on the invoice.
  5. LineSubtotal in INVOICEITEM is calculated by: Quantity * ItemPrice.
  6. InvTotal in INVOICE is calculated by: sum of LineSubtotal
Tip
For a professional product, on the E-R diagram you should include reference information: date, title, author.

 

2. Using a many-to-many relationship
Which of your assumptions will change?

View the answer

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:

ER diagram

Assumptions

  1. Business name and address have been prefixed with cust in CUSTOMER to keep the attribute names unique
  2. The same applies for invoice number and date in INVOICE and item number, description and price in ITEM
  3. Quantity and LineSubtotal are placed in Contains to allow a separate quantity and subtotal for each item on the invoice
  4. LineSubtotal in INVOICEITEM is calculated by: Quantity * ItemPrice.
  5. InvTotal in INVOICE is calculated by: sum of LineSubtotal

3. Which of the two E-R diagrams are better?

View the answer

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 review

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 review

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
View the answer

Company Projects E-R diagram

Grade Report

Review the scenarioreview

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 review

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
View the answer

Grade Report  E-R diagram

Real Estate Firm

Review the scenarioreview

  • 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 review

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
View the answer

Real Estate Firm  E-R diagram

Courts

Review the scenarioreview

  • 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 review

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
View the answer

Courts E-R diagram