Database Reference
In-Depth Information
of each project aggregated per year and month, respectively in R 2 and R 3 . In particu-
lar, the costs in R 1 and R 2 took into account both the equipments and the pays of the
employees, while in R 3 only the sums of the pays of the employees were reported.
Consider the following constraints:
κ a : for each project, the yearly cost for equipments and salaries must be greater
than or equal to the sum of the monthly costs for salaries in the same year;
κ b : for each year, the overall cost for equipments and salaries must be equal to the
sum of the yearly costs for equipments and salaries for the different projects.
The above constraints can be expressed as follows::
(
,
, )= ⇒ χ
(
,
) − χ
(
,
)
κ
a : R 2
x
y
x
y
x
y
0
3
4
where:
χ 3 (
x
,
y
)=
R 2 ,
Cost
, (
Pro ject
=
x
Year
=
y
)
χ 4 (
x
,
y
)=
R 3 ,
Cost
, (
Pro ject
=
x
Year
=
y
)
κ b : R 1 (
y
, )= ⇒ χ 5 (
y
) − χ 6 (
y
)=
0
where:
χ
(
y
)=
R 1
,
Cost
,
Year
=
y
5
χ 6
(
y
)=
R 2
,
Cost
,
Year
=
y
.
Example 2.5. Consider the database scheme consisting of the relation schemes
R 1 (
.In R 1 , each depart-
ment is associated with a research area, and, in R 2 , each research project is associ-
ated with the department in which it was developed and its overall costs. Consider
the following integrity constraint: for every project developed in a department of
the 'database' area, the costs must be less than or equal to 100 K . This constraint is
expressed by the following aggregate constraint: R 1 (
Department , Area
)
, and R 2 (
Project , Department , Costs
)
x
,
'database'
)
R 2 (
y
,
x
, )=
χ (
y
)
100 K , where
χ (
y
)=
R 2 ,
Cost , Project
=
y
.
Example 2.6. Consider a relation instance over the relation scheme Employee( Name ,
Salary, Bonus) , where both Salary and Bonus are measure attributes. Consider
the constraint requiring that a total amount of 30K of bonuses has been dis-
tributed among the employees receiving a salary greater than 5K . We can ex-
press this constraint by means of the aggregate constraint
= ⇒ χ () =
30 K , where
χ () =
Employee, Bonus,
(
Salary
>
5 K
)
returns the sum of bonuses for the em-
ployees whose salary is greater than 5 K .
Observe that, according to Definition 2.1, the conjunction of atoms on the left-
hand side of an aggregate constraint can be empty. Thus, an expression of the form
= ⇒ χ (
k q are constants, is an aggregate constraint,
whose semantics derives from assuming the left-hand side of the implication true.
In the following, for the sake of readability, we will omit the symbol '
k 1 ,...,
k q )
K , where k 1 ,...,
=
' for this
 
Search WWH ::




Custom Search