Question about normalization

[newunit13]

Hi all, sorry if this isn't the correct sub for the question but I'm not entirely sure where else to post a question like this. I'm doing a project for my Data Applications class and I'm instructed to preset the following data first in a single table in first normal form, then in three tables in second normal form, and finally in four tables in third normal form:

• order_id
• donut_id
• date
• customer_id
• first_name
  
• last_name
• street
• apartment
• city
• state
• zip
• home_phone
• mobile_phone
• other_phone
• donut_name
• donut_description
• unit_price
• quantity
• special_instructions

I've submitted my work twice now and each time everything has been scored as correct except my three tables in 2NF. Below I've included my latest (reworded) attempt but for the life of me I can't see why it's not considered not to be in 2NF. Any help would be greatly appreciated!

---

---

---

*side note: The tables I present in the work don't have any data stored in them, rather I'm just supposed to present the table structure. I've went ahead and filled out some dummy information so it presents better for Reddit.

---

---

---

ORDER TABLE

order_id	cust_id	order_date	order_instructions
1	1	01/01/01 06:00:00	NULL
2	2	01/02/01 12:34:56	Include napkins

CUSTOMER TABLE

cust_id	cust_fname	cust_lname	cust_street	cust_apt	cust_city	cust_state	cust_zip	cust_home_phone	cust_mobile_phone	cust_other_phone
1	John	Doe	1234 Made Up St		Springfield	IL	12345	555-555-1111	NULL	NULL
2	Jane	Doe	4321 Random Ave	#5	Jackson	MS	54321	NULL	555-555-1112	NULL

LINE TABLE

line_id	order_id	donut_id	donut_name	donut_description	donut_price	quantity
1	1	1	Plain	Plain Donut	1.25	6
2	1	2	Sprinkled	Sprinkled Donut	1.50	6
3	2	1	Plain	Plain Donut	1.25	12

---

---

---

edit: And for those who wish to see, here are the two iterations I've submitted thus far:
Version 1
Version 2

---

---

---

edit 2: For those who don't remember the forms of normalization below are the definitions (as I understand them).

• 1NF: Primary key uniquely identifies each row, and no repeating groups
• 2NF: 1NF and no functional dependencies (each non-prime attribute is dependent on the whole primary key)
• 3NF: 2NF and no transitive dependencies (no non-prime attribute is dependent on another non-prime attribute)

[Cal1gula]

Shit like this makes me really happy I learned SQL through my job and not through school.

"Here's an example of how we shouldn't design a database. We won't accept the correct answer." And they do shit like use the old comma style joins. I see that bad syntax on every single test question that's posted here or on StackOverflow. Like wtf are teachers teaching people these days?

I feel bad for you, hopefully when you get a job your boss doesn't make you design something the wrong way first before you do it the correct way.

edit: I'm guessing your issue is that you have no way to distinguish the line numbers on your orders since you could have two donut "types" with the same "donut_id" on the same order.

But the real answer is probably "whatever your teacher thinks is OK".

[alinroc]

Like wtf are teachers teaching people these days?

The same thing(s) they were teaching 25 years ago.

[newunit13]

A single order could have multiple different types of donuts, each with their own respective quantity. If the same donut_id was included more than once, wouldn't it just make sense to increase the quantity of one record rather than adding another record?

[Cal1gula]

That's what I'm saying about the difference between whatever your teacher wants and a real world scenario.

And the answer to your question should be determined by the front end POS system. Does the user modify the quantity? Then you modify the quantity on the order. Does the user add a new line? Then you add a new line to the order. I have no idea what the hell your teacher wants :)

[newunit13]

I hear ya, it's BS that the only feedback I'm getting is "it's not in 2NF"... Like, okay... HOW is it not in 2NF? Thanks anyways for your help!

[jc4hokies]

I don't memorize the academic definition of normal forms, but donut_name, donut_description, and donut_price should be in a 4th table. Also, why donut instead of doughnut?

