14 Replies - 1174 Views - Last Post: 30 August 2013 - 06:45 AM Rate Topic: -----

#1 AnalyticLunatic  Icon User is offline

  • D.I.C Lover

Reputation: 221
  • View blog
  • Posts: 1,030
  • Joined: 25-June 12

Assistance understanding Functional Dependencies & Anomalies?

Posted 28 August 2013 - 07:28 AM

I'm working through an old Database book of mine to get a better grasp on Database concepts. I read through a chapter on Functional Dependencies and how to normalize the database (a couple times...) but one of the chapter review questions has me particularly stumped:

Consider the relation: PERSON_2 (Name, Sibling, ShoeSize, Hobby)

Assume that the following functional dependencies exist:

Name->->Sibling
Name->ShoeSize
Name->->Hobby


Describe deletion, modification, and insertion anomalies for the relation.

My thinking goes along like this:

INSERTION: ???

MODIFICATION: Since 'Name' has not been shown as a Primary Key, if you modified records where 'Name' = 'John' and you had 2 or more Johns in the table (thinking older times with large families) then each one would then be modified.

DELETION: Same as Modification?

I seem to be having a lot of difficulty wrapping my head around this kind of thinking and understanding the idea. Could anyone be of assistance?

Is This A Good Question/Topic? 0
  • +

Replies To: Assistance understanding Functional Dependencies & Anomalies?

#2 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 8937
  • View blog
  • Posts: 33,462
  • Joined: 12-June 08

Re: Assistance understanding Functional Dependencies & Anomalies?

Posted 28 August 2013 - 08:42 AM

Just think of it one is required for the other to exist. Having a bunch of data about "siblings" doesn't make sense with out having the locus of 'name' to make that relation. In other words expressing a relationship with 'siblings' only works if you have someone (other than the sibling) to point to.

Same with shoe size... you can have a whole mess of people with size 12 shoes, but that relationship only has value when attached to someone (like a name) or a specific inventory item in a shoe store.

The same logic applies - hobby data is not overly interesting, but when you attach a specific hobby to a name _THEN_ you have useful data.

For insertion - typically you would need to insert the dominate data first and then tack on the secondary data... Say you have a ten people with various size shoes (and some repeats). If you insert the shoe size first you may find yourself inserting a duplicate records or unable to jump from the secondary data to the primary. You would best insert a name first, take that row's ID and use it to make a relation to a shoe size!

It's a difference between inserting "size 12".. then insert "bob".. then trying to find that record for "size 12" (that isn't being used by Susan or another name) and declaring that relationship. Instead you would want to insert "bob" then insert the relationship "bob is a size 12". It clears up confusion, orphaned secondary data bits, and makes a more logical flow.

With siblings - would it make sense to insert: Anne, Carol, and Dan first (in the sibling relationship).. then insert Bob as the 'name'.. and have to go back through all the siblings to find the ones missing a relationship and make that connection.. what if there were multiple annes?

No.. it would be better to insert bob first.. take bob's row ID and use that into insert Anne (saying - with the insert- that Anne is bob's sibling).. and repeat taht for Carol and Dan.

Deletion is off the same principle. You don't want orphaned data floating around causing a problem, right? So if you want to delete the name 'bob' it would be best to delete the relationship (and data) to Anne, Carol, and Dan - THEN delete Bob.

If you delete bob first then you won't have an ID to find Anne, Carol, or Dan and they'll just mope around in your table!

Does that make some sense? It's all about not wanting to have orphaned data.
Was This Post Helpful? 0
  • +
  • -

#3 AnalyticLunatic  Icon User is offline

  • D.I.C Lover

Reputation: 221
  • View blog
  • Posts: 1,030
  • Joined: 25-June 12

Re: Assistance understanding Functional Dependencies & Anomalies?

Posted 29 August 2013 - 09:24 AM

I'm trying to get a grasp, but think I need more of a visual. I tried drafting the following in word, but, I think I messed up considering the Name->->Sibling Functional Dependency? :/

[Name_]|[Sibling]|[ShoeSize]|[Hobby___]
[John_]|[Allan__]|[10.5____]|[Baseball]
[John_]|[Steve__]|[10.5____]|[Baseball]
[Allan]|[John___]|[9_______]|[Tennis__]
[Allan]|[Steve__]|[9_______]|[Tennis__]
[Steve]|[John___]|[12______]|[Football]
[Steve]|[Allan__]|[12______]|[Football]
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 8937
  • View blog
  • Posts: 33,462
  • Joined: 12-June 08

Re: Assistance understanding Functional Dependencies & Anomalies?

Posted 29 August 2013 - 09:29 AM

okay.. I must have skimmed this with the neon green text coloring.. what is your book's difference between ->-> and -> ?

Ideally you would have a name match up to zero to many siblings.. a name match up to one shoe size.. and a name match up to zero to many hobbies.
Was This Post Helpful? 0
  • +
  • -

#5 AnalyticLunatic  Icon User is offline

  • D.I.C Lover

Reputation: 221
  • View blog
  • Posts: 1,030
  • Joined: 25-June 12

Re: Assistance understanding Functional Dependencies & Anomalies?

Posted 29 August 2013 - 09:35 AM

Ideally, Yes. This is an instance where no Primary keys currently exist, just the singular table being discussed.

The double arrows ->-> represent a Multivalued Dependency, which occurs when a determinant is matched with a particular set of values. A singe arrow -> represents a single Functional Dependency.

