ITS232 Introduction To Database Management Systems

1. ITS232Introduction To Database Management Systems SitiNurbaya Ismail Faculty of Computer Science & Mathematics, UniversitiTeknologi MARA (UiTM), Kedah | sitinurbaya@kedah.uitm.edu.my | http://www.sitinur151.wordpress.com | | A2-3039 | ext:2561 | 012-7760562 | CHAPTER 4 Entity Relationship (E-R) Modeling (ERD)

2. Chapter 4: Entity Relationship (E-R) Modeling 4.0 Entity Relationship (E-R) Modeling 4.1 The Entity Relationship (ER) Model 4.2 Developing An E-R Diagram 4.3 Database Design Challenges

3. Chapter 4: Entity Relationship (E-R) ModelingBasic Modeling Concept A model is description or analogy used to visualize something that cannot be directly observed.

4. Chapter 4: Entity Relationship (E-R) ModelingBasic Modeling Concept • Relatively simple representations of complex real world data structures • Represents: • data structures and their characteristics • relation and constraints • Can be physical or abstract: • car, student = physical • subject, register = abstract • Used by database designer as: • communications tools to communicate and understanding between a client and the database designer, which the database to be develop.

5. Chapter 4: Entity Relationship (E-R) ModelingBasic Modeling Concept The importance of data modeling: • Data • constitute the most basic information units employed by a system • Application • is created to manage data and to transform data to information • View • different people views the same data differently based on their understanding • Model • helps different user to have the holistic view of the same data

6. Chapter 4: Entity Relationship (E-R) ModelingBasic Modeling Concept • Three Level ANSI-SPARC Architecture User 2 User 1 User n -user’s view External Model … View 1 View 2 View n 1. External level -designer’s view -h/w independent -s/w independent ERD Conceptual Model Conceptual Schema 2. Conceptual level -DBMS’s view -h/w independent -s/w dependent Internal Schema Internal Model 3. Internal level Database -h/w dependent -s/w dependent Physical Model Physical data organization

7. Chapter 4: Entity Relationship (E-R) Modeling4.1 The Entity Relationship (E-R) Model Based on the set theory and the relational theory, it is used as tools to: • translate different views of data among managers, users and programmers to fit into a common work • define data processing and constraints to help meet the different views • help implement the database • considered as a stage in a database design preceding the relational database modeling • gives data structures representation of: • what information have to be stored • the relationships between informational elements and constraint on the data structure • relationship

8. Chapter 4: Entity Relationship (E-R) Modeling4.1 The Entity Relationship (E-R) Model • ER model forms the basis of an ER diagram (ERD) • ERD represents conceptual database as viewed by end user • ERDs depict database’s main components: • Entities • Attributes • Relationships

9. Chapter 4: Entity Relationship (E-R) Modeling4.1 The Entity Relationship (E-R) Model: Entity Entity • Refers to entity set and not to single entity occurrence • Corresponds to table and not to row in relational environment • In both Chen and Crow’s Foot models, entity is represented by rectangle containing entity’s name • Entity name, a noun, is usually written in capital letters

10. Chapter 4: Entity Relationship (E-R) Modeling4.1 The Entity Relationship (E-R) Model: Attribute Attribute • Characteristics of entities • Property that explains about entity • Correspondents to fields of a table • Primary key are underline with a straight line • Foreign key are underline with dotted line or an * • Chen Model • attributes are represented by ovals and are connected to entity rectangle with a line • each oval contains the name of attribute it represents • Crow’s Foot Model • attributes are written in attribute box below entity rectangle

11. Chapter 4: Entity Relationship (E-R) Modeling4.1 The Entity Relationship (E-R) Model The Attributes of the STUDENT entity: Chen & Crow’s Foot

12. Chapter 4: Entity Relationship (E-R) Modeling4.1 The Entity Relationship (E-R) Model: Relationship Relationship • Associates between entities • Logical interaction among the entities in a relational database • Operate in both directions • Chen Model • Crow’s Foot Model

13. Chapter 4: Entity Relationship (E-R) Modeling4.2 Developing An E-R Diagram Development of ER model is an Iterative Process that involved: • Step1: General narrative of organizational operations developed • Step2: Basic E-R Model graphically depicted and reviewed • Step3: Modifications made to incorporate newly discovered ER components • Repeat process: Until designers and users agree on complete E-R Diagram

14. Chapter 4: Entity Relationship (E-R) Modeling4.2 Developing An E-R Diagram: Model Components

15. Chapter 4: Entity Relationship (E-R) Modeling4.2 Developing An E-R Diagram: Chen Model: Entity Entity • Corresponds to table and not to row in relational environment • Represented by rectangle containing entity’s name • Entity name, a noun, is usually written in capital letters • Examlpe: Entity STUDENTwith attributes

16. Chapter 4: Entity Relationship (E-R) Modeling4.2 Developing An E-R Diagram: Chen Model: Entity Variations of Entity: • Weak Entity • Recursive Entity • Composite Entity • Entity Supertype and Subtype

