Slide 1 Slide 6 Compound UID - Composite Compound UID - Composite - - PDF document

SLIDE 1

Slide 1 Advanced Ent it y Relat ionship Concept s Slide 2 Advanced Concept s

• UI Ds
• I nt ersect ion

Ent it ies

• Recur sive

Relat ionships

• Roles
• Subt ypes
• Exclusivit y
• Hist orical
• Fan Traps
• Non t ransf erabilit y

Slide 3 Simple Unique I dent if ier Simple Unique I dent if ier

CUSTOMER # * customer num

Single attribute Tag the UID with #

Slide 4 Compound UI D - At t ribut es Compound UI D - At t ribut es

MEMBERSHIP # * num # * start date

Multiple attributes Members get a new membership each year but keep t heir membership number.

Slide 5

Compound UID - Composite Compound UID - Composite

ACCOUNT * num BANK # * num

What would you need to know to identify a specific instance of ACCOUNT?

Slide 6

Compound UID - Composite Compound UID - Composite

ACCOUNT * num BANK # * num

Use a UID bar to indicate that a relationship is part of the entity’s UID Use # to indicate that the attribute is part of the entity’s UID What would you need to know to identify a specific instance of ACCOUNT?

#

Slide 7

What do you need to know to identify a specific instance of RENTAL ITEM? Rental item requires the rental transaction num and the inventory num

Compound UID - Relationships Compound UID - Relationships

RENTAL ITEM * rental period return date

• RENTAL

# * transaction num * transaction date COPY # * inventory num * purchase cost

Slide 8 Mult i-Level Relat ionship UI Ds Mult i-Level Relat ionship UI Ds

What would you need to know to identify a specific instance of TICKET?

VENUE PLAY # * title PERFORMANCE # * date # * time # * name CUSTOMER # * name TICKET * seat number

Slide 9 Mult i-Level Relat ionship UI Ds Mult i-Level Relat ionship UI Ds

Venue name + Play title + Performance date + Performance time + Customer name

VENUE PLAY # * title PERFORMANCE # * date # * time # * name CUSTOMER # * name TICKET * seat number

Slide 10 Mult iple UI Ds Mult iple UI Ds

badge num payroll num first and last name EMPLOYEE badge num payroll num last name first name

#(1)* #(3) * #(2) * #(2) *

SLIDE 2

Slide 11 Composit ion of a UI D

SUPPLIER # CODE PERSON # NAME # DATE OF BIRTH

• Example A

Example A

SUPPLIER TITLE SUPPLIER TITLE distributor distributor

• f
• f

distributed distributed by by available as available as for for

• Example B

Example B

RENTAL ITEM RENTAL part of part of composed composed

• f
• f

# LINE NO

• Example C

Example C

Slide 12 Primary Unique I dent if ier Primary Unique I dent if ier

• An ent it y can have several unique

ident if iers.

• One may be appoint ed as t he

primary unique ident if ier.

• Only t he P

rimary UI D is shown in t he ent it y relat ionship diagr am.

• An ent it y can have several unique

ident if iers.

• One may be appoint ed as t he

primary unique ident if ier.

• Only t he P

rimary UI D is shown in t he ent it y relat ionship diagr am.

Slide 13

RENTAL ITEM * rental period return date

• RENTAL

# * transaction num * transaction date COPY # * inventory num * purchase cost RENTAL # * transaction num * transaction date COPY # * inventory num * purchase cost

I nt ersect ion Ent it y Slide 14 I nt ersect ion Ent it ies: I dent if ying t he P roblem I nt ersect ion Ent it ies: I dent if ying t he P roblem

TITLE SUPPLIER

supplied by supplier of

In which entity would you store the attribute: purchase price?

# * prod code * name # * supplier no * name

Slide 15 I nt ersect ion Ent it ies I nt ersect ion Ent it ies

CATALOG ITEM

available as

SUPPLIER

supplier of

TITLE TITLE

# * prod code * name # * supplier no * name * purchase price for for

Slide 16 Unique I dent if iers Unique I dent if iers

available as CATALOG ITEM SUPPLIER supplier of TITLE TITLE # * prod code * name # * supplier no * name * purchase price for for CATALOG ITEM available as SUPPLIER supplier of TITLE TITLE # * prod code * name # * supplier no * name # * item num * purchase price for for

OR

Slide 17 Modeling Recursive Relat ionships Modeling Recursive Relat ionships

...and mine ...he’s my manager ...but I’m HIS manager! EMPLOYEE manager of managed by

Slide 18 Modeling Hierarchical Dat a Modeling Hierarchical Dat a

Company Division Team Department TEAM # name DEPARTMENT # * name DIVISION # * name COMPANY # * name

Slide 19 Hierarchies as Recursive Relat ionships Hierarchies as Recursive Relat ionships

ORGANIZATION ELEMENT made up of within # * name * type TEAM # name DEPARTMENT # * name DIVISION # * name COMPANY # * name

Slide 20 Net work St ruct ures Net work St ruct ures

COMPONENT # * identifier a part of made up of

SLIDE 3

Slide 21 Net work St ruct ures Net work St ruct ures

COMPONENT # * identifier a part of made up of COMPONENT COMPONENT made up of a part of # * identifier # * identifier

Slide 22 Net work St ruct ures Net work St ruct ures

COMPONENT COMPONENT made up of

a part of

# * identifier # * identifier ASSEMBLY RULE COMPONENT # * identifier COMPONENT # * identifier

made up of a part of

for for

• quantity

