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


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

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

  1. #1
    artnamus Guest

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


    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.





  2. #2
    Rune Bivrin Guest

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

    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.
    >
    >
    >
    >
    >



  3. #3
    artnamus Guest

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


    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.
    >>
    >>
    >>
    >>
    >>

    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center