The Entity Relationship Model Part II Instructor Mohamed
- Slides: 50
The Entity. Relationship Model Part II. Instructor: Mohamed Eltabakh meltabakh@cs. wpi. edu CS 3431 -B 11 1
Entities with Different Attribute Types (Recap) Multivalued Attribute: major Composite Attribute: address Derived Attribute: Age 2
Binary Relationships (Recap) 3
Multi-Way Relationships (Recap) Model the relationship Supplier supplies Products to Consumers Ternary relationship (three-way) 4
Recursive Relationship Types and Roles (Recap) Refer to the same entity in the relationship Recursive relationship type : Part-Subpart Roles: There are Parts that play the role of super. Part There are Parts that play the role of sub. Part 5
More Elements in ER Model l Key Constraints l Cardinality Constraints l Weak Entities l Subclass Entities (ISA Relationships) l Principles for Good Design 6
Keys of Entity Sets l Remember entity set is a group of entities with the same type l Key of Entity Set l l Examples: l l “Car” VIN “Person” SSN “WPI Student” WPI ID A key has to be unique within the scope of your application l l Set of attributes that uniquely identify each entity Does not have to be globally unique Example: l “US Student” SSN, University. Name + Univesity. ID 7
Types of Keys l A super key of an entity set is a set of one or more attributes whose values uniquely determine each entity l l l “Person” SSN, SSN + First. Name “Account” Account. Number + Account. Type A candidate key of an entity set is a minimal super key l l l “Person” SSN “Account” Account. Number “US Student” SSN, University. Name + Univesity. ID l Each candidate key is a super key but not vice versa l A primary key is one from, possibly several, candidate keys Pick one and declare it as “primary key” l “Student” SSN, Student. ID, First. Name + Middle. Name + Last. Name 8
Primary Keys in ERD l Select only one key to be the primary key l Primary key is modeled by “underline” under its set of attributes l Good Practice: l Select singleton and number fields whenever possible 9
Multi-Attributes Primary Key for Movie is <title, year> Key for Student is s. Number We can represent key for entity type consisting of more than one attribute (e. g. : Movie) 10
Keys of Relationships l Relationship without attributes l l The combination of primary keys of the participating entity sets forms a key of a relationship set (customer_id, load_number) is the key of borrower 11
Keys of Relationships (Cont’d) l Relationship with attributes l l Attributes of the relationship may (not always) participate inside the key + the external keys (s. Number, c. Number, Date) is the key of Taken 12
More Elements in ER Model l Key Constraints l Cardinality Constraints l Weak Entities l Subclass Entities (ISA Relationships) l Principles for Good Design 13
Cardinality Constraints l Express the number of entities to which another entity can be associated via a relationship set l Most useful in describing binary relationship sets l For a binary relationship set the mapping cardinality must be one of the following types: l l One to one One to many Many to one Many to many 14
Mapping Cardinalities 15
Mapping Cardinalities (Cont’d) 16
Representing Cardinalities in ERD l In a relationship: l l l “ “ “ ” : Represent “many” (including 0) ” : Represent “one” (including 0) ”: Represent “one” (must be one) A student is taking “many” courses. A course can be taken by “many” students. 17
One-To-Many Relationship l In the one-to-many relationship a loan is associated with at most one customer via borrower, a customer is associated with many (including 0) loans via borrower One-to-Many from Customer to Loan 18
Many-To-One Relationship l In a many-to-one relationship a loan is associated with many (including 0) customers via borrower, a customer is associated with at most one loan via borrower 19
Many-To-Many Relationship l In a many-to-many relationship a loan is associated with many (including 0) customers via borrower, a customer is associated with many loan via borrower 20
Degree of Cardinalities How : Expressed using (min, max) l Student can take many courses, and a course can be taken by many students l Student can take 0 to 5 courses, and a course can be taken by 3 to 60 students 21
Cardinality Constraints for Recursive Relationships p. Number p. Name Part super. Part sub. Part Contains quantity A Part may contain many subparts A Part can be subpart in many super. Parts 22
Cardinality Constraints for Recursive Relationships A Part can have many sub. Parts A Part can be subpart for at most one super. Part 23
Cardinality Constraints for Multi-way Relationships p. Number p. Name Product s. Name c. Name Supply Supplier Consumer s. Loc c. Loc price qty Every Supplier supplies some Product to some Consumer To add degree constraints, introduce a new entity set and create multiple binary relationships !!! 24
Adding Cardinality Constraints to Multi-way Relationships p. Number p. Name Product c. Name s. Name in Supplier Consumer s. Loc consumes supplies c. Loc Supp_Cons_ Prod price qty What is the key of this entity ? ? ? (Weak Entity) 25
More Elements in ER Model l Key Constraints l Cardinality Constraints l Weak Entities l Subclass Entities (ISA Relationships) l Principles for Good Design 26
Weak Entity Sets l An entity set that does not have a primary key is referred to as a weak entity set l l Its attributes are not enough to form a key The existence of a weak entity set depends on the existence of an identifying entity set l It must relate to the identifying entity set via a total, one-to-many relationship set from the identifying to the weak entity set Identifying entity set Weak entity set Course number is unique only within the department 27
Weak Entity Sets l Discriminator (or partial key) of a weak entity set l l Primary key of a weak entity set l l The composition of the primary key of the identifying entity set + the weak entity set’s discriminator Identifying entity has to exist for each weak entity l l The set of attributes that uniquely identify a weak entity given its identifying entity Cannot have a course without a corresponding department (d. Number, c. Number) is the primary key for Course discriminator 28
Representing a Weak Entity Set l Weak entity set is represented by double rectangles l Weak relationship (supporting relationship) is represented by double diamonds l Weak relationship is one-many from the weak entity to the identifying entity 29
Again: It Depends on Your Application/Assumptions l If you assume the course number is unique within a department l l “Course” is a weak entity set If you assume the course number is unique across all departments l “Course” is a strong entity set 30
More Elements in ER Model l Key Constraints l Cardinality Constraints l Weak Entities l Subclass Entities (ISA Relationships) l Principles for Good Design 31
What about an Exercise !!! Lets interactively design a database for a hospital 32
ISA Relationship Types l Similar to “subclass” concept in Object-Oriented languages l Entity sets share some common attributes but differ in others l Sometimes called “Specialization/Generalization” l Example l Students can be UGStudents or Grad. Students l l l UGStudents take undergrad Classes Grad. Students can be TAs or RAs Grad. Students are advised by Professors 33
ISA Relationship Types (Cont’d) l Top-down design process l Build entities with the common attributes, then build sub-entities with distinctive attributes from other entities in the set l These sub-entities become lower-level entity sets that have attributes or participate in relationships that do not apply to the general higher-level entity set l In ERD, represented by a triangle component labeled ISA (E. g. customer “is a” person) l Attribute inheritance l Lower-level entity set inherits all the attributes and relationship participation of the higher-level entity set to which it is linked 34
ISA Relationship: Example I 35
ISA Relationship: Example II 36
ISA Relationship: Generalization l Bottom-up design process l l Specialization and generalization are simple inversions of each other l l Combine a number of entity sets that share the same features into a higher-level entity set They are represented in an E-R diagram in the same way The terms specialization and generalization are used interchangeably. 37
Multiple ISA Relationships l Can have multiple specializations of an entity set based on different features l E. g. permanent_employee vs. temporary_employee, in addition to officer vs. secretary vs. teller l Each particular employee would be l l A member of one of permanent_employee or temporary_employee, And also a member of one of officer, secretary, or teller 38
Multiple ISA Relationships: Example 39
ISA Relationship: Constraints l Constraint on which entities can be members of a given lower-level entity set l l l Example: all customers over 65 years are members of senior-citizen entity set; seniorcitizen ISA person Denoted in ERD on the ISA edge Constraint on whether or not entities may belong to more than one lower-level entity set within a single generalization. l Disjoint l l Overlapping l l An entity can belong to only one lower-level entity set An entity can belong to more than one lower-level entity set Denoted in ERD by writing “disjoint” or “overlapping” next to ISA triangle, by default “disjoint” 40
ISA Relationship: Constraints (Cont’d) l Completeness constraint -- 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 a generalization l Total : An entity must belong to one of the lower-level entity sets l Partial: An entity need not belong to one of the lower-level entity sets 41
Example Overlapping & Partial 42
ISA Relationship: Keys & Multiplicity l l Key of sub-entities is inherited from the super-entities Multiplicity is 1: 1 person_id is the primary key 43
More Elements in ER Model l Key Constraints l Cardinality Constraints l Weak Entities l Subclass Entities (ISA Relationships) l Principles for Good Design 44
Summary of Symbols used in ERD 45
Coming up with a good design for your application l No single right design, there can be many… l Put clear, reasonable assumptions and make a design that captures the assumptions l l Without stating the assumptions, others can claim your design is wrong !!! It is like art, common sense and experience make a difference 46
Guidelines Toward a Good Design (I) l Convey “real” application requirements l Utilize meaningful names l Try simpler construct first l Avoid redundancy, do not store the same data in multiple places l Be as precise as possible (E. g. , cardinality constraints) l Don’t over specify (limits input) 47
Guidelines Toward a Good Design (II) l Do not overuse non-binary relationships l l Do not create entity sets with single attributes l l They are harder to understand interpret They may be better as attributes of other entity sets Do not overuse ISA relationships l There always some commonalities between things this does not mean they should inherit from common ancestor 48
Guidelines Toward a Good Design (III) l Choose meaningful relationships l Know when to add attributes to entity sets vs. relationships l Some business constraints will not be captured in the design l E. g. , For a customer to get a load, the sum of the previous loans to him/her must be < Max. Loan 49
Summary of Entity-Relationship Model l Concepts l l Entity, Entity Sets, Weak Entity Sets Relationships Types l l binary, ternary, multi-way, recursive, weak, ISA Attributes l For entity sets or relationship types l Simple, composite, derived, multi-valued l Constraints – key, cardinality l Guidelines for Good Design 50
- Ideally an entity identifier is composed of
- Data modeling using entity relationship model
- Peter chen er diagram
- Monotremes
- Extended entity relationship model
- Entity-relationship model
- Data modeling using the entity relationship model
- Ternary relationship sql
- Entity-relationship data model
- Entity relationship data model
- Enhanced entity-relationship model
- Contoh mapping cardinality
- Contoh strong entity dan weak entity
- Public interest entity vs listed entity
- Public interest entity
- Entity vs relationship
- E.e.r
- Contoh er diagram
- Tujuan erd
- Student course entity relationship diagram
- Erm diagram
- Elements of entity relationship diagram
- Simbol erd diagram
- One mandatory to many optional
- Pengertian entity relationship diagram
- Erd business
- Connectivity in erd
- Airbnb er diagram
- Airbnb class diagram
- Airbnb entity relationship diagram
- College entity relationship diagram
- Entity vs relationship
- Airbnb architecture diagram
- Partial specialization rule example
- Enhanced entity relationship adalah
- Entity relationship diagram for bakery
- Enhanced erd
- Perbedaan primary key dan foreign key
- Existence dependency occurs on
- Tiny college erd
- Gambaran hubungan antar entitas dinamakan
- Entity relationship diagram video rental store
- Associative entity relationship example
- Associative entities
- Entity relationship diagram
- Recursive relationships are expressed on the erd with:
- Enhanced entity relationship
- Riham mohamed aly
- Mohamed dahoui
- Lodacain
- Mohamed kossentini