So my name is Scott Schiff, I'm actually a Dublin City Council member and also a proud member of the MCC community. Pleasure to be here in Mubarak, I'd also like to introduce our Vice Mayor Sherry Hu to say a few words. Good morning. Eat Mubarak to all of you. I'm Sherry Hu. I'm the Vice Mayor of the City of Dublin. Thanks to Zahir and KSF for inviting me here. So today marks the end of the month of fasting, Reflection and Community. It's a time to celebrate the achievements in self-discipline and spiritual growth. Let us be thankful for the blessings we have received and remember those who are less fortunate. Let the eat to be the time of giving and sharing. Eat is also time to come together, not just as families and friends, but also as a community that supports and cares for each other. So let's use this opportunity to strengthen our bond and reach out to those in need. So I pray this eat will open doors to peace and harmony and bring us closer and achieving the world and everyone next to me and enjoy life. And here I thank you for all come to our city of Dublin. It's a beautiful city, so let us all have a peace and joy here. So once again, eat, move, or walk to everyone here. Thanks for having me. Thank you. Before I get off, just one quick note, City Hall for the next few days is going to be looking green in the evening after motherhood, so please take a visit. It's a hundred city Plaza, so now I have you. Thank you for the number and thank you, Vice Mayor. Good luck.


Okay, so now let's move back to questions first and then we will move on to new slides. Sri Ramakrishna in Poimbatu, please go ahead. Sir, good morning sir. This is Jyothi from Sri Ramakrishna Institute of Technology. I have two questions. One is, what is the difference between Warkare and Warkare 2? The second question is, how to motivate the students in designing the database by using the normalization concept because while doing the project, the students have less plan in designing the database. So how to motivate? What strategy we can adapt to motivate the students to go for normalization for designing their database during their project work? Over to you, sir. Thank you. Both are good questions. The first was, what is the difference between Warkare and Warkare 2? Warkare is a normal single byte character string. Warkare 2 is 2 byte character string and in the SQL standard, it's called nWarkare for national as in not just ASCII, but character sets which are there in Unicode. So if you are going to store names in any language other than English, you better use Warkare 2 or nWarkare depending on what is supported in your database. If you use plain Warkare, you may not be able to support Unicode in full generality, although you might be able to store utf8, which is Unicode encoded in 8-bit format. You may be able to use that, but nWarkare or Warkare 2 avoids issues with this. So that's what that is. The second part of the question was, how do you motivate people to use normalization for their projects? That's a good question, but I would like to defer that to the point when we cover normalization and it turns out that when you do ER modeling right, most of the time you don't have much to do with respect to normalization, but it is something which you do need to check as a sanity check and we will return to this question when we cover normalization a few days from today. Periyar, when am I in Valam? Hello, good morning sir. I have talked about two questions regarding sync operations. Like operator used to be used two characters, percentage and underscore. What is the major difference between these two characters? And another thing, how null value and unknown values are stored distinctly in a database? First part was percentage and underscore. What do they mean? These are called wildcard characters which are used typically to match prefixes. So many times you know a name partially, but you are not very sure about the spelling of the name later on. So if you get the first three or four characters of a name, you can easily look at a list of names and pick the names that you want. This is a very, very common occurrence.  Out here in Mumbai people tend to spell it right, but back when I was in Chennai, it would be spelled in all possible manners and the right spelling was more an exception than the norm. On the other hand, most people would say that Sudarshan, it must start with S-U-D although there was some who would spell it as S-U-N and turn it into Sundarshan and various other things. But let's say that they got at least the first three characters, right, S-U-D. So you could look for S-U-D percent and then you would get Sudarshan in that list. You might get other things. You might get Sudama. You might get many, many other names. But let's say that you are looking at just instructor names out of 500 or student names out of a few thousand. The number of things which start with S-U-D is small enough that you can browse and pick the one you want. So this is a typical example of using the percent symbol or to look for names which start with something. In other cases, you don't know whether name is the first name or the last name and the name may be stored in the database in either order. So then you might say take a name and then search for it as percent Sudarshan percent. So anywhere if Sudarshan appears in a name, it will show up. Whether it was written as S-U-Darshan, Sudarshan S or it would even match something like Sudarshan M because, you know, if space is not considered special here, percent can match space. It can also match non-space. But all of those would show up as answers. So it's used for searching when you know things approximately. Underscore is a special case where it's just for one single character, not multiple characters. So that is useful in certain cases where you are not sure of one character but the other ones are clear. Does that answer your question? The second part was how do you store null values in the database? We are actually going to cover this in database internal. Now for strings, it's easy enough to store a null string. If you are used to Java or C++, you know that a null pointer, it can be used to represent a null string. But what about integers? There is no pointer. Every single value for an integer is a valid value. So what do you do? So the way it's done in SQL implementation is to have an extra bitmap for such a type. So there is a bit which says that is this value a null value or not? It's stored separately from the value. So in a tuple, there is a single bitmap for all the integers which occur in the tuple and maybe all the cares, not the wirecares but the cares which occur in the tuple and other such types and one bit for each attribute and if the bit is one, it's null. If it is zero, it's not null, something like that. I hope that answered your question. Thank you sir for answering our question. We have two more questions to be answered. Go ahead. First question is related with null value. When we are checking some condition against null value, we instead of making use of equal to operator, we make use of ease. Is there any specific reason for making use of ease here? Another question is related with group by function what we have seen already. That is, we have seen one query which is grouped using department name. In that you have mentioned, if one department name is available but no other data are available there, then the department will not be available in the answer. But assume one situation, we have no department available. That is department value is null but all the other fields are available. So many rows are available but all those rows, department value is null. In that case, null value will be available in the department where aggregate function will be applicable for all the other data. Let me answer your second question first. So the question is, supposing I group by an attribute and in a relation that attribute value is null. So department name is null. So what happens? I am going to answer that in the context of nulls with aggregate which is coming up in the next few slides. So I will answer it when I resume my talk but before that your first question was, what is the question? Can you just remind me? When we are checking some condition in relation with null value. So the question was, why do we need this null? Why do not we simply use equal to null? And the reason is that equal is defined on nulls to always return unknown. So if I say some value equal to null, null value equal to null value is unknown. So it will never show up in the results. But sometimes I do want to find those tuples for which a value is null. And I cannot do anything. If I say equal to null it will fail. If I say not equal to null, well the not equal to null will be unknown. Not of unknown is unknown. So every time it will be unknown. So I have two options. One is to use the is null syntax which will match exactly those values which are null. That is why I have the new syntax for is null. There is also another option which says is unknown. Which I can use on top of a comparison with null. So supposing I say r dot a equal to 5 and r dot a happens to be null, r dot a equal to 5 is unknown. So I can say is it unknown? It is unknown. So that is a separate thing. I think I have some slide on it later. But if not you can look it up. So that is what you could use instead of equal to. So with that hopefully I have answered the question. I think we should get back to the slides now. There are many more questions I am sure. So let us get back. So this answers the second part of your question. Null values and aggregates. So there are multiple places where null values can occur. It can occur in the group by column. It can occur in the column on which we are aggregating the data. So let us take these two cases separately. Let us first take the case where the null value appears in a column which we are aggregating. So here is a query. Select some salary from instructor. Now what happens if one of the instructors has a salary which is null? Now technically if I add a null value to any value that value is going to be null. So technically this should give me null. If any one instructor has a null salary the result should be null. But practically speaking that is not very desirable. Just because one or two instructors have a null salary I do not want to say I know nothing about the total salary. It is okay if I give an approximate value but I should not give null just because one or two values are null. So this is the basic principle which SQL uses in the context of aggregates. So most of the time when I use aggregates it is okay if I get an approximate value. It may not be exact. That is fine. So what SQL says is any aggregate operation sum, count, min, max whatever it is is simply going to skip the null values. Not even going to look at them. So some salary will just sum up the salaries which are not null and throw away the salaries which are null. So the only exception to this is count star which is counting the number of tuples there are and therefore count star will include a tuple even if its values are all null. It does not matter. It will include it. Now there are a few special cases. Supposing I said some salary from instructor where department name equal to computer science. Supposing there is no instructor with computer science then what has happened? I have a set with no values at all. It is an empty set. What is the sum on an empty set? That is one situation. The second situation is maybe there is one instructor in computer science but that instructor's salary is null. Now what happens? Using our previous principle null values are ignored. So the set had only null. After removing the null value the set became empty. So in either case we have some on an empty set. So what happens here? The answer is count returns zero but some returns null. That is everything else returns null. Count of an empty set is zero. Everything else is null. That is the basic principle which SQL follows. It can be a little arbitrary but it is designed to do the right thing for most applications. Now we have some time to try out a few more queries. I have put down a few queries here. But I am willing to take queries which are asked by remote center participants. So let me take maybe the first couple of queries here and then I will open it up to others to ask queries. And then we will try writing them. So I hope you are reading the queries here. The first query says the total number of courses taken by Shankar. How do we do that? We have the name of the student but that name is in the student relation. The courses are in the takes relation. And therefore we need to first join the student with the takes relation. We want to restrict it to students name Shankar. And then we want the total number of courses. So how do we find the total? Well we use the count. So let us write this to select something. We will fill that in. From we need the student relation because the name is in the student relation. And then we need the takes relation because the course information is in the takes relation where name equal to Shankar. And we need a join condition between these two. What is the join condition? Student dot id. Id is the field which is common between the two. Equal to takes dot id. So far what have we got? We have done the join. From this we want to find the number of courses that Shankar has taken. So here we want to count the number of courses. So we need a count of what? We will include course id. So that is the final query. So we are including here courses which Shankar has taken but may not have passed yet. In our schema we are going to differentiate courses which have been taken but not yet completed by having a null grade in there. Once the course is completed the grade will be not null. So this includes courses which Shankar has taken but not yet finished. Now supposing we want to find courses which Shankar has actually finished. Regardless of whether Shankar passed or failed the course that is not our concern. We want to eliminate courses which are still incomplete. So what do we want to do? We want to eliminate those courses from this list where the grade is null. So we can say and grade is not null. So what you have done is eliminated those courses which are ongoing and now the new query gives completed courses regardless of whether Shankar passed or failed. On top of this supposing we want to find courses which Shankar has passed. In other words we want to further eliminate courses which Shankar has failed. How do we know failed? Let us assume that grade is equal to the character f for failed courses. So we want to eliminate those. We will say and grade is not equal to. So we can write many different variants of the same query by minor changes. I hope this is clear. I will go back to the document. Let us take the next query. This is a very easy query. Find the first year in which CS801 ran. So what do we mean by course ran? It means there was an offering of this course. In other words a tuple in the section table which is down here. And the section table has both course ID and it has a year. So how do we find the first year, the earliest year? That is easy. We just say find min year from section where we want to restrict it to course ID equal to 801. So we will say where course ID equal to CS801. So that is a very easy query. The last one is find IDs and number of courses taken by all students who have taken more than 20 courses. So what are the things we have to do? Find for each student we want to know the number of courses taken. Just a moment back we did this for Shankar. Now we want to know it for all students. And furthermore after finding out the number of courses taken we want to filter out those students who have taken less than 20 courses. So let us go back to our earlier query and make a few changes to do it not just for Shankar but for all students. I am going to switch back to the whiteboard. So I am going to keep this query as it is. But now I am going to make some changes. First of all here we said name equal to Shankar. Now we do not want to do it only for Shankar. We want to count it for many students. Let us strike that out. But now I want to count the number of courses taken by each student. Therefore I need to do a group by what? We want to group by the student. What identifies the student? The ID. So group by ID. So this is going to give me then for each student first I am grouping all the courses taken by that student and then count of course ID is the number of courses taken by that student. But I also need to know the ID of the student because what will happen right now as shown I am going to get many different counts once per student. But I also need to know the ID. So let me add this field ID over here. So what we have done is select ID comma count course ID and the rest of the query is as you can see group by ID. So for each student I have the count of course ID. But the last part of the query is restricted to those students who course count is greater than 20. So I can add a having clause here. We saw the having clause earlier. Having count of course ID greater than 20. So I hope this query is clear. Now I will take a few questions. And what I would like is not question but I would like suggestions for queries which we can write online. So you ask the query and I will answer it. But before I answer it as the query is being asked I would like every participant to think about how to write that query and then I will answer it. So let's go over to remote centers. We have Amity University. The question is that since the power of expression of SQL and query by example is completely similar and since QB is based on relational calculus which is theoretically simpler than relational algebra then why do we have SQL at all? Why don't we use query by example in all the databases? So first of all query by example did not have a language syntax. It was meant more as a user interface. It turns out that most of the uses of SQL are not directly in a user interface. It's from an application program. Most people don't write SQL but pretty much every single application which we use these days at the back end talks to some kind of data storage system. Most of them SQL some of them other data storage system but it's not interactively writing query. As a result QB's focus was on the user interface and as a it didn't pick up. It did survive in Microsoft Access which had a version of QB but Microsoft Access itself is not used all that much these days. Whereas SQL had a language syntax and it could easily be used for building applications. That's why it succeeded. So it's not to do with the convenience of the language but rather for writing queries but rather the ability to create a language and write queries in that language. So what I would like now is to focus on writing SQL queries. So please anybody else who's asking queries please suggest the query for me to write. That's all I want right now. Everything else we will discuss later. Nagole Institute Hyderabad please suggest the query for me to write. How can we delete special characters from a database table? Again you're not answering what I want. SQL queries for me to write that is the purpose of the current exercise. I'll be happy to take on these questions later but right now please suggest queries for me to write. How to delete a special character from a database table? I want you to write the query. You want me to write a query to delete special characters from a database table. This is purely a string manipulation exercise and you will need appropriate string functions and there are functions which can take a string and do string manipulation, replace characters in there. These are tend to be database specific. It's not part of the SQL standard per se. If you want to delete those special characters, look up your database to find the string match and replace functions and then use that to do the replacement. I cannot answer it in a generic sense. What I want to do is SQL queries which use the functionality which we have seen so far. That's the purpose of it. Javahlal Nehru Simoga Do you have a SQL query for me to write? Sir, write a SQL query to select nth maximum entry in the table. That is, suppose if a marks based on average marks, 10th average marks, 10th highest average marks or 12th average highest marks. That's a very good question. That's a nice challenging kind of question. One which you could ask your students if you want to challenge them a bit. Let me repeat the question. Supposing we have a table of marks, the question said average marks, but to make it just a little simpler, we'll say we have a table with marks already. If you want to find the average, you could do that and use that as an input to the main query. Now the goal is not to find the maximum mark, but to find the 10th mark in class. How do you do this? It turns out that this is a little tricky in the core SQL which we have looked at so far. Now what everybody will say, intuitively, sort the relation by the mark and then go to the 10th row and output that. This might actually work in certain SQL dialects where you can say order by and then say row number or something which counts the position of the row in the output, but that is not part of the SQL standard. These are non-standard extensions because they are not fully declaration. However, there are two answers to this. The first answer is there are extensions to SQL to do ranking and there is syntax for this which we will cover if time permits in chapter 5. If you don't cover it, it's okay. You can go read it up, but it is in fact quite possible using the constructs we have seen so far. So, let me answer the question using the constructs we have seen so far. For this, the first point is we need to somehow state declaratively what is the 10th highest mark in the class? What is the highest mark in the class? It's a mark such that there is nobody who has got a higher mark. What is the second highest mark? It's a mark such that there is only one mark which is higher than it. The third one is the mark such that there are two different marks that are higher than it. Now, note that marks and ranks can be a little bit different. So, supposing two people both got the highest mark. What is the second highest mark? You know, supposing two people got 99 out of 100 and the next one got 98. What is the rank of 98? Is it 2 or is it 3? If you count the number of students, there are two first rankers and then the next one would be the third ranker. That is one way to talk about it and this corresponds to what in SQL terminology is called sparse rank. Let me write it on the white board. So, what is the sparse rank? Supposing I have marks 99, 99, 98. This would be rank 1. This would also be rank 1 and this would be rank 3. In contrast, dense rank does not leave gaps. So, this is sparse rank. Dense rank has no gap. So, this would be 1. This is the same mark. So, it also has to be 1. We cannot make one of them 1 and then the next one would be 2. So, first let us get this concept clear. We have a sparse rank and a dense rank. Now, the next thing is how to calculate the dense rank and this is what I was referring to earlier. For each person, the rank is the number of people or the number of distinct marks which are higher than that person's mark. So, how do we do this? It turns out we can do a self join with of the marks relation with itself. So, first of all let me put down the marks relation. So, marks id, let us say that this is a very simple marks relation on which we want to write this query. So, now the first thing is to pair up each mark with every mark which is higher. How do you do this? So, I am going to use a self join in SQL. So, select I will fill in the details of the select in little bit. From I want two copies of marks. Marks let us call it a marks d there are two copies of marks where a dot marks a is the person for whom we want to find the rank a dot marks. So, I want to find all the marks which are greater. So, less than b dot marks. Now, I want to find for each student how many others have got higher marks or how many distinct marks there are above this. So, I am going to group by a dot id. So, what do we have so far? I have paired up each mark like what here each student with every other student who has higher marks. So, now I want to count let us say how many distinct marks there are that are higher. How do I do this? I already have group by a dot id. So, now I can select id comma count b dot it is supposing I want distinct. So, I want to know how many distinct. So, this corresponds to the first of all the dense rank is how many distinct marks there are above sparse rank is how many marks there are above. So, depending on what I want I will add distinct or not. So, let us say here b dot marks and then I add a keyword distinct which is optional. If I have the distinct it is going to give me the dense rank. Because in this case one has nothing above it sorry plus one here the count would be 0 well there is a little bit more tricky stuff here. First of all the person with the highest mark would not have anything here. And the right way to do it would actually be to use a left outer joint which we have not yet covered it is coming up. But let us say that for the moment let us forget the top ranker the question was how do you find the tenth highest ranker right. So, we can deal with that coming up. So, now the highest ranker is going to be lost from here, but the second highest ranker that is somebody with 2 will be paired up with 2 will be paired up with both of these. And now if I say count distinct b dot mark these 2 have the same mark 99 distinct will remove duplicates and then that will give me 1 count is 1 and then plus 1 will give me 2. So, I have the rank of the second highest for whoever is second highest the rank will be 2 whoever is third highest the rank will be 3 correspondingly. Now, supposing I want the tenth highest I can have a having clause having count again distinct depending on which version I want b dot marks plus 1 equal to 10. So, for the dense part it is easy somebody will be 10 with the sparse thing there is always a possibility that nobody has the tenth rank there may be 2 ninth rankers and then the next one is 11. So, there is no tenth rankers in many of our entrance exams this is done. So, I think there are gaps in the ranks. So, that corresponds to the sparse but depending on your application you may want to use sparse or dense. I think I will stop on the queries here because I want to make some progress. This was a very interesting query I am sure many more have interesting query, but I do want to cover certain number of topics today. So, I am going to switch back to the lecture. So, we have tried writing some queries. Now, the next topic is method sub query which allows us to put sub queries where we expect either relation or we can put it inside of where clause or even inside a select clause. So, let us look at some examples. So, the first example is actually something we have seen before but it is just written in a different way this is just to keep life simple to introduce the syntax later we will see more meaningful usage. So, what is this example? Find courses offered in fall 2009 and in spring 2010. So, the outer query is finding courses which were offered in fall 2009 and that is an extra condition which is course ID in select course ID from section where semester spring 2010. So, this will first filter out courses to those which are offered in fall 2009 and additionally check that the course ID is in the result of this sub query. So, what is this sub query doing is getting a set of value and this condition in condition make sure this value is in this set. Similarly, I can say not in which is the opposite which is course offered in fall 2009 but not in 2010. So, earlier how did we did not in 2010 we said except or in oracle minus here we are using not in. Now, here is another slightly more complex example which is find the total number of distinct students who have taken course sections taught by a particular instructor. So, I want to know how many people were taught by the instructor do not double count if a particular student took two courses as an instructor count that student is only one. So, basically the outer query is or you would expect count select count distinct ID distinct avoid duplicate from takes relation but the key thing is to make sure that this particular student took a course taught by a particular teacher. Now, if I simply look at course ID you know many people may have taught a course this student may have taken a section taught by a not a section taught by ID 1 0 1 0 1 which is what we want. So, what we have done in this particular query is first of all find which all sections this teacher has taught how do you do that select course ID, second ID semester year from teachers where teachers dot ID equal to this value. So, this is giving me all the sections taught by this instructor and now this has become a sub query to this outer query which checks that the takes couple is included provided the course ID, second ID semester year is in this list. And then I find the count of distinct ID. So, this is just illustrating one more query with in. Now, many queries with in can be naturally replaced by different query using a join construct. So, quiz question 8 says above query can be rewritten using natural join aggregation group by order by there are many options I have kind of spoiled the suspense here by saying that you can rewrite it using join I decided in the interest of time to tell you how to rewrite it using join also. So, what would you do here this is the these are the sections taught by this person. Now, I want to join it with teacher and what is the join condition I could just do a natural join which will equate course ID, second ID semester year those are all the things which are common between teachers and takes is it we have to be careful here is these the only things common we have to go back to the schema and check. So, if you look at takes we need we have the ID of a student who has taken it in teachers we have an ID of a professor whose teaching. So, in fact this quiz question 8 is wrong I forgot to correct this is raised earlier the problem is if you do a natural join of takes and teachers the ID will get equated.  So, instead of natural join we can use join conditions explicitly or using. So, the query is actually simple let me flip back to the white board in just a second. So, the query is select count this distinct takes dot ID because I only want to count students from takes but I cannot do natural join because extra attributes would be equated. However, I can use the join using takes join teachers using the common attributes what are the common attributes we have course ID, section ID year semester. So, this is matching students who have taken courses which are offered by some teacher. So, we also need to narrow it down to where teachers dot ID equal to 1 0 1 0 1. So, that is the final query to find the number of distinct students taught by this particular teacher. So, the moral of this story is many queries which you could write using the in construct can actually be rewritten using the join. Now, this actually raises another interesting issue with respect to how to evaluate the query. We will come back to this later on but at this point I want to mention that a naive way of evaluating the first query is to go over every student and then evaluate the inner query. Let me flip back to the screen here. A naive way of evaluating this particular query is to take every row of takes and then evaluate this sub query completely every single time which is expensive. You can imagine if 1 0 1 0 1 has taught many courses we compute that whole set over and over again and each time we check whether this particular set of values is in this value I mean this couple of values rather is in this set here. That can be very expensive. So, naive evaluation of next set queries can be very expensive. Now, most databases have a good optimizer which can take a query like this and actually turn it into the join query which we saw and that will be more efficient. However, if you write more complex queries sometimes the optimizer is not clever enough it depends on which database you use. If you use PostgreSQL the optimizer is moderately clever but not overly so and it will handle this query very efficiently but somewhat more complex queries it may not do it very efficiently. In contrast if you use SQL server which actually has a really good optimizer it will take pretty much any query you nested query you throw at it and find out the best way of evaluating it. But if you are using PostgreSQL or MySQL my advice would be use nested queries with care. If you can translate it to a join query reasonably easily do that. Don't depend on the optimizer to do it for you because it may not. So, that was nested query. There is a little more stuff here on set comparison which is illustrated here. Find names of instructors which shall be greater than that of some many at least one instructor in the biology department. So, we can write it using a self join I am going to skip this for the sake of time and instead come down here. What do we have here? Select name from instructor where shall be greater than some and then a sub query. So, what we have done earlier is the sub query is linked using an in clause. Now we have replaced that in clause by the greater than some clause. So, this will succeed if there is at least one value here such that this condition is satisfied. You can also have greater than all which means it must be satisfied for every thing in here. So, now take this query and this one. Now, let me come back to this query. There is a quiz question here. So, let me first explain this query. This has instructors as p instructor as a self join. What is the join condition? p dot shall be greater than s dot shall be. We just saw something like this when we did ranking, but here our goal is not to count. We just want to make sure that there is at least one person in biology with a lesser salary. So, s dot department name is biology and t dot shall be greater than s dot shall be. That is our condition. So, we want these instructors. Now, the problem is that if you have a particular instructor with shall be say 100000 and there are five people in biology with shall be less than 100000. Every one of those five will match this and the name will appear five times the same person's name. So, what did we do? We said select distinct t dot name so that the name will appear exactly once. That is the query. Now, these two queries look to be the same and in fact, if you do not have duplicates at all there is no problem. They are exactly the same. Now, this is a very small variant of the previous query where we said let us say salary is greater than all select salaries from instructor where department name is biology. So, now what the only change is instead of greater than at least one person we want to say greater than all the people in biology. So, this is valid in SQL. It works. The question is can we rewrite these queries using aggregates? So, just think for a moment I want instructors whose salary is greater than all the salaries of instructors in biology. So, how do we rewrite this using aggregates? It should be intuitively clear that if it is greater than all of the salaries it must be greater than the max of the salary and vice versa. It is greater than the max. It is clearly greater than all. So, the basic idea is to find the max salary and then check if it is greater. So, that is in the next slide. Select name from instructor where salary greater than select max salary from instructor where department name is biology. I hope this query is clear. Now, I am going to rush through a few more constructs and then take a break for question. The next one is test for empty relation. You can have exist or not exist. Exist is if a relation is not empty, not exist is true if the relation is empty. P here denotes the empty fact. P is the Greek letter C denoting the empty fact. So, here is a query which says select course ID from section SS where some fall year 2009 and exist select star from section ST. So, note that I am naming this copy of section SS, this copy of section ST and I am doing something more here. Where semester equal to spring year is 2010 and here is one extra thing which we did not see before. What we are doing is this S over here section SS. I am using in the subquery and I am checking if S dot course ID equal to this copy of section is called t, t dot course ID. So, because I had section inside and outside I rename them otherwise I can use the original relation. So, what we are doing here is we are running a subquery which effectively takes as a parameter a value from the outside query. So, this value is think of it as a parameter which comes from the outer query and this kind of a situation is called a correlated subquery. Why correlated? Because there is a parameter from the outer query which is used in the inner query and this thing here S dot course ID is really from the outer query. This is called a correlation name or a correlation variable. This occurrence here in the subquery is called a correlation variable. So, how is such a query executed? Again the way all subqueries are executed is intuitively simple. We take the outer query and execute it ignoring the subquery initially and then check the subquery condition. So, once we have got a section here which satisfies all 2009 we will then check the subquery condition exists blah blah blah. So, now when we are looking at a particular section a particular instance a particular row in the section table S dot course ID is now meaningful. So, for that course ID I will execute this query. So, what we are checking is that particular course offered in spring 2010. If it is then a tuple will be generated here and select star outputs that tuple exists succeeds because a tuple is output. Supposing that particular course is not offered in spring 2010 then what is the result of this select query is the empty relation because there is no matching section here. And therefore, exists will fail and then this outer query will not output that particular course ID. So, I hope this correlated execution is clear that is the default semantics of subquery. Again as we saw there are many cases where we can rewrite the subquery to turn it into a joint. And usually that is faster than the subquery. So, if you have the choice go with the joint do not do a subquery. In this case it is actually very easy we saw how to do this query earlier using intersection that is perfectly fine you can use intersection you can actually do this particular case using joint also. So, just take this query and then similar query with spring 2010 and then if you do a natural joint on those only course IDs which appear in both the relations will appear in output. So, that is in this particular case is equivalent to intersection and intersect with respect to duplicate count it is not the same as intersection. But if we do a select distinct then it would be the same as intersection. Similarly you can have a not exist. Now somebody had asked me about division. Now this particular query illustrates division and let me explain the query first before I come back to the term division. The query says find all students who have taken all courses offered in the biology department. Now why would we care about all courses offered in the biology department. This is really a simplified version of a query which is actually very useful. Most universities have a set of courses which a student must have completed and then a set of electives out of which the student can complete any of say 5 electives or 10 electives. But there is a bunch of core courses which the student should have passed unless the student passes all the core courses the student is not given a degree. So, this is a simplified version of it which says all courses offered in the biology department. But you could replace this by if you had a relation which says these are the core courses you could substitute that relation here. So, that was a motivation. So, let us look at the query as shown here students have taken all courses offered in the biology department. So, what is going on here I am finding students where something is true. Now what is this something let us break it into pieces. The first sub query here is select course ID from course where department name equal to biology what is this doing it is finding all courses in the biology department. Now take the second sub query what is it doing select t dot course ID from takes as t where s dot id equal to t dot what is s here s is actually this outer thing from student. So, for a particular student I will take I have a particular s dot id. So, I am taking in effect I am doing a selection on takes to find all course IDs taken by this student. So, now what am I doing let us call this s q a and let us call this s q b s q a is all biology courses s q b for a given student is all the courses that the student has taken and my goal is to make sure the student has taken all the biology courses. So, before you understand the above query let us jump ahead here you can think of the above query as logically as select distinct this part is the same from student as that part is the same where s q b contains s q a. So, what is our goal here s q b is the set of all courses taken by the student s q a is all the biology courses what I want is students who have taken all biology courses. So, what I want is cases where s q b courses taken contains all the biology courses. Now, this is actually fairly nice simple syntax and weirdly enough it was part of the s q l language early on in the very first version of s q l in addition to you know things like in not in they had this contain sub query contain sub query for some reason this fell by the way side and was removed from s q l and nobody reintroduced it I do not know why this happened probably it made it easy for people to write very expensive query and rather than allow people to write queries it can bring the database to a halt because it is so expensive may be they removed it from the language but it is a very useful construct. But in its absence what you do can you get the same effect in fact you can and what this means is you could have equally well brought the database to its knees by writing a somewhat different query but people may be thought that this makes it too easy to write bad queries will make it a little bit harder. But if you did want to write this query here is how you do it so we have two levels of query this is where not exist select course ID from biology except this thing what is s q a all biology courses except the courses taken by the student what is this result going to be if this student took every single biology course this result s q a except s q b is going to be empty if the student missed even one biology course not going to be here and that course will show up in s q a except s q b and now we are checking not exist so if the student took every single biology course then this will be empty and not exist would return true and therefore the student would be output if the student missed even a single biology course that course would appear in this result this except that not exist would fail that student would not appear in the result so I hope that is clear. So this is how you would write this query in current generation s q a I do want to point out that you cannot write this query using equal all in its variant many people think that can we write this query where student the set of courses taken by the student equal to all set of courses taken by a set of all biology courses you cannot do this equal to all takes on its left hand side a single value not a set and sometimes people write where you know write other kinds of queries with equal to all which are totally meaningless in this context. So it is a little complex query to write but this is currently the only way to write it in SQL but what you should do is first of all think of writing this query in this form with contain and then rewrite it to this form to get the query that you want so that is how you think about it in SQL it turns out that this is the equivalent of the relation algebra division operation so the some reason there is a box line here ignore it so what is the division operator this it is a little hard to read but I will give an example which shows the division operators is given relations r with some schema capital R and s with some schema capital S the important thing is the schema of s must be a subset of r. So the second relation must have a subset of the attributes of the first relation the division operator says r divided by s is the largest relation p on what attributes on attributes in r minus the attribution that is all the remaining such that p cross product with s is subset of r. So think about this if you are doing division on real numbers how do you define the result of division you can define it using multiplication a divided by b is simply the number such that b times that number equal to a so that is what we are doing here p times the denominator equal to the numerator this is r divided by s. So p times s is equal to r is what we would do if we are doing real numbers if you are doing integers how would we define it we want integer division which means we want to truncate we want 9 by 4 integer division will give 2 not 2.25 but 2 we truncate. So what is the result of integer division it is the largest number such that p into say 9 by 4 that number into 4 is less than 9 what is that largest number it is 2 the same intuition applies for relational division it is the largest relation such that p cross product this is the Cartesian product now s is subset of r. So that is intuitively how this is defined now let me switch to white board and let us take an example with biology suppose I have biology courses I have a sub query to compute the biology courses let us say I have a set of those bio 101 bio 102 and bio 210 let us say those are the only courses in biology there are 3 courses now I have a student ID and the courses they have taken. So ID this is the takes relation let me get rid of the year semester and so on in fact I have to project it out I will remove those attributes and all that I want so this is course ID it is important before doing division to remove all extraneous attributes. So I am going to project takes on just the ones that I want so ID 20 comma course ID other attributes will cost trouble when I do the cross product I need to project it out on just the minimum attributes that I need. So let us say ID 25 has taken bio 101 ID 25 has also taken bio 210 but has not taken 102 whereas ID 37 has taken all the bio courses. So what should let us call this R and let us call this S what should the result of R divided by SB it should include 37 because 37 has taken all the bio courses. So the expected result is 37 now if I go back and so this is a relation with a single tuple in this case you could have more and a single column ID. So note the schema the result is ID course ID the schema of S is course ID. So this is R sorry this is capital S the schema this schema is capital R. So if I do R minus S is just ID that is what we saw in the definition so that is what I want here if I take 37 cross product with this what are the tuples I get 37 bio 101 that is present next one is 37 bio 102 is present 37 bio 210 it is present. So certainly 37 is in the result supposing 25 we wanted to check if 25 is in the result we take 25 101 sure it is in the input relation 25 102 sorry it is not there 25 cannot be in the result. So this is the answer and this is exactly the same thing which we did in the SQL query in a kind of roundabout way with the not exist you know sub query 1 except sub query 2. So we did it in a more convoluted way but there are many ways of actually doing it in SQL one way of doing it is actually using aggregates supposing I want to do the same thing what I can do is I can subset this takes relation only include courses in biology. So how do I do that it is actually is very simple using relational algebra you can write it in SQL equivalently supposing I do natural join this is step one. So what is the result of natural join these are the biology courses now in this case all the course of the biology but let us say I also had 25 with course chemistry 101 this could be in the R series. So what does R join S do it is going to output all of these because they are bio courses this one chemistry 101 will get eliminated because it is not here. So what we are left with is just IDs with bio courses now what I can do is count the number of bio courses taken by each student. So if I want to write this in SQL I can say select ID comma count course ID we can do distinct in case students can take the same course twice I need to put in distinct from takes natural join takes join I want to find biology courses so there is a relation here select course ID actually I could I could do natural join that is not a problem here let me stick with natural join takes natural join select course ID from course where department name equal to biology whatever I achieve so far I have taken a natural join and then I have done well I need to complete the query group by so I have taken a natural join of the takes relation with all the courses from biology and the natural join is on the course ID attribute that is the only common attribute and then I have found for each student how many distinct courses biology courses in this case the student has taken now how do I check if a student has taken all the biology courses think about it for a moment what I have done so far is I have got a count of the number of biology courses a student has taken number of distinct biology courses a student has taken how do I know a student has taken all the biology courses and answer is very simple I simply count how many biology courses there are and check if the counts are equal so I am kind of out of space here so I am going to write this here having count distinct course ID equal I want to know how many biology courses there are that is simple select count star from course where department name equal to biology so I have effectively done division in a two step process first for the right hand side I count how many couples there are then I am doing a join of the left hand side with the right hand side to eliminate tuples which are irrelevant in this case CH 101 was irrelevant I do not want to count CH 101 so the natural join eliminated all the irrelevant tuples and then I did a group by and did a count to find how many biology courses in this case the student had taken and then make sure that the counts match if the count for a particular student is less then the student has not completed all the biology courses can the count be greater well it cannot be because I am only counting biology courses which the student has taken it cannot be greater than the complete set of biology course so it turns out division can be done using aggregation like this but aggregation is not essential for division division can also be done using the basic relational algebra operation and in fact I am not going to spend time on it now but the slide on division operation which is at the end of chapter three slide shows you how to do it I am not going to spend time on it little complex but it shows you how to do it using only cross product set difference project that is it just using the basic relational algebra operators you can do


