Consider the table above. Identify the tables in their current normal forms (1,
ID: 3749103 • Letter: C
Question
Consider the table above.
Identify the tables in their current normal forms (1, 2, 3 or no forms).
Follow the stepwise process an normalize the table and create a relations between the tables.
You are only responsible up to 3NF. Show step by step process.
Create dependency diagrams
Use conventional methods (all caps) to write the table name, primary key/s, functional
dependencies, partial dependency and transitive dependency.
Purpose: Comprehend the normalization concept and implement in designing database.
Explanation / Answer
Summary of Definitions of the Normal Forms
1st Normal Form (1NF)
Definition: A table (relation) is in 1NF if
1. There are no duplicated rows in the table.
2. Each cell is single-valued (i.e., there are no repeating groups or arrays).
3. Entries in a column (attribute, field) are of the same kind.
Note: The order of the rows is immaterial; the order of the columns is immaterial.
Note: The requirement that there be no duplicated rows in the table means that the table has a key (although the key might be made up of more than one column--even, possibly, of all the columns).
2nd Normal Form (2NF)
Definition: A table is in 2NF if it is in 1NF and if all non-key attributes are dependent on all of the key.
Note: Since a partial dependency occurs when a non-key attribute is dependent on only a part of the (composite) key, the definition of 2NF is sometimes phrased as, "A table is in 2NF if it is in 1NF and if it has no partial dependencies."
3rd Normal Form (3NF)
Definition: A table is in 3NF if it is in 2NF and if it has no transitive dependencies.
Boyce-Codd Normal Form (BCNF)
Definition: A table is in BCNF if it is in 3NF and if every determinant is a candidate key.
4th Normal Form (4NF)
Definition: A table is in 4NF if it is in BCNF and if it has no multi-valued dependencies.
5th Normal Form (5NF)
Definition: A table is in 5NF, also called "Projection-Join Normal Form" (PJNF), if it is in 4NF and if every join dependency in the table is a consequence of the candidate keys of the table.
Domain-Key Normal Form (DKNF)
Definition: A table is in DKNF if every constraint on the table is a logical consequence of the definition of keys and domains.
Section 3. Functional Dependency and Determinants
Before we develop the ideas of normalization further, it is important for you to have an understanding of "functional dependency." The essence of this idea is that if the existence of something, call it A, implies that B must exist and have a certain value, then we say that "B is functionally dependent on A." We also often express this idea by saying that "A determines B," or that "B is a function of A," or that "A functionally governs B." Often, the notions of functionality and functional dependency are expressed briefly by the statement, "If A, then B." It is important to note that the value B must be unique for a given value of A, i.e., any given value of A must imply just one and only one value of B, in order for the relationship to qualify for the name "function." (However, this does not necessarily prevent different values of A from implying the same value of B.)
For the terminology of relational databases, the word "function" was borrowed from mathematics, where it is common to say things like "y is a function of x" or "y = f(x)". (The latter expression is read "y equals f of x".) The determiningvalue, x, is called the argument; the determined value, y or f(x), is called the result.
The expression "y = f(x)" is a very general, and abstract, way of talking about functionality. Outside of mathematics--and, in particular, ordinarily in relational database management--we talk not abstractly but in terms of particular examples. (Indeed, the general idea of a "function" is best understood when one has seen enough examples of specific functions to be able to start generalizing about the abstract, or general, properties that the specific functions share.)
Here are some examples of functions. An easy one is y = x2. This particular function says that if we are given a particular value for x, say 3, then we must say that y has the value 9. (We could also write y = f(x) = x2 or just f(x) = x2.) Another easy one is: y = x3. This particular function says that if we are given a particular value for x, say -2, then we must say that y has the value -8.
A common way of indicating functions is to place the determining and determined values side by side in a table. Thus we can place sample values of the function, y = x2, in a table like the one shown here.
3
9
4
16
-3
9
This table shows just three of the infinity of possible pairs of values, x and y, for the function y = x2. It also shows that for some functions, different values of x (here, 3 and -3) imply the same value (here, 9) of the function.
The functions we have given as examples so far have been functions that are specified by an algebraic function. But the idea of function is more general; i.e., functions need not be algebraically defined. The essence of the idea of function is that to a specified determining value corresponds a unique determined value. This essence can be defined, among other ways, by placing the determining and determined values in a table that displays and/or defines the relationship between the argument and the result.
Note that the table above displays, but does not fully define, the relationship, y = x2. This function, since it has an infinite number of pairs of values, cannot be fully defined in a table. For functions that involve only a finite number of pairs of values of argument and result, a table is often a convenient way--and may in fact be the only way--of displaying and, at the same time, defining the function.
Here is a simple example of a finite function that is both displayed and defined in a table. Most of you will be familiar with the conventional (though often delightfully breakable) rules for serving different types of wines with different courses in a dinner. Let us assume for the purpose of this example that these rules can be summarized as follows: with meat, serve red wine; with fish, white wine; and with cheese, rosé wine. Then the following table defines the course-wine function:
But note that this table looks just like a database table. In fact, there is no reason not to consider it a database table. Indeed, this table defines a relation in the database sense: it has columns, each of which contains entries of the same kind, and it has no duplicate rows. In other words, not only does the course-wine table display the data about the conventional rules for which wine to serve with which course, but also the table can be viewed as defining a function for which the determining value is the dinner course and the determined value is the type of wine. Thus we can say that type of wine is functionally dependent on the dinner course, or equally well, that the course determines the wine.
In relational database terminology, we often call the argument of the function (the dinner course in this example) the "determinant", and we often use an arrow notation to exhibit the functional dependency. Thus, we can say that the dinner course is the determinant of the type of wine, and we can write: dinner course wine. And we can say that the attribute, type of wine, is functionally dependent on the attribute, dinner course.
In general, a functional dependency is a relationship among attributes. In relational databases, we can have a determinant that governs one other attribute or several other attributes. To go back to our mathematical examples for a moment, we could view the situation of functional dependency of several attributes on one determinant as being like having several linked functions that share an argument and can be displayed economically in just one table. For example, consider the following table that displays sample values of the algebraic functions y = x2, y = x3, and y = x4.
3
9
27
81
4
16
64
256
-3
9
-27
81
Looking at this table from the relational-database point of view, we can say that the attributes x2, x3, and x4 are all functionally dependent on the attribute x.
Similarly, we could expand the dinner-course and wine table to exhibit also the type of cutlery that would be appropriate in the case of a formal dinner.
From this table we see that the attributes, type of wine and type of cutlery, are functionally dependent on the attribute, dinner course.
Using the arrow notation, we have:
dinner course wine
and
dinner course cutlery.
Value of x ("argument," or "A") Value of y = x2 ("the function," or "the result", or "B")3
9
4
16
-3
9