5

In order to understand what is second normal form I was reading some articles and there are some things that I don't understand .

In the article here in the customer table it says it is not in 2NF because there are several attributes which don’t completely rely on the entire Customer table primary key.Here by primary key I think it has meant {customerId,EmployeeId}

enter image description here

If we select {customerId,employeeId} as the candidate key then it is true that Customername,customerCity,PostalCode depends only partly on candidate key and hence not in 2NF. But if we consider the candidate key to be customerId alone then all columns in Customer table are fully dependent on customerId right?(Because employeeId is dependent on customerId ).
Also as CustomerId alone can be a candidate key can we have {CustomerId,EmployeeId} as a candidate key because a candidate key can't contain another candidate key as a part of it.

Therefore if we take the customerId alone to be candidate key isn't this table in 2NF form?
but then in the article it says the a table in 2NF form should serve one purpose and here this customer table serves two purposes.
To indicate which customers are called upon by each employee To identify customers and their locations.
Then I feel like this table is not in 2NF.
So what is the candidate key in this table?

My second question is in this article

enter image description here

these table are in 3NF. In the table TABLE_BOOK the candidate key is bookId right?We can't select {bookId,genereId} as the candidate key right?If selected so it wouldn't be in 2NF as price doesn't depend on genreId.

Can someone please help me to better understand this theory behind normalization

sam_rox
  • 739
  • 3
  • 14
  • 29
  • The FDs determine the CKs. You don't get to "select" CKs. The CKs are the column sets that determine all other columns. You decide on a criterion for putting rows in the table. Then given all the situations that can arise each subset of columns (non-trivially) determines 0 or more columns, ie there is or isn't a FD from the set to the column. – philipxy Dec 14 '14 at 22:08
  • Those articles are incompetent. What textbook are you using? – philipxy Dec 16 '14 at 05:54

1 Answers1

4

Both your questions demonstrate the limitations of exercises like these. You can't make effective database designs or apply normalization principles unless you know or can determine the relevant business rules in advance. In a real life database design situation you can determine business rules by interviewing subject-matter experts and investigating existing systems and processes. In a sketched example on a website all you have to go on are a few rows of sample data and some possibly ambiguous names for attributes and tables. Solutions derived in that way are necessarily hypothetical and often imprecise and subjective.

In the first case, you are right that if CustomerID was the only candidate key of the Customer table then it would satisfy 2NF. In that case however, there could only be one EmployeeID per CustomerID - in other words: CustomerID->EmployeeID. I expect the point of the example is that more than one employee could be selling to the same customer and therefore the CustomerID alone wouldn't be sufficient as a candidate key if EmployeeID was included in the same table. That's not something shown in the sample data but it is implied by the fact that {CustomerID, EmployeeID} is stated to be a key of this table rather than CustomerID alone.

The second example is quite similar to the first. The choice of BookID as a key means that each book identified by BookID can only have one GenreID and one price associated with it: BookID->GenreID, BookID->Price. If you defined {BookID, GenreID} as the key then the dependencies BookID->GenreID, BookID->Price would no longer be enforced because the table would permit multiple genres and multiple prices per book. This would be in violation of 2NF with respect to the dependency BookID->Price.

nvogel
  • 24,981
  • 1
  • 44
  • 82