from Berlin, Germany. It's theCUBE, covering DataWorks Summit Europe 2018. Brought to you by Hortonworks. Well, hello and welcome to theCUBE. I'm James Kobiela. I'm the lead analyst for Big Data Analytics with the Wikibon team within SiliconANGLE Media. We are here at the DataWorks Summit 2018 in Berlin, Germany. And I have a special guest. We have a special guest, Bernard Maher. One of the most influential thought leaders in the Big Data Analytics arena. And it's not just me saying that. You look at anybody's rankings. Bernard's usually in the top two or three of influentials. He publishes a lot. He's a great consultant. He keyed out of this morning on the main stage at DataWorks Summit. It was a very fascinating discussion, Bernard. And I'm a little bit starstruck because I assumed you were this mythical beast who just kept putting out these great books and articles and so forth. And I'm glad to have you. So Bernard, I'd like to you to stand back. We are here in Berlin, in Europe. In, this is April of 2018. In five weeks time, the global data, general data protection feels global because it sort of is. General data protection regulation will take full force. Yeah. Which means that companies that do business in Europe and the EU must, under the law, protect the personal data they collect on EU citizens, ensuring the right to privacy, the right to be forgotten, ensuring users of people's ability to withhold consent to process and profile and so forth. So that mandate is coming down very fast and so forth. What is your thoughts on GDPR? Is it a good thing, Bernard? Is it high time? Is it a burden? Give us your thoughts on GDPR currently. Okay, first, let me return all the compliments. It's really great to be here. I think GDPR can be both. And for me, it will come down very much to the way it gets implemented. So in principle, for me, it is a good thing because what I've always made companies do and advise them to do is to be completely transparent in the way they're collecting data and using data. I believe that the big data world can't thrive if we don't develop this trust and have this transparency. So in principle, it's a great thing. For me, it will come down to the implementation of all of this. I had an interesting chat just minutes ago with the event photographer saying that once GDPR kicks in, he can't actually publish any photographs without getting written consent for everyone in the photograph. That's a massive challenge. And he was saying he can't afford to lose 4% of his global revenue. So I think it will come, it will be very interesting to see how this will- I think it will be affecting face recognition. I'm sorry, go ahead, yeah. Maybe. Well, maybe that's a bad thing. Maybe it's a good thing. Maybe it is, yeah. Maybe. So for me, in principle, a very good thing. In practice, I'm intrigued to see how this will get implemented. Of the clients you can sell, what percentage in the EU, without giving away names, what percentage do you think are really ready right now, or at least will be by May 25th to comply with the letter of the law? Is it more than 50%? Is it more than 80%? Or will there be a lot of catching up to do in a short period of time? My sense is that there's a lot of catching up to do. I think people are scrambling to get ready at the moment. But the thing is nobody really knows what being ready really means. I think there are lots of different interpretations. I've been talking to a few lawyers recently, and everyone has a slightly different interpretation of how far they can push the boundaries. And so, again, I'm intrigued to see what will actually happen. And I very much hope that common sense prevails and it will be seen as a good force and something that is actually good for everyone in the field of big data. So, slightly changing track. So, in the introduction of you this morning, I think it was John Christ, important work, said that you made a prediction about this year that AI will be used to automate more things than people realize, and it will come along fairly fast. Could you give us a sense for how automation, AI is enabling greater automation and whether, this is the hot button topic, AI will put lots of people out of work fairly quickly by automating everything that white collar workers and so forth are doing. What are your thoughts there? Is it cause for concern? Yes, and it's probably one of the questions I get asked the most, and I wish I had a very good answer for it. If we look back at the other industry, I believe that we're experiencing a new industrial revolution at the moment. And if you look at what the World Economic Forum's CEO and founder, Klaus Schwab, is preaching about, it is that we're experiencing this new industrial revolution that will truly transform the workplace and our lives. In history, every other, all of the other three previous industrial revolutions have somehow made our lives better. And we've always found something to do for us and they've changed the jobs. Again, there was a recent report that looked at some of the key AI trends and what they found is that actually AI produces more new jobs than it destroys. Will we all become data scientists under as AI becomes predominant or what's going on here? And this is, I wish I had the answer to this. For me, the advice I give my own children now is to focus on the really human element of it and probably the more strategic element. The problem is five, six years ago, this was a lot easier. I could talk about emotional intelligence, creativity with advances in machine learning. This advice is no longer true and lots of jobs, even some of the things I do, I write for Forbes on a regular basis. I also know that AI is right for Forbes. A lot of the analyst reports are now machine generated. Natural language generation, a huge use case for AI that people don't realize. Absolutely. So for me, I see it as an optimist, I see it positively. I also question whether we as human beings should be going to work eight hours a day doing lots of stuff we quite often don't enjoy. So for me, the challenge is adjusting our economic model to this new reality. And I see that there will be significant disruption over the next 20 years with all of this technology coming in and really challenging our jobs. Well AI put you and me out of a job. In other words, will it put the analysts and the consultants out of work and allow people to get expert advice on how to manage technology without having to go through somebody like you or me? Absolutely. For me, my favorite example is looking at medicine. If you look at doctors, traditionally he's been a doctor to medical school for seven years. You then hope that they retain 10% of what they've learned. If you're lucky, then they gain some experience. You then turn up in their practice with your conditions. Again, if you're super lucky they might have skim read some of your previous conditions and then diagnose you. And unless you have something that is very common, the chance that they get this right is very low. So compare this with your old stomping grind IBMs, Watson. So they are able to feed all medical knowledge into that cognitive computing platform. They can update this continuously. And I could then talk to Watson eight hours a day if I wanted to. But can you trust that advice? Why should you trust the advice that's coming from a bot? Yeah, that's one of the key issues. Absolutely. And I think at the moment, maybe not quite because there's still a human element that a doctor can bring because they can read your emotions. They can understand your tone of voice. This is going to change with effective computing and the ability for machines to do more of this too. Well, science fiction authors run amuck, of course, because they imagine the end state of perfection of all the capabilities like you're describing. So we perfect robotics. We perfect emotion analytics and so forth. We use machine learning to drive conversational UIs. Clearly a lot of people imagine that the technology, all those technologies are perfected or close to it, but clearly you and I know that it's a lot of work to do to get them. And we both have been in the technology space long enough to know that there are promises and there's lots of hype and then there's a lot of disappointment. And it usually takes longer than most people predict. So what I'm seeing is that every industry I work in, this is what's my prediction, is that automation is happening across every industry I work in. More things, even things that I thought five years ago couldn't be automated. But to get to a state where it really transforms our world, I think we are still a few years away from that. Bernard, in terms of the hype factor for AI, it's out of sight. What do you think is the most hyped technology or application under the big umbrella of AI right now in terms of the hype far exceeds the utility? I don't want to put words in your mouth. I've got some ideas. Your thoughts? Lots of them. I think that the two areas I write a lot about and talk to companies a lot about is deep learning, machine learning, and blockchain technology. Blockchain. So they are for me to where they have huge potential, some amazing use cases. At the same time, the hype is far ahead of reality. There's sort of an intersection between AI and blockchain right now, but it's kind of tentative. Hey, Bernard, we are at the end of this segment. It's been so great. We could just keep going on. I know, I know we could just be. Yeah, there's a lot I've been wanting to ask you for a long time. I want to thank you for coming to theCUBE. Pleasure. This has been Bernard Maher. I'm James Kobielus on theCUBE from DataWorks Summit in Berlin. And we'll be back with another guest in just a little while. Thank you very much.


