Search code examples
databasedatabase-normalization3nf

I am trying to normalize a set of functional dependencies to 2NF and 3NF without loss of dependencies

Following are the functional dependencies for table 'Portfolio' (containing information about the stocks bought by a particular user):

User_name, Stock_ID -> Quantity, Buying Price ;
Stock_ID -> Current_Price ;
Buying_Price, Current_Price -> Change ;
Quantity, Change -> Net_Change ;
Solution

  • Are you sure you want such attributes in the table which seem to be redundant and only cause unnecessary functional dependencies? If 'change'='buying_price'-'current_price' and 'Net_change'='Quantity'*'change', then 'buying_price' and 'current_price' might be sufficient to achieve the same result with less redundancy, because 'Change' and 'Net_change' can be computed when required.

    As Lordferrous already mentioned the relation is not in 2NF and to achieve that we would have to move partial dependency 'Stock_ID -> Current_Price' to a different relation. In the process FD3 (Buying_Price, Current_Price -> Change ) would be lost.