Slide 1 Slide 6 Main Element s of an Oracle ERD Main Element s of - - PDF document

Slide 1 Ent it y Relat ionship Diagrams Slide 2 Dat abase Development Process Dat abase Development Process

Business Information Requirements Operational Database Conceptual Data Modeling Logical Database Design Physical Database Build

Business view Systems view

Slide 3 Terminology Terminology

LOGICAL CONCEPTUAL

DESIGN ENTITY RELATIONSHIP ATTRIBUTE TABLE FOREIGN KEY COLUMN (Business view) (Systems view) ANALYSIS UNIQUE IDENTIFIER PRIMARY KEY UNIQUE KEY

Slide 4 I nf ormat ion Models I nf ormat ion Models

• Or ganize t hought processes
• Accurat ely model business dat a
• Communicat e wit h st akeholders
• Analyze t he scope
• Provide sound basis f or syst em design
• Or ganize t hought processes
• Accurat ely model business dat a
• Communicat e wit h st akeholders
• Analyze t he scope
• Provide sound basis f or syst em design

Slide 5 CS 450 ERD Convent ions

EMPLOYEE DEPARTMENT Works For M 1 empnum entity relationship attribute Participation Requirements (optionality) Cardinality (degree) key

Slide 6

• f

• f

Recursive Relationship

Main Element s of an Oracle ERD Main Element s of an Oracle ERD

Exclusive Arc MEMBERSHIP CUSTOMER # * customer number * first name * last name

• other initials

• f

Unique Identifier Attributes Relationship Entity Super-type Sub-type

Slide 7

Hardware and Software Independence Hardware and Software Independence

Slide 8 Relat ionship Def init ions Relat ionship Def init ions

• The way one ent it y relat es t o anot her
• The business rules t hat link t oget her

business inf or mat ion needs

• What one t hing has t o do wit h

anot her

• A named associat ion bet ween ent it ies
• The way one ent it y relat es t o anot her
• The business rules t hat link t oget her

business inf or mat ion needs

• What one t hing has t o do wit h

anot her

• A named associat ion bet ween ent it ies

Slide 9 Relat ionship Diagramming Convent ions Relat ionship Diagramming Convent ions

• A line bet ween t wo ent it ies
• Lower case r elat ionship name
• Opt ionalit y (Minimum car dinalit y)
• A line bet ween t wo ent it ies
• Lower case r elat ionship name
• Opt ionalit y (Minimum car dinalit y)

Optional - may be Mandatory - must be One or more One and only one

• Degree (Maximum cardinalit y)
• Degree (Maximum cardinalit y)

Slide 10 Relat ionship Diagramming Convent ions Relat ionship Diagramming Convent ions

COPY TITLE many (crow’s foot) mandatory

• ptional
• ne

Slide 11 Relat ionship Synt ax Relat ionship Synt ax

Each Subject entity must be

• r

may be relationship name

• ne or more
• r
• ne and only one

entity 1 entity 2 Degree Object entity Optionality Name Ent it y 1 Ent it y 2

Slide 12 How do you read t his? How do you read t his?

EMPLOYEE DEPARTMENT assigned to

Slide 13

EMPLOYEE DEPARTMENT assigned to Each EMPLOYEE must be assigned to one and only one DEPARTMENT

How do you read t his? How do you read t his? Slide 14

EMPLOYEE DEPARTMENT responsible for

How do you read t his? How do you read t his? Slide 15

EMPLOYEE DEPARTMENT Each DEPARTMENT may be responsible for one or more EMPLOYEES responsible for

How do you read t his? How do you read t his? Slide 16

EMPLOYEE DEPARTMENT assigned to Each EMPLOYEE must be assigned to one and only one DEPARTMENT EMPLOYEE DEPARTMENT Each DEPARTMENT may be responsible for one or more EMPLOYEES responsible for

Full Reading of Relationship Full Reading of Relationship

Slide 17 How do you read t his? How do you read t his?

taken by enrolled in STUDENT COURSE

Slide 18

Each STUDENT may be enrolled in one or more COURSES Each COURSE may be taken by one or more STUDENTS taken by enrolled in STUDENT COURSE

How do you read t his? How do you read t his? Slide 19 Analyzing and Modeling Relat ionships Analyzing and Modeling Relat ionships

