Based on Monster group and GPT write:
Based on the given function dependency, you can get {S#, CNAME} candidate code for the relationship pattern STUDENT.
First, we need to determine whether STUDENT conforms to 2NF. It can be found that the properties in R1(S#,SNAME,SDEPT,MNAME) all depend on only one part of the candidate code {S#}, while the properties in R2(S#,CNAME,GRADE) all depend on the other part of the candidate code {CNAME, S#}. Therefore, STUDENT conforms to 2NF.
Then, we determine whether STUDENT conforms to 3NF. It can be found that R1(S#,SNAME,SDEPT,MNAME) has a transition-function dependency of non-primary SDEPT on candidate codes, because S# → SDEPT, SDEPT → MNAME, So R1 can be further decomposed into R11(S#,SNAME,SDEPT) and R12(SDEPT,MNAME). Therefore, STUDENT does not conform to 3NF and needs to be decomposed.
Decompose STUDENT into R11(S#,SNAME,SDEPT), R12(SDEPT,MNAME), and R2(S#,CNAME,GRADE), so that each relation pattern is consistent with 3NF.
