Final Take 2.1 Flashcards
How does quality of of an E-R diagram affect normalization process?
A data model that effectively captures the entities and the relationships between those entities will require little to no modification when transposed into relations.
If a data model is done poorly, a number of changes to the relations will be required to remove anomalies and reduce data redundancy.
Relationship Class
Denotes a “connection” between entity classes.
There can be multiple relationships between entity classes.
Relationships should be named with a “verb phrase”
One phrase – grammar modified to reflect each direction EMPLOYEE has DEPARTMENT; DEPARTMENT has EMPLOYEE
EMPLOYEE mentors another EMPLOYEE; EMPLOYEE is mentored by another EMPLOYEE
Two phrases – specific for each direction
Relationship Entity
Denotes a “connection” between entity instances.
Relationships Degree
the degree of a relationship is the number of entity classes that participate in the relationship
Recursive Relationship
A relationship among entities of the same type.
The same entity class participates more than once in different roles.
A relationship between instances of the same entity class.
A recursive relationship will always have a degree of 1.
Binary Relationship
A relationship between exactly two entities.
A binary relationship will always have a degree of 2
Maximum Cardinality
The maximum number of entity instances that can occur on one side of a relationship.
The maximum cardinalities from each side come together to form the cardinality ratio, which is also known as the relationship type.
One-to-One (1:1)
One-to-Many; Many-to-One (1:N; N:1)
Many-to-Many (M:N)
If a specific maximum is known, you can use the number.
To determine the maximum cardinalities, you must ask a question from each perspective.
§ Can “ENTITY_A” have more than one “ENTITY_B”?
§ Can “ENTITY_B” have more than one “ENTITY_A”?
If an answer is ‘NO, there is at most one’, then the maximum cardinality is “one” for that perspective: Denoted by a single line going into the entity that is restricted to a maximum of one.
If an answer is ‘YES, there can be more than one’, then the maximum cardinality is “many” for that
perspective: Denoted by a crow’s foot going into the entity that can have many.
Minimum Cardinality
The minimum number of entity instances that have to occur on one side of a relationship.
To determine the minimum cardinalities, you must ask a question from each perspective.
Does “ENTITY_A” have to have an “ENTITY_B”?
Does “ENTITY_B” have to have an “ENTITY_A”?
If an answer is ‘NO, there doesn’t have to be one’, then the minimum cardinality is zero (optional) for
that perspective: Denoted by an oval placed before the maximum cardinality for the entity that is not required to participate in the relationship.
If an answer is ‘YES, there must be one”, then the minimum cardinality is one for that perspective: Denoted by a perpendicular line to the relationship, placed before the maximum cardinality for the
entity that is required to participate in the relationship.
Business Rules
Restrictions on the model
that reflect the business
activities being modeled.
Such rules are the basis for
determining the appropriate
elements to use in the E-R
diagram.
Has-A vs IS-A
‘IS-A’ is a relationship where one object is a subtype of another. A Car is a Vehicle, a Dog is an Animal; this relationship is manifested through inheritance. The ‘HAS-A’ relationship is where one object contains or uses another object
Supertype vs subtype
Denotes an “IS-A” relationship
Also known as Generalization Hierarchies
Supertype – A type of entity class that contains attributes that are common
across all specializations, A generalization
Subtype – A type of entity class that “inherits” attributes of its supertype as well as containing attributes of its own - A specialization
Strong vs Weak Entity
Strong entity – can exist independently of all other entities.
Supertypes. Denoted by: Square
Weak entity – can’t exist on its own; must have another entity to support it.Subtypes, Do not have their own identifier. Denoted by: round or double lines
When is a supertype/subtype relationships necessary?
There are attributes that apply to some, but not all, instances of an
entity.
There is a relationship that pertains to a defined subset of instances for
an entity.
Implications of having a relationship at supertype vs subtype level
Relationships at the supertype level indicate that instances of the
supertypes, including all subtypes, can participate in the relationship.
Relationships at the subtype level indicate only instances of that
subtype can participate in the relationship.
Completeness Constraints
The minimum number of subtype instances that a supertype instance must belong to.
To determine the type of completeness constraint, you must ask a question.
Must every instance of the supertype belong to a subtype?
- If an answer is ‘NO, an instance of the supertype doesn’t have to belong to any of the subtype(s)’, then
there is partial specialization. Denoted by a single line from the supertype to the supertype/subtype relationship indicator.
-If an answer is ‘YES, every instance of the supertype must belong to a subtype’, then there is total
specialization. Denoted by double lines from the supertype to the supertype/subtype relationship indicator.
Disjointedness Constraints
The maximum number of subtype instances that a supertype instance can belong to.
To determine the type of disjointedness constraint, you must ask a question.
- Can an instance of the supertype belong to more than one subtype?
- If an answer is ‘NO, an instance of the supertype may only belong to at most one subtype’, then the
disjoint rule is in effect. Denoted by a ‘d’ inside the supertype/subtype relationship indicator. - If an answer is ‘YES, an instance of the supertype can belong to more than one subtype’, then the
overlap rule is in effect. Denoted by an ‘o’ inside the supertype/subtype relationship indicator
Subtype discriminator
A discriminator is an attribute, or a group of attributes, in a supertype that will indicate any subtypes that an entity instance belongs to.
Disjoint Rule: A single attribute can be used as a discriminator in the supertype. The value for the discriminator will indicate which subtype, if any, an entity instance of the supertype belongs to.
Overlap Rule: For each subtype an attribute will need to be used as a discriminator in the supertype. The value of each attribute will indicate which subtypes an entity instance belongs to.
Transposing Process
- The name of each entity becomes the name of a “relation”.
- Attributes of the entity become attributes of the “relation”.
- The identifier of the entity becomes the proposed primary key of the “relation”, denoted by an underline.
- For a relationship, create a primary key (pk)/foreign key (fk) connection based on the type of relationship.
- Add a reference statement for the foreign key
a. Notation: RELATION_X(foreign key) mei RELATION(primary key) - Repeat steps 4 and 5 until all relationships have been implemented.
Foreign key placement for different types of relations
a. 1:1 – Examine the minimum cardinalities
i. If the minimum cardinalities are the same, then examine whether a simple or composite fk scenario exists, go with simple fk
ii. If the minimum cardinalities are different, go in the direction that avoids null values for the fk.
b. 1:N (N:1) – Take the pk from the relation on the “one” side and add it as a foreign key to the relation on the “many” side.
c. N:M – Create a new intersection relation.
i. The intersection relation will have a foreign key from each entity involved in the relationship.
ii. The fks will form a composite pk.
d. Supertype/Subtype
i. The pk of the supertype becomes a fk in each subtype.
ii. The fk in each subtype becomes the pk.
Participation Constraints
Total participation (existence dependency) – an entity can exist only if it participates in a specific
relationship.
Weak entities always have total participation
Partial participation – an entity can exist without participating in a specific relationship.
Identifying vs non-identifying relationship
Identifying Relationship
A solid relationship line that denotes that
the foreign key will need to help form a
primary key.
Non-Identifying Relationship
A dashed relationship line that denotes
that the foreign key will not be needed to
form a primary key.
Lookup Table
Also known as Reference table
Used to minimize storage of repetitive values
Ensures sure that only certain values are allowed
Helpful to create drop-down list of options in front-end interface
Typical use:
Store a code in the “main” table
Store the code and full value in the lookup table