1

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)

TechTubeCentral
  • 27
  • 5

2 Answers2

2

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.

nvogel
  • 24,981
  • 1
  • 44
  • 82
1

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.

Community
  • 1
  • 1
displayName
  • 13,888
  • 8
  • 60
  • 75
  • 2
    +1 the only exception is if each Department has its own name for its location. Like if locations are subject to change, but each Department knows its own location as the name that was current when it the Department was created. Similar problem to recording product prices per order in an e-commerce system. Prices can change, so each Order needs to record the price as of the time the Order was placed. – Bill Karwin Mar 10 '17 at 20:30
  • @BillKarwin: That's a good exception case that you have pointed out. +1 for that. – displayName Mar 10 '17 at 20:33
  • So If I read your other answer correctly, the following would be a 2NF violation as there is no unique index? "Customer (CustName, CustName_Address, Description)" – TechTubeCentral Mar 10 '17 at 20:34
  • @TechTubeCentral: You mean there is no PK in Customer table. In that case yes, there is a violation of 2NF as well. – displayName Mar 10 '17 at 20:41
  • 1
    @TechTubeCentral, Nothing about normal forms requires that the primary key is an integer. If CustName is unique and is the primary key of the table, then your example Customer table satisfies 2NF and 3NF. – Bill Karwin Mar 11 '17 at 00:18