Lecture 2 entity relationship modelling
This presentation is the property of its rightful owner.
Sponsored Links
1 / 29

Lecture 2: Entity/Relationship modelling PowerPoint PPT Presentation


  • 94 Views
  • Uploaded on
  • Presentation posted in: General

Lecture 2: Entity/Relationship modelling. www.cl.cam.ac.uk/Teaching/current/Databases/. Dr. Peter Chen http://bit.csc.lsu.edu/~chen/. Database design lifecycle. Requirements analysis User needs; what must database do? Conceptual design High-level description; often using E/R model

Download Presentation

Lecture 2: Entity/Relationship modelling

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Lecture 2 entity relationship modelling

Lecture 2: Entity/Relationship modelling

www.cl.cam.ac.uk/Teaching/current/Databases/

Dr. Peter Chen

http://bit.csc.lsu.edu/~chen/


Database design lifecycle

Database design lifecycle

  • Requirements analysis

    • User needs; what must database do?

  • Conceptual design

    • High-level description; often using E/R model

  • Logical design

    • Translate E/R model into (typically) relational schema

  • Schema refinement

    • Check schema for redundancies and anomalies

  • Physical design/tuning

    • Consider typical workloads, and further optimise

Today


Today s lecture

Today’s lecture

  • E/R modelling

    • Entities

    • Attributes

    • Relationships

    • Constraints on relationships

  • Extended E/R modelling

    • Object ideas


Conceptual design

Conceptual Design

  • What are the entities and relationships in the enterprise?

  • What information about these entities and relationships should we store in the database?

  • What are the integrity constraints (business rules) that hold?

  • We can represent this information pictorially in E/R diagrams (and then map these to a relational schema later).


E r basics

E/R basics

  • An entity is a real-world object that is distinguishable from other objects

  • Each entity has attributes (with domains)

  • A particular entity will have a value for each of its attributes

  • An entity type defines a set of entities that have the same attributes

  • An entity set is the collection of all entities of a particular entity type (at a particular point in time)


Entities and attributes

Name

NI

dob

Employees

Entities and attributes

  • Entity types are drawn as rectangles, e.g.

  • Attributes are drawn as ovals, and attached to the entity sets with lines, e.g.

Employees


Key attributes

Name

NI

dob

Employees

Key attributes

  • A key attribute of an entity type is an attribute whose values are distinct for each entity

  • Sometimes several attributes (a composite attribute) together form a key

    • NB: Such a composite should be minimal

  • We underline key attributes


Relationships

Relationships

  • A relationship type among two or more entity types defines a set of associations between entities from those types

    • Mathematically, relationship type R

      R  E1   En.

  • The set of instances of the relationship type is called the relationship set


Relationships in e r

Name

dname

NI

DID

dob

budget

Employees

Departments

Relationships in E/R

  • Relationship types are represented by diamonds

  • They connect the participating entity types with straight lines, e.g.

Works_in


Relationship set diagrams

Relationship set diagrams

  • Sometimes its useful to represent the relationship set diagrammatically

r1

d1

e1

r2

d2

e2

e3

r3

d3

e4

d4

r4

e5

d5


Relationship attributes

since

Works_in

Name

dname

NI

DID

dob

budget

Employees

Departments

Relationship attributes

  • Relationships can also have attributes

    • NB: A relationship must be uniquely determined by the entities, without reference to the relationship attributes


N ary relationships

since

Works_in2

Name

dname

NI

DID

dob

budget

address

Locations

capacity

Employees

Department

N-ary relationships

  • Although relatively rare, we can have n-ary relationships, e.g.


Recursive relationships

Each entity type in a relationship plays a particular role, which is associated with a role name (this is usually suppressed)

An recursive relationship is when an entity type plays more than one role in the relationship type

In this case the role name is required

Recursive relationships


Recursive relationships in e r

name

dob

NI

Employees

supervisor

subordinate

Reports-to

Recursive relationships in E/R

e.g.


Constraints on relationship types

Constraints on relationship types

  • For example:

    • An employee can work in many departments; a department can have many employees

    • In contrast, each department has at most one manager

  • Thus we need to be able to specify the number of relationship instances that an entity can participate in.

  • For binary relationships the possible ratios are: 1:1, 1:N, N:1, M:N


Cardinality ratios

Cardinality ratios

1:1

1:N

M:N


Cardinality ratios in e r

Cardinality ratios in E/R

N

M

M:N

1

N

N:1

1

1

1:1

Note: Sometimes this is written using different arrowheads


Participation constraints

Participation constraints

Every department must have a manager

  • This is an example of a participation constraint

  • The participation of an entity set, E, in a relationship set R is said to be totalif every entity in E participates in at least one relationship in R. (If not its participation is said to be partial)


Participation in e r diagrams

Name

dname

NI

DID

dob

budget

Employees

Department

Participation in E/R diagrams

  • Total participation is displayed as a bold line between the entity type and the relationship

    • NB. Sometimes this is written as a double line

since

N

Manages

1


Weak entity types

Weak entity types

  • An entity type may not have sufficient attributes to form a primary key

  • Such an entity type is called a weak entity type

  • A weak entity can only be identified uniquely by considering the primary key of another (owner) entity


Weak entity types cont

pName

age

Cost

Name

NI

N

1

Policy

Dependents

Employees

Weak entity types cont.

  • Thus the owner and weak entity types must participate in a 1:N relationship

  • Weak entity set must have total participation in this identifying relationship set.


Extended e r modelling

Extended E/R modelling

  • What we’ve seen so far is “classic” E/R

  • Over the years a number of features have been added to the model and the modelling process

  • These features include:


Isa hierarchies

Name

NI

dob

Employees

ISA

hours

cid

rate

Contract_Emp

Temp_Emp

ISA hierarchies

  • We can devise hierarchies for our entity types

  • If we declareA ISA B, everyA entity is considered to be a B entity


Attribute inheritance

Attribute inheritance

  • As we’d expect, attributes of superclasses are inherited by the subclasses.

  • Thus: Temp_Emp also has attributes NI, Name and dob

  • In fact, subclasses inherit relationships too


Aggregation

since

start

dname

budget

budget

PID

DID

Sponsors

N

M

Projects

Departments

Aggregation

  • Suppose we have an entity set of Projects and that each project is sponsored by one or more departments; thus


Aggregation cont

Aggregation cont.

  • Suppose that employees are assigned to monitor a sponsorship

  • Monitors should be a relationship between Employees and the Sponsorsrelationship

  • Aggregation allows us to indicate that a relationship set participates in another relationship set

  • Use dashed box


Aggregation cont1

Aggregation cont.

NI

name

Employees

Monitors

until

since

start

dname

PID

DID

budget

budget

Sponsors

N

M

Projects

Departments


A data model from the european bioinformatics institute ebi

A Data Model from the European Bioinformatics Institute (EBI)

See http://intact.sourceforge.net/uml/intactCore.gif


Summary

Summary

You should now understand:

  • Database design lifecycle

  • Entities and attributes

  • Relationships

    • Cardinality ratios

    • Participation constraints

  • Weak entity types

  • ISA hierarchies & aggregation

    Next lecture: The relational model


  • Login