Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I created a relation called Customer and defined as:

enter image description here

I just wanted to know if it is in 3NF?

My concern is regarding address, since customer may have the same address.

share|improve this question

migrated from stackoverflow.com Mar 8 '13 at 12:43

This question came from our site for professional and enthusiast programmers.

2  
read your text book carefully. How can we know its nominal form if you show only 1 table ? –  Raptor Mar 8 '13 at 9:18
    
Have you looked at Wiki? Also, it's not a good idea to keep the whole name in one field and the whole address in one field. –  Westie Mar 8 '13 at 9:18
1  
This particular table is not in a 3NF, because values in your address column are separatable (city + country). It violates 2NF. –  user1315357 Mar 8 '13 at 9:21
2  
Separate values from address column into two tables (e.g. cities and countries), in table customer create two new columns for city_id and country_id and link these columns via FKs to new tables. –  user1315357 Mar 8 '13 at 9:31
1  
I would add that it can be quite valid to string an entire address in a single string as long as the elements of that string are not considered distinct for query/reporting purposes. In this case each field includes only one value, as is the case here. It isn't necessarily a normalization violation to do what you have done provided that the country of the customer is not important as anything other than a part of the address. –  Chris Travers Mar 10 '13 at 5:37
show 3 more comments

2 Answers 2

In the end whether the table is 1NF and 3NF depends on what the domain value of a city is. This answers whether or not you have atomicity.

In essence what normalization means in the abstract regarding address information is quite controversial and it is not an easy area to answer and so I personally believe that this must be addressed on a case by case basis, based on the semantic use of the data.

Otherwise consider the following addresses:

418 N Bradley St
Chelan, WA 98816

How many atomic fields would this require with maximum decomposition? So we consider this to be:

CREATE TABLE address (
    id int not null unique,
    street_segment char(1),
    street_name varchar not null,
    city_name varchar,
    state_province varchar,
    mail_code
);

That seems straight forward enough until you get an address that requires a cross-street as is common in South America, then we start to add fields. But now you have to track an address in Managua like:

Bo Altagracia km 3 1/2 carretera sur | Montoya 3c al Oe

Note that this is an ordinal address based on landmarks, distance and direction, and it does not fit into this data model at all and a normalized representation would require at least one additional table and some additional fields, and we can no longer enforce the same not null constraints.

But then this doesn't necessarily mean there is anything breaking 1NF if we just store the address as a text string up through and including the country.

If you have no reason to track countries, then one may see "London, England" as a valid representation of a city designation and then yes, we could see this as 1NF. In essence such a table has a city domain (where "London, England" is distinct from "London, UK" and "Paris, France" is distinct from "Paris, North Carolina"). This gives you a number of possible issues, but it does not pose classic normalization problems unless you need to track countries (here the country is just an incidental part of the city's domain value, not a domain in itself).

So in this case it may or may not be 1NF depending on how you define the domain of the address to be and what you have to track it relative to. If it is 1NF then it is also 3NF.

However if you break city and country into separate fields, then you have a normalization problem because city is dependent on country, and so to be truly 1NF you'd probably need to break out your regional hierarchies quite a bit more (a cities table, a state/province table, a country table).

A typical approach to this is exactly what you have done, which is to treat the address as a text string atomic domain, and not break things out any further.

Edit: Another example of 1NF problems using arrays

Suppose on PostgreSQL I am storing IP addresses and will be querying on octets. I might represent an IP address as a smallint[] array like: array[192,168,1,101,24] instead of a cidr representation like '192.168.1.101/24' and this does not break 1NF. Each array of smallints is distinct in its domain and each represents a single value of its domain. This does not break 1NF because in the domain of an IP address, each array represents a single value in its domain (and this is ensured by the fact that ordinality is important). This is a good example of why it is wrong to assume, for example, that the inclusion of complex data structures or arrays necessarily violates 1NF.

Finally if "this datatype can be decomposed and therefore it isn't atomic" breaks 1NF then so does every use of a datetime datatype....

TL;DR

1NF's atomicity requirement is violated only when the column stores two or more values within a given domain. That is not the case in the initial question so 1NF is not violated given the information shown. Given that 1NF is not violated and both name and address are functions of customerid, and not functions of eachother, the requirements of 3NF are met.

share|improve this answer
add comment

No, it is not even 1NF. 1NF is each cell in a table must contain only one piece of information, and there can be no duplicate rows. 2NF and 3NF are all about being dependent on the primary key

Keep address details in separate table.

cust_id  name           c_id
200 johnsmith   101




city country cid
London uk   101
share|improve this answer
    
There is no duplicate rows, customer_Id is different. –  Sam Mar 8 '13 at 9:23
    
In Address field they are duplicate values are there. –  divyabharathi Mar 8 '13 at 9:29
4  
But addresses can be duplicated, hence they probably should be in separate table. It is not about ROW duplicates, but piece-of-INFORMATION duplicates. –  quetzalcoatl Mar 8 '13 at 9:29
    
Yes, John Smith and John Terry probably have the same description of an address but not logically the same address. normalisation isn't simply about finding duplicates. –  JamesRyan Mar 8 '13 at 16:08
2  
No, it doesn't necessarily break 1NF. The question is what a city domain represents, and whether there is a country domain. If no country domain, no 1NF violation. –  Chris Travers Mar 10 '13 at 5:29
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.