Good afternoon, my name is Jordan Bass and I'm the coordinator for the Research Services and Digital Strategies Unit here at the University of Manitoba. This presentation provides an overview of lessons learned in developing and deploying a new research service unit at the University of Manitoba Libraries. The University of Manitoba is a research intensive medical and doctoral level university, the only one of its kind in the province of Manitoba, and a member of the U15 group of Canadian Research Universities. The University of Manitoba Libraries comprises 11 libraries distributed over two campuses that support over 100 academic programs. The practice brief itself aims to offer research libraries of comparable size and scope, an overview of our experience developing three research support services over a three-year period. Library support of research in their parent academic institutions should be informed by local contexts and demonstrated need, but should also be highly perceptible and accessible by faculty and students. This presentation will include a brief introduction for context, a description of the three research services we deployed, as well as a discussion on lessons learned and recommendations for the future. When research services and digital strategies, or RSDS, became a unit in 2017, significant organizational changes took place within the University of Manitoba Libraries and within the larger university itself. As new projects and priorities arose through the 2017 to 2019 period, the pace at which RSDS could deliver on its service goals was impacted. Research support initiatives were reprioritized to address new objectives that redeemed more immediate and time-sensitive. Consequently, some RSDS initiatives were suspended or slowed in their development, and the unit has since recalibrated its focus and goals with these shifting priorities. Our key performance indicators are limited in some areas due to the fact that many of these research services are still new and measuring their success at this juncture would be over the speculative. Our final section, Recommendations for the Future, discusses the importance and necessity of quantifiably measuring the impact and performance of our research services. Quantitative data was drawn primarily from the University of Manitoba Libraries' repository systems, while the qualitative data was derived from personal files, team and vendor correspondence, as well as project documents. Our institution has not collected indicator data that would serve as drivers for our research services. One of the future priorities for the unit includes a more coordinated, efficient and purposeful gathering and reporting of the unit's activities and impact. To date, the research data management RDM support has been focused on our instance of dataverse, as well as our M-space institutional repository. In addition to hosting and supporting these two data repositories, the University of Manitoba Libraries offers additional RDM support in the forms of data management planning, data deposit assistance, as well as RDM consultation, which involves librarians working with faculty members and or research teams at various phases of the research project lifecycle. Most of this work occurs leading up to our during research deposit within our data repositories. The implementation and ongoing maintenance of M-space and dataverse occurred sometime before the promotion of these repositories to researchers began in earnest. M-space, which serves as the institutional repository, is populated primarily by electronic theses and dissertations. Until very recently, faculty researchers have deposited their work at a much lower and less frequent rate. Though the open access movement was adopted by the libraries early on through the establishment and then dissolution of an open access fund, uptake of open scholarship at our institution has been slow but steadily increasing. In support of data deposit, UM Dataverse was launched in 2016. It could be said that its launch anticipated the need. With RSDS established a year later, one immediate priority was to promote and provide support to data deposit. In tandem with this support was an institutional gap. For a time, there was no reasonably secure data file share option available to researchers that was not a third-party cloud service. As a stop-gap measure, researchers were using UM Dataverse to meet that need. Consequently, the majority of research data deposited in UM Dataverse was restricted to the research teams responsible for that content. This places UM Dataverse repository uniquely as engaged in the active stages of the research project where data is actively being created and manipulated and less at the preservation archival stage of the research lifecycle where it should theoretically be published openly. This situation is not necessarily a negative one and has been more of an opportunity to better understand how researchers are using Dataverse. For approximately four years, the University of Manitoba Libraries have been working in anticipation of the Canadian Tri-Agency mandating the inclusion of a data management plan as a condition for their funding. The Tri-Agency's data management policy is reasonably imminent and it is likely that DMPs will be introduced as a strongly recommended component. To that end, we have started to see some research groups develop DMPs as part of their overall project planning. Our DMP service is largely based on the successful building and promotion of DMP Assistant, which was created by a Canadian library consortial group called the Portage Network, now integrated into the Canadian New Digital Research Infrastructure Organization. DMP Assistant is an online tool which contains DMP templates that can be used to guide researchers through a series of questions they must answer in order to generate and export a DMP that can be used for their grant purposes. At present, we have created one generic DMP template using this online application. Using the DMP exemplars and discipline specific templates developed by the Portage Group, we are now working on adapting these for our own community. In 2018, the libraries began to address its digitization backlog and commissioned a third-party digitization service. Due to the fragile nature of the materials within these collections, we required that the vendor perform their work onsite within one of our libraries. While the project was ultimately a success and that we processed the collection backlog within one calendar year, there were a number of unforeseen challenges to the project, most notably in the areas of coordinating library staff to assist in the project startup phase, as well as securing adequate and secure space in our library so the work could proceed while at the same time not interrupting the many other faculty and student services offered in that library. Successful completion of this digitization project has helped us move forward with the new digitization initiatives. The added benefit of this project was the camaraderie it created with our colleagues, as the project was, in the truest sense, a very successful team-building exercise. Realizing that it is difficult to quantify the value of a respectful and cooperative inter-institutional project, we never the last feel that this was an unintended but nevertheless most welcome project outcome. The University of Mantable Libraries has worked diligently to modernize how it delivers our digitization requests. In what is best described as digitization on demand, we have typically scanned images and textual documents not only for our faculty and students, but also for clients outside of our institution. Our most regular requests, I might note, are to digitize collections held by the libraries when that content is not already available online through one of our content management systems. Given that the University of Mantable Libraries has some of the best digitization equipment in the province, as well as the employee expertise to support it, we also receive requests to digitize material that is held externally by private individuals and commercial entities. The evolution of a research impact service at the University of Mantable Libraries grew organically through demand from faculties and departments who required calculations of publication output, primarily for accredited program requirements as well as grant funding agency requirements. The demand for bibliometrics and related services grew dramatically within our College of Medicine and some of the research-based basic medical sciences in and around 2013. Emerging demands for research impact services is linked to larger external forces within academia, such as funder requirements and inter-institutional competition, through rankings based on a number of different metrics. These forces have given rise to much faculty perception evaluations, case studies and analysis in the literature on the use and misuse of research metrics and the inequity that its application may cause. As with other services discussed within this brief, the idea that institutional research impact or bibliometric support services would benefit from a team-based or centralized committee governance structure, which is highly desirable. To our knowledge, the Canadian universities that have successfully implemented such an approach in the last few years have done so in part due to their respective senior administration understanding the necessity for a centralized and standardized delivery of research impact services that also acknowledges disciplinary nuance. The expected learnings that emerge from this project were as follows. First, in the area of research data management, we expected to learn that our data repositories require more support than they currently receive. However, while we did anticipate that more people being formally trained on our data repositories only made sense to provide better service, we did not conduct enough upstream training nor reinforced the necessary supports needed to accommodate increases in service demands. A second key takeaway from this project relates to the role librarians play in forming and advising the university community on national and international changes within the research industry that will impact work here at home. For example, when major grant funding agencies implemented new requirements related to open scholarship, libraries were called on to assist in meeting those requirements in terms of resources and education. In addition to hosting and supporting data repositories, online resources, as well as providing educational sessions for researchers, the libraries were and continued to be called upon to present on such matters to senior level researchers and university administration to help inform decision making. This point is well articulated in our work on research impact and bibliometrics. Dispelling the misconception that research metrics may only be applied to the detriment of some academic disciplines while other disciplines prosper is not an easy task. However, we have through our research impact service in addition to presentations on the topic become a strong voice on the topic of research metrics and more importantly their proper use and application within our institution. The immediate next step for the three research services reviewed in this practice brief include a more rigorous collecting and analysis of quantifiable data associated with our research services. While our key performance indicator initiative is still in early development stages, our intent is to use the data we collect to better inform and direct our research services. Counts of data and publication deposits, the tally of items digitized and the number of bibliometric analysis projects completed are undeniably important indicators to review for future planning. That being said, the more qualitative elements such as ongoing and meaningful engagement with research teams, establishing rapport with administrators, as well as day-to-day support and consultation for research services are also factors to be recorded in our future performance analysis. Easier said than done, of course, but we will endeavor to bring this less perceptible element of research support to the forefront in our review of performance indicators. There is much we could do to improve on what we have reported on in this practice brief. For instance, more quantitative data would certainly have been of benefit to our analysis and our reporting. Analyzing research support beyond the RSDS unit would also have added considerable depth to this report as there is much more we could do to have included to make this report a more holistic review of library's research support at our institution overall. Developing and launching new research services for all academic disciplines within our institution has and continues to be rewarding and beneficial for both the libraries and researchers. And yet, it has been a challenge to take a pause and reflect. With that in mind, we feel that the work we have done here for this practice brief could possibly be revisited in the future as part of a more longitudinal study. Thank you.


