Database Models: Entity Relationship Modeling

Only available on StudyMode
  • Download(s) : 63
  • Published : April 29, 2013
Open Document
Text Preview
Database Models
s

L11: ER Diagrams
Kieran Herley
Summary The database design process and the role of ER modelling in same. Basic constituents of ER diagrams: entities, relationships and attributes.

While designing database it’s handy to have notation to “sketch” schema s Two common diagrammatic notations: x x
s

UML diagrams (Universal Modelling Language) ER diagrams (Entity Relationship) → cs1106 choice

Captures the information requirements of our database: what data it con and how these interrelate

[TOC]

ER Di

Database Development Process
1. Requirements analysis: figure out what is required of our database 2. Design: s s s s

Entities

Develop an ER diagram to sketch a design that meets these requirements “Translate” ER diagram into detailed DB schema

An entity is a “thing” e.g. a person An entity set is a collection of similar entities e.g. a collection of persons s An attribute is some property of the entities in an entity set e.g. the dat birth of each person

3. Implementation: Generate SQL code for design

[TOC]

ER Diagrams – 3 / 18

[TOC]

ER Di

Entities in ER Diagrams
s

Relationships
Relationship captures a connection between two or more entity sets s Example: s

Will represent entity sets using rectangles, attributes as ovals

x x

Entity sets: students, modules Relationships:
s s

is taking: Alice is taking cs1106 loves: Bob loves cs1109

s

Entity set eaters has attributes name and address; each entity with this set has a value for each attribute

[TOC]

ER Diagrams – 5 / 18

[TOC]

ER Di

Relationships in ER Diagrams
s

A More Complex Example
Key:
s s s

We use diamonds to depict relationships

Restaurants serve some foods Eaters like some foods Eaters frequent some restaur

s

Connect diamond to entity sets involved in relationship

[TOC]

ER Diagrams – 7 / 18

[TOC]

ER Di

Relationship Sets
The “value” of an entity set is the set of entities that currently belong to it e.g. the set of eaters in our database s The “value” of a relationship is a relationship set, the set of tuples with one component for each related entity set s Example (serves relationship) s s

Binary and Multiway Relationships

Relationships such as likes, frequents, serves are all binary i.e. involve tw entity sets s We sometimes need relationships involving three or more entity sets

Restaurant Luigi’s Luigi’s Luigi’s Gunther’s Gunther’s Pierre’s Pierre’s

Food Spaghetti Lasagne Pizza Bratwurst Sauerkraut Beans on Toast Pizza

[TOC]

ER Diagrams – 9 / 18

[TOC]

ER Dia

Relationship Sets cont’d
s s

Many-Many Relationships

Restaurant Luigi’s Pierre’s Luigi’s Gunther’s Pierre’s

Eater Alice Alice Bob Bob Clare

Food Spaghetti Beans on Toast Spaghetti Bratwurst Pizza

Consider likes relationships between eaters and foods In many-many relationship each entity in either entity set can be connect with many entities from the other entity set s Example (likes relationship): x Each eater may like many different foods x Each food may be liked by many eaters

[TOC]

ER Diagrams – 11 / 18

[TOC]

ER Dia

Many-One Relationships
In a many-one relationship each entity from the first set is connected to at most one entity in the second set s Note that each entity in the second entity set can be connected to many entities in the first s Example: x Imagine a favourite foods relationship between eaters and foods x Each eater can have (at most) one favourite food x A food can have any number of eaters form whom it is their favourite: zero, one, two, many s s

One-One Relationships

In a one-one relationship each entity in either set is connected to at most entity in the other entity set s Example: x Imagine is head chef relationship between chefs and restaurants x Each restaurant has (at most) one head chef x Each chef can be head chef in at most one restaurant (we assume)

[TOC]

ER Diagrams – 13 / 18

[TOC]...
tracking img