Question

3-students take courses. Someone design this table: Student_Course (student name, ID , SSN , course number, grade, home address, course credit ) Description: ID, SSN; each one has a unique value for a given student. Given ID and course number we can get grade. Given SSN and course number we can get grade. Given ID we can get everything except grade and course credit. Given SSN we can get everything except grade and course credit. Given course number we can get course credit. Student name can be first and last name We need to be able to get detail of home address such as city, state and zip Each step may require multiple actions. a. Identify primary key. b. Is this table in 1st NF? If not convert it into 1st NF. c. Is the table in a in 2nd NF? If not convert it to be in 2nd NF. d. Is the table in b in 3rd NF? If not convert it to be in 3rd NF. e. Is the table in c in BCNF? If not convert it to be in BCNF.

Answer 1

A) The FD's possible are :

ID, CourseNumber -> Garde

SSN ,CourseNumber -> Grade

ID -> studentName, SSN, CourseNumber, homeaddress

SSN -> studentName, SSN, courseNumber, homeaddress

CourseNumber -> credit

The primary key for the relation is (ID, CourseNumber) (SSN, CourseNumber)

B) The given relation is in 1NF since it doesn't contain any Multivalued attribute.

C) Given relation not in 2NF since it contain partial FD.

So decompose it to form 2NF relation

R1:(ID, SSN, studentName, courseNumber, homeaddress)

R2:(courseNumber, credit)

To make decomposition lossless, we need to include primary key of R1 and attribute grade in one relation. Since R1 includes 2 primary key, include either one of them. Let's take ID to add in R3.

So R3:(ID, Grade)

Here in R1, student name can be further divided to First name and Last name.

While home address is divided as (city, state, zip)

So R1 =(ID, SSN, FirstName, LastName, CourseNumber, city, state, zip)

D) The decomposed relation R1, R2 and R3 is in 3NF.

E) The decomposed relation R1, R2 and R3 are in BCNF as well.

If you have any questions comment down. Please don't simply downvote and leave. If you are satisfied with answer, please? upvote thanks..