CODEWITHSUNDEEP
sqldatabasedatabase-design
TechTubeCentral
TechTubeCentral

Reputation: 27

Database Schema Normalization Violation

To my knowledge the following doesn't violate 1NF or 2NF. Is 3NF violated because LocationID and LocationName isn't separated into a different table?

Department (DNum PK, DName, LocationID, LocationName)

Upvotes: 1

Views: 303

Answers (2)

nvogel
nvogel

Reputation: 25526

You can't be sure whether a relation is normalized or not based only on a list of attribute names. What really matter are the dependencies that apply to those attributes. For example, given the following set of dependencies

F: {DNum}->{LocationID}->{DNum,DName,LocationName}

we can say that Department satisfies 3NF (and therefore 2NF) with respect to F because both DNum and LocationID should be keys for Department and there are no functional dependencies other than the ones implied by those keys. The choice of primary key is irrelevant as far as normalization is concerned because a relation may have more than one key and all the keys are equally significant.

Alternatively, given the following set of dependencies

G: {DNum}->{DName,LocationID,LocationName}, {LocationID}->{LocationName}

the Department relation violates 3NF with respect to G because LocationID is a non-key determinant.

Upvotes: 2

displayName
displayName

Reputation: 14379

Yes, 3NF is violated.

3NF requires 2NF and that no non-key field should depend on another non-key field.

LocationName is dependent on LocationID as (I'm assuming that) LocationID is a PK and that's the violation.

I earlier explained 1NF, 2NF and 3NF in this another answer.

Upvotes: 1

Related Questions