<> Design questions

* There are three basic tables in the teaching database ：

Student list S（SNO,SNAME,AGE,SEX）, Their attributes represent student numbers respectively , Student name , Age , Gender . Class Schedule Card C（CNO,CNAME,TEACHER）, Its attributes represent the course number respectively , Course name , Class teacher name . Elective table SC（SNO,CNO,GRADE）, Their attributes represent student numbers respectively , Course number , achievement .
There are the following SQL Query statement ：
SELECT CNO
FROM C
WHERE CNO NOT IN
(SELECT CNO
FROM S,SC
WHERE S.SNO=SC.SNO
AND SNAME=‘ Zhang San ’);
Please complete the following questions ：
（1） Explain the above with Chinese sentences SQL Meaning of statement ;
（2） The above is expressed by equivalent relational algebraic expressions SQL Query statement .

* Established relationship R And functional dependency F：
R（A,B,C,D,E）,F = { ABC→DE,BC→D,D→E }.
Try the following questions ：
（1） relationship R What is your candidate code ?R Which paradigm does it belong to ? And explain the reasons .（3 branch ）
（2） If relationship R Not belong to BCNF, Please put the relationship R Gradually decompose into BCNF.（5 branch ）
requirement ： Write down the decomposition process to reach each level of paradigm , And indicate what type of functional dependency to eliminate .
（1） relationship R The candidate code is （A,B,C）,R∈1NF, because R Non primary attribute in D,E Pair candidate code （A,B,C） Partial functional dependency of .　（3 branch ）
（2） First, eliminate some functional dependencies
Decompose relationships into ：
R1(A,B,C) （A,B,C） Is a candidate code ,
R1 There is no nontrivial functional dependency in
R2(B,C,D,E),（B,C） Is a candidate code ,
R2 The functional dependency set of is ：F2={（B,C）→D,D→E}
In relation R2 Non primary attribute in E Pair candidate code （B,C） Transfer function dependency of , So will R2 Further decomposition ：
R21(B,C,D) ,（B,C） Is a candidate code ,
R21 The functional dependency set of is ：F21 = { （B,C）→D }
R22(D,E) ,D Is a candidate code ,
R22 The functional dependency set of is ：F22 = { D→E }
stay R1 There are no nontrivial functional dependencies in , stay R21,R22 The determinants of functional dependence in relational patterns are candidate codes , Therefore, the above three relationship models are BCNF.
four , Design questions
1. Established relationship STUDENT(S#,SNAME,SDEPT,MNAME,CNAME,GRADE),（S#,CNAME） Is a candidate code , Assume that the relationship has the following functional dependencies ：
（S#,CNAME）→SNAME,SDEPT,MNAME
S#→SNAME,SDEPT,MNAME
SDEPT→MNAME
Try the following questions ：
（1） relationship STUDENT Which paradigm does it belong to ? And explain the reasons .（3 branch ）
（2） If relationship STUDENT Not belong to BCNF, Please put the relationship STUDENT Gradually decompose into skillful
BCNF.（7 branch ）
requirement ： Write down the decomposition process to reach each level of paradigm , And indicate what type of functional dependency to eliminate .
（1） relationship STUDENT yes 1NF, because F Non primary attribute in SNAME,SDEPT,MNAME For candidate code （S#,CNAME） Partial functional dependency of .
（2）
① First, eliminate some functional dependencies （S#,CNAME）→SNAME,SDEPT,MNAME Decompose relationships into ：
R1(S#,SNAME,SDEPT,MNAME),S# Is a candidate code ,
R1 The functional dependency set of is ：
F1 = { S#→SNAME,SDEPT,MNAME,SDEPT→MNAME}
R2(S#,CNAME,GRADE),S# Is a candidate code ,
R2 The functional dependency set of is ：