Re: Bill of materials / Groups in Groups

>>I would say that a constraint rejecting an input to the DB is an
anomaly. <<

No, a constraint is supposed to reject invalid data. A database that will accept anything as input has problems. Remember the old file systems where the product description could be "I hate my job" if the data entry clerk was haivng a bad day <g>?

>> The only way to modify the database is to use a
stored procedure that performs the requested modification and then updates all the lft and rgt attributes. (The database travels through many invalid states on its way to becoming valid again. That's fine with me. I don't know if you would consider it an "anomaly".) <<

That is called a transaction. The important point is that at the end of the transaction the database is in a state which is consistent with all the constraints. That state is either where it started (ROLLBACK WORK) or a new one (COMMIT WORK). How it looks at any step inside the transaction is not important.

>> I've been trying to figure out what notion of "normalization" Mr.
Celko was referring to when he made the comment about changing Chuck's id number. <<

In a relational databse there is no such thing as an object identifier, which is how you wanted to treat the sequential row number you added to the original table. Everything can be updated if you can keep the constraints right.

Delete Chuck from the adjacency list model and you have a forest instead of a tree -- you lost the subordination relationship his subordinates had with the boss at the root of the tree. You have Chuck playing two roles in the same table, boss and subordinate -- change one and you have to change the other. Swap Chuck with someone else and you drag his subordinates along with him.

The problem is that the table represents both the tree structure (organizational chart) and the nodes (personnel who hold the jobs). So you have Chuck as a data item and as a link in a pointer chain. Two roles!

>>Apparently not any standard notion based on FDs, ... <<

This is Domain-Key Normal Form, which is a superset of the ones based on Functional Dependencies. Look up the name Fagin in the literature; I think the ACM has his original paper on their website or as a reprint for members. Halpin's semantic models (ORM, NIAM methods) are related, but slightly different and probably a little stronger still.

--CELKO-- Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Jan 20 2000 - 00:00:00 CET