Can these constraint be implemented in an RDBMS ?

In another thread I was asked to produce an example which can not be implemented with RDBMS constraints. I have given this some thought.
This is a long mail, so if you do not want to 'waste' your time, you should not read on or attempt a solution. The length was because I wanted to be precise.
(I tried to be precise, I do not know if I have succeeded in that).

Thanks for any participation,
Ben Brugman

So I have tried to come up with an example according to this request. I came up with an example, which I think is difficult to implement. (But I do not know if I have succeeded).

Below I have written an example for an imaginairy customer who runs a special kind of financial institution. The request and requirements are written down in terms the imaginairy customer understands. This should be implemented, the customer has specifically stated that no extra constraints are to be implemented without his consent. So his customers do not get any extra limitations not given by the requirements of him. Because it is an accounting problem the amounts have to be kept exact to the currency the customer is using.
(cents or pennies). The total amount of money does not
exceed 100 miljoen of the currency. (There is only one currency to be accounted for).

The implementation has to be done in Oracle or SQL-server. The customer wil make a choice on that later on. The customer preveres a solution which would work in both, so that he has the freedom to switch the RDBMS, but if that is not possible he will agree to one RDBMS to be choosen by him at a later point in time.

Because the customer employs some programmers who are total morons in respect to constraints, but deliver perfect programs in any other respect, the customer has stated that the RDBMS should handle al the constraints.

I have tried to be precise in the specification (and language used). But if there are questions, I'll try to anwser them. If something is understood different from what I did mean, I'll try to reformulate.

If something is wrong with 'the rules' of the game. For example I am not allowed to use the specified programmers, please point this out to me. We might find a solution for 'the rules' then. I did specify Oracle (and/or) SQL-server because they are existing implementations and I have access to both. I specified the choice to be made later because I think that the solution should be RDBMS independant, if possible.
The rule an account should be positive or a set of accounts should be positive
is database constraint (this is my thought). If this is not the case please point
this out to me before attempting a solution.

Here follows the example.

An institution does have customers,
customers can have accounts.

An account belongs to one customer.
An account holds (represents) an amount of money. In general the amount of money should be positive.

There are sets.
A set can consist of one or more accounts.
(Ok an empty set is allowed as wel, but has no meaning.)
In general the amount of money in a set should be positive. But in an individual account the amount of money can be negative.

The minimum amount of a set might be specified.
(If not specified it is zero).

(Zero is considered positive).

The model might be simple,
a table of customers,
a table of accounts
and a table of sets.
(Or a table of sets and an intersection table between the
set table and the account table).

There is a program which can alter the amount of money in accounts. The program does not implement any of the constraints and is not build to handle any constraints. The program can add money to accounts, take money from accounts or move money between accounts. This program is made by the programmers who are morons when it comes to constraints but deliver perfect programs otherwise.

No additinal constraints should be added without consulting the customer who presented the above problem.

Ben Brugman.

Possible solution for the tables.
(This is supplied to illustrate the problem not to
give a solution.).

Customer
 name
 number

Account
 accountnumber
 customernumber
 amount

Set
 setname
 MinimumAmount (can be NULL)

SetAccount
 setname
 accountnumber Received on Mon Mar 01 2004 - 22:16:33 CET