1

UPDATE: Relational Model may not work in the way I want it to, see:Database normalization for facebook-like messaging system Time for NoSQL!

I am having trouble putting a database into 2nf. For that, you must determine all functional dependencies before you can decide if an attribute is prime or non prime.

Have a look here:

--------------------------------------------
   to   |  from  |   msg          |  time  
--------|--------|----------------|---------
  joe   |  jim   | hello          |   1
  jim   |  joe   | hey            |   2
  jim   |  joe   | how are you    |   3
 victor |  bryce | i love carrots |   4
  joe   |  jim   | im doin great  |   5
  bryce |  jim   | hello          |   6

NOTE: Time will be unique. It will be transacted.

Does time->message despite

time1->"hello"
time6->"hello"

Because I have heard as long as there are unique instances of message, its fine. However, I am confused by this.

Also, I want to add a message id column. Is that good practice?

Community
  • 1
  • 1
Steve Webster
  • 101
  • 8

1 Answers1

1

A functional dependency asks, "If I know one value for 'X', do I know one and only one value for 'Y'?", where 'X' and 'Y' are attributes of a relation. ('X' and 'Y' refer to sets of attributes.)

If the values of the attribute "time" are actually unique, then knowing one value for "time" means you know one and only one value for "msg". That means the functional dependency time->msg holds for this relation.

In contrast, the functional dependency "to"->"msg" does not hold in this relation, because knowing the value "joe" means I know two values for "msg": 'hello', and 'im doin great'. It doesn't hold for this relation, so we say "to"->"msg" is not a functional dependency in this relation.

For exactly the same reason, "to, from"->"msg" does not hold in this relation. So "to, from"->"msg" is not a functional dependency in this relation.

Also, I want to add a message id column. Is that good practice?

Adding attributes that are not in the original relation has to do with data compression, not with normalization. Normalization never introduces new attributes or new dependencies. Adding "msg_id" as an attribute introduces two new functional dependencies (depending on what "msg_id" means): "msg_id"->"msg", and "time"->"msg_id".

So adding a "msg_id" attribute might be a good idea sometimes (less often than you think), but it has nothing to do with normalization. Assuming you intend to project "msg_id, msg" as a new table and remove "msg" from the original relation, you need to declare "msg" as unique, too.

Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
  • how would you fix this database to get it to work in practice, so i can get it to 2nf – Steve Webster Oct 02 '16 at 14:33
  • @SteveWebster: If "time"->"to", "from", "msg", which seems reasonable, this relation is already in BCNF. That means it's also in 3NF, 2NF, and 1NF. Informally, since a) "time" is the only candidate key, and b) "time" is a single column, it must be in 2NF. (You have to have multiple columns in a candidate key before you can have a partial-key dependency.) Since "time" is the only attribute on the left-hand side of a functional dependency, there can be no transitive dependencies, so it must be in 3NF. Since every arrow in every FD is an arrow out of a candidate key, it must be in BCNF. – Mike Sherrill 'Cat Recall' Oct 02 '16 at 15:09
  • but in practise, will "time"->"msg" hold? two msg will very likely repeat.in a social site – Steve Webster Oct 03 '16 at 12:26
  • @SteveWebster: Yes, "time"->"msg" will hold, because "time" is unique. (You said.) – Mike Sherrill 'Cat Recall' Oct 03 '16 at 14:25
  • "If I know one value for 'X', do I know one and only one value for 'Y'?", where 'X' and 'Y' are attributes of a relation. ('X' and 'Y' refer to sets of attributes.) (You said in your reply) "time" is unique,ok, but not "msg". I know unique values for X, but not unique values for Y. Y is "msg". See the example. – Steve Webster Oct 03 '16 at 17:57
  • @SteveWebster: Imagine executing the SQL statement `select msg from yourtablename where time = 1;`. How many values will you see for "msg"? – Mike Sherrill 'Cat Recall' Oct 03 '16 at 18:30
  • Thanks! I was confused by the language. – Steve Webster Oct 03 '16 at 18:46