Question

What is the multivalue, multicolumn problem? What is a multivalued dependency, and how is it resolved by 4NF? To answer these questions, alter exercise 2.40 to allow students to have multiple majors. In this case, the relational structure is: STUDENT (StudentNumber, StudentName, SiblingName, Major) A. Define and discuss the multivalue, multicolumn problem. Define and discuss a multivalued dependency. B. Show an example of this relation for two students, one of whom has three siblings and the other of whom has one sibling. Assume that each student has a single major. C. Show the data changes necessary to add a second major for only the first student. D. Based on your answer to part C, show the data changes necessary to add a second major for the second student. E. Explain the differences in your answers to parts C and D. Comment on the desirability of this situation. F. Define and discuss 4NF, and how 4NF can be used to allow a set of wellformed relations. . Divide this relation into a set of well-formed

Answer 1

A:

Multi value , multi column problem : This problem is happens when database table is used multiple columns to store values of an attribute.

example: STUDENT(StudentNumber, StudentName, SiblingName1,SibilingName2,SibilingName3, Major)

in above schema siblingName is an attribute and it has multiple values and those values are placed in different columns ,this situation leads to multivalue ,multicolumn problem

this is a problem beacause:

1. if students have more than three siblings then schema is not supports to insert all sibling names
2. accessing data with this type of tables using query is difficult   

Multivalued dependency :

if table contains three columns like p,l,n and l,n are independant on each other and both are dependent on p then multivalued dependency occurs

example:STUDENT(StudentNumber, StudentName, SiblingName, course)

in above table StudentNumber is decides both siblingname and course and course is independent to siblingname

studentNumber->->SiblingName

StudentNumber->->course

B)

Student Number	Studentname	Siblingname	Major
101	Harry	Sony	Finance
101	Harry	Stephen	Finance
101	Harry	Karl	Finance
102	Measly	Maximus	Accounting
102	Measly	Jupiter	Accounting

C) candidate keys in above relation:

Candidatekeys:(StudentNumber,StudentName)

above student name is taken as not unique value

D)functional dependencies:

here studentNumber is defines studentname and major of student ,

1)StudentNumber->(StudentName,Major)

and here sibling name+ studentNumber can define studentName and major

2)(StudentNumber,SiblingName)->(StudentName,Major)

E)reason of not meet relational criteria :

parts of primary key like studentNumber is defines the StudentName and Major

according to relational criteria it's not allowed to functional dependency with part of primary composite key

F)4th normal form:

table to be in 4NF it should be satisfy 1NF ,2NF,3NF and boyce_codd NF

and there should not contain multivalued dependencies more than one

G) dividing table:

break student table into 2 tables

1)Student(StudentNumber,StudentName,Major)

functional dependencies: StudentNumber->(StudentName,Major)

  candidate key : StudentNumber

normalization is made here in the way every attribute is dependent on candidate key

2)Student_Sibling(StudentNumber ,SiblingName)

functional Dependency : StudentNumber->SiblingName

candidate key: StudentNumber

normalization is made here in the way every attribute is dependent on candidate key

Note: pl