Normalization & Representing Entities Into Relations Flashcards
What is Total Specialization? and how is it represented in an ERD?
specifies that each entity instance must be a member of some subtype, and it is represented using double lines connecting the supertype to the circle
What is Partial Specialization? and how is it represented in an ERD?
specifies that an entity instance is allowed to not be a member of any subtype, and it is represented using a single line connecting the supertype to the circle
What is the Disjoint Rule?
if an entity instance is a member of one subtype, it cannot be a member of another subtype
What is the Overlap Rule?
an entity instance can be a member of two or more subtypes
How do you represent Binary 1:N Relationships?
Add the primary key of the entity that is on the one-side of the relationship as a foreign key to the entity on the many side of the relationship
e.g EMPLOYEE(Name, EmpNo)
DEPENDENT(DepName, DepNo, EmpNo)
How do you represent Binary 1:1 Relationships if they can share the same primary key, the entity types are total and do not participate in other relationships
Merge the two entities into one entity
How do you represent Binary 1:1 Relationships if one of the entities participate in another relationship?
Add the primary key of the entity A as a foreign key to entity B
How do you represent Binary 1:1 Relationships if the relationship between the entities have an attribute?
Any attributes in the relationship will be placed in the entity where the foreign key is placed
How do you represent Binary M:N Relationships?
Create a new relation, and add the primary keys of both entities to the new relation as foreign keys and combine them to make a composite primary key for the new relation, if the relationship has any attributes, then add them to the new table
How do you represent Unary 1:N Relationships?
Add a foreign key to the table named with reference to the relationship.
How do you represent Unary M:N Relationships?
Create a new relation, and add the primary keys of both entities to the new relation as foreign keys and combine them to make a composite primary key for the new relation, if the relationship has any attributes, then add them to the new table.
NOTE: One of the foreign keys must be renamed to something similar
What is Normalization?
The process of removing inappropriate functional dependencies in a relation
Who is the originator of relational models and proposer of the three normal forms.
Dr Edgar F. Codd
What are repeating groups?
they are the presence of a single multivalued attribute or multiple multivalued attributes in a single instance of an entity
What is an Unnormalized Form?
It is a table that contains one or more repeating groups
What are the problems with Unnormalized Form?
- Difficulty in updating
- Difficult to query multivalued attributes
- No atomicity
What is the First Normal Form?
A relation in which the intersection of each row and column has one and only one value.
How do you transform an UNF to 1NF
we need to identify the repeating groups of the UNF relation and decompose the repeating groups into new relations
What is the Second Normal Form?
a relation is 2NF if it is already in 1NF and has the follow conditions
- every non-key attribute is fully functionally dependent on the primary key
- no non-key attribute exists in the relation
- the primary key consists of only one attribute
What is the Third Normal Form?
a relation is in 3NF if it is already in 2NF and has no transitive dependencies
How do you transform a relation to 3NF?
we decompose the relation by separating the attributes, that caused the transitive dependency to exist, to a new relation
