LOGICAL SCHEMA

only for RuBoard - do not distribute or recompile

LOGICAL SCHEMA

The implementation of true retrospection for any entity, attribute, or relationship requires that the existence of the lifespan of the object in question is recorded. This must be in the form of a period marking the starting and ending times. The introduction of such periods changes the structure of the entities. For instance, the customer circumstances from the Wine Club model has a requirement for true retrospection on its own existence as well as the relationship with the sales area and the customers' addresses. Each of these would be given their own existence period attributes as is illustrated in the diagram in Figure 6.2. Figure 6.2 shows how the implementation of true retrospection using existence attributes results in the creation of new relations. The relational logical schema for the diagram is also shown. A more complete logical schema for the Wine Club is shown in Appendix C.

Figure 6.2. Logical model of part of the Wine Club.

Each of the relations in the diagram is now described:

Relation Customer

Customer_Code

Customer_Name

Hobby_Code

Date_Joined

Primary Key (Customer_Code)

Foreign Key (Hobby_Code references Hobby.Hobby_Code)

Relation Customer Exist

Customer_Code

Customer_Exist_Start

Customer_Exist_End

Primary Key (Customer_Code, Customer_Exist_Start)

Foreign Key (Customer_Code references Customer.Customer_Code)

Relation Customer Address Existence

Customer_Code

Customer_Address_Exist_Start

Customer_Address_Exist_End

Customer_Address

Primary Key (Customer_Code, Customer_Address_Exist_Start)

Foreign Key (Customer_Code references Customer.Customer_Code)

Relation Customer Sales Area

Customer_Code

Sales_Area_Code

Sales_Area_Code_Exist_Start

Sales_Area_Code_Exist_End

Primary Key (Customer_Code,Sales_Area_Code,Sales_Area_Code_Exist_Start)

Foreign Key (Sales_Area_Code references Sales_Area.Sales_Area_Code)

Foreign Key (Customer_Code references Customer.Customer_Code)

Relation Hobby

Hobby_Code

Hobby_Name

Primary Key (Hobby_Code)

Relation Sales_Area

Sales_Area_Code

Sales_Area_Name

Primary Key (Sales_Area_Code)

The logical schema has been included as an aid to clarity and is not intended to prescribe a physical model. Performance issues will have to be considered when implementing the solution, and some denormalization of the above schema may be appropriate. However, the above schema does present several examples of true retrospection.

True retrospection of an entity is exemplified in the existence of the customer by providing a separate relation containing just the customer code and the existence period.

True retrospection of an attribute is shown by the customer address relation.

True retrospection for a relationship is shown by the customer sales area relation that records the relationship between the customer and the sales area. In each case the primary key includes the start time of the period attribute.

False retrospection is supported for the relationship between customers and hobbies by the inclusion of the hobby code as a foreign key in the customer relation.

The customer name , for example, also has the property of false retrospection.

only for RuBoard - do not distribute or recompile