ER Modeling Flashcards
Miniworld
part of the real world that we are interested in modeling
What is the importance of conceptual design?
- Fundamental
- Involves users
- Provides good documentation for the Db
- Independent of the DBMS
What is the Entity-Relationship (ER) model?
Notion that a database can be modelled as a collection of entity classes and relationships among them
What is the goal of the ER model?
Develop an ER schema or ER diagram
Entity
Data object that exists and is distinguishable from other objects
What can be considered entities?
An object that physically exists, abstract or organizational entity, or an event
Entity type
description of the attributes that a set of entities has in common
Entity set
A collection of of entities of the same type
Entity class
combination of entity set + entity type
Attribute
Property of an entity
All members of an entity class will have the same ______
Attributes
But they may have different attribute values
(e.g. all customers will have a name but the name will likely be different)
How is an entity class represented?
A rectangle
How is an attribute represented?
labelled oval connected to a entity class
How are entities distinguished from one another?
Each entity will have a unique combination of attribute values
Candidate Identifier
a set of one or more attributes whose values uniquely determine each entity.
Can you have more than one candidate identifier?
Yes there can be several
Ex: Name, DOB of customers, customerid of customers
Can you have more than one primary identifier?
No. One candidate identifier is selected to be the primary identifier
What conditions must be met for a primary identifier?
- Unique
- Mandatory
- Time invariant
How are primary identifier represented?
Labelled oval with an underline
How are null values represented? When are they used?
@ symbol
For unknown or inapplicable values
How are multi-valued attributes represented? What is an example?
Double oval
Customer could have more than 1 phone number
What is a composite attribute? What is the opposite of a composite attribute?
A composite attribute is comprised of multiple values to describe it (e.g. address has street, city, zip etc.)
The opposite of a composite is a simple
What is a derived attribute? How is it represented? What is the opposite of a derived attribute?
The opposite of derived is stored. It is an attribute that is calculated from another attribute. It’s represented with a dotted oval. An example being age calculated from date of birth attribute
Where are characteristics captured that are not typically represented in the ER diagram? What are some examples?
Data dictionary
- Attribute cardinality
- Entity class cardinality
- Attributes that are optional or mandatory, attribute uniqueness
- Domain constraints (e.g SSN must be 9 exactly)
Cardinality
Number of elements in that set
Cardinality constraint
bounds the upper and/or lower limits of the cardinality
What does a cardinality constraint do for an attribute?
Restricts the number of values an attribute can take on
Interaction relationship
association between or among entities
Relationship type
is a list (ordered) of participating entity classes
Relationship set
specific set of associated entity instances based on relationship type
Relationship Class
relationship type + relationship set
How is a relationship class represented?
Diamond
Can a relationship class have attributes?
No
How is data that exists due to the relationship classified as?
Weak entity
The data has no meaningful existence without the relationship that defines it (e.g order details)
What is the purpose of a weak entity?
Provide more data about the interaction between the 2 entities
Degree of an interaction relationship
Number of entity classes involved in a relationship
What are examples of interaction relationships?
unary
binary
ternary
Business Rule
business policy that leads to a rule/statement constraining some aspect of the business
Where are Business rules and other or behavior-oriented/process oriented rules located?
Annotations on the ER or data dictionary
Weak entity class
inherits identifying attributes from another entity class to uniquely identify its own entities
How is a weak entity class represented?
Double lined box and an arrow pointing to the identifying relationship
What is the defining characteristic of a weak entity?
If an entity class depends on another class for all or part of its identifier, it’s weak
When does a weak entity have a partial identifier?
If the max cardinality > 1 in its identifying relationship
How is a partial identifier represented?
Dotted underline
What is the circle relationship symbol?
Indicates a superclass and associated subclass
Designation
Classifying attribute for the superclass/subclass relationship
What are the 4 types of superclass/subclass relationships
Disjoint, 0:1
Partition, 1:1
Overlap, 0:M
Cover, 1: M
Disjoint superclass/subclass relationship
0:1 relationship
Example: an employee may not be an associate or a manager (just in the general employee pool)
Partition
1:1
Example: Employee must either be a manager or an associate.
No employee can be both
Overlap
0:M
An employee can be both a manager and an engineer (or could be neither)
Cover
1:M
Employee has to be either a manager or engineer or could be both (can’t be neither)
When to create subclasses
- Attributes relative only to a subclass
- Relationship classes relevant only to a subclass
- Cardinality of relationships associated with subclasses are different
How do subclasses gain attributes and relationships?
They are inherited from the superclass (including the identifier)
Specific attributes
attributes to the subclass only
Specific relationships
entities in a subclass participating in direct relationships
