Re: The Practical Benefits of the Relational Model

From: Paul G. Brown <paul_geoffrey_brown_at_yahoo.com>
Date: 28 Oct 2002 17:09:56 -0800
Message-ID: <57da7b56.0210281709.262a4094_at_posting.google.com>

"Paul Vernon" <paul.vernon_at_ukk.ibmm.comm> wrote in message news:<ap8j1f$i2q$1_at_sp15at20.hursley.ibm.com>...
> . . so all you need
> do is show one case where transactions are *required*.

  CREATE TABLE Emps (

     Id     INTEGER      NOT NULL  PRIMARY KEY,
     Name    VARCHAR(32) NOT NULL

  );

  CREATE TABLE Dept (

     Id     INTEGER      NOT NULL  PRIMARY KEY,
     Name   VARCHAR(32) NOT NULL

  );

  CREATE TABLE Works_For (

     Emp   INTEGER     NOT NULL  REFERENCES Emps ( Id ),
     Dept  INTEGER     NOT NULL  REFERENCES Dept ( Id ),
     Start DATE        NOT NULL,
     Finish DATE       DEFAULT FOREVER() -- This is a UDF.

  );

   -- 
   -- Can't do this now, but let's posit another rule
   -- 

   CREATE RULE Everyone_must_Work
   ON SELECT Raise_Exception_w_Rollback( E.Name || ' is a lazy bum!' )

        FROM Emp E 
       WHERE NOT EXISTS ( SELECT 1 
                            FROM Works_For W 
                           WHERE E.Id = W.Emp
                             AND TODAY NOT BETWEEN W.Starts AND W.Finish );
 

   BEGIN TRANSACTION;    INSERT INTO Emps VALUES ( 1, "Me" );
   INSERT INTO Works_For (Emp, Dept, Start ) VALUES ( 1, 1, TODAY );

   END TRANSACTION;     The business rule holds that all Emps must work for someone. The purpose    of the transaction is to define an atomic unit of work: it either completes    or it fails.

    All of your examples, Paul, talk about Isolation and Consistency of a    single query but Atomicity and Durability are also part and parcel of the    transaction concept. Having a mechanism to specify the boundaries of a    unit of work (after which the database must be consistent) is necessary    in most business applications.

     Call it what you will, but the concept of a 'user-specified unit of work'    seems to me to be a necessary feature of any data language. At points    within a multi-query operation the database can be inconsistent, but not    at the end.

     KR

              Pb Received on Tue Oct 29 2002 - 02:09:56 CET