claudio
Note: I use a dual terminology(or even triple considering Database Design theory) when I am talking about Databases and their elements.
 
In Relational Model terminology
  1. Relation
  2. Tuple
  3. Attribute

In SQL terminology

  1. Table            
  2. Row
  3. Column

 

Equally numbered terms have the same meaning under different perspectives.

I may use the terms belonging to the different perspectives interchangeably whenever that does not lead into confusion.
 
 
First Normal Form:
 
Loosely speaking First Normal Form says that each attribute of a Relation must contain one and only one value of the domain.
 
What I would like to convey with this article is that a Relation is by definition in First Normal Form(1NF), while the SQL Table that represents the 'real world' instance(or, a lower level instance) of that Relation may not be in 1NF.
 
Q: Why by definition?
A: Because...
 
When we design a Relation(variable - not value) we must(should) define the meaning of the relation and the meaning of its elements, the tuples and attributes.
After we define what is the meaning of the attribute of each tuple, only at that point we can tell if the values we assign to that attribute are 'compatible'.
Relations are represented as SQL Tables of Relational Database Management Systems in real life applications, that means application query a RDBMS and not a Relational Model.
In a system with strong typing(like Java) it's more difficult(if not impossible) to force values into a variable that are not compatible with the abstract data type associated with that variable. A RDBMS uses a few fundamental types to represent any information we want to manage, for this reason it's easier to force conceptually invalid values. 
 
For Example, a table BorrowedBook that is defined in human language as 'the books that people have borrowed' and defined in Relational Model as the set of tuples representing each 'A book borrowed by one person', and having each tuple two attributes, the PersonName and the BookTitle would look like this in tabular representation:
 
PersonName      | BookTitle
==================================
Claudio         | Baking Bread Secrets
Claudio         | The Godfather
Kolbe           | Into The Wild
Hartmut         | Cats & Keyboards
Hartmut         | Lenovo Notebooks Service&Repair Manual
 
 
Now this Table is in 1NF by definition because we conceived it to have each row (tuple) to have one Person, and one and only one Book.
 
What should we do for the table above not to be in 1NF?
 
Here is an example:
 
PersonName      | BookTitle
==================================
Claudio         | Baking Bread Secrets,The Godfather
Kolbe           | Into The Wild
Hartmut         | Cats & Keyboards,Lenovo Notebooks Service&Repair Manual
 
We 'humans' still understand the meaning of the above Table, but now we have that each row has one Person, but a list of Books for each person, this is now not in 1NF anymore according to our definition of the Relation represented by this Table (We may even say that this Table does not represent anymore our Relation).
 
You can think of First Normal Form(1NF) as a high level check of conceptual integrity.
 
Imagine to (try to) join the above Table with another Table(BookPublisher for instance) on the BookTitle attribute, that's simply not possible.
 
Please note then that this 'check of conceptual integrity' is defined at Relational Model level, while at SQL level this table is still containing valid strings(which is the SQL type used for the Book attribute) 'Baking Bread Secrets' is no more correct string than 'Baking Bread Secrets,The Godfather'.
SQL does not have strong typing, so we cannot easily check the adherence of the values to the conceptual meaning of the attribute.
 
Q: Is it possible for the Table above to be in 1NF?
A: Yes, by defining it accordingly.
 
Changing the definition of the Relation (which the above Table should be a faithful representation!),
 
From: A set of tuples representing each 'A book borrowed by one person'
 
To: A set of tuples representing each 'A list of books borrowed by one person'
 
Now, while the basic data type used by SQL is still a String the concept of the attribute has changed.
 
What little lesson we can take from all this?
 
By looking at the Table instance (Relvalue in Relational Model terminology) we have no means to determine if a table is in 1NF or not.
 
If we can sum up with a sentence: For a SQL Table, not being in First Normal Form means misinterpreting(or violating) the definition of the Relation represented by that Table.
 
 
Tags: 

Newsletter Signup

Subscribe to get MariaDB tips, tricks and news updates in your inbox: