-3
ID Name Number Quantity Measurement
101 A 2541 5 cm
102 A 2341 5 cm
103 B 4561 15 cm
104 B 1896 15 cm
105 C 1546 30 mt
106 C 2531 30 mt
107 C 7852 30 mt

I have the above table and now, I want to convert it into BCNF, 4th & 5th Normal Form. I've tried and read online and also have gone through youtube videos but not able to do it for this table.

Any help is appreciable. Thanks in advance.

Edit: This is my thought for

  1. 1NF: For any table to be in first normal form-
  • All non-key columns of a table should depend on the primary key.
  • There should be unique entries in the table.

Hence, on having a closer look at the table, we notice that the table is already in the first normal form since it satisfies the above properties. Here we have considered ‘ID’ to be our primary key. The table structure is similar to the one already given but for our convenience we will reorder it. We will consider the table to be like – [ID, Name, Number, Quantity, Measurement].

  1. 2NF: For a table to be in second normal form-
  • A table should be in 1NF.
  • All non-key columns depend on the whole primary key.
  • A table with a simple primary key is automatically in second normal form.

Since, all the above properties are satisfied by the current table structure it is in 2NF too. Hence, the table structure is- [ID, Name, Number, Quantity, Measurement].

  1. 3NF (Not so sure): It occurs in 2NF when a non-key column depends on another non-key column. A similar thing happens in the table we are given here. The Number and Measurement depend on the Name which is causing redundancy. Moreover, for the table to be in 3NF-
  • The table should be in 2NF.
  • All non-key columns depend on the key, the whole key, and nothing but the key. Thus, we need to normalize our table. For this we decompose the table as below- ✓ Table 1: ID (PK), Number, Name ✓ Table 2: Name (PK), Number ✓ Table 3: Name (PK), Measurement
Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
dough
  • 3
  • 6
  • 1
    Just posting the table does not explain the relationship between Name and Number. – Gilbert Le Blanc May 14 '21 at 18:37
  • @eshirvana I've edited my questions with my thoughts and tries for 1, 2 & 3NF. – dough May 14 '21 at 19:02
  • @GilbertLeBlanc Can you check now and let me know if you have some more doubts or a good solution for BCNF and 4NF. – dough May 14 '21 at 19:03
  • 1
    I've never cared much about when a table is in 1NF, 2NF, etc. I find these very theoretic, because when creating a database one looks at normalization without considering the single stages. In your sample data you always have the same quantity and measurement for a name. This may be mere coincidence, though. If this is the case, then there is no problem with the table. If this is not the case, then the table is not normalized and the data model should be fixed. – Thorsten Kettner May 14 '21 at 19:44
  • What is your 1 specific researched non-duplicate question? PS Those NF definitions are not correct. You can find correct definitions in (good) textbooks & (correct) SO answers. Sadly, there are a lot of bad ones. Web posts & videos are useless. Right now your post is effectively asking for the 1st definition you got wrong or aren't sure about. That's a faq. See [ask], other [help] links, hits googling 'stackexchange homework' & the voting arrow mouseover texts. PS ["1NF" has many meanings.](https://stackoverflow.com/a/40640962/3404097) PS NFs >1 depend on CKs & FDs, but you don't mention them. – philipxy May 14 '21 at 19:44
  • https://bookboon.com/en/an-introduction-to-relational-database-theory-ebook https://bookboon.com/en/author/0908031c-ce02-9b86-11e6-6dd7d2c299d1 – philipxy May 14 '21 at 19:56
  • I still don't understand the relationship between Name and Number. Until I do understand, there's nothing more I can say. – Gilbert Le Blanc May 14 '21 at 22:29

1 Answers1

1

Table data is not self-explanatory.

By looking at table data without context, i.e. without an explanation what the data represents, one cannot tell whether it matches normal forms or not.

Let's look at the table you have shown us:

  • If the Number 2541 in your table means "2 pieces in May 1941", then it violates 1NF, because the column data is not atomic.
  • If Name = 'A' is a product called A that is 5 cm long, then why is that information twice in the table? 2NF violated.
  • If Name is a unique product name and Number is a unique person number and a row says how much (Quantity x Measurement) a person owns of a product, then the table doesn't violate any NF I am aware of.
Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73