the entity relationship model n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
The Entity-Relationship Model PowerPoint Presentation
Download Presentation
The Entity-Relationship Model

Loading in 2 Seconds...

play fullscreen
1 / 50

The Entity-Relationship Model - PowerPoint PPT Presentation


  • 132 Views
  • Uploaded on

The Entity-Relationship Model. Instructor: Mohamed Eltabakh meltabakh@cs.wpi.edu. Part II. Entities with Different Attribute Types (Recap). Multivalued Attribute: major. Primitive Attribute: sNumber. Composite Attribute: address. Student entity type with all its attributes. DoB. Age.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'The Entity-Relationship Model' - dyami


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
the entity relationship model

The Entity-Relationship Model

Instructor: Mohamed Eltabakh

meltabakh@cs.wpi.edu

Part II.

CS3431-B11

entities with different attribute types recap
Entities with Different Attribute Types (Recap)

Multivalued Attribute: major

Primitive Attribute: sNumber

Composite Attribute: address

Student entity type

with all its attributes

DoB

Age

sNumber

Derived Attribute: Age

Age

binary relationships recap
Binary Relationships (Recap)

pNumber

sName

sPrice

product

date

quantity

supplies

buys

sName

cName

supplier

consumer

sLoc

cLoc

Attributes can be attached to Entity Sets or Relationships

multi way relationships recap
Multi-Way Relationships (Recap)

Model the relationship Supplier supplies Products to Consumers

Ternary relationship

(three-way)

recursive relationship types and roles recap
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 superPart

There are Parts that play the role of subPart

more elements in er model
More Elements in ER Model
  • Key Constraints
  • Cardinality Constraints
  • Weak Entities
  • Subclass Entities (ISA Relationships)
  • Principles for Good Design
keys of entity sets
Keys of Entity Sets
  • Remember entity set is a group of entitieswith the same type
  • Key of Entity Set
    • Set of attributes that uniquely identify each entity
  • Examples:
    • “Car” VIN
    • “Person” SSN
    • “WPI Student” WPI ID
  • A key has to be unique within the scope of your application
    • Does not have to be globally unique
  • Example:
    • “US Student” SSN, UniversityName + UnivesityID
types of keys
Types of Keys
  • A super key of an entity set is a set of one or more attributes whose values uniquely determine each entity
    • “Person” SSN, SSN + FirstName
    • “Account”  AccountNumber + AccountType
  • A candidate key of an entity set is a minimal super key
    • “Person” SSN
    • “Account”  AccountNumber
    • “US Student” SSN, UniversityName + UnivesityID
  • Each candidate key is a super key but not vice versa
  • A primary key is one from, possibly several, candidate keys  Pick one and declare it as “primary key”
    • “Student”  SSN, StudentID, FirstName + MiddleName + LastName
primary keys in erd
Primary Keys in ERD
  • Select only one key to be the primary key
  • Primary key is modeled by “underline” under its set of attributes
  • Good Practice:
    • Select singleton and number fields whenever possible
multi attributes primary key
Multi-Attributes Primary Key

Key for Movie is <title, year>

Key for Student is sNumber

We can represent key for entity type consisting of more than one attribute (e.g.: Movie)

keys of relationships
Keys of Relationships
  • Relationship without attributes
    • 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
keys of relationships cont d
Keys of Relationships (Cont’d)
  • Relationship with attributes
    • Attributes of the relationship may (not always) participate inside the key + the external keys
  • (sNumber, cNumber, Date) is the key of Taken

Grade

Date

project

What if we do not underline “Date” attribute ??

more elements in er model1
More Elements in ER Model
  • Key Constraints
  • Cardinality Constraints
  • Weak Entities
  • Subclass Entities (ISA Relationships)
  • Principles for Good Design
cardinality constraints
Cardinality Constraints
  • Express the number of entities to which another entity can be associated via a relationship set
  • Most useful in describing binary relationship sets
  • For a binary relationship set the mapping cardinality must be one of the following types:
    • One to one
    • One to many
    • Many to one
    • Many to many
representing cardinalities in erd
Representing Cardinalities in ERD
  • In a relationship:
    • “” : 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.

one to many relationship
One-To-Many Relationship
  • In the one-to-many relationship a loan is associated with at most onecustomer via borrower, a customer is associated with many(including 0) loans via borrower

One-to-Many from Customer to Loan

many to one relationship
Many-To-One Relationship
  • In a many-to-one relationship a loan is associated with many(including 0) customers via borrower, a customer is associated with at most oneloan via borrower
many to many relationship
Many-To-Many Relationship
  • 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
degree of cardinalities
Degree of Cardinalities

How : Expressed using (min, max)

(0, 5)

(3, 60)

  • Student can take manycourses, and a course can be taken by manystudents
  • Student can take 0 to 5 courses, and a course can be taken by 3 to 60 students
cardinality constraints for recursive relationships

pNumber

pName

Part

superPart

subPart

Contains

quantity

Cardinality Constraints for Recursive Relationships

A Part may contain manysubparts

A Part can be subpart in manysuperParts

cardinality constraints for recursive relationships1
Cardinality Constraints for Recursive Relationships

A Part can have manysubParts

A Part can be subpart for at most onesuperPart

cardinality constraints for multi way relationships
Cardinality Constraints for Multi-way Relationships

pNumber

pName

Product

sName

cName

Supply

Supplier

Consumer

sLoc

cLoc

price

qty

Every Supplier supplies some Product to some Consumer

To add degree constraints, introduce a new entity set and create multiple binary relationships !!!

adding cardinality constraints to multi way relationships
Adding Cardinality Constraints to Multi-way Relationships

pNumber

pName

Product

cName

sName

in

Consumer

Supplier

cLoc

sLoc

