 The first thing I should really tell you about is what the purpose is of the fourth dimension and how do you track that data across it What's the purpose of in the first dimension... How do we experience the $Ùs to being 3D But physicists typically talk about the fourth dimension, that is time.. Ion y gweithio rych yn fawr, mae'r dataeul yn bwysig a'r dataeul yn tarinh yn eitha ffraith. Mae'n gweld i bwysig y byd i ymddangos y cwestiwr, mae y gallwn hyn mae'n gweld i bwysig. A byd i'n gweld i bwysig eitha ffraith i bwysig y byd i bwysig? Mae'n gweld i bwysig y byd i bwysig y byd i ffraith i'u bwysig yn eu bod yn tuol iddyn nhw y byddai. Yn mynd yn i bwysig y byd i bwysig y byd i bwysig. Do you think about SQL we have got a rich macro that describes things, times down, state times etc. What is different about temporal data that can't be expressed using what SQL already has? To answer that question, I don't really know what you think about what I think that databases are good at, and that is now. Think about the life cycle of using data, the problem with the system is creating and inserting with this system. Ond, dyna, yn ddweud yn y dynod ond mor unrhyw, os yw Lleithfodol, i wych mewn eu gwirionedd? A gwrs y mynd i eisteddol, sydd â'u gwirionedd? Maew, eich rôl yn y ddweud hyn sy'n ddweud i gwirionedd? Yw'r mynd i ddweud? Rhyw hollwm yn newydd o fynd o fawr, yw hwnnw – ar sy fwyaf? A'r fawr o'r ddweud, mae'r rôl yn y fawr a'r ddweud y fawr o'r ddweud? Rwy'n gyfeirin, a gallodd ddim yn gyflym, Felly, mae'r cyfnod gyda'r cyfnod yw'r cyfnod. If I want to know given my categories for the best sellers 12 months ago, I will only track in the current stage of the data that cannot know this. So, to answer the question of what temporal database is, a temporal database allows you to go back to any point in time in the past, present, or future and to conceive a visualisation of a data as it exists at that point in time. It also allows you to query across time ..daw i'r rhaid i fynd i ddechrau, sy'n dweud ychydig i chi'n gynyddu'r cyflwyno. Yn yw'r cyflwyno sy'n dweud o'r cyflwyno yw'r cyflwyno. Ond rwy'n gweithio i ddweud o ysgol, o'r ddweud o'r defnyddio, o'r ddweud o'r rhaid, o'r ddweud o'r defnyddio, o'r ddweud o'r defnyddio, o'r ddweud o'r gyflwyno. Ond mae'r gyflwyno ar y twdd yn ymddangos o'r dweud o'r tenrwynau cyflwyno. ..mau i'r cyfrydd. Give-up. Mae'r cyfrydd y cwmng na'i o'r cyfrannu, oherwydd mae'r cyfrannu yn mynd yn dweud, fyf leather diolch yn gwm correlation i'r cyfrannu. Mae'r llyfr yn ddim yn ei wneud. Er mwyn ychydig hi'n ei ddraethio'r newidau. Mae'r cyfrannu yn cyfrannu yn y ddraethio'r newid. Mae'r cyfrannu yn ei dddodol, mae'r cyfrannu yn gwneud o'r cyfrannu i'r ddodol, sy'n symud i'r cyfrannu'n cyfrannu o'i ddodol. I'll like to think I better explain this stuff now. But let me see aside, the reason I put this slide here, it's not that this theory is particularly hard, but it demands a mental shift. I think Kerry was just talking for a while ago that when you move your mindset, this is good as getting out of you, she said 18 points in that IQ test. I can't say anything about the IQ points, but it definitely demands a mindset shift to think about the temporal data. So the need for the temporal support was then originally identified in SQL back in the early 90s, and there were various attempts to make the part of the SQL standard, but it officially became part of the SQL standard in 2011, and I think the temporal model was regarded as the most significant division to SQL and in many, many years. So it's well worth understanding what it means. The concept of temporal theory is this concept of temporal aspects, and an aspect that just describes a different type of temporality that you might want to track. So current theory defines three temporal aspects. That's not to say that the only three that could possibly exist are not the only three where you can talk about how the data evolves across time. They're apparently what's understood commonly, and what is commonly tracked in databases. The important thing about this is that temporal aspects simply be used all the time, every day, in our conversations. I can say to a sentence like, In the 1990s, I considered the New Order of the Greatest Bank of the 90s. However, I now think that Radiohead is the Greatest Bank of the 90s. In that sentence, I've just used two different temporal aspects. Now, what those are, and how they work, I'll explain to you in a few minutes, but bear in mind there's nothing new about this. We use this all the time in our conversation. The first temporal aspect we'll talk about by far the same as what was decision time. Decision time simply recalls the time at which, the point of time at which a decision was made about the fact. Now, on the side, I should say, the temporal theory of a fact is a single row in the database. Not a single column or a single value, but a single entire single row. Now, all become important a little later, but just bear in mind for now. So, what you might imagine, decision time is a very easy track. It's a single value, granularity is up to you. You might want to know the year, the fact is taken in, you might want to know the millisecond fact is taken in, but the decision is taken out. But you can also imagine it's extremely easy to record. For example, here, you can see that we're recording some HR data, employee's names, data hired, and the decision was taken to hide the decision time there. So, if you want to track decision time, you don't need anything special in the database, and those specials are more than required. Many applications are probably doing this already. However, the next aspect is something a little tricky. That's called valentine. So, for a definition of what valentine is, let's go back to what we need here. First of all, databases. Valentine is the time period during which a database bank is valid in the model created. So, remember a fact again is a single row of databases. That's how it's modeled always at the rank in between, two points in time, beginning and end. The lower bound is always closed. Valentine is always at the beginning. This thing is always valid as or at a certain point in time. If we currently believe it to be true, the valentine has no up bound, it's not closed. However, the closing time can also be in the future. It's quite possible in the world, we can say, this thing is valid, we know this thing is going to cease to be true at a certain point in the future. Valentine allows us to model this. But for now as well, there in mind is this also called application time in the theory of why values will become apparent and important at a later on point. So, let's take a look at some sample data using modeling valentine. Again, it's an example of some HR data. We don't track names, positions, and a valid time period for when this is back to valid. Straight away, you'll notice that tracking valentine implies storing more data. We simply don't delete anything in a previous state. Otherwise, how can we go back and say what was valid at a previous point in time? And another thing, because the granularity is down to be a visual role, there is by definition a lot of duplication of example data. Typically, when we update data, you see in this example we're only changing the position that this person has at a company. We'll typically change it in one or two columns at a time, but we have to duplicate the entire row and just change the values, but we're interested in getting that granularity. So, this is tracking the evolution that I've only seen about the PJ Dev's manager. Interesting one here is Johnny. So, if you query the data today and say what position does Johnny have, it will come back with director. He is forecast to become the vice president on the 1st of October this year. So, if you were set to the database, tell me everything about positions of people in this company as of the 1st of December 2016. Johnny will be a vice president. If you were set to the database, obviously, on the 1st of January 2016, his position was incorrect. Valid time can be used to schedule changes in the system that automatically come valid when that point in time takes over. Does that make sense? I should encourage you, I should say that I would really encourage you to ask me the many questions as possible about the theory. The most important thing you can take away from this talk is an understanding of the theory behind technical places. If we run out of time later, that's a fine and escape to some of the latest value. Is that clear for a valid time? Great, that's fine, good. So, at this point, you might be thinking, well, okay, the temporal data, we've got it. Unfortunately, it's not that easy. To illustrate that, I want us to imagine now that we're taking a database table that models celestial objects in our solar system. For this example, we're just going to say sac and buta. All I'm going to record is the name of the thing, the time, what is it, and what is the valid time you need. So, sac and buta is a planet. Valid time starts within three years of time, no idea where. Even I knew the exact date sac came to be, no idea how to write this in a simple big time. So, what does it say within three years of time? Valid time, as far as we know, is still a thing. Pluto is also a planet. It's valid time starts within three years of time, and is still a thing. Can any of you see what's been stated? Okay, yes, good. Okay, we've labelled it, sorry. We've re-labeled it. So, the problem with Pluto is our understanding of what Pluto is has changed over time. So, we've still been close to a planet that we believe Pluto is a dwarf planet and I think now, as of 2008, we actually officially called it this thing called Plutoic. There are a number of objects in the solar system called Plutoic. Across those three sides though, what have I done? Imagine this is a backwards table. What have I done to that table? Just take a single row and let that date to it there. Now, if all you're interested in tracking the valid time, that's absolutely fine. Okay? I've been able to flex my state of the scene and what I understand that Pluto is today. But now, we've lost the ability to look at things and let them know that we've ever looked at the answer of what Pluto is true. So, you might think to yourself, what have I got valid time? Let's model the state throughout like this. But remember, this is now a big problem because remember the valid time and models and why there's nothing of fact in reality. What the sable is now saying is that billions of years ago until 2006 Pluto was a planet and then a shrine. And 2006 to 2008, Pluto was a dwarf planet. And then it expanded again that not as much as before to become a Pluto. Now, that's clearly ridiculous. Pluto hasn't changed our understanding of what it is that's changed. So, if you need to track that, you cannot do the valid time alone. For that, you need another temporal aspect. So, that aspect is called transaction time. So, let's take a look at the quick definition of that. In temporal databases, transaction time is the time period during which a fact stored to a database is considered to be true. So, think about it that valentine models what is this thing of fact in reality. Transaction time is the time period that is existing of facts in reality. Transaction time models when to be the leader in this moment. And what transaction time is particularly useful for is all it does. You can now say with transaction time and valid time in combination not only what was true in reality at any point in time, although there's a lot of applications I'm working with the HR ones, but for HR data it's incredibly important that we think about that in the trace. Only what was true about this is that I'm sure you can get a lot of legal ramifications around that. So, how do we model transaction time? Simply, it's exactly the same as valid time. It's a range between two points in time. The lower bound again is always closed. It must always have a beginning. If we currently believe this thing to be true, is it true? There's no other valid thing. And, like valentine, it also has another name, an assistant type. And again, the other mind, why doesn't what be true become apparent to a few years? So, a second look again at Pluto. This time we're going to model valid time and transaction time. Now, we then look here with three words. Now, Pluto was discovered in 1930. Its valid time is, billions of years ago when it stood its, that doesn't change across our evolution at this back place, that part of the reality hasn't changed. Transactions are, we must say, that we believe Pluto was a planet until 2006. 2006, our understanding changes. We now believe that Pluto was an awful planet. And, we believe that until 2008. In 2008, it's a third row, what we currently believe is as Pluto going forward. And that one there has no ending point in time. It's what we currently believe to be true. But now, I can say to this database, in the year 1400, what was Pluto in the year 1931? What did I believe Pluto was? It was a planet. Does that make sense? Do you understand the difference that Pluto had a transaction time? Sorry. Anyone else? Any other questions before you go on? So, bear in mind, transaction time, it should be apparent for this, transaction time and valid time occupy different periods of time. There's nothing that says what you usually do. You can imagine a system where the validity of the information of the time system knows about it when it says it's exactly the same. But that isn't the usual case. All right. How many temporal aspects should you use? Well, there's really as many more exclusive user application views. Typically, if you talk about a table that has a simple temporal aspect, it's being a temporal table. Two is bite temporal. It's the temporal aspect you try to optimise the complexity of your data model. If you're dealing with valid time and transaction time, you have an awful lot of extra complexity to try. And some of that, again, will look at it a little bit later. But you need to bear in mind what the story of data I'm inquiring for and how this is going to work. All right. Theories are up here. We're basically getting better at this now. So, at this point, let's take it real quick. We have three temporal aspects that we haven't understood today. Decision time records the point in time when a decision was taken about the fact that Rowan did this. Valid time records the period of time for which the fact is considered to be true in the model reality. On transaction time, records the period of time for which the fact is considered to be. By the time it records the period of time for which the fact is valid in the model reality, transaction time records on its considered to be true. In the different times Roedd eich fodwch wedi wneud eich penlau o'r cyd- fantasym a'r cyd-gym Lithu, ond ond y syniic clicked i'r creu atau'r cyd-pyfeуск yn dewwod mewn cyntafol lefnod i'r cyneswy teaspoon i d ONAL Ac oednod a'r cefnod brewi'n gweithioo reion iawn i gyd- الكrwydd rhed motherf premiere o hyd oren Crolwsthofunr seidio adeilad, Efallai meowwydedheb hyn니까u hi oma arlaffn whdredaluaint Erd roedd y d economiaid yma hwy wedi dysgu y gallu ddau cyd-dymedd. Ond bod yn clywed i ni'n ddau cyd-dymedd ar gyfer, ac dwi'n ddet yn y ei wneud a ddau cyd-dymedd. Roedd yn ddau cyd-dymedd yn rhoi ddau cyd-dymedd yn rhoi ddau cyd-dymedd, a yna ein bod ydych yn ddau cyd-dymedd yn rhoi ddau cyd-dymedd. Ac yna dderbydd ar gyfer eraill iddo yn ddau cyd-dymedd ac ar hyn i gyd, the end time has to be greater than the start time when it were on the vice. So, if we are tracking valid time, you must go to a 21. The source of author book application time, the key to this is, say who was responsible or was responsible for tracking this time period. Your application is entirely responsible for tracking valid. System doesn't do it for you. What that means is that when your students say you are responsible for saying I can say to you I used to believe the sky is green, but now I believe it's blue. I currently believe it's blue, but there was a period of time I used to believe it was green. Whatever the reason, that's my crazy example. All I can tell you is what I currently believe to do in the future. All the system can tell you what it currently believes to be true. It has no knowledge of what is absolutely true in the real world. It just knows what you tell it. Does that answer your question at all? If it's from the air, the air, the air... No, it's fine. Any question is fine. But again, I think all we can know from the application is what we tell it about the state of the world. That's sure. So for the HR data, for example, let me give you an example of this. I interested in the question about somebody and I made some mistake in that data. I come out of that data later, but then the company says because he got this wrong, I have been, you know, it's cost me this much money. If you're not tracking, this will be a transaction time. If you're not tracking transaction time, you have no idea of going back to your database and saying, what did you know about this person and when did you know about them? So it's incredible news for all it that this is. I took part in the call with Oracle about it eight months ago. We asked them how customers were using father time and transaction time. And they came up with an example of a financial institution that was taking a fee of shared prices. Now, the share prices are valid in valid time, but for period of time in which they're basically true. So they can use valid time to trace the evolution of the stock price at the time. And it will be fine if there were never any mistakes in the fee that they frequently are. So as later we can come to that in corrections. And they can then use transaction time to update the share price at a point in time in the past. But for all it does is they can prove they did not know about that share price until this later point in time. So those are a couple of examples of the way you might want to consider using transaction time. And to be able to prove what you do and what you do about it. Does that help? It does demand a little bit of a sideways shift in how you think about data. It's an interesting one, but I find the more I think about this, the more applications I need to see tracking data time. It's more data involved at the time. All right. So we've got a pair of columns. In the period of time of break that's how we model valid time. Names of columns in the next period of time to you. There's a special semantics around having a date next. This is a period for valid time. Once you've got a valid time period in your table, it has huge implications of boundary keys. So traditional people normally take some sort of, very often takes an incrementing value as a boundary key. As we've certainly worked on changing boundary keys upon to etc etc. If you do that with temporal data, you've now got a huge problem. Because you now cannot trace back any degree of knowledge, what was the illusion of what further was. So if you're probably making changes on each other, because each time your valid time period changes, and each time your employee changes to a different role, how can you say that these rows are connected? This is an evolution of a fact. So as you go to what you learn says, that you should never make a valid time period part of your primary key. And the other interesting thing this allows you to do is to enforce whether or not valid time periods should overlap. There are cases when that is definitely allowed, or cases which should be. Again, at the age of a name, I'll give you two examples. A person can be an employee at a company at two different time periods, if they even come back. It doesn't make sense to allow those valid time periods to overlap. The same person can't be two employees at a company at the same point in time. However, a contracted code, a contracted could be working due to contracts for a company at the same point in time. So if you want to contract it later, allowing valid time periods is never allowable. And a temporal primary key will allow you to enforce whether or not that is valid. Once you've got the primary key, you've now got massive knock-on effects on your foreign keys. So let's take an example here. Imagine that parents and a child take both valid time periods. Let's again use HR, the parts employees. Employees of all the time, the parts of all the time, the names change, the locations change, etc. It should not be possible for me to assign an employee to a part that exists in the past, or that only exists in the future. So what SQL 2011 says is that you can create a temporal foreign key. And it says that the role in the child table must be constrained by one of all particular roles in the parent. So we don't care the fact that the part has changed over time. If the part that's made changes from X to Y, as long as there's valid time periods, it will be exactly a butting. And as long as they overlap the valid time period of the child with the employee, that constraints will be satisfied. As soon as the part that ceases to exist, and the employee is going to do exist beyond that, that constraints will have broken. That's not valid at all. Does that make sense? So querying valid time tables. The good news is, all you've got is two new columns on your table. You're just querying them exactly as normal. There's nothing changed as that. However, SQL 2011 gives us a whole bunch of new sugarcoats that can allow us to deal with this data. So, first of all, you can make updates for a period of time, or deletions for a period of time. You can say this employee is going to the back door, therefore they're deleted for three months. The database isn't entirely responsible for working out. What does that mean for a model? You can say update or feed for a portion of valid time. You can also construct periods in your queries and use a whole bunch of new pre-cades to actually query for the data so that you can say, return to me, all employees who use valid time overlaps this period, doesn't retain this period, encloses this period, bugs into four or after the song and so on. There's a whole bunch of ways you can do this. Or you can simply say to the database, return to the information about employee X before valid time, as of now. And the database is trying to do that. Again, application time, valid time, you are responsible for saying why you don't want to see the data as of. Transaction time. Also known as system time in SQA 2011. And as you might guess, this means that the database is entirely responsible for managing transaction time for you. When you assert a row, the transaction time beginning is set to now. When you update a row, the row is copied, the old row, the transaction time end is set to the point when you make the update, the new row doesn't transaction time start as of now. When you use a row, transaction time closing is set, the row exists or stays in the database. So transaction time becomes more, what is this thing? Considered to be true in reality, it's more when does the database know about it. It also means that if you can prove your database in FMSSQA 2011, when you're using transaction time, you've got a cast time or a guarantee. Again, you can use datal time stamp columns to multiple transaction time periods and cast time columns of a period over the full transaction time. There are a new period cases to query across transaction time periods as well. Crucially, the way you query the database is different. Because it's managed by the database, the standard says that if you don't specify what period of transaction time you have for the data add, the database will know what you currently need. Now that must be multiple rows You have to especially query to say what do I believe about future in 1936? Or is there a way you return to the stream or will you be the one to go now? Okay? Yes? Why would you ever do it if you were wrong? Why would you ever do it if you were wrong? Or at least this. So, typically, again, an HR data is regarded as the privacy law statement which would only keep the data for as long as we actually need to have it. Possibly. I mean, if I'm destroying sensitive information about employers, which we frequently are in HR information systems, it's considered, I believe, five years specifically about the value of time. So, which can come down to a different area. Your database is storing all this knowledge going all the way back. You can remove the data from the system beginning with deleting the transaction time of the base data, but if you haven't managed that. Now, because the database is responsible for managing transaction time, you cannot change transaction time once the database is set up and you cannot change them. And it's also not possible to add any future data transaction time. If you think about it, it makes sense. You're effectively saying, I believe this data until this point in time. I believe this thing is assured until this point in time it ends. None of us know this. We only know what it is. Does that make sense? So, the system is guaranteeing that I'm ordered to build a single data for you. All right. A billion-dollar question. A $11,000 to be quite honest with you is a bit of a mix bag. If you're starting a new application today and licensing fees are not an issue, I would say go with idea D2 for what I've read for the only vendor that is fully supported today. If anything, they have temporal primary keys, temporal foreign keys or the DML or any of those. That's quite impressive. Oracle 12c has a valid time of transaction time support, but limited DML Mae ni wedi gweld o flwyddyn o fawr o gwyloedd o'r gwleid diwylliant, felly mae'r ffawr yng Nghymru yn cael byd yn unrhyw yng ngrysdoedd, felly haith yng Nghymru yn cael byd yn cael byd yn cael byd yn cael byd mwyoedd. Felly, mae'n ystyried i'r hyn ar gyfer y lleonidion byddwn ni'n gwycofydd o'r hynny, oherwydd mae'r thaiwch yn cael byddwch ein hynny, If you do this today, however, it makes your life pretty hard, and you will have some big problems with your career. Temporal priorities and temporal forum is the top of this. You've got to enforce these things yourself. If you don't wanna go back in by this time period, you've got to make sure your later doesn't have any. And if you wanna make sure that a child wrote and a parent wrote makes sense temporarily, you've got to give her as well. Secondly, your formulas list. If you're adding this stuff to an existing application, you start to really take huge problems. The good is your data is similar and stored in a single table. The baddest for anki has become very hard even with three columns right in that for anki is true. Another approach would be to use two tables and this works really well when all your investment in anki is tracking the evolution of the type of things that are in the past by the time it evolves. As soon as you need to schedule for your data table this gets tricky. You would have a main table which only records the current state of the data and a back-up table which records the state of the data of the other periods of valid data. As the data changes, whether you do anki application or a database, you move the old state into the back-up table. If you need to track the future data, then promoting that data becomes very hard in the main table. You probably need some sort of process that's going to run through, find things as they're becoming valid in the main table so that every precision you achieve with that is your application. You require that the data is instantaneously valid depending on what type of query you're getting at. If I'm updating half a million rows, I'm not prepared to accept that it takes me three minutes to copy that data across. During those three minutes my data isn't valid. So that's a big problem. Transactional time is a little simpler. The classic way of doing it is to give us two tables and as the data changes copy the previous data back into a back-up table. So there's lots of things to do to go about that. In terms of implementing this as well, extra complexity helps to keep the back-up table for. Do you optimize the faster speeds of faster flights to shuffle a lot of this data around so that it has an always-impact mode? And whether you optimize your back-up tables for faster speeds of faster flights is largely a factor of half a million to the greatest in four. I'm pretty much out of time. So I would strongly recommend, take a look at the Wikipedia article on temporal databases. This is some good, high-level overview of some other interesting examples, particularly around tags for temporal data. The second link here is to about a six-page media, and it talks in quite a lot of detail about the new table features in 2011, how to create the data, how the data should behave. It's a lot of difficult being on a stronger end. So at this point, any final questions? Yes? Well, who are you? We use Oracle, so I love your message, so we'll pretty much work with the hit, Oracle. We started investigating temporal data. It's been on a mind of two years. It's going to become a reality probably next year for us. We started looking at Oracle 12c, and the deal break for us was that that is horrible. Because if you consider the parts we're placing in that example, if the parts evolve in time, so you've got a once-many relationship, other way you should have one-to-one. But if the parts evolve across time, you've now got another one-to-many relationship with no back-up table. Without native support for tackle for our keys, we probably end up having to drop it straight into the two tables. I'm not sure how Oracle would behave if I tried to have a one-to-many, a reference one-to-many on the way back. I'm not sure how it would work. So when we started talking to our DVs about this, they were like, oh my God, no, you can't do that. So we are using a two-table solution about DV2 is really the only one that's going quite out there. All right, thank you very much. Thank you for the rest of the conference.