Entity relationship diagrams
This presentation is the property of its rightful owner.
Sponsored Links
1 / 37

Entity Relationship Diagrams PowerPoint PPT Presentation


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

Entity Relationship Diagrams. Conceptual Data Modeling. Database Development Process. Business Information Requirements. Business view. Systems view. Logical Database Design. Physical Database Build. Operational Database. Terminology. CONCEPTUAL. LOGICAL. (Business view).

Download Presentation

Entity Relationship Diagrams

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


Entity relationship diagrams

Entity Relationship Diagrams


Database development process

Conceptual Data Modeling

Database Development Process

Business Information Requirements

Business view

Systems view

Logical Database Design

Physical Database Build

Operational Database


Terminology

Terminology

CONCEPTUAL

LOGICAL

(Business view)

(Systems view)

ANALYSIS

DESIGN

ENTITY

TABLE

RELATIONSHIP

FOREIGN KEY

ATTRIBUTE

COLUMN

PRIMARY KEY

UNIQUE

IDENTIFIER

UNIQUE KEY


Information models

Information Models

  • Organize thought processes

  • Accurately model business data

  • Communicate with stakeholders

  • Analyze the scope

  • Provide sound basis for system design


Cs 450 erd conventions

CS 450 ERD Conventions

attribute

Cardinality (degree)

empnum

key

M

1

Works For

EMPLOYEE

DEPARTMENT

entity

relationship

Participation Requirements (optionality)


Main elements of an oracle erd

COMPANY

Super-type

held

by

SUPPLIER

Sub-type

the holder

of

OTHER

COMPANY

composed

of

Recursive

Relationship

part of

Exclusive Arc

Main Elements of an Oracle ERD

Relationship

Unique

Identifier

CUSTOMER

# * customer number

*first name

*last name

o other initials

held

by

Entity

MEMBERSHIP

Attributes

the holder

of


Entity relationship diagrams

Agreement

Item1

Item 2

Product X

Product X

Hardware and Software Independence

ENTITY RELATIONSHIP MODEL

Agreement Term

Product

NETWORK DATABASE

HIERARCHICAL DATABASE

Agreement

Agreement

Product x

Product y

Item 1

Item 2

RELATIONAL DATABASE

Code

Date

Customer

Code

Description

Quantity

Agreement

Number

Product


Relationship definitions

Relationship Definitions

  • The way one entity relates to another

  • The business rules that link together business information needs

  • What one thing has to do with another

  • A named association between entities


Relationship diagramming conventions

A line between two entities

Lower case relationship name

Optionality (Minimum cardinality)

Degree (Maximum cardinality)

Relationship Diagramming Conventions

Mandatory - must be

Optional - may be

One or more

One and only one


Relationship diagramming conventions1

COPY

TITLE

Relationship Diagramming Conventions

many

(crow’s foot)

optional

one

mandatory


Relationship syntax

Relationship Syntax

Entity 2

Entity 1

must be

or

may be

one or more

or

one and only one

relationship

name

Each

entity 1

entity 2

Object

entity

Name

Subject

entity

Optionality

Degree


How do you read this

How do you read this?

assigned to

EMPLOYEE

DEPARTMENT


Entity relationship diagrams

How do you read this?

assigned to

EMPLOYEE

DEPARTMENT

Each EMPLOYEE must be assigned to one and only one DEPARTMENT


How do you read this1

How do you read this?

EMPLOYEE

DEPARTMENT

responsible for


How do you read this2

How do you read this?

EMPLOYEE

DEPARTMENT

responsible for

Each DEPARTMENT may be responsible for one or more EMPLOYEES


Entity relationship diagrams

Full Reading of Relationship

assigned to

EMPLOYEE

DEPARTMENT

EMPLOYEE

DEPARTMENT

responsible for

Each EMPLOYEE must be assigned to one and only one DEPARTMENT

Each DEPARTMENT may be responsible for one or more EMPLOYEES


How do you read this3

How do you read this?

enrolled in

COURSE

STUDENT

taken by


How do you read this4

enrolled in

COURSE

STUDENT

taken by

How do you read this?

Each STUDENT may be enrolled in one or more COURSES

Each COURSE may be taken by one or more STUDENTS


Analyzing and modeling relationships

Analyzing and Modeling Relationships

  • Determine the existence of a relationship

  • Name each direction of the relationship

  • Determine the degree of each direction of the relationship

  • Determine the optionality of each direction of the relationship

  • Read the relationship aloud to validate it


Oracle s layout guidelines

Oracle’s Layout Guidelines

Parent Entity (1)

Child Entity

Dead Crows Fly East !

Parent Entity (2)


Attributes

Badge Number - Identifies an employee

Name - Qualifies an employee

Payroll category (weekly or salaried) - Classifies an employee

Date of birth - Quantifies an employee

Employment status (active, leave, terminated) - Classifies of an employee

Attributes


Finding attributes

Finding Attributes

Is this attribute really needed ?

Beware of obsolete requirements from previous systems

Beware of derived data


Attribute diagramming conventions

Attribute Diagramming Conventions

  • Inside the entity's soft box

  • Singular

  • Lowercase

EMPLOYEE

badge num

first name

last name

payroll num

date of birth

employment status


Meaningful components

ITEM

ITEM

type

vendor

num

code

Meaningful Components

PERSON

PERSON

last name

first name

name

Break down aggregate attributes


Verify for single value

RENTAL

transaction date

total amount paid

item

RENTAL ITEM

RENTAL

item num

transaction date

total amount paid

Verify for Single Value

Can an attribute have more than one value for one instance of the entity?

Yes, more than one item may be rented at a time. An entity is missing.


Attributes which have attributes

TITLE

REVIEW

product code

title

description

review details

author

comment

date recorded

Attributes Which have Attributes

TITLE

Does information need to be stored about any of the attributes?

product code

title

description

review details

Yes, review details. An entity is missing.


Finding common or derived data

12 08 30 22----

72----

Finding Common or Derived Data

  • Count

  • Total

  • Maximum, Minimum, Average

  • Calculation

Derived attributes are redundant and can lead to inconsistent values


Attribute optionality

A value must bestored for each entity instance

Tagged with

A value may bestored for each entity instance

Tagged with o

Attribute Optionality

Mandatory Attributes

*

Optional Attributes


Attribute optionality1

Attribute Optionality

EMPLOYEE

*

badge num

*

first name

*

last name

o

title

o

weight


Attribute details and volumes

Attribute Details and Volumes

Attribute- * Engine Size

FormatTypeNumber

Maximum length4

Average length4

Decimal place1

Unit of measurecc

Allowable values900,1000,1500,1800,2000

VolumeInitial100%


Using a domain

Using a Domain

Movie

Mono

AUDIO

Stereo

MON

STE

SUR

Audio

Game

Surround

Sound


Creating an entity

Creating an Entity

For DB Planning


Adding details

Adding Details


Domains

Domains


Creating relationships

Creating Relationships


Diagrammer behavior

Diagrammer Behavior

  • Definition changes are stored in Repository even if the diagram is not saved.

  • Saving a diagram saves the layout.

  • Diagrams are not synchronized automatically.

  • Requerying is your responsibility.

  • Diagrams can be exported to, for example, Word documents.


Editing in the ron

Editing in the RON


  • Login