-1

I am creating a database for a super market in my database class. The database needs to be at least 3nf but if possible, BCNF. Could someone let me know if this is satisfactory? I believe it is and I just want to make sure.

enter image description here

Sean Gallagher
  • 73
  • 9

1 Answers1

-1

The values all look atomic, the only problem I see is in your System.orders table. 3NF should not store calculated values.

Here's a good topic page on calculated values.

The other one I'm not sure about is the lack of a primary key on System.orderItems. I've been told it's good practice for each table to have a Primary Key. On your relational table, you make both orderId and itemId a linked Primary Key.

Here's a good discussion on the need for a Primary Key.

Hope that helps.

Community
  • 1
  • 1
Morris Buel
  • 126
  • 1
  • 8
  • **Total** in the _System.Orders_ table - Total is quantity * price. When I learned Normalization the values had to be atomic. A calculated value isn't atomic, it's dependent on the value from two other tables. – Morris Buel May 01 '17 at 23:43
  • "Calculated", "computed" and "generated" are used re a column evaluated by the DBMS. Such a column can be ignored in normalization, *because* it is calculated. The question doesn't say it's calculated in that sense. Regardless, the NFs a table is in are a function of its possible values, but TOTAL would be a function also of some other table(s), and that constraint/redundancy would not affect NFs. (Although it is relevant to good design.) (Also in Orders it would be a sum of products.) In OrderItems (as a product) it's the same situation. Price & quantity both in Orderitem would violate 3NF. – philipxy May 02 '17 at 00:24
  • You are also misusing the term "atomic", which does not have to do with functional dependencies. (And although "[atomicity](http://stackoverflow.com/a/24038895/3404097)" has to do with some definitions of "normalized" & "1NF", those definitions are more or less irrelevant to higher NFs, which don't care what the types of columns are.) – philipxy May 02 '17 at 00:27
  • Atomic, the value is not divisible or capable of further decomposition. A total value is by definition composed of values from two other tables, is divisible and decomposable. It's not atomic, nor is it 3NF. http://stackoverflow.com/questions/24029620/what-is-atomicity-in-dbms – Morris Buel May 03 '17 at 05:14
  • I just gave a link to my answer to that linked question. (And the answer by MikeSherrill'CatRecall' is also very good.) Your comment is not clear, I don't know what you are trying to say, but it doesn't look like anything that I haven't already contradicted. You need to learn about FDs & normalization to higher NFs from a textbook, presentation or course. (Many are online.) Good luck. – philipxy May 03 '17 at 07:37
  • I did learn normalization from a textbook. I quoted atomic from the textbook. No calculated values should make it to the third normal form. If you turned this in as a project for any normalization class, it would be rejected because of the calculated value. – Morris Buel May 03 '17 at 12:01
  • PS [Normalization in database management system](http://stackoverflow.com/a/40640962/3404097) – philipxy May 05 '17 at 03:17