Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Jan 2004
    Location
    London
    Posts
    8

    Unanswered: How to enforce complex business rules in an oracle database

    I am trying to enforce the following business rules in my database and am failing to find a way to enforce them. I would be very grateful if anyone could help me with this problem

    Supervisor needs to be allocated by a key date
    Supervisor cannot also be secondmarker
    Second marker needs to be from same school as supervisor Dissertation topic should be related to one of the curriculum groups
    Supervisor should be allocated from the curriculum group as the topic



    The tables in the database are as follows;
    --Constraints have been implemented separately using the Alter command
    ************************************************** **********
    Create table CurricDev_Groups (
    CurriculumDevelopment_id varchar2(10) ,
    CurriculumDevelopment_name varchar2(40));
    ************************************************** **********
    Create or replace type NonfTimeDates_item_type as object(
    Handout_date date
    ,Submission_date date
    ,Marking_date date
    ,ProposalSubmission_date date
    ,SupervisorAllocation_date date
    ,FirstGrade_submissionDate date
    ,SecondGrade_submissionDate date
    ,FinalGrade_submissionDate date);
    /
    Create type NonfTimeDates_item_table_type
    AS table of NonfTimeDates_item_type;
    /
    Create table KeyDates (
    Fulltime_code varchar2(10),
    DissertationModule_id varchar2(10),
    Handout_date date,
    Submission_date date,
    Marking_date date,
    ProposalSubmission_date date,
    SupervisorAllocation_date date,
    FirstGrade_submissionDate date,
    SecondGrade_submissionDate date,
    FinalGrade_submissionDate date,
    NonfTimeDates_item NonfTimeDates_item_table_type)
    Nested table NonfTimeDates_item STORE AS NonfTimeDates_item_table;

    ************************************************** **********
    Create table Diss_Modules (
    DissertationModule_id varchar2(10),
    DissertationModule_name varchar2(50),
    School varchar2(35));
    ************************************************** **********
    Create or replace type address_type as object
    (street varchar2 (25)
    ,city varchar2 (25)
    ,Post_code varchar2(9)
    ,country varchar2 (20))
    /

    Create type contactDetails_type as object
    (home_number varchar2(15)
    ,mobile_number varchar2(15)
    ,email_address varchar2(30)
    );
    /
    Create type contactDetails_varray_type as varray(10) of
    contactDetails_type;
    /

    Create table Students (
    Student_id varchar2(10),
    Student_fname varchar2(20),
    Student_lname varchar2(20),
    address address_type,
    contactDetails contactDetails_varray_type,
    DissertationModule_id varchar2(10),
    Date_of_Birth date,
    Status varchar2(15));
    ************************************************** **********
    Create table Dissertations(
    Dissertation_id varchar2(10),
    Dissertation_title varchar2(100),
    Dissertation_topic varchar2(50),
    DissertationModule_id varchar2(10));
    ************************************************** **********
    Create or replace type Staffaddress_type as object
    (street varchar2 (25)
    ,city varchar2 (25)
    ,Post_code varchar2(9)
    ,country varchar2 (20))
    /

    Create type StaffcontactDetails_type as object
    (Room_number varchar2(25)
    ,Extension_number varchar(3)
    ,email_address varchar2(30))
    /
    Create table StaffcontactDetails of StaffcontactDetails_type;
    /
    Create table Staff (
    Staff_id varchar2(10),
    Staff_fname varchar2(20),
    Staff_lname varchar2(20),
    address Staffaddress_type,
    StaffcontactDetails REF StaffcontactDetails_type SCOPE IS StaffcontactDetails,
    School varchar2(35),
    CurriculumDevelopment_id varchar2(10));

    ************************************************** **********
    Create table Dissertation_staff (
    Dissertation_id varchar2(10),
    Dissertation_topic varchar2(35),
    Student_id varchar2(10),
    Supervisor_id varchar2(10),
    SecondMarker_id varchar2(10),
    FirstGrade varchar2(2),
    SecondGrade varchar2(2),
    FinalGrade varchar2(2));

  2. #2
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    It is best to start any request with the version of Oracle and OS that you are using.

    You do not say if you are using Forms for a front end or how the data is entered into the DB. You could use table triggers in either case. If you are using Forms you could use a combo of Forms logic and triggers.
    NOTE: Please disregard the label "Senior Member".

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by Todd Barkus
    It is best to start any request with the version of Oracle and OS that you are using.

    You do not say if you are using Forms for a front end or how the data is entered into the DB. You could use table triggers in either case. If you are using Forms you could use a combo of Forms logic and triggers.
    I doubt if Forms can handle those ghastly object-based tables and REFs. I certainly can't (nor do I want to). I wish Oracle had never introduced them!

    Standard SQL allows for the creation of complex database constraints (called ASSERTIONS) that can validate rules like those above. But unfortunately, Oracle doesn't suport them (does any DBMS support them?) So you have 2 choices if you want to keep the rules close to the data:

    1) Database triggers, as Todd suggests. However, these can get nasty if the rules are complicated.

    2) Packaged procedures (APIs): i.e. encapsulate the business rules in procedures like "create_supervisor", and prevent any DML against the tables other than by calling these procedures.

  4. #4
    Join Date
    Jan 2004
    Location
    London
    Posts
    8
    Firstly thank you for the help.I have decided against using forms for the front end since they do not support objects. I had decided to use ASP for the front end. I am trying to enter the data using the back end just to test if the code is working. I will eventually move to ASP for data entry etc. I tried to use a trigger for one of the business rules but it failed. I think I'm failing to understand how to implement the rules into triggers.

    The following trigger is to ensure that the second marker is not also a supervisor but its not working.


    Create or replace TRIGGER Supervisor_SecondMarker
    AFTER INSERT OR UPDATE
    ON SecondMarker_id
    FOR EACH ROW
    DECLARE
    iSecondMarker_id integer;
    iSupervisor_id integer;
    xSupervisor cannot also be secondmarker EXCEPTION;

    BEGIN
    SELECT SecondMarker_id
    INTO iSecondMarker_id
    FROM Dissertation_Staff
    WHERE secondmarker_id = :new.secondmarker_id;


    IF (iSecondmarker_id <> iSupervisor_id) THEN
    RAISE xSecondMarker_CANNOT_ALSO_BE_SUPERVISOR;
    END IF;
    END;

  5. #5
    Join Date
    Jan 2004
    Location
    London
    Posts
    8
    I forgot to add i am using oracle 8i on Windows Server 2003

  6. #6
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    Is it not working or are you getting an error trying to compile?
    NOTE: Please disregard the label "Senior Member".

  7. #7
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    For one thing you are doing it "AFTER" insert. The data is already in the data base by then. Try a "BEFORE" insert. That way you can keep the "bad" data out.

    You did not put any exception handling in for "xSupervisor cannot also be secondmarker".

    I think what you are want to say is

    BEGIN

    select whatever
    where "this person is in the DB";

    {the select worked, uh oh that is bad}
    RAISE TRIGGER_FAILURE;

    EXCEPTION
    NO DATA FOUND { that is good}
    NULL; { let the insert proceed}
    END
    NOTE: Please disregard the label "Senior Member".

  8. #8
    Join Date
    Jan 2004
    Location
    London
    Posts
    8
    i'm getting errors when trying to compile. i'm convinced the syntax of the trigger is wrong somewhere.

  9. #9
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    Is the trigger as you posted it earlier?

    If so your EXCEPTION definition is wrong. Get rid of the spaces.

    Post your SQL and the error message. (Put them in same post for easy reference). That will be helpful.
    NOTE: Please disregard the label "Senior Member".

  10. #10
    Join Date
    Jan 2004
    Location
    London
    Posts
    8
    thank you Todd. i will try and implement what you have just told me into the trigger

  11. #11
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I think that should be more like this:

    Code:
    Create or replace TRIGGER Supervisor_SecondMarker
    BEFORE INSERT OR UPDATE
    ON SecondMarker_id
    FOR EACH ROW
    DECLARE
      iCount INTEGER;
    BEGIN
      SELECT COUNT(*)
      INTO iCount
      FROM Dissertation_Staff
      WHERE secondmarker_id = :new.secondmarker_id
      AND ROWNUM = 1;
    
      IF (iCount = 1) THEN
        RAISE_APPLICATION_ERROR(-20001,'SecondMarker CANNOT ALSO BE SUPERVISOR;
      END IF;
    END;
    (It is not a good idea to declare exceptions and then raise them to the caller, as you will just get "undefined exception" rather than a useful message).

    As a matter of interest, are you developing this as an exercise in using object features, or is this a "real" system? IMHO, object-based tables and REFs have no place in a production database. They are toys. Anybody disagree with that?

  12. #12
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    Tony,
    "Anybody disagree with that?". Sounds like somebody wants fight. :-) Or maybe it is just my "mood". I for one do not disagree, but that is because I know virtually nothing about object tables and such. I am sure there is some theorist at Oracle that disagrees. We learn by doing, maybe with enough practice Oracle will get it right.

    I assume your distaste relates to performance (or lack there of)?
    NOTE: Please disregard the label "Senior Member".

  13. #13
    Join Date
    Jan 2004
    Location
    London
    Posts
    8
    I am developing this as an exercise in using object features. Its for a university assignment of which they have asked for the maximum use of objects. Thanks a lot for your help

  14. #14
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    Man, I hate doing people's homework for them. Hopefully our help is just a small portion of the total project. Will Tony (Andrews) and I (I think you can figure out my name) get references on your bib page? :-) If so send me a copy. I have never been published before. :-)
    NOTE: Please disregard the label "Senior Member".

  15. #15
    Join Date
    Jan 2004
    Posts
    370
    I've always felt that database objects were mainly marketing hype.
    Just like the Network Computer, Information Superhighway, Downsizing, Rightsizing, Y2K, (Grid Computing)?

    Are object based tables widely used?
    Has anybody blazed a trail and taken the hit on these?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •