-Normalize the database into smaller tables in BCNF complete with foreign key co

Question

-Normalize the database into smaller tables in BCNF complete with foreign key constraints.

-Normalize the database into smaller tables in BCNF complete with foreign key constraints.

-Using only the BCNF tables, create a VIEW named CSR_VIEW that displays the original table except the columns are ordered: TERM, CRN, ID, StudentEmail, Course, InstructorEmail, Grade and ordered by TERM, CRN, StudentEmail. UHID CRN TERM StudentEmail 1384 696 FA17 lamanh@houston.edu ICS211 english@houston.edu 3759 748 FA17 justin@houston.edu ICS312 taniguchi@houston.edu 8467 696 FA17 sharon@houston.edu ICS211 english@houston.edu 8467 764 SP18 sharon@houston.edu ICS141 espero@houston.edu 8615 748 FA17 jimmy@houston.edu ICS312 taniguchi@houston.edu 8615 764 SP18 jimmy@houston.edu ICS141 espero@houston.edu Course InstructorEmail Grade

Explanation / Answer

Normalization to BCNF

Functional Dependencies:

UHID -> StudentEmail , Grade

CRN -> Course , InstructorEmail

UHID,CRN -> TERM

Tables in BCNF:

Student(UHID , StudentEmail , Grade)

Course(CRN , Course , InstructorEmail)

StudentCourse(UHID,CRN , TERM)

underlined are primary keys and italicised are foreign keys. Some attributes are both.

View

Create view CSR_VIEW as Select TERM, CRN, ID, StudentEmail, Course, InstructorEmail, Grade from Student inner join StudentCourse on Student.UHID = StudentCourse.UHID inner join Course on StudentCourse.CRN = Course.CRN order by TERM, CRN, StudentEmail;

Do ask if any doubt.Please upvote.

Related Questions

Navigate

• Browse (All)
• Browse #
• Subjects

---

---

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