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

Slide 1 Slide 6 Compound UID - Composite Compound UID - Composite What would you need to know to identify a specific instance of ACCOUNT? Advanced Ent it y ACCOUNT BANK Relat ionship Concept s # * num # * num Use a UID bar to indicate Use # to indicate that that a relationship is part of the attribute is part of the the entity’s UID entity’s UID Slide 2 Slide 7 Compound UID - Relationships Compound UID - Relationships Advanced Concept s RENTAL ITEM What do you need to • UI Ds • Subt ypes Rental item requires the * rental period o return date rental transaction num know to identify a • I nt ersect ion • Exclusivit y specific instance of and the Ent it ies • Hist orical inventory num RENTAL ITEM? • Recur sive • Fan Traps Relat ionships RENTAL COPY • Non t ransf erabilit y # * transaction num # * inventory num • Roles * transaction date * purchase cost Slide 3 Slide 8 Mult i-Level Relat ionship Mult i-Level Relat ionship Simple Unique I dent if ier Simple Unique I dent if ier UI Ds UI Ds PERFORMANCE PLAY TICKET VENUE # * date * seat number # * title # * name # * time CUSTOMER # * customer num CUSTOMER # * name What would you need to know to Single attribute identify a specific instance of TICKET? Tag the UID with # Slide 4 Slide 9 Compound UI D - At t ribut es Compound UI D - At t ribut es Mult i-Level Relat ionship Mult i-Level Relat ionship UI Ds UI Ds Members get a new membership each year PERFORMANCE PLAY but keep t heir membership number. TICKET VENUE # * date * seat number # * title # * name # * time Venue name + Play title + CUSTOMER MEMBERSHIP Performance date + # * name # * num # * start date Performance time + Multiple attributes Customer name Slide 5 Slide 10 Compound UID - Composite Compound UID - Composite Mult iple UI Ds Mult iple UI Ds badge num EMPLOYEE ACCOUNT BANK # (1) * badge num # * num * num payroll num # (2) * first name # (2) * last name # (3) * payroll num What would you need to know to identify first and last name a specific instance of ACCOUNT?

Slide 11 Slide 16 Composit ion of a UI D Unique I dent if iers Unique I dent if iers • • Example A Example A SUPPLIER PERSON # CODE # NAME # DATE OF BIRTH CATALOG ITEM CATALOG ITEM * purchase price # * item num • Example B Example B • distributed distributed * purchase price for for OR by by SUPPLIER TITLE for for for for distributor available as available as distributor available as supplier of available as supplier of of of SUPPLIER TITLE SUPPLIER SUPPLIER TITLE TITLE # * prod code # * prod code # * supplier no # * supplier no TITLE TITLE * name * name * name * name • Example C Example C • part of part of RENTAL RENTAL ITEM # LINE NO composed composed of of Slide 12 Slide 17 Modeling Recursive Modeling Recursive Primary Unique I dent if ier Primary Unique I dent if ier Relat ionships Relat ionships ...but I’m HIS ...and mine manager! • An ent it y can have several unique • An ent it y can have several unique ident if iers. ident if iers. • One may be appoint ed as t he • One may be appoint ed as t he ...he’s my manager primary unique ident if ier. primary unique ident if ier. • Only t he P • Only t he P rimary UI D is shown in rimary UI D is shown in t he ent it y relat ionship diagr am. manager of t he ent it y relat ionship diagr am. EMPLOYEE managed by Slide 13 Slide 18 I nt ersect ion Ent it y Modeling Hierarchical Dat a Modeling Hierarchical Dat a TEAM # name Company RENTAL DEPARTMENT COPY # * name # * transaction num Division # * inventory num * transaction date * purchase cost Department DIVISION # * name Team RENTAL ITEM COPY RENTAL * rental period COMPANY # * inventory num # * transaction num o return date * purchase cost # * name * transaction date Slide 14 Slide 19 I nt ersect ion Ent it ies: I nt ersect ion Ent it ies: Hierarchies as Recursive Hierarchies as Recursive I dent if ying t he P I dent if ying t he P roblem roblem Relat ionships Relat ionships TEAM # name made up of DEPARTMENT supplier of TITLE SUPPLIER # * name ORGANIZATION # * prod code ELEMENT # * supplier no supplied by * name * name # * name DIVISION within * type # * name In which entity would you store the attribute: COMPANY purchase price? # * name Slide 15 Slide 20 I nt ersect ion Ent it ies I nt ersect ion Ent it ies Net work St ruct ures Net work St ruct ures a part of CATALOG ITEM * purchase price COMPONENT # * identifier for for made up of available as supplier of SUPPLIER TITLE TITLE # * prod code # * supplier no * name * name

