logical data modelling Flashcards
What is a functional dependency, and how does it relate to normalization?
A functional dependency is a rule that states that the value of one attribute (or set of attributes) uniquely determines the value of another attribute.
What is the main goal of normalization in database design?
Reducing redundancy is a major goal of normalization.
What is the first normal form (1NF), and what are the conditions for a table to be in 1NF?
Each cell contains a single value. This means there are no repeating groups or arrays within a cell.
All attributes are atomic. This means that attributes cannot be further decomposed into smaller meaningful components.
What is the second normal form (2NF), and what is the condition for a table to be in 2NF?
A table is in 2NF if it is in 1NF and every non-key attribute is fully dependent on the primary key.
What is the condition for a table to be in 3NF?
It’s already in 2NF.
There are no transitive dependencies.
A transitive dependency occurs when a non-key attribute depends on another non-key attribute, rather than directly on the primary key
What is the primary key of a table, and how is it different from a foreign key?
Uniqueness: The primary key must have unique values for each row.
Non-null: The primary key cannot contain null values.
Minimal: The primary key should be as small as possible to improve performance
foreign key characteristics
Reference: The values in a foreign key must match the values in the primary key of the referenced table.
Nulls: Foreign keys can be null, but this often indicates a missing or optional relationship.
Referential integrity: Databases typically enforce referential integrity, which ensures that foreign key values are valid and match existing primary key values.
What is a key in a relational database?
Determinant: A determinant is a set of attributes that uniquely determines the values of all other attributes in a relation.
Candidate key: A candidate key is a minimal determinant, meaning that no attribute can be removed from the set without losing its uniqueness property.
What is the difference between a candidate key and a primary key?
All primary keys are candidate keys.
Not all candidate keys are primary keys.