<> 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
(S#,CNAME)→GRADE
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 :
F2={(S#,CNAME)→GRADE}
② In relation R1 Non primary attribute in MNAME Pair candidate code S# Transfer function dependency of S#→MNAME, So will R1 Further decomposition :
R11(S#,SNAME,SDEPT) ,S# Is a candidate code ,
R11 The functional dependency set of is :
F11 = { S#→SNAME,SDEPT}
R12(SDEPT,MNAME) ,SDEPT Is a candidate code ,
R12 The functional dependency set of is :
F12 = { SDEPT→MNAME}
stay R2,R11,R12 Functional dependencies in relational patterns are nontrivial , And the determinants are candidate codes , Therefore, the above three relationship models are BCNF.

<> Comprehensive design questions

Technology
©2019-2020 Toolsou All rights reserved,
【 Explain in detail 】 Interview must ask :SpringBoot Automatic configuration principle 【 Hyper detail 】Java Realization of student information management system 2020 The 11th National Blue Bridge Cup Python group JavaScript note ( five )---- Branch statement MYSQL database SQL Sentence practice experiment EXERCISES. 1 SIMPLE COMMANDSMySQL Basics Commonly used sentence ( Add / delete / modify query )C++ of string of compare usage vue limit input Only positive numbers can be entered C Language of a Gobang game implementation Programmer refused due to low salary offer,HR become shame , Netizens instantly blew up ..