Entity - person, place, object, event, concept (often corresponds to a row in a table)
Entity Set – collection of entities (often corresponds to a table)
Relationship instance – link between entities (corresponds to primary key-foreign key equivalencies in related tables)
Relationship set – category of relationship…link between entity types
What Should an Entity Be?
SHOULD BE:
An object that will have many instances in the database
An object that will be composed of multiple attributes
An object that we are trying to model
SHOULD NOT BE:
A user of the database system
An output of the database system (e.g. a report)
Attributes
Attribute - property or characteristic of an entity type
Classifications of attributes:
Simple versus Composite Attribute
Single-Valued versus Multivalued Attribute
Stored versus Derived Attributes
Identifier Attributes
Identifiers (Keys)
-Identifier (Key) - An attribute (or combination of attributes) that uniquely identifies individual instances of an entity type
-Simple Key versus Composite Key
-Candidate Key – an attribute that could be a key…satisfies the requirements for being a key
Characteristics of Identifiers
-Will not change in value
-Will not be null
-No intelligent identifiers (e.g. containing locations or people that might change)
-Substitute new, simple keys for long, composite keys
Degree of Relationships
-Degree of a Relationship is the number of entity types that participate in it
=Unary Relationship
=Binary Relationship
=Ternary Relationship
Cardinality of Relationships
Expresses the number of entities to which another entity can be associated via a relationship. For binary relationship sets between entity sets A and B, the mapping cardinality must be one of the following
One-to-one: An entity in A is associated with at most one entity in B, and an entity in B is associatedwith at most one entity in A.
One-to-many: An entity in A is associated with any number in B. An entity in B is associated with at most one entity in A.
Many-to-one: An entity in A is associated with at most one entity in B. An entity in B is associated with any number in A.
Many-to-many: Entities in A and B are associated with any number from each other.
The appropriate mapping cardinality for a particular relationship set depends on the real world being modeled.
Modeling Constraints
Extracting constraints is what modeling is all about. But how do
we express them?
Examples:
Keys: social security number uniquely identifies a person.
Single-value constraints: a person can have only one father.
Referential integrity constraints: if you work for a company, it
must exist in the database.
Domain constraints: peoples’ ages are between 0 and 150.
Keys
A set of attributes that uniquely identify an object or entity:
Person: social security number
name
name + address
name + address + age
Perfect keys are often hard to find, but organizations usually
invent something anyway.
An object may have multiple keys:
employee number, social-security number
Single Value Constraints
An entity (or object) may have at most one value for a given
attribute or relationship.
Person: name, social-security number
Company: stock price
How do we do this in ODL?
In E/R, every attribute has at most one value.
Arrows tell us about multiplicity of relations.
If we have a single-valued constraint, we can either:
1. Require that the value exist (see referential integrity shortly)
2. Allow null values.
Referential Integrity Constraints
A relationship has one value and the value must exist.
Example:
Product madeBy Company: company must exist.
How do we enforce referential integrity constraints? (otherwise,
we get dangling pointers)
- forbid to delete a reference object, or
- delete the objects that reference an object we’re deleting.
Cardinality Constraints
-Cardinality Constraints - the number of instances of one entity that can or must be associated with each instance of another entity.
-Minimum Cardinality
If zero, then optional
If one or more, then mandatory
-Maximum Cardinality
The maximum number
Strong vs. Weak Entities, and identifying Relationships
-Strong entities
exist independently of other types of entities
has its own unique identifier
represented with single-line rectangle
-Weak entity
dependent on a strong entity…cannot exist on its own
Does not have a unique identifier
represented with double-line rectangle
-Identifying relationship
links strong entities to weak entities
represented with double line diamond
Associative Entities
It’s an entity – it has attributes
AND it’s a relationship – it links entities together
When should a relationship with attributes instead be an associative entity?
All relationships for the associative entity should be many
The associative entity could have meaning independent of the other entities
The associative entity has a single-attribute identifier, and should also have other attributes
The associative may be participating in other relationships other than the entities of the associated relationship
Ternary relationships should be converted to associative entities (p102)
Supertypes and Subtypes
Subtype: A subgrouping of the entities in an entity type which has attributes that are distinct from those in other subgroupings
Supertype: An generic entity type that has a relationship with one or more subtypes
Inheritance:
Subtype entities inherit values of all attributes of the supertype
An instance of a subtype is also an instance of the supertype
Relationships and Subtypes
Relationships at the supertype level indicate that all subtypes will participate in the relationship
The instances of a subtype may participate in a relationship unique to that subtype. In this situation, the relationship is shown at the subtype level
Generalization and Specialization
Generalization: The process of defining a more general entity type from a set of more specialized entity types. BOTTOM-UP
Specialization: The process of defining one or more subtypes of the supertype, and forming supertype/subtype relationships. TOP-DOWN
Constraints in Supertype/ Completeness Constraint
Completeness Constraints: Whether an instance of a supertype must also be a member of at least one subtype
Total Specialization Rule: Yes (double line)
Partial Specialization Rule: No (single line)
Constraints in Supertype/ Disjointness constraint
Disjointness Constraints: Whether an instance of a supertype may simultaneously be a member of two (or more) subtypes.
Disjoint Rule: An instance of the supertype can be only ONE of the subtypes
Overlap Rule: An instance of the supertype could be more than one of the subtypes