Good afternoon. Hello everybody. Welcome to Building blockchain products for UNICEF how to get involved My name is Shane O'Connor and I am an innovation manager with UNICEF's office of innovation Which is based in Stockholm in Sweden But I'm very happy to be here at DevCon with you guys Probably a lot of you know UNICEF as an organization you know you've heard of us You know what we do Maybe it's not so clear exactly Why and how we work with blockchain and technologies in general So UNICEF's mandate is really to advocate for children's rights To try and make sure that they achieve the best outcomes And make sure that their health education protection is Maximized across the world and UNICEF operates in about 190 countries around the world But UNICEF also has an office of innovation and within that office of innovation We have a ventures team and we have a special team called giga and we'll be hearing from them a little while So actually UNICEF has been working with blockchain since are looking at blockchain since about 2014 2015 And UNICEF is one of the larger UN organizations. So big bureaucratic Sometimes slow to move So it takes us a long time to try and get UNICEF to move In this type of space and to get us up to speed On how to properly use the technology that's out there In the real world to support UNICEF's programming Um My own team that I work with the venture fund We have been providing Kind of seed funding To support what we call frontier technology solutions that benefit children and the world and we have some people from a few of the Companies that have benefited from that here in the room today But really from the slide there you'll see we're looking at the intersection of The needs of 1 billion people and 100 billion dollar industries. So we look at AI Drones 3d printing machine learning and also blockchain and cryptocurrency We've actually gone through three rounds of blockchain funding cohorts We did our first cohort in about 2018 Which was a more of a kind of a thematic Call for submissions and you'll see that we had Submissions from supply chain solutions digital certificates and badges impact investing We did a second cohort back in 2020, which was more focused on financial inclusion Um and and that kind of particular not not so thematic but more focused um And some of the kind of products platforms and companies that came into that cohort were focused on humanitarian cash transfers remittances and decentralized decision-making tools We have a cohort that is about to be announced in the next one month or so And this is some of the our third cohort around blockchain. Um, and you can see some of the um kind of solutions and companies that have been involved there and Yeah, I think we'll hopefully people can see those coming out pretty soon. Thanks one And this is really exciting and sanna is going to be giving a talk later on at about 540 on the the crypto fund. So I won't take too much of her thunder here but in 2019 unicef launched Crypto fund and we were in fact the first un agency To be able to receive hold and disperse cryptocurrency So this is a a big big thing for the un And certainly a big big thing for unicef And this has really been able to help us to build digital solutions and particularly digital public goods To help us ensure a kind of equitable access to these technologies and these tools to really try and support human development obviously through the lens of unicef's focus unicef's mandate on supporting children One of the new applications again new for unicef new for un and using blockchain is cash-based transfers both kind of in a development context supporting social protection schemes, but also Possibly more interesting in humanitarian situations. So responding to disasters Trying to get funding funds out to communities who have been affected And we're actually at the moment looking at blockchain for cash transfer and we're in fact I don't know if they're here in the room yet, but we do have another cohort graduate that is supporting us room sand from Nepal and but using blockchain for cash transfer to really increase transparency of these processes so everyone can see the Movement of the funds all the way to the beneficiary and this is interesting for us as a un agency, but also particularly interesting. I think to donors too and of course saving costs compared to the traditional aspects for this and this particular space Unicef is also looking currently we're using blockchain to support the The cash transfer programming. It's not Cryptocurrency at the moment. It does get converted to fiat, but we are hopefully looking at using pure crypto cash transfers Early in 2023. I think that's what we're looking at So my name is herman. I lead the blockchain work for giga at unicef Giga is kind of like operates as a little startup within the office of innovation at unicef Um, we were started. We started in 2019 And we're sort of a joint venture between unicef and the itu the international telecommunications union And our goal is to connect all the world schools to the internet by the year 2030 So we have quite a lot of work to do And we operate along sort of three pillars of work. The first pillar is mapping Believe it or not. We actually don't know where all the schools in the world are So we're using machine learning algorithms to Analyze satellite data and identify schools and actually help governments find all the schools in their country So that's always an interesting conversation with the ministry of education um The second pillar is about financing. So we estimate that it's going to cost approximately $428 billion to connect all the world schools to the internet, which is quite a lot of money. So we're really investigating uh like innovative financing models to Find sustainable ways to to make this happen. So There's two types of expenses that We have to we have to solve for the first one is obviously sort of capital expenses, you know, putting fiber in the ground Putting putting towers up that sort of thing But the second is, you know, once you connect to school, you have to pay for ongoing internet connectivity You all sort of have subscriptions to that. That's kind of like how it works. You have to pay your bills. Otherwise you get disconnected So we've got a team sort of focusing on on making that happen Um, and the third pillar is about actually connecting. So we uh operate a team called the accelerate team where We work with governments to investigate different kinds of business models for different kinds of scenarios, you know Schools in urban areas need a different solution than schools in super rural areas So we work with governments to try to figure that out and advise them on sort of the best way to go Um, so that they at some point can take over the financing and the rollouts of these programs and do it sustainably So like I said, uh, we launched in 2019. Our goal is to connect all the world schools by 2030 So far, we were joined by 19 countries. Um, we have connected 5,300 schools We've mapped about 1.1 million out of an estimate at 6.5. So there's still like quite a lot of work to do Um, and we were super happy to be joined by 14 partners that support us including ericsson, dubai cares musk foundation Uh del recently joined, which is a really big deal Um, and we've raised about 210 million dollars, uh towards this goal So as you can see, we're still quite far off from the 4 and 28 billion required Um, so I'm gonna talk also today a little bit about some of the projects and the products that we're developing at giga um So the first one is what we call giga counts, which is like a accounting platform and a sort of monitoring platform For the people who uh are actually controlling the money to pay for school internet connectivity So the way it usually happens is that um a country government or A unicef country office will write out a contract for like 100 schools or 1000 schools in the country, uh And have very little tools to be able to actually monitor whether this uh internet service is delivered So what we're developing is a way for them to make it super easy to monitor that so we um, we have our own sort of real-time data Application that schools can install on their on their networks and reports data back to us so that If data is subpar or connections get disconnected We can actually hold uh internet service providers accountable. So that's that's our thing and what we're hoping to go with this is um Right now the tool is really sort of to support support these people in their in their in their work But what would be really cool is to actually uh connect this with smart contracts and Monitor whether internet quality is delivered and if it is automatically pay internet service providers So that becomes super easy for people to uh to connect schools and to to get paid for that um, the second product we're working on is uh called connectivity credits it's kind of um Kind of similar to the carbon credit market. So we're building a global uh marketplace for uh connectivity To make The con the connection of schools sort of more equitable So what's currently the case in the free market is that schools in rural areas get connected and the ones in urban In urban areas don't or if you're a school in the rainforest An internet service provider isn't gonna like lay a fiber optics cable there So what we're doing is we're um Providing a marketplace where we put tokens on these difficult to connect schools And allow internet service providers to redeem these tokens for benefits from the government like for example tax breaks or special deals when uh bidding for uh spectrum uh That bends with that that sort of thing So that's currently in progress in in Botswana and we're looking for other countries to to implement this Um another area that we're investigating uh is nfts. So we've launched uh in the beginning of this year, uh the first uh first The largest I could say the largest sort of collection within the un uh For this called patchwork kingdoms It's based on all the school data that we have so for these 1.1 million map schools that we have We worked with a data uh artist data analytics artist to create these 1000 unique art pieces And raise funds for giga. So, uh, we we sold all of them raised about 700, uh 1000 us dollars for that Which is really cool and um Looking to sort of build upon that uh with our follow-up collection currently title in progress called nft 2 Where we're basically uh looking to build the world's most comprehensive decentralized database of school data So the idea will be to um create an nft for every single school in the world um Give this nft for uh to the schools themselves to own uh and give them the ability Together with their communities to uh edit the data for their school keep it up to date Uh and and own this data themselves. So Right now it's sort of On our cloud server in frankfurt, uh, sort of locked up And for for us to use but it'd be really cool if schools own their own data So that's where we're hoping to go with that. So we're looking to use nfts as a sort of A portal into into making that happen And the last uh projects that were uh actually, uh, really keen to talk about uh at this event It's like we're finally allowed to talk about this in public, which is really cool Is a staking experiment that we've been running uh in rwanda. So um, we've been working uh with the ethereum foundation and the the government of rwanda Um to create an impact investing product using ethereum staking to pay for school connectivity So that's um currently live and uh, we'll also be talking about that more in the panel discussion on thursday if people are interested I think that's it and then I'll hand over. Oh, there's one more So, I mean rome wasn't built in a day. Uh, and these products aren't either. Uh, we're still, you know, iterating on these Uh, so we're asking you guys, uh, do you want to help us build? Um, so Antonio conterres, um, yeah Mentioned us at the at the previous sort of, uh, general assembly. So There's really sort of been a global call now for governments to step up and uh, and and and help with this Help with this calls To to finish with the with the talk before we go into uh, breaking sessions is that We we cannot do this alone. We are relatively a fairly small team Compared to all the things that we do and all the products that we are building So we work with companies with people on different ways One one it's that we have a tech team an internal tech team of product product managers that the scientists and software developers And we are continuously sourcing for that. So if you are interested in joining our team Keep an eye in our website because you have different jobs that go out there At the moment we have one for a blockchain developer that's open. I think it's going to be open for Another week, uh, or so. So if yeah, if either you are interested, you know, anyone that might be interested Please spread the word. There will be another one for a blockchain product manager role as well that will be coming out soon And others will keep coming out. So definitely keep an eye on that and and apply We also especially for blockchain work because many of the things that her been was presenting are so new And they are experiments and prototypes that we are building We work with external companies and with vendors to to build them and then we take them and we Scale them internally, but we first work with companies to to build them So again, if you are a company that's working in the blockchain space either on the nft space staking tokens And and others and you are interested in working with us. There will be again few calls For companies to apply to help us build the the things that her been was presenting. So there will be One call To help us build this nft 2 that doesn't have an end yet So we'll be working with a company to to build that The same for the accounting platform that he presented gig accounts We have a working prototype at the moment We'll again be looking for a company to help us Build the next version of that and test it pilot it in a couple of countries So again, if you are in that space of smart contracts, uh, etc Feel free to apply and then the last one that will also shortly be coming out. It's on the connectivity credits We are building that at the moment in Botswana But looking to pilot it in a couple of other countries So there will be another call looking for companies to build that a token marketplace for for connectivity and then Yeah, I think those are the the main calls that will be coming out Of course, then we also work with uh companies as partners. So not so much Contracting them, but we work with companies like they'll the mask fund. Well, that's not a company But the mask foundation Ericsson I just ours as partners where These these companies give in kind contributions either funding monetary But also sometimes they give us tech support either through their developers through their team So again, if you're interested in helping out reach out to us And then finally The last one. It's the venture fund. So if you are a startup in the blockchain space There are continuous calls that are coming out for that. There will be one That will go out soon around climate tech solutions. So if again, you are building something on that 20th of october it's coming out So so again, that will be out and it's an investment of 100,000 100,000 dollars more or less Uh with that I would also encourage you to check this QR code so that you can register Like that you can also keep you in the loop And if we have something that we also think that might be relevant to you We can keep you in our network and reach out to you


