Static or dynamic integrity constraints

[mathmari]

Hey!

Classify the following statements according to static or dynamic integrity constraints, based on the database that is to be created in the thread https://mathhelpboards.com/threads/creating-tables-integrity-constraints.29157/.

1. A customer's discount may not exceed 50 percent.
2. The discount of a foreign customer may not be more than 30 percent.
3. A customer's discount may be increased by a maximum of 10 percent within a year.
4. The status of a paid order must never change again.
5. The average discount for all customers must not exceed 30 percent.
6. The status of an order may only change to "delivered". The status of a delivered order only in "paid".
7. The total value of all products in the same warehouse cannot exceed 1 million euros.
8. There has to be at least one product
9. The invoice amount for an order is the product of the price and the ordered quantity of the ordered product minus the customer discount.
10. A customer's balance is the (negative) sum of the invoice sums for all orders placed by the customer that have not yet been paid for.
11. A customer's discount must never be reduced.
12. Orders will no longer be accepted from customers whose balance is below -100,000 euros.I haven't really understood how we know if one constraint is static or dynamic. Is a static constraint something that holds because of the description of an attribute of a table and a dynamic constraint is something that holds because we decided these constraints ? :unsure: Would it be then as follows ?

1 : dynamic integrity constraint
2 : dynamic integrity constraint
3 : dynamic integrity constraint
4 : static integrity constraint
5 : dynamic integrity constraint
6 : static integrity constraint
7 : dynamic integrity constraint
8 : static integrity constraint
9 : static integrity constraint
10 : static integrity constraint
11 : dynamic integrity constraint
12 : dynamic integrity constraint

:unsure:

 

[I like Serena]

Hey mathmari!

I found a definition somewhere that said:

Constraints also can be distinguished as static (or state) and dynamic integrity constraints. A static constraint expresses state-independent properties that must hold at any state of the database. It depends only on the current state, independently of any previous states of the database (Plexousakis, 1991). An example of a static constraint is the condition "an employee's salary must be less than her manager's". A dynamic constraint allows expressing conditions over (usually time-ordered) sequence of two or more database states, e.g. the condition "an employee's salary must never decrease".​

Perhaps we can use that definition? Or do you have another definition in your notes? :unsure:

 

[mathmari]

In my notes there is not a really definition... It is shown with examples :

So from the definition you quoted a static constraint is something that is true in general and a dynamic constraint is something that holds at the specific case? :unsure:

 

[I like Serena]

As I understand it, a static constraint is independent of time, and a dynamic constraint is dependent on time.
Put otherwise, a static constraint is a constraint on the state of the database as is, and a dynamic constraint limits how the state of the database can change over time.

 

[mathmari]

With this definition do we get the following ?

1 : dynamic integrity constraint
2 : dynamic integrity constraint
3 : dynamic integrity constraint
4 : static integrity constraint
5 : dynamic integrity constraint
6 : static integrity constraint
7 : dynamic integrity constraint
8 : static integrity constraint
9 : static integrity constraint
10 : static integrity constraint
11 : dynamic integrity constraint
12 : dynamic integrity constraint

:unsure:

 

[I like Serena]

1. A customer's discount may not exceed 50 percent.

Discount will be something that is stored in a table somewhere.
Its required state is that it must be less than 50 percent.
This does not involve a future or a previous state.
So I believe it is static instead of dynamic.

2. The discount of a foreign customer may not be more than 30 percent.

Same here.

3. A customer's discount may be increased by a maximum of 10 percent within a year.

This one does involve 2 different states and how they are connected over time.
So I believe this one is indeed dynamic.

4. The status of a paid order must never change again.

The state of the attribute can apparently be either NULL or it can have some value.
Both states are valid, which means it is not static.
Instead we have a constraint how it may not be changed over time into a new state.
I believe that is dynamic instead of static.

 

[mathmari]

I am not really sue if I have understood completely the two meanings. What does it mean that a constraint depends on time or not? If after some time this condition can change? Or how can we consider that? :unsure:

Discount will be something that is stored in a table somewhere.
Its required state is that it must be less than 50 percent.
This does not involve a future or a previous state.
So I believe it is static instead of dynamic. Same here.

So here we have something that doesn;t depend ontime, that's why these are static? :unsure:

This one does involve 2 different states and how they are connected over time.
So I believe this one is indeed dynamic.

Here we have something that happens within a year, so it depends on the time, that's why it is dynamic? :unsure:

The state of the attribute can apparently be either NULL or it can have some value.
Both states are valid, which means it is not static.
Instead we have a constraint how it may not be changed over time into a new state.
I believe that is dynamic instead of static.

Here we have that after some time the status cannot change, does this then mean that it depends on time? :unsure: