Advanced Entity Relationship Concepts Advanced Concepts UIDs Intersection

Advanced Entity Relationship Concepts

Advanced Concepts • UIDs • Intersection Entities • Recursive Relationships • Roles • • • Subtypes Exclusivity Historical Fan Traps Non transferability

Simple Unique Identifier CUSTOMER # * customer num Single attribute Tag the UID with #

Compound UID - Attributes Members get a new membership each year but keep their membership number. MEMBERSHIP # * num # * start date Multiple attributes

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

Compound UID - Relationships RENTAL ITEM What do you need tothe Rental item requires know to identify a num rental transaction specific and the instance of RENTAL ITEM? inventory num * rental period o return date RENTAL # * transaction num * transaction date COPY # * inventory num * purchase cost

Multi-Level Relationship UIDs TICKET * seat number PERFORMANCE # * date # * time PLAY # * title VENUE # * name CUSTOMER # * name What would you need to know to identify a specific instance of TICKET?

Multi-Level Relationship UIDs TICKET * seat number PERFORMANCE # * date # * time PLAY # * title VENUE # * name Venue name + Play title + CUSTOMER # * name Performance date + Performance time + Customer name

Multiple UIDs badge num EMPLOYEE payroll num first and last name #(1)* #(2) * #(3) * badge num first name last name payroll num

Composition of a UID • Example A PERSON SUPPLIER # CODE # NAME # DATE OF BIRTH • Example B distributed for by SUPPLIER TITLE available as distributor of SUPPLIER • Example C RENTAL ITEM # LINE NO TITLE part of RENTAL composed of

Primary Unique Identifier • An entity can have several unique identifiers. • One may be appointed as the primary unique identifier. • Only the Primary UID is shown in the entity relationship diagram.

Intersection Entity Note how Crows’ Feet Change! RENTAL COPY # * transaction num * transaction date RENTAL # * transaction num * transaction date # * inventory num * purchase cost RENTAL ITEM * rental period return date o COPY # * inventory num * purchase cost

Intersection Entities: Identifying the Problem supplier of TITLE # * prod code * name supplied by SUPPLIER # * supplier no * name In which entity would you store the attribute: purchase price?

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

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

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

Modeling Hierarchical Data TEAM # name Company Division Department Team DEPARTMENT # * name DIVISION # * name COMPANY # * name

Hierarchies as Recursive TEAM Relationships # name DEPARTMENT # * name made up of ORGANIZATION ELEMENT DIVISION # * name COMPANY # * name * type within

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

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

Network Structures ASSEMBLY RULE o quantity for a part of made up of COMPONENT # * identifier a part of COMPONENT # * identifier

Network Structures ASSEMBLY RULE o quantity for made up of a part of COMPONENT # * identifier

Identifying Roles ENROLLMENT * date enrolled * fee for taken by included in STUDENT # * id * name COURSE SECTION INSTRUCTOR # * id * name the * salary teacher of taught by * location * start date for COURSE the subject of # * code * name

Modeling Roles ENROLLMENT * date enrolled * fee enrolled on PERSON # * id * name o salary for taught by the teacher of taken by COURSE SECTION * location * start date for included in COURSE # * code * name

Subtypes TITLE # MOVIE * category * duration O audio * * * product code title description GAME * category * medium * minimum memory All titles are either movies or games

Creating a Subtype In RON: In ERD: Set Property Type of. Drag and drop. or or Drag and drop. Draw new entity and fully within or around the other. Select association for dropped entity. Select Associations Sub Entities Type of

Exclusive Entities COPY * inventory num o condition MEMBERSHIP # * num * start date * expiry date o termination COMPANY acquired from the source of held by the holder of # * id * name * telephone num o supplier num o sales contact Company in two roles

Splitting Entities COPY COMPANY acquired from # * inventory num o condition * * id name telephone num SUPPLIER the source of MEMBERSHIP # * num * start date * expiry date o termination held by the holder of Could teacher-student-person be so modeled? * supplier num * sales contact OTHER

Nesting Entities Business Rule: only sales reps can drive company cars authorized to drive EMPLOYEE SALES CLERICAL REP CAR driven by TELESALES HUMAN RESOURCES

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

Recursive Subtypes ORGANIZATION ELEMENT of ORGANIZATION SUBDIVISION ORGANIZATION ELEMENT the classification TYPE for within made up of

Modeling Exclusive Relationships COMPANY held by MEMBERSHIP * num * start date * expiry date o termination o o held by the holder of * name * postal area 0 contact name CUSTOMER * num * first name * last name

Creating an Arc PUBLICATION CATALOG of of in REVIEW in

Modeling Exclusivity “We offer membership to individual customers and companies” MEMBERSHIP INDIVIDUAL ORGANIZATION MEMBERSHIP CUSTOMER COMPANY MEMBER CUSTOMER COMPANY

Modeling Data over Time What if you need to hold an apartment’s rental history? APARTMENT # * code * address rented by the renter of PERSON # * id * last name * first name

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

Modeling Data over Time MEMBER # * id * last name * first name employed by COMPANY the employer of # * code * name

Modeling Data over Time EMPLOYMENT HISTORY ENTRY # * from date o to date for employed by MEMBER # * id * last name * first name for the employer of COMPANY # * code * name

Fan Traps PERSON POSITION the holder of # * id * last name * first name held by # * job title o job description employed by included in COMPANY the employer of # * code * name the employer of

Fan Traps PERSON # * id * last name * first name employed at for employed POSITION HISTORY as for * start date o end date COMPANY HISTORY * start date o end date POSITION for # * job title o job description held by the subject of for ORGANIZATION HISTORY * start date o end date for COMPANY the employer for # * code * name the employer for

Resolving Fan Traps EMPLOYMENT HISTORY # COMPANY at the employer for * start date o end date # * code * name as for a party to PERSON # * id * last name * first name POSITION included in # * job title o job description

Transferable Relationships PERSON # * id * last name * first name works in employs DEPARTMENT # * code Head office Personnel Finance Sales

Non-Transferable Relationships COMPANY # COPY * inventory num o condition acquired from * id * name * telephone num SUPPLIER the source of * supplier num * sales contact OTHER