I welcome you, good weather or bad, hot or cold. We count on this crew to show up. This is the second in our series of discussions with DoD in the ongoing process for both soliciting and incorporating industry views and inputs into the process for bettering acquisition and saving money along the way, which is underway. I wanted to welcome not only you all, but those on the phone as well. I believe we have arranged that the phone is broadcast only this morning, so there should be substantially less interference from rustling papers, background noises, barking dogs, and other assorted interferences. None of us in this room, of course, heard any of that, but those who are actually trying to make sense out of this telephonically might appreciate that a lot. What that does mean is you will not be able to transmit orally any questions. If you do have comments or questions that you want to submit, you can email them to me at dberteau at csis.org, and I will read them and deal with them appropriately, submit them or ignore them as the case may be. I want to welcome Brett Lambert back. Brett, of course, is running this process for DoD. I believe that both Jim Thompson and Katrina McFarlane are en route somewhere on the bridge, probably dealing with the things we've all dealt with the last couple of mornings, and we'll probably get here in time before the thing's over. Without any further comments, Brett, over to you. Thanks again for everyone. I appreciate it. This is really just a status update to let you know where we are in the process and to re-emphasize the seriousness which the Secretary, the Deputy Secretary, who is in Guam today, and the Under Secretary, Dr. Carter, and Mr. Kindle, take this initiative and the inputs we're getting. And I have to say, first of all, thank you to industry we've received. I have a large stack here of some of it that's come through the Internet site that we posted, and a lot of it has come directly to me. And either way is great. They're being delivered to the five working groups. I can assure you that there have been several of the comments that are working their way through the internal processes now. And I just wanted to share with you a few, just a reminder of a few of the comments. We were at Farnborough last week with Dr. Carter, and he mentioned that there were going to be 16 issues raised in his deliberation, and that was a bit out of context, so I first want to clarify that. The 16 items were really the result of our initial rollout here on June 28th of the areas he wanted to consider. So let me just briefly reset ourselves on the objectives of this effort. The objectives, as stated by the Secretary and the Under Secretary, are to deliver the warfighter capability we need for the dollars we have. And this is the phrase I've been using, we need to do more without more. Get better buying power for the warfighter and the taxpayer. You don't have to write this down because I have handouts here. Restore affordability to defense goods and services. Improve defense industry productivity. That's where we all come in. Remove government impediments to leanness. Again, that's where we play a role. Avoid program turbulence. And maintain a vibrant and financially healthy defense industrial base. Those are our objectives in this initiative. I would also point out that there's a key paragraph in Dr. Carter's initiative from June 28th, which is in relation to the memo we plan to issue in September, mid-September, as a result of both our internal efforts and the inputs we're getting from industry. And I'll just read this paragraph. The guidance memorandum I plan to issue will require each of you, and this is a memo to the procurement professionals inside the building, will require each of you as you craft and execute the department's contracts in the coming years to scrutinize these terms to ensure that they do not contain inefficiencies or unneeded overhead. The guidance will give you specific features to examine and targets to hit in the pursuit of greater efficiency. The guidance will focus on getting better outcomes, not on bureaucratic structures. But it must also take note of where the government's processes and regulations contribute to inefficiency in our business relationships. We really want to stress that. The inputs we're getting from industry have been very helpful, but I often feel inside the building that we shoot ourselves in the foot, we do a study, and I'm looking at one from 1988, we reload and then we shoot ourselves in the other foot. This is largely, in my mind, we have to change internally, but we understand that and we need your help to give us real honest suggestions of what we can be doing better internally to make the process work better. So the 16 issues that Dr. Carter mentioned at Farm Bureau, the topic areas, again I'm not going to go through all of them, but you all, if you were here before, had the handouts. The 16 questions we're trying to answer have been divided into these five buckets that Jim and Katrina are supposed to talk about here in a second. I'll just, I'll read the headlines. Leveraging real competition, using proper contract vehicles. Now we're probably going to get a lot of questions about that. Using proper contract types for services. Aligning policy on profit and fee based on the circumstance. Sharing the benefits of cash flow, we understand how important that is. Targeting non-value added costs. Involving dynamic small businesses in defense. Rewarding excellence in supplier base. Adopting should cost and will cost management. Strengthening the acquisition workforce. Improving our audit system. Improving, not increasing. Mandating affordability as a requirement. Stabilizing production rates. Eliminating redundancy within the war fighting portfolios. Establishing senior managers for procurement of services. And protecting our technology base. Those are the areas that we're trying desperately to work on. And when we release this directive in mid-September, it is our intent, as I've said before, this is a marathon. We didn't get here overnight. We're not going to get out of this overnight. We had a permissive budget environment where it covered a multitude of sins on both industry and government side. We know that. When we had program problems, the pressure we applied to the bleeding was money. We're no longer going to be able to do that. So we need, all of us need to step up our games, both in industry and in the department. And we recognize that. And so we're hoping that this initiative will allow us to have this interchange and interaction with industry to help us do our job better. So for those of you who don't have the June 28th memo, and it was the two of the three attachments to that memo, which include the 16 bullets, it's Ash Carter's 16 items that he mentioned in Farmboro and elsewhere, that is available on the DOD website. If you look at the press conference that was held at 3 p.m. on June 28th, there's a link that has the memo and the attachments there. So you can get all 16 of those items from that. I can just talk a little bit about the process. I'm sorry, Jim and Katrina obviously are here somewhere. But I'll talk a little bit about the internal process for people who want to know. Again, our goal here is to be as transparent as possible with industry. As I keep reminding folks, we don't, in the building, despite spending about a billion and a half dollars a day, we don't make anything. We rely on industry to provide the parts and the components to make things and to make them work and then sustain them for 20 or 30 years. So industry is a critical component. And I think Dr. Carter, Secretary Lan and the Secretary himself have demonstrated their understanding of that fact by opening lines of communication both through my office and their offices directly. So we need inputs. We need cooperation. We need to better understand what you're, you know, a lot of our program officers, as you know, have never read a P&L. So we're trying to educate. We have a lot of education to do on the inside, but we're going to need work. This is going to have to be a team effort here and we understand the importance of industry. So as I've said in my office over and over again, I need insight before we dictate oversight. So we need to make sure we are not acting on matters that have unintended consequences. We all know the story of the automobile industry and pushing down margins in the automobile industry which just went to the second and third tiers and we all know how that ended up. So we're going to avoid that in this effort. We are conscious of the effects that any decisions we make have on both not just the primes but the second and third tiers. So the five groups that Jim and Katrina are heading have been working tirelessly, although is Professor here? Yes, he's here. Where is he? Steve Schooner. Okay, so I know you're the skeptic. So good, good. So as a result of your skepticism from the last meeting, I've asked Eugene who's back there somewhere who's just joined my staff, Dr. Gould. We're going to convene a session of academics outside who aren't related to individual companies. They may have contracts, but these are 15, I think Eugene you came up with, 15 of the best and brightest from around the country of people who are concerned about reform, who have been through the 88 reform, who like you have skepticism and we've scheduled or we're trying to schedule something on the 10th and we're in August and we're hoping that you bring your skepticism. We need to know that. I think that the department will benefit from that kind of interchange and we just haven't had that interaction and we need to be skeptical because again, my fear is the unintended consequences. If we make these actions, if we're not doing it in an appropriate and effective way, we could make it worse, not better. So now that the choices that are on the table are we either get this right between ourselves and industry or somebody else tells us what to do or the European example where you just say you're going to take 5% out of every program, which would be devastating. So this is our opportunity to get it right and that's how we're viewing it inside the building is to just realize that we are where we are. We had a double digit growth environment at 46%, I think 46% over the last decade, an increase in defense money. It covered a lot of sins, like I said, on both sides and now we just need to correct that and we need to get smarter about how we operate and that's what this initiative is about, is not taking money away from contractors, it's not taking money away from the services. I think one of the unique features of this effort is that all the efficiency savings that will be retained will be retained inside the services. This is not a budget drill, as we've said, this is not trying to reduce them. It's trying to find greater efficiency inside of what we're doing. So for those of you who are skeptics, I encourage that. It's a good thing to be skeptics. I started this process out, I have to say, as being somewhat pessimistic, but the more I have become engaged with the senior leadership, particularly Dr. Carter and Mr. Kendall, the more optimistic I am about their real desire to have benefits and the comments we have gotten back from industry have been extremely positive. They're telling us what we need to do to change and we are spinning that out across the five working groups and they are engaging them and I would hazard to say that you will see a lot of your comments when you put them in, you'll see a lot of your comments reflected in the directive that Dr. Carter issues in September. Let me, Brad, if I can, exercise the moderator's prerogative and ask a long and complicated, actually it's a simple question, but it is a long and complicated lead-in, if you will. The chart that you laid out last week that showed the five issue groups and your industry working group on one side and the service working group on the other side with Jim and Katrina and the executive director role sort of implied that the issue groups would be taking input and wrestling with issues both from outside, that is, from industry and the inputs that you've alluded to there, as well as from inside the department and then they would somehow adjudicate, moderate, integrate, et cetera, those and the executive directors themselves had some of that integrating function because as was noted in some of the questions last week there are issues that clearly span across the issue groups and would touch more than one, in some cases three or four, maybe even all five of the issue groups. Is that an accurate reflection of what's happening and how is that done in real time? Right, well, so we are getting inputs and we have, if you go to our website, you'll see the form and some people are filling out the forms, others are just deciding to send me directly suggestions. Either one is fine. We then, the process is we categorize and bucket them and then I go to Jim and Katrina and talk about which buckets they should go into and as you've said, some of the suggestions we've been getting some are coming from associations, some are coming from individual companies and some are coming from individuals and some are very specific. DFAR 9.44 needs to be checked, which is fine and then we determine which of the organizations, which buckets they need to go into and then as they do their meetings where my office participates in their internal meetings to make sure that industry is being represented in those discussions and then when they're out briefed, the process is when they're out briefed to Mr. Kendall and Dr. Carter and eventually Deputy Secretary Lynn that the industry inputs are reflected. Now I will say that some of the industry inputs we've received are what I would call the part of the back end of the marathon. You know, I'm trying to take mile post one. What can we change immediately that needs to change or needs to, where we have effect? It doesn't mean that the other suggestions, I'd probably say half of the suggestions we've received so far have been more systemic changes that we need to make internally to the department and the process. Those are not going to be dismissed. There will be a lot of follow on effort. Dr. Carter's concept, which I think is solid, is that we will make these recommendations to procurement executives in September on new programs, new program starts. This is how you should be looking at these things as you do new programs. But then toward the end of the year, the calendar year, he'll ask for accountability. I think one of our groups for, I think is the group, has the largest challenge of anyone which is documenting accountability. How do we know we've improved anything? And that's a huge challenge internal to the building. So there will be a follow on effort to this. This is, again, it's a marathon, not a sprint. So the sprint will be the September guidance, but then there will be a significant amount of follow on from that effort. Let me ask a follow on question then. The service palms are essentially due to OSD, I believe at the end of this week, 30th of July. Yeah, I have them here. You have them? Okay, good. And I know from conversations with the military departments they've been wrestling heavily with implementing and reflecting in their own palms. The initiatives that the Secretary gave him in his guidance of June 4th, which implemented his Eisenhower speech back in May. It's obviously fair to say that those inputs will not reflect the deliberations you just described since the issue groups are just getting started and have just wrestled through that. So they've come to their own conclusions on this broader set of, I believe the Gates memo refers to it as efficiencies initiative, plural efficiencies. I tend to think of it as efficiency as an overall objective, so I haven't figured out whether I have a singular or plural in my own mind here. But how do you meld those two together over the next month and a half? That is the review of the service palms and their initiatives with the work of the issue groups as it folds forward into DOD. Well, again, I think this is part of the effort that we're engaged with industry on is about looking forward. So we're locking down palm as you've indicated, but when Dr. Carter has articulated specific programs where he would like to see whatever initiatives come out in mid-September, whatever we indicate, or guidance that he gives to procurement officials, and this will again be a document that's about guidance. It will be about new programs, new contract activities, so you can imagine the programs in mind. This is not about trying to correct past mistakes, although we will be guided by those mistakes and the anecdotes and a lot of the, you know, the documents and input we're getting are you guys really messed up and you could have saved a lot of money if you had just done this. Well, that's helpful, but it's like writing a report that's what I often find in my office we do, which is it may be of historical interest that. So we're looking forward. So we're looking at what programs are we, and we have some big programs coming up. I know everyone says we have no major program initiatives, but we have Ohio class replacement. We have new blocks on JSA. There are a lot of programs that are coming up where we can apply this discipline, this new effort on efficiencies, and that's what we'll be concentrating on. So it's not specifically, again, I go back to it's not a budget drill. It's about us trying to be better at what we do and getting industry to be aligned in rewards and incentives with what we do and getting everyone rowing in the same direction because if we don't row in the same direction, somebody's going to tell us what to do, and we want to avoid that. All right. Let me open the floor up for questions now. Just to remind you of our procedure, wait for the microphone. Stand up, speak into the microphone, identify yourself and your affiliation, and then you may offer your question, comment, or reaction. All right. I know that Ralph Nash is championing at the bit already. So while the microphone is winding, I'll mention the 1988 study to which Mr. Lambert referred to earlier. There's actually a couple of people in this room who worked on it. This was an incentivizing industry for better performance, and you'll recognize some of the names of the folks who wrote that work. I have it here. It's Mimeographed. It's really impressive. It is not available electronically, except in scanned PDF file, but it was Bob Moore, Chuck Henry, Tony Malayo, Bill Federaccio, and one other whose name I don't recall right now. So the microphone was not on. Let me just introduce. So these are the House 14th Street. Good. No, sorry. So we're all, I don't know, how many people here are without power? So yeah. So I spent two days now without power. So when I was trying to get ready for this last night, my kids helped me. I just wanted to show a prop here. I was trying to tell them what I was going to talk about today. They helped me. This is me. This is what I'm supposed to talk about. And these are the shiny items that we're supposed to. This is the good news thing at the end of the day. So anyway, it goes on and on. But this is all we could do at Candle. But I appreciate you guys for making it. And I won't say that this is their second full-time job anymore. So you corrected me of that. But we just went over kind of the process where we are. If you would just spend just a second talking about kind of individually, the five groups and the process. And then we'll just go to questions. So it's been, I guess, a couple weeks. And we have approximately 30 full-time people who have reached into additional staff inside of the services and agencies. And we are working very diligently on issues. In fact, on Friday, we were able to take one issue forward to Dr. Carter to expressly ask, is this the granularity you need for your ability to make the appropriate decisions? And he said yes. So now we have a template so we know what the product will look like. We have a lot of very, very good input coming in. And I have to say we're very fortunate that these folks that we have on this team are experienced and know exactly how to collect data and how to express it and to also formulate good alternatives that we can deliberate on. And that is essentially where we're at. We've got a lot of work in front of us. We've just been sitting in the car over here. And by the way, I don't envy your traffic. I thought it was bad on the other side of the water. My goodness. But on the ride over here, we went through the calendar realizing that there's pretty much no airspace left on it. Trying to find an opportunity to add some additional interest items to the table. And that's pretty much a top level unless you have anything you'd like to add. Maybe just to add a couple of quick things. Dr. Carter had signed out a letter back on 28 June that had a number of things in it that really gives us to be blind and really running start. There are a number of things in there that we're starting with to take a look at and to flesh out. So we're not starting from a completely clean sheet of paper. We've got a number of things that he's already articulated that he would like our group to take a look at. So we're doing that. The other thing just as Katrina said, you know our op tempo here is actually she and I meet about either three or four times a week depending on, pretty much every day, depending on whether or not we're having a team leadership meeting and then we meet with them extensively two to three hours each week to go through where they're at. So that's really the tempo we're on. So we're already off and running in the last two weeks to get the ideas fleshed out. The other thing that I would encourage you all to do if Brett hasn't already emphasized this, we need fact-based, data-based ideas. The time for anecdotes and those kind of things have been made clear to Ms. McFarland and I from Dr. Carter. We're not doing that for this drill. We really need fact-based information and ideas and adjustments in the policies, procedures, and practices and behaviors. So that's really what our team is focused on, is getting the facts right, getting the data to support those facts and then making the recommended adjustments and policies and procedures and practices and the things that we want to carry forward. So I would just ask, as you think about submitting ideas or adjustments in our thinking, that you do it with as much fact-based information as you can. That would certainly help us. As I said earlier, we have a large mix. I have yet to get the comment that I was expecting, which is if you just sell off the aliens in Area 51, you could reduce the deficit. So we're getting actually better things than that. But to Jim's point, the more fact-based it can be, the better it is for us and the more helpful it is and the more useful it will be in our deliberations. And then maybe one thing worth stating again, as Dr. Carter has made it clear to our teams that this is not a budget drill. The effort that we are in is really looking at those changes and adjustments and the way we do business and then we'll look to have those ideas and those initiatives undergird and support, looking at adjusting budgets from tail to tooth as we go forward. But our job is really not to go find specifically X numbers of billions of dollars. Our job is to really look at again, what are those policies, procedures, maybe even ledge props that we need to consider going forward. So it just certainly keeps our mind off of the other piece and keeps our mind very much on what we can really get our hands on and adjust and change. Yeah, I just, I had only one thing. I've been in all these meetings with these guys and the undersecretary in the DEBs here. This is not about how we find money within programs to cut and that's, you know, this is about how we do things better. And I just can't stress that enough. This is about how we do things that we are doing now, how we just do them better. One of the most remarkable things that the teams have discovered is that pretty much all of the guidance and policy exists already and has gone through the process and has been approved and has been legislated and has been authorized. The fact is we do not necessarily comply with it. We may comply with it at a margin. We may comply with it at the intent level, but we have not investigated the full details. Most of these activities that we're looking at have a spectrum of opportunity that you can take advantage of as a tool when you're out there working together. And so one of the things that we're targeting is where within that spectrum, you know, from one end to another, is the best place for us to look at doing the business better. So understanding the intent of the guidance that's out there and understanding the policies and what they were written and inscribed to be, what are we doing that we can do better to effectively meet that intent rather than taking the most expedient or the simplest route out. And so most of what we have found, in fact the detailed example that I was referencing earlier with the granularity it had, was I think it was 10 policies and governance and laws that existed today that would allow or already were in place to do this work. So it's not, you know, I hate to say this from my position, but rocket science, but it is essentially really understanding what the intent of these policies and procedures were in implementing them appropriate to the work that's in hand rather than taking the most expedient route forward. Mr. Nash, do you still have a question or has it now been adequately addressed? Do you still have the mic? Okay. Okay. All right. I'm an old academic skeptic. I started out on the... I started out on the... Yeah, this is a young one here. Which one? I've been through it in my newsletter. I call it a memory loss because if there ever was a program that should have been done for a competitive prototyping program, that was it. And you ran big paper competitions. If you've done that program right, you'd be flying tankers now down on Edwards testing. And I just wrote up an army program where they did competitive prototyping 1,000, they won 5,000 vehicles. They're going to do competitive prototyping for the test articles. And then they're going to award a single contract for 1,000. They're not going to run competition. Paper competitions don't like real competition between real products. That's what drives me up the wall if you want it. You know, there's a lot of success out there. But it's all forgotten. Of course, I won't comment on the ongoing efforts. But when you said locked into this initiative, into these... I'm confused as to what you meant by that. Well, there are some things that are one that lives. Yeah. Do you don't think they're broad enough? Well, requirements is any model that lives. Well, requirements is... Well, it's part of affordability. Well, it's part of affordability. But anyway, I just was... These are... These are all... Alan Shavotkin asked the question last time. These are all things we've been talking about for 50 years. There's nothing new here. And you're not going to improve the system by playing around with types of contracts. You're going to do more harm than good with types of contracts.  I don't know what you're going to do about profit, but I don't think that's a big issue.  As I said, you like paper competitions. You like... You came out on the Internet last week. It cost Airbus $75,000 just to print the proposals. Printing costs. Euros or dollars? I don't know what it was. It said dollars, but I asked the second question. It might have been Euros. But it's all... Well, I hope you'll join us for the lunch on the 10th because this is the kind of feedback we have. But I do think that there's a difference in that... I take your point on competitive prototyping because that's come up in a bunch of the suggestions we've had from industry. So that I think is... and going back and looking and I recall the jail study that was done on successful programs, whether Sidewinder or A10, and... you know, again for this exercise. I mean, I think one of the issues we face and I think these guys particularly have been faced with is that we understand everyone agrees on industry and inside the building the general parameters of what needs to change. And so when there's an initiative like this, there is an incredible amount of pressure on that individual initiative to solve all the world's problems and to make things right. But we are not starting with a clean slate or in my old world, a clean whiteboard. We have to work with legacy systems and we have to think forward, not backward. And so we... this initiative is not going to make everyone happy. In fact, it's probably going to make some people upset. But we are trying to do what's in the interest of the department and I think the taxpayer and if we can get a 10% effective rate here, I will look at it as I did in my previous life in industry is this is a turn organization and you don't turn organizations quickly. It takes time. And so we just have to make the right steps and what we have to ensure and that's where we're going to need people like you, your help is that we're not doing things that have unintended consequences that make things worse rather than better. But that's why we're trying to have this dialogue and this outreach. If I could offer, there was something that I think is important to answer to that question. One of the things that we have been challenged in our teams are is how do you take effectively what is obviously information that's been made available to the department for a long time and make sure it's implemented. And that's the question of follow through. And what we've been working on is a path that we're calling it enablers. What are your steps that you take to enable the action and the intended consequences that you're desiring to be effectively put into the process and carried through. And we haven't come up with a final answer to it but we've postured things such as we're going to take programs go to the services pick specific programs and literally walk through the entire balance of the life cycle on that program with these initiatives imposed I'm using a bad term but imposed on them. There's several effects that that brings it brings education mentoring, it shows the process and how it's done it allows for people to understand and open forum critical thinking it's intended to be done for all the services so there's there's a let's be plain speaking here we all understand the resources the human resources and the government in a specific this time frame when we're going to go through an economic climate that we have that we're dealing with we have to have our side grow our government side grow so we're a better partner and a better customer to industry so that education process is critical in order to have success and we have a youthful community and we have a lot of folks that are going to have to go through that process of learning by hands on and so there is a natural progression of tasks that have to happen post this initiative that follow through on the desired outcomes that we're tabling that we're going to engage in just to have a sense of what we're intending to do I have on my list of questions I've got Bill, I've got Gordon and I've got Steve so let's start Bill here and if you want to catch my eye for a question I'll put you on the list here yeah there's you know it's like any household when the budget gets tighter everyone looks for coupons so everyone's out there doing initiatives our initiative is not focused on a dollar amount it's focused on this initiative is on the two to three percent of what we spend in the acquisition from an AT&L perspective a year and trying to wring out efficiencies there so there are ongoing efficiencies our effort is not related to any sizing in terms of the overall and I know that was mentioned in troop strength we're not doing that we're solely focused on about four hundred billion dollars a year or so that we spend on procurement on investment accounts and what we do with that I don't think we've had any guidance to look at anything else there are other people looking at other things that this is our focus and we were all asked to write checks for one hundred billion and not date them and we've given them in to the secretaries and no no we're not being asked it we haven't been asked to hit a specific target we've just asked we've been asked a very simple question which is how do we do what we're doing now better how do we get more without more and there is no dollar amount tied to it again this is not a budget drill we're not trying to find numbers we're trying to find efficiency I will add that this is just my personal comment on this would be that I think many in the building would say that it's going to be hard to find those kind of numbers unless we fundamentally go back and relook at notwithstanding that the comments earlier about we've been there before but without fundamentally going back and asking ourselves where can we get back to first principles that are already there we're not going to be able to achieve numbers anyway that's really being pressed down on the services so I think even though the services and everyone is out looking apart from this effort where they can find specific savings this effort instead is really looked at what do we need to do to really adjust again the practices and the behaviors and procedures that we can do we can affect those things we're going to have to go in and dial them in very specifically to get back to first principles and I'll go back as an example on prototyping there's probably no other person that I know in my sphere that is passionate about prototyping in general competitive prototyping is a very specific item in prototyping but to me that's a first principle argument it's first principles so our question should be to ourselves and we're asking this among our teams where is it that we've allowed ourselves not to prototype whether it's competitive prototyping or whether it's prototyping within industry government teams it's just a fundamental first principle so we need to go back and ask ourselves why is it that we don't see that practiced as much whether it's a radar or whether it's an aircraft or an unmanned aircraft why is it we don't see as much of that today where have we become unpracticed at that is a rhetorical question we want to ask ourselves and to me those are the first principle things we're asking our teams to come back and look at so that regardless of what the number is going to be in the future that we at least have an opportunity to pursue those numbers but without some fundamental adjustments in the way we're looking at our first principles in engineering and first principles in program management I think we're going to be hard pressed to get there In the back of the room on my right Court Sterling with AIA a number of the things that you're looking at are internal to the department processes that you can fix there's no doubt about that but there's still a lot of things that are imposed upon the department and on industry by congress there's a tremendous number of those government unique clauses we've had staff tell us they don't care about the cost of them it's about the policy the principle to what extent are you or is the secretary willing to take on congress on these issues I think of things such as the 3% tax the department estimated it cost it 17 billion over a 5 year period whether that number is accurate or not we know it's several billion but there's a number of other that are really clauses that congress has imposed on DOD has imposed on industry that are driving up costs is the secretary willing to take on the congress on this issue really push for these changes that would allow us to reduce our costs by reducing these mandates well I'd say we try never to use the word take on in congress in the same sentence ever I don't think it's a I just don't think it's a question of taking on somebody we need to do the right thing if we do the right thing I think reason people will respond in kind the direction we've been given is to do the right thing do what makes sense if I were running a business I wouldn't run it the way we're running our business I'd change it that's what we've been asked to do we're trying to change it it's going to take cooperation of our suppliers and our industrial base and it's going to take cooperation of the people who pay the bill which is ultimately the congress but we need to focus on doing the right thing not trying to please people and I think if anything the secretary has demonstrated his willingness to do the right thing and not necessarily worry about the the political fallout from that Steve if you would just pass it over to Bill Tuttle when you're finished that would be fine thanks I just want to briefly start with Katrina's point because I think it's so tremendously important there's reform and then there's implementation and realistically if you want to go back and look at the data and the experience DOD has either been gutting or under investing in its acquisition workforce since 1989 at the end of the day that's where a lot of your efficiencies come from if you're talking about how these things are going to change there's a lot of people who think that Shae Asad Frank Anderson have been partners in all this and at the end of the day if you're really going to get any kind of improvements you're going to have to do it with people implementing existing policies and you can rewrite the rules all you want but that's all about people you don't have the people and it's going to take your generation to fix it so let's be honest about that you know Brett I appreciate the fact that it's easy to say that I was the skeptic at the last meeting and I'm ecstatic that Eugene's going to host the academics in another week but as I mentioned to Matisse when I walked in we walked out of here on Thursday and a significant number of the people in this room all flew to Florida the National Contract Management Association World Congress and whether you like it or not this was what we talked about all week and every discussion we had on everything came back to this and so let me just offer you a few observations that we kept hearing time and time again last week I think the first one you keep telling us that you want fact based input but you keep talking about fixed price contracts and frankly what we're hearing is profit caps there are generations of empirical evidence that demonstrate that with fixed price contracts by inappropriate in inappropriate situations are failure and litigation and it's not what you want we can go all the way back to the renegotiation act and the rationales that killed the renegotiation act but the one good thing about the renegotiation act was it reflected that if you made too much profit on one contract whatever the hell that made if you lost money on another contract it was the contractors overall profitability that mattered profit caps are doomed to fail and if you're going that route then we're not really having an intelligent conversation second there's a lot of anxiety about the note in the Carter memo that talks about cash flow and sharing the benefits of cash flow and I think it's imperative that you all keep in mind and I think you know this that you can't squeeze the contractors on the time value of money there is a significant trade off in the industry and Wall Street has reflected it for years defense contractors make relatively less profit they take in many ways more in different types of risk but they do that because they have consistent reliable cash flow if you try to squeeze that as a saving then you're upsetting the apple cart in extremely significant ways just a couple other quick things and I think court already opened the door on this if you really want to talk about more competition if you really want to talk about getting the inefficiencies out of the system let's talk about far part 19 far part 25 and far part 37 but the reality is you're not going to do it with Congress and you're not going to win but that's where the game becomes inefficient that's where the lion's share of your regulation your training your implementation and your workforce energy gets squandered that's where you can get major improvements you're just not going to get them the last thing I just want to go back to the same thing that I started with when we talked on I think it was the 15th it's all about requirements you're going to have to decide what you don't want to pay for anymore and that's where the real savings are going to come from that's a leadership issue it's at the highest levels of the defense department in the Congress but the most frustrating thing about that is the requirements decision what you can do without is not an acquisition function and you can squeeze acquisition all you want but people are going to have to make really hard decisions about what you can live without and that's where the savings are going to come from I agree with your last point this is sort of a follow on question accidentally but that's all right I'm sorry bill title my affiliation today I guess is DAU Board of Visitors because I'm really my question is have you got DAU involved in this in terms of because the message out that you want to send Ms. McFarland to the workforce is use the authority you have and use it intelligently and that's a continual training problem but I think that will be important because over 100,000 a year that DAU reaches out and touches and this is a marvelous way to transmit the message of the need for efficiencies what it means keep it up to date so if you're doing that you've answered my question thanks I just say they've been intimately involved the whole point of this when we rolled out this initiative the Secretary gave his speech in the great state of Kansas and then when Dr. Carter rolled out this initiative on the 28th we made a point of coming here to CSI as first to reach out to industry they were gracious hosts and then we went directly from there to into you to meet with I think I don't know how many hundreds of our contract professionals to give them the same message that this was coming and then the Secretary himself met with Dr. Carter for a press release that afternoon so they are critical we need a level playing field I've seen it on the other side in my industry experience we have to step up our game in terms of our negotiating capabilities and knowledge we're the buyers and we just need to do a better job of that and I think everyone is very aware inside the building that we need to do better any other questions as we look out over the room here I've gotten none in by email either so you guys want to wrap up your concluding remarks and then by the way traffic is just as bad going the other way just different people those who have given up and gone back on the way now I just say you know I think I think there's I don't mean to characterize you as a skeptic but there's skepticism about this and I understand that I think in my personal role I've been in industry so I understand what these initiatives are this initiative is about first principles how do we do things better with what we have and it's not about cutting budgets it's not about saying we want fewer of this and that it's about how do we all get on the same page and start rowing in the same direction industry and the government it's going to be frankly a lot more work on our end inside the building I think I find that industry tends to respond when we give them clear direction we just need to give them clear direction of where we're going and I think that will hopefully be part of these directives and this initiative that comes out and these guys are working on their direction but the direction is not a fate of complete the directive that comes out in September will not be the end of it it will be the beginning of it and it's a long process and it's going to take years and it'll go into the you know I know the palms close all of that's closed this is going to go on for a long time it's just we're trying to course correct here for a new world that we find ourselves in as Dr. Carter says we're not in the that we had before and we're certainly not in the 46% or compounded double-digit annual growth that we've had in the last decade we're somewhere in between and we're trying to sort our way through that and part of it part of that will be being more efficient about the way we buy things so that we can ensure and we can say with a straight face to the taxpayer that we're spending their dollars wisely on behalf of CSIS I want to thank you all for coming for participating three of this series will be in mid-August we'll be sending out a save the date announcement shortly I think that power should be back on at most PEPCO homes by mid-August they may have gone off again but the CSIS will also continue to wrestle with ways to help DOD as they overcome the skepticism and the weight of 50 years of success here so thank you all and I appreciate your continued support