Re: Dreaming About Redesigning SQL
Date: 26 Oct 2003 06:10:52 GMT
Message-ID: <3523794.1067146772_at_dbforums.com>
Thanks for the example Marshall. Really - it helped me understand a thing or two. Oh - and sorry for the top post (do you consider it bad netiquette?), but I can assure you I did read it all. I've always considered the 'integrity checks' you describe to be 'input validation' - but maybe I can use either term depending on the context...
I'm changing my view of what a DBMS is.
I have become used to developing applications within Pick - using PickBasic - and thinking of my file IO as accessing the database (DBMS).
I've thought of the DBMS as that which handles the actual data access, manages linkages between frames in groups and the pool of available unused frames, automatically fires triggers on updates, handles item and groups locks, etc., etc. It seems to me that there is a hell of a lot being managed for me - so that I can simply tell it I want to read this data, write that and delete the other without having to concern myself with the nitty gritty.
Now I'm beginning to see that it's all part of the whole. AQL (the Pick enquiry language) has no function - no identity - outside of Pick, and nor have any of the other Pick-specific tools including PickBasic. They're all part of the whole - part of the Pick DBMS. When I talk about validation on input when a name/value pair set hits my Pick application from a web-server, that *is* an 'integrity constraint' as a function of the DBMS after all. Never mind that to me it's always *seemed* like validation on *input*, it is in fact input to the application that is running *as part of the DBMS* isn't it?
If I'm right now - and was wrong before - I have to apologise to the group for the bum steer. It seems that integrity constraint checking *is* a central function of the Pick DBMS - I just didn't realise it. It's not declared in exactly the same way as it is in SQL-relational, although it certainly doesn't lack power - there's not a lot we can't do in PickBasic. Personally, I'm grateful for the enlightenment. Thank you very much.
Cheers
Mike.
Originally posted by Marshall Spight
> "Mike Preece" <michael_at_preece.net> wrote in message news:1b0b56-
> 6c.0310240634.24c30c30_at_posting.google.com"]news:1b0b566c.0310240634.2-
> 4c30c30_at_posting.google.com[/url]...
> > Can you tell me what exactly is the function of the central
> integrity
> > enforcement in the DBMS for a single table with no parent and
> no
> > child? What is required to be done in order to maintain
> integrity in
> > this case?
>
> Sure. Note that the complexity of the schema is independent
> of the need to enforce integrity centrally. (Although it is
> likely that the more complex the schema, the more
> integrity rules there will be.)
>
> Also note that you've left it up to me to come up with a
> specific example, so I will.
>
> Let's say you've got a hotel room booking table that
> participates in no relationships with any other table.
> (This example is somewhat artificial, but I'll try to
> make it as real as possible.)
>
> Uh, attributes, let's see:
>
> create table bookings
> (
> RoomNumber int, -- the room number at the hotel
> CreditCardNumber varchar(16), -- the credit card number to reserve
> the room
> Name varchar(200), -- the customer's name
> StartDate datetime,
> EndDate datetime,
> Occupants int, -- together with StartDate, determines price
> AmountCharged float -- float is a terrible choice for currency in the
> real world
> )
>
> Integrity constraints are relevant everywhere we have
> an application that *updates* the database. If we
> just want to read, no big deal.
>
> So let's say we have a big Java swing application for
> the front desk, a PHP web application for remote
> booking by our two booking agents, and a Python
> biller. (I am not necessarily endorsing any of these
> choices, but they do happen in the real world.)
>
> Okay, so the most obvious kind of integrity constraint
> is the domain constraint for the columns. We want to
> ensure that the only room numbers that get used are
> actually ints. We wouldn't want to have a row with
> "hello world" as the room number, would we? That
> would certainly count as data corruption. Etc. etc.
> for the other columns: "hello world" is also problematic
> as a StartDate (although maybe it could be a legal Name. :-)
>
> Other things we want to verify that are also column constraints:
>
> Legal room numbers, which are in the range [100-130] or
> [200-220]. So you have a constraint
>
> CHECK (RoomNumber >= 100 AND RoomNumber <= 130) OR
> (RoomNumber >= 200 AND RoomNumber <= 220)
>
> You also want to be sure you have a valid credit card number.
> There are specific algorithms for this you can get from
> the credit card companies if they trust you.
>
> CHECK ValidCreditCardNumber(CreditCardNumber)
>
> and for good measure
>
> CHECK !ReportedStolen(CreditCardNumber)
>
> You want to be sure the name is filled in. (Omitted.)
>
> You want to be sure the booking is sensible. Now we
> move into table constraints, because more than one
> column is mentioned. Just roughly:
>
> CHECK EndDate > StartDate
> CHECK EndDate > Now()
>
>
> Also,
>
> CHECK Occupants > 0
> CHECK (
> RoomNumber >= 100
> AND
> RoomNumber <= 130
> AND
> Occupants < 4)
> OR (
> RoomNumber >= 200
> AND
> RoomNumber <= 220
> AND
> Occupants < 6)
> -- upstairs rooms are bigger
>
> Note that this most recent constraint is redundant
> with the well-formed room constraint; that's okay.
>
> CHECK AmountCharged >= 0. -- or whatever.
>
> So, these declarative constraints are pretty nice.
> We just say what we want to make sure of, and
>
> If we don't have these enforced centrally, then
> we have to write that code in Java, again in PHP
> for the web app, and again in Python for the biller.
> You can't reuse the code, because each app is
> written in a different language, and each app
> has different update usage. (The booking
> app inserts; the biller updates, etc.)
>
> If the constraints change, we have to remember
> where each of them is enforced, and make sure
> they are all always consistent, or else ouch.
>
> Also, if we write them procedurally, we have
> to be sure to put the code for the check in
> the right place, each time. If we are sleepy
> and put the check immediately after the insert
> instead of immediately before, then ouch again.
> If we miss an update, then ouch again. Any of
> these mistakes is a hole though which data
>
> I hope this example has been very easy
> to follow; I tried to make it quite simple
> and yet at least have some obvious
> degree of utility.
>
> Comments welcome.
>
>
Marshall
-- Posted via http://dbforums.comReceived on Sun Oct 26 2003 - 07:10:52 CET