Assessing the normal form for a relation with a multi-valued attribute

database-designdependenciesnormalizationrelational-theory

Assume that you have designed the following schema of relations:

COURSES (CID, CNAME)
STUDENTS(SID, SNAME, FAV_CIDS)

However, you can not have a relation with set valued attributes (Did not really understand what was meant by this) and you
need to convert it to a normal form. What is the normal form and the resulting schema?

We are studying SQL at school. Could not really make out which normal form this was in and functional dependencies.

Best Answer

You first need to know what's the relationship between those two entities: Courses and Students. But, you also need to know the abstraction of those entities into your database schema; we all know that a Student could be enrolled in many Courses, and a Course could contain many Students. The relationship between them are many-to-many.

The way to enforce a many-to-many relationship is by creating a table apart that will store as Foreign Keys the Students, and Courses Primary Keys. Both foreign keys will compound the table's primary key. This will gives to you the flexibility to store many courses, and many students.

However, check below how the tables should look like:

Courses(CourseId (PK), CourseName)
Students(StudentId (PK), StudentName)
StudentsCourses(StudentId (FK)(PK), CourseId (FK)(PK))

Related Solutions

Database Normalization – Does 3NF Require 2NF?

The normal forms are specified in such a way that for any normal form, the model is also meets the criteria for the lower numbered normal forms. So, yes if your model is in 3rd normal form, it is also in 2nd normal form and 1st normal form.

When normalizing a model, you normally work at resolving one normal form before the next. This makes normalization simpler and less error prone as you are only applying a few rules at at time. It is possible that an entity at a lower normal form is also at a higher normal form. Most 3rd normal form entities are also in 4th and 5th normal form.

4th, 5th, and 6th normal forms go beyond the functional dependence. Where applicable, they further simplify the data, at the expense of more tables. They reduce redundancy, and increase data integrity.

Normalization – Proving or Disproving Dependencies in A ? BC and A ? B then A ? C

A,B and C are sets of attributes.

All examples I've seen in books are like Employee ↠ Project | Dependent, where Employee simultaneously determines two attributes.

A ↠ B | C is a special notation different from A ↠ BC. It's useful because MVDs always come in pairs. It says that both A ↠ B and A ↠ C hold. This implies that the relation equals AB JOIN AC. Ie the relation satisfies join dependency *{AB,AC}.

What does it means to have a multi valued dependency on two attributes like A ↠ BC?

Please give an example of such a dependency.

Just think of values for subrows the way you would think of values for one attribute. Suppose {W} ↠ {X}. Then whenever W appears it's with all of some set of X values. Suppose each X has a numerator N and denominator D. Then we could have a relation {W} ↠ {N,D}. Whenever W appears it's with all of some set of < N, D > subrow values.

Please help me prove/disprove option C.

C) If A ↠ BC and A → B then A → C. Here using Coalescence rule I'm getting A->B back. I'm not able to prove/disprove A → C.

Hint: In my last paragraph if {W} → {N} then the variation must come from D.

Hint: If you are permitted to prove by counterexample then it's good to try to disprove things via guessing at one or more trivial examples. Eg with {W} ↠ {N,D} and {W} → {N} (A={W}, B={N}, C={D}):

  V   W   N   D
=================
| v | w | n | 1 |
-----------------
| v | w | n | 2 |
-----------------

( How do I apply this definition of multivalued dependency (MVD) to this example relation? )

Best Answer

Related Solutions

Database Normalization – Does 3NF Require 2NF?

Normalization – Proving or Disproving Dependencies in A ? BC and A ? B then A ? C

Related Question