Data Modeling and Entity Relationship Diagrams

1
Data Modeling and Entity Relationship Diagrams

• Trisha Cummings

2
What is Data Modeling

• Most people involved in application development
  follow some kind of methodology.
• A methodology is a prescribed set of processes
  through which the developer analyzes the client's
  requirements and develops an application.
• One technique commonly used in analyzing the
  client's requirements is data modeling
• The purpose of data modeling is to develop an
  accurate model, or graphical representation, of
  the client's information needs and business
  processes.

3

• The data model acts as a framework for the
  development of the new or enhanced application.
• Data modeling is the process of creating a data
  model by applying a data model theory to create a
  data model instance.
• A data model theory is a formal data model
  description.
• A database model is a theory or specification
  describing how a database is structured and used.
  Several such models have been suggested.

4
Three Basics Styles of Data models

• Conceptual data models. 
• Logical data models (LDMs). 
• Physical data models (PDMs). 

5
Conceptual data models.

•   These models, sometimes called domain models,
  are typically used to explore domain concepts
  with project stakeholders. 
• On Agile teams high-level conceptual models are
  often created as part of your initial
  requirements envisioning efforts as they are used
  to explore the high-level static business
  structures and concepts. 
• On traditional teams conceptual data models are
  often created as the precursor to LDMs or as
  alternatives to LDMs. 

6
Logical data models (LDMs).

•   LDMs are used to explore the domain concepts,
  and their relationships, of your problem domain. 
  
• This could be done for the scope of a single
  project or for your entire enterprise. 
• LDMs depict the logical entity types, typically
  referred to simply as entity types, the data
  attributes describing those entities, and the
  relationships between the entities. 
• LDMs are rarely used on Agile projects although
  often are on traditional projects (where they
  rarely seem to add much value in practice).

7
Physical data models (PDMs)

•   PDMs are used to design the internal schema of
  a database, depicting the data tables, the data
  columns of those tables, and the relationships
  between the tables. 
• PDMs often prove to be useful on both Agile and
  traditional projects and as a result the focus of
  this article is on physical modeling.

8
Database Models

• Common models include
• Hierarchical model
• Network model
• Relational model
• Entity-relationship
• Object-Relational model
• Object model
• Associative
• Concept-oriented
• Entity-Attribute-Value
• Multi-dimensional model
• Semi-structured
• Star schema
• XML database

9

• When data modeling, we are structuring and
  organizing data.
• These data structures are then typically
  implemented in a database management system.
• In addition to defining and organizing the data,
  data modeling will impose (implicitly or
  explicitly) constraints or limitations on the
  data placed within the structure.
• Managing large quantities of structured and
  unstructured data is a primary function of
  information systems.
• Data models describe structured data for storage
  in data management systems such as relational
  databases.

10
Entity Relationship Diagram

• Entity Relationship Diagrams are a major data
  modelling tool and will help organize the data in
  your project into entities and define the
  relationships between the entities.
• This process has proved to enable the analyst to
  produce a good database structure so that the
  data can be stored and retrieved in a most
  efficient manner. 

11
The parts of a Entity Relationship Diagram

• Entity
• Relationship
• Attribute  

12
Entity

• A data entity is anything real or abstract about
  which we want to store data.
• Entity types fall into five classes
• roles, events,
• locations,
• tangible things or
• concepts.

13
Relationship

• A data relationship is a natural association that
  exists between one or more entities.
• Employees process payments.
• Cardinality defines the number of occurrences of
  one entity for a single occurrence of the related
  entity.
• an employee may process many payments but might
  not process any payments depending on the nature
  of her job. 

14
Attribute

• A data attribute is a characteristic common to
  all or most instances of a particular entity.
• Synonyms include property, data element, field.
• Name, address, Employee Number, pay rate are all
  attributes of the entity employee.
• An attribute or combination of attributes that
  uniquely identifies one and only one instance of
  an entity is called a primary key or identifier.
• Employee Number is a primary key for Employee. 

15
Steps

• Identify Entities
• Find relationships
• Draw a rough ERD
• Fill in cardinality
• Define primary keys
• Draw key based erd
• Identify attributes
• Map attributes
• Draw Fully Attributed ERD
• Check Results   

16
A SIMPLE EXAMPLE

• A company has several departments.
• Each department has a supervisor and at least one
  employee.
• Employees must be assigned to at least one, but
  possibly more departments.
• At least one employee is assigned to a project,
  but an employee may be on vacation and not
  assigned to any projects.
• The important data fields are the names of the
  departments, projects, supervisors and employees,
  as well as the supervisor and employee number and
  a unique project number.

17
Identify Entities 

• The entities in this system are Department,
  Employee, Supervisor and Project.
• One is tempted to make Company an entity, but it
  is a false entity because it has only one
  instance in this problem.
• True entities must have more than one instance. 

18
Find Relationships 

• We construct the following Entity Relationship
  Matrix 

  Department Employee Supervisor     Project
Department   is assigned  run by   
Employee belongs to     works on
Supervisor runs      
Project   uses   
19
Draw Rough ERD 

• We connect the entities whenever a relationship
  is shown in the entity Relationship Matrix. 

20
Fill in Cardinality 

• From the description of the problem we see that 
  Each department has exactly one supervisor. 
• A supervisor is in charge of one and only one
  department. 
• Each department is assigned at least one
  employee. 
• Each employee works for at least one department. 
  
• Each project has at least one employee working on
  it. 
• An employee is assigned to 0 or more projects. 

21
(No Transcript)
22
Define Primary Keys 

• The primary keys are Department Name, Supervisor
  Number, Employee Number, Project Number.

23
Draw Key-Based ERD 

• There are two many-to-many relationships in the
  rough ERD above, between Department and Employee
  and between Employee and Project.
• Thus we need the associative entities
  Department-Employee and Employee-Project.
• The primary key for Department-Employee is the
  concatenated key Department Name and Employee
  Number.
• The primary key for Employee-Project is the
  concatenated key Employee Number and Project
  Number

24
(No Transcript)
25
Identify Attributes

• The only attributes indicated are the names of
  the departments, projects, supervisors and
  employees, as well as the supervisor and employee
  NUMBER and a unique project number. 

26
Map Attributes 
Attribute    Entity    Attribute           Entity
Department Name       Department Supervisor Number       Supervisor
Employee Number         Employee Supervisor Name       Supervisor
Employee Name         Employee Project Name          Project
    Project Number         Project
27
Draw Fully Attributed ERD 
28
Check Results 

• Look at your diagram from the point of view of a
  system owner or user.
• Is everything clear? Check through the
  Cardinality pairs.
• Also, look over the list of attributes associated
  with each entity to see if anything has been
  omitted. 

29

• http//www.islandnet.com/tmc/html/articles/datamo
  dl.htm
• http//en.wikipedia.org/wiki/Database_model
• http//www.agiledata.org/essays/dataModeling101.ht
  ml
• http//www.infocom.cqu.edu.au/Courses/spr2000/9516
  9/Extra_Examples/ERD.htm