Forum Stats

  • 3,784,163 Users
  • 2,254,899 Discussions
  • 7,880,717 Comments

Discussions

Implement a model into database

bebedor_1
bebedor_1 Member Posts: 4
edited Apr 26, 2014 6:25PM in SQL & PL/SQL

Hi,

I have the below model.

  • A parliament can have a minimum of 150 members and a maximum of 200 members.
  • A parliament belongs to a state.

  • A state can have 0 or 1 parliament.
  • A member works for only one parliament.

image

I don't know how to implement the “150..200” constraint. That's an exercise for my academic training. Our instructor advised us to use PL/SQL (trigger, procedure and function).

I think this model is wrong. I can understand the business logic which require a minimum of 150 members per parliament. However, in technical logic, a new parliament must be entered in the database without members. So, the cardinality should be “0..200”.

Do you know how to implement the constraint which require a minimum of 150 members per parliament ?

Thanks for your reply.

Answers

  • Hoek
    Hoek Member Posts: 16,076 Gold Crown

    Mention your database version. The result of : select * from v$version;

  • bebedor_1
    bebedor_1 Member Posts: 4

    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

    PL/SQL Release 11.2.0.1.0 - Production

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,433 Red Diamond

    Hi,

    You could do that with constraints, but it's a bad idea.  Here's how: Add 150 NOT NULL columns to the parliament table, each with a foreign key constraint referencing the member table.  To make sure you don't have any duplicates, add a CHECK constraint to each one, to make sure it's less than the next one.  How many problems can you find with this approach?  Discuss amongst yourselves.

    As your instructor said, triggers (along with constraints) are a better way than constraints only for enforcing this requirement.

    One way to do that is to add a member_count column to the parliament table, recoding how many members that parliament has.  A trigger on the member table can keep this  figure up to date.

    Even if a new parliament is added before any of its members, you can have deferrable constraints, that are only enforced when the transaction is committed.   Whenever you added added a new parliament, you would add the minimum number of members later in the same transaction.


  • This is the same issue as your other thread:

    https://community.oracle.com/thread/3552477

    I have the below model.
    
    
    A parliament can have a minimum of 150 members and a maximum of 200 members.
    A parliament belongs to a state.
    
    
    
    A state can have 0 or 1 parliament.
    A member works for only one parliament.
    
    
    I don't know how to implement the “150..200” constraint. That's an exercise for my academic training. Our instructor advised us to use PL/SQL (trigger, procedure and function).
    I think this model is wrong. I can understand the business logic which require a minimum of 150 members per parliament. However, in technical logic, a new parliament must be entered in the database without members. So, the cardinality should be “0..200”.

    It is you, not the model, that is wrong. Why must  a new parliament be entered without members? You can enter BOTH the new parliament and its members at the same time as part of ONE transaction.

    As I said in my reply to your other thread if you want to perform 'set' processing you need to do it in code.

    1. begin a new transaction

    2. create a new parliament

    3. create 150-200 members for that new parliament

    4. commit/end the transaction

    What is the problem?

    You control a transaction by writing code. In that code you control the start of the transaction, the contents (DML) and the end of the transaction.

  • You could do that with constraints, but it's a bad idea.  Here's how: Add 150 NOT NULL columns to the parliament table, each with a foreign key constraint referencing the member table.  To make sure you don't have any duplicates, add a CHECK constraint to each one, to make sure it's less than the next one.
    
    

    Not just a bad idea but a HORRIBLY DENORMALIZED data model. What about the other potential 50 members? Where are the columns for those?

    As your instructor said, triggers (along with constraints) are a better way than constraints only for enforcing this requirement.

    I don't see where the instructor said any such thing. All I see is that OP said this:

    Our instructor advised us to use PL/SQL (trigger, procedure and function).

    That doesn't say, or imply, that 'triggers (along with constraints) are a better way' to do that.

    One way to do that is to add a member_count column to the parliament table, recoding how many members that parliament has.  A trigger on the member table can keep this  figure up to date.

    No - a trigger can NOT do that. Not in a multi-user Oracle environment. Triggers can't see uncommitted changes being made by other users and they can't query the table the trigger is defined on. So they not only 1) can't get a count but 2) any count they get does not necessarily reflect the reality that will exist if others users commit their transactions and change the count.

This discussion has been closed.