Question type 4
Decomposing relational schema ： Examination site ： Basic functional dependencies of relational patterns , Relationship candidate key , Which paradigm does the relationship belong to ,
What is the relationship NF, decompose NF Schema set （10 branch ）
To master this topic, we must first master many basic concepts , Let's have a look , Just talk about what you need to test and what you will use to do the questions , I won't speak the official language in the book , How to understand how to come
one , Basic concepts
1, Functional dependency (FD): Function dependency is a constraint relationship between attributes within the same relationship , That is, in R(A1,A2...) Middle lecture A1 And A2 The relationship between , For example, set the attribute student number as X, Name is Y, Because the student number can determine the name , Then there is X decision Y Or Y Dependent on X, record as ：X->Y
2,FD collection （ Functional dependency set ）： Multiple FD Just put them together ： Such as function dependency set F={A->B,B->C,A->C}, If there is a set, there will be implication relations , therefore F logically implied X->Y, record as ：F|=X->Y
3, Hyperkey ： Assume a relationship R The property set of is U,X yes U Subset of , If U Some properties in X Can decide U, Namely X->U, So called X yes R A super key of （ Remove any attribute from the superkey ,X->U Still possible ）
4, Candidate key ： In hyperkey , If you remove any of the attributes in the superkey ,X->U Will not be established （ That is, there is no redundant attribute in the super key ）, Then call this super key a candidate key
5,L class ：F={A->B,B->C,A->C} All in -> Properties on the left , Namely A,B
6,R class ：F={A->B,B->C,A->C} All in -> Properties on the right , Namely B,C
7, Minimum functional dependency ： Difficult words , Pass the examination , But not in the past two years , It's the test site , You can read by yourself if you want to learn , Not here
8, Decomposition of relational schema ： We can R(A1,A2,A3,A4) Decompose into R1（A1,A2）,R2（A3,A4）
9, Complete functional dependency ： With attribute set X And attribute sets Y, Functional dependency X->Y, Remove X Any attribute in ,X->Y No （ Which means that X No redundant attribute in ）, that Y Fully functional dependent X, Otherwise, it is called local dependency
10, Non primary attribute ： Attributes that are not included in any candidate code in the relationship .
for example ： In relation —— student （ Student ID , full name , Age , Gender , class ） in , Primary attribute , Candidate code is “ Student ID ”, So the others “ full name ”,“ Age ”,“ Gender ”,“ class ” Can be called non primary attribute
（ Similar decision candidate key , It all depends on the L Whether the class has redundant attributes ）
11, Transitive dependencies that may appear in large problems ： There are candidate keys X, Non primary attribute Y,A, if X->Y,Y->A,Y!->X,A Not belonging to Y, So called X->A Is transitive dependency （A Delivery depends on X）
two , Paradigm of relational pattern
Inclusion relationship exists between paradigms ：1NF contain 2NF contain 3NF contain BCNF
1, First paradigm （1NF）
As long as the relationship mode R(A1,A2...) All properties in A1,A2...... Are inseparable attributes , All atomic properties , Then the first paradigm is satisfied
Not satisfied with 1NF Relationship mode of R, We will R Become satisfied 1NF Relationship mode of ： Or decompose its non atomic properties , Or decompose its relational schema , You are satisfied with the general questions 1NF
2, Second normal form （2NF）
If relationship mode R yes 1NF, And each non primary attribute is fully functional dependent on candidate keys , be R Is the second normal form （2NF）
Judge whether it is 2NF： Set candidate key （ Common candidate keys in large questions generally include 2 Attributes ） Determine each non primary attribute in turn , Judge the right side of function dependency （L class ） Whether there is redundancy attribute , If not , Then comply with 2NF
, Otherwise, non conformance
Not satisfied with 2NF Relationship mode of R, We will R Become satisfied 2NF Relationship mode of ： It's actually a simple category , We put R Decompose into R1,R2....... Non primary attributes that will be redundant with candidate keys , And its corresponding candidate keys are placed in R1 in ; Non primary attributes that will not be redundant with candidate keys , And its corresponding candidate keys are placed in R2 in . Let's take an example to understand :
example ： Assume a relationship R（ Customer number , Purchase item number , quantity , Name of manufacturer , Business address ）, judge R Whether it belongs to 2NF, If R Not belonging to 2NF, Please R Break down into 2NF Schema set
solution ： Candidate key is customer number , Purchase item number , have （ Customer number , Purchase item number ）->（ Name of manufacturer , Business address ）, Due to single attribute “ Purchase item number ” You can decide “
Name of manufacturer , Business address ”, Therefore, there is a case where the non primary attribute locally depends on the candidate key ,R Not belonging to 2NF
take R Break down into 2NF Pattern set thinking （ Not on the paper , Write only on scratch paper ）
take R Break down into 2NF Mode set is ：
R1（ Purchase item number , Name of manufacturer , Business address ）
R2（ Customer number , Purchase item number , quantity ）
3, The third paradigm （3NF）
Go over it ：11, Transitive dependencies that may appear in large problems ： There are candidate keys X, Non primary attribute Y,A, if X->Y,Y->A,Y!->X,A Not belonging to Y, So called X->A Is transitive dependency （A Delivery depends on X）
If relationship mode R yes 2NF, And each non primary attribute does not pass dependent on candidate keys , be R The third paradigm （3NF）
Judge whether it is 3NF： By definition , See if there is a phenomenon that non primary attribute transfer depends on candidate keys in the relational schema .
Not satisfied with 3NF Relationship mode of R, We will R Become satisfied 3NF Relationship mode of ：
If in R（X,Y,A,B,C） in , Candidate keys are X, Non primary attribute is Y,A,B,C, existence X->Y,Y->A,Y!->X,A Not belonging to Y（ Namely X->A,A Delivery depends on X）, Then just R Decompose into R1（X,Y,B,C）,R2（Y,A） Can be satisfied 3NF, It can be understood as R1 Only the Y,R2 Only two non primary attributes that are transitively dependent on candidate keys are retained Y,A
Look at an example to understand
example ： Relational mode ： readers （ Reader number , full name , Company , address ）, Each unit has only one address , Please normalize it to 3NF
solution ：
From the meaning of the title ： Reader number -> Company -> address
readers 1（ Reader number , full name , Company ） Only the address at the end of the delivery position is removed
Company （ Company , address ） Only two non primary attributes that are transitively dependent on candidate keys are retained ： Company , address
True topic ：
Technology