Slide 23 Net work St ruct ures Net work St ruct ures

COMPONENT ASSEMBLY RULE a part of made up

• f

for for

• quantity

# * identifier

Slide 24 I dent if ying Roles I dent if ying Roles

ENROLLMENT STUDENT COURSE SECTION INSTRUCTOR for for included in taken by taught by the teacher

• f

* date enrolled * fee # * id * name * location * start date # * id * name * salary COURSE # * code * name for the subject

• f

Slide 25 Modeling Roles Modeling Roles

for for enrolled

• n

ENROLLMENT * date enrolled COURSE # * code * name PERSON # * id * name

• salary

* fee taught by for COURSE SECTION * location * start date taken by the teacher

• f

included in

Slide 26 Subt ypes Subt ypes

* category * duration

O audio

TITLE #

* *

product code title description

*

GAME MOVIE * category * medium * minimum memory All titles are either movies or games

Slide 27 Creat ing a Subt ype

I n ERD: I n ERD: Drag and drop. Drag and drop.

• r
• r

Draw new ent it y Draw new ent it y f ully wit hin or f ully wit hin or ar ound t he ar ound t he

• t her .
• t her .

In RON: Set Property Set Property Type of Type of. .

• r

Drag and drop. Drag and drop. and Select association for Select association for dropped entity. dropped entity.

Select Associations Type of Sub Entities

Slide 28 Exclusive Ent it ies Exclusive Ent it ies

COMPANY

id name telephone num supplier num sales contact #

*

• *

*

• COPY

acquired from the source of the holder

• f

held by

MEMBERSHIP * num * start date * expiry date

termination

# * inventory num

• condition
• Company in two roles

Slide 29 Split t ing Ent it ies Split t ing Ent it ies

COMPANY #

* *

acquired from the source of the holder

• f

held by

id name telephone num

*

OTHER

supplier num sales contact

*

SUPPLIER

MEMBERSHIP * num * start date * expiry date

termination

# COPY * inventory num

• condition
• Could teacher-student-person be so modeled?

Slide 30 Nest ing Ent it ies Nest ing Ent it ies

EMPLOYEE SALES REP TELESALES CLERICAL HUMAN RESOURCES CAR driven by authorized to drive

Business Rule: only sales r eps can dr ive company cars

SLIDE 4

Slide 31 Nest ing Ent it ies Nest ing Ent it ies

AIRCRAFT AIRPLANE GLIDER POWERED AIRPLANE PROP PLANE JET PLANE HELICOPTER HOVERCRAFT OTHER AIRCRAFT

Slide 32 Recursive Subt ypes Recursive Subt ypes

ORGANIZATION ELEMENT TYPE ORGANIZATION ELEMENT made up of ORGANIZATION SUBDIVISION within

• f

the classification for

Slide 33 Modeling Exclusive Relat ionships Modeling Exclusive Relat ionships

• MEMBERSHIP

COMPANY CUSTOMER the holder of held by held by the holder of * num * start date * expiry date

• termination

* name * postal area

0 contact name

* num * first name * last name

Slide 34 Creat ing an Arc Creat ing an Arc

CATALOG PUBLICATION REVIEW in

• f
• f

in

Slide 35 Modeling Exclusivit y Modeling Exclusivit y

“We offer membership to individual customers and companies”

MEMBERSHIP CUSTOMER COMPANY MEMBERSHIP CUSTOMER COMPANY MEMBERSHIP CUSTOMER COMPANY MEMBER INDIVIDUAL ORGANIZATION

Slide 36 Modeling Dat a over Time Modeling Dat a over Time

APARTMENT # * code * address PERSON # * id * last name * first name rented by the renter of

What if you need to hold an apartment’s rental history?

Slide 37 Modeling Dat a over Time Modeling Dat a over Time

APARTMENT # * code * address PERSON # * id * last name * first name rented by RENTAL HISTORY for the renter of for # * from date to date

• Slide 38

Modeling Dat a over Time Modeling Dat a over Time

the employer

• f

employed by COMPANY # * code * name # * id last name * * first name MEMBER

Slide 39 Modeling Dat a over Time Modeling Dat a over Time

COMPANY # * code * name # * id last name * * first name MEMBER # * from date to date

• EMPLOYMENT

HISTORY ENTRY for for employed by the employer

• f

Slide 40 Fan Traps Fan Traps

PERSON POSITION COMPANY # * id * last name * first name # * job title job description

• # * code

* name the holder of held by included in the employer of the employer of employed by

SLIDE 5

Slide 41 Fan Traps Fan Traps

PERSON POSITION COMPANY # * id * last name * first name # * job title job description

• # * code

* name employed as held by the subject

• f

the employer for the employer for employed at POSITION HISTORY ORGANIZATION HISTORY COMPANY HISTORY for for for for for for * start date end date

• * start date

end date

• * start date

end date

• Slide 42

Resolving Fan Traps Resolving Fan Traps

PERSON POSITION COMPANY # * id * last name * first name # * job title job description

• # * code

* name the employer for for EMPLOYMENT HISTORY a party to at as included in * start date end date

• #

Slide 43 Transf erable Relat ionships Transf erable Relat ionships

DEPARTMENT # * code PERSON # * id * last name * first name works in employs

Personnel Finance Sales Head office

Slide 44 Non-Transf erable Relat ionships Non-Transf erable Relat ionships COMPANY

#

* *

acquired from the source of

id name telephone num supplier num sales contact

* *

SUPPLIER

COPY * inventory num

• condition

OTHER

*