CODEWITHSUNDEEP
databasedatabase-designdatabase-normalization
query
query

Reputation: 529

why this table is not in 1st normal form

Suppose i have given the following table and i am trying to determine it is in 1NF or not.

Table:
Mem_id mem_name phone email Book_num Book_titl Author_nam publication edition
100     smith    12443 eml1    200     physics   john         MACGROW   1ST
                               201     math      martin       MACGROW   2ND
101     hena     1020  eml2    200     physics   john         MACGROW   2ND 
102     moon     2020  eml3    204     new java  korth        ALPHA     4TH

I have applied following rules and it all satisfied then why the table is not in 1NF ?

My applied conditions:

1.values of each attribute is atomic/ Only one value per column
2.no two rows are identical 
3.every column has unique name
4.all entries in any column must be same type

Upvotes: 0

Views: 2535

Answers (3)

JacquesB
JacquesB

Reputation: 42649

It really depends on how the blank fields in the second row are supposed to be interpreted. If this is from an exercise, I would say it is quite ambiguous without further context.

If the blank fields are supposed to represent nulls or empty strings in a distinct database row, then the table is indeed in 1NF. But if the layout is supposed to represent a table containing a nested table (also known as a repeating group), then it is not in 1NF. Such "repeating" groups were allowed in hierarchical databases, but it not allowed or supported in relational databases.

Given mem_id is probably intended to be the primary key, I would guess it is intended to represent a nested table, and therefore not in 1NF.

To bring this into 1NF, you will have to split it up into two tables and connect via a foreign key:

Mem_id mem_name phone  email 
100     smith    12443 eml1           
101     hena     1020  eml2    
102     moon     2020  eml3    

Book_num Book_titl Author_nam publication edition Mem_id 
200      physics   john         MACGROW   1ST     100
201      math      martin       MACGROW   2ND     100
200      physics   john         MACGROW   2ND     101
204      new java  korth        ALPHA     4TH     102

Note I added a foreign key column Mem_id in the second table.

By the way, only condition 1 in your list is about 1NF. The conditions 2-4 are the definition of a relation, so would also apply to a relation which is not in 1NF.

Upvotes: 1

nvogel
nvogel

Reputation: 25526

First Normal Form is concerned with the data structures, not the data itself. Based on four sample records we can't tell you whether your table satisfies 1NF or not.

Does your table have a key, named and typed attributes, permit exactly one value per attribute in each tuple, no nulls or other "special" data, no column ordering or tuple ordering? If yes to all those things then it qualifies as a proper relational table and meets the requirements of 1NF.

Upvotes: 3

Renzo
Renzo

Reputation: 27424

The table is in 1NF, since each relation composed by a set of rows with a unique "atomic" value for each attribute is in 1NF, see for instance the definition in wikipedia.

Actually, the 1NF has only an historic interest, since it is now considered part of the definition of the relational model.

Upvotes: 3

Related Questions