1945. And as Western armies sweep across Germany, voices crackle on the field radios, speaking in English and in Russian. Until finally those allied in the struggle come together on the River Elbe. For Europe and the world, an unforgettable moment. And if only all had continued to act in the spirit of that moment, what a Europe, what a world might then have emerged. West of the Elbe runs the River Rhine for centuries the highway, yet the divider. The river of the Goths and Romans, Christians and pagans, three men and slaves. A river with a history of strife. For it runs through the heartland of Europe, the continent so long torn between races and factions that only dreamers like Charlemagne could visualize it as a peaceful unity. But in spite of the bloodshed, most of it so recent, one Europe is at last becoming a fact, born out of the very dragon's teeth of war, a harvest from the seeds of Europe's own destruction. 1945 and the end of yet another European war. And who has won when in fact all have lost? A war in which more civilians died than fighting men, not in hundreds, but in millions. But they at least were spared the aftermath, much had gone forever. And what was in its place? In the aftermath but mere existence, where an undamaged roof made a palace, a loaf of bread, a banquet. One long line up with want ever there in the queue. What a Europe to grow old in. What a Europe to grow up in. A black market where even pride was for sale to the highest bidder. A Europe in which degradation had brought the end of self-respect and human values harked back to the jungle. But in order that queels could turn again, some had to put faith in others, they had to trust and sit round the same table to find by agreement the means of putting Europe, indeed the whole world, to rise. Already it was evident that the Europe of 1939 had gone forever. The annexation of the Baltic states, the huge advances of the Red Armies had placed enormous areas of Eastern Europe under Soviet domination. And Marshal Stalin was hardly the man to yield over the table that which he had seized and battled. Nevertheless he had agreed on free elections and amid the ruin of countries such as Poland, they were held. Though how free they were was a matter of opinion. With communists already in key posts, it was little surprise that things went their way. And if anything else was needed to make their victory certain, there was always the presence of the secret police and the numberless soldiers of the occupying Red Armies. And peoples exhausted by war were hardly likely to resist such coercion with determination, for all their energies were engaged putting one brick back upon another. In Western Europe where destruction had been lighter, morale was much higher. Here regimentation had been accepted only because without it, winning the war would not have been possible. Now with the end of that war, the essential freedom of democracy burst out with new vigor. There was determination to see to it that such a disaster would never happen again. And for the more reflective there was to the realization that to be done with insecurity, Europe must be united. But how could these Europeans be united when they're all so different? Here was the blood of many races, nationalities each with their own traditions and ways of life, not to mention the score of different languages. They might smile at each other, but as neighbors they seemed to have little in common. Sometimes a frontier was a natural barrier, a river or a channel. But more often than not it was merely an invisible line across fields or streets, an invisible line and an attitude of mind. Yet even in this time of preoccupation with essentials, there were those who realized already what must be done. It is not a movement of parties, but a movement of people. It must be all for all. Europe can only be united by the heartfelt wish and vehement expression of the great majority of all the people in all the parties, in all the freedom-loving countries, no matter where they dwell or how they vote. All very well for such as Winston Churchill to make speeches in 1947. He had self-respect. He had enough to eat. He had a place in which to live. Unity. Was this the earth in which to plant such seeds? At such a time, unity was well down the list of priorities. Anyway, unity was supposed to stem from the United Nations. And their lack of progress was such that when delegates from east and west sat down together, hopes were so thin that even they were cynical about them. How could there be a chord when the east blocked every move in its direction? In the economic chaos of this post-war period, communism saw its greatest chance. Wherever there was dissatisfaction, there they could fan the flames of revolt. World revolution, the end justified any means. In countries like Czechoslovakia, infiltration overcame the obstacles. At first, communist leaders paid lip service to democratic principles, treating the veteran President Benish with the respect he deserved as head of state. But then, they whipped up left-wing popular feeling into a mood of taking drastic measures to obtain, not just the power due to them by the votes cast, but total control of the traditionally democratic Czech state. Soon, President Benish was forced to retire and die broken-hearted in virtual exile. In the Czech parliament, the new leaders of the land made their debut with enthusiastic demonstrations of support. Then, in an exhibition of cruel irony, they stood in silence to pay homage to an absent member, the great liberal Democrat, the son of the founder of his country, Jan Masaryk. Jan Masaryk now lay in state after a fall to his death from a window. By accident, suicide on murder no one knew. Except that for the communists, it could not have come at a better moment. And if infiltration failed, there was always the threat of strength based on Moscow. While their wartime allies had demobilized, they had retained their armies. Here was a powerful factor in any arguments. In an attempt to gain control over the vital Bosphorus, the Russians had offered to renew a treaty with Turkey only if they could have bases within her frontiers. By standing firm, the Turks called the bluff. But their neighbors in Greece were not so lucky. In Greece, the communists staged open revolt, a desperate struggle in which the government only kept control after bitter fighting. And already, war-impulverized people were brought to the very limits of hunger, suffering and despair. The method of settlement might be with guns, but what good were guns without bread? And so, for the world, there came the realization of the basic truth that Europe's problems stemmed not just from the recent war, but mainly from the neglect and indifference of centuries. What good were guns against backwardness? What good were guns against the malaria which sapped the strength of whole peoples? What good were guns against the tuberculosis, the rickets, the squalor and filth? Feed the needy children, but what of their hungry fathers, mothers, grandparents? Now was the time to lift the stone and reveal the horrors that lay beneath. Far from Europe's shores, there were those who cherish what they owed to their European origins. And these were people never prone to let human suffering exist without protest. True, they gave from a wealth the greatest in the world, but this in no way detracts from their Christian intent. In Washington, George C. Marshall made known the principles of the plan that was later to bear his name. The whole situation is critical in the extreme, but there's no doubt whatever in my mind that if we decide to do this thing, we can do it successfully. And there's also no doubt in my mind that the whole world hangs in the balance as to what it is to be in connection with what we're endeavoring to put forward here. Thank you. Later, President Truman drove to the capital to put the Marshall plan for European aid before Congress. I'm here today to report to you on the critical nature of the situation in Europe and to recommend action for your consideration. I believe that it must be the policy of the United States. In Europe, the response had been immediate. In Paris, world statesmen met to discuss how and where the proper American aid could best be applied. Among the delegates was Soviet Russia's Mr. Molotov, for the Marshall plan took no regard of politics, only human need. But for the Communists, human need was there stuck in trade. Such a plan could only put back their own plans of revolution by means of economic chaos. And so, out of the Paris conference, stalked Mr. Molotov to be followed by the delegates from Communist Poland and Czechoslovakia. But a few dissenters could not call a halt to the greatest humanitarian step in history. And so the cargoes began to move across the Atlantic, food, grain and raw materials. And it wasn't just a matter of feeding the hungry. From Chicago, Illinois, Douay, France, a giant press for steel production, not only to put French mills back on their feet, but also to make them among the most mottled. Truly, the power of the New World come to redress the balance of the ocean. In Paris, the Organization for European Economic Cooperation was set up. A council of 16 nations, whose task it was to ensure that Marshall plan aid was used to the best possible advantage, to rebuild the shattered economy and to re-establish inter-European trade on a basis of strength. By their agreement, for instance, Holland could import three quarters of the cotton needed to keep her textile looms in full production. By their recommendation, could Britain receive the carbon black she needed to make the tires for her all-important automobile industry. Thus could France bring into her ports the sulfur needed to make fertilizers to maintain her world-famous vineyards at their best. Then there were tractors and other equipment for Turkey to bring her agriculture into line with that of her more fortunate neighbors. Then steel for the shipyards of Italy, for Trieste, Genoa and Naples. But over and above revitalizing Europe's economy, the Marshall plan had longer term aims. With its funds and technical assistance, vast areas of Italy, whole islands like Sardinia and Cyprus became battlegrounds in the fight against the malarial mosquito. Everywhere, health teams went out to battle with the scourges of centuries. For thousands, such undertakings often meant the difference between life and death. A new hope for Europe was read in the eyes of children. But this is mere sentimentality. Revolution first, humanitarianism afterwards. The Marshall plan has put a scheme to gain control of Europe and feather the nests of capitalists. So down with the Marshall plan, down with the Marshall plan by strikes, unrest and even sabotage. Alarmed by Soviet and communist structures, statesmen of western Europe came together to discuss common defense. The place, Brussels, with memories only too painful and close to that field called Waterloo. Only a handful of nations signed the Brussels Treaty. But in terms of European unity, it was an historic step and the continent owes much to the foresight of the five. The treaty brought tried and tested warriors to Fontainebleau in France to make the first tentative plans for meeting if need be, force with force. But there was little military strength with which to do it and already events were outstripping their efforts. The next hot spot, the city of Berlin, occupied by French, British, Americans and Russians. Ever since the failure to establish free elections throughout the whole city, Berlin's situation between east and west had grown steadily more uncomfortable. Now the Soviets felt strong enough to take action. Their efforts to starve the western population were frustrated as all the world knows by the fame of Berlin airlift. But in a few short years of peace, the Soviet area of domination had spread alarmingly over Europe. How could the Brussels powers alone halt further advance? And so for poor old Europe, talk of war yet again and one brick hardly back up in another. What hopes now for the Marshall Plan? And so it wasn't surprising that the Europeans read their papers with not a little cynicism and despair. And then they read how 12 nations in Washington had signed what came to be known as the North Atlantic Treaty. And if they had doubts about the new organization, NATO, the need for it was proved at once by the virulence of the Soviet and communist reaction. This was driving a rift between east and west. This was an affront to all peace loving peoples. They paraded the dog as a symbol of peace. And peace was their slogan. But was this the peace for which so many had died? In Normandy, in Alamain, in front of Stalingrad and the hills of Italy and the streets of Paris? Was this the aftermath they had envisioned? And so the peoples of western Europe pondered a new step and decided that it was indeed the only way. And so they backed the Atlantic Pact and gave strength to its council. For they saw that there were worse things than war and that to avert war was to be prepared for it. And so Europe took the move towards defensive rearmament very calmly. And in spite of all the threats, when NATO's first supreme commander, Dwight D. Eisenhower, arrived in Paris to take his post, the planned demonstrations came to naught. In a temporary headquarters in Paris, the military staff of the Alliance began their task of building the continent's defenses. And although they didn't realize it, they were taking the first steps to a western unity that was to extend far beyond the military field. Soldiers of many nations working together in peacetime for a common cause. The growth of NATO's strength moved a pace with the progress of the Marshall Plan. So that and mingled with the goods pouring into European ports were traded weapons and other armaments. Goods for peace and goods to safeguard that peace. And as well as the goods, men, soldiers, sailors and airmen from across the Atlantic, from Canada and the United States, here this time to prevent the guns from sounding. Month after month the buildup continued. The buildup for war to prevent war. And as the camps and airfield began to mushroom on her landscape, old Europe took it all in as tried. For those from overseas who visited her in those critical days, there was plenty to see. Farmers from the Middle West, for instance, who came over to see whether France's farm products were all they were cracked up to be and found they were and then some. But for all visitors, whatever their mission, one thing was obvious. The changing face of Europe. The nations receiving Marshall aid dollars were putting their own cut and say free by those dollars, the counterpart funds to good work. Roads, railways, bridges, dams, hydroelectric schemes, everywhere the rattle of cranes and concrete mixers. And in the factories from Turin to Aberdeen, there was ever growing roar and bustle. The Marshall Plan was the stimulant, NATO was the shield. But the effort itself came from hundreds of thousands of men and women who worked now with a will because they saw a mighty purpose. Now they were working for peace, working for themselves and working for tomorrow. In Strasbourg, the Council of Europe was meeting regularly and at its conferences, many European nations, NATO members and others discussed the future of their country. The council had no executive powers and its members could only advise their governments of the general trend of its thinking. But it was at least a common meeting place, somewhere where Europe could be viewed as a whole. A start, but there was a long way to go. The arteries of the continent might be throbbing now with new life, yet still the age old barriers loomed up. It seemed that little short of atomic bombs could shift the walls of rubber stamps, secure and rock-like as Cologne Cathedral or the Citadel of Kakasan. And yet the solution was easier than anyone imagined, for the necessity for unity affected millions where it hurt most, in their pockets. The needs of trade and the higher standard of living were the factors that finally pushed down the walls. Once the steelworks and coal mines of the Tsar, the Ruhr and Lorraine were the crucibles of war. Some then came all the cannons and without them war would have reverted to an affair of bows and arrows. And then in one step, by one agreement, all were brought together under one community, the European community of coal and steel, the dream of Francis Schumann turned into reality. In the city of Luxembourg, the community set up its headquarters and went quietly to work. In point of fact, the idea was purely an economic one. Six Western European nations who dealt in coal and steel decided to cooperate to prevent overlapping, waste of effort and unrealistic prices, to establish in this field one common market. A praise of the world was soon to hear more of. There are reasons for economic, yet by this one act a handful of practical visionaries had done more for European unity than all those who had striven by blood and force throughout centuries. The business of making swords had been turned into making plowshares because there was little future in swords. In the meantime, at NATO headquarters in Paris equally important moves had been made. A project to form a true European army to include forces from western Germany had failed. But in its place, by general agreement of all the NATO nations, the Federal Republic of Germany was invited to become their 15th ally. A step vital to the alliance, a step vital too for European unity. For it placed the Federal Republic firmly within the western orbit and out of the old no man's land. That was 1955 and since then old Europe has seen much. Profitless summit meetings, frustration, Hungary, Cuba and yet all the while the changing face. Now in the heartland of Europe, the defensive forces of NATO provide routine spectacles, for by now they've been around for more than a decade. But familiarity does not always breed contempt. Many of the bystanders were themselves born after the Atlantic pack was signed. Naturally they are more interested in how things work rather than why. And why is not now quite so clear as it once was. Why in the heartland can there still be seen men and women wearing uniforms of states far across the ocean? Why NATO today? Today the river Rhine is too busy to consider strife and warring factions and hostilities between Christian and pagan. It's a long haul from Switzerland to the sea and the customers are waiting. Besides the Rhine is now bridged in more ways than one. Today the tanks on NATO exercises are ferried across, not for the purpose of conquering the other side but merely to practice how to cross water. For now, German, American, British, Canadian, French, Belgian and Dutch all work together to safeguard not only the heartland but all Western Europe as a whole. And now there are more than just ruins to defend. Today on the sleek new international trains there are still those who must examine passports and ask if you have anything to declare but they do it rather half-heartedly while they express it in full dash for prosperity cannot wait. Dear sir, with reference to your order of the 15th we are pleased to be able to state that the desired consignment will reach you on time even though your order is for half as much again as your last. And here in Brussels there's something else to see now. For out of that first idea of the coal and steel community there has emerged an even more ambitious move. Here in Brussels the headquarters of what is now called the common market. International cooperation across the table in terms of practically everything that men produce and consume. Today just the six but tomorrow without doubt the 13 with perhaps even some more from outside the old continent. Only the beginnings but there for sure but only there for sure because of the watch and ward of NATO because of the men and equipment keeping 24 hour guard by land, sea and air for still across from the heartland only a few miles east of Hamburg and Frankfurt men toil ceaselessly to add yet more miles of wire to the already enormous web of the iron curtain. Over there working with or even passing the time of day with the west is strictly forbidden and so these scuttle away like mice because they are afraid. And where there is no wire there is the wall the wall of shame dividing peoples and families without rhyme or reason. This is why NATO today just in case those beyond the wall who sneer and smile might still be tempted to do something desperate but for us no time for walls we have a train to catch. If they over there can't join us then more is the pity because Europe is now moving ahead so fast there's a real danger of being left behind and much as we wish them with us even without them we over here are going full speed ahead together.


