I like the way Maulin has put Normalization Exercise 2 to the test (the pdf answer is here). My own observation is that it all depends on how you conceptualize the tables. If this is a table about stock sales, then sell date should be part of the key. It should never be null in that case. If the table is not about completed sales, then sell date should not be part of the key.
I somewhat disagree with Maulin concerning the 0/1 approach to sales. What I might do is treat it as a conditional dependency as described in Chapter 3.3.
Solution to Exercise 2(normalization)
I have been trying to figure out how the primary key in the "in class normalization exercise #2" consists of CustID, StockSymbol and SellDate. What confuses me is that how can the SellDate be part of the primary key. According to the definition, the primary key cannot have a null value i.e. it must have a value at all times. However, suppose a customer has not sold his stock as yet, the sellDate will be null. Hence i feel that SellDate cannot be part of the primary key. But then again, without SellDate, we cannot get the SellPrice. Now, on the first page of the answers under "Removed derived data", the 3rd bullet points out that the field Sold is a derived data. This is true but I feel removing this field may not be appropriate. If we keep this field which will have a boolean value, then it will tell us if the stock was sold and if that is true, then we will get the SellDate and SellPrice. Thus we can actually make the Sold field part of the primary key which will always have a value. Am a little confused with this solution and was wondering if anyone agreed to it. If so could you please explain the logic behind it.
[maulin]
I tried linking this solution from the course website but i dont think it's posted online.
Comments