databasedatabase-normalizationfunctional-dependencies# 2nd normal form violation with lhs of the dependency is composite (prime and non-prime together)

I was studying functional dependencies and normalization and I've come across a question. The original question is below:

"Given the relation R = {v,w,x,y,z} and functional dependency set {v->w,y->z,yz->v,wx->z} find BCNF composition and check if dependency preservation holds."

First I tried to find minimal cover and came up with this:

**Minimal Cover:**

v -> w

y -> z

y -> v

wx -> z

Then I tried to found candidate keys, came up with only one candidate key:

**Candidate Keys:**

xy

Then I started to check normal forms:

**1st Normal Form:** check

**2nd Normal Form:**

I thought the below dependencies are violating 2nd normal form:

**1)** y -> z

**2)** y -> v

**3)** wx -> z

The first two were easy to solve. However, I've never seen an example of the 3rd where the left-hand side is a composite of prime and non-prime attributes. How do we solve this kind of situation? Do we make a new relation for the 3rd making w and x primary key?

If I solve that part, the 3rd and BC normal forms will be easy I guess.

Solution

Whether one considers a FD (functional dependency) to "violate 2NF" depends on one's definition of 2NF. A common definition of 2NF is, no FDs hold where a non-prime attribute is partially functionally dependent on a CK (candidate key). So are the violating FDs the ones where a non-prime attribute is partially functionally dependent on a CK? Or the ones where a non-prime attribute is functionally dependent on a proper subset of a CK, by which the preceding FDs are partial? Or both? And/or others? Or what? The fact is that it isn't individual FDs that violate NFs but the set of all FDs that hold. If you want to talk about individual FDs violating then you need to give a definition for 2NF & then give & justify a definition of violating FD based on how the definition talks about such FDs.

The following uses the 2NF definition above & talks about "bad" FDs explicitly disallowed by that definition, where a non-prime attribute is partially functionally dependent on a CK.

Those three FDs are not bad. A FD is partial when its right hand side is functionally determined by a proper/smaller subset of its left hand side. None of those three FDs are partial dependencies on a CK (candidate key). None of them are even partial, because none has a right hand side that is determined by a subset of the left hand side (determinant). And none of them are even on a CK, because none of them have a CK as their left hand side.

You might consider the first two to "violate 2NF" per a 2NF definition that there are no FDs with left side a proper subset of a CK & right side a non-prime attribute. That definition explicitly disallows those FDs. So we do not have 2NF.

However the FDs xy->z & xy->v are partial, because proper/smaller subsets of xy determine z & v. And they are bad: xy is a CK and Z & v are non-prime attributes so both have a non-prime attribute partially dependent on a CK. So we do not have 2NF.

wx->z isn't bad. And it doesn't "violate 2NF" per a 2NF definition that there are no FDs with left side a proper subset of a CK & right side a non-prime attribute.

It doesn't matter whether "the left-hand side is a composite of prime and non-prime attributes". What matters is what is mentioned in your definitions. (It happens that you will never see such "an example" of a bad or "violating" FD. Because both those require left-hand sides with only CK attributes.)

Read some academic definitions for partial FD & 2NF. (Many textbooks/presentations/courses are free online.) Memorize and apply definitions, theorems and algorithms exactly. You seem to not understand numerous things:

*Being in BCNF*implies being in all lower NFs.*Getting to BCNF*does not require going through lower NFs.*Examples of decompositions*you have seen are not*presentations of decomposition algorithms*.- We don't normalize via successive NFs. We use an algorithm for the NF we want. (Going through lower NFs can even mean good higher-NF designs become unavailable.)
- When some FDs hold, all the ones implied by them by Armstrong's axioms also hold.
- To determine CKs & NFs it's not enough to know that some FDs hold, we need to know what FDs hold & what FDs don't hold. You need to know a
*closure*or*cover*of FDs. - Each time we decompose we get new relations & sets of FDs & CKs for each.
- The FDs that hold in a component are all those of the original whose attributes are in it. (Those of a closure, not just those of a cover.)
- A FD is partial when its right hand side is functionally determined by a proper/smaller subset of its left hand side.
- A common 2NF definition explicitly disallows partial FDs of non-prime attributes on CKs.
- "Violating FD" is not a helpful term, refer to the things that definitions mention.

- Why can't npx prisma db push find my prisma schema?
- Can't export my database from mysql workbench
- The type or namespace name 'SQLConnection' could not be found
- Best representation of an ordered list in a database?
- data restored from mongodb dumb throwing bson error on _id field
- What happens if a int column reach the max value?
- find student numbers of students who take all courses in department 'CS'
- Views Creation Permissions without table Creation Permissions in POSTGRESQL
- Is there a way to update 2 values each time calculating data on each row?
- Unable to retrieve data from existing database
- Mongodb connection failed using mongoclient in nodejs
- When to add a column vs adding a related table?
- Opposite of MySQL FIND_IN_SET
- Make a table in MySQL based on specific values of two other tables?
- KDB table sharding based on column values
- nestjs pgsql typeorm ON CONFLICT DO UPDATE command cannot affect row a second time
- Any DB/Schema design critique / assistance tool
- FInding multiple time reportings across columns
- Atomically reorder huge list of documents in firestore
- How to synchronize the data received from Apache IoTDB to be real-time when using pipe in Apache IoTDB?
- pgadmin error when restoring database from tar file
- How to get item count from DynamoDB?
- AWS: can't connect to RDS database from my machine
- How can I assign multiple Model in One Foreign Key Model as a Parent Model [ database design ]
- Reference Existing fields in Update Query MongoDB
- How can i debug Anylogic SQL database TLS error?
- Store employee info in mongo DB for entire country(considering more than 50 millions records)
- Enable binary mode while restoring a Database from an SQL dump
- Order of B+ Tree
- Always get a RowExclusiveLock on table pg_proc When I create a Function,why?