CH5 Flashcards
Transforming a Data Model into a Relational Design
• Create a relation for each entity.
– A relation has a descriptive name and a set of attributes that describe the entity.
• Specify a primary key.
• Specify column properties
• The relation is then analyzed using the normalization rules
Column Properties
– Data type
– Null status
– Default values (if any)
– Data constraints (if any)
Modification Problems
• Tables that are not normalized will experience issues known as modification problems
Solving Modification Problems
• Most modification problems are solved by breaking an existing table into two or more tables through a process known as normalization.
Functional Dependency
The relationship (within the relation) that describes how the value of one attribute may be used to find the value of another attribute.
Determinant
The attribute that can be used to find the value of another attribute in the relation
Candidate Key
– The value of a candidate key can be used to find the value of every other attribute in the table.
– A simple candidate key consists of only one attribute. – A composite candidate key consists of more than one attribute.
Normal Forms
– First Normal Form (1NF)
– Second Normal Form (2NF)
– Third Normal Form (3NF)
– Boyce-Codd Normal Form (BCNF)
Normalization
breaking them apart into many component relations
Denormalization
- There are situations where denormalized relations are preferred.
- Fundamental reason to denormalize is to improve query performance
Representing Weak Entities
- If not ID-dependent, use the same techniques as for strong entities.
- If ID-dependent, then must add primary key of the parent entity.
1:1 Relationships
– The key from one relation is placed in the other as a foreign key.
– It does not matter which table receives the foreign key
1:N Relationships
• However, in a 1:N the foreign key always goes into the many-side of the relationship.
– The 1 side is called the parent.
– The N side is called the child.
N:M Relationship
• To create an N:M relationship, a new table is created. This table is called an intersection table.
Intersection Table
• An intersection table has a composite key consisting of the keys from each of the tables that it connects.