Slide 21 Slide 26 Net work St ruct ures Net work St ruct ures Subt ypes Subt ypes a part of TITLE # * product code COMPONENT * title * description # * identifier made up of MOVIE GAME * category * category * duration * medium O audio * minimum memory made up of COMPONENT COMPONENT # * identifier # * identifier a part of All titles are either movies or games Slide 22 Slide 27 Creat ing a Subt ype Net work St ruct ures Net work St ruct ures I n ERD: In RON: I n ERD: ASSEMBLY RULE Set Property Type of Type of . . o quantity Set Property Drag and drop. Drag and drop. or or or for for made up of a part of Draw new ent it y Draw new ent it y Drag and drop. Drag and drop. f ully wit hin or and f ully wit hin or COMPONENT COMPONENT ar ound t he ar ound t he Select association for Select association for # * identifier # * identifier ot her . ot her . dropped entity. dropped entity. Select Associations made up of COMPONENT COMPONENT Sub Entities Type of # * identifier # * identifier a part of Slide 23 Slide 28 Net work St ruct ures Exclusive Ent it ies Net work St ruct ures Exclusive Ent it ies COPY ASSEMBLY acquired from COMPANY * inventory num RULE o condition the source of o quantity * id for for # * name made up * telephone num a part of of o supplier num MEMBERSHIP held by o sales contact COMPONENT # * num the holder # * identifier * start date of * expiry date o termination Company in two roles Slide 24 Slide 29 I dent if ying Roles I dent if ying Roles Split t ing Ent it ies Split t ing Ent it ies ENROLLMENT COMPANY * date enrolled COPY * fee acquired from # id * inventory num * name for for o condition telephone num * taken by SUPPLIER included in taught by INSTRUCTOR * COURSE supplier num # * id the source of SECTION MEMBERSHIP STUDENT * sales contact * name the * salary # * id held by teacher * location # * num * name of * start date * start date for COURSE the holder * expiry date of OTHER # * code the o termination * name subject of Could teacher-student-person be so modeled? Slide 25 Slide 30 Modeling Roles Modeling Roles Nest ing Ent it ies Nest ing Ent it ies enrolled PERSON ENROLLMENT Business Rule: only on # * id EMPLOYEE * date enrolled * name sales r eps can dr ive for * fee o salary company cars the for SALES authorized teacher to drive CLERICAL of taught REP CAR by driven by taken by COURSE HUMAN COURSE for # * code RESOURCES SECTION TELESALES * name * location included in * start date

Slide 31 Slide 36 Nest ing Ent it ies Nest ing Ent it ies Modeling Dat a over Time Modeling Dat a over Time AIRCRAFT What if you need to hold an apartment’s AIRPLANE rental history? POWERED AIRPLANE PROP PLANE GLIDER PERSON APARTMENT JET PLANE rented by # * id # * code * last name the renter of * address * first name OTHER HELICOPTER HOVERCRAFT AIRCRAFT Slide 32 Slide 37 Modeling Dat a over Time Modeling Dat a over Time Recursive Subt ypes Recursive Subt ypes PERSON ORGANIZATION ELEMENT for RENTAL HISTORY of ORGANIZATION # * id ORGANIZATION ELEMENT * last name # * from date the classification TYPE the renter of * first name to date o for within SUBDIVISION for rented by APARTMENT made up of # * code * address Slide 33 Slide 38 Modeling Exclusive Modeling Exclusive Modeling Dat a over Time Modeling Dat a over Time Relat ionships Relat ionships COMPANY * name held by * postal area employed the holder of 0 contact name by MEMBERSHIP o MEMBER COMPANY * num # * id the # * code * start date last name * employer * name * expiry date * first name of CUSTOMER the holder of o termination o * num * first name held by * last name Slide 34 Slide 39 Creat ing an Arc Creat ing an Arc Modeling Dat a over Time Modeling Dat a over Time PUBLICATION EMPLOYMENT CATALOG HISTORY ENTRY # * from date of to date o for for of in in the employed employer REVIEW by of MEMBER COMPANY # * id # * code last name * * name * first name Slide 35 Slide 40 Modeling Exclusivit y Modeling Exclusivit y Fan Traps Fan Traps “We offer membership to individual customers and companies” PERSON POSITION MEMBERSHIP the holder of MEMBERSHIP # * id # * job title INDIVIDUAL ORGANIZATION * last name job description o held by * first name MEMBERSHIP employed by included in CUSTOMER COMPANY CUSTOMER COMPANY COMPANY MEMBER # * code the * name the CUSTOMER COMPANY employer of employer of