Entity Relationship Modelling

1
Entity Relationship Modelling

• Database Development
• A Relational model
• Its E-R equivalent
• E-R notation
• Developing an E-R Model
• E-R difficulties

2
Database Development

• From UoD requirement
• To implemented system
• How to structure the activity?
• Dont do everything at once
• Sequence decisions - Users first, technology
  last
• Relational model too close to technology

3
Why not Relational schema directly?

• Which DBMS will be used?
• Oracle - 7, 8, 9, Jet, MySQL?
• Maybe another Data model altogether?
• OLAP, Object oriented, Logical, XML
• Relational schema too far from the user -
• some concepts are not user terms
• e.g. primary keys, foreign keys
• many tables in schema are artifacts
• e.g. resolving many-many relationships
• some relationships not explicit in schema
• e.g. employee and salgrade, no self-managers

4
A Relational Schema

• Dept (Deptno, Dname, Loc)
• Emp(Empno, ename, job, mgr, hiredate, sal, comm,
  deptno)
• Salgrade(grade, losal, hisal)
• Only tables and columns, but columns used for
  different purposes -
• what are they?

5
Different kinds of column

• Primary key - Underline
• Foreign Key - Italic
• Attribute

6

• Dept (Deptno, Dname, Loc)
• Emp(Empno, ename, job, mgr, hiredate, sal, comm,
  deptno)
• Salgrade(grade, losal, hisal)

7
Entity Relation model

• More abstract - not specific to relational data
  model
• Entity - cf- Table
• Relation - cf Primary Key/Foreign key
• Attribute - cf most columns
• Sometimes called an EAR model

8
E-R ltgt Relational Schema

• Relational Schema gtgtgt E-R Model
• We will first use E-R models as a way to
  represent a Relational Schema, using the example
  database from the SQL workbook
• Uod gtgtgt E-R model gtgtgt Schema
• We will then use an E-R model as a step in
  translating user requirments into a database
  schema

9
E-R model of Employee DB

• Entity Department
• Attributes Name, Location
• Entity Employee
• Attributes Name, Job, Hiredate, MonthlySalary,
  Commission
• Entity SalaryGrade
• Attributes Grade, losal, hisal
• Relationship
• Manages (Employee, set of Employee)
• Relationship
• Employs(Department,set of Employees)

10
E-R Diagrams

• E-R models are often represented as diagrams.
• Diagrams help to show Relationships clearly
• No single notation, so ensure you use a legend to
  explain your notation

11
E-R diagrams

• Entity
• Relationship
• 1 to a set of (many)
• Attribute

Department
name
Employee
12
E-R instance diagram
D2
D1
E2
E5
E3
E4
E1
13
Entity Questions

• Entities are complex things of interest in the
  UoD
• Entity corresponds to Table in Relational schema

14
Entities
Department
SalaryGrade
Employee
15
Attribute questions

• Is EmpNo an attribute
• If EmpNo already used in UoD, eg by an existing
  system, then it is an attribute
• If it is not of interest to the user, and just
  used to link tables, then ignore it

16
Attributes
Name
Department
Location
SalaryGrade
Grade
Name
Employee
Hisal
Job
Hiredate
Losal
AnnualSalary
Commission
17
Employs relationship

• Meaning - department currently employs - note
  that this changes over time
• Constraints
• every employee must work for exactly one
  department
• a department may employ zero, one or more
  employees
• Cardinality is 1 to 0.N

18
Relationships - Employs
Name
Department
Location
employs
SalaryGrade
Grade
Name
Employee
Hisal
Job
Hiredate
Losal
AnnualSalary
Commission
19
Manages relationship

• Every employee except the top guy has exactly one
  manager
• Employees manage zero, one or more employees
• also
• a manager cannot manage themselves - directly or
  indirectly

20
Relationship - Manages
Name
Department
Location
manages
employs
SalaryGrade
Grade
Name
Employee
Hisal
Job
Hiredate
Losal
AnnualSalary
Commission
21
Relationship Employee-grade

• In the database this was computed or derived
  by finding the grade such that
• losal lt sal lt hisal
• We can show such relationships in E-R (but more
  common in an Object model)

22
Name
Department
Location
manages
employs
SalaryGrade
Grade
Name
Employee
Hisal
Job
Hiredate
Losal
AnnualSalary
Commission
23
From UoD to E-R model

• Which Entities?
• Entity - Attribute - Relationship ambiguity
• Entity or Instance of Entity
• Many to Many relationships

24
Which Entities?

• Nouns in description of Uod become entities in
  E-R
• Is Job an entity
• Is Location an entity
• Is information required to be held about the term
  - details of responsibilities for Job, full
  address for Location.

25
Entity - Attribute - Relationship ambiguity

• Suppose we need to know if an employee is married
• Attribute - married - True/False
• Attribute - name of spouse
• Relationship - married to (another employee)
• Entity - marriage, with date of marriage, date of
  divorce etc

26
Entity or Instance of Entity

• Each Entity is a class of entity instances -
• employee entity has (14) entity instances
• Book / Copy problem
• Book has a title, author, ISBN
• Copy has an accession no, location, condition,
  onloan

Copy
Book
27
Many to Many relationships

• Suppose employees move between departments, so
  that over a period of time an employee is
  employed by several departments

Department
employs
Employee
28
Many to Many gtgt 2 One to Many
Department
Hiredate
Employment
Employee
29
Next week

• Lecture Normalization
• Chapter 23
• Tutorial E-R modelling exercises