The basis of an entity
relationship Model (ERM) which depicts the:
·
Conceptual database as viewed by end user
·
Database’s main components
- Entities
- Attributes - Relationships
- Entities
- Attributes - Relationships
·
Chen (of Chen diagrams) described how to generally map between English
sentence structures and ERD’s in this table from his ER
Diagram paper:
He also did the same analysis for Chinese characters.
Attributes
·
Identifiers or keys: One or more attributes that uniquely
identify each entity instance
·
Simple attribute: Attribute that cannot be subdivided
o
Most attributes are this way
·
Single-valued attribute: Attribute that has only a single value at a
time
o
Most attributes are this way
·
Multivalued attributes: Attributes that have many values
o
Generally, to be avoided unless necessary for performance or to match
common app accesses.
o
Slides 2 and 3 A Multivalued Attribute in an
Entity (CAR_COLOR)
o
These require creating:
§ Several new
attributes, one for each
§ component of the
original multivalued attribute
·
Derived attribute: Attribute whose value is calculated from other
attributes
o
Derived using a formula or an algorithm
o
Slide 4 - Depiction of a Derived
Attribute
o
Slide 5 - Advantages and Disadvantages
of Storing Derived Attributes
Entities: Strong vs. Weak
Entities can either exist on their own or they can only exist when
associated with some other entity type.
·
Strong entities - A strong entity can be uniquely identified by its own
attributes. - Therefore, the entity’s existence does not depend on any other entity
- e.g., a Dormitory can be uniquely identified by its name and location.
- e.g., a US Bank is uniquely identified by its bank number.
- e.g., a US Bank is uniquely identified by its bank number.
·
Weak entities
o
Thus, you must add attributes to the weak entity to uniquely identify
it.
§ This means you must
extend the weak entity’s primary key to include one or more attributes from the
parent entity as a foreign key.
§ e.g., a Room in a
Dormitory needs the Dormitory information as part of its identity.
§ e.g., an Account
may be identified by an Account Number, but it is meaningless without being
associated with a Bank.
o
Alternatively, you could add a surrogate key to the weak
entity.
§ These keys are not
related to the entity’s real attributes, such as an AUTO INCREMENT or other
generated value. Adding a surrogate key would turn it into a strong entity
Relationships
An association between entities, typically meaningful in both directions
·
Participants: Entities that participate in a relationship
·
Connectivity: Describes the relationship classification
·
Cardinality: (optional) Expresses the minimum and maximum number of
entity occurrences associated with one occurrence of a related entity
o
Slide 6 - Connectivity and Cardinality
in an ERD
·
Existence Dependence/Independence
o
Some Entities exists in the database only when associated with another
entity occurrence
o
Others exist on their own without dependence on other entities existence
·
Relationship strength
o
A weak or non-identifying relationship exists between
two entities when the primary key of one of the related entities does
not contain a primary key component of the other related entities.
o
Slide 7 - A Weak (Non-Identifying)
Relationship between COURSE and CLASS
o
A strong or identifying relationship is when the
primary key of the related entity contains the primary key of the “parent”.
§ Slide 8 - A Strong
(Identifying) Relationship between COURSE and CLASS
·
Entity strength
o
Weak Entity
1. The entity is
existence-dependent on another entity.
2. The entity gets
at least part of its primary key from that other entity.
Database designers
determine whether an entity is weak based on business rules
§ Slide 9 - A Weak
Entity in an ERD
§ Slide 10 - A Weak
Entity in a Strong Relationship
§ Dependent wouldn’t
exist without an Employee
§ the primary key
of Dependent is (EMP_NUM, DEP_NUM) which includes the
primary key of Employee.
o Strong entity
§ An entity that is
existence-independent.
·
Review the Crow’s Foot symbols
o
Slide 11
o
Slides 12 & 13
·
Relationship Degree
o
Binary relationship: Two entities are associated
o
Ternary relationship: Three entities are associated
o
Slide 14 - Three Types of Relationship
Degree
o
Slide 15 - An ER Representation of
Recursive Relationships
·
Associative Entities
o
Also known as composite or bridge entities
o
Used ONLY in the ERD, they are NOT typically
specified in the business rules.
o
May also contain additional attributes that play no role in connective
process
o
Slide 16 - Converting the M: N
Relationship into Two 1:M Relationships
o
Slide 17 - A Composite Entity in an ERD
Developing an ER Diagram
1.
Identify business rules based on the descriptions, ER Diagram.
2.
Identify main entities and relationships from the business rules *
Develop the initial ERD
3.
Revise and review ERD
·
In class example in Slides 18-26
·
Summary of example in Slide 27
Conflicting Goals
Only the simplest databases can achieve all of these guidelines and
goals. You will often be faced with conflicting goals. Slide 28
·
High update occurrence requirements can conflict with designs including
many related entities
·
common access patterns may drive changes to the design
Slide 28 The special case of the 1:1 recursive relationship
This kind of relationship occurs often and the first time you see it,
it’s often puzzling as to how to design and implement it.
Discuss these sample implementations
Example (time permitting): Library book catalog
· Patron can checkout 1+ or 0+ books?
·
Library has 1+ books?
·
A book may have been checked out by many patrons
o
(thus, a many to many)
o
fix with a helper “Checkout Order”
If you are interested to learn Digital marketing so kindly go to the World Stream SEO official website.
Use My Notes is an Educational Website for providing all Technical course material.
Post a Comment