Question 35
NECO
•
2020
•
COMPUTER_STUDIES
•
objective
Consider the relation STUDENT\(\)(CourseCode, StudentID, Name, Email, Grade) with the functional dependencies: (CourseCode, StudentID) \(\rightarrow\) Name, Email, Grade; StudentID \(\rightarrow\) Name, Email. (a) State the highest normal form of this relation. (b) Decompose the relation into a set of relations that are in Boyce‑Codd Normal Form (BCNF). (c) Write an SQL query that lists the StudentID and Name of all students who have obtained a grade of ‘A' in more than one distinct course.
Answer Options
A.
Highest NF: 1NF; BCNF decomposition: STUDENT_INFO(StudentID, Name) and ENROLLMENT(CourseCode, StudentID, Grade, Email); SQL: SELECT s.StudentID, s.Name FROM STUDENT_INFO s JOIN ENROLLMENT e ON s.StudentID = e.StudentID WHERE e.Grade = 'A' GROUP BY s.StudentID, s.Name HAVING COUNT(DISTINCT e.CourseCode) > 1;
Correct Answer
B.
Highest NF: 1NF; BCNF decomposition: STUDENT_INFO(StudentID, Name, Email) and ENROLLMENT(CourseCode, StudentID, Grade); SQL: SELECT s.StudentID, s.Name FROM STUDENT_INFO s JOIN ENROLLMENT e ON s.StudentID = e.StudentID WHERE e.Grade = 'A' GROUP BY s.StudentID, s.Name HAVING COUNT(DISTINCT e.CourseCode) > 1;
C.
Highest NF: 1NF; BCNF decomposition: STUDENT_COURSE(CourseCode, StudentID, Grade) and STUDENT_DETAILS(StudentID, Name, Email); SQL: SELECT s.StudentID, s.Name FROM STUDENT_DETAILS s JOIN STUDENT_COURSE e ON s.StudentID = e.StudentID WHERE e.Grade = 'A' GROUP BY s.StudentID, s.Name HAVING COUNT(DISTINCT e.CourseCode) > 2;
D.
Highest NF: 2NF; BCNF decomposition: STUDENT_INFO(StudentID, Name, Email) and ENROLLMENT(CourseCode, StudentID, Grade); SQL: SELECT s.StudentID, s.Name FROM STUDENT_INFO s JOIN ENROLLMENT e ON s.StudentID = e.StudentID WHERE e.Grade = 'A' GROUP BY s.StudentID, s.Name HAVING COUNT(e.CourseCode) > 1;
Correct Answer
B
Correct Option:
Highest NF: 1NF; BCNF decomposition: STUDENT_INFO(StudentID, Name, Email) and ENROLLMENT(CourseCode, StudentID, Grade); SQL: SELECT s.StudentID, s.Name FROM STUDENT_INFO s JOIN ENROLLMENT e ON s.StudentID = e.StudentID WHERE e.Grade = 'A' GROUP BY s.StudentID, s.Name HAVING COUNT(DISTINCT e.CourseCode) > 1;
About This Question
This is
Neco
2020
Computer Studies
Question 35.
It is one of the
objective
questions from the
2020
Neco
Computer Studies
examination.
Difficulty level:
Hard
.