Entity Relationship Modeling: Understanding Entities, Attributes, and Relationships, Slides of Introduction to Database Management Systems

Download Entity Relationship Modeling: Understanding Entities, Attributes, and Relationships and more Slides Introduction to Database Management Systems in PDF only on Docsity! Entity Relationship (ER) Modeling Docsity.com In this chapter, you will learn: • How relationships between entities are defined and refined, and how such relationships are incorporated into the database design process • How ERD components affect database design and implementation • How to interpret the modeling symbols for the four most popular ER modeling tools • That real-world database design often requires that you reconcile conflicting goals Docsity.com Attributes • Characteristics of entities • In Chen model, attributes are represented by ovals and are connected to the entity rectangle with a line • Each oval contains the name of the attribute it represents • In the Crow’s Foot model, the attributes are simply written in the attribute box below the entity rectangle Docsity.com The Attributes of the TUDENT Fntitv FIGURE 4.1 THE ATTRIBUTES OF THE STUDENT ENTITY Chen Model Crow’s Foot Model STUDENT STU_LNAME STU_FNAME STU_INITIAL STULE-MAIL STU PHONE Docsity.com Domains • Attributes have a domain: – The attribute’s set of possible values • Attributes may share a domain Docsity.com Attributes • Composite attribute • Simple attribute • Single-value attribute • Multivalued attributes Docsity.com A Multivalued Attribute in an Entitv FIGURE 4.3 A MULTIVALUED ATTRIBUTE IN AN ENTITY Chen Model CAR YEAR Crow’s Foot Model CAR PK CAR VIN MOD_CODE CAR YEAR CAR COLOR Docsity.com Resolving Multivalued Attribute Problems • Although the conceptual model can handle multivalued attributes, you should not implement them in the relational DBMS – Within original entity, create several new attributes, one for each of the original multivalued attribute’s components • Can lead to major structural problems in the table – Create a new entity composed of original multivalued attribute’s components Docsity.com A New Entity Set Composed of a Multivalued Attribute’s Components Docsity.com Derived Attributes • Attribute whose value may be calculated (derived) from other attributes • Need not be physically stored within the database • Can be derived by using an algorithm Docsity.com Depiction of a Derived Attribute FIGURE 4.6 DEPICTION OF A DERIVED ATTRIBUTE Chen Model Crow’s Foot Model EMPLOYEE heen peeemeemee PK |EMP_NUM EMP_LNAME EMP_FNAME EMP INTIAL EMP_DOB EMP_AGE ® Docsity.com Connectivity and Cardinalitv in an FRN FIGURE 4.7 CONNECTIVITY AND CARDINALITY IN AN = PROFESSOR os > Chen Model Connectivities Cardinalities Crow’s Foot Model Connectivities PROFESSOR — CLASS | ae CLASS cia Cardinalities Docsity.com RELATIONSHIP Strength • Existence dependence – Entity’s existence depends on the existence of one or more other entities • Existence independence – Entity can exist apart from one or more related entities • Weak (non-identifying) relationships – One entity is not existence-independent on another entity • Strong (Identifying) Relationships – Related entities are existence-dependent Docsity.com A Weak (Non-ldentifying) Relationship Between COURSE and CLASS FIGURE 4.8 A WEAK (NON-IDENTIFYING) RELATIONSHIP BETWEEN COURSE AND CLASS COURSE GRS_CODE CRS_TITLE CRS_DESCRIPTION CRS_CREDITS CLASS CODE CLASS_SECTION CLASS_TIME CRS_CODE Docsity.com A Strong (Identifying) Relationship Between COURSE and CLASS FIGURE 4.10 A STRONG (IDENTIFYING) RELATIONSHIP BETWEEN COURSE AND CLASS COURSE CRS CODE CLASS SEC rion CRS _ CODE CRS_TITLE CRS_DESCRIPTION CRS_CREDITS DEPT_CODE CRS _CODE DEPT_CODE ACCT-212 c1s-220 crs_420 MATH-245 om_261 OM-s62> CRS_CODE | CL4ASS_SECTION ACCT-214 ACCT 211 ACCT-212 ACCT-212 cIS-220, cls-220 cIS-220 cIS-420 MATH 245 M261 om 261 @M-s62 om-_s62 NANe+suN4N40N- CLASS Time PROF_Nuss ROOM_ CODE CRS _DESCRIPTION Accounting | Accounting Il Intra. ta Micracomputing Detabase Design and Implementation Mathematics for Managers Intro. to Statietice Statistical Spplicetions: clLass_TMeE ROOM_CODE MAF S00 5:50 a.m BUSSIt MAF 9:00-3:50 am BuUs200 TT 2:30-3:45 p.m. BUSZs2 MAF 10:00-10:504m. SlISS44 Th 6:00-8:40 pom BUS252 MAF 9000-9 50 x rn KLR20S MVE 3:00-3:50 a.m KLR211 MAF 10:00-10:50 a.m, KLR20S v¥ 6:00-6:40 pam. KLR209 Th 6:00-8:40 p.m DREISS MAF &:00-8:50 am KLR2O0 71h 1:00-2:15 p.m. KLRZO0 MF 11:00-11:50am. KLR2O00 TT 2:30-3:45 p.m. KLR200 CRS _CREDIT PROF _NUM 105 405 Baz 304 304 228 444 228 162 325 444 444 4162 162 Docsity.com An Optional CLASS Entity in the Relationship PROFESSOR teaches CLASS Docsity.com COURSE and CLASS in a Mandatory Relationship FIGURE 4.13 COURSE AND CLASS IN A MANDATORY RELATIONSHIP COURSE Chen Model 1 (1,N) (1,1) Crow’s Foot Model COURSE M Sieve CO} cass CLASS Laff a me SENET pt ) Docsity.com A Weak Entity in a Strong Relationship FIGURE 4.15 A WEAK ENTITY IN A STRONG RELATIONSHIP EMP_NUM | EMP_LLMAME | EMP_FNAME | EMP_INITIAL TE Callitante Jeanine 1002 Smithson william 1003 Vvashington Herman 1004 Chen Lydia 1005 Johnson Melanie 1006 Ortega Jorge 1007 O'Donnell Peter 1008 Brzenski Barbara EMP_NUM 1001 1003 1006 1008 1008 1008 DEP_NUM | DEP_FNAME 1 Annelise 2 Jorge 1 Suzanne 1 Carlos 1 Michael 2 George 3 Katherine DEP_DOB 05-Dec-97 30-Sep-02 25-Jan-04 25-May-01 19-Feb-95 27-Jun-98 18-Aug-03 EMP_DOB 12-Mar-64 23-Nov-70 15-Aug-68 23-Mar-74 28-Sep-66 42-Jul-79 10-Jun-74 12-Feb-70 EMP_HIREDATE 25-Wtay-S7 23-May-37 20-May-37 45-Oct-95 20-Dec-95 05-Jan-02 23-Jun-02 01-Nov-03 Docsity.com Relationship Degree • Indicates number of associated entities or participants • Unary relationship – Association is maintained within a single entity • Binary relationship – Two entities are associated • Ternary relationship – Three entities are associated Docsity.com Three Types of Relationships FIGURE 4.16 THREE TYPES OF RELATIONSHIPS Chen Models RECIPIENT Binary Ternary M4 PROFESSOR CONTRIBUTOR 1 e ech FUND MM CLASS. Crow’s Foot Models Binary relationship SaaS Unary relationship =e era eourse PRoPESsoR oo Lay Seas od dlernary relationship cen iS dsihurest in Docsity.com An ER Representation of Recursive Relationships FIGURE 4.18 AN ER REPRESENTATION OF RECURSIVE RELATIONSHIPS Chen Model EMPLOYEE Crow’s Foot Model EMPLOYEE EMPLOYEE manpges is manqgged by reqlires is prerequisite to is marfied to is spopse of COURSE EMPLOYEE EMPLOYEE Docsity.com The 1:1 Recursive Relationship “EMPLOYEE is Married to EMPLOYEE” Docsity.com Implementation of the M:N Recursive “PART Contains PART” Relationship Docsity.com Converting the M:N Relationship into Two 1:M Relationships FIGURE 4.24 CONVERTING THE M:N RELATIONSHIP INTO TWO 1:M RELATIONSHIPS Table name: STUDENT [ sTu_NumM [| STU_LLNAME | Bowser 324257 Smithson Table name: ENROLL CLASS_CODE | STU_NUM | ENROLL_GRADE | » E 321452 40014 324257 B 10018 321452 A 10018 324257 B 10021 321452 ¢ 40021 324257 C Table name: CLASS Database name: Ch04_CollegeTry [cLass_cobe | CRS_CODE [ CL&SS_SECTION [ b+ ACCT-211. (3 + 10018 cis-220 2 + 10021 OM-261 1 CLASS_TME | CLASS_ROOM | PROF_NUM | TTh 2:30-3:45 pm. BUS252 342 MAF S:00-3:50 am. KLR211 414 MVVF 8:00-8:50 am. KLR200 114 Docsity.com The M:N Relationship Between STUDENT and CLASS FIGURE 4.25 THE M:N RELATIONSHIP BETWEEN STUDENT AND CLASS Chen Model M N (0,N) (0,N) STUDENT Crow’s Foot Model wn in, tof JcLass Lipo ai eprlledin oo -O¢ Docsity.com A Composite Entity in an FIGURE 4.26 A COMPOSITE ENTITY IN AN ERD FRI Chen Model CLASS (O,N) (1,1) @ sTU_LNAME 9) STUDENT (1,1) (0,N) ENROLL_GRADE Crow’s Foot Model ROOM_CODE PK sTuU_NUM ENROLL CLASS pp iSite In eyed PK,FK2 STU_LNAME PK,FK1 CLASS CODE CLASS CODE - : ENROLL_GRADE CLASS_SECTION CRS_CODE CLASS_TiME ROOM_CODE PROF_NUM Docsity.com A Generalization Hierarchv FIGURE 4.28 A GENERALIZATION HIERARCHY Supertype EMPLOYEE se Docsity.com Disjoint Subtypes • Also known as non-overlapping subtypes – Subtypes that contain a subset of the supertype entity set – Each entity instance (row) of the supertype can appear in only one of the disjoint subtypes • Supertype and its subtype(s) maintain a 1:1 relationship Docsity.com The EMPLOYEE/PILOT Supertype/Subtype Relationship FIGURE 4.29 THE EMPLOYEE/PILOT SUPERTYPE/SUBTYPE RELATIONSHIP Table name: EMPLOYEE (the supertype) Database name: Ch04_AirCo [ EmP_wumt [ EmP_LMaMe | EMP_HIRE DATE | SI 15-Mar-85 +) 25-Apr-89 g 102 Vandam 20-Dec-93 + 103 Jones 26-Aug-03 + 104 Lange 20-Oct-97 £3 105 v¥illiams 08-Nov-97 + 106 Duzak 05-Jan-04 +) 107 Diante 02-Jul-37 +] 106 iesenbach 18-Nov-95. + 109 Travis 14-Apr-01 I 110 Genkazi 01-Dec-0353 Table name: PILOT (the subtype) [_emP_num | PIL_LICENSE [| PIL_RATINGS [_PIL_MED_TYPE | el Ty are SEL/MELAnstriCFil 4 +] 104 ATP SEL/MEL¢Anstr 1 as 105 COM SELJMELAnstriCrl 2 I 106 COM SEL/MELAnstr oz +I 109 COM SEL/MELASESAnstr!Cril 1 Docsity.com The Chen Representation of the Invoicing Problem FIGURE 4.32 THE CHEN REPRESENTATION OF THE INVOICING PROBLEM d M 1 CUSTOMER iret CO INVOICE (O,N) (,1) (1,N) This model may be read as follows: (1,1) M each CUSTOMER may generate one or more INVOICEs each INVOICE is generated by one CUSTOMER aa M each INVOICE contains one or more invoice LINEs each invoice LINE is contained in an INVOICE Onn each invoice LINE references one PRODUCT PRODUCT each PRODUCT may be referenced in one or more invoice LINEs 3 Docsity.com The Crow’s Foot Representation of the Invoicing Problem FIGURE 4.33 THE CROW’S FOOT REPRESENTATION OF THE INVOICING PROBLEM CUSTOMER INVOICE Generates This model may be read as follows: each CUSTOMER may generate one or more INVOICEs each INVOICE is generated by one CUSTOMER each INVOICE contains one or more invoice LINEs each invoice LINE is contained in one INVOICE referdnces each invoice LINE references one PRODUCT each PRODUCT may be referenced in one or more invoice LINEs PRODUCT 3 Docsity.com The Rein85 Representation of the Invoicing Problem FIGURE 4.34 THE REIN85 REPRESENTATION OF THE INVOICING PROBLEM generates CUSTOMER +-—_<—_] INVOICE This model may be read as follows: contains each CUSTOMER may generate one or more INVOICEs LINE each INVOICE is generated by one CUSTOMER each INVOICE contains one or more invoice LINEs each invoice LINE is contained in an INVOICE references each invoice LINE references one PRODUCT each PRODUCT may be referenced in one or more invoice LINEs PRODUCT @ Docsity.com A Supertype/Subtype Relationship FIGURE 4.36 A SUPERTYPE/SUBTYPE RELATIONSHIP 1 1 EMPLOYEE PROFESSOR (0,N) <> C) (1,1) CLASS (0,1) (0,1) 1 1 ADMINISTRATOR (0,1) ic © (1,1) SCHOOL 1 Docsity.com A Supertype/Subtype Relationship in an ERD FIGURE 4.37 A SUPERTYPE/SUBTYPE RELATIONSHIP IN AN ERD Chen Model 1 1 EMPLOYEE PROFESSOR (0,1) (1,1) Crow’s Foot Model EMPLOYEE PROFESSOR PK | EMP NUM PK,FK1 | EMP NUM EMP_LNAME PROF_SPECIALTY EMP_FNAME PROF_RANK EMP_INITIAL EMP_E_MAIL Docsity.com Components of the ER Model TABLE 4.2 COMPONENTS OF THE ER MODEL ENTITY RELATIONSHIP oO) era i hg ENTITY SCHOOL DEPARTMENT DEPARTMENT DEPARTMENT COURSE PROFESSOR PROFESSOR PROFESSOR PROFESSOR PROFESSOR STUDENT BUILDING ROOM S operates has employs offers generates is an is dean of chairs teaches advises enrolls in contains is used for Spay, os DEPARTMENT STUDENT PROFESSOR COURSE CLASS EMPLOYEE SCHOOL DEPARTMENT CLASS STUDENT CLASS ROOM CLASS Docsity.com of Various Implementations a 1:1 Recursive Relationship FIGURE 4.48 VARIOUS IMPLEMENTATIONS OF A 1:1 RECURSIVE RELATIONSHIP Table name: EMPLOYEE_V1 Database name: Ch04_PartCo EMP_NuM | EMP_LUN@MeE | EMP_FMaMe [ EMP_SPOUSE | >» James 347 Anne 349 First implementation Louise 345 348 Delaney Robert 349 Shapiro Anton 346 Table name: EMPLOYEE Table name: MARRIED_V1 EMP_NUM | EMP_UNSmMeE | EMP_FNAME | EmMP_NUM | EMP_SPOUSE | > Ramirez James: > 347 dimpl : 346 Jones Anne 346 ey) SSeend pee 347 Ramirez Louise 347 345 348 Delaney Robert 349 346 349 Shapiro Anton Table name: MARRIAGE Table name: MARPART Table name: EMPLOYEE [ marR_num | MAR_DATE | MAR_NUM | EMP_NuM | EMP_NUM [| EMP_UNAME | EMP_FNAME | ay 2 04-Mar-03 > 1 345 > Ramirez James: +] 2 02-Feb-3939 1 347 346 Jones Anne 2 346 347 Ramirez Louise 2 349 345 Delaney Robert 349 Shapiro Anton The Relational Sche: MARRIAGE MAR_DATE ma for the Third Implementation MARPART co Third implementation Docsity.com Summary • Entity relationship (ER) model – Uses ER diagrams to represent conceptual database as viewed by the end user – Three main components • Entities • Relationships • Attributes – Includes connectivity and cardinality notations • Connectivities and cardinalities are based on business rules Docsity.com Summary (continued) • ER symbols are used to graphically depict the ER model’s components and relationships • ERDs may be based on many different ER models • Entities can also be classified as supertypes and subtypes within a generalization hierarchy • Database designers are often forced to make design compromises Docsity.com