CSE1IS Information Systems
Week 7

Systems Design Overview & Entity-Relationship Diagrams

Introduction to Systems Design:

So far, in Systems Analysis we have defined requirements for the new system:

• data structures and data elements
  • input masks, validation rules
• processes
  • logic involved within each process
• development environment
  • assume an alternative has been accepted by management
• presented within a Systems Requirements Document

Systems Design is the next major phase in the SDLC.
The major deliverable from the design phase will be a Systems Design Specification.
There are several major components of a system which will need to designed:

• Files/Database --- the stored data
• The User Interface --- the dialogue between the user and the system - templates, menu hierarchy etc.
• Input/Output --- the actual screens and reports used or produced by the system
• Software  --- the software that will drive the system
• Procedures --- the manual and automated procedures (logic) necessary for successful system operation

Data Design: Entity-Relationship Diagrams:

(S.C.&R. Chapter 7)

• E-R Diagrams are a general method of describing data.
• There are a number of notations for E-R Diagrams

MS-Access E-R Diagram:

Standard E-R Diagram:

Extended E-R Diagram:

Components of an E-R Diagram:

Entity:

• A person, thing, or event for which data is collected.
• Usually falls into one of the following categories:
  • person : customer, salesperson, student
  • place : town, stadium
  • object : inventory, aeroplane
  • event : registration, race
  • concept : order, invoice, subject , course
• An entity usually has attributes
• An entity becomes a table in the database

Attributes (or column or field):

• An entity can have attributes.
• The key attribute name is underlined.
• Composite attribute : can be further broken down
  eg. name into first and last.
• Multivalued attribute : may have many values for each instance. Denoted by the double-lined ellipse.
• Primary Key:
  • is an attribute or set of attributes that uniquely identify each record of an entity.
  • eg. customer# in the customer table
• Candidate Key:
  • is another attribute or set of attributes that uniquely identify each record of an entity
• Foreign Key:
  • is an attribute or set of attributes of an entity that link to another entity (via a relationship)
  • eg. customer# in the orders table
• Secondary Key:
  • an attribute or set of attributes other than the primary key that is often used to select records
  • does not have to be unique
  • usually, an index is created on identified secondary key field(s)

Relationship:

• Relates (associates) two entities together.
• Use a verb (action word) or small phrase to describe the relationship.
• Cardinality: the numeric relationship between two entities
• Cardinality values within relationships:
  • 1:many  (1:m)  -  eg. an order is for one customer, but a customer can have many orders
  • many:many (m:n)   -  eg. an order can contain many items and each item can be on many orders
  • 1:1  -  eg. one staff member is head-of-department
            sometimes, the entities involved in a 1:1 relationship can be combined into a single entity.
• m:n relationships may also have attributes ==> associative entity.

Notation:

• Entities use a rectangular box
• Relationships a diamond
• Associative Entities can be depicted as a diamond within a rectangle
• Simple Crow's-Foot notation:

  0ne-to-one (1:1)

  
  
  

  0ne-to-many (1:M)

  
  
  

  Many-to-many (M:N)

  
  
  

• More specific Crow's Foot notations:

  exactly one

  
  
  

  one or more

  
  
  

  zero or one (Optionally one)

  
  
  

  zero or more (Optionally many)

  

  Note that these cardinality notations cannot display specific minimum/maximum values, such as (0,6).
  
  
• S.C.&R. Fig 7-19 (gif image 34Kb) displays the extended Crow's foot notation, and Fig 7-20 provides some examples:
  
  

Guidelines for Construction of an Entity-Relationship Diagram:

Identify entities:

• An entity is a person, place, object, event or concept, and usually can be identified by a single, unique attribute.
• Usually, external entities from your DFD are entities for the ERD.
• Data flows involving composite data structures, such as invoice, are also entities
• Data Stores often are based on an entity
• Generally, if something has attributes, it is an entity.
• Provide a meaningful name for the entity and key attribute.
• List Business Rules concerning the identification of the key attribute.
• For example:
  • A small ordering operation where sales representatives visit customers to take orders. Products are stored in a series of warehouses.
  • Some possible entities that may arise during your analysis could be:
    customers, orders, products, sales representatives, warehouse
  • In other words you find out that we need to know:
    - who our customers are,
    - the products we are selling,
    - which orders have been placed,
    - who our sales representatives are,
    - etc.
  • Note that some of these are people and some may have been identified from documents such as customer orders, product
    listings etc.
  • Document collection is a very important source of information for E-R and data modelling.
  • Also realize that for each the entities listed above there might quite a bit of information that we will need to store about each them.
  • For example we may need to know the names of our customer, where they live, how much they owe us etc.

Identify Attributes for each entity:

• The Data Dictionary can be used to identify attributes for each entity
• Identify the key attribute(s) for each entity.
• Often, this is added as an extra attribute - using a sequential numbering system for the records
• List appropriate business rules for each attribute.
  - customer# must be unique
  - name cannot be left empty
  - credit_rating must be one of: ’okay’, ’under_review’ or ’bad’
• Examples
  • customer (customer#, name, address, credit_rating, .....)
  • order (order#, order_date ......)
  • product (product#, description, price ......)
  • sales representative (salesrep#, name, address .......)
  • warehouse ( wname , address.....)

Identify Relationships:

• A relationship is a small description of how two entities relate to each other
• Format:
  entName1   relPhrase   entName2
• Specify the business rule(s) pertaining to the relationship, as these rules should make the cardinality clear(er).
• Provide a meaningful name for the relPhrase, usually a verb (doing word) or phrase.
• Draw an E-R diagram for each relationship, comprising only the relationship, the entities involved and cardinalities.
• Examples:
  • a customer places an order
  • a sales representative serves a customer
  • an order lists products
  • a warehouse stores product
  • initial ER diagrams (jpeg image 74Kb)
• Combine into a single ERD (jpeg image 47Kb)
• ERD after adding cardinalities (could also use Crow's Feet):
  
   
• Alternatively, attributes can also be shown as in this diagram: ERD4 (jpeg image 78Kb)

Many-to-Many Relationships:

• m:n relationships (Associative Entities) must be converted into a new entity
• eg.: stores relationship above is converted into product-location table
• Diagram using M:N notation:
  

Final Diagram:

• Combine all of your single relationship diagrams into a single E-R diagram.
• Construction of E-R diagrams is an iterative process.
• When defining relationships or constructing the full E-R diagram, new entities, attributes or relationships may be identified.
• 

Example:

S.C.&R. P354, Apply Your Knowledge, Question 1(2):

Assume that the Pick and Shovel's main entities are its customers, employees, projects, and equipment. A customer can hire the company for more than one project, and employees sometime work on more than one project at a time. Equipment, however, is assigned only to one project. Draw an ERD showing those entities.

References:

• Shelly, Cashman & Rosenblatt, Systems Analysis and Design, 6th Edition,  Course Technology, 2006.

copyright © 2006 Brian Retallick. This page last updated on Thursday 28 August 2008 by Noel McEwan, La Trobe University, Bendigo