Entity relationship diagrams
Download
1 / 36

Entity Relationship Diagrams - PowerPoint PPT Presentation


  • 130 Views
  • Uploaded on

Entity Relationship Diagrams. Mandatory 1. Sends. Supplier. Mandatory many. Optional many. Supplies. Many with maximum. Sent by. Supplied by. Includes. Item. Shipment. Production Plan. Produces. Produced on. Included on. Goes into. Generated on. Composed of. Generates. Builds.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Entity Relationship Diagrams' - damien


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

Mandatory 1

Sends

Supplier

Mandatory many

Optional many

Supplies

Many with maximum

Sent by

Supplied by

Includes

Item

Shipment

Production

Plan

Produces

Produced on

Included on

Goes into

Generated on

Composed of

Generates

Builds

Product

Master

Schedule

Built on


Entity relationship diagrams1
Entity Relationship Diagrams

  • Purpose: Show the structure (relationship of data elements)

  • Can be used in multiple stages of development

  • Used to communicate and verify understanding between developers and users, and to document the perceived data model


Erd elements
ERD Elements

  • Entities

    • Things about which you collect information

  • Relationships

    • Means of association between entities

  • Identifiers

    • Unique attributes of the entity

  • Attributes

    • Characteristic or property of the entity that is of interest


Erd symbols
ERD Symbols

  • Entities: rectangle

  • Relationships: diamond (may or may not be used), on a line showing the “cardinality” of the relationship (1 to many), etc.

  • Identifiers: Underlined text

  • Attributes: Text by the entity (if shown at all on the diagram)


Sample erds

ORDER

CAN HAVE

CAN HAVE

Sample ERDs

Entity attributes:

ORDER: #, DATE, PART #, QUANTITY

PART: #, DESCRIPTION, UNIT PRICE, SUPPLIER #

SUPPLIER: #, NAME, ADDRESS

Order

1

1

Part

PART

M

1

Supplier

SUPPLIER


Erd notes
ERD Notes

  • Relationship connect entities

  • Attributes are not technically a part of the diagram but are often included

  • ERD is not the same as a relational table design


Entity sets
Entity Sets

  • Each individual object is called an entity. A collection of such entities is an entity set.

    • Example: Joe, Jill, and Mary represent entities. They are all ascribed to the entity set, PERSON.

    • Example: A collection of projects is the project entity set.


Relationship
Relationship

  • Relationship

    • One interaction between one or more entities

    • For example: if a person works on a project, there is a relationship between that person and the project

  • Relationship set

    • A collection of such relationships.

    • A component in an E-R diagram that represents a set of relationships with the same properties.


Notes on entities and relationships
Notes on Entities and Relationships

  • We can actually see entities, but we cannot see relationships.

    • For example:

      • Entity sets: Person; project

      • Relationship: People work on projects. This becomes the relationship set “Work”

Persons

The set of people, set of projects and

set of working relationships.

Work

Projects


Entity relationship structures
Entity-Relationship Structures

Persons are in departments

Persons work on projects

Persons

Parts

Supply

Suppliers

Projects use parts

Suppliers supply parts

Warehouses hold parts

Hold

Are-In

Work-On

Use

Warehouses

More complex relationship:

See persons and parts

Depts

Projects


Entities can have multiple relationships
Entities can have multiple relationships

Companies

Leases

Owns

Vehicles


More than two entity sets can be associated with the same relationship set
More than two entity sets can be associated with the same relationship set

Relationship sets that include only two

entities are known as binary. More than

two are known as N-ary.

Customers

Buy

Stores

Each relationship in this set includes a person, a part bought

by the person, and the store where the purchase was made.

Parts

A person, a part bought by the person,

and the store where the purchase was made


Identifiers
Identifiers relationship set

  • One of the attributes of an entity or relationship set is called the identifier

  • It has one important property: its values identify unique entities in the entity set.


Identifiers are underlined here
Identifiers are underlined here relationship set

PERSON-ID

NAME

ADDRESS

PERSON-ID

PROJECT-ID

TIME-SPENT

PROJECT-ID

START-DATE

BUDGET

Persons

Work

Projects

The set of people, set of projects and

set of working relationships.


Convention for identifiers in relationships
Convention for Identifiers in Relationships relationship set

  • Use the identifiers of the entities that participate in the relationship as the relationship identifiers.

  • Identifiers are not file keys here. At this stage, they are the identifiers of entities that participate in the relationship.


Cardinality
Cardinality relationship set

  • The number of relationships in which one entity can appear.

  • An entity can appear in:

    • one (1) relationship;

    • any variable number (N) of relationships; and

    • a maximum number of relationships


Cardinality example
Cardinality - Example relationship set

PERSON-ID

NAME

ADDRESS

PERSON-ID

PROJECT-ID

TIME-SPENT

PROJECT-ID

START-DATE

BUDGET

Persons

N

A persona can appear in more than one

WORK relationship, and so can a project.

If there was a limit to the number of times an

entity can take part in the relationship, then

N or M would be replaced by the actual

maximum number.

Work

M

Projects

The set of people, set of projects and

set of working relationships.


Cardinality example1
Cardinality - Example relationship set

MANAGER-ID

NAME

ADDRESS

PERSON-ID

PROJECT-ID

TIME-SPENT

PROJECT-ID

START-DATE

BUDGET

Manager

1

Here a project has one (1) manager, whereas

