Evergreen Databases: A Practical Tool for Student’s Training

To motivate students, the Database course should be full of real-life tasks. Their formulation should require the learners to concentrate on the currently trained topic, i.e., not to expect solving additional ones which should distract them. Among other limitations, it for example means that records containing dates must be authentic i.e., to refer to the period students know from their daily practice and are ready to accept them with no discussion on their relevance.

Let us discuss a query producing all records from a travel agency database responding to the client request: Show me all trips I can take part in in the next four weeks. Table 1 shows its first ten records (Table 1).

Table 1: Ten records in the database Trips.

First, the database of trips must be prepared in advance and the students should not build it. Its creation would take a long time which could be devoted to other queries i.e., to deeper knowledge of the subject. Even though the table is short and contains a few records, its design and its value input would take dozens of minutes. Accessing the ready-made table saves this time.

The query solving the client’s request sounds:

SELECT *

FROM Trips

WHERE DateOfTrip <= Date() + 28

Let us presume that the table was formed on 13 August 2023. On this date, four records will be extracted – see Table 2. As time will go, the query will produce different results. For example, after 24 August, it will show just three of them. Finally, after 2nd January 2024, an empty data set will be the response. The situation is realistic, and the students understand why it happens (Table 2).

Table 2: Records selected by query.

On the other hand, the lecturers are in a less convenient position. They must modify the table every time they plan to exploit such permanent records in their course. The database gets older: in one day non-updated tables will produce empty sets only.

Manual editing is tidy and may produce errors. This discomfort can be avoided by creating “evergreen databases”. In such databases, some attributes are always updated when it is open. The same query will always generate the same number of records. In our case, the database will contain the replica of the table at Table 1, but its third column contains integers – the difference between “today” (the date of modification) and the desired value of DateOfTrip attribute.

In addition to the data for students, the evergreen database contains more tables - two in our case. The records for students will be in table Trips as before. From the table named DataSource, the educator will generate them. During the opening the records in the Trips table are delete using the action query

DELETE *

FROM Trips

Then new records to added to it using:

INSERT INTO Trips (ID, Destination, DateOfTrip, Transport, Price, MaxParticipants, Guide, Contact)

SELECT ID, Destination, Date()+TimeShift AS DateOfTrip, Transport, Price, MaxParticipants, Guide, Contact

FROM DataSource;

To speed up the process, the queries can be included into the Autoexec macro. Such an approach guarantees that there is always a database with fresh data and production at the same time when used in education. The author and his colleagues have used this approach for a long time. Using this approach, all databases in the textbook [1] and in the problem solver [2] were modified using the described approach. Then, they were attached to the books on a memory key. The readers often express their satisfaction with the approach.

None.

No conflict of interest.