17. Chapter 4: Entity Relationship (E-R) Modeling4.2 Developing An E-R Diagram: Chen Model: Entity Variations of Entity: • Weak Entity • Existence-dependent • Primary key partially or totally derived from parent entity in relationship • Database designer determines whether an entity is weak based on business rules

18. Chapter 4: Entity Relationship (E-R) Modeling4.2 Developing An E-R Diagram: Chen Model: Entity

19. Chapter 4: Entity Relationship (E-R) Modeling4.2 Developing An E-R Diagram: Chen Model: Entity

20. Chapter 4: Entity Relationship (E-R) Modeling4.2 Developing An E-R Diagram: Chen Model: Entity employeeMANAGER employeeSPOUSE employeeNAME employeeNAME employeeNO employeeNO 1 1 EMPLOYEE EMPLOYEE 1 1 married manage Variations of Entity: • Recursive Entity • Entity set that have relationship with the same entity set • Example: EMPLOYEE entity

21. Chapter 4: Entity Relationship (E-R) Modeling4.2 Developing An E-R Diagram: Chen Model: Entity grade courseNAME studentNAME enroll M N courseID studentID COURSE STUDENT COURSE STUDENT STUDENT_COURSE / ENROLL Variations of Entity: • Composite Entity • Originally a relationship between 2 entities that involved in M:N relationship • Composite entity takes its primary key from both entities that it bridges • Example: enroll

22. Chapter 4: Entity Relationship (E-R) Modeling4.2 Developing An E-R Diagram: Chen Model: Entity Variations of Entity: • Entity Supertype & Subtype • Parent-Child relationship • Supertypecontains the shared attributes an entity type that include distinct subclasses that required to be presented in data model parent • Subtype contains the unique attributes an entity type that has a distinct role and also a member of supertype child

23. Chapter 4: Entity Relationship (E-R) Modeling4.2 Developing An E-R Diagram: Chen Model: Entity Variations of Entity: • Entity Supertype & Subtype • Example: Superype (EMPLOYEE) Subtype (Engineer & Full-Time)

24. Chapter 4: Entity Relationship (E-R) Modeling4.2 Developing An E-R Diagram: Chen Model: Entity G Gs Variations of Entity: • Entity Supertype & Subtype • Have two types of relationship:

25. Chapter 4: Entity Relationship (E-R) Modeling4.2 Developing An E-R Diagram: Chen Model: Entity Disjoint G Gs Overlap Variations of Entity: • Entity Supertype & Subtype • Example: Disjoint & Overlap

26. Chapter 4: Entity Relationship (E-R) Modeling4.2 Developing An E-R Diagram: Chen Model: Entity Variations of Entity: • Entity Supertype & Subtype • Example: Disjoint

27. Chapter 4: Entity Relationship (E-R) Modeling4.2 Developing An E-R Diagram: Chen Model: Entity Variations of Entity: • Entity Supertype & Subtype • Example: Disjoint & Overlap

28. name course STUDENT studentID Chapter 4: Entity Relationship (E-R) Modeling4.2 Developing An E-R Diagram: Chen Model: Attributes email address Attributes • Represented by ovals that are connected to entity with a line • Oval contains of attribute (field) it represents • PK are underlined with straight line • FK are underlined with doted line or * • Example: Entity STUDENTwith attributes name, course, studentID, address, email

29. Chapter 4: Entity Relationship (E-R) Modeling4.2 Developing An E-R Diagram: Chen Model: Attributes Attributes types: • Simple Attributes • Composite Attributes • Multivalued Attributes • Derived Attributes

30. studentID STUDENT Chapter 4: Entity Relationship (E-R) Modeling4.2 Developing An E-R Diagram: Chen Model: Attributes addressPOSTCODE addressNO addressTOWN address phoneNO Simple Attributes age name gender Attributes types: • Simple Attributes • An attribute composed of single component with an independent existence • Cannot be subdivided into smaller components • Example: gender, martial statues

31. studentID STUDENT Chapter 4: Entity Relationship (E-R) Modeling4.2 Developing An E-R Diagram: Chen Model: Attributes addressPOSTCODE addressNO addressTOWN address Composite Attributes phoneNO age name gender Attributes types: • Composite Attributes • An attribute composed of multiple components, each with an independent existence • Can be further subdivide to yield additional attributes • Example: name first, middle, last address street, city, state, zip

32. Chapter 4: Entity Relationship (E-R) Modeling4.2 Developing An E-R Diagram: Chen Model: Attributes Attributes types: • Multivalued Attributes • attribute that holds multiple values for each occurrence of an entity type • Should not be implemented multivalued attributes in relational database • Can simplifies multivalued attributes by: • Create several attributes • Create new entity of the original multivalued attributes components • Example: phone number handset,office,home qualification diploma,degree,master

