Database «Recycled materials company»

The recycling company owns several buy-back centers (further referred to as points) for collection of recyclable materials. Each of these centers receives funds to be paid to the recyclables. In fact, the diagram shows two databases. Exercises referring to this schema use only one pair of tables (with the suffix «_o» in the table name, or without it).

The tables Income_o and Outcome_o have a primary key consisting of a pair of attributes {point, date} – point number and date the funds were received/paid on. Such a key reflects the case when data about fund reception and payments by a specific buy-back center is recorded no more than once a day.

Note

The values in the column date don’t contain the time part, e.g., 2001-03-22 00:00:00.000. Unfortunately, using the datetime data type for this column may cause confusion, since it’s obvious that registering time won’t allow to rule out entering multiple records having the same date (and point number) yet a different time. This flaw caused by the lack of dedicated data types for date and time values has been overcome in SQL Server 2008. When using SQL Server 2000, correct data input can be ensured by using, say, the following constraint (CK_Income_o):

ALTER TABLE Income_o ADD
CONSTRAINT PK_Income_o PRIMARY KEY
(
    [point],
    [date]
),
CONSTRAINT CK_Income_o CHECK
(
    DATEPART(hour,[date]) + DATEPART(minute,[date]) +
    DATEPART(second,[date]) + DATEPART(millisecond,[date]) = 0
)

This constraint (the sum of hours, minutes, seconds, and milliseconds must be zero) prevents from setting a time other than 00:00:00.000. With it, the primary key for this table indeed ensures there is only one record per day for each point.

The Income_o (point, date, inc) table contains information on the funds received by the points (inc). Another table with a similar structure, Outcome_o (point, date, out), is used for recording payments to the suppliers (out).

The second pair of tables — Income (code, point, date, inc) and Outcome (code, point, date, out) — simulate the case with funds receipts and payments possibly occurring several times a day. It should be noted that, if these last two tables store the date without time (which is the case), there is no combination of attributes that could be used as a natural primary key, since the sums paid/received may be the same, as well. Therefore, we need to either record the time as well or add a surrogate key. or record in the last table only date without time (that occurs), then no natural combination of attributes can be used as the primary key, since the amount of money can be the same. Therefore, we must take into account the time, or add an artificial key. We used the second option, adding the integer code column just to ensure the records in the table are unique.