Hey, pooter welcome back to the channel here at the treehouse got a treehouse vlog before you today, and we're gonna be building a setup to Take out this backyard farm it that has been Tearing up the grass. I thought I got it with the possum you guys didn't see that video I did some trapping trapped possum. I also trapped a raccoon I think it was the raccoon that had knocked over some of our pots and been digging into our Garbage getting out Ben's dirty diapers, which is disgusting and possibly the same one that killed our chickens We had a tire flock of chickens last fall get decimated by a raccoon There was a possum in in the coop as well Lots of varmints running around here. However, we still have a problem with the plants being destroyed and the grass Being killed and holes dug in the yard and digging around the house And so I put out some more trail cameras and I discovered that we have got an armadillo I like armadillos. I think they're cool. They're sort of like our state Pet here in Texas cool animal, but they do a lot of rooting a lot of digging similar to like a mole like they're Little nests that they build their hide. I don't know what you call them They do a lot of digging I have them all over my deer lease and in this instance They're just they're just digging holes in the grass. They're killing the plants kill the grass Even found some digging spots around the house area like trying to get up under the foundation and stuff like that So it's just the relationship is not working out at this point. I'm pretty pumped today Cuz I just got some new archery equipment and I've been playing around with it So let's go get into it actually starts you guys off on a sad note and the traditional archery department I probably got a few hundred arrows through my snake bow and then it broke it broke in a Weird spot a spot that I really was not expecting it to break, but it was like a little It was like a little butt crack in the wood. It was sort of weird. I I Covered it up thinking I mean it's a natural part of the wood It wasn't any kind of mistake that I made it was just a weird little just a little indention In the outside bark of the wood and it just just popped popped clean right on that spot So put a lot of work into that bow and unfortunately It is no longer with us still pretty cool.  That's gonna be my next one to replace that snake bow and Hopefully be my my final bow of this year I mean I'm running out of time to make it and get comfortable with it I've got a bunch of other Osage that is almost completely dry now because it's been You know 110 every day for two months and here in the woodworking and tackle cave We have sort of started turning into well a woodworking Tackle and archery cave as well working on the traditional bows has led me down a path of Trying to be a better archer overall with compounds and traditionals. It is so hard to take a tree Make a stick out of the tree then take another stick and get it to fly down range straight and accurate and Do that in a repetitive consistent form So a lot of respect for it be going down a rabbit hole trying to learn more be a better archer Through that process started working on a lot of my own stuff. I like bow tech bows I've been working with my friends at bow tech for a couple of years. They've been sending me some some bows To play around with and this year super excited about Couple new bows. This is my CP 28 28 inch axel axel just a little powerhouse of a bow but this one's gonna be really good for the blind and up in the trees and doing stocking and all of that fun stuff and I would really love to use that To Take out an armadillo. I've actually got a red light that I could put on the front of this and do some some night Hunting with it, but I don't even have a sight on it yet, and I can't record video that way That's the most disappointing part. So my buddies at bow tech They're sending me these bows and they asked me if I wanted a crossbow from one of their affiliate brands X caliber And I was just said no. I I'm so dedicated. It's so hard just to shoot the regular bow I'm gonna stick with that and Anyway one showed up at the house, and I was like, I don't know what I'm gonna do with this and then I started looking at it And I was like wow, this is Very similar platform to all my other ARs. It's already got a picatinny rail on it and I said, oh I've got a infrared night vision scope. I can put on there and it turns this baby into a What what is potentially an ultimate backyard? Critter getter That is just neighborhood friendly, but these things are incredibly simple. It is not like Working on a compound. In fact, I'll just show you guys Like I just want to take this down real quick You know, you put this at a backpack either That's the Little nut keeps everything together one bolt And it just comes apart like that. That's a little bow right there It's a little bow This isn't one of their high-end models. This is the mag air right here, but I think it's gonna be perfect for what we're gonna do You know, it might not be the best at taking down whitetails in 100 yards, but For this it's gonna be the juice go ahead and put our bolt back in here Tighten that up. Now when you scope, okay, I just got done charging this sight mark wraith This is typically what I hog hunt with and it's an IR scope So it's already set up to pop on here. They go easy. This is gonna be my goodness How about that? That's like childhood dreams Coming true right there night vision crossbow scope in literally a minute and then what we're gonna do To really make this thing pop is I've got an aftermarket IR light. This is the sniper hog light Things are great. So this will actually attach right to the scope itself and we're not gonna be Shooting very far. This is probably gonna be less than 20 yards So it's gonna be really bright. The greatest thing about this is that I can't achieve with the regular bow is I can film it. So it's all on video 4k on this Some may say, well, why don't you do it on the on the on the pellet gun? That's a that's a good idea as well. In fact, I've got these big old field rats That have been getting into the chicken coop And they've been getting in the side of the house and I'm charging up the thermals right now getting ready for all out war with this These rodents around here, but I don't have a picatinny on top of this I need to get an adapter and I'm all honestly not even sure if that would work on An armadillo because they've got that hard armor shell. I mean Not quite a turtle, but pretty daggum tough 308 would probably get it done But that's not neighborhood friendly and that's why we've got the night vision crossbow Okay, now it's time to have some fun. So we're gonna set this thing in safety glasses are going on The sink scared me a little bit. So hopefully nothing goes wrong. Nothing explodes And this is currently set for my rifle at a hundred yards So we're gonna get pretty close about ten yards away And see where it hits This is the just crazy part about using these things. They're so So strong Safety on Okay, we got about I don't know this is about Maybe 18 18 yards here I'm gonna go center. Okay. It's a little high Little high to the right. So I'm gonna re zero this pretty much dead on Okay, that's pretty cool Doing that one shot zero with this I wasn't sure if it was gonna work with a crossbow, but it seems like it is working really good So I guess it works on on anything. I'm gonna pull those I'm gonna take two shots if we're on I would say we're good. I'm just gonna back it up. It's about 20 30 and just see where the drop is but I can almost guarantee you we're not gonna be shooting past 20 So that should be perfect man's picture in the scope looks Really good That is bully. This is either gonna be like an upper deck type thing upper deck shot in this flower bed in front of me or It's gonna be Really close and personal looks super juicy at 10 Do what we got here No complaints so, yeah, first one was just a hair low Just out of the bull and then at 10 we're just like You know a centimeter low on bull So I think that's a pretty good indicator now. We just need to back it up And just get myself an idea where I need to aim For elevation if we do have like a 25-yard shot So it looks like we did about actually about four inches low shorter than I thought on the 25 and then at the 20 We're only like two inches so That's not bad at all. It's not a bad holdover You know basically right out of armadillos back and then Successful side in with crossbow Happy with the scope. I got an SD card issue So I got to get that worked out before we do the real deal filming at night next test I really want to test out some broad heads some that I've never used before when I started searching for Small game broad heads for crossbows. This is what I came up with And it's these these are g5s G5s literally called the small game hand head. It's 125 grains Which is perfect because I can actually screw those in to my regular bow Arrows and use those if I want to for shooting squirrels You know hunting whatever small game Let's take a look at these come on camera. You can do it. I believe in you find There it is So it's like a claw It is nasty, but something that I like about it Number one is grabby. So it's gonna it's gonna reach outside. Just your standard like stump shooter But it it's also it's also gonna hook into things what which is great for this scenario that I'm in Where I don't want my arrow to go too far, you know I've got like almost two acres here and I don't want my arrow to go shooting off So this is going to stop the arrow after it passes through or I miss or whatever it'll hit the ground And these hooks will dig in and it'll make it flip over So that's another reason I think these are going to be really fun Uh to shoot and you know, if I got some flu flu arrows for my for my compound bow, this would be really fun to Go after squirrels and stuff with some trees But I think that's going to be a a big help for stopping those and You don't want to lose an arrow or bolt or whatever you call it lose a whole broad head situation They look dirty, but I want to see what it's going to do. I mean we're dealing with an armored creature here I want to see what it'll do to a can. So We'll finish drinking our little midday refreshment here and then I'm gonna fill this back up with some water and take a shot out and see how it tears this can up All right, we're setting up likely scenario here. I'll put some water in this It's an area with where the armadillo might come right here. Wow that water is boiling Boiling hot. Jeez I think the armadillo's nest Is either in this side yard Or the back corner of the yard. I'm not sure But this is a likely area Likely scenario, okay We're gonna put our can Set our can right here Okay That might be 17-yard shot Pretty good little poke for a can 18.4 Safety on eyewear check Now this wheel Make it shoot a little bit lower. So it's 25 extra grains. We got to keep that in mind We've got a uphill grade. So the arrow should catch and flip over And not go too far Should I freehand this baby? Let's freehand it Smoke the can See if we damage the rod headed off all these rocks. Nope She looks good I could have been a Armadillo could have jumped on that one could have got away But the can Is done. I mean, it's not it's not terrible It's uh less than I would think honestly. I don't maybe I hit I think I caught the side And I kind of ripped it I think that's what happened, but the broad head looks great. Looks fine And I I really didn't see it flip over in uh In my purview, but I mean the arrow did not go far. That's exactly what I wanted it to do Hit the ground flip over Not go very far at all recover the arrow That's a pretty good test Okay, it's about to be dark And we just have one more thing to do and that was to put this IR light on So I just mounted it on this little picatinny Mount on the side of the wraith It looks good. It's a little heavier now the most of the weight's right in the middle. It's not End heavy, which is great. This is the nine-time crossbow night vision crossbow Might have just discovered a fun new way to hog on as well um, I'm gonna leave you guys with shooting some arrows Into the target at night with the lighted uh knock arrows, but inside it in it's ready to go, baby.  Thank you guys for being here. God bless you and I'll see you on the next adventure Okay, we're locked and loaded I'm gonna turn our IR light on barely on It's giving it a little bit of gas right there. You can see that target great Look over here.  I just heard something walking through The brush over here. I don't know what it was Didn't turn on the IR till just now All right, we're gonna take our practice shot here Everything is looking good night vision wise Safety off we're hot Sure, there's no other animals back here Just a deer faked one All right cleared and hot Sending a nighttime arrow bullseye