1 Det ermine t he exist ence of a relat ionship 2 Name each dir ect ion of t he r elat ionship 3 Det ermine t he degree of each direct ion

• f t he r elat ionship

4 Det ermine t he opt ionalit y of each dir ect ion of t he r elat ionship 5 Read t he relat ionship aloud t o validat e it 1 Det ermine t he exist ence of a relat ionship 2 Name each direct ion of t he relat ionship 3 Det ermine t he degree of each direct ion

• f t he r elat ionship

4 Det ermine t he opt ionalit y of each dir ect ion of t he r elat ionship 5 Read t he relat ionship aloud t o validat e it

Slide 20 Oracle’s Layout Guidelines Oracle’s Layout Guidelines

Dead Crows Fly East !

Child Ent it y P ar ent Ent it y (1) P ar ent Ent it y (2)

Slide 21 At t ribut es At t ribut es

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

Slide 22 Finding At t ribut es Finding At t ribut es

Is this attribute really needed ? Beware of obsolete requirements from previous systems Beware of derived data

Slide 23 At t ribut e Diagramming Convent ions At t ribut e Diagramming Convent ions

EMPLOYEE badge num first name last name payroll num date of birth employment status

• I nside t he ent it y' s

sof t box

• Singular
• Lower case
• I nside t he ent it y' s

sof t box

• Singular
• Lower case

Slide 24 Meaningf ul Component s Meaningf ul Component s

PERSON name PERSON last name first name ITEM code ITEM type vendor num

Break down aggregate attributes

Slide 25 Verif y f or Single Value Verif y f or Single Value

RENTAL transaction date total amount paid item

Yes, more than one item may be rented at a time. An entity is missing. Can an attribute have more than one value for one instance of the entity?

RENTAL transaction date total amount paid RENTAL ITEM item num

Slide 26 At t ribut es Which have At t ribut es At t ribut es Which have At t ribut es

Does information need to be stored about any of the attributes? Yes, review details. An entity is missing.

TITLE product code title description review details REVIEW author comment date recorded product code title description review details TITLE

Slide 27 Finding Common or Derived Dat a Finding Common or Derived Dat a

• Count
• Tot al
• Maximum, Minimum, Aver age
• Calculat ion
• Count
• Tot al
• Maximum, Minimum, Aver age
• Calculat ion

Derived attributes are redundant and can lead to inconsistent values

• 72
• Slide 28

At t ribut e Opt ionalit y At t ribut e Opt ionalit y

• A value must be st ored f or each ent it y inst ance
• Tagged wit h
• A value must be st ored f or each ent it y inst ance
• Tagged wit h

Mandatory Attributes Optional Attributes

• A value may be st or ed f or each ent it y

inst ance

• Tagged wit h o
• A value may be st or ed f or each ent it y

inst ance

• Tagged wit h o

*

Slide 29 At t ribut e Opt ionalit y At t ribut e Opt ionalit y

EMPLOYEE badge num first name last name title

* * *

• weight
• Slide 30

At t ribut e Det ails and Volumes At t ribut e Det ails and Volumes

Attribute - * Engine Size

Format Type Number Maximum length 4 Average length 4 Decimal place 1 Unit of measure cc Allowable values 900,1000,1500,1800,2000 Volume Initial 100%

Slide 31 Using a Domain Using a Domain

AUDIO MON STE SUR Movie Game Audio Sound Mono Stereo Surround

Slide 32 Creat ing an Ent it y

For DB Planning

Slide 33 Adding Det ails Slide 34 Domains Slide 35 Creat ing Relat ionships Slide 36 Diagrammer Behavior

• Def init ion changes ar e st ored in

Def init ion changes ar e st ored in Reposit ory even if t he diagram is Reposit ory even if t he diagram is not saved. not saved.

• Saving a diagr am saves t he layout .

Saving a diagr am saves t he layout .

• Diagrams are not synchronized

Diagrams are not synchronized aut omat ically. aut omat ically.

• Requerying

Requerying is your responsibilit y. is your responsibilit y.

• Diagr ams can be expor t ed t o, f or

Diagr ams can be expor t ed t o, f or example, Word document s. example, Word document s.

Slide 37 Edit ing in t he RON