Question

1. Consider a Relation that holds attributes of courses and sections at a university: R = CourseNo, SecNo, OfferingDept, CreditHours, CourseLevel, InstructorSSN, Semester, Year, Days Hours, RoomNo, NoOfStudents Assume that the following functional dependencies hold:

• CourseNo → Offering Dept, CreditHours, CourseLevel

• CourseNo, SecNo, Semester, Year → Days Hours, RoomNo, NoOfStudents, InstructorSSN

• RoomNo, Days Hours, Semester, Year → InstructorSSN, CourseNo, SecNo Normalize Relation R upto 3NF.

2. Consider the relation for published books: Book = Book title, AuthorName, Book type, ListPrice, Author affil, Publisher Author affil refers to affiliation of the author. Assume that the following FDs exist: • Book title → Publisher, Book type • Book type → ListPrice • AuthorName → Author affil Apply normalization to Book relation until 3NF

can you please show 1nf 2nf and 3nf

thank you

Answer 1

1.to explain in a simplest way,

All the attributes in the right hand side of functional dependencies can be derived so attributes that are not in right hand side of functional dependencies should be present in candidate key

Now, closure of an attribute is the set of those attributes which can be derived from the closure attribute

So closure of course No is {Course No}+ = {Course No, Offering Dept, CreditHours, CourseLevel} as courseNo can be derived from CourseNo simply ----(1)

{CourseNo, SecNo, Semester, Year}+ = {CourseNo, SecNo, Semester, Year, Days Hours, RoomNo, NoOfStudents, InstructorSSN,Offering Dept, CreditHours, CourseLevel} = R -----(2)

Now, {RoomNo, Days Hours, Semester, Year}+ = {RoomNo, Days Hours, Semester, Year,InstructorSSN, CourseNo, SecNo} now as {Course No}+ = {Course No, Offering Dept, CreditHours, CourseLevel}} we can substitute that and get

{RoomNo, Days Hours, Semester, Year}+ = {RoomNo, Days Hours, Semester, Year,InstructorSSN, CourseNo,Offering Dept, CreditHours, CourseLevel, SecNo}

From (2) we can say {CourseNo, SecNo, Semester, Year}+ contained {NoOfStudents} as well

So, {RoomNo, Days Hours, Semester, Year}+ will contain {NoOfStudents} too

So, {RoomNo, Days Hours, Semester, Year}+ = {RoomNo, Days Hours, Semester, Year,InstructorSSN, CourseNo,Offering Dept, CreditHours, CourseLevel, SecNo,NoOfStudents} = R ------(3)

From (2) and (3) we can say, following both are candidate keys:

Key1 = {CourseNo, SecNo, Semester, Year}

Key2 = {RoomNo, Days Hours, Semester, Year}

The relationship is in 1NF but not in 2NF

Now for 2NF, we can see CourseNo → Offering Dept, CreditHours, CourseLevel is a partial dependency for Key1 as CourseNo is there in Key1 So 2NF form of the Relation R is as follows:

R1 = {Course no, Offering dept, Credit hours, Course level}

R2 = {Course no, Sec no, Instructor ssn, Semester, Year, Days hours, Room no, No of students}

As there is no transitive dependency of R1 and R2 on candidate keys , we can safely say R1 and R2 are in 3NF also

2. Candidate key has been found as {BookTitle , AuthorName} as

{BookTitle , AuthorName}+ = {BookTitle , AuthorName,Publisher,Book type,Author affil } = {BookTitle , AuthorName,Publisher,Book type,ListPrice,Author affil } as {Book type}+ = {Book type,ListPrice} [substituted for {Book type}]

So, {BookTitle , AuthorName}+ = Book

The relationship is in 1NF

The relationship is not in 2NF as no attributes are totally functionally dependent on the key

So, required 2NF decomposition is

B1 = {BookTitle, AuthorName}

B2 = {BookTitle, BookType, ListPrice, Publisher}

B3 = {AuthorName, AuthorAffil}

The above relation is not in 3NF as there is transitive dependency BookTitle->BookType->ListPrice and BookType is not a key

Required decomposition

B1 = {BookTitle, AuthorName}

B21 = {BookTitle, BookType, Publisher}

B22 = {BookType,ListPrice}

B3 = {AuthorName, AuthorAffil}