Results 1 to 12 of 12

Thread: Theory Question

  1. #1
    Join Date
    Mar 2008
    Posts
    11

    Theory Question

    Hi there,

    I have a question regarding the difference between structurally enforcing a functional dependency versus procedurally enforcing a functional dependency.

    Not being able to find any actual definitions for these, my educated guess is that a functional dependency is enforced:
    1. structurally when it is enforced using one of the constraints available in the relational model e.g. primary key, foreign key, check constraint, uniqueness constraints etc.
    2. procedurally when it is enforced using some means outside the relational model e.g. a stored procedure, a trigger or some other kind of coding.


    If anyone can set me straight or confirm my thoughts I would be much obliged. Note, this forum is not my first port of call. I have attempted to find this answer in many books and using Google.

    Thanks

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Your definitions appear perfectly sound to me
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i though FD was all about the relationship between non-key attributes and keys

    you know, normalization
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    OK.
    So next question for discussion would be "When would procedural integrity be preferential to structural integrity?"
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I prefer the term declarative integrity.

    And yeah - can the OP supply an example of a functional dependency that cannot be enforced declaratively? Or have missened the point again....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pootle flump
    Or have missened the point again....
    no, haven't missened
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Mar 2008
    Posts
    11
    Hi everybody,

    Thanks very much for your replies. Later today, I will post the exact scenario that I am talking about. I have to be at a meeting now and don't have the time to make that post.

    So ... until a bit later.

    Thanks again.

  8. #8
    Join Date
    Mar 2008
    Posts
    11
    Hi Again. Here is the whole scenario.

    We start with a table in 3NF {Degree, Faculty, Coordinator}

    The functional dependencies extant in the data are:
    Degree, Faculty -> Coordinator AND Coordinator -> Faculty

    From that, we can discern the candidate keys (using Armstrong's axioms) -:
    Degree, Faculty AND Degree, Coordinator

    So, we can now decompose that to BCNF:
    DegFac_tbl: {Degree, Faculty}
    Coord_tbl: {Coordinator, Faculty}

    We lose 1 FD in that decomposition (as is common when decomposing to BCNF) - Degree, Faculty -> Coordinator

    Now we get to the question which I was unsure of and originally asked.
    One of the FDs can be enforced structurally i.e. Coordinator -> Faculty (co-ordinator being the PK of the relation Coord_tbl.

    Therefore, we must use some other means to enforce the lost FD e.g. a stored procedure, trigger or the like. I used the term procedurally to describe this.

    I hope I haven't bored everyone. It's really just a definition thing. I have the concepts down pat perfectly. I just want to make sure that I am using the correct lingo when discussing it.

    Thanks

  9. #9
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    1. structurally when it is enforced using one of the constraints available in the relational model e.g. primary key, foreign key, check constraint, uniqueness constraints etc.
    2. procedurally when it is enforced using some means outside the relational model e.g. a stored procedure, a trigger or some other kind of coding.
    Procedural RI implies that something (a proc) needs to be called explicitly to enforce the RI. Structural implies that the RI is built into the database and so cannot be sidestepped ie FKs. If the above is true then surely triggers are structural and not procedural as mention above. Also at my current place we use Sybase with a database design tool to declare the FKs - this turns these FKs into triggers - what side of the fence would these FKs sit on?

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Although it acts like DRI in the way describe I would put triggers as procedural. In effect the are like event prodecures in other languages - they are procedures that are triggered by events. How closely coupled they are with the database tables is not really the defining characteristic IMO.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Mar 2008
    Posts
    11
    That is an interesting point. Some databases (like ProstgreSQL) use triggers to enforce the foreign keys. However, in a situation like this, I believe the trigger usually calls a procedure. I know in PostgreSQL that triggers call trigger procedures. So in effect, although it is triggered automatically, it does call a procedure (depending on the situation).

    But importantly, your statement that Procedural RI implies that something (a proc) needs to be called explicitly to enforce the RI has helped a lot. That really makes sense to me. And in the context of my example, it does make sense.

    Out of the two FDs, you could definitely say that the FD Coordinator -> Faculty can be enforced structurally (with my new understanding of that term).

    As for the other FD (Degree, Faculty -> Coordinator), it would be implementation-specific, and therefore, not clear-cut. Perhaps procedurally, perhaps structurally.

    Thanks for helping me think through these concepts/definitions. It's been a great help.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    stop beating yourself up over all that nonsense and go back to the 3NF design

    it's one more FD and one less table, dude
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •