ASSIGNMENT 1 PHYSICAL DATABASE DESIGN (CPT309)

SEPTEMBER 8, 2015

ASSIGNMENT 1

PHYSICAL DATABASE DESIGN (CPT309)

ASADHU SHUJAAU (33475) FACULTY OF SCIENCE

Assignment 1

Table of Contents Abstract ...................................................................................................................................... 2 1. Introduction ............................................................................................................................ 3 2. Background ............................................................................................................................ 4 3. Focus ...................................................................................................................................... 5 4. Discussion .............................................................................................................................. 6 4.1 Features ............................................................................................................................ 6 4.2 Implementation Solutions ................................................................................................ 7 5. Conclusion ............................................................................................................................. 8 References .................................................................................................................................. 9

1

Assignment 1

Abstract Some of data needs to be recorded in databases in such a way related data’s history can be viewed. This can be achieved even with a relational database model. However, temporal database systems are specifically designed for this purpose. It uses a valid time and transaction time attribute to identify records uniquely on a given period of time. Such databases can be used to record a company’s financial history or an employee’s performance and improvements as his or her work progresses. This report will look into exactly what a temporal database system is along with how it all started and why it had to be developed. Report also takes an indepth look at some of its features and different ways of implementing temporal databases along with the implementation challenges faced as of today.

2

Assignment 1

1. Introduction This report will explore the concepts used in the temporal database systems. Report will look into what temporal database systems are and their features. Also the report will look into the history of temporal database systems and how these databases are implemented in real life circumstances. The report is divided into five main headings. In this section, that is the introduction, will focus on briefing about this report followed by answering the question what is a temporal database system. Next section, background, will look into temporal database systems in-depth covering its history and features. Main focus area for this report will be discussed third section which report focus. Focus area will then be critically analyzed in the discussion section of this report. Finally, the conclusion will sum up the report based on the findings. Before moving on to next section, now we look into what a temporal database system really is. Temporal databases extend conventional databases by associating timestamps with facts (Torp, Jensen, & Snodgrass, 1997). Conventional databases normally updates the database as required without keeping a history of the edited data. Unlike the data stored in these non-temporal databases, temporal databases records a time period expressing when it was valid or stored. By adding a time period, data can be stored in different database states and show changes that occur throughout time (TimeConsult, 2005). An example can be a staff getting a promotion which changes his rank and salary. Temporal database will show old data with his or her old salary and job with the respective time period and latest data will show the improvement in salary and change in job and when the change occurred.

3

Assignment 1

2. Background As temporal databases store timestamps to record different states of the database, it raises the question of how this is done. There are 2 philosophies of time that are applicable to temporal databases. One, valid time, and second, transaction time. Valid time shows the time period which is true to the stored data with respect to the real world. Transaction time is the time data is stored in the database. Due to these time factors temporal databases take different forms; historical database stores data with respect to valid time, rollback database stores data with respect to transaction time and bitemporal database stores data with respect to both valid and transaction time (TimeConsult, 2005). By developing and using SQL it was found that when date columns are added key fields some issues surface. For example, if a date field is used as with a primary key column number of records to be saved increases. The issue was recognized in 1992. Development of temporal extension to SQL was proposed by Richard Snodgrass in 1992. 1993 TSQL2 was by database development committee (Snodgrass, Developing Time-Oriented Database Applications in SQL, 1999). Later on updates were brought to TSQL2 to finally publish the final version of TSQL2 Language Specification on 1994 (Snodgrass, TSQL2 Temporal Query Language, n.d.). As of today, SQL:2011 includes table definitions for valid time, transaction time and bitemporal time tables (Kulkarni & Michels).

4

Assignment 1

3. Focus This report will focus on 2 areas of temporal database systems. Namely, the features and implementation solutions of temporal database systems in real life circumstances. This section will point out the features of temporal databases and the different implementations solutions for temporal databases. The discussion section will deeply look into these two areas. Below listed are the features of the temporal database systems as discussed by (Kulkarni & Michels) and (IBM, 2012). 

Has a time period datatype



Includes columns to include valid and transaction time



have bitemporal tables which stores data with respect to both valid and transaction time



Transaction times are maintained by the system without hard coding it



Chronological primary keys which includes time data



Time constraints that holds referential integrity without causing redundancies



Can be queried for times of current time which gives result for a duration within the queried time. Can show past or future time values



Automatic splits and merges of time periods when updating or deleting data



Coding required in latest temporal databases are significantly less

As per (TimeConsult, 2005) the solutions available to implement temporal database system are:

5



Using a type date supplied in a non-temporal databases and building temporal support



Implement an abstract data type for time



Extending a non-temporal data model to a temporal data model



Generalizing a non-temporal data model into a temporal data model

Assignment 1

4. Discussion 4.1 Features Temporal database systems has time period datatypes and includes columns to store valid time and transaction times. This means temporal data management can enable companies to track and search historical, current and future conditions in a straightforward and efficient manner. Knowing valid and transaction time can simplify auditing. This can also help to correct human errors and ensure the integrity of data over time. Changes that occur overtime in a business or any other applied field can be assessed (IBM, 2012). Transaction times are maintained by the system without hard coding it. In relational databases and other databases, times are hard coded into database applications, triggers and stored procedures. This means it takes extra effort, time, money and storage. Instead companies using latest versions of temporal databases or add-ons that support temporal database features for relational databases can use simple SQL statements to tell database to automatically manage multiple versions of their data which is based on time. Moreover, with help of this feature it enables tracking dates effectively for changing business conditions. Automatic merging and splitting of time data while modifying in temporal databases enables to simplify updating and deleting records without having to worry about possible updates that have to be made to other linked records as the system handles them automatically (IBM, 2012). Coding requirement is reduced in latest temporal database versions. The latest implementation reduced coding requirements by more than 90 percent over both homegrown implementation. As per (IBM, 2012) implementing just core time logic or Java technology required 16 times and 45 times as many lines of code respectively as compared to current temporal database features. This means development costs can be reduced drastically. IBM explains more by adding that “it took less than an hour to develop and test these few DB2 SQL statements. By contrast, the homegrown approaches required four to five weeks to code and test and both provided only a subset of the temporal data management support built into DB2”. This points that lots of hours of coding, money and total cost are reduced by implementing the latest versions of temporal database systems.

6

Assignment 1 4.2 Implementation Solutions Using the type date provided by a conventional databases and implementing abstract data type for time are 2 of the solutions of temporal database implementation. These 2 methods does not involve changing the current relational database. It might be easier to get temporal support for these 2 methods just by adding temporal database support for the existing database (Patel, 2003). One way of achieving this is using timeDB which is a bitemporal relational database system based on SQL. It supports a query language, a data manipulation language, a data definition language and assertions/constraints to deal with time related data. (TimeConsult, 2005). Generalizing a non-temporal data model into a temporal data model or extending a nontemporal data model into a temporal data model is another solution for dealing with time data. This solution will involve thorough understanding of the current data model in order to extend or create a new temporal model. This process will take a lot of time and money hence all the underlying principles used in conventional databases to optimize operations must be reformed so that it is optimized to handled time related data and theoretical work needs to be carried out to show modifications so that others can understand too. As of today, second method is still expensive and not widely used so many tend to follow first to methods to implement temporal database systems. However, they still face challenges when implementing such systems. Below listed are some of these challenges as noted by (Data Science Central, 2015). These challenges occur as it is still not fully adopted and extensions are being used to deal with time related data. 

Having to add valid time and transaction time columns manually adds more fields to primary key



When add-ons are used for temporal support complexity of queries is increased



When add-ons are used updates have to be made manually to all affected records because of one single update or delete

These problems can be overcome by using a fully developed temporal database system. They support time-range data type. The uniqueness of records are guaranteed without having to hardcode time data. Data can be identified uniquely in any moment in time. Where clauses are not always required hence simplifies the queries. Updates and deletes will enforce referential integrity and minimize manual work.

7

Assignment 1

5. Conclusion Every database will not need temporal database implementation. There are many cases where it is best to keep only the latest information. However, some do need temporal database implementation. Banks will probably need such a system to track customers and their own financial details and companies may use them to keep track of their performance. However, as of today, it is still not widely accepted and used. Hence many challenges are faced when dealing with time related data. Future improvements and full acceptance of temporal database systems can help nullify or reduce the challenges faced in dealing with time related data. As they help reduce manual workload and simplify and optimize time related data and provide historical and future data which companies may and can use to benefit in better decision making.

8

Assignment 1

References Data Science Central. (2015, January 12). Temporal Databases: Why you should care and how to get started (Part 3 of 3). Retrieved from Data Science Central: http://www.datasciencecentral.com/profiles/blogs/temporal-databases-why-youshould-care-and-how-to-get-started-2 IBM. (2012, April 3). A matter of time: Temporal data management in DB2 10. Retrieved from IBM developerWorks: http://www.ibm.com/developerworks/data/library/techarticle/dm1204db2temporaldata/dm-1204db2temporaldata-pdf.pdf Kulkarni, K., & Michels, J.-E. (n.d.). Temporal features in SQL: 2011. Retrieved from SIGMOD. Patel, J. (2003, June 18). Retrieved from http://www.doc.ic.ac.uk/~pjm/teaching/student_projects/jaymin_patel.pdf Snodgrass, R. T. (1999). Developing Time-Oriented Database Applications in SQL. July: 26. Snodgrass, R. T. (n.d.). TSQL2 Temporal Query Language. Retrieved from Computer Science: http://www.cs.arizona.edu/~rts/tsql2.html TimeConsult. (2005, May 21). TimeDB - A Bitemporal Relational DBMS. Retrieved from timeConsult: http://timeconsult.com/Software/AboutTimeDB1.0.html TimeConsult. (2005, May 21). What are temporal databases? Retrieved from TimeConsult: http://www.timeconsult.com/TemporalData/TemporalDB.html#Temporal Databases TimeConsult. (2005, May 21). What kind of Solutions are available today? Retrieved from TimeConsult: http://www.timeconsult.com/TemporalData/Solutions.html Torp, K., Jensen, C. S., & Snodgrass, R. T. (1997, March). Stratum Approaches to Temporal DBMS Implementation. Retrieved from TimeCenter: http://timecenter.cs.aau.dk/TimeCenterPublications/TR-5.pdf

9