Pubali bank Recruitment Test Short Question Answer -04

Pubali bank Recruitment Test Short Question Answer -05

5(a) Candidate key and 2NF

Given the relation:

STUDENT_COURSES
(StudentID, StudentName, CourseID, CourseTitle, CourseCredit, Grade)

Functional dependencies:

• StudentID → StudentName
• CourseID → CourseTitle, CourseCredit
• (StudentID, CourseID) → Grade

Candidate key

The candidate key is:

(StudentID, CourseID)

Together, these attributes determine all other attributes in the relation.

Is the relation in 2NF?

No, the relation is not in Second Normal Form (2NF).

The candidate key is composite, but some non-key attributes depend on only part of the key:

• StudentName depends only on StudentID
• CourseTitle and CourseCredit depend only on CourseID

These are partial dependencies, which violate 2NF.

5(b) Decomposition into 3NF

The relation can be decomposed into the following three relations:

1. STUDENT

STUDENT (StudentID, StudentName)

• Primary key: StudentID
• Functional dependency: StudentID → StudentName

2. COURSE

COURSE (CourseID, CourseTitle, CourseCredit)

• Primary key: CourseID
• Functional dependency: CourseID → CourseTitle, CourseCredit

3. STUDENT_COURSE / ENROLLMENT

ENROLLMENT (StudentID, CourseID, Grade)

• Primary key: (StudentID, CourseID)
• StudentID is a foreign key referencing STUDENT
• CourseID is a foreign key referencing COURSE
• Functional dependency: (StudentID, CourseID) → Grade

Therefore, the final 3NF design is:

STUDENT(StudentID, StudentName)
Primary Key: StudentID

COURSE(CourseID, CourseTitle, CourseCredit)
Primary Key: CourseID

ENROLLMENT(StudentID, CourseID, Grade)
Primary Key: (StudentID, CourseID)
Foreign Keys:
StudentID → STUDENT(StudentID)
CourseID → COURSE(CourseID)

All three relations are in Third Normal Form (3NF).