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 | 019-5710562 | 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 holistic view of the same data View 1 Model Data Application View 2 View n The importance of data modeling:

7. 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

8. 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

9. 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

10. Chapter 4: Entity Relationship (E-R) Modeling4.1 The Entity Relationship (E-R) Model: Entity STUDENT SUBJECT STUDENT SUBJECT 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

11. Chapter 4: Entity Relationship (E-R) Modeling4.1 The Entity Relationship (E-R) Model: Entity Entity • An entity is a thing thatexists and can be distinguishable • concrete/physical (individual, place, object) • conceptual/abstract (events, concepts) • Examples; • Jeffry, 2009222624  STUDENT • Mathematics II, MAT111  SUBJECT • Have property that can explains about the entity • Entity set  a set of entity

12. 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 *

13. name name course STUDENT SUBJECT credit studentID code Chapter 4: Entity Relationship (E-R) Modeling4.1 The Entity Relationship (E-R) Model: Attribute

14. name name course STUDENT SUBJECT credit studentID code Chapter 4: Entity Relationship (E-R) Modeling4.1 The Entity Relationship (E-R) Model STUDENT SUBJECT

15. Chapter 4: Entity Relationship (E-R) Modeling4.1 The Entity Relationship (E-R) Model: Relationship works has has works works Relationship • Associates between entities • Logical interaction among the entities in a relational database • Operate in both directions

16. 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

17. name course STUDENT studentID Chapter 4: Entity Relationship (E-R) Modeling4.2 Developing An E-R Diagram: Model Components STUDENT STUDENT works works

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

19. name course STUDENT studentID Chapter 4: Entity Relationship (E-R) Modeling4.2 Developing An E-R Diagram: Chen Model: Entity email address 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

20. 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

21. Chapter 4: Entity Relationship (E-R) Modeling4.2 Developing An E-R Diagram: Chen Model: Entity depNO employeeNO depDATE employeeNO 1 (0,N) has 1 (1,N) depNAME employeeNAME EMPLOYEE DEPENDENT DEPENDENT Variations of Entity: • Weak Entity • Existence dependency • Primary key derived from parent entity • Example: DEPENDENT entity EMPLOYEE(employeeNO, employeeNAME) DEPENDENT(employeeNO, depNO, depDATE, depNAME)

22. 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

23. 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 1 1 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

24. 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

25. Chapter 4: Entity Relationship (E-R) Modeling4.2 Developing An E-R Diagram: Chen Model: Entity Employee(emp_no, emp_name) Manager(emp_no, emp_name,bonus) Variations of Entity: • Entity Supertype & Subtype • Example: Superype (EMPLOYEE) Subtype (Engineer & Full-Time)

26. 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:

27. 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

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

29. 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

30. 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

31. 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

32. 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

33. 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

34. 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

35. 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

36. 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. • Should not implement derived attributes in relational database • Example: age, cgpa

37. 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

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 • Connectivity of the relationship • Cardinality of the relationship • Participation

39. STAFF Chapter 4: Entity Relationship (E-R) Modeling4.2 Developing An E-R Diagram: Chen Model: Relationship married teach teach CLAS INTERVIEW LECTURER CANDIDATE MANAGEMENT Relationship is described by: • Degree of the relationship • Indicates number of associated entities within the relationship • There are three types: • Unary Relationship • Binary Relationship • Ternary Relationship

40. Chapter 4: Entity Relationship (E-R) Modeling4.2 Developing An E-R Diagram: Chen Model: Relationship 1 1 1 M under have PROGRAM RECTOR UiTMBRANCH STUDENT M N 1 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. name staffNO name studentID STUDENT SUPERVISOR staffNOname ______ ____ ______ ____ supervise staffNO* refer refer STUDENT studentIDnamestaffNO ________ ____ ______ ________ ____ ______ Chapter 4: Entity Relationship (E-R) Modeling4.2 Developing An E-R Diagram: Chen Model: Relationship SUPERVISOR Relationship is described by: • Cardinality of the relationship • Relationship Strength  Existence Dependence • Entity’s existence depends on the existence of one or more other entities

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 • 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

46. Chapter 4: Entity Relationship (E-R) Modeling4.2 Developing An E-R Diagram: Chen Model: Relationship Relationship is described by: • Participation • Determines whether all or some entity occurrences participates in a relationship • There are two types: • Optional (Partial) • Condition in which other participating entity may or may not be associated with occurrences of the optional entity in the relationship • Mandatory (Total) • Condition in which one participating entity must be associated with one or more occurrences of the other participating entity in the relationship

47. Chapter 4: Entity Relationship (E-R) Modeling4.2 Developing An E-R Diagram: Chen Model: Relationship connectivity (0,3) (1,1) • One class can be thought • by one lecturer only ‘not all lecturer teach class’ optional participation for course teach One lecturer can teaches maximum 3 classes COURSE LECTURER cardinality M 1 ‘all class are teach’ (mandatory participation for lecturer) Relationship is described by: • Participation • Example 1: One lecturer can teaches maximum 3 courses One course can be thought by 1 lecturer only participation

48. Chapter 4: Entity Relationship (E-R) Modeling4.2 Developing An E-R Diagram: Comparison of E-R Model Alternate style developed to enable easier use of CASE tools. Chen Model The Chen notation favors conceptual modeling Crow’s Foot Model Crow’s Foot notation favors a more implementation-oriented approach UML Model UML notation can be used for both conceptual and implementation modeling

49. Comparison of E-R Modeling Symbols • Chapter 4: Entity Relationship (E-R) Modeling4.2 Developing An E-R Diagram: Comparison of E-R Model 1 M

50. Summary of Cardinality and Participation for a Strong Relationship • Chapter 4: Entity Relationship (E-R) Modeling4.2 Developing An E-R Diagram: Comparison of E-R Model 1 1 M 1 M 1 1 has has has has has has has M N M 1 1 M N has (0,1) (0,n) (1,1) (1,n) (1,n) (0,1) (1,n) (0,1) (1,1) (1,n) (1,1) (0,1) (1,1) (0,n) has has has has has has