We are trying to make the world's smallest, fastest and most energy efficient places. We would use them for applications ranging from telecommunications to general light applications. We are able to do this because we are able to make nanowires. These aren't found in nature. These are grown in our lab here. A nanowire is a crystalline material. The size of it is smaller than the wavelength of light. A nanowire is so small that with the space of a thinny hair, we can fit almost more than 1,000 nanowires in. I find it phenomenal that we can reduce nature down to a few equations and using these equations, we can design structures that we can't even see with our eyes. Without the team that we have, we would not be able to achieve what we do. It is really a lot of work from many people. Our group has wonderful ideas and wonderful technical experience and knowledge and we are able to work in fields that have not been explored before. It is just like a big family that we are working together. We learn from each other and there is no such big gap saying, supervisor or students, we all learn together. The facilities at ANU are a state of the art. They are leading in this field of nanotechnology and so we are a better place than just be at ANU. We would like to see our devices being used in everyday applications and we would like to see that they are taking us towards a more sustainable society.


Why did you think it was important to write this paper? Well, ISO 26000 has really made quite a big impression on the world of standards and in the world of sustainability and corporate responsibility as well. And ISO standards can have a big impact if you think about ISO 14001 it is used by hundreds of thousands of companies worldwide. And of course ISO 26000 is squarely addressing the issues of sustainability and sustainable development. Therefore I thought it was important to try and understand the kind of impact that it was having or could have. And how can you assess the impact of a standard like ISO 26000? Well it's hard, it's hard because it's not a standard like ISO 14001 which is a certifiable standard so there are very clear criteria for whether or not someone is actually using it in a formal sense. That's not the case with ISO 26000 it's a guidance standard but it can still have a lot of influence. It has already influenced the debate about what it is legitimate to consider as part of sustainable development. It has meant a much broader range of issues and substantive issues like human rights as well as the environment and many others are all now considered to be part of sustainable development. It has also influenced a number of very significant institutions for example the European community whose new definition of CSR corporate social responsibility has been revised and a new one is awfully like ISO 26000's definition of social responsibility. And even ISO itself is now rethinking the way it approaches all its standards and there are a great many of them to consider whether or not sustainability issues are properly integrated within them. And then of course it can make a difference through how companies actually use it which is perhaps the key. What are the main pathways for its influence? Well one as I said is through organisations through the actual use which organisations of all sorts make of it because it is not just applicable to companies it's also applicable to every kind of organisation. And as I said it has reset the agenda which is very important. Well companies are certainly looking at it and one I think of the most common ways of using it is to review their existing practice against what's suggested in the guidance standard and to look for gaps hopefully. There's also quite a lot of use which is unacknowledged so you can clearly see its influence in some sustainability reports because of the way they're structured. But what is very hard is to know how much difference and how much good practice there is as a direct result of the standard. On the other hand you need to remember that it's actually although the standard came out over a year ago it's actually relatively early days and I can only think that its influence will grow over time. And do you think it will make a real positive difference to sustainability development? Well I hope so. I hope so. So it is squarely aimed at that, at those issues. It is being taken very seriously and even if it does no more than to legitimise the full scope of issues relevant to sustainable development it will have made a significant difference.


