1

for an assignment for school, we are to draw out the UNL,1NF,2NF,3NF relations for a table we are provided. I think i was able to complete UNL,1NF,2NF but i was not sure how to do that for 3NF.

Here is a picture of the table:

enter image description here

Here are my possible solutions missing 3NF.

UNF Relation

ProductReport[ProductC, Classif, Markup(ProductID, Desc, Cost)]

1NF Relation

ProductReport[ProductC, Classif, Markup]

ProductDetails [ProductC, ProductID, Desc, Cost]

2NF Relation

ProductReport[ProductC, Classif, Markup]
Product [ProductID, Desc]
ProductDetails [ProductC, ProductID,  Cost]

3NF Relation

So my question is, Is my UNF,1NF,2NF correct? and if yes, how would 3NF look?

philipxy
  • 14,867
  • 6
  • 39
  • 83
user3072143
  • 77
  • 1
  • 2
  • 7
  • 2
    Each normal form of a relation MUST indicate the key(s). – reaanb Dec 05 '16 at 06:20
  • Please [use text, not images/links, for text (including code, tables & ERDs)](https://meta.stackoverflow.com/q/285551/3404097). Use a link/image only for convenience to supplement text and/or for what cannot be given in text. Make your post self-contained. – philipxy Jan 14 '19 at 22:26
  • Normalization to higher NFs uses FDs. What are yours & why? PS Show & justify the steps of your work, otherwise you expect us to redo it instead of just checking it. (Like in everyone's entire academic education.) Also give [your class's definition of "UNF" & "1NF"](https://stackoverflow.com/a/40640962/3404097)--there are no fixed meanings. (Also define your "UNF" notation & if your starting table isn't a representation of a relation then define how to interpret it.) – philipxy Jan 14 '19 at 22:33

2 Answers2

0

I can help with the first two forms, I'm not 100% sure on 3NF in your case.

Here's my interpretation.

1NF

  • The fields of the table must contain atomic values.
  • There can be no repeating groups

Your table is already in 1NF because each field only contains single values (even if they are multiple word strings) and there are no repeating groups because each field stores a different attribute; e.g. if products were repeated on the same row for one classification

2NF

  • The table is in first normal form
  • All non-key attributes are fully functional dependent on the primary key

As you have identified, we need to split the classification and markup from the products as well as the product description from the cost; and the final charge will be a calculated field in the final report query.

3NF

The definition of 3NF is:

  • It is in second normal form
  • There is no transitive functional dependency

A transitive functional dependency is described as: A is functionally dependent on B, and B is functionally dependent on C. So A is transitively dependent on C via B.

So we are looking for dependencies between fields in the same table, not across tables as I first said (thanks to reaanb for pointing that out).

I don't see a transitive dependency in your tables, I'd be interested to know if anyone else can spot one. I'm thinking that if you have been given this as an assignment there must be a third normal form they are looking for, or maybe not! :)

Tony
  • 9,672
  • 3
  • 47
  • 75
  • 2
    Did you notice that Markup is dependent on Product Class? That is, Garden Tools (GT) all have a 30% markup, Shrubs (SB) have 50%, Fertilizers (FT) have 25%, and Sprinklers (SP) have 40%. – dan04 Dec 02 '16 at 00:02
  • While the table data as it stands doesn't *have* any duplicate rows, my understanding of 1NF is that the table must be explicitly defined to not *allow* duplicate rows to be added in the future. In SQL, this can be accomplished by declaring a `UNIQUE` or `PRIMARY KEY` constraint. In this case, Product ID seems to be ideal for the purpose. – dan04 Dec 02 '16 at 00:06
  • Yes, the OP has moved the markup in to a table with the product class. I'm not sure what you mean by your comment, are you saying there is a 3NF dependency? – Tony Dec 02 '16 at 00:06
  • I don't see any requirement in 1NF to prevent duplicate rows. Here's the definition from Wikipedia: "A relation is in first normal form if and only if the domain of each attribute contains only atomic (indivisible) values, and the value of each attribute contains only a single value from that domain." – Tony Dec 02 '16 at 00:09
  • Yes i think 3NF is the Markup table – user3072143 Dec 02 '16 at 00:09
  • @Tony: The OP is correct on this point for 2NF (with the `ProductReport` table); it just didn't seem clear from the 2NF section of your answer. – dan04 Dec 02 '16 at 00:11
  • The markup is a direct dependency but I don't thinks it's transitive, unless I'm missing something :) – Tony Dec 02 '16 at 00:12
  • Also @Tony: I did not completely understand what you replied, does that mean my 1NF AND 2NF are correct? – user3072143 Dec 02 '16 at 00:13
  • @Tony: According to the Wikipedia “First normal form” article (the “1NF tables as representations of relations” section near the bottom), “There are no duplicate rows” is a requirement of 1NF, and “A table that lacks a unique key” is given as an example of a violation of it. – dan04 Dec 02 '16 at 00:14
  • @dan04 - I've clarified the 2NF part. Thanks. – Tony Dec 02 '16 at 00:15
  • @user3072143 - your 2NF seems correct to me, I'm not sure about 1NF but I may be wrong from what dan04 has been commenting. – Tony Dec 02 '16 at 00:18
  • @user3072143: Your 1NF is technically correct but “overnormalized”. All you need is at least one unique key (which may include one or more columns), only one value per column (e.g., don't store a comma-separated list in a table cell), and no dependencies on row/column order or hidden fields like ROWID. – dan04 Dec 02 '16 at 00:23
  • 1
    In your 3NF description, you describe dependencies between tables. There's no such thing in relational theory. Transitive dependencies (and functional dependencies in general) refer to dependencies among *columns* of a relation. – reaanb Dec 05 '16 at 06:31
  • @reaanb - You are correct (it was getting late when I was answered this question); I'll correct my answer. – Tony Dec 05 '16 at 10:01
0

There's a transitive dependency between Markup and Charge. The two fields must be placed in a separate table to pass 3NF.

A more common example of 3NF is zip code and city. If a user changes the city value, it directly affects the zip code value.

I personally would keep it in 2NF and handle this in the server side code (c# or java).

user10553012
  • 19
  • 2