a manager can manage any number (N) of

projects.

Manage

N

Projects

The set of people, set of projects and

set of working relationships.


Cardinality example2
Cardinality - Example relationship set

MANAGER-ID

NAME

ADDRESS

PERSON-ID

PROJECT-ID

TIME-SPENT

PROJECT-ID

START-DATE

BUDGET

Manager

1

The denotes optional participation on the

project. If it is mandatory, then there is no

placed there.

Manage

N

Projects

The set of people, set of projects and

set of working relationships.


Issues in building an er diagram
Issues in building an ER-Diagram relationship set

  • How to choose entities, relationships and attributes

  • How to choose names

  • What steps should be followed


Choosing attributes
Choosing Attributes relationship set

  • Attributes, just like entity and relationship sets, should express simple concepts.

  • E-R diagrams should not contain multi-valued or structured attributes

    • For example:

PERSON-ID

DATE-OF-BIRTH

QULIFICATION* (asterisk means it is multivalued-repeating)

ADDRESS

(NUMBER, STREET, SUBURB)

PERSONS

Non-simple attributes

Addresses has structured attributes.

These should be replaced in the final diagram

by relationships.


Ex removing multi valued and structured attributes
Ex: Removing multi-valued and structured attributes relationship set

PERSON-ID

DATE-OF-BIRTH

Persons

PERSON-ID

QUALIFICATION

HAVE

LIVE-AT

PERSON-ID

NUMBER

STREET

SUBURB

QUALIFICATION

NUMBER

STREET

SUBURB

QUALIFICATIONS

ADDRESSES


Choosing object set names
Choosing Object Set Names relationship set

  • Remember, that one goal of E-R modeling is to produce a model that is easily understood by users as well as computer personnel.

    • Entity sets are labeled as nouns

    • Relationship sets are labeled by verbs

    • Relationship sets are structured as prepositions when modeling structural relationships


Where to begin
Where to begin relationship set

  • Start with entity sets

  • Look at how entities interact with each other and model this in terms of relationship sets

  • Then add cardinality to the system

  • Add attributes and choose identifiers


Normalization highlights

Normalization Highlights relationship set


Normalization
Normalization relationship set

  • The process of organizing data in a database. This includes creating tables and establishing relationships between those tables according to rules designed both to protect the data and to make the database more flexible by eliminating two factors: redundancy and inconsistent dependency.


Factor 1 inconsistent dependency
Factor 1: Inconsistent Dependency relationship set

  • Customer table should have customer address but not employee salary who calls on customer

  • If customer information is contained in an order file, if the order is canceled, all the customer information could be lost

    • Solution: create two tables--one table contains order information and the other table contains customer information.


Factor 2 redundant data
Factor 2: Redundant Data relationship set

  • Data exists in more than one place

  • Wastes disk space and creates maintenance problems

    • Example: a staff person changes their telephone number and every potential customer that person ever worked with has to have the corrected number inserted.


Rules for database normalization
Rules for Database Normalization relationship set

  • First Normal Form

    • No repeating groups

  • Second Normal Form

    • Eliminate data redundancy

  • Third Normal Form:

    • Eliminate data not dependent on key


Database normalization example
Database Normalization - Example relationship set

DMOD

ISDATAD

STARTING WITH A SET OF DATA ITEMS:

Employee Name

Employee ID

Department

Dept Address

Item#

Item Description

Item Price

Warehouse ID

Warehouse Address

Item Location in each Warehouse

Quantity on Hand in each Warehouse


Database normalization example1
Database Normalization - relationship setExample

DMOD

ISDATAD

1. CLUSTER DATA ITEMS INTO ENTITIES (to become TABLES):

Employee ID

Employee Name

Department

Dept Address

Item#

Item Description

Item Price

Warehouse ID

Warehouse Address

Item Location in each Warehouse

Quantity on Hand in each Warehouse


Database normalization example2
Database Normalization - Example relationship set

DMOD

ISDATAD

2. PULL OUT MULTI-VALUED ITEMS or REPEATING GROUPS:

From:

Item# Description Price

WarehouseID Address ItemLocation Quantity on Hand

WarehouseID Address ItemLocation Quantity on Hand

WarehouseID Address ItemLocation Quantity on Hand

WarehouseID Address ItemLocation Quantity on Hand

To:

Item#Description Price

Item# Warehouse IDAddress ItemLocation Quantity on Hand

NOTE: Item# propagates down and becomes part of the identifier. Why?


Database normalization example3
Database Normalization - Example relationship set

DMOD

ISDATAD

3. PULL OUT FACTS ABOUT A PORTION OF THE KEY (partial dependency):

From:

Item#Description Price

Item# Warehouse IDAddress Item Location Quantity on Hand

To:

Item#Description Price

Item# Warehouse IDItem Location Quantity on Hand

Warehouse IDAddress


Database normalization example4
Database Normalization - Example relationship set

DMOD

ISDATAD

4. PULL OUT FACTS ABOUT A NON-KEY DATA ITEM (transitive dependency):

Employee IDEmployee Name Department Dept Address

From:

To:

Employee IDEmployee Name Department

Department IDDept Address

What is the “Department” field called in the Employee record?

Why does it remain in the Employee record?


Role of data values
Role of data values relationship set

  • Table components

  • Fields vs. values

  • Records are related via the values

Customer table

Order table

Relation by

data value


ad