9

In a lecture, my lecturer showed us a table without a primary key. Upon questioning, he said that in 3NF when you remove transitive dependencies it's ok to then have a table without a primary key.

However, no primary key implies there's no functional dependencies - but 3NF is the removal of transitive dependencies, and I was taught that each table needs to have a primary key for normalization as it's all about functional dependencies.

I know it's fully possible to create a table without a primary key, but is that database considered normalized if that table exists?

I should add, the table doesn't have any “unique key”, no primary, no composite, no foreign.

The table shown has three attributes with none of them labeled as primary or unique. I asked if it was a mistake and he said it's fine to not have one. I questioned the remark as none of the information in the table can be uniquely identified and he claimed it's ok to be like this. This goes against what I was taught about normalisation.

15

If a relation does not have any candidate key (and the primary key is just one of the candidate keys), then it can have duplicate rows, so in fact it is not a relation! (since relations are always sets).

In that case, it is more correct to call it a table, not a relation, as you did in your question, and note that several RDBMS in effect can manage not-relations, allowing tables without any constraint of uniqueness, even if this case is very uncommon, and produces problem (anomalies) when operating on the data.

But in this case talking about normal forms is not appropriate: all the normalization theory is based on the fundamental assumption that the objects of interest are relations, not multisets. In fact this theory is based on the (somewhat discussed) Universal Relation Assumption, that assumes that all the relations of a database are a subset of a projection of such relation, that contains all the attributes in every relation. And that object is in fact a relation (i.e. a set), not a multiset.

The fact that, when talking about data of the Relational Data Model, sometimes we exchange the two terms, tables and relations, does not means that they are actually synonym, and this difference is fundamental when we talk about normalization theory. Remember that in the books, when some normal form is introduced, it is always said something like:

A relation is in xxx normal form if...

  • Isn't a multiset isomorphic to a set where you add an additional count attribute to the elements? – Barmar Feb 8 at 16:25
  • 3
    @Barmar yes, but then you add a candidate key and all the relevant functional dependencies, so from the normalization theory point of view you are in a completely different case. – Renzo Feb 8 at 16:59
  • 1
    When people carry out relational operations on non-relational tables, they expect relational results. When they don't get them, these appear anomalous. – Walter Mitty Feb 11 at 15:31
0

3NF refers to a table compliant with the first 3 Rules of Data Normalization.

  1. Elimination of repeating groups
  2. Elimination of redundant data
  3. Elimination of columns not dependent on key

The third rule requires a key. You are right, the lecturer's table was not 3NF.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.