Understanding Database Normalization - Second Normal Form(2NF)

I quote from "Fundamentals of Database Systems" by Elmasri and Navathe (6th edition).

An example under 2NF:

The candidate key is {SSN, Pnumber}. The dependencies are SSN, Pnumber -> hours, SSN -> ename, pnumber -> pname and pnumber -> plocation.

A relation schema R is in 2NF if every nonprime attribute A in R is fully functionally dependent on the primary key of R.

In the above picture, if I define an additional functional dependency SSN -> hours, then, taking the two functional dependencies {SSN,Pnumber} -> hours and SSN -> hours, the relation won't be in 2NF, because SSN -> hours is now a partial functional dependency, as SSN is a proper subset for the given candidate key {SSN, Pnumber}.

Looking at the relation and the definition on 2NF, the above relation is in 2NF.

So a relation is in 2NF if one cannot find a proper subset (prime attributes) of the left hand side (candidate key) of a functional dependency which defines a non-prime attribute.

1

Why does the textbook consider the relation above to be not in 2NF?

There is, however, an informal way (steps per the textbook that a normal person not knowing normalization can take to reduce redundancy) being defined at the beginning of this chapter:

• Making sure that the semantics of the attributes is clear in the schema
• Reducing the redundant information in tuples
• Reducing the NULL values in tuples
• Disallowing the possibility of generating spurious tuples

The guidelines mentioned:

2

Using the above steps, and considering why the following relation is not in 2NF, is it the functional dependencies

{SSN,Pnumber} -> Pname
{SSN,Pnumber} -> Plocation
{SSN,Pnumber} -> Ename

that make the decomposition of the relation correct?

If the functional dependencies assumed are incorrect, then why does the relation to not satisfy 2NF?

When looked at a general point of view ... because the table contains more than one primary attributes and the information stored is concerned with both employee and project information, one can point out that those need to be separated, as Pnumber is a primary attribute of the composite key, the redundancy can somehow be intuitively guessed. This is because the semantics of the attributes are known to us.

what if the attributes were replaced with A,B,C,D,E,F

3

Are functional dependencies pre-determined based on "functionalities of database and a database designer having domain knowledge of the attributes"?

Because based on the data and relation state at a given point the functional dependencies can change. What was valid in one state can go invalid in another state. In general this can be said for any non-primary attribute determining a non-primary attribute.

A functional dependency, denoted by X → Y, between two sets of attributes X and Y that are subsets of R specifies a constraint on the possible tuples that can form a relation state r of R. The constraint is that, for any two tuples t1 and t2 in r that have t1[X] = t2[X], they must also have t1[Y] = t2[Y].

So won't predefining a functional dependency be wrong, as one cannot generalize the relation state at any given point?

Why is the above relation not in 2NF?

Your original/first/informal "definition" of 2NF is garbled and not helpful. Even the quote from the textbook is wrong since 2NF is not defined in terms of "the PK (primary key)" but rather in terms of all the CKs (candidate keys). (Their definition makes sense if there is only one CK.)

A table is in 2NF when there are no partial dependencies of non-prime attributes on CKs. Ie when no determinant of a non-prime attribute is a proper/smaller subset of a CK. Ie when every non-prime attribute is fully functionally dependent on every CK.

Here the only CK is {Ssn, Pnumber}. But there are FDs (functional dependencies) out of {Ssn} and {Pnumber}, both of which are smaller subsets of the CK. So the original table is not in 2NF.

If the above statement is taken into account, do you assume the following functional dependencies

so won't the same process of the decomposition shown based on the informal way alone be difficult each time such a case arrives?

A table holds the rows that make some predicate (statement template parameterized by column names) into a true proposition (statement). Given the business rules, only certain business situations can arise. Then given the table predicates, which give table values from a business situation, only certain database values can arise. That leads to certain tables having certain FDs.

However, given some FDs that hold, we can formally use Armstrong's axioms to get all other FDs that must also hold. So we can use both informal and formal ways to find which FDs hold and don't hold.

There are also shorthand rules that follow from the axioms. Eg if a set of attributes has a different subrow value in each tuple then so does every superset of it. Eg if a FD holds then every superset of its determinant determines every subset of its determined set. Eg every superset of a superkey is a superkey & no proper subset of a CK is a CK. There are also algorithms.

Are functional dependencies pre-determined based on "functionalities of database and a database designer having domain knowledge of the attributes" ?

When normalizing we are concerned with the FDs that hold no matter what the business situation is, ie what the database state is. Each table for each business can have its own particular FDs per the table predicate & the possible business situations.

PS Do "make sense" of formal things in terms of the real world when their definitions are in terms of the real world. Eg applying a predicate to all possible situations to get all possible table values. But once you have the necessary formal information, only use formal definitions and procedures. Eg determining that a FD holds for a table because it holds in every possible table value.

so would any general table be in 2NF based on a solo condition of a table having a composite primary key?

There are tables in 5NF (hence too all lower NFs) with all sorts of mixes of composite & non-composite CKs. PKs don't matter.

It is frequently wrongly said that having no composite CKs guarantees 2NF. A table without composite keys and where {} does not determine any attribute is in 2NF. But if {} determines an attribute then it's a proper/smaller subset of any/every CK with any attributes. {} determines an attribute when every row has to have the same value for that attribute.