I need to say a little bit about state modeling in games, that is how do we represent the data which is the game logic part in our games. And it's something I talked about in an earlier video, but I think the prescription I gave in that video was perhaps well out of date, really not up and up on the latest practices, and also it may not have been terribly clear, so I want to correct that. So say you have a game like Super Mario, and in this game you have a bunch of things which we can model as data, like say Mario himself has an X and Y coordinate, fireballs have X and Y coordinates, and there's momentum and so forth, and you go through a momentary freeze frame of this game or any game, and you can just start just enumerating all the things that make up the game state. The question is how do we model that state, how do we organize it? The most obvious approach is to use what's called the object model of data, which is not necessarily about object oriented programming, it's just we organize the data in terms of these things which are data objects that reference each other, such as you get this big hierarchy or actually in practice this big graph of things that reference each other. And so when it comes to say modeling Mario, we end up with this big composition hierarchy or graph, where say we have like a object representing the game itself and that has a bunch of fields which are like a list of the monsters and an object which represents the player, the Mario himself and game state like whether the game is paused and that sort of stuff. And these objects in turn are going to be composed of their own objects potentially. Now this object model is obviously a workable solution, it's also the obvious solution, but the downside, well there are a few downsides, I think the main one though comes down to how flexible is the state of structure. In practice when we create any program, but especially games where we don't really know exactly what we want ahead of time and we're just incrementally working out features, adding stuff, removing stuff and so what we don't want is for our data structure to tie us down such that anytime we add anything new or remove something or move stuff around and reorganize that like that one change has repercussions for the rest of the data and also for our code logic, right? As much as possible, if I say add some new thing into my game state, I don't want to have to rewrite any existing code. Now for various reasons, the object model of data isn't really all that fantastic measured along that metric. It's not particularly flexible. It's simple and obvious, but it's not really particularly flexible in that way. In contrast, the relational model of data tends to be more flexible, and the gist of the relational model is that we organize our data into these things called tables where each row is an entry, it's a thing made up of the properties represented by the columns, the attributes as we might call them, and the way we organize our data into tables is governed by these rules called the rules of normalization or the normalization forms, and the idea is that if we follow these rules, then our data will have the virtues of these four properties which were laid out by E.F. Codd, the creator of the relational model, and the way he expressed these benefits is fairly academic, but I can translate quickly. First, to free the collection of relations from undesirable insertion, update and deletion dependencies, the gist of that one is that if you follow the rules of normalization, you're removing redundancies in your data, so you don't have like the same facts expressed more than once, such that when we add new entries into these tables or remove entries or modify an entry, we don't have to do it in multiple different places. If we're updating just one single fact in our database, that just exists in one place, so we don't have to do it in multiple places. The second rule here to reduce the need for restructuring the collection of relations as new types of data are introduced and thus increase the lifespan of application programs, that's the flexibility I was talking about. The idea is that if you follow normalization rules, then you should be able to add new tables and remove tables or modify existing tables like add new columns without having to rearrange everything else. Number three, to make the relational model more informative to users, I think really all that means is just the ideas of the relational model is somehow more intuitive and more natural, has a more natural correspondence to the way we think about the data. That one's I think really arguable, but maybe it's sometimes true. And then the last one, number four, to make the collection of relations neutral to the query statistics, where the statistics are liable to change as time goes by. The gist of that is that when we design our database, on first pass inspection, we don't want to bias the performance of querying one part of the data versus querying the others. We want to have equal performance for similarly complex operations, like if I look up 1,000 elements in one table, it should take about as long as it takes me to look up 1,000 elements in another table. However, there does come a point in real world programs where we care much more about certain kinds of queries than others. And so maybe this property isn't exactly what we want. Maybe we do want to bias our data structure to be more efficient for some parts rather than others, because that's the sort of thing we need to do more frequently and more critically in the application. So there comes a time when we make certain tradeoffs about efficiency, but the relational model itself is attempting to keep everything kind of, everything kind of even. So those other purported benefits of the relational model, the question is, well, what are these normalization rules? Well, there are depending whom you ask, there are at least six rules of normalization. In practice, the most critical rules and the ones which people abide by most of the time are rules one through three, and not necessarily four or five or six. The idea is that you can conform with rule one, plus also rule two, plus also rule three in order. Like if you're conforming with rule three, then implicitly you're conforming with rules one and two already. And it just, for most people's purposes, conforming with rules four or five and six is more trouble than it's worth. The most bang per buck is in rules one through three. So what are these rules exactly? Well, I won't go into them here, but the gist of it, it works out is that, well, we need to first off put attributes in the right table that maybe that's not very helpful perhaps, but that's kind of what rule number three kind of boils down to. In other words, make sure when you include a property in a table that that property is really a direct fact about that thing described by the table. Like if you have a table describing the planets, make sure that the property is directly a property of the planet itself and not some secondary thing, which is something which might be a component of the planet, for example. Like if you have an atmosphere and there's all sorts of things about an atmosphere you want to store in your data, then you probably want to have a separate atmosphere table. Also, according to the first normal form rule, all of our values that we store in columns have to be scalar, basically meaning they can't be lists or other kinds of collections. They have to be just individual values like a number or a string. When we have lists of things, when we have many things, that's what having multiple rows in our tables is for.  That's how we express our multiplicitous data as multiple rows in some table somewhere. And then the remaining implications of normal forms one, two and three is that when we have something that we would most intuitively think as a one to many relationship as this thing having a relationship with many other things, we invert that and express it in our data in our tables as a many to one relationship. So imagine like a player has an inventory and that inventory is basically a list of stuff, but we don't have lists in our tables. So instead of having say a player table where there's an entry pointing to many different things that exist in their inventory, that player's inventory, we have a separate table where items existing in inventory are each entries in the table or rows in the table and they each point back to the player. So we're inverting the relationship instead of having a player with a list pointing to the references of many inventory objects, those individual inventory objects point back to the player. We're inverting the relationship. So that's how we express one to many relationships. And then when it comes to many to many relationships, those we express in some separate table. So like say imagine you have a bunch of people in your game and you want to express in your data, which people are friends with whom the obvious way to do it in the object model is that each person has a list pointing to all of their friends, but we don't have that sort of thing in our tables. So instead what we do is we have a table where each entry is one of these relationships is just a record of this if each relationship. So it's typically just a two column table that records a reference to one person and then the other person. And this way of expressing this sort of relationship can be a little unintuitive, especially because like in this example of friendship, we think of as just being a mutual relationship that goes both ways like if a is a friend with B, then B is also a friend with A. So do we record that in the database twice? Do we do in this table, do we say A is a friend of B and B is a friend of A or do we just express a relationship one way and consider it equivalent for both? That's a sort of icky question that arises when we follow this pattern. So it's not a totally ideal way of expressing this data, but that's actually a problem we already have in the object model where you know you have each person potentially listing all of their friends and so the you have the same redundancy of the relationships being expressed both ways. Note the advantage however that in the relational model, this relationship information is a separate fact about our person objects. And so when we add new relationship facts of some nature about our persons, then we don't have to modify the existing person table, we're just adding new tables. The interesting question though, well if this model is so flexible and ideal in that sense, why is it not what we use in games? And I think basically two answers. And the first is well performance. Certainly we wouldn't use off the shelf relational databases like MySQL or MSSQL or Oracle or whatever, because you know those are separate programs that we talk to over a network connection.  I don't think you could conceivably have really a even an in memory database like SQLite which operates in memory as a library of your program. It still even then isn't really conducive to high performance because relational databases they have different different goals in mind. They're about like concurrent access to this large mass of data that's probably in excess of the sort of thing we need to represent in games because even most games don't represent all that much data, even the most intensive games. They have a lot of data in terms of like textures and assets and so forth. But the actual state data is relatively small compared to even fairly trivial, like web apps, but you know how much data they need in terms of like user data stored in of like, you know, content management systems and purchase history and that sort of stuff, right? So maybe these performance problems are just a matter of tradition and just coming from different development backgrounds of you know, where there are different concerns and different needs. Maybe it's conceivable we could have an in memory database that followed the relational model strictly that was up to snuff with the performance requirements of if not the highest performance games at least like moderate level games. But to my knowledge, no such thing has ever been done or even attempted. Aside from performance, the other sticking point I believe with this relational model in games is that in games relative to other sorts of applications, we tend to need many, many sort of ad hoc things. The nature of games is that we tend to have many more one off exceptions in terms of data.  There was a lot of ugliness and in non game applications, but in games in particular, the data itself has to be ad hoc where you have, well, there's this one part of this level where there's this one monster that's special in this one way, that sort of thing. And so I believe in response to this need for having the flexibility of relational model, but also having a need to define many ad hoc things in the course of game development, game developers about like early 2000s started embracing a way of data modeling with what are called components and entities. And the gist of components is that they are effectively like tables in a relational database. But then an entity is a record that potentially spans multiple tables. So it's like a row and a table, but it's the row that potentially belongs simultaneously to multiple tables. And entity is a thing that's made up of multiple components. And there isn't a lot of formal literature about this stuff.  But from what I've read, a common prescription among developers is that with components and entities, you should follow rules that are very much like the normalization rules. Like most importantly, your components, the properties of a component should be scalar, they shouldn't be like lists and other kinds of collections. And then also I see developers suggesting that well, generally, when you're expressing a one to many relationship, you should invert that and express it as a many to one relationship with the each thing of the many pointing back to the one referencing the one. Now, the degree to which you should normalize your data in this pseudo relational model is one of those things that I think people are not totally clear on and there's a lot of debate on and probably actually just varies on your use case because in practice also when people use a relational model and conventional relational databases, you know, there's we deliberately often de normalize for the sake of optimization because yes, we're adding redundancy into our data, but there's certain kind of queries that we need to be really efficient, we need to get at this certain data a lot very quickly. And so we sometimes need to denormalize. Well, same deal with these components and entities is sometimes for the sake of performance, we're going to be denormalizing. And then also in connection with these components and entities, you'll sometimes see the term system used to refer specifically to these functions, which are used to loop through every entity with a particular component and update those entities. And in some versions of this component entity system pattern, the prescription is made that well, you do all of your updates, all of all of your processing of entities is with these system things, these functions that loop through all the entities of with a particular component. So it's sort of like having this, you know, the pseudo relational database, where we're doing one primary query where we're iterating through every element of a particular table, except in this case, we don't have tables, we have components. And that's I think where this this whole pattern, there's a lot of disagreement, I think of how strictly should you restrict yourself to just that kind of query, that kind of looping through entities by component, like what if you want to do other things, because sometimes in your game logic, you need to be comparing the data of this entity with this other entity, which is totally elsewhere in your data, you know, has a totally different set of components that that does happen in many games, there's logic that depends upon disparate elements. And how exactly you handle that is something which I don't think there's a clear prescription. And there's there's variances of opinions. So that that's an interesting question to resolve. It's not something I have enough experience with where I can really give my own prescription. So I'm just going to leave the question open there.


