Podcast
Questions and Answers
[Blank] in database design aims to minimize redundancy, organize data efficiently, and reduce the potential for data anomalies.
[Blank] in database design aims to minimize redundancy, organize data efficiently, and reduce the potential for data anomalies.
Normalization
A table violates First Normal Form (1NF) when it contains ______ attributes, composite attributes, or combinations thereof, requiring each attribute to hold only single, atomic values.
A table violates First Normal Form (1NF) when it contains ______ attributes, composite attributes, or combinations thereof, requiring each attribute to hold only single, atomic values.
multivalued
In Second Normal Form (2NF), a nonprime attribute must be fully ______ on the entire primary key; if the primary key contains only a single attribute, 2NF is automatically satisfied.
In Second Normal Form (2NF), a nonprime attribute must be fully ______ on the entire primary key; if the primary key contains only a single attribute, 2NF is automatically satisfied.
dependent
A table violates Third Normal Form (3NF) if a nonprime attribute is transitively ______ on the primary key, meaning it depends on another nonprime attribute rather than directly on the key itself.
A table violates Third Normal Form (3NF) if a nonprime attribute is transitively ______ on the primary key, meaning it depends on another nonprime attribute rather than directly on the key itself.
To achieve First Normal Form (1NF), one technique involves removing the attribute that violates 1NF and placing it in a ______ relation along with the primary key.
To achieve First Normal Form (1NF), one technique involves removing the attribute that violates 1NF and placing it in a ______ relation along with the primary key.
Flashcards
Database Normalization
Database Normalization
Restructuring the logical data model of a database to eliminate redundancy, organize data efficiently and reduce the potential for data anomalies.
Data anomalies
Data anomalies
Inconsistencies in the data stored in a database, such as update, insertion, and/or deletion.
First Normal Form (1NF)
First Normal Form (1NF)
It disallows multivalued attributes, composite attributes, and their combinations. Each attribute in a tuple must have a single, atomic value.
Second Normal Form (2NF)
Second Normal Form (2NF)
Signup and view all the flashcards
Third Normal Form (3NF)
Third Normal Form (3NF)
Signup and view all the flashcards
Study Notes
- Database normalization restructures the logical data model to eliminate redundancy, organize data efficiently, and reduce data anomalies.
- A simple relations table is the goal of normalizations
Data Anomalies
- Data anomalies are inconsistencies that arise in a database due to update, insertion, or deletion operations.
- They occur when a record is stored in multiple locations, and not all instances are updated.
- Anomalies can be prevented by implementing seven levels of normalization, known as Normal Forms (NF), the first three being the focus.
Overview of Normal Forms
- Normalization requires understanding the requirements of each of the three Normal Forms.
- Normal Forms are progressive, to achieve 3rd NF requires 2nd NF, and 2nd NF requires 1st NF.
First Normal Form (1NF)
- 1NF disallows multivalued attributes, composite attributes, and their combinations, requiring only atomic (simple) values.
- Each attribute in a tuple must hold a single value.
- A DEPARTMENT relation is an example containing Dname, Dnumber, Dmgr_ssn, and Dlocations where Dlocations is atomic.
- Three techniques achieve first normal form:
- Removing the attribute that violates 1NF and placing it in a separate relation with the primary key decomposes the non-1NF relation into two 1NF relations.
- Expanding the key to create a separate tuple for each non-atomic attribute means the primary key becomes a combination, disadvantaging redundancy.
- Replacing the non-atomic attribute with n-attributes (e.g., Dlocation1, Dlocation2, Dlocation3) introduces NULL values.
Second Normal Form (2NF)
- 2NF is typically used in tables with a multiple-field primary key (composite key).
- It the primary key contains a single attribute, is not necessary applicable
- A nonprime attribute has to be fully dependent on the entire primary kay
- The EMP_PROJ relation in 1NF violates 2NF, the nonprime attribute Ename violates 2NF.
- FD2 makes Pname and Plocation partially dependent the nonprime attributes.
- A relation schema transforms into a number of 2NF relations where nonprime attributes associate only with the part of the primary key on which they are fully dependent.
Third Normal Form (3NF)
- 3NF is based on the concept of transitive dependency.
- A relation schema R is in 3NF if it satisfies 2NF and no nonprime attribute of R is transitively dependent on the primary key.
- Ssn→Dmgr_ssn involves a transitive dependency.
- Ssn → Dnumber and Dnumber → Dmgr_ssn hold true where Dnumber becomes a non-key not part of the EMP_DEPT key.
- The dependency of Dmgr_ssn on Dnumber is undesirable in EMP_DEPT as Dnumber is not a key.
- The EMP_DEPT relation schema is not in 3NF due to transitive dependency of Dmgr_ssn (also Dname) on Ssn via Dnumber.
- EMP_DEPT can normalize by decomposing it into two 3NF relation schemas ED1 and ED2 for independent entity facts about employees and departments.
Studying That Suits You
Use AI to generate personalized quizzes and flashcards to suit your learning preferences.