consumes

supplies

Supp_Cons_

Prod

What is the key of this entity ???

(Weak Entity)

price

qty

more elements in er model2
More Elements in ER Model
  • Key Constraints
  • Cardinality Constraints
  • Weak Entities
  • Subclass Entities (ISA Relationships)
  • Principles for Good Design
weak entity sets
Weak Entity Sets
  • An entity set that does not have a primary key is referred to as a weak entity set
    • 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
    • It must relate to the identifying entity set via a total, one-to-many relationship set from the identifying to the weak entity set

Course number is unique only within the department

Weak entity set

Identifying entity set

weak entity sets1
Weak Entity Sets
  • Discriminator(or partial key) of a weak entity set
    • The set of attributes that uniquely identify a weak entity given its identifying entity
  • Primary key of a weak entity set
    • 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
    • Cannot have a course without a corresponding department
  • (dNumber, cNumber) is the primary key for Course

discriminator

representing a weak entity set
Representing a Weak Entity Set
  • Weak entity set is represented by double rectangles
  • Weak relationship (supporting relationship) is represented by double diamonds
  • Weak relationship is one-many from the weak entity to the identifying entity
again it depends on your application assumptions
Again: It Depends on Your Application/Assumptions
  • If you assume the course number is unique within a department
    • “Course” is a weak entity set
  • If you assume the course number is unique across all departments
    • “Course”  is a strong entity set

offers

Course

Stating your assumptions in text is very important !!!

more elements in er model3
More Elements in ER Model
  • Key Constraints
  • Cardinality Constraints
  • Weak Entities
  • Subclass Entities (ISA Relationships)
  • Principles for Good Design
what about an exercise
What about an Exercise !!!

Lets interactively design a database for a hospital

isa relationship types
ISA Relationship Types
  • Similar to “subclass” concept in Object-Oriented languages
  • Entity sets share some common attributes but differ in others
  • Sometimes called “Specialization/Generalization”
  • Example
    • Students can be UGStudents or GradStudents
      • UGStudents take undergrad Classes
      • GradStudents can be TAs or RAs
      • GradStudents are advised by Professors
isa relationship types cont d
ISA Relationship Types (Cont’d)
  • Top-down design process
    • Build entities with the common attributes, then build sub-entities with distinctive attributes from other entities in the set
  • 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
  • In ERD, represented by a trianglecomponent labeled ISA (E.g. customer “is a”person)
  • Attribute inheritance
    • Lower-level entity set inherits all the attributes and relationship participation of the higher-level entity set to which it is linked
isa relationship generalization
ISA Relationship: Generalization
  • Bottom-up design process
    • Combine a number of entity sets that share the same features into a higher-level entity set
  • Specialization and generalization are simple inversions of each other
    • They are represented in an E-R diagram in the same way
  • The terms specialization and generalization are used interchangeably.
multiple isa relationships
Multiple ISA Relationships
  • Can have multiple specializations of an entity set based on different features
  • E.g. permanent_employeevs. temporary_employee, in addition to officer vs. secretary vs. teller
  • Each particular employee would be
    • A member of one of permanent_employeeor temporary_employee,
    • And also a member of one of officer, secretary, or teller
isa relationship constraints
ISA Relationship: Constraints
  • Constraint on which entities can be members of a given lower-level entity set
      • Example: all customers over 65 years are members of senior-citizen entity set; senior-citizen 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.
    • Disjoint
      • An entity can belong to only one lower-level entity set
    • Overlapping
      • 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”
isa relationship constraints cont d
ISA Relationship: Constraints (Cont’d)
  • 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
    • Total: An entity must belong to one of the lower-level entity sets
    • Partial: An entity need not belong to one of the lower-level entity sets
example
Example

Overlapping & Partial

isa relationship keys multiplicity
ISA Relationship: Keys & Multiplicity
  • Key of sub-entities is inherited from the super-entities
  • Multiplicity is 1:1

person_id is the primary key

more elements in er model4
More Elements in ER Model
  • Key Constraints
  • Cardinality Constraints
  • Weak Entities
  • Subclass Entities (ISA Relationships)
  • Principles for Good Design
coming up with a good design for your application
Coming up with a good design for your application
  • No single right design, there can be many…
  • Put clear, reasonable assumptions and make a design that captures the assumptions
    • Without stating the assumptions, others can claim your design is wrong !!!
  • It is like art, common sense and experience make a difference
guidelines toward a good design i
Guidelines Toward a Good Design (I)
  • Convey “real” application requirements
  • Utilize meaningful names
  • Try simpler construct first
  • Avoid redundancy, do not store the same data in multiple places
  • Be as precise as possible (E.g., cardinality constraints)
  • Don’t over specify (limits input)
guidelines toward a good design ii
Guidelines Toward a Good Design (II)
  • Do not overuse non-binary relationships
    • They are harder to understand and interpret
  • Do not create entity sets with single attributes
    • They may be better as attributes of other entity sets
  • Do not overuse ISA relationships
    • There are always some commonalities between things  this does not mean they should inherit from common ancestor
guidelines toward a good design iii
Guidelines Toward a Good Design (III)
  • Choose meaningful relationships
  • Know when to add attributes to entity sets vs. relationships
  • Some business constraints will not be captured in the design
    • E.g., For a customer to get a load, the sum of the previous loans to him/her must be < MaxLoan
summary of entity relationship model
Summary of Entity-Relationship Model
  • Concepts
    • Entity, Entity Sets, Weak Entity Sets
    • Relationships Types
      • binary, ternary, multi-way, recursive, weak, ISA
    • Attributes
      • For entity sets or relationship types
      • Simple, composite, derived, multi-valued
  • Constraints – key, cardinality
  • Guidelines for Good Design