SQL server7.0 - Implementing inclusion dependency ( participation constraints)

[artnamus]

consider a many-to-many relation "visits"
between "Doctor" and "Patient"
say i) Doctor(RegNo, ...)
-----
ii) Patient(BedNo, ...)
-----
ii) visits(RegNo,BedNo, ...)
----- -----
Now consider that we require full participation on behaif of Doctors
i.e. each Doctor tuple must participate in the visit relationship at least
once(each doctor must have a patient).
How to ensure this using constraints...
Soln1:
------
in Doctor state as follows...
primary key (RegNo)
foreign key (RegNo) references visits(RegNo)
...
and
in visits
primary key (RegNo,BedNo)
foreign key (RegNo) references Doctor(RegNo)
...
This technique has the so called "chicken and the egg " problem solved by
deferring the checking (key constraints and referential) till the end of
transactions.
Soln2:
-----
Use deferred assertions.
Soln3:
------
modify the ERDiagram and schemas to avoid such situations.

Questions:
-----------
1) If there are any other possible solutions please state them.
2) How can i use assertions( constraints over the database as a whole - avoiding
table dependency) in sql server 7.0( no create assertion...)
3) can its equivalent be done only using Check( ...) constraints in sql server
7.0?
4) Can trigger play any role in solving the problem in sqlserver7?
5) please discuss the best way to implement it in sql server 7.

[Rune Bivrin]

Nice try. You should do your own homework, I think. Or learn how to
rephrase the question to mask the origins

However, there is no way that I'm aware of you can force a 1-1..n
relation in SQL Server using the schema, CHECK constraints or triggers.
It would be nice to have ON COMMIT triggers for this kind of problem.

Rune Bivrin

"artnamus" <artnamus@rediffmail.com> wrote in
news:3d7d1288$1@10.1.10.29:

>
> consider a many-to-many relation "visits"
> between "Doctor" and "Patient"
> say i) Doctor(RegNo, ...)
> -----
> ii) Patient(BedNo, ...)
> -----
> ii) visits(RegNo,BedNo, ...)
> ----- -----
> Now consider that we require full participation on behaif of Doctors
> i.e. each Doctor tuple must participate in the visit relationship at
> least once(each doctor must have a patient).
> How to ensure this using constraints...
> Soln1:
> ------
> in Doctor state as follows...
> primary key (RegNo)
> foreign key (RegNo) references visits(RegNo)
> ..
> and
> in visits
> primary key (RegNo,BedNo)
> foreign key (RegNo) references Doctor(RegNo)
> ..
> This technique has the so called "chicken and the egg " problem solved
> by
> deferring the checking (key constraints and referential) till the end
> of
> transactions.
> Soln2:
> -----
> Use deferred assertions.
> Soln3:
> ------
> modify the ERDiagram and schemas to avoid such situations.
>
> Questions:
> -----------
> 1) If there are any other possible solutions please state them.
> 2) How can i use assertions( constraints over the database as a whole
> - avoiding table dependency) in sql server 7.0( no create
> assertion...) 3) can its equivalent be done only using Check( ...)
> constraints in sql server 7.0?
> 4) Can trigger play any role in solving the problem in sqlserver7?
> 5) please discuss the best way to implement it in sql server 7.
>
>
>
>
>

[artnamus]

thanx, (for the help )
but there isnt one ON COMMIT trigger - is it? ...
well, this is what i have found till now - triggers , constraints in sql
server 7 isnt deferrable( have to explicitly enable or disable these using
ALTER TABLE ) and so there is a lot of problem.
[ by deferable i mean what Rune said - like "ON COMMIT" - the triggers are
fired at the end of transactions if set deferred - otherwise they act normally
]
if anyone has some more ideas that will certainly be of help ( not for homework
- just for fun and learning and discussing ofcourse ) )
plz do reply and write

Rune Bivrin <rune@bivrin.com> wrote:
>Nice try. You should do your own homework, I think. Or learn how to
>rephrase the question to mask the origins
>
>However, there is no way that I'm aware of you can force a 1-1..n
>relation in SQL Server using the schema, CHECK constraints or triggers.

>It would be nice to have ON COMMIT triggers for this kind of problem.
>
>Rune Bivrin
>
>"artnamus" <artnamus@rediffmail.com> wrote in
>news:3d7d1288$1@10.1.10.29:
>
>>
>> consider a many-to-many relation "visits"
>> between "Doctor" and "Patient"
>> say i) Doctor(RegNo, ...)
>> -----
>> ii) Patient(BedNo, ...)
>> -----
>> ii) visits(RegNo,BedNo, ...)
>> ----- -----
>> Now consider that we require full participation on behaif of Doctors
>> i.e. each Doctor tuple must participate in the visit relationship at
>> least once(each doctor must have a patient).
>> How to ensure this using constraints...
>> Soln1:
>> ------
>> in Doctor state as follows...
>> primary key (RegNo)
>> foreign key (RegNo) references visits(RegNo)
>> ..
>> and
>> in visits
>> primary key (RegNo,BedNo)
>> foreign key (RegNo) references Doctor(RegNo)
>> ..
>> This technique has the so called "chicken and the egg " problem solved
>> by
>> deferring the checking (key constraints and referential) till the end
>> of
>> transactions.
>> Soln2:
>> -----
>> Use deferred assertions.
>> Soln3:
>> ------
>> modify the ERDiagram and schemas to avoid such situations.
>>
>> Questions:
>> -----------
>> 1) If there are any other possible solutions please state them.
>> 2) How can i use assertions( constraints over the database as a whole
>> - avoiding table dependency) in sql server 7.0( no create
>> assertion...) 3) can its equivalent be done only using Check( ...)
>> constraints in sql server 7.0?
>> 4) Can trigger play any role in solving the problem in sqlserver7?
>> 5) please discuss the best way to implement it in sql server 7.
>>
>>
>>
>>
>>
>