R=(StudentID,LName,FName,CourseID,CourseSection,CourseName,Grade,InstructorLName

Question

R=(StudentID,LName,FName,CourseID,CourseSection,CourseName,Grade,InstructorLName,InstructorFName,Building,OfficeNo)

Assumptions:

StudentID is computer generated and unique

A course can have multiple sections

An office is assigned to only one instructor.

It is okay to generate a surrogate key at the time of normalization if that is necessary.

Using straight-to-BCNF process normalize the above relation to BCNF. Make sure you follow the four step process. Show your work.

List final set of relations that are normalized to BCNF. Include schemas for each of the final set of relations.

Incidate all the primary, foreign, surrogate keys in the final schemas.

Explanation / Answer

BCNF (Boyce Code Normal Form):

R=(StudentID,LName,FName,CourseID,CourseSection,CourseName,Grade,InstructorLName,InstructorFName,Building,OfficeNo)

Functional dependencies:

StudentID -> LName, FName

CourseID -> CourseSection, CourseName

StudentID, CourseID->Grade

CourseName ->InstructorLName,InstructorFName,Building,OfficeNo

Step1 : 1NF ("Only atomic Values")

Student(StudentID,FName, LName)

Student_Course(Student_ID,CourseID,CourseSection,CourseName,Grade,InstrInstructorLName,InstructorFName,Building,OfficeNo)

Step2 : 2NF (1NF, "Delete partial key dependancies")

Student(StudentID,FName, LName)

Student_Course(Student_ID,CourseID,Grade)

Course(CourseID,CourseSection,CourseName, InstructorLName,InstructorFName,Building,OfficeNo)

Step3: 3NF (1NF, 2ND, "Remove Transitive Dependancies")

Student(StudentID,FName, LName)

Student_Course(Student_ID,CourseID,Grade)

Course(CourseID,CourseSection,CourseName, InstructorLName,InstructorFName)

Instructor(InstructorFName,InstructorFName,Building,OfficeNoBuilding,OfficeNo,Building,OfficeNo)

Step4: BCNF(1NF, 2NF,3NF, "Every Determinant is a candidate key")

Student(StudentID, FName, LName) : Only Determinant is StudentID

Student_Course(Student_ID,CourseID,Grade): Only Determinant are StudentID, CourseID

Course(CourseID,CourseSection,CourseName, InstructorLName,InstructorFName) : The determinant are CourseID, CourseSection

Instructor(InstructorFName,InstructorFName,Building,OfficeNoBuilding): Only determinat are InstructorFname, InstructorLname

So the course table has been divided further and the BCNF outputs are

Student(StudentID, FName, LName) : Pk (StudentId)

Student_Course(Student_ID,CourseID,Grade) : FK(studentID refers primary key of Student, CourseId refers primary key of Course)

Course(CourseID,CourseSection, CourseName) : PK(CourseID)

Course_Section(CourseSection,InstructorLName,InstructorFName) PK(courseSection)

Instructor(InstructorFName,InstructorFName,Building,OfficeNoBuilding)

Related Questions

Navigate

• Browse (All)
• Browse R
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.