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

Your definitions appear perfectly sound to me

i though FD was all about the relationship between non-key attributes and keys

you know, normalization

OK.
So next question for discussion would be "When would procedural integrity be preferential to structural integrity?"

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....

pootle flump ur codings are working excelent.

no, haven't missened

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.

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

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?

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.

pootle flump ur codings are working excelent.

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.

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