1 / 60

Entity-Relationship Model and Enhanced Entity-Relation Model

Entity-Relationship Model and Enhanced Entity-Relation Model. Outline. Data Models Notation basics Understanding types of relationship Generalization/Specialization hierarchies. Data Models.

mlove
Download Presentation

Entity-Relationship Model and Enhanced Entity-Relation Model

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. Entity-Relationship Model and Enhanced Entity-Relation Model

  2. Outline • Data Models • Notation basics • Understanding types of relationship • Generalization/Specialization hierarchies

  3. Data Models Data Models : a collection of conceptual tools for describing data, data relationships, data semantics, and consistency constraints. The Entity-Relationship Model The Relational Model

  4. Entity-Relationship Model (ER Model) เป็นโมเดลที่ช่วยในการออกแบบระบบฐานข้อมูลโดยรวม (Overall Logical Structure) ให้กับองค์กร E-R Model เป็นโมเดลที่มีประโยชน์ และมีความสำคัญมาก เพราะสามารถแสดงความหมาย และความสัมพันธ์ของข้อมูลตามสภาพที่เป็นจริง (real world) ขององค์กรหนึ่งได้อย่างชัดเจน สำหรับการออกแบบในระดับหลักการ (Conceptual Level)

  5. Entity-Relationship Model (ER Model) Entity-Relationship Model (ER Model): perceives the real world as consisting of basic objects, called entities, and relationships among these objects. E-R Model was developed to facilitate database design by allowing specification of an enterprise schema, which represents the overall logical structure of a database. E-R Model is very useful because it can represent the meaning and relationship between objects of real world enterprise onto conceptual schema.

  6. Components of ER Model Entity set is a set of entities of the same type that share the same properties, or attributes. An entity is a thing or object in the real world that is distinguishable from all other objects. Relationship set is a set of relationships of the same type. Each relationship is a row which represents the relations among several entities from nentity sets (n >= 2).

  7. องค์ประกอบของ ER Model Entity set : เป็นเซ็ตของ entities ที่มีโครงสร้างข้อมูลแบบเดียวกัน (บางครั้งเราเรียกแต่ละ entity ว่า object หรือ thing) Relationship set : เป็นเซ็ตของความสัมพันธ์ (relationships) ระหว่าง Entity sets ที่มีโครงสร้างข้อมูลแบบเดียวกัน โดยที่แต่ละrelationship คือแต่ละแถวที่แสดงความสัมพันธ์ระหว่าง entities ที่มาจากnentity sets (n >= 2).

  8. An entity is represented by a set of Attributes Attributes are descriptive properties possessed by each memberof an entity set. Attribute คือข้อมูลที่แสดงลักษณะ และคุณสมบัติของ entity เช่น เลขประจำตัว ชื่อ-นามสกุล ส่วนสูง หมู่เลือด วันเดือนปีเกิด …. เป็นต้น Relational Model ER Model Name Std_ID Height Student Blood DoB

  9. Attribute value : ค่าของ attribute Each entity has a value for each of its attributes. For instance, a particular student entity may have the value 50051044 for Std_ID, the value Kanchana Boonmak for Std_Name, and the value 3.12 for GPA. For each attribute, there is a set of permitted values, called the domain, or value set of that attribute. For instance, the domain of attribute GPA might be the set of real number whose value is not greater than 4.0 and is not less than 0.0 .

  10. Attribute Type An attribute, as used in the E-R model, can be characterized by the following attribute types : Simple or composite : Simple : The attribute is a simple attribute if its value can not be divided into subparts. For example Std_ID, GPA. Composite : The attribute is a composite attribute if its value can be divided into subparts. For example Name, Address.

  11. Attribute Type (cont..) Single-valued or Multivalued : Single-valued : The attribute is a single-valued attribute if it has only one value for a particular entity. Multivalued : The attribute is multivalued attribute if it has a set of values for a particular entity. Derived: The attribute is a derived attribute if its value can be derived from the values of other related attributes or entities.

  12. Entity-Relationship Diagrams Key attribute Derived attribute Relationship set FirstName Sub_code LastName Lec_ID Sub_name Dept M M Age TTeach Subject Lecturer Specialization Sub_type Sec Address Credit Stree Add_No Descriptive attribute Relationship type Zipcode City Multivalued attribute Composite attribute

  13. Constraints An E-R schema may define certain constraints to which the contents of a database must conform. Such constraints are as following : Mapping cardinalities constraints Key constraints

  14. Mapping Cardinalities Mapping cardinalities or cardinality ratios, express the number of entities to which another entity can be associated via a relationship set. Degree of relationship : The number of participating in a relationship. • A relationship of degree two is called binary relationship. • A relationship of degree three is called ternary relationship. • A relationship of degree four is called Quarternary relationship

  15. Degree of relationship : The number of participating in a relationship. A relationship of degree two is called binary relationship. owns person car A relationship of degree three is called ternary relationship. Model License Year LastName Car Location FirstName P-ID Addr Rept_no Date participated Person Accident Damage_cost

  16. A relationship of degree four is called Quarternary relationship. Model License Year Car LastName Location Rept-No FirstName Date P-ID Tparticipated Person Accident Addr Insurance Policy-No Start_Date Amount

  17. Binary Relationship Mapping cardinalities are most useful in describing binary relationship sets, (although they can contribute to the description of relationship sets that involve more than two entity sets as mentioned above 2 slides.) Now, only binary relationship sets must be concentrated. For a binary relationship set R between entity sets A and B, the mapping cardinality must be one of the following: One-to-one One-to-many (or many-to-one) Many-to-many

  18. One-to-one mapping An entity in A is associated with at most one entity in B, and an entity in B is associated with at most one entity in A. LastName FirstName Proj_name Proj_No Budget GPA Std_ID 1 1 Twork Project Student

  19. One-to-many mapping An entity in A is associated with any number (zero or more) of entities in B. However, An entity in B can be associated with at most one entity in A. Firstname Lastname LastName FirstName Std_ID GPA Dept L_ID M 1 Tadvise Student Lecturer

  20. Many-to-many mapping An entity in A is associated with any number (zero or more) of entities in B and an entity in B is associated with any number (zero or more) of entities in A. Sub_name LastName FirstName Credit Sub_code Dept L_ID M M TTeach Subject Lecturer sec

  21. Crow’s Foot notation for ERD Crow’s Foot is a useful notation to become familiar with, as it is easy to understand and is supported by many of the CASE tools students may encounter in their database career

  22. Key in Database System We must have a way to specify how entities within a given entity set are distinguished. Conceptually, individual entities are distinct, however, the difference among them must be expressed in terms of their attributes. Therefore, the value of the attribute must be such that it can uniquely identify the entity. In other words, no two entities in an entity set are allowed to have exactly the same value for all attributes. A key allows us to identify a set of attributes that enough to distinguish entities from each other. Keys also help uniquely identify relationships, and thus distinguish relationships from each other.

  23. Key type in Database System Candidate Key : In each entity set, there can be more than one attribute whose value can distinguish one entity from all other entities in the same entity sets. These attributes are known as candidate key. Primary Key : PK denotes a candidate key that is chosen. Foreign Key : FK is an attribute which is used to reference from one relation to another relation. Primary key Candidate key Prov_ID and ProvName are candidate keys because they can uniquely identify a specific entity. ProvName Region Prov_ID Area A candidate key, which is chosen, is primary key. Therefore, Prov_ID is primary key. Province

  24. Conversion of ER Diagram to Relational Model One-to-one mapping LastName FirstName Proj_name Proj_No Budget GPA Std_ID Year 1 1 Twork Project Student

  25. Conversion of ER Diagram to Relational Model One-to-many mapping Case 1: No descriptive attributes associated with relationship set. FirstName LastName LastName FirstName Salary Std_ID GPA L_ID Dept M 1 Tadvise Student Lecturer Lecturer Foreign key Student Primary key Primary key

  26. Conversion of ER Diagram to Relational Model One-to-many mapping Case 2: Descriptive attributes associated with relationship. genre lastName firstName media title rating addr accountID videoID length M 1 Trents Videotape Customer dateRented cost dateDue

  27. Schema I Customer Videotape

  28. Case 2: Descriptive attributes associated with relationship. Change relationship to Weak Entity Set Weak entity class Participation contraint LastName title videoID media firstName addr genre Rental 1 1 M 1 has has Customer Videotape length dateRented cost accountID rating dateDue Identifying relationship type

  29. Schema II Customer Videotape Rental

  30. Conversion of ER Diagram to Relational Model Many-to-many mapping Sub_name LastName FirstName Credit Sub_code Dept L_ID TTeach Subject Lecturer M M sec Primary key FK FK

  31. Weak and Strong Entity Sets An entity set may not have sufficient attributes to form a primary key. Such an entity set is termed a weak entity set. An entity set that has a primary key is termed a strong entity set. For a weak entity set to be meaningful, it must be associated with another entity set, called the identifying or owner entity set. Every weak entity must be associated with and identifying entity; that is, the weak entity set is said to be existence dependent on the identifying entity set. The identifying entity set is said to own the weak entity set that it identifies. The relationship associating the weak entity set with the identifying entity set is called the identifying relationship. The identifying relationship is many to one from the weak entity set to the identifying entity set, and the participation of the weak entity set in the relationship is total. Weak entity class Participation contraint 1 Rental 1 1 M has has Customer Videotape dateRented cost Identifying relationship type dateDue

  32. Weak entity class Participation constraint 1 Rental 1 1 M has has Customer Videotape dateRented cost Identifying relationship type dateDue A customer may have many rentals. A rental must have one customer. A videotape may have one rental. A rental must have one videotape. A Rental object cannot exist without being related to a Customer and a Videotape, and no combination of attributes of class Rental is unique. Rental must be an example of a weak entity class. A rental is identified by its relationship to a videotape. The related videotape is considered the owner of the rental. Without that relationship, the rental cannot exist. The key for a Rental entity is the VideotapeID attribute, which is the key of the owner videotape.

  33. Problems with ER Models • The problems normally occur due to a misinterpretation of the meaning of certain relationships. These problems are referred to as Connection traps which are divided into 2 subtypes : • Fan traps • Chasm traps To identify connection traps, we must ensure that the meaning of a relationship is fully understood and clearly defined. If we do not understand the relationships we may create a model that is not a true representation on the real world.

  34. Fan Traps: Where a model represents a relationship between entity types, but the pathway between certain entity occurrences is ambiguous. 1 1 operates IsAllocated Division M M Branch Staff Branch entities Operates relationship Staff entities IsAllocated relationship Division entities B3 B7 B5 Customer SG37 SA9 SL21 r1 r2 r3 D1 D2 r4 r5 r6

  35. How to correct Fan Traps problem: M IsAllocated 1 Operates Branch M 1 Staff Division IsAllocated relationship Staff entities Division entities Operates relationship Branch entities SG37 SA9 SL21 Customer D1 D2 B3 B7 B5 r1 r2 r3 r4 r5 r6

  36. Chasm Traps : Where a model suggests the existence of a relationship between entity types, but the pathway does not exist between certain entity occurrences M Oversees 1 IsAllocated Staff M 1 Property_for_rent Branch Branch entities IsAllocated relationship Staff entities Oversees relationship Property_for_rent entities B3 B7 B5 SG37 SA9 SL21 Customer PG36 PG14 PL94 r1 r2 r3 r4 r5

  37. How to solve the Chasm Traps problem: M Oversees 1 IsAllocated Staff M 1 M 1 Property_for_rent Has Branch Branch entities IsAllocated relationship Staff entities Oversees relationship Property_for_rent entities B3 B7 B5 R1 R2 r3 SG37 SA9 SL21 r4 r5 PG36 PG14 PL94 r6 r7 r8 Has relationship

  38. The Enhanced Entity-Relationship Model The basic concepts of ER modeling are not sufficient to represent the requirements of the newer, more complex applications. The stimulated the need to develop additional semantic model concepts. The ER model supported with additional semantic concepts is called the Enhanced Entity-Relationship (EER) model. The EER model includes all the concepts of the original ER model together with the additional concepts of specialization/generalization.

  39. The concepts of specialization/generalization Specialization : (Top-Down Approach) The process of finding and specifying differences among objects of a single class. A single class is divided into one or more specialized subclasses. The set of subclasses is defined on the basis of some distinguishing characteristics of the entities in the superclass. When we identify a set of subclasses, we then associated attributes specific to each subclass. Generalization : (Bottom-up Approach) The process of minimizing differences between entities by identifying their common features. The process of generalization can be viewed as the reverse of the specialization process.

  40. Account-number balance Account ISA Overdraft-amount Interest_rate Saving_account Checking_ccount ISA Senior Standard Gold Date-of-birth Min_balance Interest_payment Num_checks

  41. The concepts of specialization/generalization The concepts of ER specialization/generalization are associated with the related concepts of entity sets described as superclass and subclass and the process of attribute inheritance. Superclass : An entity set that included distinct subclasses that require to be represented in a data model. Subclass : A subclass is also an entity set that has a distinct role and is also a member of a superclass.

  42. name ID address Person ISA Salary Credit_rating Employee Customer ISA Secretary Officer Teller Wage Hours_worked Station_number Office_number

  43. Attribute Inheritance A crucial property of the higher and lower level entities created by specialization and generalization is attribute inheritance. The attributes of the higher-level entity sets are said to be inherited by the lower-level entity sets. • Whether a given portion of an E-R model was arrived by specialization or generalization, the outcome is basically the same : • A high-level entity set with attributes and relationships that apply • to all of its lower-level entity sets. • Lower-level entity sets with distinctive features that apply only • within a particular lower-level entity set.

  44. There are 2 important reasons for introducing the concepts of Superclasses and Subclasses into an ER Model • To avoid describing different types of entities with possibly different • attributes within a single entity set. 2. To avoid describing similar concepts more than once, thereby saving time for designer and making the ER diagram more readable. 3. To add more semantic information to design in a form that is familiar to many people.

  45. Constraints on Specialization and Generalization A second type of constraint relates to whether or not entities may belong to more than one lower-level entity set within a single generalization. The lower-level entity sets may be one of the following: • Disjoint : A disjointness constraint requires that an entity belong to no more than one lower-level entity set. e readable. • Overlapping : The same entity may belong to more than one lower-level entity set within a single generalization.

  46. ID name address Person SiteA employee Customer O Officer Teller Bonus Account commision-_rate Daily_sale

  47. A final constraint, the completeness constraint on a generalization or specialization, specifies whether or not an entity in the higher-level entity set must belong to at least one of the lower-level entity sets within the generalization/specialization. This constraint may be one the following • Total generalization or specialization : Each higher-level entity must • belong to a lower-level entity set. • Partial generalization or specialization : Some higher-level entities may • not belong to any lower-level entity set.

More Related