1 / 59

ITS232 Introduction To Database Management Systems

ITS232 Introduction To Database Management Systems . Siti Nurbaya Ismail Faculty of Computer Science & Mathematics, Universiti Teknologi MARA ( UiTM ), Kedah | sitinurbaya@kedah.uitm.edu.my | http://www.sitinur151.wordpress.com | | A2-3039 | ext:2561 | 019-5710562 |. CHAPTER 4

pegeen
Télécharger la présentation

ITS232 Introduction To Database Management Systems

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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

More Related