33. STUDENT STUDENT studentID Chapter 4: Entity Relationship (E-R) Modeling4.2 Developing An E-R Diagram: Chen Model: Attributes address handsetNO age studentID homephoneNO handsetNO gender address homephoneNO age has CONTACT gender studentID* Attributes types: • Multivalued Attributes • Can simplifies multivalued attributes by: • Create several attributes • Create new entity of the original multivalued attributes components

34. studentID STUDENT Chapter 4: Entity Relationship (E-R) Modeling4.2 Developing An E-R Diagram: Chen Model: Attributes address phoneNO name age Derived Attributes gender Attributes types: • Derived Attributes • An attributes that represents a value that is derived from the value of related attribute or set of attributes, not necessarily in the same entity type. • Need not be physically stored within database • Example: age, cgpa

35. Chapter 4: Entity Relationship (E-R) Modeling4.2 Developing An E-R Diagram: Chen Model: Attributes

36. Chapter 4: Entity Relationship (E-R) Modeling4.2 Developing An E-R Diagram: Chen Model: Relationship Relationship • Associations between entities • Logical interaction among the entities in a relational database • Operates in both directions • Naming Relationships: • Relationship name is a verb phrase • Avoid vague names • Defining Relationships: • Definition explains what action is being taken and why it is important • Give examples to clarify the action • Optional participation should be explained • Explain reasons for any explicit maximum cardinality • Explain any restrictions on participation in the relationship • Explain extent of the history that is kept in the relationship • Explain whether an entity instance involved in a relationship instance can transfer participation to another relationship instance

37. Chapter 4: Entity Relationship (E-R) Modeling4.2 Developing An E-R Diagram: Chen Model: Relationship Relationship is described by: • Degree of the relationship • Connectivity of the relationship • Cardinality of the relationship • Participation

38. Chapter 4: Entity Relationship (E-R) Modeling4.2 Developing An E-R Diagram: Chen Model: Relationship Relationship is described by: • Degree of the relationship • Indicates number of associated entities within the relationship • There are three types: • Unary Relationship • Association is maintained within single entity • Binary Relationship • Two entities are associated c. Ternary Relationship • Three entities are associated

39. Chapter 4: Entity Relationship (E-R) Modeling4.2 Developing An E-R Diagram: Chen Model: Relationship

40. Chapter 4: Entity Relationship (E-R) Modeling4.2 Developing An E-R Diagram: Chen Model: Relationship 1 1 1 M under have RECTOR PROGRAM STUDENT UiTMBRANCH M N 1 register COURSE STUDENT Relationship is described by: • Connectivity of the relationship • Logical interaction among entities in a relational database • There are three types: • 1:1 • 1:M • M:N

41. Chapter 4: Entity Relationship (E-R) Modeling4.2 Developing An E-R Diagram: Chen Model: Relationship Relationship is described by: • Cardinality of the relationship • Express the specific number of entity occurrences associated with one occurrence of the related entity • Function of organizational policy  business rules

42. Chapter 4: Entity Relationship (E-R) Modeling4.2 Developing An E-R Diagram: Chen Model: Relationship (1,9) (0,35) register COURSE STUDENT N M Relationship is described by: • Cardinality of the relationship • Example 1: One student can register 1 to 9 courses One course maximum can have 35 student

43. Chapter 4: Entity Relationship (E-R) Modeling4.2 Developing An E-R Diagram: Chen Model: Relationship (0,3) (1,1) teach COURSE LECTURER M 1 Relationship is described by: • Cardinality of the relationship • Example 2: One lecturer can teaches maximum 3 courses One course can be thought by 1 lecturer only

44. Chapter 4: Entity Relationship (E-R) Modeling4.2 Developing An E-R Diagram: Chen Model: Relationship Relationship is described by: • Cardinality of the relationship • Example 3:

45. Chapter 4: Entity Relationship (E-R) Modeling4.2 Developing An E-R Diagram: Chen Model: Relationship Relationship is described by: • Cardinality of the relationship • Relationship Strength  Existence Dependence • Existencedependence • Entity exists in database only when it is associated with another related entity occurrence • Existenceindependence • Entity can exist apart from one or more related entities • Sometimes such an entity is referred to as a strong or regular entity

46. Chapter 4: Entity Relationship (E-R) Modeling4.2 Developing An E-R Diagram: Chen Model: Relationship Relationship is described by: • Cardinality of the relationship • From Existence Dependence, exist two relationship strength: • Weak Relationship • Entity not existence-independent on other entity • PK of related entity doesn’t contain PK component of parent entity • Non-Identifying Relationship • Strong Relationship • Existence dependence • PK of related entity contains PK component of parent entity • Identifying Relationship

49. Chapter 4: Entity Relationship (E-R) Modeling4.2 Developing An E-R Diagram: Chen Model: Relationship Database Systems, 9h Edition

50. Chapter 4: Entity Relationship (E-R) Modeling4.2 Developing An E-R Diagram: Chen Model: Relationship