ADVANCED TECHNIQUES FOR INFORMATION PROCESSING (TPI) [309494]

UNIVERSITY OF CRAIOVA

FACULTY OF SCIENCE

DEPARTMENT OF COMPURER SCIENCE

SPECIALIZATION INFORMATICS /

ADVANCED TECHNIQUES FOR INFORMATION PROCESSING (TPI)

PAPER OF DISSERTATION

Scientific Coordonator: Graduate:

Lect. Dr. Dorel Săvulea Elena Ștefania Călcioiu

Craiova

2018

UNIVERSITY OF CRAIOVA

FACULTY OF SCIENCE

DEPARTMENT OF COMPURER SCIENCE

SPECIALIZATION INFORMATICS /

ADVANCED TECHNIQUES FOR INFORMATION PROCESSING (TPI)

DATABASES WITH BITEMPORAL TABLES

Scientific Coordonator: Graduate:

Lect. Dr. Dorel Săvulea Elena Ștefania Călcioiu

Craiova

2018

[anonimizat] a [anonimizat]. These databases were active the survey of the mid-1970s. Some of these surveys adopt the extreme position that data from such a [anonimizat]. another extreme is an instant database containing only current data and data is deleted or updated when the facts represented by these data cease be true ([anonimizat] a conventional database it does not really understand a temporal database. [anonimizat], an instant database and is shown among the S1 is currently 20. A [anonimizat], it may not be clear that this status is currently

In a [anonimizat] "now" ([anonimizat]). Even if the time of the snapshot happens to be some time other than "now", it makes no material difference to the way the data are managed and used. [anonimizat], how the data are managed and used in a temporal database differs in a variety of important ways from how it is managed and used in a snapshot database[];

[anonimizat]. In a [anonimizat], [anonimizat]. It follows that a temporal relation is one in which each tuple includes at least one timestamp ([anonimizat]). It further follows that a temporal relvar is one whose heading is that of a [anonimizat] a (relational) temporal database is one in which all of the relvarsare temporal ones. Note: We are being deliberately vague here as to what data of some timestamp type might look like.

Having just offered a reasonably precise definition of the concept temporal database (in its extreme form), we now dismiss that concept as not very useful. [anonimizat] ([anonimizat]) would not be temporal.

[anonimizat], but is not itself a temporal relation.

And it would be a strange DBMS indeedcertainly not a relational onethat would let us obtain results that could not themselves be kept in the database.

CHAPTER 1

[anonimizat]. These baseline data were active polls in the mid-1970s. Some of these polls take the extreme stance that data from such a database is inserted, not deleted or updated in cases where the database contains only historical data, another extreme is an instant database containing only the current date and the data is deleted or updated when the facts represented by these data are no longer true (in other words, a snapshot database is just a database as conventionally understood, not a temporal database at all.

If the data is a coded representation of the facts, then the temporal data is coded representation of time stamped facts. In a temporal database, according to the extreme interpretation of this term, all data are temporal, meaning that each recorded fact is marked with time. It follows that a temporal relationship is one in which each tuple includes at least one time stamp (that is, the heading includes at least one attribute of a type of stamp). Further it follows that a temporal relvar [2] is one whose position is that of a temporal relationship and a relational (relational) temporal database is one in which all relvars are temporal.

Offering only a fairly precise definition of the temporal concept (in it’s extreme form), we reject this concept as not very useful. We reject it because even if the original revvars in the database were all temporal, many relationships that could be derived from that database (e.g. query results) would not be temporal. For example, the answer to the query Get the name of all the people I've ever hired obtained from a given time base, but it is not a temporal relationship.

And it would be a strange DBMS, indeed, not a relative relationship would allow us to obtain results that can not be kept in the database.

VALID-TIME STATE TABLES [2]

In a feed yard, cattle are grouped into “lots”, with subsets of lots moved from fold to fold. One of Thomas's tables, the LOT_LOC table, records how many cattle from each lot reside in each fold of each feed yard. The full schema for this table has nine columns; we'll just consider a few of them.

Thomas wishes to capture the history of which cattle were coresident, to study how disease moves from cow to cow. He adds two columns, FROM_DATE and TO_DATE, to this table:

LOT_LOC(LOT_ID_NUM, FOLD ID, HD_CNT, FROM_DATE, TO_DATE)

These two columns will allow many interesting queries to be expressed (some of considerable intricacy), while enormously complicating previously innocuous constructs such as primary and foreign keys. These columns render the table and validtime state table: it records information valid at some time in the modeled reality, and it records states, that is, facts that are true over a period of time. The FROM_DATE and TO_DATE columns delimit the period of validity of the information in the row. The temporal granularity of this table is a day.

The first three columns are whole columns. The last two columns are of type DATE. SQL supports three types of installs, DATE, TIME, and TIMESTAMP, which differ in their value range (for example, DATE’s vary over 9999 years, while TIME’s time interval of only 24 hours) and their temporal granularity (one day for DATE and a seconds for TIME default).

The last two columns indicate the start instant (actually, the starting day) of the period of validity of the order and the end of the period of validity.

Unfortunately, SQL-92 does not support periods, so the period of validity must be implemented with two delimiting instants.

Table 1.1 records the movement of three lots of cattle in the feed yard. In this table we see that 17 head of cattle were in fold 1 for 11 days, moving inauspiciously off the feed yard on February 18 (SQL-92 DATE literals are expressed as year-monthday). Also, 14 head of cattle from lot 374 are still in fold 1 (we use forever to denote currently valid rows), and 23 head of cattle from lot 219 were moved from fold 1 to fold 2 on March 1, with the remaining 20 head of cattle in that lot moved to fold 2 on March 14, where they still reside.

Table 1.1 The LOT LOC table.[2]

Without the timestamp columns (FROM_DATE and TO_DATE), the LOT_LOC primary key is the pair (LOT_NUM_ID, FOLD_ID), which can be expressed informally as ”the (batch identifier, fold_ID) is unique for a single line”. With time stamp columns, it can be generalized “at any time in time (batch identifier, fold identifier) is unique for a single round”. It is regrettable that SQL PRIMARY the KEY construction is inadequate for real-time status tables; expressing this manifest constraint in SQL-92 requires a complex statement.

Queries [1,2]

Queries over conventional tables ask, “What is?” Queries over time-varying tables can be placed in three broad classes. For each conventional (nontemporal) query over a table without these two DATE columns, there exist “current” (“What is now?”), “sequenced” (“What was, and when?”), and “nonsequenced” (“What was, at any time?”) variants over the corresponding valid-time state table.

Consider the nontemporal query “How many head of cattle from lot 219 in feed yard 1 are in each fold?” The current analog over the LOT_LOC valid-time state table is “How many head of cattle from lot 219 are (currently) in each fold?” For such a query, we only are concerned with currently valid rows, and we need only to add a predicate requesting such rows. This query returns the following result, stating that all the cattle in the lot are currently in a single fold:

FOLD_ID HD_CNT

The sequenced variant is “Give the history of how many head of cattle from lot 219 were in each fold.” The result (Table 1.2) provides the requested history. We see that lot 219 moved around a bit.

The nonsequenced variant is “How many head of cattle from lot 219 were, at some time, in each fold?” Here we don't care when the data was valid. Note that the query doesn't ask for totals; it is interested in whenever a portion of the requested lot was in a fold. Table 1.3 shows the result. Nonsequenced queries are often awkward to express in English, but can sometimes be useful.

As another example, consider the nontemporal query “Which lots are coresident in a fold?” Such a query could be a first step in determining exposure to putative risks. Indeed, the entire epidemiologic investigation revolves around such queries, which turn out to be notoriously difficult to express in SQL-92.

The current version, “Which lots are currently coresident in a fold?”, will return the empty table when evaluated on Table 1.1, as none of the lots are currently coresident (lots 219 and 374 are currently in the feed yard, but in different folds).

The nonsequenced variant is “Which lots were in the same fold, perhaps at different times?” The result is Table 2.4: all three lots had once been in fold 1.Note however that at no time were any cattle from lot 137 coresident with either of the other two lots. To determine coresidency, the sequenced variant is used: “Give the history of lots being coresident in a fold.” This requires the cattle to actually be in the fold together, at the same time. The result of this query on Table 1.1 is the following:

Modifcations[2,3]

Modifications (that’s, intersections, delections, and updates) comprise te bulk of many applications and are challenging when applied to time-varind date. We’ll ilustrate modifications on the LOT table, which captures the gender of the cattle in each lot. Surprisingly (especially to the cattle!), the gender attribute is time-varying.

As an aside on terminology, a “bullock” I a male bovine animale (the term also denotes the term also denotes a male moose). A “cow” is a female bovine animal (or a female whale). A “calf” is the young of a cow (or a young elephant). A “heifer” is a cow that has not yet borne a calf (or a young female turtle). “Cattle” are collected bovine animals.

Table 1.2 The history of lot 219.

Table 1.3 Result of a nonsequenced query.

Table 1.4 Result of another nonsequenced query.

137 219 1

137 374 1

219 374 1

A “lead” is a castrated male of the cattle family. To lead an automobile or a committee is emphatically different from leading to a calf. Cows and heifers are not streed, they are “spayed”, or generically, neutered, rendering them a “neutered cow”. There is no single term for neutered cow paralleling the term “lead”, perhaps because spaying is a more invasive surgical procedure than leading to, or perhaps because those doing the naming are cowboys.

Bullocks are leaded to reduce injuries to themselves (bullock s are quite aggressive animals) as well as to enhance meat quality. Basically, all that fighting reduces glycogen in the muscle fiebers, which increases the water content of the meat, which results in less meat per pound. Heifers are spayed only if they will feed in open fields, because calving in the feed yard is expensive and dangerous to the cow.

Bullock calf Bullock

Lead

Heifer Cow

Neutered cow

Figure 1.1 Gender transitions.[2]

A slice of the LOT table is shown in Table 1.5 (in this excerpt, we've omitted several columns not relevant to this discussion). The GNDR_CODE is an integer code.

For expository purposes, we will use single letters, with indicating the lot consists of bullock calves, h indicating the lot are heifers, and s indicating the lot are leads. The FROM_DATE and TO_DATE in concert specify the time period over which the values of

all the other columns of the row were valid.

In this table, on March 23, 1998, a rather momentous event occurred for the cattle in lot 101: they were leaded. Lot 234 consists of calves; a TO_DATE of forever denotes a row that is currently valid. Lot 234 arrived in the feed yard on February 17; lot 799 arrived on March 12.

Brad collects data from the feed yard to populate his database. In doing so he makes a series of modifications to his tables, including the LOT table. As with queries, there are three general classes of modifications: current, sequenced, and nonsequenced.

“Lot 433 arrives today” is a current insertion. “Lot 101 leaves the feed yard today “ is a current deletion. The two modifications in concert result in Table 1.6. All information on lot 234 after today has been deleted. (As this is being written on January 13, 1999, “today” is shown in SQL as 1999-01-13, exposing the nonlinear fashion in which this book evolved.), “The cattle in lot 799 are being leaded today” is a current update, with the result shown in Table 1.7.

A current modification applies from “now” to “forever”.” A sequenced modifi-cation generalizes this to apply over a specified period, termed the “period of applicability.

“ This period could be in the past, in the future, or overlap “now”. “Lot 426, a collection of heifers, was on the feed yard from March 26 to April 14” is a sequenced insertion. “Lot 234 will be absent from the feed yard for the first three weeks of October, when the leadeing will take place” is a sequenced deletion.

Table 1.5 The LOT table.

Table 1.6 Result of a current insertion and deletion.

101 c 1994-01-01 1994-03-23

101 s 1994-03-23 9995-12-31

433 c 1994-01-13 9995-12-31

234 c 1994-02-17 1995-01-13

799 c 1994-03-13 9995-12-31

Table 1.7 Lot 799 was leaded to today.

101 c 1994-01-01 1997-03-23

101 s 1994-03-23 9995-12-31

433 c 1994-01-13 9995-12-31

234 c 1994-02-17 1998-01-13

799 c 1994-03-12 1998-01-13

799 s 1998-01-13 9995-12-31

A sequential update is the temporal analogue of a non-temporal update with a specific term applicable. Lot 799 was only targeted for March is a sequenced update. (Something Magic happened on April 1. The idea is here to show how to implement sequential updates in general, and not just on cattle.)

As with queries, an unused change treats identical stamps to the other columns and often mentions the period of validity of the rows removed. An example is the deletion of batch entries 234 that last longer than three months.

Most changes will be expressed primarily as changes in the enterprise that is modest (some things become true or will be true in the future, some aspects of change, now or in the future, a fact is no longer true). Such modifications are current or sequential modifications. Modifications unchanged, while generally easier to express in SQL, are rare.

1.2 TRANSACTION-TIME STATE TABLES[2, 5]

The LOT_LOC and LOT tables capture the history of reality. First row of table 1.6

says we checked bovine animals in batch 101 anytime in the first three months since 1998, I have been seen to be calves.

The Brad database also includes the LOT_CONTAINS table, with the following schema (again, omit some columns): LOT_CONTAIN (LOT_ID_NUMBER, BKP_ID, NAME_ONE).

The primary key of this table is LOT_ID_NUM, so that at any time this value is unique one line ID, which records the backup identifier and application name for that batch.

Thomas copies the forest from the courtyard system, then processes the information later Mation. The LOT_CONTAIN table stores for each batch the rescue reserve from which current information about the fold in question was extracted. Because these data tend to be dirty, with inconsistencies and omissions, Thomas would like to follow the information in the LOT_CONTAINS table. In particular, he would like to rebuild his state with all the data of the past. He adds two columns and START_DATE indicating when that line was first entered into the table and a STOP_DATE indicating when that line was updated or deleted. We have to point out that rows are a logical error, because the physical deletion of old rows would prevent the reconstruction of past states. A care table can be reconstituted at a previous date is called transaction status table, while capturing applied transactions table game.

While Tables 1.5 and 1.8 have both DATE columns, interpretation these columns are dramatically divergent. Valid time tables capture a history of real-while trading time tables also capture the history of a table's change state.

We can not ask for LOT care table was his state three days ago, but we can look

LOT_CONTAIN table this question. Similarly, we can not request the LOT_CONTAINS table what was true in reality three days ago, but we can ask for the LOT care table. While any row in the LOT table may change, we will correct the mistakes captured history, the LOT_CONTAINS table grows monotonically, with old rows remaining unchanged in perpetuity. The most relevant query on a transaction status table is to rebuild a past state. Provide the status of the LOT_CONTENT table on January 12, 1998 the following result:

Note that the BKP ID for lot 100 was (erroneously) thought to be 15 on that Monday, and lot 453 hadn’t yet arrived.

Now we asc, Provide the sate of the LOT_CONTAINS table on February 18, 1994, with the following result:

The BKP_ID fro lot 100 is now the correct value of 19. Only current modifications are permitted on transaction-time satet tables, as past states cannont be changed. Modifications must permit subsequent reconstructions.

The modification Correct the backup identier forf lot 453 to 27 proceduces the result show n in Table 1.9.

Table 1.9 The corrected backup identier.

Table 1.10 The LOT bitemporal table.

1.3. BITEMPORAL TABLES [8]

Validity of time, capturing the history of a changing reality and a trading time, the state sequence of a changing table is orthogonal and can thus be used separately or be applied in a concerted manner. A meal that supports both is called bitemporal.

The LOT is critical for Thomas's epidemiological analysis, so he also follows the changes made in this table. This table already has a valid time; he adds two columns, START_DATE and STOP_DATES to capture transaction time.

Table 2.10 are four stamps that care for its bite-matrix nature. There is wealth information in such tables, if taken in reading. Let's examine that table row by row.

• Row 1: On January 3 (START_DATE), the fact that batch 100 and a group of calves arrived in the feeding yard two days before, January 1(DATA), is recorded. The valid time for this is January 1 forever (DATA), indicating that the calves are expected to remain. We will return to STOP_DATES when we discuss the fourth line.

• Row 2: On February 17 (START_DATE), that batch 234, also a group of calves, arrived in the grocery yard that day. STOP_DATE forever indicates that this is considered to be correct.

• Row 3: On March 12, that batch 799, and a group of vines, arrived in animal feed the yard that day is recorded.

Table 1.11 The history as known on March 15.

Row 4: On Thursday, Mach 19, unbeknownst to the cattle in lot 100, these cattle were scheduled to be streered early the next week, on Monday, March 23. So we logically update the rst row by setting it’s STOP_DATE to the current date, insert row 4, indicating that lot 100 consisted of claves from January 1 to March 23, and insert row 5.

Row 5: On Thursday, March 19, the fact that lot 100 is a collection of leads from March 23 to forever was recorded, and that fact is still considered correct.

Since this table supports transaction time, we can reconstruct it’s state in the past. Provide the hisory of the LOT table as best jnow on March 15, 1994 (the Ides of March, beware!), would generate the result shown in Table 1.11. As of March 15, we hadn’t yet scheduled lot 100’s leading to. Provie the istory as best known on April 1 yields a different result (Table 1.12).

Interactions between valid and transaction time are especially interesting, as in when were leading to scheduled (as oppesed to being recorded after the fact), which would identify one such leading to:

As bitemporal tables include transaction time, all modifications are transaction time current. However, we can still provide the period of applicability for modifications, as in “Lot 345 will be absent from the first three weeks of Octomber.” .

CHAPTER 2

TEMPORAL DATABASE DESIGN

The study of Thomas's feeding application, discussed informally in Chapter 1. We are now returning to this study, this time with the help of a profound understanding of semantic temporality communicated in the chapters that have intervened.

Thomas began to investigate the temporal relationships between the supposed ones expose the risk factor and subsequent health events by understanding the structure of data kept by food places as they track the movement of cattle between folds. Combine these data denunciations carefully into a global schema.

After working months, Thomas built an entity-relationship scheme (ER) with about 40 types of relationships and over 150 roles. Even when printed in a small font, this ER chart requires a large poster to see it in full. The relational scheme generated by this conceptual model contains 55 tables and approximately 850 columns. In conclusion, the scheme is typical: large and complex.

After Thomas had the scheme, he read an early project of this book, and realized he had a bite-time database. One worked with the author you understand its application within the framework presented in this book. This analysis highlighted semantic issues with the schema, which were solved by applying the methodology presented here. This analysis highlighted semantic issues with the schema that were resolved by applying.

2.1. Properly sequencing the design[16]

The purpose of the chapter is to get a more general picture for reasons of clarity when considering the decision each time.

Application design and deployment consists mainly of a series of decisions, each affecting the subsequent, often subtle and unexpected compromises ways. Over the past two decades, experience and research have been transformed on a series of three basic stages: (1) conceptually conceptual using the ER model, (2) logical design using the relational model and physical design (3) to ensure performance.

Unfortunately, because both the ER and the relational model are not themselves adequately supports information that varies over time, the current practice using these models is actually counterproductive in some places. Among the most selfish ones is temporal partitioning, which is generally considered very early in conceptual design, when, in fact, it should be one of the last considerations in physical design. Very complex ER models and relational schemes resulting from the examination of the previous time rather than later. The temporal aspects of the application must be ignored initially when designing the conceptual scheme. In my approach here, the conceptual initial project ignores the variable time character of the application. We focus on capturing real reality and temporarily ignoring any history that could be useful for capture. This selective amnesia somewhat simplifies what is often an extremely complex application capture semantic task. An additional advantage is that existing design concepts are fully applied.

Only after completing the complete design, we increase the ER schema with time, variable semantics of the application. We consider each component of the ER in turn, annotating the component with its temporal semantics. Entity types, relationships, attributes, and keys are addressed individually. All annotations are expressed in prose so that they do not contracted the ER scheme.

Similarly, design logic takes place in two stages. First, the non-temporal ER scheme is mapped to a non-temporal relational scheme, a collection of tables. Here again, we ignore the temporal aspects of the application and, therefore, we can apply existing mapping strategies that are not loaded by analyzing how to capture history.

In a second stage of logical design, each of the annotations is applied to the logic scheme, table modification, or integrity constraints to match the temporal aspect. We proceed in a disciplined way, treating each annotation,in change. Hereinafter, we start with a non-temporal ER schema and we continue to make it fully a SQL development scheme, taking into account the variable nature of the application. This still serves another force through the basic and the temporal conceptsit also serves for a useful emphasis on the time of thinking in these terms.

2.2. Conceptual design[17]

Contrary to current practice, all temporal aspects should be ignored in most conceptual design cases. An ER system should not be built characteristics. All design considerations must be seen at this time, including keys, integrity constraints, composite attribute, multi-valued attribute, relationship (one-to-one, multi-to-many, optional or mandatory) of entities, subclasses, superclasses, categories, specialization, generalization and inheritance.

Once the ER scheme has been developed carefully, only then should it do it the temporal aspects to be taken into account.

2.2.1. Nontemporal ER Schema[17]

Figure 2.1 shows a small portion of the original ER scheme for applying Brad, in the traditional notation of entity rectangles and diamonds for relationships. Note that in the description of this diagram we use the present time, because time is not yet involved. Name names of entities and relationships are within all boundaries, attribute names are capitalized, and individual entities (such as a given batch) and individual relationships are not capitalized.

Strong types of entities

There are two (strong) entities present: FEEDYARD and APPLICATION. Brad dates from power plants.

Types of weak entities

There are four types of weak entities, indicated by a double rectangle; a double diamond indicates the type of identification relationship that refers to a strong type of entity at the type of weak entity. From this necessity, this relationship is total, indicated by a double line. A fold is a fenced land where cattle can be kept. It is linked to FEEDYARD strong entity type through IN_FOLD relationship type. Because the relationship is total, each FOLD entity must be in a particular FEEDYARD entity.

A lot is a specific group of cattle, resident in a fold or divided into several folds. The analysis of the alleged treatments and factors is carried out on bovine lots. LOT is linked to FEEDYARD by the IN_LOT relationship type.

Power Platforms use FoxPro to collect this information; FoxPro stores each table in a dbf file. Thomas then periodically copies these to an oppy disk and uploads information to the SQL database that runs on Sybase SQLAnywhere. He says each such transfer and load, which may involve multiple data coordination, a backup for which a weak BACKUP entity is created.

Figure 2.1. A nontemporal entity-relationship schema[17]

Entity Type Key Attributes

In the ER model, each entity must have an identifier that identifies that entity. Types of strong entities have a key, which is highlighted in the ER schema; types of weak enzymes have a partial key, which is indicated with a dotted line. The FEEDYARD key is Feedyard_ID; for APPLICATION is a name.

A key combination for strong entity type and partial key for the type of weak entity identifies each weak entity. Therefore, a certain pair (Feedyard_ID, Fold_ID) will identify a individual fold.

Attributes

Some other attributes are included. You might ask: Why is the type of LOT entity both batch ID and lot identification numbers? It seems the first is a string of characters used by the power court; the latter is an assigned number and the partial key is designated. The most interesting attributes in this context are user-timed atributes: Proj Closeout in LOT (data projected by the group leaving the power court) and Year and Data processed in BACKUP, indicating the time of backup processing, and what months data was associated. These attributes are dependent on the other attributes and relationships that the entity type performs. In particular, they do not indicate the extent of the time or the trading time of the entities or relationships. Rather, they approximate when the backup was made and when it was processed. Backup data is used to calculate a more precisely (until the granularity of the day). Due to the various verification and consistency cleaning activities, the actual transaction time may be different from the processed data.

LOT.Valid is a Boolean attribute that indicates whether the entity information has passed several validation tests.

Note that we will not include the attribute timestamp. These attributes will be added when mapping to the relational model (i.e., tables).

Relationship Types

There are types of relationships, in addition to the types of identifying relationships discussed earlier. LOCATION identifies the fold (s) in which each batch is located. Cattle in a large number can live in multiple feathers, and a fold can contain bovine animals from multiple batches (complicating the disease propagation analysis). DESCRIBE refers to the batches of backups.

One dbf contains one or more batches. MASS_TRTMNT is administering a drug regimen to the cattle of a large number of residents in one or more folds. This is a type ternary relationship, while associating each individual relationship with the backup time that was loaded.

The type of MOVE relationship is complex: it is a quill, capturing the movement of a batch (perhaps partially) bovine from a fold (from roll) to another fold (in role). This move was loaded during a backup, from a certain dbf.

Several types of relationships have associate associates. Both MOVE and LOCATION types of relationships include an Hd Cnt attribute; MASS TRTMNT The relationship type includes the Avg Wgt and Trtmnt Code attributes.

Participation Constraints

The participation restrictions must be specified for each type of entity participating in a relationship tip. Each constraint is denoted in the ER schema with a pair of integers, indicating the minimum and maximum participation.

The minimum participation constraints (the first component of the pair) differ optional from compulsory participation. A minimum of 0 is optionally named; and a minimum participation greater than zero is called total, or required. All types of relationships than LOCATION are total; a special fold be naked. The maximum participation status (the second component of a pair) identities the relationship as one-on-one, one-to-many or many-to-many. E.g, the DESCRIBES_LOT relationship is multi-to-one. A backup copy describes many batches, but the batch is described by a single backup.

In a non-temporal ER scheme, these constraints are considered to be kept at anything moment in time. When the scheme is later interpreted in a temporal ER scheme,

the constraints will then be considered as sequenced, taking each isolated point

at time.

In the courtyard power scheme, IN_FOLD, is described by, creates, describes FOLD, and LOT_DESCRIBE are all kinds of one-to-many relationships. Each location relationship designates bovine animals from a lot in one or more folds. Every relationship of movement denotes the movement of cattle from one lot to another, as record a backup and backup. Every mass relation trtmnt catches a the treatment being applied to a single batch in a fold, as recorded by a single backup.

2.3. LOGICAL DESIGN [16, 17]

At this point, we have a conceptual scheme of a non-temporal entity relationship (Figure 2.1) augmented with prose notes describing the various aspects of this scheme. These annotations refer to the life and trading aspects of the types of entities and the valid and trading-related aspects of relationship types, attributes, and integrity constraints.

We now design this scheme in a data logic model, here the relational model, resulting in a collection of tables. This mapping is in two stages. In the first stage, temporal annotations are largely ignored, thus arriving at a non-temporal logical scheme. This logical scheme is then used to adapt the time-varying aspects by adding a column (s) for the timestamp and decomposing some tables into several constituent tables. Also in this second stage, the various statements and SQL constraints are denied on the tables to capture the temporal integrity constraints.

2.3.1.Mapping to the relational scheme [16, 17]

In this first step, the following mapping actions are performed. We are laconic only because this process does not refer to time, and should be familiar.

Indeed, this step can be done automatically by the CASE tools, as the steps do it does not concern the temporal aspects. To keep things right, use a Roman font for conceptual constructions (e.g. LOT) and a sans-serif font for logical constructs (e.g., LOT Table).

1. Create a table for each type of regular entity (FEEDYARD, APPLICATION). Add

columns for each attribute. The primary key is the appropriate key entity type.

2. Create a table for each type of weak entity (FOLD, BACKUP, LOT, DBF FILE). It includes the key type of strong entity as a foreign key. The primary key is a the combination of the partial key of this entity and the key of the strong entity tip.

3. For each type of one-to-one relationship, expand the table to one of them the entity with the key of the other entity type as a foreign key. We have there are no such types of relationships here.

4. For each type of binary relationship one-to-many, either extend a table or create

a new table for the type of relationship. for DESCRIBE_FOLD, DESCRIBE_LOT and CONTAINS, extend the mass on one side (FOLD, LOT and LOT, respectively) with the key of the other entity type (from BACKUP, from BACKUP or DBF_FILE respectively) as a foreign key, as well as an attribute a relationship.

5. Create a table for each type of remaining relationship (MOVE, MASS TRTMNT, LOCATION). Add external keys to all types of participating entities. The primary key is in general, a subset of these foreign keys, but it is usually (and in these cases) only combining them.

6. Create a table for each multivalued with an attribute. There are no such attributes here.

The result of this first step is the scheme of the non-temporal database, with the primary key columns underlined. We omit some of the FDYD.

FDYD ( FDYD_ID, NAME, FDYD_SHORT_NAME, FDYD_MNGR_LNAME,…,

UNIQUE (FDYD_SHORT_NAME) )

LOT ( FDYD_ID, LOT_ID_NUM, LOT_ID, GNDR_CODE, PROJ_CLOSEOUT, IN_WEIGHT,

VALID, OWNER, COMMENT, BKP_ID, A_NAME, DBF_NAME,

DBF_UPDATE_RECNO,

UNIQUE (FDYD_ID, LOT_ID_NUM, LOT_ID),

FOREIGN KEY (FDYD_ID) REFERENCES FDYD,

FOREIGN KEY (FDYD_ID, BKP_ID) REFERENCES BKP,

FOREIGN KEY (A_NAME, DBF_NAME) REFERENCES DBF_FILE

)

FOLD (FDYD_ID, FOLD_ID, FOLD_TYPE_CODE, BUNK_LENGTH, APRON_WIDTH,

FOLD_AREA, WATER_SPACE, BKP_ID,

FOREIGN KEY (FDYD_ID) REFERENCES FDYD,

FOREIGN KEY (FDYD_ID, BKP_ID) REFERENCES BKP

)

APPLICATION (A_NAME, A_DESCRIPTION, A_DATA_DIRECTORY)

DBF_FILE (A_NAME, DBF_NAME, DBF_DESCRIPTION, DBF_USED,

FOREIGN KEY (A_NAME) REFERENCES APPLICATION

)

BKP (FDYD_ID, BKP_ID, YEAR_MONTH, DATE_PROCESSED, QUIRKS,

VTRC_LAST_DATE_MOD, BRDR_LAST_DATE_MOD, ARCH_LAST_DATE_MOD,

FOREIGN KEY (FDYD_ID) REFERENCES FDYD

)

Figure 2.2 Initial nontemporal logical schema [17]

to save space. Finally, I changed a few names of tables and columns to the current name that Brad used.

In figure, the (non-temporal) scheme is presented using the cubic feet the notation used by the PowerDesigner tool that Brad used to create a schema. Diamonds are foreign keys, crow's feet are relationships that are many participations, small circles denote optional participation, and small cuts between links denote mandatory participation.

2.3.2. Applying Temporal Annotations [17]

In this second step, we apply the time annotations. As we review the land crossed in the previous chapters,

LOT_MOVE (FDYD_ID, LOT_ID_NUM, FROM_FOLD_ID, TO_FOLD_ID, HD_CNT, BKP_ID,

A_NAME, DBF_NAME,

FOREIGN KEY (FDYD_ID, FROM_FOLD_ID) REFERENCES FOLD(FDYD_ID, FOLD_ID),

FOREIGN KEY (FDYD_ID, TO_FOLD_ID) REFERENCES FOLD(FDYD_ID, FOLD_ID),

FOREIGN KEY (A_NAME, DBF_NAME) REFERENCES DBF_FILE,

FOREIGN KEY (FDYD_ID, LOT_ID_NUM) REFERENCES LOT,

FOREIGN KEY (FDYD_ID, BKP_ID) REFERENCES BKP

)

LOT_LOC (FDYD_ID, LOT_ID_NUM, FOLD_ID, HD_CNT, YEAR_MONTH,

FOREIGN KEY (FDYD_ID, LOT_ID_NUM) REFERENCES LOT,

FOREIGN KEY (FDYD_ID, FOLD_ID) REFERENCES FOLD

)

MASS_TRTMNT (FDYD_ID, LOT_ID_NUM, FOLD_ID, M_TRTMNT_AVG_WGT, M_TRTMNT_CODE, M_ M_TRRMNT_HD, BKP_ID

FOREIGN KEY (FDYD_ID, FOLD_ID) REFERENCES FOLD,

FOREIGN KEY (FDYD_ID, LOT_ID_NUM) REFERENCES LOT,

FOREIGN KEY (FDYD_ID, BKP_ID) REFERENCES BKP

)

Figure 2.2 (continued)

User-Dened Time Attributes

Return points (as page numbers in brackets) are provided to those pages introducing these approaches through case studies, providing elaborations and more alternative.

Each attribute is mapped to a column in the associated table. Attribute care records the user-defined time values are differentiated by type: instantly, an interval or a period. All temporal values have granularity, although some DBMSs are the granularity of their temporal patterns. For anchored values (moments and periods), SQL-92 and some DBMSs allow a certain amount of time to be stored. Otherwise, the time zone is either default or xed. Now it should be represented in approximate or near approximation.

Periods can be represented in a few moments if you represent each delimitation moment closed (period) or open (even outside the period).

A representation of a closure period in which the marking of the granules after the last granule is preferred. Schedule The spindle, if any, can be stored for the first time.

CHAPTER 3

MGlobalit – Aplication for management employers

MGlobait is a software product represented by a web application in which Laravel Framework-based technologies and its components have been used. The purpose of the application is to interconnect the interaction between the administrator of the single institutions and the persons employed in that institution.

There is only one category of users: Administrator. A type user Administrator is the equivalent of an institution administrator.

An Administrator user can add new employees to the database, edit the information of an employee and be able to see new and old saved versions, employee history, edit account password, and user picture.

The following technologies, programming languages and frameworks:

Laravel is a free, open-source PHP web framework, created by Taylor Otwell and designed for the development of web applications following the model-view-controller (MVC) architectural pattern and based on Symfony. Some of the features of Laravel have a modular packaging system with a dedicated dependency manager, different ways of accessing relational databases, utilities that aid in deployment and maintenance, and its orientation towards syntactic sugar []

Laravel 5.6 continues the improvements made in Laravel 5.5 by adding an improved logging system, single-server task scheduling, improvements to model serialization, dynamic rate limiting, broadcast channel classes, API resource controller generation, Eloquent date formatting improvements, Blade component aliases, Argon2 password hashing support, inclusion of the Collision package, and more. In addition, all front-end scaffolding has been upgraded to Bootstrap 4.

JetBrains PhpStorm is a commercial, cross-platform IDE for PHP built on JetBrains' IntelliJ IDEA platform.

PhpStorm provides an editor for PHP, HTML and JavaScript with on-the-fly code analysis, error prevention and automated refactorings for PHP and JavaScript code.

PhpStorm's code completion supports PHP, including generators, coroutines, the finally keyword, list in foreach, namespaces, closures, traits and short array syntax. It includes a full-fledged SQL editor with editable query results.

PhpStorm is built on IntelliJ IDEA, which is written in Java. Users can extend the IDE by installing plugins created for the IntelliJ Platform or write their own plugins.

All features available in WebStorm are included in PhpStorm, which adds support for PHP and databases. WebStorm ships with pre-installed JavaScript. []

Microsoft SQL Server is a relational database management system developed by Microsoft. As a database server, it is a software product with the primary function of storing and retrieving data as requested by other software applications—which may run either on the same computer or on another computer across a network (including the Internet).[ ]

MySQL Workbench is a visual database design tool that integrates SQL development, administration, database design, creation and maintenance into a single integrated development environment for the MySQL database system. It is the successor to DBDesigner 4 from fabFORCE.net, and replaces the previous package of software, MySQL GUI Tools Bundle. []

Also, remaining in the area of the front-end, the look and feel was provided by Bootstrap 4 UI framework.

Bootstrap is a free and open-source front-end web framework for designing websites and web applications. It contains HTML- and CSS-based design templates for typography, forms, buttons and other interface components, as well as optional JavaScript extensions.

Unlike many web frameworks, it concerns itself with front-end development only.

The architectural model used for the back-end of the application is the MVC software architectural pattern (Model-View-Controller).

Model–view–controller (MVC) is a software architectural pattern for implementing user interfaces on computers. It divides a given application into three interconnected parts. This is done to separate internal representations of information from the ways information is presented to, and accepted from, the user. The MVC design pattern decouples these major components allowing for efficient code reuse and parallel development.

Components:

The model is the central component of the pattern. It expresses the application behavior in terms of the problem domain, independent of the user interface. It directly manages the data, logic and rules of the application.

A view can be any output representation of information, such as a chart or a diagram. Multiple views of the same information are possible, such as a bar chart for management and a tabular view for accountants.

The third part or section, the controller, accepts input and converts it to commands for the model or view.

Interactions

In addition to dividing the application into three kinds of components, the model–view–controller design defines the interactions between them.

A model stores data that is retrieved according to commands from the controller and displayed in the view.

A view generates new output to the user based on changes in the model.

A controller can send commands to the model to update the model state (e.g., editing a document). It can also send commands to its associated view to change the view presentation of the model (e.g., scrolling through a document, movement of document).

3.1. Architecture

A web application based on the Laravel web Framework must adhere to a type structure model – controller – service – warehouse. The division into the project is done at the level sources and packages. The first division is located at the source file level which separates the configuration, test and application classes, and the second partition is in the first file in which the model, service, and warehouse controller packages are separated.

Figure 3.1. Arhitecture package

3.2. Functional specifications

MGolbalit-management is an application that solves the problem of keeping private information, writing it in different places or memorizing it.

This application is a platform that provides a place where a user can store all personal information grouped into the available categories: change passwords, change user photo employee Information, history hired, employee contract, contract history hired.

Application’s entities

The user has the following structure:

Id – number – unique identifier

Email – string – user credentials

Password – string – user credentials

The change passwords have the following structure:

Id – number – unique identifier

Old password – string – credentials

New password – string – credentials

Confirm password – string – credentials

The employee have the following structure:

Id – number – unique identifier

Id_parent – number – unique identifier

First_name – string – the first_name for record

Last_name – string – the last_name for record

Adress – string – the adress for record

City – string – the city for record

Contry – string – the lcountry for record

Mobile_phone – number – the mobile_number for record

Fix/fax_phone – number – the mobile_number for record

Birthday – date – the birthday for record

The contract employee have the following structure:

Id – number – unique identifier

Id_contact_parent – number – unique identifier

Job – string – the job of the contact

Department – string – the department of the contact

Brut_Lei – number –the job of the contact

Net_Lei – number –the net of the contact

Brut_USD – number –the job of the contact

Net_USD – number –the net of the contact

3.3.Features of the application

Login

By providing a valid user credentials, the system allows an single user access, admin.

Management Application

CRUD (Creation, Reading, Updating) on the following entities:

Emploayee,

History Employee,

Contact,

History contact.

Settings

Allows the admin to change their your password and user of picture.

3.4.MGlobalit management project in depth

In this section is presented the MGlobalit-Management project as it was developed. Accessing MGlobalit Management to redirect to page authentication to url http: // mglobalit-management /.

The sign-in page shows 2 files: (email and password) and check password for show password. The application is responsive to automatically resize to make it look good on all devices (desktops, tablets, and phones).

Login Controller

<?php

namespace App\Http\Controllers\Auth;

use App\Http\Controllers\Controller;

use Illuminate\Foundation\Auth\AuthenticatesUsers;

class LoginController extends Controller

{

use AuthenticatesUsers;

protected $redirectTo = '/home';

public function __construct()

{

$this->middleware('guest')->except('logout');

}

}

Login.blade.php

@extends('layouts.app')

@section('content')

<div class="container">

<div class="row justify-content-right">

<div class="col-md-8">

<form method="POST" action="{{ route('login') }}" class="form-login needs-validation" novalidate>

@csrf

<h1>Login</h1>

<div class="input-container">

<i class="fa fa-envelope icon"></i>

<input class="input-field" type="text" placeholder="Email" name="email" value="{{ old('email') }}" required autofocus>

</div>

<div class="input-container">

<i class="fa fa-key icon"></i>

<input class="input-field" type="password" placeholder="Password" name="psw" id="myInput">

</div>

<div class="passwordview">

<!– An element to toggle between password visibility –>

<input type="checkbox" onclick="myFunction()">Show Password

</div>

<div class="input-container">

<button type="submit" class="btn">{{ __('Login') }}

</button>

</div>

</form>

</div>

</div>

</div>

@endsection

The @media rule is used in media queries to apply different styles for different media types/devices.

Media queries can be used to check many things, such as:

width and height of the viewport

width and height of the device

orientation (is the tablet/phone in landscape or portrait mode)

resolution

Using media queries are a popular technique for delivering a tailored style sheet (responsive web design) to desktops, laptops, tablets, and mobile phones.

You can also use media queries to specify that certain styles are only for printed documents or for screen readers (mediatype: print, screen, or speech).

In addition to media types, there are also media features. Media features provide more specific details to media queries, by allowing to test for a specific feature of the user agent or display device. For example, you can apply styles to only those screens that are greater, or smaller, than a certain width.

Figure 3.2. Login

/* On screens that are 992px wide or less, the background color is blue */

@media screen and (min-width: 992px) {

.navbar-brand img {

max-width: 150px;

height: 65px;

margin-left: 10px;

}

.form-login{

max-width:400px;

margin-top:90px;

margin-left: 58%;

margin-right: auto;

margin-bottom: auto;

}

.form-login h1{

color: white;

font-family: "Arial Black", Gadget, sans-serif;

font-weight: bold;

text-align: center;

}

}

/* On screens that are 600px wide or less, the background color is olive */

@media screen and (max-width: 600px) {

.navbar-brand img {

min-width: 50px;

height: 55px;

margin-left: 7px;

}

.form-login{

width: 350px;

margin-top:80px;

margin-left: auto;

margin-right: auto;

margin-bottom: auto;

}

.form-login h1{

color: white;

font-family: "Arial Black", Gadget, sans-serif;

font-weight: bold;

text-align: center;

}

}

Figure 3.3. Login responsive

The Authentication button will make the database call made in Sqlite and will query the values entered by the users in the two fields.

If this corresponds to the values in the database element, it will display a message and allow users to go further into the Admin page. If the values are not, a message will be displayed.

Check show password is used to see the password you've entered to make sure it's correct chakc is used to see the password you've entered to make sure it's correct.

Figure 3.4. Check show password

LoginController show change password:

public function showChangePasswordForm(){

return view('auth.changepassword');

}

public function changePassword(Request $request){

if (!(Hash::check($request->get('current-password'), Auth::user()->password))) {

// The passwords matches

return redirect()->back()->with("error","Your current password does not matches with the password you provided. Please try again.");

}

if(strcmp($request->get('current-password'), $request->get('new-password')) == 0){

//Current password and new password are same

return redirect()->back()->with("error","New Password cannot be same as your current password. Please choose a different password.");

}

$validatedData = $request->validate([

'current-password' => 'required|string|min:6',

'new-password' => 'required|string|min:6|confirmed',

]);

//Change Password

$user = Auth::user();

$user->password = bcrypt($request->get('new-password'));

$user->save();

return redirect()->back()->with("success","Password changed successfully !");

}

The Admin page is the page of the user that is visible after the correct user authentication. Contains various elements to retrieve database information, modify and display it. If the credentials are correct, they redirect us to the admin page.

The administration page features the following features: creating an employee, editing an employee, viewing employee history.

The employee creation creator contains 3 cards (name, surname, address), 3 Dropdowns and a picker data.

Figure 3.5. Create Employee

Validation on the check boxes, if the fields are not filled in, informative messages will appear and warn you to fill in

Figure 3.6. Validate form Create Employee

Emploayee Controller

$this->validate($request,[

'name' => 'required|min:8',

'last_name' => 'required|min:8',

'adress' => 'required|min:8',

'city' => 'required|min:3',

'country' => 'required|min:3',

'number_phone' => 'required|min:10',

'number_phone' => 'required|min:10',

'valoare_date' => 'required',

]);

Employers::create([

'name' => $request->name,

'last_name' => $request->last_name,

'adress' => $request->adress,

'city' => $request->city,

'country' => $request->contry,

'number_phone' => $request->number_phone,

'number_phone' => $request->number_phone,

'valoare_date' => $request->valoare_data,

]);

return redirect(route('emploayee'));

After the information has been entered and the submit button has been clicked, the information is redirected to the employee's historical page. The employee page shows two buttons (employee history and updates). Employees page shows 2 buttons, hired historically. the history of each employee, information about the updates made.

Displays all employee history in page. EmploayeeController.

$allLatest = Employers::whereNull('end_date')->orderBy('name', 'asc')->get();

return view('employers', ['allLatest' => $allLatest]);

Figure 3.7.History all employee

Figure 3.8.Update employee

Figure 3.9.History employee

The hired employee history page contains a worker information sheet and 3 buttons (creates contact, contact history, edits the contract).

Figure 3.10.Create contact

Figure 3.11.History contact

Figure 3.12.Update contact

The password change password shows 3 pins (old password, new password and confirms the password) and the button saves, if the old password does not match the password, it will display the message that the password is not correct. If the password passwords (the new password and the config password) are not the same, it will display a warning message.

Figure 3.13. Change password

Figure 3.14. Validate password

CONCLUSIONS

A bitemporal table combines both valid time and transaction time into a single structure. It contains four time stamps, two indicating the valid validity period and two indicating the period of presence of the transaction. Such a meal can also be represented by several meals, such as a current store, a history store, and an archive store store.

The key key constraints on such tables are generally sequential valid and the current time of the transaction. Expression of such a constraint requires a PRIMARY SQL KEY constraint and an assertion. Often you will also want to restrict your valid time the history of an entity (or a relationship) to have no gaps; is needed another statement.

Changes to bitemporal tables may be difficult. I have examined several variants, all currents during the transaction: current real-time insertions, deletions, and updates; valid insertions, deletions, and updates; and the validity of the deletions unchanged. I saw that there were actual changes in real time boring and sequenced modifications in valid and longer time. For the latter, our lawyer and the two-step conversion process: the first deals with the valid time, with each of them modication resulting in a series of SQL statements, then dealing with the transaction time, mapping each SQL statement into one or more instructions that maintain the transaction-time semantics. Cartography has been mechanical, but has led to a long time series of SQL statements. The worst case scenario was that of a sequential update, in which the non-temporal update of several extended lines to about 60 SQL lines.

We've analyzed time slice queries, valid, and bitemeal varieties of transactions, then show that there are new versions of any non-term queries.

Of these, they are more useful than others, but the benet of a bitemporal table is that it admits the entire generalization of temporal questions.

Temporal interrupt temporal interruptions; in fact, any temporal constraint of integrity can be expressed as a temporal query embedded in BITEMPORAL TABLES. This mechanical translation allows you to map a temporal constraint in SQL; later analysis can be used to simplify.

We examined several ways to represent a bite-table state table through multiple tables, each holding, and part of the table's temporal extent. There are two somewhat orthogonal decisions that need to be made: how to divide the time diagram among the tables of representation, and for each table, whether it is used instantly or the time period.

Partition tables and bitemporal tables make some more effcient questions to spend on other questions. Time-sequenced and non-sequenced queries have negatively affected; the current trading queries, especially the current time of the current subset, are made faster. If the performance of current / current queries is critical, having an updated upgraded store can be quite efficient, however, this arrangement introduces difficulties in keeping the current store. And nice compromise is a pair of history / archive, the one that contains the current row transactions and the last one contains those rows with a time off of the forward transaction now. The history store is stamped over time and stamped instantly during the transaction (the start time of the transaction); the stop time of the transaction is implicit and is equal to change. The archive store is stamped in both valid and transaction time. It is convenient to keep the archive store by triggered advertisers in the history store.

If space is a prime, then it is better to use instant stamping in the transaction time in the archive store at a cost during the queries for noncurrent transaction queries.

BIBLIOGRAPHY

1. C.J. Date, An Introduction to Database Systems, 7th Edition, (Chapter 22), 2000 Addison-Wesley Publishing Company, Inc. Reprinted by permission of Addison Wesley Longman.

2. Jensen, C. S., and R. T. Snodgrass. Semantics of Time-Varying Information, Information Systems 21(4):311352, 1996..

3. Ahn, I., and R. T. Snodgrass. Partitioned Storage Structures for Temporal Databases, Information Systems 13(4):369391, 1988.

4. Allen, J. F. Maintaining Knowledge about Temporal Intervals, Communications of the ACM 26(11):832843, 1983.

5. Ozsoyo ̈ glu, G., and R. T. Snodgrass. Temporal and Real-Time Databases: A Survey, IEEE Transactions on Knowledge and Data Engineering 7(4):513532, 1995.

6. Jensen, C. S., and C. E. Dyreson (eds.), M. B ̈ohlen, J. Clifford, R. Elmasri, S. K. Gadia, F. Grandi,P. Hayes, S. Jajodia, W. K ̈afer, N. Kline, N. Lorentzos, Y. Mitsopoulos, A. Montanari, D. Nonen,

7. Hofstadter, D. R. G ̈odel, Escher, Bach: An External Golden Braid. Vintage Books, New York, 1979.

8. Melton, J. (ed.). (ISO Working Draft) Temporal (SQL/Temporal). American National Standards Institute X3H2-97-135, International Organization for Standardization DBL:LGW-013, April, 1997.

9. Gregersen, H., and C. S. Jensen. Temporal Entity-Relationship ModelsA Survey to appear in IEEE Transactions on Knowledge and Data Engineering, 1999.

10. Laravel. https://en.wikipedia.org/wiki/Laravel.

11. PhpStorm. https://en.wikipedia.org/wiki/PhpStorm.

12. Microsoft SQL Server- https://en.wikipedia.org/wiki/Microsoft_SQL_Server

13. MySQL Workbench- https://en.wikipedia.org/wiki/MySQL_Workbench

14. Bootstrap_(front-end_framework) https://en.wikipedia.org/wiki/Bootstrap_(front-end_framework).

15. Model-View-Controller–https://en.wikipedia.org/wiki/Model%E2%80%93view%E2%80%93controller

16. Jensen, C. S., and R. T. Snodgrass. Semantics of Time-Varying Information, Information Systems 21(4):311352, 1996.

17. Jensen, C. S., and R. T. Snodgrass. Temporally Enhanced Database Design, to appear in Object-Oriented Data Modeling, M. P. Papazoglou, S. Spaccapietra, and Z. Tari (eds.), MIT Press,Cambridge, MA, 1999.