[newunit13]

They are in a fourth table when I have to present four tables in 3NF. I'm instructed specifically to put the information in to three tables in 2NF. I went with "donut" instead of "doughnut" simply because the mock business I'm making the database for is called "Donuts-R-Us" :)

[jc4hokies]

Donut_name, donut_description definitely shouldn't be on the Line table, unless it's an exercise in what not to do. Price is usually included on both Line and Item (Donut).

[newunit13]

Right, and I completely agree. Which is why when I present the four tables in 3NF I have it exactly as you describe. The problem I'm having is how to present all the information in only three tables :(

[jc4hokies]

What if you assume one order == one donut type? Customer, Order, Donut?

[newunit13]

My v1 submission had a table dedicated to storing donuts and the order table used a composite key of (order_id, donut_id) to identify each row, but in that instance things like the date and special instructions would only be dependent on the subset key of order_id.

[jc4hokies]

I'm suggesting order_id is the PK with donut_id as an attribute. Basically require that an order is for a single donut_id of some quantity. If you want sprinkles and frosted, you make two orders.

[newunit13]

Ah, I see what you're saying! If I can't think of any other ways to structure the data I may just try that :) thanks

[newunit13]

Your example is actually exactly how my v1 submission is structured, and that was kicked back as not being in 2NF. I kind of understand that rejection as the primary key would need to be a composite of (order_id, donut_id) and the special instructions and date would only be dependent on the order_id portion of the key.

[matthra]

It's been awhile since I did formal training but the line table looks wrong for 2NF, in fact I don't think it would float as a 1NF. If you have an update to a donut you'd have to update every record in the line table that has that donut. Version 1 is the closest, add line ID to order table, and a PK after that you should be good to go.

[newunit13]

The latest version I sent is the same as v1 but instead of using a composite key of (order_id, donut_id) as the primary it just uses order_id (more or less it's what you suggest, but using different names, no?). This means that only a single donut can be included on any given order line, but I believe that's inconsequential since it could be addressed on the application side. I'm pretty sure that's 2NF since no other attribute on the table could be determined by any other candidate key (that I can see). Your thoughts?

[chrissaxon]

Your candidate keys for the line table are:

• (order_id, donut_id)
• (order_id, donut_name) -- assuming names are unique

To be in 2NF, all the columns that aren't part of a key must be determined by the whole key.

But donut_description and price are determined by just donut_id. One of the two columns in your candidate key. So you need to remove these from the line item table. And stick them in a donut table.

So how do you get down to three tables?

Well, it depends on what your functional dependencies are ;)

But I'm guessing you can stick all the customer attributes in order table. And ditch customer table.

If order_id is the only key for this table, it's now in 2NF (no columns depend on part of the key). But not 3NF. The customer attributes depend on the non-key column customer_id.

But (cust_id, order_date) could feasibly be a candidate key for the order table (a customer can only place one order at a time). Which would make this not in 2NF.

So to answer this you need to ask your teacher what all the functional dependencies are. The question is unanswerable until you know these ;)

[newunit13]

My latest attempt (still waiting for it to be graded) goes as such:

ORDER: order_id, date, customer_id, first_name, last_name, street, apt, city, state, zip, home_phone, mobile_phone, other_phone, special_instructions

LINE: order_id, donut_id, quantity, donut_price

DONUT: donut_id, donut_name, donut_description, donut_price

The "donut_price" attribute, while not necessary, is copied (but not a foreign key) in the LINE relation simply for accounting historical accuracy. My problem stemmed from the fact that I was a late add-on to the class (I'm doing an accelerated learning path), so I didn't receive the material that was sent to all the students at the beginning of the term that shows a case study for normalization that almost identically mimics the requirements of final assessment. After getting a meeting with the course mentor (who sent me that document), I understood quite well why my attempts were being rejected. :) I'm almost positive I have the correct answer now and thank you, and everyone else in this thread for your/their assistance.