Questions and Answers
What is the main purpose of converting a relation from 2NF to 3NF?
To remove partial dependencies
In the context of normalization, what does the term 'transitive dependency' refer to?
An attribute functionally determines a non-key attribute through another non-key attribute
Which of the following is a violation of the Third Normal Form (3NF)?
A non-prime attribute is transitively dependent on the primary key
What action is taken to resolve a transitive dependency violation in 3NF normalization?
Signup and view all the answers
Which of the following statements best describes Partial Dependency in database normalization?
Signup and view all the answers
What is the significance of a Candidate Key in database design?
Signup and view all the answers
How does the decomposition of relations help in achieving normalization?
Signup and view all the answers
Study Notes
Normalization Forms
- A non-prime attribute is an attribute that is not part of the primary key.
Second Normal Form (2NF)
- A relation R is in 2NF if and only if it is in 1NF and every non-prime attribute of relation is fully dependent on the primary key.
- Example: StudentID and ProjectID are prime key attributes in a relation, and StudentName and ProjectName are non-prime attributes.
- Partial dependency occurs when a non-prime attribute is dependent on only one part of the primary key.
Solving 1NF Issues
- To solve the problem of composite attributes, insert separate attributes for each sub-attribute.
- To solve the problem of multi-valued attributes, insert separate attributes for each value or place the multi-valued attribute in a separate relation along with the primary key of the original relation.
Second Normal Form (2NF) Decomposition
- Decompose the relation to remove partial dependency and violation of 2NF, for example, decomposing the Student table into StudentInfo and ProjectInfo tables.
Third Normal Form (3NF)
- A relation is in 3NF if it is in 1NF and 2NF, and no non-primary-key attribute is transitively dependent on the primary key.
- To normalize a 2NF relation to 3NF, remove transitive dependencies by placing the transitively dependent attribute(s) in a new relation along with a copy of the determinant.
Third Normal Form (3NF) Example
- In the example relation, STU_STATE is transitively dependent on STUD_NO, violating 3NF.
- To convert it to 3NF, decompose the relation STUDENT into STUDENT and STATE_COUNTRY tables.
Studying That Suits You
Use AI to generate personalized quizzes and flashcards to suit your learning preferences.
