Deleting

To maintain the integrity of the DBMS a deleting a record is not possible under any circumstance. The only time the user can "delete" a record is given by the constraint below:

1. Record to delete is created today.
2. Record to delete is dated today.

If the record to be deleted meets the two criteria mentioned above, then that record may be deleted. It is obvious that this is more of an "undo" than a delete; following this, henceforth it shall be referred to as such.

Updating

Furthermore, updating a table's column is not possible under any circumstance. All records created are final (of course, as mentioned above, undoing its creation is possible).

The only instance where updating is necessary is for the Retrieved column of the Supply_Record. Since the Retrieved Boolean column starts as false, but it can be updated to true. However, going back from true to false is not possible (but again, undoing the change from false to true is possible).

Inserting

As evident, majority of the DBMS is reliant on the dates of the data.

It is assumed and agreed upon that the users will track the tables daily (as the DBMS is design in such way). However, wiggle room will be provided to the user by allowing them to insert records in-between two dates or a missed day of recording.

But, the developers emphasize that this feature is only to be used for instances where the data is not recorded for that day. The database is designed such that all data inserted for that day is from that day, not another day. In other words, this feature is meant only to back-fill missed data, not to introduce data retroactively from unrelated days.

We introduce some special case for inserting for Flock_Details and Supply_Record.

For Flock and Supply_Type the ordinary rules for insertion apply. That is, the constraints must be followed before insertion.

Flock_Details: Inserting In-Between two Dates

It is possible for the user to insert a Flock_Detail (henceforth, FD) in-between two pre-existing FDs.

Recall that for FD to be inserted, the user will first pick the Flock. Once done, the user will be able to insert an FD anywhere in the range of that Flock as long as it is

• Within the scope of that Flock; and
• Does not overlap with another FD or a different Flock.

Hence, inserting an FD in-between two dates follows this same idea: the user can only insert an FD as long as it is within scope of the Flock and does not overlap with another FD of that Flock. Furthermore, the Depleted_Count column must allow for all pre-existing FDs to make sense and exist.

That is, given the FD table of the Flock with 10 as the Starting_Count :

FD_Date	Depleted_Count
X	0
X+1	0
X+2	0
X+4	3
X+5	0

Notice that FD_Date at X+3 is missing, hence the user can insert the missing FD in-between X+2 and X+4. However, the Depleted_Count of that FD must allow for the depleted count of all FD_Date =/= X+3 to make sense. That is,

	\sum_{i \,:\, \text{FD\_DATE}_i \ne \text{X+3}} \text{Depleted\_Count}_i + \text{Depleted\_Count}_{\text{X+3}} \leq \text{Starting\_Count}

It follows that the possible Depleted_Count of the FD to be inserted is [0,7].

Supply_Record: Inserting AFTER the most recent Supply_Record

We can only insert a Supply_Record (henceforth, SR) only after the most recent SR.

If we have an $\texttt{SR}_i$ with $\texttt{SR}_j.\texttt{date}$ = $x$ we can only insert another $\texttt{SR}_j$ if $\texttt{SR}_j.\texttt{date}$ = $y$ where $x.\texttt{before}(y)$

Scope of Supply_Records

A scope of supply record is first filtered by its SR.Supply_Type_ID; that is, every SR in a scope has the same Supply_Type_ID.

A scope starts at the very first SR created that is after a Retrieved Date. If no Retrieved Date exists, the scope starts at the first SR.

And the scope ends at the selected date of the user.

Example,

SR	(Supply_Type_ID, Date, Retrieved)
X	(1, k, false)
X+1	(1, k + 1, false)
X+2	(1, k + 2, true)
X+3	(1, k + 3, false)
X+4	(1, k + 4, false)

• If the user checks the SR scope at date $k+1$, the range is $[X, X+1]$.
• If the user checks the SR scope at date $k+4$, the range is $[X+4, X+5]$

Another example,

SR	(Supply_Type_ID, Date, Retrieved)
X	(1, k, false)
X+1	(2, k + 1, false)
X+2	(2, k + 2, false)
X+3	(1, k + 1, false)
X+4	(1, k + 2, false)
X+5	(1, k + 3, false)
X+6	(1, k + 10, true)
X+7	(2, k + 10, true)
X+8	(2, k + 11, false)
X+9	(1, k + 11, false)
X+10	(1, k + 12, false)
X+11	(1, k + 13, false)

• If the user checks the SR scope at date $k+2$, we get two scopes. The first scope is $[X, X+4] -$ { $X+1, X+2$ } since these dates have Supply_Type_ID = 2. The second scope is $[X+1, X+2]$.
• If the user checks the SR scope at date $k+1$, we again get two scopes: $[X]$ and $[X+1] -$ { $X+1$ }
• If the user checks the SR scope at date $K+100$, we again get two scopes: $[X+8]$ and $[X+9, X+11] - $ { $X+8$ }.