570 likes | 684 Views
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 | 012-7760562 |. CHAPTER 4
E N D
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)
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
Chapter 4: Entity Relationship (E-R) ModelingBasic Modeling Concept A model is description or analogy used to visualize something that cannot be directly observed.
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.
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
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
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
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
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
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
Chapter 4: Entity Relationship (E-R) Modeling4.1 The Entity Relationship (E-R) Model The Attributes of the STUDENT entity: Chen & Crow’s Foot
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
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
Chapter 4: Entity Relationship (E-R) Modeling4.2 Developing An E-R Diagram: Model Components
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
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
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
Chapter 4: Entity Relationship (E-R) Modeling4.2 Developing An E-R Diagram: Chen Model: Entity
Chapter 4: Entity Relationship (E-R) Modeling4.2 Developing An E-R Diagram: Chen Model: Entity
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
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
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 • Supertypecontains 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
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)
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:
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
Chapter 4: Entity Relationship (E-R) Modeling4.2 Developing An E-R Diagram: Chen Model: Entity Variations of Entity: • Entity Supertype & Subtype • Example: Disjoint
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
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
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
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
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
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
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
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
Chapter 4: Entity Relationship (E-R) Modeling4.2 Developing An E-R Diagram: Chen Model: Attributes
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
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
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
Chapter 4: Entity Relationship (E-R) Modeling4.2 Developing An E-R Diagram: Chen Model: Relationship
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
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
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
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
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:
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
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
Chapter 4: Entity Relationship (E-R) Modeling4.2 Developing An E-R Diagram: Chen Model: Relationship Database Systems, 9h Edition
Chapter 4: Entity Relationship (E-R) Modeling4.2 Developing An E-R Diagram: Chen Model: Relationship