Entity Relationship Modeling

1

• Entity Relationship Modeling

2
Topics

• Real things and descriptions
• Example for the workshop
• Implementation into RDBMS and XML
• ER models as abstract models
• Modeling as a language task

3
Real things and descriptions
4
The timetable data model
As Relational tables
Route ( routeno, destination, bay)
Departure ( routeno, dtime )
SELECT FROM route NATURAL JOIN departure
5
As XML

• ltroutegt
• ltroutenogt99lt/routenogt
• ltdestinationgtCentre via Blackboy
  Hilllt/destinationgt
• ltbaygt2lt/baygt
• ltdeparturesgt
• ltdtimegt7.00lt/dtimegt
• ltdtimegt720lt/dtimegt
• lt/departuresgt
• lt/routegt

6
ER models are abstract

• Relationships
• Datatypes
• Stored / derived data
• Identity
• Constraints
• Subtypes

7
Types and Subtypes
8
RDBMS implementation

• In Oracle 9i and Postgres, we can implement this
  type/subtype relationship directly, but in SQL-92
  DBMSs we will have to do some work to achieve
  this.
• Generate two tables, one for each of the
  subtypes each table will contain the common
  fields
• salariedEmp (empno, ename, job, mgr, hiredate,
  sal, comm)
• contractEmp (empo, ename, job, mgr, hiredate,
  hourlyRate, contractHours)
• Generate three tablesone for the supertype and
  one for each of the subtypes
• emp (empno, ename, job, mgr, hiredate)
• salariedEmp (empno, sal, comm)
• contractEmp (empno, contractHours)

9
Modeling and Language

• Modeling as Lego
• you need the ability to understand how the UoD is
  structured (analysis), you need knowledge of how
  all the different kinds of blocks work, what fits
  with what and then the ingenuity to use the
  blocks to create the closest possible model
• misapprehensions

10
Creativity in naming

• Guidelines
• names of entities must be singular, not plural
  naming the entity type, not the entity set
• the name of entity should match the name of the
  real-world concept - employee in the real world
  is modeled by employee in the information system,
  not by employeeRecord, or employeedata, or
  employeedetails
• Names should be based on user terminology

11
What to model

• Identify the model boundary
• Depends on purpose of system
• Reject things outside the boundary
• E.g. users, stakeholders, external agencies
  unless data required to be held
• Reject things in the implementation
• E.g. database, report
• (agile modelling) Model only to the detail
  immediately required

12
Evaluating models

• How do you know how good they are?
• Generate instances of the model

dept
employs
emp
manages
13
Experienced Modelers

• Martin Fowler, Analysis Patterns reusable
  object models Addison Wesley Longman ,1997
• David C. Hay, Data Model Patterns
  conventions of thought Dorset House, New York,
  1996
• Models on-line
• Pet shop store
• Inherent in XML specification