My name's Gary Smith. I'm the Director and Founder of Cardiff Street Pastors. We did a project in 2008 with a whole bunch of young people in Cardiff City Centre and we partnered with South Wales Police and they asked us to trial something a little bit like the Street Pastors and it worked really well. Crime came down in one weekend and so we decided to officially form Street Pastors and we went out for the first time in November 2008 and we've been out every single weekend since then. We provide practical help, we care and listen for people. People that may be vulnerable on their own. They've had too much to drink, sometimes they're with friends, sometimes they've been left by their friends and we're there just to make sure they get home safe. So very often we'll get a call and it'll be from the police who found somebody who's perhaps drunk or upset or been separated from their friends. They've got lots of other things that they can do and they need to do but we can spend time and sit with those people until we've actually resolved that problem. Yeah, we've got a lot of work to do and we've got a lot of work to do. We've got a team of people that are going to work in Cardiff Street Pastors and we're just in the middle of it, but we're not sure what we're doing. We've got a bottle of water, we've got some flip flops that we can throw away. We've got a lot of people in the north of Cardiff.


Good morning everyone. I hope all the centers are tuned in. Today we are going to look at more join related constructs in SQL and then move on to other constructs such as net sets of queries, aggregation and so forth. Joining section and course because the query wanted the course ID, semester, year and title of each course. The title was there in the course relation while the other attributes were there in the section relation and therefore we had to join them. So we saw how to join them by putting a condition in the where clause which required that section dot course ID equal to course dot course ID and we listed the relations in the from clause. So this is roughly where we left off and you would have done a bunch of problems in the assignment which required joints. So moving on to the next slide this we covered yesterday. This was a quiz question. Moving on the next construct I want to cover is natural joints. We saw natural joints briefly in the context of relation algebra and somebody was asking this question yesterday also. SQL certainly includes a natural joint construct and the construct is illustrated by the query below which joins instructor and teachers. So this time the teacher's relation has an instructor ID or in this case the attribute is called just ID and instructor also has an attribute called ID and note that these are the only attributes which have the same name in common between the two relations. So the query which you see here say select star from instructor natural joint teachers. So what does natural joint do? It first of all takes the two relations and finds what are the attributes which are in common. So the only attribute in common here is ID. Now this is something you should be careful about because you might see that you want to join an ID but by chance there may be some other attributes in the two relations or if you are joining multiple relations you have to be even more careful. There may be some other attributes which you did not intend to be the same but they happen to be the same name. Natural joint will make them all equal and you may get the wrong result if you use natural joint carelessly. So this is a construct which has to be used very very carefully and there are variants of which which are safer as we will see in a little bit. So the basic premise is the natural joint takes the attributes which are common and then it matches rows from the two relations which have the same value for the common attribute. In this case the ID attribute. So here the natural joint does what we expect. In the teacher's relation for each tuple which it has an ID value it matches it up with the corresponding tuple from the instructor relation. Note the schema of the result which you can see below here. The ID attribute appears only once. Why did that happen? Although it was present in both relations if we did a Cartesian product ID would have appeared twice. Once from instructor.id, once from teacher.id. But natural joint has already equated them so they have to be the same. There is no point having the same attribute twice. Therefore the natural joint operation whether it is in relational algebra or in SQL removes the duplicate attributes and stores ID exactly once. So the schema over here has the attributes ID, name, department name, salary, course ID, section ID, semester and year. So those are all the attributes from the two relations and you can verify that the rows in here correspond to things that match. So that is the natural joint operation. As I said there is a danger with natural joint that unrelated attributes with the same name in the two relations may get equated incorrectly. So for example let us take the query which says list the names of instructors along with the titles of courses that they teach. So now what are the relations that we need for this? If you have the schema diagram along with you you can refer to that and you will notice that the names of instructors appear only in the instructor relation while the titles of courses appear in the course relation. Now we cannot directly join instructor and course. The connection is through teachers which has both a course ID and an instructor ID. So we have to join instructor, teachers and course. And what would we be joining on? The joint between instructor and teachers would be on the ID as we saw already. The joint between teachers and course would be on course ID because that is the course information for a particular course that is being taught. So this is what we want to have equated. So if we carelessly write this query as select name title from instructor natural joint teachers natural joint course what goes wrong? Something goes wrong. You actually may run this query and you may get a result which looks correct. In fact it would be correct in most cases but there are cases where this query gives the wrong result. So you have to be very careful with the natural joint constraint. So what is wrong here? To understand that you have to carefully look at what all attributes are in common between any two of these relations. And if you look more carefully we see that there is one more attribute name in common which is department name. It is present both in the instructor relation which indicates which department the instructor in is in. And it is also present in the course relation to show which department a particular course belongs to. If you do a join natural join on this you do get a result which seems correct. But there is a problem. There is a case where this fails. And this is a case which is not very common but does happen. Now most of the time instructors would be teaching courses in their own department. And then the department name of the course would match the department name of the instructor. But this is not a requirement. In many places instructors do teach courses from other departments when the courses are related. So in IIT Bombay for example we have faculty from computer science teach courses in our center for technology alternatives for rural areas sitara. So here you have a faculty member who is in computer science department but is teaching a course in sitara. Now what happens to that particular combination? Now the course ID matches the instructor ID matches. So you would have expected to find the name of this instructor along with the title of the course in the sitara department. Unfortunately the department names of the instructor and course are forced to be equal and that test fails. So although we have an instructor teaching that course that instructor vanishes from the result. And if you test this query you know you may create a few sample data sets and test it actually seems to give the correct answer. But if you took the trouble to create a case where the department names don't match then you would realize that there is an error in this query. So since it's rather difficult to detect such errors you should be very careful when using natural join. Now how do you do this correctly? We can do it correctly by not using natural join at all. We can join all the relations and explicitly list the conditions to be satisfied or we can do part way as is shown in the second query where we do a natural join of instructor and teachers. There everything is fine because only common attribute is the ID. But when we join with course that is when there are two attributes in common one should be equated one should not. So if you observe the query it says instructor natural join teaches comma course. So the comma course forces the cross product between the first part and the second part. The first part is the natural join and then the cross product with the course and the where clause condition specifies the matching condition and here the matching condition is only on course ID. The department name also comes from both sides but that is not there in the condition. Therefore this result is the correct result unlike the previous query. Now there is another alternative syntax which we can use in place of natural join where instead of equating every attribute in common SQL allows us to specify exactly which attributes are to be equated. This is a much safer thing than using natural. I would recommend this rather than natural join and the construct is shown at the bottom. So what is this construct? It says select name comma title from the first part is the same instructor natural join teaches that did not need any change. The second part has changed into join course using course ID. So the join using construct is exactly like natural join but it does not try to match every pair of attributes which have the same name in the two sides. Instead it only equates those attributes which are explicitly listed in the using clause. So here in the using clause we have only listed course ID we did not list department name. As a result the query using the using clause does not try to equate department names and therefore this query is correct just like the second query the preceding query which we saw. So I hope this using clause is clear and in your assignments you can use natural join when you know it is very safe but otherwise please use the using construct. So we have seen so far a few join constructs. Later on we are going to see a few more join constructs including the concept of outer joins but before we get to that there are a number of more basic constructs which are widely used. So I would like to cover those and later in chapter 4 we will get to outer join operations. So the next construct which we want to look at is the rename construct. Now why do you need the rename construct? There are many uses for the rename construct. One use is if you have an expression in the select clause then what is the column name which is output. If you have an attribute name so take the first query the query say select ID name. So in the result of the query which is a relation the names of that first two attributes are obviously going to be ID and name there is no problem. What about the third attribute salary divided by 12 what name does the system give to it? Now it is not defined in SQL the system can give any internal name it wants you do not know what it is. So if you want to give it a meaningful name to be printed out as part of the result you can use the as clause to rename columns. In this case we decided to call it monthly salary to so that it is clear what it is the original one would have been the annual salary presumably and when we divided by 12 we get the monthly salary. Now I should note that certain databases allow the renaming without the as clause. In fact SQL makes as clause optional so even if you just said salary divided by 12 space monthly salary it should work. But certain databases do not support the as clause explicitly but it is always there implicitly if you just say salary divided by 12 space monthly salary that would mean the same thing. So in Oracle for example you should just take these queries and drop the as construct and leave everything else as is and it will work fine although if you use the as construct Oracle would reject the query as a syntax error. So that is one kind of use for renaming where we rename attributes or columns. There is another very important need where we have to rename an entire relation and there are multiple uses for this but one place where it is fairly essential is if you want to join a relation with itself why would you want to join a relation with itself. So here is a query which says find the names of all instructors who have a higher salary then some instructor in compsci. So what we want to do is for each instructor we pair it up with every other instructor from compsci. In fact every instructor from compsci and check if this instructor has a salary which is greater than the other instructor salary if so we output it. So what we want to do is compare every instructor with every other instructor and that requires doing a cross product followed by a selection on two copies of the instructor relation. Now in SQL supposing you write select something from instructor comma instructor there are two copies of instructor but how do you distinguish them they both have the same name. If I say instructor dot salary which one the first instructor or the second instructor that is not clear. Therefore SQL allows you to give an explicit name to two instances of a relation which occur in the from clause by renaming them using the as construct. So in this query we say select distinct t dot name from instructor as t instructor as s. So what do we have here we have two relations in the query now t and s which are basically the instructor relation the query is not actually making physical copies of the relation although internally it may create temporary copies if required but conceptually they are equivalent to turning them into two separate relations and called t and s which are the same as which have the same contents as the instructor relation. So what does this query do first it takes a cross product then it says where t dot salary greater than s dot salary and s dot department name equal to comp sign. So what is that doing it sparing up every t tuple with every s tuple first of all it is eliminating all such pairings where the s tuple is not comp sign. So now clever evaluation engine will actually before doing the cross product it would actually filter out all s tuples which do not satisfy s dot department name equal to comp sign but that is a topic for a later chapter. So now let us say it pairs it up with every comp sign tuple now there is one more condition which is t dot salary greater than s dot salary. Supposing the t dot salary for a particular t tuple is greater than at least one of the s dot salaries then with that particular s tuple that in other words with that particular instructor in comp sign who has a lower salary this t tuple would match both the conditions in the wire clause. Therefore it would appear in the result paired with that s tuple. Now of course the result does not actually output the s tuple it is only outputting t dot name. So that particular name will come in the output now why do we need a select distinct over here that is because a particular t tuple may have a salary of let us say 100,000 and there are five people in the comp sign department which salary less than 100,000. Now you will note that that particular t tuple will get matched with every one of these five instructors in comp sign with a lower salary and therefore t dot name would appear five times in the result and since we do not want that we have put a select distinct there to remove the duplicates. So that is an example of a renaming in the from clause. Now again in certain databases such as oracle the as clause has to be removed and if you just say instructor t comma instructor s that has the same effect. Now a scale is stored used to store character strings very often and naturally you need to do certain operations on character strings occasionally. For example you know you may want to match name supposing I know that there is some employee whose name contains some particular string let us say Fatak but how do I find which of the employee names matches Fatak? I cannot say name equal to Fatak because the name may be stored as db Fatak. So it is not exactly equal. So I want to pattern match that is I want to find names which contain the string Fatak. Now how do I specify this? It is an important kind of query is a very common kind of query therefore SQL has a way of specifying this and it has a very simple pattern language which has two special characters percent and underscore. Percent matches any string while underscore matches a single character any single character. So here is an example of a query this time instead of Fatak it is looking for any name of an any instructor whose name contains the characters dar. So what did we do? This query says select name from instructor where name like percent dar percent. So what are the two percent doing? It says there can be anything preceding dar there can be anything following dar and it does not matter what is there. So for example Sudarshan would appear as a result because it has something before and something after. What if a name was just Darshan something? So dar is right at the beginning then too it would be a part of the answer. Percent can also match the empty substring it does not have to have any characters there. So this query will find anything which has the substring dar. I should note that the like operation in the SQL standard is case sensitive. Therefore it will not match something which where the D is capital as stated. However many databases support extensions to this. For example in PostgreSQL you can say I like which is case sensitive like and if you use I like then it would match a string where D is capital or D is small it does not matter. Now if that is not supported what do you do? Well there are operations which can map strings to upper or lower case. So in SQL for example there is the upper operation. So that operation is the function upper. So if I say where upper of name like percent and then I put capital Dar percent then it will match regardless of whether the string initially had lower case or upper case or if I said lower name like percent Dar percent. Again if I use small Dar there it would still work because if the name initially had a capital D the lower function would convert the capital D to a small D and then it would match. Now what if I want a string which actually has percentage. So I have some statistics and I want to find an occurrence which has let us say 90 percent. So how do I match the percent? So SQL has a escape character which you can explicitly list. So you can say like 100 backslash percent escape percent. Now note that all strings in SQL are enclosed in single quotes. Therefore quote 100 backslash percent quote is in single quotes as also is quote percent quote. So now this will match the string 100 percent literally rather than look for anything which contains 100 followed by any other characters. And there are a number of other functions in SQL. Another very commonly used function is string concatenation which in SQL you perform by using the vertical bar twice a double vertical bar. So that concatenates two strings. So if my database has first name and last name and I want to output it as a single column I can say first name double vertical bar last name and they would come out as a single column concatenate it together. There are other functions for example finding the length of a string extracting substrings if I want to pull out certain characters from a string and so forth I can do that. So there is a variety of string functions in SQL. I would not list them here but your manual for the database will tell you what functions it has. Now let us move on to another topic which is important which is how to order the results of a query. Now as I told you earlier a relation in the relational model is unordered. There is no particular meaning to the order in which the tuples occur. But when I output results to the user the order is very important. Why? Let us say I have a list of students with their IDs which I want to output onto a screen and now the user wishes to scan through this to look for specific IDs. If the IDs are sorted obviously it is a lot easier for the user to find those. Therefore SQL allows a way to order the results of a query when it is output to the user. There is another utility for this which we will see later but for the moment it is just to order the results in order to present it to the user. So SQL has an order by clause which is applied at the end of a query. It is listed at the end of a query. So here is a query which says select distinct name from instructor order by name. So the names come in alphabetical order and by default the order is ascending. So the least name is order up to the highest from A up to Z at the end. So that is the ordering. Now if I want to change the ordering I can say order by name descending. If I want to order by multiple attributes that is if I want to order by department name and among all the instructors in the same department I want to sort them alphabetically. I can say order by department name comma name. Now what if I want to order department names in descending order while instructor names are in ascending order. I can do that too. I can say order by department name descending, instructor I can say ascending or if I leave it default it is ascending. So I do not need to specify that. And descending and ascending can be abbreviated to DESC and ASC. So that is how you order. Now note that the order by clause is applied after everything else is done in a query. So we have seen select from where clauses they will all be applied to get a result and at the very end the rows in the result are sorted based on the order by clause. Now to take a short break here is a quiz question. We need to set up the quiz. So do not answer it yet but you can read the question meanwhile. I have a couple of questions from yesterday which I thought I can answer now. The first question is from Manipal Institute which says I believe relational algebra is categorized as a procedural language. Could you please let me know why it is so when we just use a single statement to access the data. This is a very good question is relational algebra procedural or is it declarative? Now as in many things in life everything is a shade of gray. It is not all black and white. So what is procedural and what is declarative has a lot of shades of gray in between and certain people look at things in the middle and say the glass is half full while certain others say it is half empty. So that depends on whether you are an optimist or a pessimist. So an optimist in the relational camp would say relational algebra is purely declarative while a pessimist would say hey it is procedural because it tells you do this step followed by this step followed by this step. And both the points of view have definitely some degree of correctness in them. Relational algebra is not purely declarative because it does tell you to do a sequence of steps. First do this join then do this projection and so forth. However, it is still fairly declarative because it does not tell you how exactly to do the join or the projection and so on. And there are many different ways of doing the same join or projection as we will see when we look at query processing. So it is certainly declarative up to this point. In fact, it can be treated as much more declarative because it is fairly easy for a query optimizer to rewrite a relational algebra query in a different way which is equivalent. And therefore, even though the programmer thought they were specifying a particular order of operations, the evaluation engine can completely change the expression but still get the same result. So what is happening is even though the you know the person who wrote the query may have wanted it procedural. But in fact, most programmers who write in relational algebra do not assume that the query will actually be executed in this particular sequence because they know the optimizer may change it. In fact, nobody writes relational algebra queries to be honest. Everyone writes SQL queries. But what happens is that it is translated into relational algebra and then it is optimized. So if you went to the intermediate level, anyway it is going to be changed. Therefore, you can't read relational algebra as being essentially declarative. So depending on which camp you are, you can call it either way. But it is clear that if you write a query in C or C++, it is certainly procedural. It is not declarative. If you write it in relational calculus which we have not covered in this course, they certainly don't give any procedural element. They are very declarative. Relational algebra is somewhere in between. What about SQL? SQL is again, it is based on relational algebra. So it is somewhat procedural but nobody would really call it a procedural language. SQL is certainly declarative. So I would go on the side of saying both of these are essentially declarative languages. So I hope that answered the first question. Now there is another question which is from Amrita Bangalore. The question says in the create table takes schema, you said that section ID need not be a part of the primary key because at a time a student can register for one section. But I feel semester and year need not be part of the primary key either because given ID and course ID, it can be in a particular semester of year. So can ID course ID be a primary key? Now in a world where no student ever fails a course and everybody passes and therefore they take the course only once and no one can re-register trying to get a better grade for that matter. In such a world you could say that ID course ID is a primary key but in reality students do fail a course and they may need to take it again. So then ID course ID would not be a primary key because we want to record that the student took it in autumn 2010 failed and will be taking it again in spring 2011. So the second one will be the same as the first if you just take ID course ID but once you add semester year it will be a primary key. But it is much more clear that a student cannot take the same course twice in the same semester in parallel that does not make any sense. So I hope that answered part one. There is another part of this query in a query where we find the course ID sum etc. where we join two tables section and course. We explicitly mentioned section dot course ID to resolve it. Can it be course dot course ID? We have a course ID in section and in course. Both the tables have the same attribute name. Now if both the tables are present in the query in the where clause if you simply say course ID you are not being clear about which course ID you meant whether you meant section dot course ID or course dot course ID. If both of these are in the from clause in the where clause or in the select clause you have to be explicit. So you have to say one of these two section dot course ID or course dot course ID. Now if you equate the two. So in the where clause I say where section dot course ID equal to course dot course ID. So let me write it out. I do not know if you can get this screen up here. Select star from section comma course where section dot course ID equals course dot course ID.  So it will actually have two copies of course ID one of which will be section dot course ID the other will be course dot course ID. Now we know that we have actually equated them. So they are both going to be the same. So why have two copies? So instead of select star if we struck that out and instead we list explicitly what we want. So I could have said select section dot course ID comma whatever else I want. Now why did I choose section dot course ID? Well I could have equally well said course dot course ID. Now this is going to be entirely equivalent because anyway both of these have been forced to be equal in the where clause. So I could use either one. It does not really matter. I hope that answered the specific query which you had. Please see the question. Do not answer it yet until the clickers are enabled. But first please press the S T key on your clickers. So the clickers wake up and are ready to answer the question. We are giving you one minute for that. Please press S T key on clicker. So I hope you have read the question by now. You can omit the order by if you omit the order by clause. The options are there is no default ordering. The results may turn out to be ordered. Both the above are true and neither one nor two is true. So those are the four options. Press A B C or D to answer the question. I think you had enough time. So let me move on to the next slide. We will see the results only after two more minutes. Now let us move on to some more syntactic features of SQL. These are syntactic sugar. They are not really essential but they are useful. So the first construct is something called the between operator which lets you say that a particular value should be between two specified values. So if I say that salary between 90,000 and 100,000 that means it is greater than or equal to 90,000 and it is less than or equal to 100,000. We could have written it having two comparisons and an end. This is just a simpler way which is useful in some context. Another piece of syntactic sugar is instead of listing A equal to B and C equal to D, we can say A comma B equal to C comma D. So here is an example of that. We have instructor and teachers. Now I want to find instructor teacher's address where the IDs match of course and the department name is biology. So this is a fairly silly example. Nobody would write it this way but there are other examples where it is more useful. But here I could write the query as where instructor.id comma department name equals teachers.id comma biology. So it is a pairwise equality. So the instructor.id will be equated to teachers.id and department name will be equated to biology. So that is the tuple comparison. Now we will see later that there are much more meaningful uses of this when we see subqueries. The next topic is duplicates. Now as I told you before if you do not declare a relation to have a primary key SQL will happily let you have duplicate tuples in the relation. That is usually a bad idea but there are certain situations where duplicates are useful. So for example if I want to find the average salary of instructors. Now I want to get the salaries of the instructors and then find the average. We will see how to do that in a little bit. But note that there may be two instructors with the same salary. If I eliminate one of the salaries, one of the copies of the same salary then the average is going to differ. So I do want to keep both copies. Therefore there is a good reason to have multi-sets in SQL which have potentially have duplicate copies. So that is a semantically meaningful reason. But SQL started dealing with duplicates for another reason as well which is to do with efficiency. So the idea was if you want to eliminate duplicates as in relation algebra does there is a cost to it. How do you eliminate duplicates? You have to find if there are duplicates. Now given a large relation how do you find if there are duplicates? Well the standard way of doing this is to sort the relation and then any duplicates would be adjacent. So you could look at adjacent tuples if you are equal drop one of the tuples. So this way we can sort and then find duplicates. Unfortunately sorting is a very expensive operation and if every query required a sort queries would run very slowly. So SQL made duplicates elimination an optional feature. So what it says is if you say select distinct I will eliminate duplicates. If you just say select I will keep the duplicates. So this was SQL stand initially which made sense from a efficiency viewpoint. There are places where the programmer is clever enough to realize there are new duplicates although SQL is not. So even without sorting and eliminating duplicates there will be no duplicates in the first place. There are other cases where there may be duplicates but it is okay the programmer realizes it does not matter really. So this is how the situation was until people started making use of the results of the query on something above it. So if you put an aggregate on top of this then you will realize that the number of duplicates matters. Now the number of duplicates it turns out can vary if you use different evaluation plans for the same query. The original intent was let us keep things cheap by not eliminating duplicates. The problem was depending on how you evaluated the query you may have a different number of duplicates. So now a database system might say look I will make sure that whatever the plan I will get the same number of duplicates. But now Oracle may make a decision, IBM may make another decision, PostgreSQL may make a third decision and the same query may give different results on different database. Therefore SQL standards committees decided early on that if you are going to allow duplicates in the result we better define exactly how many copies of duplicates will be present. So that is what we are going to see. To understand this instead of dealing with the whole SQL language it is easier to deal with relational algebra and define the number of duplicates in a multi-set version of relational algebra. And then the SQL semantics in terms of how many duplicates generate will be defined in terms of relational algebra. So the two-step process makes life simpler because it helps us avoid explicitly dealing with each of the syntactic components of the SQL language. So let us start with the selection operation in relational algebra. So if I am given a relation R1 with which may have duplicates and I apply sigma theta on R1. So what is sigma theta? Theta is a predicate which filters out rows in R1 and only outputs those which satisfies the predicate theta. Theta stands for any predicate. It could be you know name equal to. I had duplicates in the input relation R1. The multi-set version of selection says that I will just go through the relation and for each couple regardless of whether it is a duplicate or not I will just apply the selection and output it. That is simple, efficient and that is exactly the semantics of sigma theta. If there are duplicates it just preserves them as is. It does not change anything. The next operation is the projection operation which outputs specific columns and eliminates other columns. Now projection is interesting because it can introduce duplicates. We may have a relation which differs on overall the tuples are not the same. There are no duplicate tuples. But if I project on a single column for example if I project instructor on department, instructor relation does not have duplicates. But if I project it on just department surely computer science has multiple instructors and there will be duplicates in the projection result. The basic relational algebra version of project eliminates these duplicates. In contrast multi-set relational algebra keeps all these duplicates. It does not try to do anything. It does not waste time removing duplicates and it is actually very straightforward. It just goes through the tuples one by one projects out the columns which are as for and outputs that tuple without bothering whether it introduced duplicates or not. So, that was easy. Now let us go to cross product. So, R1 cross product R2. Now what are the duplicates in there? In fact here too what it does is the cross product operation outputs every tuple in R1 paired with every tuple in R2. And it continues to do that. There is no change in that operation. But we need to understand what happens in terms of the counts. Now you may implement the cross product in a different way or you may optimize the query and do various things to it. But how many copies of a duplicate tuple will be there in the result if there are duplicates in the input. So, to understand this supposing tuple T1 occurs C1 times in R1 while T2 occurs C2 times in R2. So, the first one occurs maybe 2 times in the first relation and same tuple sorry the matching tuple in fact for a cross product any tuple T2 it does not even have to be matching appears maybe 5 times in the other relation. Now each copy in the first relation will be matched with 5 copies in the second relation. So, how many copies do we get overall? 2 copies in the first relation each match with 5 or 2 times 5. So, in general you will get a product of the number of duplicates in the 2 relations. So, if there were C1 copies of T1 and C2 copies of T2, T1 T2 will appear in the result C1 times C2 number of times. So, that is also straight forward. Now what about the join operation? How do we define the duplicates? It turns out join is not a basic operation join we know can be expressed in terms of a cross product followed by a selection. Therefore, the number of duplicates in that does not have to be explicitly defined. It is implicitly defined by saying the join is simply cross product followed by a selection and we have already seen how to count duplicates for selection and for cross product. So, if you did not follow that fully here is an example with duplicates. So, R1 has 2 tuples 1a and 2a while R2 has 3 tuples and the schemas by the way R1 has the attributes A, B and R2 has attributes C this 1 attribute. So, R2 has 3 tuples 2, 3, 3 note that 3 appears twice over here there is in fact no duplicate, but if you did a pi on B of R1 what do you get? The value 2 appears twice although R1 did not have duplicates the projection has duplicates. Now if you take a cross product of that with R2 what do we get? Well the A value appears twice here and over here 3 appears twice therefore, you will get A3 2 into 2 which is 4 times. Similarly, 2 appears once here and A appears twice here. So, you get 2 into 1 which is twice A2 A2 followed by 4 copies of A3. So, that is the result for relation algebra. Now what about SQL? I have been saying that SQL queries can be written in terms of relation algebra. I am not going to do it for all SQL queries, but for the simplest form select from where the translation is very very simple. Query of this form select A1 through AN from R1 through Rn where P stands for any condition it can be an AND or it can be a complex condition it can be anything which appears in the wire clause. Now this SQL query is defined it is semantics or it is equivalent to the relation algebra query which does the following. The relation algebra query first takes a cross product of R1 through Rn then it applies the predicate P which is in the wire clause that is we have selection sigma sub P applies the predicate P. Finally, the attributes which are listed in the select clause appear in the projection. So, project on A1 through AN sigma P of R1 cross R2 up to cross Rn. So, this defines the meaning of a SQL query and now we know how many duplicates there are for relation algebra. So, we know how many duplicates that would be in the SQL query result. Now there are some details which I am omitting here what if the SQL query had an expression those details are fairly straightforward and I am not going to spend time on those. So, that was the duplicate issue. Now duplicates do have an impact on query processing if you did a select this thing the SQL can do whatever it wants as long as it removes all duplicates at the end. If you did not use a select this thing then SQL has to make sure the number of duplicates is correct.