To quote the book:

Quote

Multivalued dependencies pose no problem as long as they exist in tables of their own.


EDIT: Apologies for the Green Text. I just did that to match the question in the book.

This post has been edited by AnalyticLunatic: 29 August 2013 - 09:37 AM

Was This Post Helpful? 0
  • +
  • -

#6 AnalyticLunatic  Icon User is offline

  • D.I.C Lover

Reputation: 221
  • View blog
  • Posts: 1,030
  • Joined: 25-June 12

Re: Assistance understanding Functional Dependencies & Anomalies?

Posted 29 August 2013 - 11:54 AM

Like this question here:

Assume that Marcia keeps a table of data about her customers. Consider just the following part of that table: CUSTOMER(Phone, FirstName, LastName) Explain the conditions under which each of the following are true:

Phone --> (FirstName, LastName)
(Phone, FirstName) --> LastName
(Phone, LastName) --> First Name
(LastName, FirstName) --> Phone
Phone --> --> LastName
Phone --> --> FirstName
Phone --> --> (FirstName, LastName)


I don't even understand how I am supposed to answer?... :/
Was This Post Helpful? 0
  • +
  • -

#7 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 8937
  • View blog
  • Posts: 33,462
  • Joined: 12-June 08

Re: Assistance understanding Functional Dependencies & Anomalies?

Posted 29 August 2013 - 12:00 PM

It's a question of trying to determine what conditions the left hand side must be to find a unique record from the right.

Phone --> (FirstName, LastName)

If I want to find the record for "Joe, Blow" then the phone number needs to be unique. If I have duplicate phone numbers then I will have more than one exact return for Joe Blow.

(Phone, FirstName) --> LastName
If I want to find the lastname Blow then what combination of the phone and first name must be true? Between both of those I must have a unique set, right? But I can have duplicates of either/or, but the combination of the two must be unique else we can get a whole mess of lastnames we don't want.

etc.
Was This Post Helpful? 0
  • +
  • -

#8 AnalyticLunatic  Icon User is offline

  • D.I.C Lover

Reputation: 221
  • View blog
  • Posts: 1,030
  • Joined: 25-June 12

Re: Assistance understanding Functional Dependencies & Anomalies?

Posted 29 August 2013 - 12:44 PM

Does this sound right?

[attachment=33828:CorrectCheck.png]
Was This Post Helpful? 0
  • +
  • -

#9 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 8937
  • View blog
  • Posts: 33,462
  • Joined: 12-June 08

Re: Assistance understanding Functional Dependencies & Anomalies?

Posted 29 August 2013 - 12:48 PM

I am unclear of your question. Is what right?
Was This Post Helpful? 0
  • +
  • -

#10 AnalyticLunatic  Icon User is offline

  • D.I.C Lover

Reputation: 221
  • View blog
  • Posts: 1,030
  • Joined: 25-June 12

Re: Assistance understanding Functional Dependencies & Anomalies?

Posted 29 August 2013 - 12:58 PM

In my last post I attached an image with my answer & thought to be correct example of each situation...?
Was This Post Helpful? 0
  • +
  • -

#11 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 8937
  • View blog
  • Posts: 33,462
  • Joined: 12-June 08

Re: Assistance understanding Functional Dependencies & Anomalies?

Posted 29 August 2013 - 01:00 PM

Gotcha.. yeah.. that looks right.. does it make sense on why if those specific examples were, say, not unique how that would cause a problem?
Was This Post Helpful? 0
  • +
  • -

#12 AnalyticLunatic  Icon User is offline

  • D.I.C Lover

Reputation: 221
  • View blog
  • Posts: 1,030
  • Joined: 25-June 12

Re: Assistance understanding Functional Dependencies & Anomalies?

Posted 29 August 2013 - 01:03 PM

View Postmodi123_1, on 29 August 2013 - 08:00 PM, said:

Gotcha.. yeah.. that looks right.. does it make sense on why if those specific examples were, say, not unique how that would cause a problem?


I think so... There could potentially be several issues, but the biggest would be the duplication of data bulking up the database unnecessarily and harming data integrity?
Was This Post Helpful? 0
  • +
  • -

#13 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 8937
  • View blog
  • Posts: 33,462
  • Joined: 12-June 08

Re: Assistance understanding Functional Dependencies & Anomalies?

Posted 29 August 2013 - 01:04 PM

Harming the integrity of the unique relationship.. yes.
Was This Post Helpful? 1
  • +
  • -

#14 AnalyticLunatic  Icon User is offline

  • D.I.C Lover

Reputation: 221
  • View blog
  • Posts: 1,030
  • Joined: 25-June 12

Re: Assistance understanding Functional Dependencies & Anomalies?

Posted 29 August 2013 - 01:21 PM

So like with this one, is [EmployeeName]-->-->[ProjectName] the ONLY functional dependency? It's the only one I'm seeing, since [Date] cannot determine [EmployeeName], like in my example where "Bob" & "Dan" both had meetings on "8/3/2013".

I'd like to think I've got B correct, but I'm not quite sure.

[attachment=33829:Question.png]
Was This Post Helpful? 0
  • +
  • -

#15 AnalyticLunatic  Icon User is offline

  • D.I.C Lover

Reputation: 221
  • View blog
  • Posts: 1,030
  • Joined: 25-June 12

Re: Assistance understanding Functional Dependencies & Anomalies?

Posted 30 August 2013 - 06:45 AM

???
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1