Docsity
Docsity

Prepare for your exams
Prepare for your exams

Study with the several resources on Docsity


Earn points to download
Earn points to download

Earn points by helping other students or get them with a premium plan


Guidelines and tips
Guidelines and tips

Entity Relationship Modelling-Databases-Lecture 02 Slides-Computer Science, Slides of Database Management Systems (DBMS)

Entity/Relationship Modelling, Database Design, Entities, Attributes, Relationships, Constraints on Relationships, Extended E/R Modelling, Entity Set, Key Attributes, Relationship Type, Relationships in E/R, Relationship Attributes, N-ary Relationships, Recursive Relationships, Cardinality, Cardinality Ratios, Participation Constraints, Weak Entity, Weak Entity Types, ISA Hierarchies, Attribute Inheritance, Aggregation, Data Model, European Bioinformatics Institute

Typology: Slides

2011/2012

Uploaded on 02/12/2012

tiuw
tiuw 🇺🇸

4.7

(18)

288 documents

1 / 29

Toggle sidebar

This page cannot be seen from the preview

Don't miss anything!

bg1
1
Lecture 2:
Entity/Relationship modelling
www.cl.cam.ac.uk/Teaching/current/Databases/
Dr. Peter Chen
http://bit.csc.lsu.edu/~chen/
pf3
pf4
pf5
pf8
pf9
pfa
pfd
pfe
pff
pf12
pf13
pf14
pf15
pf16
pf17
pf18
pf19
pf1a
pf1b
pf1c
pf1d

Partial preview of the text

Download Entity Relationship Modelling-Databases-Lecture 02 Slides-Computer Science and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

1

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
  • 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

4

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

  • 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)

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 NI Name dob

Employees

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 E 1 En.
  • The set of instances of the relationship type is called the relationship set

Relationship set diagrams

  • Sometimes its useful to represent the relationship set diagrammatically

e e^12 e 3 e 4 e 5

d 1 d 2 d 3 d 4 d 5

r 1 r 2 r 3 r 4 … … …

Relationship attributes

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

Works_in

NI Name dob

Employees

DID dname budget

Departments

since

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 in E/R

e.g.

Employees

Reports-to

NI

name (^) dob

supervisor subordinate

Cardinality ratios

1:1 1:N

M:N

Cardinality ratios in E/R

M:N

N:

1:

M N

N 1

1 1

Note: Sometimes this is written using different arrowheads

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

Manages

NI Name dob

Employees

DID dname budget

Department

since

1 N

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