-3

I want to normalize the table So far i have done this:

is it my 3Nf correct?

Order(orderNo,orderDate,customerId)

Customer(customerId,customerName,customerCurrentAddress,customerPermanentAddress)

Product(productId,productName,Qty,categoryId)

Category(categoryId,categoryName)

Sub-Category(subCatId,subCatName,categoryId)

Given table here: https://drive.google.com/file/d/1F7cQjjxz9rnY6RHaGtZXuwVGFEHBVgNo/view

Sheri
  • 1,383
  • 3
  • 10
  • 26
  • Re "is this right": Show the steps of your work following your reference/textbook, with justification--you may find mistakes that make your question unnecessary & we don't know exactly what algorithm you are following & we want to check your work but not redo it & we need your choices when an algorithm allows them & otherwise we can't tell you where you went right or wrong & we don't want to rewrite your textbook. Please see [ask], hits googling 'stackexchange homework' & the voting arrow mouseover texts. – philipxy Oct 31 '19 at 18:15
  • 1
    Hi. Your comment is nowhere near what my first comment said to give. PS Please clarify via edits, not comments. Please [use text, not images/links, for text--including tables & ERDs.](https://meta.stackoverflow.com/q/285551/3404097) Paraphrase or quote from other text. Give just what you need & relate it to your problem. Use images only for what cannot be expressed as text or to augment text. Images cannot be searched for or cut & pasted. Include a legend/key & explanation with an image. Also, links die. Insert images/links using edit functions. Make your post self-contained. – philipxy Nov 09 '19 at 23:52
  • 1
    I already told you "Your image was not obviously a relational table--a set of distinct rows of one entry per column--so you need to tell us how to interpret it." So telling us that it is an unnormalized table--even if we knew what you meant by either of those two words--does not help us read it. PS ["1NF" has no single meaning.](https://stackoverflow.com/a/40640962/3404097) Nor does "unnormalized" or "UNF" or "0NF" or for that matter "relation". "Show the steps of your work following your reference/textbook [etc]" – philipxy Nov 10 '19 at 00:11
  • 1
    I don't think you're quite there yet. `Qty` on `Product` looks wrong to me - I don't know what "quantity" that represents - quantity-ordered, or quantity-on-hand, or quantity-in-warehouse, or something else? I would expect a `ProductInventory` table with `ProductId`, `OnHandQty`, `OutstandingPurchaseOrderQty`, `QtyInTransit`, etc. I would also expect there to be an `OrderedProduct` table with columns `OrderNo, ProductId, OrderedQty`. And even the above isn't right, as `OutstandingPurchaseOrderQty` shouldn't be there - just ref the PO's. And `QtyOnHand` is derived from warehouse locations. Etc. – Bob Jarvis - Слава Україні Nov 19 '19 at 19:55
  • Don't use links or images for tables or text. Use text to put your table into your post. – philipxy Nov 20 '19 at 02:47

1 Answers1

1

You have to make a junction table between Order and Product in order to keep track of orders that contain more than one product.

Order(orderNo,orderDate,customerId)

Order_Details(orderNo,productId,Qty)

Customer(customerId,customerName,customerCurrentAddress,customerPermanentAddress)

Product(productId,productName,Qty,categoryId)

Category(categoryId,categoryName)

Sub-Category(subCatId,subCatName,categoryId)

Base on the definition from Wiki: a table is in 2NF if it is in 1NF and no non-prime attribute is dependent on any proper subset of any candidate key of the table. Most of your tables only have 2 columns, so they satisfied this. For Customer(customerId,customerName,customerCurrentAddress,customerPermanentAddress), the candidate key is customerId, and the other columns completely depend on the whole candidate key, then it's OK.

For 3NF, Wiki said: all the attributes in a table are determined only by the candidate keys of that table and not by any non-prime attributes. As you see, your tables are all satisfied.

AS2
  • 89
  • 1
  • 7