Yeah, no, I'm not. Turns out I'm not. If you do that again. Cool. Welcome to the Data Storage Mini Conference. This is a very relaxed kind of fun day about data storage and a whole bunch of different topics on it. Basically, the idea was to have as much different ways to store and eat and understand and process data as possible. So feel free to ask questions. We have microphones for recording. And there's probably a first for LCA. It looks possible that some kind of video may be recorded, and if we're really lucky, sound as well. So a question we asked. Our first speaker is Arjen, talking about understanding joins, of which there will be whiteboards and markers. And I believe we have the one last whiteboard marker left in Queensland. I just put it in. Yes. Yeah. So that's very valuable. I need three colors. I might have to make it up. Yeah, so it's three colors as long as you want black. So, Arjen. Howdy. What's that? I don't want to lose that. Your mobile phone rings. I'll answer it, which may be problematic if it's your boss. You want to have mine? It's to you. My boss is not going to call me. I could save heckling for later. You could save heckling for later. This is however an interactive session. Am I allowed to run around? You're going to be very active there. Yeah, I can't stand still, sorry. Yes, yes, green. Anybody else? Got two colors now. Surely we can crowdsource this whole thing. Red or blue I'll go for. Yeah? Anyone? Anyone? Want to tweet to Nick's door? Sarah, do you have whiteboard markers? Other colors? Ah, here. Ace, thank you. Ah, no, no, no, you're getting it. OK, you get to keep that one. I need multiple. Thank you. We win, yes. So I asked you yesterday, can I get a whiteboard? Surely we have one around here. Somewhere we'll check. So you walked into the room, checked, got the whiteboard. OK, so that's all good. OK, who here reckons they understand joins? OK, there's one maybe to. Stuart probably gets the credit. Yeah, yeah, yeah, OK, I've tested him before. There is hope, yes. So this is a bit from open career training, where years ago I was running the MySQL training material, which was boring as it was written by someone with a double degree in mathematics. And of course, everything is really easy.  I couldn't work that. And at some point, I saw someone else do another presentation, and I suddenly figured out how to do it. So that's why we're doing the visual thing and interactively, because it's much more fun. It keeps you awake. I once did this for someone with a visual brain, visual spatial brain, over the phone in four minutes. So it can be done even quicker. I could do it in a lightning talk sometime. It could be fun. The interactivity gets lost at that point. So let's see if this actually all works, I think, at that point. Yes, it actually does change. So who here uses subqueries? Let's do check that. Yeah, so I presume you're all, by the way, using MySQL, you don't have to. So who here doesn't use MySQL but something else? That's cool. What do you use? Postgres? And there's a drizzle. Sure, Mr. Heckler. OK, that's cool. Yeah, so I mean, the basics apply.  They fixed up what they were supposed to fix up like five years ago, and now joins, or subqueries now actually work fast. It's really good. It's a bit of a non-event, but if you do use the media apps, please do use MariaDB. There's a talk about it this afternoon. Now, the reason most people are really working with joins and not subqueries in MySQL, and are not necessarily familiar with how things work, is that, well, MySQL used to not have subqueries until version 4.1, which is, of course, ancient history now. But it was quite an important issue at the time. And then before, certain types of joins, outer joins, didn't exist before SQL 92. So before then, you had to use subqueries rather than something else. So lots of textbooks, including the people who learned from the textbook, and then started teaching, will have been teaching people subqueries rather than joins. And there's a big problem, because with MySQL optimizing for joins rather than subqueries, you need those joins. So let's get those right. So first of all, let's play a game, because that's what we're here for, aren't we? Let's get this right. Stop. I'm not that tall. What key words related to joins exist? And this is interactive.  Using? Using. Let's try using. Join. Absolutely. Possibly yes. Yeah, there's no trick question there. That's absolutely perfect. Join is a join keyword. Yep. Inna. OK. Outer. Outer. Hm? All? No, all. Sorry? No, all. That would be union. All. No union. On. Right. On. On, yes. Or a puzzle symbol that goes out of the comma. Yes, please. I always forget that. Thank you. Thank you, Stu. Yes, there is a comma. The humble comma. We'll work out what an evil thing that is later. Yes? Left. Hm? Left. Left. Yes, I'll go with left. Yes. Both. Both. Yeah, that's fine. Oh, mustn't forget natural. Yes. Now, in my core context, it's called straight under could join and sexually modifier. So let's play with that. Natural, all right? I have a nice statement about natural later on. You won't forget that one. Yes? What else? Where? Where? OK, let's leave that one. Yeah. OK. Not technically incorrect, but let's leave that one for now. It's not in the context here. OK, any others? I think there's some missing. Not really. How about right? Have we done outer? Have we done outer? Left, right, full. We had using, we had on. I've got a complete list somewhere, but they're so small. Inner comma. Oh, hang on. More. Something called a cross join. Why not? Oh, yeah. That's your straight join using on. I did full. Yeah, I think we're good. I think we're good. OK. Now, with all this fun in mind, how many types of joint? Let's put this on the whiteboard. Look at that. Isn't that perfect? How many types of joints actually exist? Yeah, I see you vote there. Who wants to fess up? You're not wrong. You're even right, Mr. Heckler. He's my resident Heckler. You're in the wrong row. You should be in front. No, I want to have one back. OK, that's good. Yeah, excellent. He visited everyone in my talks, and I was at OSDC doing a couple of talks. He traveled all the way from Brisbane to Melbourne to Heckel. Thank you. Thank you, guys. You're welcome. I'll stand by that. Yes. OK, so how many types of joint? Probably three or four. Probably three or four. OK, I don't know. I don't know. Who thinks it's two? There's a couple of votes for two. OK, who reckons it's three? OK, how many votes? OK. What was it, two or three people? Let's say three people. OK, types and number. OK, who thinks it's three? Two, four, five. OK, that's five people. Yeah, who thinks it might be four different types? One. And what are all the rest of you doing? One type of joint. No, that's not going to. OK. Overflow. No, the right answer is what gas secretly, then it's two. And what are those two gas? Now you have to figure out. Inner and outer. Yes, absolutely. It is inner and outer. So now let's play this again. Get this right. There. OK, so let's try inner. Stick for time. And outer. OK, so with inner and outer, since we already have those two, we can now wipe those off the board. Obviously they belong around the group. So you get the idea. Some keywords belong on one side, and some can be used with both. So now we're going to pick off this list and put it there. So then you have a bit of a grip. So which keywords might belong with inner, which might be with outer, and which might be comma on inner? Do people agree with that? Any other ideas? Dubious? Who also thinks that, really? And is pretty sure about it? You don't have much support. He is right there. He is right. I'll just mention that. You never use commerce.  Exactly, but you know what it means. If you find it in the wild, they do loiter in the wild. That's when you need those nasty ware clauses that the man in the back mentioned. He's right too, but we didn't want those. OK, so what else? Come on. I know it's early. Just had coffee, but come on. I think that would be a both. Where is it going? That would be both. On it both? OK, yep. So it's using? Very good. Now we're getting there. Hang on. Give me time. Yep, I think we're good. Yeah? Left and right are out. Left and right? Ah, what do people think? Who said that? No, he didn't. You said? Who said left and right is outer? I said left and right. OK, you said left is inner. He wins. You've been in training with me, haven't you? You have. Forgetful people. No, left and right is outer. I have a little disagreement between manuals and standards and stuff, but I think we can regard it as both. Yes. There's no particular hindrance to it. Exactly. Well, we'll talk about them. In the end, it doesn't matter. Let's put it that way. For us, it shouldn't matter. Now we still have full, cross, and straight. Yes, technically, absolutely. That's a good one. Very nice. The academic wins. OK. Well, but yes, at least you know your theory. You don't have to now apply them. OK. Which of those keywords are actually not really necessary? Isn't that an interesting one?  They're really, really not interesting at all. They're just there to confuse us. You know, our SQL was designed. It was designed by committee. And no good comes of such things. A natural join would be a perfect example of things that seem like a brilliant idea that are really, really nasty. So let's, but interestingly so. OK, let's go on to the next bit of fun. Who remembers these from high school or somewhere else? OK, for whom wasn't this high school but something else? Where did you see them first? Uni, OK, not high school. Where did you go to high school? OK, fair enough. No, seriously, different states and countries have different things. So I'd like to learn about that. OK, yeah, primary school? Queensland, or New South Wales? New Zealand. OK. I'm just going to cry when I tell them. What they do now? But OK, so what's the name of it? Venn diagrams. That's what they are. Now here's the really, really cool thing. You're looking at the way that relational databases work. This is not an analogy. This is how they work. They're both based on set theory. It's the same thing, which enables us to actually use them to explain joins. Because yeah, it's the same thing. That's just wonderful visualization. It's a beautiful thing. OK, now let's have a table. I need my pens again. Let's start here. Let's create a couple of tables like that. So we have a couple of sets. And we do a join between, I have them up there. I don't actually need to draw them there. What would happen if I do an inner join between table A and table B? Based on, let's say that's the ID column in. We're simplifying here. Yes, everybody agreeing? It's numbers two and three. Isn't that wonderful? So I'll draw those circles here so we can play with them and actually give them color. Two, three, one, four, and six, and five. And I'll draw a bit of a thing here. That's table A. That's table B. And we now get two, two. Hang on. Let me get this one. Better. Sometimes forget exactly how this works for me. I need to do one, two, three, four, and then two, three, five, six. Okay? Everybody agreeing that this is approximately right? Okay. So when you do an inner join, you use the red pen. And you get that. And now you might have an idea of why an inner join is called an inner join. That's actually visually correct. That's actually really where it came from, but as far as I'm concerned, it's good enough. I haven't yet seen it proven wrong, so that'll do. So now you can visualize it. You can remember it, and you won't get it wrong again. We'll deal with the commas and the crosses and all the other stuff in a moment when we have the rest of the visualization correct. I've got ten minutes left. We're doing good. Okay. What would happen when I do a left join? Yes, Andrea, you're banned for a second. Yes? One, two, three, four. One, two, three, four would be correct. I don't know. Left is green today. Okay. So you would get kind of that. Let's do that. Yep. That's green, and that would be left. There we go. And see, this is why I need the colors. The patterns don't quite cut it. In gray scale, it doesn't quite work that nicely. Okay. That's what you get. When would that be? When would? No, that's not an inner join. We're now talking about outer join. It's a left join. The inner join was that red block, absolutely. With an outer join, you get everything on the left-hand side that satisfies, plus the things on the right-hand side that match up. The bits that don't match up end up being gaps. How do gaps show up in the results? If you do a select star where you get the junk, the gap is nulls. What was the symbol? Was it that? Yeah. That's not a zero. That's a null symbol. Okay. That's always nice. When you build a query, don't try to build a complex query in one hit. It doesn't work. Build it step by step and do it by using select star. Do it in a tool, not in your app, because if you put select star in your app, Arjen will get cranky at you. Your app will break. Well, you will get cranky at yourself later on. Your previous self will be hurtful, will be in pain over the later self. You know, you get the idea. You always work in the team of these two people, and it's you and your future self, right? So don't hurt your future self or your past self. Okay, so that's what ends up with the left join. Want to guess what a right join might do? Yes. What does the result set look like in blue today? Zero. That sounds exactly right. Okay. Did I stuff it up? Oh, bugger. Okay. Visual design. That's the problem with white boards. They don't scroll and expand. Okay. Four. Oh, there's one color too. That one. User fail. Okay. That's a four. That should be green. Ah, for goodness sake. At some point I'll work it out. Okay. Fast testing. Okay. Right. Now you see why a left join is a left join and a right join is a right join. So what's the kind of query that you would do? Why would you do a left or a right join? What kind of things can you find with that? And that's a nice, funny trick question. The answer, the single sentence answer is kind of funny sounding. Yes. You're trying to find things that don't exist. Yeah. You can't actually do a join on something negative. Yeah? You can't join a, you can't say a join b or a comma b where a not equals, a dot id, not equals b dot id. That doesn't work. It really, really doesn't work. It joins everything that doesn't match up that exactly thing. It doesn't do which one. So the only thing you can do is joining on something positive and then cleaning up the stuff you don't need. So if you're looking, for instance, for which customers didn't buy this particular product, is you pick out which customers did buy that product, but do it with the left join and then pick all the nulls. Does that make sense? So the ones that aren't null on the right-hand side have that product in their boughtish product table, whatever it is, in the sales table. But when you pick out the nulls, you get a list of customers that don't have that. Okay? So with outer joins, you can find stuff that doesn't match up. And that's the only reason why you should be using outer joins. Many apps use left and right joins kind of because it seems like a good idea at the time, but people can't explain to me why. Try to explain, verbalize to, you know, the teddy bears, the colleague next door or whatever, why you need to use an outer join? Why you need to use a left or a right join? If you can't explain it, the answer is you don't. Yeah? You shouldn't be using an outer join if it's not necessary. So that should be an inner join. Often they work out, often they work out to be the same. However, that depends entirely on your data set. You may find that your app suddenly breaks later when your data set increases. You know how web apps always work really well with one user testing? Yeah. It causes trouble in the real world. And this is the kind of same thing, Andre. Extra attributes on the side. Again, that may or may not be there. That may or may not be there. That's, again, a left join. Yeah. So that's a person, and it has a phone number, and it has phone number one, phone number two, and those point back. They each have the ID of that person, and they point back at that. You would do that with a left join. And you get multiple rows as a result. Same thing, though. I mean, this could be an attribute table. Same thing. Yeah, attribute one, attribute two, whatever. Yeah. And it points back. It makes no sense pointing it forward, because then you get a spreadsheet there. Putting all the fields here. Yes. If you have an application that says phone one, phone two, phone three, it's a spreadsheet. It doesn't work. When you see a company trying to fill in forms, you often can tell when they're dealing with spreadsheet forms. It's often rather painful. But let's continue. We still have a couple left. What's a full join? You reckon? And by the way, MySQL does not support it directly, but Wikipedia and many other places will tell you how to do it. Yes? Anyone? Anyone? It's a left join combined with a right join. Yeah. But all of the above kind of, yeah? So it's this whole thing, but no duplicates. So if you just do a left join, union all, right join, it wouldn't quite work out correctly. So you do a left join, and you do need to use a union all, but you need to filter one of the sides out with not nulls, because there could be legitimate duplicates. If you use union, the duplicates get removed, so you can't do that. So it has to be union all, so you get all the rows. But then on one side, you use where the ID column is not null, so you actually grab all the good stuff without the null stuff. Anyway, look it up in Wikipedia, and you see what it's done exactly. So MySQL does not directly implement it. One of the reasons is it could do that, but it's very nasty to optimize. So we just have never bothered, and you can work around it by just doing the union. So it works perfectly well. There's no particular reason why you shouldn't be doing that. And union all, or the full joins, they're fairly rare that you actually need them. They have some benefits, but not often necessary. So that's a full outer join. So again, it's trying to find stuff that's not there, but both on the left-hand side as well as on the right-hand side. So you need to be really sure you need something like that before you start using it. What does the comma do? What's the difference between a comma and just a join b? What's the difference between a join b or a comma b? Anyone? Anyone? See, if you do a join b, usually, no, please, no, you would use either on and then a dot id equals b dot id or using id. And then it matches up between the two tables. So that information, I call that a join condition. It is sitting right next to the place where you join. If you use a comma, you can't use these clauses. You have to stick it where, Indy? Wear clause. Wear clause, which is kind of much later in the query together with other filtering options. That's not very cool, because if you accidentally remove that thing, it will still join. What will it do? It does a cross-join, which is a Cartesian product. You've already answered the next question. Excellent. Thank you very much. No, that's good. So yes, if you're using the commas, try to get away from that and try to make it explicit. And that's why Gary said earlier he never uses comma, because it's just clearer. You can actually see what's going on. With a left or a right join, you can't stuff that up. Now, unfortunately, with all these explicit ones, you can still use the where instead of using on and using and so on. So it's still possible, but that's just a really, really bad habit. With the comma, it is always separate, and that's just annoying. So do get away from that, please. So cross-join is a Cartesian product. Yes? There's no efficiency benefit, because my score sorts it out internally anyway. The point is just in terms of maintenance. If you're doing a join between multiple tables, you have a wear clause that actually filters things. Then there's wear conditions that select your join conditions, and when you're joining, I don't know, five, six tables or ten tables, it just becomes impossible to maintain. In this case, the join condition is right next to the table that you're joining. A join condition can actually contain multiple components. It is an expression. It may contain more than just a dot id equals b dot id. It could have an and clause something else, and in some cases, that's necessary. If I were to ask the question, which client bought product a but not product b, and you'd have to solve that with joins, not subqueries, you would have an and clause somewhere in the join conditions to make that actually work. Actually, you wouldn't be able to do that with a wear clause. So it does make a slight difference, but only in very specific cases. It just don't have time to cover that right now. So what does the cross-join do? It does a Cartesian product. What kind of result would you get out of this if you did a comma b without any wear clause or join condition? What's the result? Okay, you get the idea. Yes. Everything on the left, everything on the right and all possible combinations. It's usually not what you want unless you're trying to set up a tennis competition and everybody has to play everybody. Yeah? So there are valid reasons why you should do, why you could do that. Usually, you don't mean to create a Cartesian product. Now, my idea for this is, first of all, don't use the comma so you don't accidentally create them. Because visually in your career, you'll be able to identify them. And if you actually mean to have one, use the word cross. Because then you've identified to yourself that it's actually supposed to be a Cartesian product. Technically, inside my scroll, the implementation between an inner-join or a regular-join and a cross-join is identical. Cross is one of those fluff words. It doesn't mean anything. Mathematically, there's a slight difference, but it really doesn't matter once you start implementing it. You could debate on whether it's identical, but for implementation purposes and relational databases, it's the same thing. So when you're looking at syntax, let's get that one right. That'll be the last thing we can do, I think. So you have inner-join. You would have a cross-join. You would have a lift. Now, hang on. Yeah, and same for right. Square brackets are because it's entirely optional, and those are fluff words. So if you just specify join, it's automatically an inner-join. Yeah? If you say left or right, then it's always an outer-join. There's no such thing as an inner-join. It doesn't exist. So the word outer is fluff again. So some people have an internal standard to always write outer because they write inner, some don't. I tend to just write left, right, and join and not fuss with that. But the Cartesian product, please do add the cross-in because then you know you didn't actually forget the join condition. You meant it to be a Cartesian product. Does that make sense for everybody? Enforcing it? And removing the comma? That's the first thing you could do, enforce these two. Yeah? So if you use the explicit join conditions, the explicit join syntax, to make sure it has to have an on or using, not in the where clause. If you start enforcing that one, the other ones will probably follow, but that's one that people are probably mostly using anyway, if that makes sense. Yeah? Because once you do a join, a left join, you don't want to stick it in the where clause. OK, so we have on where you actually have a whole expression. We have using where you specify columns where the names are common between the two tables. You can also specify id, comma, and other columns. So it can match on multiple columns. That's entirely doable. So we've dealt with on and using. Straight join in MySQL specifies to the optimizer that you don't want the tables reordered. And that is purely an issue of what you know is optimal in a particular case. Do not stick that in your production system because your data set will change, and that means that the logic that the optimizer will be able to use is then restricted. You might at some point know better than the optimizer, but that's only today. Next month, it might not work. Once you start playing smarter than the optimizer, you will be hurt later. So use it for testing, and usually if something goes wrong there, there's either a missing index or superfluous index. In some cases, there's other things wrong maybe with the query, and in very rare cases these days, there could be something wrong with the optimizer. It might be making the wrong choice. So it's good for testing, and then you report it as a bug or ask someone else to actually help you with that if you can't figure it out, but it's not something you would use in production. Don't try to override the optimizer in production like forcing indexes and so on. That's a really, really bad idea. It hurts people. So last one is natural. What does natural join to? Not Andre, because you know. It causes pain. Yes, absolutely. You get bonus prizes, I don't know what, but something for that. What does it actually do as opposed to what you're on and using do? Yeah, okay. So it matches up. It looks at all the column names in table A and table B and reckons well if there's an ID column, let's match up on that one. Oh, but wait, there's a name column as well. Let's match up on that one. Does that make sense? So let's say you have a country table and countries have a name. And then you have a, I don't know, a capital table and they have a name too or city table, you know. It will match up on all matching column names. That's probably not a really, really good idea. So it's kind of nice magic until you hit the real world. I think this was probably a good idea in the committee when people were discussing this when there were these magical people in the back room designing databases and us, the big wide world, didn't have any access to this kind of stuff so that we could screw things up. This sounds absolutely perfect, yes. If we were all, yeah, so Cod designed this with mathematics in mind. The thing is that the relational ideas are relational databases. The specifications are already a subset of what the set theory model can do. And then you implement it and then you stick SQL on top and it becomes even more borked. It is really a pure substitute for what Cod originally envisaged. It can't be fully implemented in the way that SQL works. It just looks really nasty. But yeah, it seems like a nice idea. It also can work okay if you had a really, really strict elaborate rule on what your column names should be. But we all have ID columns and name columns and that kind of stuff. And that means that it doesn't work in the real world. Now imagine that you actually make it work. Next week someone will add a column. Yes, this is exactly what kind of stuff that happens and it will suddenly match up with something else and suddenly your query doesn't work anymore. But I've only added a column. It doesn't even have special data in it. It doesn't work anymore. So my suggestion is if you ever have an employee or someone near you working produce that, produce a natural join, do two things. Fire them because they're dangerous people. Do not trust them in your neighborhood. Second is please let me know. Okay? This hasn't happened yet. It has been identified in the wild but no culprit has been found. Just old code. People walked into a project and that was there but that's as far as we've got so far. No hedge have rolled yet and I've done this training for, well, almost 10 years now. So that's getting there. So luckily it doesn't, well maybe no one wants to fess up. I don't know. I'm asking other people to dop other people in. So maybe there's more hope. So yeah. I mean who thinks natural joins are still a good idea. Thank you guys. That was the heckler, yes. Yeah, pretty much. Yeah. Anyway, then here's some exercise. We've essentially done those and there's some advanced stuff if you want to play with it later but it's not really necessary. Last questions. Anyone? Do you have this in written form somewhere? Can you repeat the question? Yes. Do I have all this in written form somewhere? No. I do not. I'm afraid. I'm happy to not wipe this out and you can copy it down. Take a photo. No. Because it's something you would, yes, grab it from him.   No, you will write it, not me. You've got it already. Yeah, so why the question then? Yes, no. Because it's the exercise plus the writing down that makes you remember it. You showing it to your colleagues is not going to do the trick. So, yeah, it serves no purpose.