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

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