Good morning. Shall we get started? So, hope all of you had a chance yesterday to work on those SQL queries and maybe during the end of the lab session today we can discuss those also. Today, we are going to move on to ER modeling. Now, you will notice by its absence that we have not really covered relational algebra and relational calculator here. In the final, you know, December offering we may cover relational algebra, but time is fairly short here and my assumption is that most of you would already know that material. So, what is the goal of ER modeling? Why do we even bother to do it? If you take somebody who has not worked on designing any database or schema and ask them to go ahead and design something and they know about tables, their first thing that they do is start designing tables. They start designing the attributes, the primary keys, the foreign keys, they go straight into it. The problem with this is that you do not get a big picture of the system. What all does the system do? What are the different things in the system? You want the big picture before you dive into the details and what the ER modeling approach does is it lets you view things at a higher level of abstraction before you get into the details. Since we use relational data pieces, eventually we have to create the relations, but let us not be in a big hurry to do that. So, what we want is a model of whatever enterprise we are trying to create a database for and the question is how do we model this? As most of you are already aware, the ER approach models things in that enterprise in two ways. One is as entities and the other is relationships and sometimes there is a little bit of confusion about whether something should be an entity or a relationship, but most of the times it is clear. Using this diagrammatic notation in particular, one of the big benefits of the ER modeling is the diagrammatic notation. We can give a high level view of the enterprise and then convert it later. Now, we are going to study two things in the first lecture today. One is the concepts of ER modeling which are uniform across all the different notations. The second is we are going to look at ER diagram notation. We are going to use a specific notation, which if you have ever used either the database system concepts book or one of the other widely used books like Navathi or Ramakrishna. You will notice that the diagrammatic notation we use in this sixth edition of the database concepts and in the lecture today is a little bit different from the earlier one. So, you might ask why this switch? The reason is ER models in the original chain notation matches what textbooks have been using for many years. But in the industry, people have moved on to other notations. In particular, UML, the universal modeling or unified modeling language is very widely used and UML also has a class diagram, which is actually very close to ER diagrams and that uses a different notation and most of the tools on the market do use UML. So, we decided that we might as well move to reflect reality in the market. That is one reason. The other reason is that the original notation was a little more cumbersome. It occupied more screen space to represent some amount of information and the UML ish notation that we use is more compact. Now, why did not we use UML itself? UML does not really support ER modeling per se. It is close, but not the same and the benefits of ER modeling are still significant, which is why we do not use UML itself, but we use a notation very close to UML. So, that is a brief overview of what we will be doing in the first session, introducing you to the concepts very quickly, which you have not of familiar with and then the notation, which may be a little bit new. In the second session today, after the tea break, we will have a tutorial, which involves doing some designs hands on. So, we will let you spend a little bit of time working on the design and then we will discuss the design issues and we will do this for at least two maybe if time permits, we will do more than that. So, because ER modeling is very hands on exercise, unless you do it, if you just see the slides it does not help. So, it is really nice to actually work on some real may be not fully real, but realistic examples. So, let us get started this is an overview of this chapter. So, as I said already a database can be modeled as a collection of entities and then relationships between entities and as you know that know and entity generally reflects something, which is could be concrete could be abstract, but it is something for example, a person car or even a course. A course like this one is an abstract entity, it does not have any physical existence, except for the fact that several documents say that there is a course, you are physically here, but the course is an abstract thing, but it is still an entity, because it has attributes it can be described, although it is not concrete it is an entity. And then you have attributes for entities as we just discussed, people have names, courses have titles and so on. Now, an entity is a single thing and single object, an entity set is a set of entities of the same type, which have the same properties. So, from our university design here are two entity sets, one is instructor and the other is student, we have simplified it from the thing, which you have been using to conserve space, here we just have an ID and a name, the student ID and a student name, we do not have anything else here. Of course, in the real design there are a lot more attributes. Then we have relationships between entities, again a relationship is a connection between a specific pair or set of entities. So, in this case here is a student identified by this ID and here is an instructor with this ID. Now, these two are related in some way and in this case the relationship is that this person is the advisor of this student. So, that is the relationship. And just like we had entity sets, we have relationship sets, which are sets of relationships of the same type, that is between the same set of entities and having the same meaning. Now, two people can be related in different ways, in this case there is an advisor relationship, maybe there is also some other relationship, parent of something else, someone may be both a parent and an advisor, but they are two different relationship sets, because they mean different things. Although, the relationships are between the same pair of entities, the relationships belong to different relationship sets, because their meaning is different. So, we can show this diagrammatically for the case of a binary relationship by drawing lines between the associated entities. So, each of these lines here is a relationship and the set of lines is a relationship set advisor in this case. Any questions? And again, as you know, not only can entities have attributes, relationships can also have attributes. For example, here this is this records, when was the last time perhaps this student met this advisor, students are supposed to meet advisors. So, we can record that time as a attribute of the relationship. You have a microphone. Diagram on this slide and previous slide. Yeah, they are not ER diagrams. No, no, these are instance diagrams. Yes. So, why not same approach is used in previous case also, because attribute could be null, like, now what I use is that this approach represents relationship instance by some small square. Okay. So, why do this? That would have been consistent. I mean, why is there a specific This is not a formal notation. This is just something to help with intuition. It is not a formally defined notation. So, No, but it helps in understanding the. Yes, there are different approaches. But previous one and this could be the same. So, this notation has not changed from the previous edition. We have not changed. No, no, Edison, I am not saying previous slide. Okay. So, here we do not have any attribute for the relationship. Yes, we do not have attributes. Here we could be used for previous setup. You mean with the oval there? Yes. Yes. Yes, that is this is inconsistent between. You are right. We could change that certainly. I hope you understood the point he was making and I agree with him. Okay. So, binary relationships are the most common, which we can represent using lines. And relationships between multiple entity sets, which is more than binary, ternary, quaternary are relatively rare. They do exist. We will see some examples. But you have to think twice before you create a non-binary relationship to know if you are doing the right thing, because most of the time it is not. Most of the time the right thing is to use binary. We will see examples later. Now, attributes in our example so far have been simple. They are just values. But ER models in general allow attributes to have structure. So, you can distinguish between simple and composite attributes. There is also a notion of a domain for an attribute, which is a set of permitted values. We are not actually going to focus on this in the ER modeling. When we work on the relational schema, then we need to worry about the type system and the set of allowed values. Here we are not going to worry so much about it. Okay. Coming back, we have simple and composite, single versus multi-valued and derived attributes. So, I think we have an example here. Name, split as first name, middle initial, last name is a composite attribute. So, name is composite. Similarly, address has two levels. Street, city, state, postal code, maybe a country also here. And then street address itself is broken up into several pieces. Now, in the US, this kind of breakup makes a lot of sense because addresses are very structured. In India, it is a lot harder to break up addresses this way. But certainly, names ought to be broken up. And we often don't break up names for various reasons. For example, when we admit students in IIT, we take their names as it is in their school-leaving certificate. And some of them have their first name, middle name, last name. Others have last name, first name, middle name. Others have last name, middle name, first name. So, it's a mess. So, it would make more sense to structure it this way. We actually do that subsequently. We ask them to tell us which part is which. So, that's important. Then, there are cardinality constraints which play a very important role in both in the ER modeling and in how we convert the ER model to the relational model. Again, you are not familiar with this. So, I'll just go quickly through this. This is an example of one-to-one mapping between these two entity sets. So, this relationship set maps each thing here to at most one thing here and vice versa. So, you'll notice that A4 is not mapped to anything, which is okay. Unless the mapping is total from A, in which case everything here has to be mapped. Or if it's total from B, everything from B has to be mapped. In this case, we are allowed to, in general, we are allowed to have things which are not mapped. Then, there is one-to-many where one thing here can be mapped to multiple things there. But on the other side, each of these can be mapped to only one or at most one. It can be mapped to zero also. And correspondingly, many-to-one, which is essentially symmetric, you flip the two sides, one-to-many becomes many-to-one. There's no difference, really. So, we could save a little bit of space, but it's traditional. This notation one-to-many, many-to-one predates entity modeling. And it depends on how you are viewing it. If you think of it as function, if it's a regular function, a function could be many-to-one. If you have a function which returns a set of values, then it could be even one-to-many or many-to-many. So, depending on the application, it may help you to think of one entity being on the left and the other on the right. That may help in understanding what is going on. But really, it is symmetric. As far as year modeling and even the relational design is concerned, it doesn't matter which is left and which is right. So, we could drop one of these potential. And lastly, many-to-many, which allows each one here to be mapped potentially to more than one on the other side and vice-versa. Any comments from your teaching? Sorry, what are the questions? A is mapping A to B, one-to-many. So, this is an example, one-to-many. And B to A, again, one-to-many. So, B to A, this B1 is mapped to many here. So, both sides, if there is one-to-many relationship, can we say that it's a many-to-many? You will not say this is one-to-many. This one will be called many-to-many. Because the situation is what you described. One element here may be mapped to many here. And similarly, one element here may be mapped to many. But it is called many-to-many. One-to-many, okay, take this one. Many-to-many is if one element here can be mapped to at most one on this side. Similarly, one-to-many. Because at the time of explanation with the student, we normally take one example where this sort of thing is there. That is, from both way it is one-to-many. Or one-way one-to-many and other-way many-to-many. Then we can say that that relationship is a many-to-many. So, is it right or for the explanation purpose it is right? It is correct in the sense that if you take a particular entity here in many-to-many. If you take one entity here, the idea is that it can be mapped to many. Similarly, here it happens on both sides. One element can be mapped to many. So, it is absolutely right. That is perfectly correct explanation. Okay, good. Any other such question? Let us make this interactive. Otherwise, this is going to be boring because I am probably repeating things which you have taught many times. Sir, suppose there are three entities A, B, C. A is one-to-many, A-to-B. And from B to C, suppose many-to-many. Let me write that here. With the three entities A, B, C. From A to B, one-to-many. Okay, one-to-many. And from B to C, many-to-one. Okay. With the transitivity A, from A to C, can we say it is one-to-one? So, the question is can one thing here be mapped to more than one? From A to C. So, this one is one-to-many. One-to-many. So, here is an example. Okay. So, far everything is okay. On this side, it is one-to-many. On this side, it is many-to-one. But suppose I want a direct mapping problem. But if you do the transitivity, this A is mapped to two different C's. But that mapping from A to C is one-to-one? No, it is not. Because if you see this example here, one A entity has been mapped to two different C entities, transitively. Transitively. So, if you compose a one-to-many with a many-to-one, that could be in general, it could be one-to-many. One-to-many. One-to-many. Because from here you will map to, let me just check this. Certainly, this example shows that one can be mapped to many. And then again... You have also one element here, mapped to multiple elements there. So, you can also have... So, this is one-to-many. So, you cannot have this mapped here. So, if there is another element here, it must be mapped to something else. But on this side, it is many-to-one. So, you can map this here. This is legal. But now, if you do transitively, A is...  So, if you do the transitive thing, A1, A2, C1, C2. If you do the transitive, A1 is mapped to C1. A1 is also mapped to C2. A2 is mapped to C1. So, what can we say here? It is many-to-many. So, if you compose it this way, it could continue to be many-to-many. On the other hand, if you take a one-to-many, and this is also one-to-many. If you also compose one-to-many with one-to-many, then you will get one-to-many. And conversely, many-to-one, many-to-one will also be many-to-one. But I think you cannot bypass B. It always has to be routed through B. Well, in the model, it will go through B. The question was, if you take the transitive thing and say that this is related to that, and make it that relationship. Does that make some sense, transitivity in this context? Think of it as a query. If this particular thing over here could be thought of as a query result or a view. But query... Not as a base relation, but as a view relationship. So, conceptually, there is no problem. Do we do this cardinality business while answering the query? It's actually used in query optimization. Cardinality is used in there. So, it may not be very useful in general for design, but conceptually, there is nothing wrong in thinking about it like this. So, the notion of keys, again, is well-known. A super key is any set of attributes which uniquely identifies an entity for the case of entity. Similarly, for relationships, a candidate key is minimal, and primary key is a candidate key which you have chosen to be a representative thing, and uniquely chosen. You choose it. You, the designer chooses it. So, all this is standard stuff. Now, for relationship sets, again, we need a notion of key, super key, candidate key, primary key. The question is what set of attributes would form a key? And in the ER representation we use, we are going to insist that the combination of primary keys of the participating entity set forms a super key of a relationship set. What does this mean? Where is the diagram? Here is an example. We have instructor and student, and ID is the primary key of student. Similarly, ID, student ID is the primary key of student. Now, for this relationship set, what do we mean by saying that the combination of this and this, let us call it instructor.id and student.id. That should form a primary key for this. So, what does that imply? It implies that any pair of entities, in the case of binary, in the case of binary, a pair of entities can have only one relationship in a given relationship set. Now, why does that matter? Because if you, let us go back to the set. Yeah, here. So, here, every pair of entities here, instructor, student, has only one relationship. And on that relationship, we have added a date when that student last met that advisor. Now, supposing you want to say that, supposing I want to model not just the last time the student met, but every time the student met the advisor. I want to track all the meetings. Now, it may be tempting to say that, let us create one relationship. Let us take Crick and Tanaka. Here is one relationship with this date. Let us add one more relationship with a different date. Now, that is going to mess up things. First of all, this notion of cardinality, you know, one to many, one to one will get messed up if we create multiple things. Everything, well, you could still get back to it by doing duplicates. But the key thing is that if you have two separate ones in these, then this primary key plus this primary key will no longer uniquely identify this. Now, you could potentially have a version of ER modeling which allows that. However, in our notation, at least, which follows the Chen notation, we are not going to allow that. So, we are going to insist that a pair of relationships, in general, any set of relationships have only one relationship in a particular relationship set. And if you want to model this notion of multiple meeting times, there is a way out, though. And the way out is to have a multi-valued attribute. So, a set of meeting times, it is a single attribute, but it is a multi-valued attribute containing a set of meeting times. So, that's a better way of modeling it than by creating separate relationships. But the identification of that instance, relationship instance becomes difficult that instead of having one value, it brings in multiple values. If you have multiple separate relationships in the same set, then you can't identify it. But if you have a single relationship whose attribute has a set value, that is okay, that's not a problem. There's a difference between these two. So, in this notation, in the ER notation, it's chosen to go with this model where you can uniquely identify a relationship because there's only one relationship, but its attributes can take on. I have some specific question I will ask later. One question I hear is these terms, candidate key, super key, and the key, primary key, are they taken from relational model or they are in ER model? It comes from the relational model. That certainly predated the ER model. So, all of these came from that, but they make sense even in the ER. So, we keep on using all terms here also? No, not all, only the ones which are relevant. So, do we use primary key or do we use super key here? All these three terms are valid here also. They have the same meaning. There's no difference in the conceptually, they have the same meaning. Instead of identifying a record or a tuple, you are identifying an entity or a relationship, but the purpose is exactly the same, so the same terms apply. Now, here's a common mistake which people make. Supposing we have two entity sets. One is instructor, the other is department, and we have a relationship which relates instructor and department. Which department does this instructor belong to? Now, it's very common when people do a design to do the following. The instructor will have certain attributes, ID, name, salary and so on. In the relational schema which we have been using, there is also an attribute called department name. So, people tend to put this also in the ER diagram as an attribute of the entity instructor. Now, what is the problem with this? The problem is there is redundancy. So, you have this relationship which is already recording that an instructor is related to department, and you have a value here which presumably stores a key from the other one, that is department name identifies the department, but that is not made explicit in the diagram. Implicit in the name. I called it department name, and based on that, you have to figure out that this is really something which refers to department. Now, if you drew a schema diagram, we have seen schema diagrams also, which are relational. You take the relations and then draw lines representing foreign keys and so on. That is a schema diagram. That is different. There, you are showing the entire set of attributes of a relation. We have not yet come to a relation. We are modeling an entity, and we do not want to stick in attributes. A relationship should not be represented by an attribute at this stage. Any relationship should be explicitly shown as a relationship, and if you stick in an attribute department name, you are turning a relationship into an attribute, which will happen later perhaps when we create a relational schema, but it should not happen at this point. So, this causes a lot of confusion. Almost everybody makes this mistake. But as teachers, you have also seen many students making this mistake. When we go to the December workshop, it is important to stress this, so that the next set of teachers will carry this message. Any questions about this? That is the basic, simple set of concepts. Now, let us move on to the diagrammatic notation. I will introduce you to this UML inspired notation. So, in this notation, we show the entities as boxes. Relationships continue to be diamonds, although there are some small differences, which we will see. The name of the entity is on top here, and then the attributes are listed below here, and the primary key is underlined. So, this is, if you are familiar with the boxes and ovals notation, instead of having one oval per attribute, we are just listing them one below the other. So, it conserves space. Now, this relationship is identical from the earlier notation, which has long been used. So, then there is an issue of how do we represent composite multi-valued attributes? Again, there is no standard for this. We are creating standards, sort of. And what we do is, we have a composite attribute. We are just using indentation to show what are the parts of the main attribute. So, name has first name, middle, initial, last name. We could have done it differently. We chose to do this. Similarly, address has street. Street itself has street number, street name, apartment number, and so on. And down here is an example of a multi-valued attribute, phone number. So, again, we needed some notation for it. So, we need to indicate that it is multi-value. And last is a derived attribute, age, which is really a method, which does not take any parameters in this case. And it is just shown using brackets as usual, parenthesis as usual. Any questions on this? Now, all of this can again be done using standard tools. We just have to indent attributes and add curly brackets. The tool will not interpret any of this. It is part of the name. But we can interpret it semantically. The next question is, how do we show attributes of relationship? Earlier, we used ovals linked to the diamond. Here, we used the UML convention, which is to show a dotted line to a box, which contains the attributes. Exactly how UML shows attributes. It relates to what you have already talked about, multi-value. If the student has or instructed this, there could be different duration of the advisership. Different meetings. So, how do you model here? So, to repeat the question, we have a student, we have an advisor, which is an instructor, and we have the relationship advisor between these. So, the question is, let's say that a particular instructor was an advisor for this student between this pair of dates and this pair of dates and so on. So, there could be multiple time periods during which this person... One way to do it is to have... This is different from what we used. In our example, we used the last meeting time. Now, what we are doing is slightly different. We are trying to record during which periods of time was this person and advisor for this student. So, start... This is a new diagram. This is different. I am using the same things, but don't confuse it with the last meeting time. That is different. So, start time and end time. But as we discussed, the same advisor could become an advisor again after some time. So, here is one way of representing that. So, that is the set of attributes. This should be a dashed line. So, that this is an attribute. This is the set of attributes of advisor. And in this case, it's a multi-valued attribute which has two parts, start time and end time. So, actually in our notation, if you want to have multiple of these, instead of making it very wide, the way we would actually show it is start time, end time.  So, we could say that here is another way of modeling this. Move this up. So, we could say valid time start end. This is for one interval. Now, if you can have multiple intervals, we enclose that in parances. So, this is another way of representing the same information. Does that answer your question? So, in that case, what becomes the primary key for relationship instance? The primary key for this relationship instance remains student ID, instructor ID, because there is only one instance between a particular student and advisor. What if this same instructor happens to be advisor again? For the same student? So, we will have, that's why this is an attribute. But that unique identification of the instance is not... There is... No, there will be only one instance between a particular student and a particular even though the instructor was the advisor, let's say in 2009 and again in 2011, let's say. There were two periods of time when this person was the advisor. So, there would be two values here. It's multi-valued. So, there will be two values. So, it's multi-valued, maybe, and then similarly 1st Jan 2011 to 31st December 2011. Maybe in some of your earlier addition or in some... Ramakrishna's book, I found that you create the third entity there and have ternary relationship. Is that the... In this case... You could do that. If you model duration as an entity, which is probably not very clean, that is a workaround. In general, you can always turn anything into an entity. But... At some point... This actually gets... So, this actually gets to one of the issues in ER modeling that sometimes there is no one single answer. There are many ways of doing it and which way do you take? Sometimes there is no clear way. Sometimes it's clear, sometimes it's not. And in fact, another weird thing which happens is sometimes there are many ways of representing it in ER notation. But when you convert it to relations, they all converge back to the same thing. So, you might say it is pointless. If we convert into relations, we need primary key then. Yes. So, primary key issue still remains. Even if that is multi-value attribute, we need to create different relations for that. What will happen is when we convert it to relations, this start time could become part of the primary key. So, you would have student ID, instructor ID. So, you would have to convert it to the ER model. Because we are making that as a primary key in our relation. We could. We don't want to completely change everything from the original model. Not really, but making the entity or maybe the weak entity something like that. Weak entity will help in certain situations, certainly. But here it won't help you. We'll come to weak entities later. Okay. Thank you. One is what happens to our this new ER modeling notation. And second is how to map it to the relational model. So, when I look at the ER model, it appears as if the start time, end time and the relation of that with the student ID and the instructor ID is like multi multi-value dependency as we identify in the while doing the normalization. It is related to that. And secondly, if I look at the mapping of this to the relational model, then probably the Raghurama Krishna's suggestion that you should take out that start time, end time and create another relation, say duration or something, that makes sense. Then you are able to identify the primary key and so that will make probably. You could do it that way. There are no single answer here. There are many ways of doing it. I mean, not every possible way is correct, but there are many correct ways of doing it. So, what I mean to say is, you cannot represent everything that is there in the relational model. That should not be the goal of ER modeling. There are certain things in the ER model which you are unable to directly represent in the relational model and vice versa. There are a few things which you cannot directly, in particular constraints sometimes are a little difficult to map from one to the other. But otherwise, other than constraints, I don't think there's a... The multi-value dependency is one such thing that is multi-valued attributes. So, the mapping becomes more complex if the relational model doesn't support it. If you had an object relational model with supported sites or mighty sites, then you could actually map that directly, if you wished. What is goal of ER model? Like I said at the beginning, the goal is to have a high level view of the enterprise. If you don't do a diagrammatic notation, you give a set of create table with schema, that's a very low level view. A diagrammatic view helps to get a big picture. But it includes everything, supposed to include everything which ultimately has to be mapped to relational model. It includes other details. No, it doesn't actually because none of the ER notations takes, for example, type into account. Completely. So, it is meant to be a higher level abstraction which hides some details, but gives you the big picture. The goal is to show what all is there in the model, the big picture eliminating the details, so that you can in one diagram or in a few sets of diagrams, you can get the big picture of what all is being modeled without looking at the details. Of course, you can't throw away all details. So, it's a decision on what details you show, what you don't. So, in this particular case, we have eliminated types and a few other things like that. Those are the details. Only type, I guess. Yeah, I think, yeah, it's basically types. And the references are implicit here, not explicit. Yes. So, the relationships are shown, actually they're explicit as relationships. So, there are no foreign keys at this point because there are no attributes which would link that up. So, that comes later. In fact, it's a little cleaner because the moment you have an attribute which is a foreign key that forces you to have a many to one kind of relationship. If you want to do many to many, then you have to have a set of values. So, the relationship is a cleaner way which lets you do an initial design without worrying about cardinality constraints and then you can add the cardinality constraints to the same diagram and from that you can decide on the final schema. If you directly try to do the schema, then you have to worry about the cardinality constraints right at the initial design point. So, it lets you sequence these steps, start without worrying about it, then keep on adding detail till you have a reasonably complete picture. This is a part of top-down approach for something like that. But what happened to the diagram after normalization like we split up the table and it is... That is going to come later. So, normally the steps will be first get an ER model to get the high level view, then convert it to tables and then normalization turns out to be just it turns out that most of the common cases where you would have messed up with normalization would actually be handled if you do the ER diagram but there may be a few cases which you missed. So, normalization is more of making sure you did not do anything and if required decomposing. But there may be changes in our high level design right after normalization. So, most of the time when you if you do an ER model properly the normalization step will not actually need to do anything. It will look at the relations and say yes everything is okay. Typically that is what will happen. It is not always going to happen but so the point is that normalization becomes a verification exercise at that point, not an exercise in further decomposing. And most of the time if you have to decompose you could probably have gone back to the ER diagram and done it differently and fixed a mistake in it which required the decomposition. Is it required like if we decompose the tables do we have to come back and change it? You don't have to but if you want to have a straight mapping between the ER and the final schema yes it would be a good idea to if it was a mistake in the ER diagram it's better to go and fix it there. I have some actually comments to make regarding this one. If you look at say database or any software that you are developing this is just a specialized software that we are developing right. So it has certain process to follow. So what we should be doing actually considering that we may have to go and iterate whatever we have done or whatever we have left actually go back and fix those problems and then proceed kind of the thing. So we need to iterate you to kind of the model right. So that probably demands that after the normalization you go back and change your ER model also to make everything look consistent. Because there will be other people who will be looking at your document and making sense at some point down the line. So that I think is really very good point. You are absolutely right and it's a very good connection to the overall software design model where you iterate and fix it rather than just let it be and let the leave the two inconsistent. You are right. So if the question is why do we bother about multivalued and so on when anyway you are going to split it up. See conceptually maybe something is a multivalued so this is closer to the conceptual model of what you want and the relational schema which you come up with is an implementation which you have made certain design choices and translated it. If somebody who is new to it looks at this, this will make a lot more it will be a lot clearer to them to see this than to see a schema where you have relation which has multiple tuples corresponding to this. So that's exactly what I mean by saying this is a higher level representation. It's easier to look at this diagram and understand what it's going on than to look at the relational schema and understand what is going on. That's exactly why we do ER modeling. Otherwise if it were not higher level we wouldn't bother doing it perhaps. Although it does help in coming up with the correct design with minimal violation of normal form and so on. So we are back here. Part of the notation again you must all be familiar with. A relationship can be linked to the same entity set more than once and then the different aspects here are modeled using roll. So roll names. So here is an instance where you have a course identified by course ID. It has a title and it has credits and a course can also have prerequisites. So prerequisite is a relationship between two courses. It says that one of the courses identified by this roll, pre-rec ID is a prerequisite for the other course identified by course ID. So this is a simple example of where roles are important. Any questions on this? So now let's get on to cardinality constraints the many to one, one to one and so on. Again there are many different notations for this. There is no standardization in the ER community. For the sake of continuity from the previous edition we have and also continuing the same ER notation we have chosen to go with the arrow notation. It's easier for people to understand actually. There are other notations which use cardinality constraint of the form zero dot dot something, one dot dot star zero dot dot one, one dot dot one and so on. So there are other ways of doing it but we have chosen to stick with the arrow notation. So in this case the advisor relationship between instructor and student has arrow in both directions which means it is one to one because that may not be very realistic because we have many students per instructor but this is just a toy example. Now this is a more realistic case where the arrow points this way. So what is that arrow signify? You can think of it as a functional thing that is given a student the instructor is functionally determined which is the same as saying it's many to one. So for a student there is a unique instructor. If you remember the functional dependence notation there is an arrow. This arrow kind of reflects that arrow or the functional dependency. So that's one of the reasons we chose to stick with this notation. So that is in this case it is this one is one to many looking at it this way or looking at it this way it's many to one. The default if you don't specify any constraint is of course many to many. Now this diagram actually shows two things. We are going to postpone worrying about this double diamond. Again you are probably familiar with this but let's not discuss it right now but let's focus on this part the double line again this notation is the same as what has been used in the past. What this denotes is that this guy the section entity this is actually a weak entity but we will look at the weak entity issues later what this says is that the participation of this entity set in this relationship set is total meaning every single section must participate in one sec course relationship. There is also an arrow here so that means that every one of these can participate in only one. In other words every section must have an associated course because of the double line and the arrow implies that it has at most one so this says it has at least one and this shows it has at most one associated course. So you can also express cardinally constraints using this notation so here on this link we are saying one dot dot one which means that a student must participate in at least one and at most one instance of advisor. On this side we have zero dot dot star which means an instructor may not participate in advisor instructor may not be an advisor and star meaning the instructor can be an advisor to many students. Now similar notation also used in UML unfortunately the location of these two is flipped in UML causing confusion so what I mean is in UML this will come here and this will come there I don't know why they did it that way considering it came after this is standard ER notation that is rather non-standard so it's probably safer not to use this because it causes confusion between UML and ER the double line and arrow notation doesn't have that problem although this can express constraints which that cannot exist for example an instructor can be an advisor for at most 20 students we could say zero dot dot 20 here which you cannot express using the arrow notation. So now let's get to ternary relationships at least in the university schema we had a little bit of trouble coming up with a very good example of something which is really ternary and you'll see this example is a little contrived but so what is this example we have an instructor we have a student and then we have projects now a project typically for our students is at least at the B B tech level it's just something they do in a course or as part of their degree requirement but here maybe a project has an existence of its own beyond that so maybe it's a project which multiple students participate in or multiple instructors participate in so think of it as a large project it's a group effort so a project has a meaning of its own so it's meaningful to represent as an entity set it's important to represent as an entity set similarly student and instructor or of course entity sets so what this says is a student participates in this project as an instructor guiding that student so whenever you have a ternary relationship you should ask what if we broke this up into binary relationships so here what if we had student project and a student instructor as two separate binary relationships the issue is that an instructor may be involved in multiple projects so maybe there are two projects and on one of the projects two projects in which both the instructor and the student participates but for project one this student is advised by instructor one for project two the same student is advised by instructor two and now both instructor one and instructor two participate in both the projects so if we split this as a pair of binary relationships we may know that instructor one is the instructor for student one we know student one participated in project one and project two and similarly instructor one and instructor two guided the student but we no longer know that instructor one guided student one for project one but instructor two did not guide this student for the same project we will lose that info if we create a binary thing we lose this so in terms of normalization theory we have a lossy joint decomposition so when you take a ternary and split it into two binaries it's a decomposition and in this case you lose information if you split it so this example really requires a ternary if you split it you cannot represent it but there are certain other situations where it is perfectly fine to split it in which case you should be splitting it any questions on this so this slide says let's split it we have a complete session after this for doing designs so we could I'll give you the choice would you like to do a design at this point and then continue we can use this second session for covering the remaining slide so shall we split it that way so let's take a word would you like to split the activity in the December workshop probably and finish this whole thing and then the second session you will be conducting locally so perhaps we should stick to that if you want to see how we will do it in December but if you wish to do it differently now we can fine so let's stick with the December pattern and finish up the remaining constructs so there's this notion of weak entity set which again seems a little artificial but it actually has a lot of sense so what does this mean if you were asked to define a weak entity set you would say that an entity set that does not have a primary key is referred to as a weak entity set but then you are the designer who has to put a primary key in the entity set this is not a very good definition from the viewpoint of a designer so better way to think of this that there is an entity set whose existence depends on the existence of another entity set so this is really subsidiary to the other one this thing makes a lot more sense so we have several examples here in the university example we have a notion of a course CS 101 you know bio 103 whatever these are course IDs and there is a syllabus defined for the course and so on but every semester courses are offered to students so that is a course offering now you cannot offer a course unless the course itself exists in the database so the course offering is existence dependent on the course itself so that is a good example of something which should be a weak entity set now in a notation we get a section a section is basically an offering of a course and each existence depends on the course existing and therefore it's a weak entity set now that brings back the question what about primary key so why don't we throw in the primary key and we will come to that in the next slide also now in the notation you know because it has to have another entity set which on which it is dependent that's called identifying entity set we need to know for a given weak entity set which is the identifying entity set and we are going to use a double-diamond notation to say that this relationship links it to the identifying entity set in general you may actually have something which is existence dependent on two things so you may have two identifying relationship sets although that is less common typically it would just be one identifying entity set so now let's use the diagram so here is a section here is a course and this double-diamond indicates that section is identified by course and how do we know which direction is it why is it not course is identified by section because of this arrow which indicates that section has a unique course with it so this arrow indicates that identification goes this way that's one clue in fact there are multiple clues the main clue though is that this is a weak entity how do you know it's a weak entity set because we do not have a primary key so primary key would be underlined so in this case there is no primary key instead we have dashed underline which indicates that these attributes are a discriminator now what does this mean so you will notice that section does not have a course ID here this is important the course ID is implicit in this relationship which this section has to participate this is total so the section has to participate in this and that relationship tells you which course ID this was for therefore you should not put the course ID here if you put it here you could make this whole thing the primary key course ID, section ID, semester could be the primary key but if you put in course ID here we have the problem that the relationship is there and the course ID is again representing the same thing so that is duplication to avoid this duplication we do not put course ID here and the moment we do not put course ID here there is no primary key therefore it is a weak entity set so that comes back to the original the starting definition of what is a weak entity set now of course there may be many sections associated with a single course we have to distinguish them or discriminate them so the discriminator attributes have to be present and they are shown with a dashed underline this will be it is many from this side to one on this side if you look at it here it is one to many so now in our design what do we have here we have a semester year and a section ID so what this means is that this same course may be offered more than once in a particular semester and a year if so there has to be a section ID which uniquely identifies our section and this is a fairly common requirement pretty much any university which has multiple sections has to deal with this any questions my question is like by using year data model we are modeling the real situations so if we work with some real applications we collect the requirements then very first step in this modeling is to identify the entity sets so we start identifying first of all strong entity sets that's a straight way procedure because we are capable of identifying the situations in which some primary key is available so we directly identify strong entity set but what's the proper way of identifying the weak entity sets that's a very good question so typically what you would do here is identify both of these entities initially without knowing whether they are strong or weak entities later when you start refining the thing you realize that this entity set depends on the existence of the other entity set and therefore we will model it as a weak entity so it's a two stage process in the first phase you don't want to worry about this you just say that yes there is a in the case of the university thing we know there is something called a section which should be an entity we know there is something which is a course so at this point both are just entities we don't know whether they are strong or weak later we will in my analysis during teaching of this course to the students and solving the various case studies to the various students in the lab I identify that it's very easy to identify the strong entity set so first of all we list all the strong entity set and model them appropriately with the designing of primary keys but when we go into the analysis of every strong entity set we come to know that the redundancy problem is there in storing the entities within that entity set which is a strong one we need to reduce down that redundancy level by separately modeling a weak entity set so further we need to make a deeper analysis in every strong entity set and then we discover the weak entity set because weak entity set is meant for the strong entity set on which it is existence dependent yes so I think we need to specify the clear cut steps of modeling the entity sets that when we are going to specify the strong entity set and when we are going to specify the weak entity set that's a very valid point so one way is you know you could identify something as strong entity for sure and leave out weak entities and then add them later but it's probably easier to just identify all possible entity sets without worrying whether they are strong or weak in the first phase and then make the decision on stronger weak in the second phase that's probably cleaner maybe her observation comes from like the section could be modeled as a attribute multi-value attribute of a course so at later point of time she discovered that instead of making the multi-value attribute it needs to be modeled as weak entity yes that is possible another situation comes to my mind like say we are collecting the courses I as a user some data I am collecting courses of all IITs so I will say IT CS110 from IIT movement so if I collect courses from all IITs then even course itself is a weak entity owned by the institute ID that's certainly an interesting way of looking at it yes you could even maybe in the context or maybe in the enterprise scale of the yes you are absolutely right that's a nice example so if you are combining these things then whatever set of attributes was enough to uniquely identify it is no longer enough so then the two possible approaches are to add the institute ID to every one of these in the year diagram itself but maybe it is better to identify the institute as an entity and then each of these now becomes a weak thing in the institute that is probably a cleaner way so that's a very nice example absolutely list all the strong entity set then you analyze the situation of modeling that is strong entity set in deeper way and then think about the kind of redundancy level you are having in that and then you go for modeling or decide the weak entity set yes so that should be listed in our lecture notes definitely yeah so sometimes it's hard to say that you know you should do it exactly like this because the whole process is kind of iterative so sometimes you know you are not sure you make a decision then you realize no that was wrong and then you go back so it is a evolving process yeah so you can have some general guidelines but eventually each time you do this there are going to be there is going to be some uncertainty which there are ways of resolving the uncertainty but you may actually do a first cut thing and then look at it and say ok no this is not quite right I should redo this in particular this point which was mentioned should this be an attribute or should it be an entity that decision is sometimes not a local decision that is you know I may say that you know address is simply an attribute now if I am building a GIS system geographical information system address probably should not be an attribute you want a notion of an entity which is a city an entity which is a street maybe even an entity which is a building and then address becomes a relationship of a person to a building or a flat or whatever so what you should do depends on what else you are modeling so it is an iterative process you may first do this and then say oh but I really need to do this also and therefore now it should be a relationship not an attribute in particular for an institute like IIT we actually need to model what are the buildings what are the flats because we have an allocation system similarly for hostels and rooms so now the allocation now becomes a relationship not an attribute whereas here we have assumed that addresses are attributes so it depends on what else we are modeling in the database thank you sir


Hello and welcome to this session in which we'll discuss the purpose and objective of an important concept called internal control. Internal control is covered in your accounting courses as well as the BEC exam that's going to be going away later on the CPA exam as well as the audit. So it's very important to understand the concept of internal control. Also internal control will cover IT information technology so you have to have a deep understanding of internal control in order to appreciate the role and the benefit that the internal control generate for a company. So whether you are a student or a CPA candidate it's very important to understand this concept. What I want you to think about when you think of an internal control is think about this fort, this castle. What is inside this castle? Inside this castle we have our assets, we have our assets our resources but basically our cash inventory property plant and equipment stuff that the company cares about because we need to use it to run the business. Inside this fort sits something very important and that's our accounting information system. That's the system that generates our financial statements income statement balance sheet statement of cash flows stockholders equity. Also this accounting information system generate internal reports that's going to help us run the company. So inside this fort is a very important component the accounting information system. Inside this fort we have employees our employees and those employees are very important to us. We want to make sure they're running the company efficiently effectively. Now what is this fort protecting us from? Well it's protecting us from this group of people. What is this group of people represent? Those people represent misstatement errors fraud theft which is misappropriation so on and so forth.  The fort is to help us protect assets the accounting information system and the employees. Before we proceed any further I have a public announcement about my company farhatlectures.com. Farhat accounting lectures is a supplemental educational tool that's going to help you with your CPA exam preparation as well as your accounting courses. My CPA material is aligned with your CPA review course such as Becker, Roger, Wiley, Gleam, Miles. My accounting courses are aligned with your accounting courses broken down by chapter and topics. My resources consist of lectures, multiple choice questions, true-false questions as well as exercises. Go ahead start your free trial today no obligation no credit card required. Now let's talk about specifically what internal control is. Well that fort is basically policies processes and procedures that's what that fort is but it's set up by management. Management set it up to perform the following objectives. We have four objectives one reliability of financial reporting and this is what I meant by the accounting information system. We want to make sure that the figures reported whether externally or internally are fairly and timely presented. Fairly means they are correct they they represent what actually happened all the figures are there the valuation is correct and timely we are provided this information for users on a timely basis so they can make relevant decision about the company or how to run the company that's one objective of the financial of the internal control reliability of financial reporting number two efficiency and effectiveness of operation simply put we have asset we have resources we're going to be utilizing those resources in the most efficient and effective manner what does that mean it means operating at the lowest cost while achieving the same objective it doesn't mean we need to reduce cost to minimal well yes we reduce cost to minimal as long as we are achieving our business objective so we don't want to have any wasteful assets. Now how do we do so we set standards we set budget we use variances to follow up this is how we can have efficient and effective operation. Compliance with laws and regulation we want to make sure our employees who are inside this fort are following the rules the rules and regulations how we give them training education yearly reviews reminding them about the policies and procedure given them that handbook when they start working for the company company's policies reviews take disciplinary action to send the correct message if someone did something wrong violated laws or regulations guess what we discipline them and the fourth object the fourth objective of that fourth of the internal control is the safeguarding of assets how do we safeguard assets pretty straightforward locks security cameras fences any prevention of loss effort is considered safeguarding of assets we could use password if the information is not physical it's intangible in nature or tech digital. Now there are five components of the internal control now i'm gonna kind of show you show you physically what it looks like so if this is the fort what is this fort composed of what's what makes up this fort there are five components that make up this fort the first component is controlled environment again we're gonna have one whole session about controlled environment in which we'll discuss the the various components there are sub components of control environment we have another component called monitoring also we'll have a session about monitoring to understand what is monitoring within internal control notice those are the component of the fort risk assessment is the company assessing the risk properly information and communication they have a proper communication infrastructure accounting information system is communication flowing smoothly throughout the company upward downward so on and so forth and also the company will have control activities and all of those component that's what internal control is that's what this fort is composed of those five component those five components put together properly utilized properly implemented and maintained they can protect you from misstatements or minimize misstatement minimize or you'll try to eliminate but you're gonna see there are always weaknesses in internal control notice the weaknesses here this area is not protected this area is not protected right there are always weaknesses but we'll do our best in avoiding preventing misstatements avoiding errors and fraud protecting our our assets from theft and appropriation what should you do now go to far hat lectures and work mcqs through false multiple choice questions that's going to help you do better on your exam good luck study hard


We are delighted that Dr. Ruth Bell is here with us tonight. Her accomplishments and awards are many and impressive. She has been inducted into the Trinity College Honor Society at the University of Toronto. She has received the Governor General's Award in commemoration of the person's case. In 2007, she was honored at the YMCA YWCA Women of Distinction Awards with a Lifetime Achievement Award. That same year, Dr. Bell established the Hon. Dick and Ruth Bell Chair for the study of Canadian parliamentary democracy here at Carleton. This created a prestigious new academic position in the Department of Political Science and thus opportunity for dedicated teaching and research into Canada's dynamic democratic system. In 2004, she published her memoirs entitled, Be a Nice Girl, A Woman's Journey in the 20th Century. For her generosity, commitment and dedication to the advancement of Carleton University and its values, Dr. Bell received Carleton's highest non-academic award, the Founder's Award at Convocation Ceremonies in June 2010. In May of 2009, Dr. Bell was honored with a degree of Doctor of Sacred Ladders from her alma mater, Trinity College, for her lifelong dedication to public service, education and family, with an emphasis on the status of women. As you will see by reading the program notes, these are about a few of Dr. Bell's achievements. They are testimony to the contribution she has made and continues to make to public debate. Please join me now in acknowledging her contributions to Carleton University and the larger community. Since 1992, the Bell lecturers have given distinguished Canadians an opportunity to speak on matters of politics and public policy. We're very privileged this evening to have as our speaker a best-selling author and highly sought-after media commentator who also happens to lead one of the world's top social research firm. To introduce him to you, I'd like to call upon Barry Wright, Associate Dean and Director of the Arthur Kruger College of Public Affairs. Barry? Thank you, Dean Plourde. As Dean Plourde noted, the Bell Lecture Series honors the contributions of distinguished Canadians active in public service and public affairs and provides an opportunity for them to speak on historical or current matters of politics and public policy. Dr. Daryl Bricker joins an illustrious list of Bell Lecturers, stretching from Robert Stanfield in 1992 to Richard Gwynn, who delivered the last Bell Lecture. Dr. Bricker is CEO for Ipso Global Public Affairs in Toronto, a firm that conducts social research, political polling, and corporate reputation research around the world. He began specializing in research methods, polling, and analysis as an undergraduate at Wilfrid Laurier University, and his university studies culminated here at Carleton University where he completed his PhD thesis in political science under the supervision of Professor Conrad Wynn. Dr. Bricker was recruited as Director of Public Opinion Research in the office of the Prime Minister in the Mulbrunni government and then moved on to the Angus Reed Group, which later merged with Ipsos, the world's second largest market research firm with offices in 85 countries and research staff of 9,000. In addition to his work as CEO of Ipsos Global Public Affairs, Dr. Bricker serves as a board member for the Laurier Institute for the Study of Public Opinion and Policy on advisory groups from the Toronto's Economic Development Committee, the Fort York Centre, the Royal Canadian Military Institute, and is also an honorary colonel of the Queens York Rangers Regiment. Dr. Daryl Bricker received an honorary LLD from Laurier University in 2010 and is regularly called upon to give media commentary. In addition to numerous articles he's published, five best-selling books, searching for certainty, what Canadians think about almost everything, what we know, we know what you're thinking, connectology, and most recently the Big Shift, which came out last year. The Big Shift, the seismic change in Canadian politics, business, and culture and what it means for a future is co-authored with John Ibbotson. When I was a history student at the University of Toronto in the 1970s, Donald Creighton's Laurentian thesis was still very influential and helped to inform contemporary understandings of Canadian political culture. Bricker and Ibbotson present a provocative challenge to the idea of a Laurentian consensus. Does it still exist? Does the rising power of the West, distinct suburban interests and the gravitation of immigrants groups to the right, mark a paradigm shift in Canadian political culture? Or at the very least, can we say these forces present a significant challenge to the Laurentian interests that have traditionally dominated Canadian political agendas? Please join me in welcoming Daryl Bricker to the stage for his lecture, The Big Shift, The Battle Between Old Canada and New Canada. Thank you very much, Barry. Good evening. Must be a boring evening here in Ottawa. Come out to see this. It's great to be back at Carleton University. I certainly don't remember digs like this when I was a student here at Carleton. As I look at some of my professors who were here at the time, I remember having a wonderful office in the low building on the sixth floor where political science is and it was literally a broom closet.  I had another chair in the room that if I wanted somebody to come in and sit with me, I had to open the door and put the chair in the aisle, out the hallway outside of my office so somebody could sit there because I was the SPSS and computer consultant in the department for the time that I was here. People had to come and talk to me about their computer assignments so you'd have to sit in the hall while I sat in my little office. Thank you very much for having me here tonight. I'd like to begin by thanking the Bell family. Where'd she go? There she is. Dr. Bell, thank you very much for sponsoring tonight's lecture and my alma mater, Carleton University, for asking me to deliver it. When I look at the names of those who've previously given Bell lectures, it is to say the least an intimidating set of people. I am truly humbled to be among them. In fact, one of them is actually one of my co-authors. I wrote my first book with, so it kind of brings this a little full circle as well. Tonight's a bit of a homecoming for me. As you know, I'm a Carleton graduate. I earned my PhD in political science from this place in 1989.  Why did I come to Carleton in a way in a word, propinquity? Propinquity means physical or psychological proximity. My reasoning at the time was that if you're interested in Canadian politics and government as I was, Carleton is the only place to be. And judging by the fact that we're here tonight talking about the big shift, which was written specifically about our national political conversation, I believe I was right in that choice. Frankly, I don't know if this book would have been written if I hadn't attended Carleton. That is the absolute truth. And I also know that the career that I've enjoyed for the last 25 years definitely wouldn't have happened. So thank you very much, Carleton University, not only on my behalf, but on behalf of my wife, Nina, and my daughter, Emily, and we wouldn't have what we have today if I didn't have the good fortune of attending Carleton. Thank you very much, Carleton. So let's move on to the big shift. Whereas what one critic described is a little book that I wrote with my good friend from the Globe and Mail, John Ibbotson. The book, The Big Shift, the seismic change in Canadian politics, business, and culture, and what it means for our future. I humbly claim credit for the title, but as far as the second part of it, the subtitle, I don't think either one of us were thinking that well. It's a bit wordy and it doesn't really work. The Big Shift concept I actually do claim credit for. But let me just say that it seemed like a really good idea at the time when we wrote that subtitle. I wish we could drop it at this point. But how did the Big Shift come to be? It was born of mutual frustration. Both John and I were peaked by what we believed was a complete mischaracterization of our May 2011 national election campaign by people who we both believed should have known better. But as we shared our frustration, we came to realize that those who should have known better, in fact, did not. Why was this the case and who was responsible for losing the plot? We had an explanation. It was a group that we call in our book, The Laurentian Elite, and their shared vision of our national narrative, which we call the Laurentian Consensus. However, as our Laurentian friends have pointed out to us, they are surprised to be included in an elite group that they didn't know existed and even more surprised to be told that they share a common vision that they didn't know that they had. An explanation is clearly in order, so let's start with the elite. Who are they? From the time of Confederation until quite recently, they ran Canada. They are the political, academic, cultural media and business elites in the communities along the St. Lawrence River. On all the great issues of the day, these Laurentian elites, debated among themselves, reached a consensus and implemented that consensus. The Laurentian elite governed us and for the most part, we argue in the book, governed us very well. Canada is a great country and we have the Laurentian elite to thank for it. They guided us through two terrible wars and lost an infrastructure revolution in the 1950s that created the highways and airports we still use today. They also created a healthcare system that many Canadians see as our defining achievement as a nation. They patriated and modernized our colonial constitution and created the independent Canada that we all cherish today. Issue after issue, decade after decade, the Laurentian elite shaped the public policy arc of our nation. Its members were and are few enough that most of them know or knew each other. At first they were largely British stock and largely Protestant as well. Though any bias against French, Catholics or Jews just wasn't tolerated for long. Indeed, agreement between the French and English elites was essential and whenever it was not obtained, things went badly. They were for the most part from the upper ranks of the middle class, though the membrane was permeable. They were almost invariably small L Liberals who voted for the large L party. Indeed, the Liberal Party of Canada gave us some powerful manifestation of the Laurentian elite and their consensus. If that's the elite, what's their consensus? Today's version is based on a common set of assumptions about Canada that were a fragile nation. By the way, you can see this in all the excitement about national unity reemerging. It's like watching all these people, it's like getting the band back together, right? We're all excited. Oh my God, we can talk about national unity again because it is their animating, animating issue. I love the national unity issue. So they believe that we're a fragile nation. That the federal government's job is to bind the country together that would otherwise fall apart without their subtle hand, their subtle guidance. That the biggest challenge is keeping Quebec inside confederation and that the poorer regions must forever stay poor propped up by the richer parts of the country. That our national identity, whatever it is, must be protected from the American juggernaut and that Canada is a helpful fixer in the world, a peacekeeper, a joiner of all the best clubs. Most of all these elites assume that their version of the country is still the country and that they still run it as they always have run it in the past. Ironically, it's Canadian tolerance, the tolerance that was created by the Laurentian elite which planted the seeds of their own destruction. The cultural accommodation that was necessary for French and English Canadians to get along has made it possible for people of diverse backgrounds to live together in the Canada that we celebrate today. Open door immigration combined with multicultural tolerance represents the finest achievement of the Laurentian elite. It's also helped to do them in. If the Laurentian elite and their consensus is the past, what's the future? To me, the story can be found in numbers. What's perplexing to me is that these numbers are not in dispute and are all easy to find. But for some reason, maybe it's denial, nostalgia, maybe a little bit of laziness, we pay little attention to them. Let's start with our first number. And that number is 1.7. 1.7 is the average number of children a Canadian woman has today during her fertile years. To have a sustainable, healthy population we need at least 2.1.  At just 1.7, we're almost half a baby short. Why are birth rates dropping in Canada, but also in so many other countries? The baby decline is driven by changing choices for families created by a combination of urbanization and education. On the farm, children are an economic asset. In the city, kids are an expense. Educated and urbanized parents have fewer kids. And Canada is a very urbanized country. Over 80% of us live in cities and towns. In fact, the last decade in which half of us lived in rural areas was the 1920s. Having a low birth rate is a huge challenge. Let's take the case of Japan. Its birth rate is just 1.4. There are more than half a baby short. In 2012, Japan's population was just over 127 million. By 2015, it will drop by 25%. And the remaining Japanese are getting older every year. An aging population is an expensive population. More money is needed for an increasing healthcare and pension expenses. And since most people are counting on selling their home to pay for their retirement, who will be the homebuyers in Japan? The effects of birth rates are shown and I'm going to show you just a couple of figures because I think I have an obligation to show you some statistics since that's what I do. Sorry about that. For those of you who wanted to see something more interesting, but this is the Canadian population in 1961. So the year I was born, it's the last year of the baby boom. Up the center of the charter, ages. So if you read along it, that's the number of people who are at that age in the Canadian population in 1961. Men are on the left and women are on the right. This is a healthy, expanding population. There are lots of children being born to support and replace the people at the top. We're falling off at the top of the pyramid. And I won't say why they're falling off. I'll let you come to that conclusion on your own. Here's our population in 2011. Our pyramid has become a vase shaped like this. The big bulge you see in the middle is the baby boom. You'll also notice it bulges out more in the middle than at the bottom of the pyramid. This is a Japanese problem. This is what 1.7 looks like. These two charts show a potential population crisis. Yet Canada has the fastest growing population in the G8. Why is this? By the way, take a look at Germany, Japan, and Russia in terms of their population growth. They're slipping back. Why is this happening in Canada? In a word, immigration. Canada now tracks about a quarter million immigrants a year. Every year. This is more per capita than any other major developed country. To put this into context, and just think about this. Put this into context. It's a new Toronto every decade that comes to this country. A new Toronto every decade. And we've been seeing this last level of immigration for at least two decades. So last 20 years, two new Toronto's have moved to Canada. And there's no sign it will stop. In 2010, the UN said that 214 million people lived outside of their country of birth. But only 3% of them are refugees. So it's not that people are moving all over the place because they're being forced out of their home countries due to war or famine. They're moving for economic advantage. This is the emergence of a new mobile global middle class. They're ready to move and they have the skills to hit the ground running. Canada's big advantage is that they see us as a successful and stable country. And they also see us as tolerant of those coming from outside of our founding cultures. Which makes the Quebec issue with the values charter so problematic for the province of Quebec. They have a birth rate of 1.72. They need immigrants. Appearing intolerant to the people you need is a problem for you. And as a result, the Quebec population is not attracting their proportion of immigrants. Just like the other parts of the country are actually Atlantic Canada isn't, but particularly Toronto in the west. And we'll get into that in just a second. But who can we thank for this? Well, it's the Laurentian consensus. They created this possibility for an immigrant, an open immigration and the level of tolerance that we have in our country. It's their greatest achievement, as I said before. Our new immigrants are definitely outside of the frowning English, French and Aboriginal cultures. Where are they coming from? Here's a comparison of immigration sources in Canada for 1970 and 2010. Let's take a second and just take a look at that. I could have given you 1980 and you'd still see that the UK was at the top of the list. In fact, the number one immigrant group today wasn't even on the chart in 1970. And as far as the Chinese immigrants are concerned there, the Chinese that were coming over in 1970 were all coming basically from Hong Kong. Now it's less than 0.01% that are coming from Hong Kong. They're all coming from the mainland. As you can see, our new immigrants don't look like our founding cultures or even like previous waves of immigrants. They're mostly from the Philippines, India and China. They are less Atlantic and more Pacific in their orientation and that's a key point. They have a Pacific orientation and they are much more likely to be visible minorities. Returning to the Laurentian vision of Canada, immigration is really challenging the idea that we're comprised of three major population groups. The English, the French and Aboriginal communities. And that these groups will forever define our culture. Of the three founding cultures, the one that's having the hardest time maintaining its position in Canada today are Aboriginal Canadians. There's just over 600,000 status Indians in Canada today and which doesn't include the Métis in New York people as we all know since that's in a certain amount of dispute right now, but only about 300,000 actually live on reserves. What's about the equivalent of one year's worth of immigration? Think about that. One year's worth of immigration. Yes, the Aboriginal population is young and has a higher birth rate than the general population, but they start from a small base and their growth will slow over time with increased urbanization and education. The Aboriginal birth rate used to be as high as six. It's now down to just over two. So that's going to be changing as well. If there's a deal for the Aboriginal community, now's the time to get it because your population is going to shrink, shrink, shrink. A similar predicament faces the other two major heritage groups, the English and the French. Their respective share of the Canadian population is also shrinking. What does this mean for important national goals? For example, like bilingualism, which I always enjoy when I come here to Ottawa. Truth be told, only 17.5% of Canadians report that they can carry on a conversation in French and English. 17.5, less than 1 in 5. My research shows that the number of Canadians who believe that they can do their job equally well in the other official language is actually less than half that. It's about 8. Think about that. 8% of the population to fuel our federal public service. Bilingualism hit a wall in 2011 because new immigrants who mostly adopt English as their preferred official language are driving our population growth. How are we going to maintain a bilingual public service in Canada that's capable of providing services in both official languages from coast to coast to coast? Do we really believe that a public service drawn from such a narrow segment of the population can adequately serve Canada in the future? No wonder the Harper government had such a hard time finding bilingual Supreme Court justices and a bilingual auditor general. It's only going to get harder in the future. They're drawing from 8% of the population. Much of this wouldn't matter if immigrants choosing Canada decided to move equally across the country, but that's not happening. They're moving to where the jobs are. That's the Toronto suburbs, especially the 905 area code. So that's the telephone area code around the city of Toronto and Western Canada. So while Canada used to be an Atlantic country, it is increasingly a Pacific country. That's because most immigrants are now coming from Pacific nations and our population is shifting west. And this is what this looks like. Let's take a second and contemplate that. If I put the prairies up for just the last 10 years, you'd see they're moving way ahead as well. Atlantic Canada shrinking, Quebec is shrinking, Ontario, the growth is slowing down. Most of the growth in the country is in the prairies in British Columbia, especially Alberta. To me, this is the big shift in a single slide. And there are huge implications to this. Let's take a look at our major cities just for a second. Now outside of a war, that number of people moving to a place like Calgary is a massive displacement of population for no other reason than people seeking economic advantage. But take a look at Montreal and take a look at Calgary. By the way, percentage of the population that's foreign born in Montreal? 20. Percentage of the population that's foreign born in Toronto? 50. Percentage in Calgary? Pushing into the 30s now. Halifax? 5. Ottawa? 18. I described Ottawa, turning into Colonial Williamsburg. Not attracting a lot of immigrants. Still in a place where you kind of hang on to this tradition of bilingual people who are running things, a public service that never circulates. It's the same people all the time. It's becoming like an artifact of what Canada used to be. Because the rest of the country is moving on. I hope that was it for my slides. No more reading of slides, you listen to me now. I've proved my point. So where does this bring us as a nation?  A country that was once part of the Atlantic world is becoming part of the Pacific world. The provinces and groups that matter the most don't matter as much anymore. The country has, centre has shifted west and power has shifted with it. In fact, power is now shared by two groups in Canada. Western Canadians and immigrant middle class residents of the 905. I'm going to talk about Canadian politics in just a second, but I want you to keep that last group, immigrant middle class residents of the 905 in mind through all of this. Because if you can't win them, you cannot win now. They decide our elections. One area where you can see the impact of these population shifts is our national politics. We have a nationalist election system based on representation by population. All our political science students here know that. Regions that have the most people get the most seats in the House of Commons. That's the way it works. To understand the impact of our shifting population, let's go back to the federal election of May 2011. That spawned this book. To John and me, that election was a fracture in time, like BC versus AD. It was the birth of a new Canadian politics. We all know what happened. The Conservatives won a majority. But it would be wrong to attribute this result simply to the combination of Conservative tactical brilliance or viciousness The impact of Jack Leighton's smiling face are the dark, dark tragedy of the Liberal campaign. That's the Laurentian perspective on what happened. Rather, this was all about the emergence of a new, decisive coalition in Canadian politics. This new coalition is comprised of middle-class voters in the 905 who are increasingly new immigrants and Western and rural voters. What brought together the Conservative coalition? The answer is that their message of low taxes, sound finances, and economic growth, combined with an emphasis on law and order, convinced a large slice of 905 immigrant voters that they shared the same values with rural voters and voters in the West. They brought together Old Canada and New Canada. So New Canada's suburban, immigrant, multicultural, middle-class, found common cause with Old Canada, the white and often rural people descended from the original settler cultures. Now think for a moment about what you know about Canadian politics. It's all about winning votes in Quebec and Ontario. Right? That's how you win. If you can do that, you win a comfortable majority government. Not so much anymore. The Conservatives won a comfortable majority in 2011 with only five seats in Quebec. In fact, Quebec has not voted for a national government since 1988. Now let's fast forward to 2015 in our next federal election. There are currently 308 seats in our House of Commons. Before the next election, another 30 will be added for a total of 338. All but three of these 30 seats are in Ontario and the West, where we've seen the largest growth in population. That's how our system works. Clearly the new coalition has the potential to be even more decisive next time out, leaving aside what you're seeing in the polling right now. In fact, I would argue that Stephen Harper and the Conservatives could win a majority with a smaller percentage of the vote the next time, because most of these seats benefit them because they're in the suburbs. And I know everybody goes crazy right now about how well Justin Trudeau is doing. He's got about like five point lead, but when you really take a look at his numbers, what you see is that he's in the 50s in Atlantic Canada, in the 50s. I know incumbency is a huge factor in those particularly in rural elections, so they're not going to win all 32 seats. Remember, there's only 32 seats in Atlantic Canada. There's more than that around the city of Toronto. So what's he going to win there? 20? Let's go to Quebec. John Cratchin won 29 seats, I believe, at his High Watermark in 2000. Somebody can correct me on that if you've got better numbers than me, but it was around that, I seem to remember. Three-way shootout right now in the province of Quebec. The BQ is coming back a little bit, particularly as a result of the provincial election. The Liberals and their NDP are both in the game. So what's Trudeau going to win? 30? 35? Let's give them 35. Okay, 20 plus 35 equals 55. Let's go to Ontario. Ontario, the Conservatives and the Liberals are basically tied right now. The Liberals look stronger because they're winning a lot of votes in downtown Toronto, where they're already winning. How many seats is he going to win in Ontario? 40, 50? Let's add it up. 20, 35, 55. Let's give them 45. Whatever that adds up to, 90. He's got to find another 80 seats in order to win a majority election. Can you tell me where the 80 are? I can't think of where they are. He's got a tough row to hoe. And Stephen Harper's vote just got more efficient because of those 30 new seats that were added to the House of Commons. It's going to be a competitive election. Not saying he's going to win, but I'm saying that the odds-makers are laying it on the wrong odds right now because they're not looking at what I just told you. They're looking at national polling, which doesn't mean anything. As we say in the big shift, the new Conservative Coalition isn't about one election. The Conservatives have the potential to be to the 21st century what the Liberal Party was to the 20th to the 20th century, the National Governing Party. As long as immigrants keep moving to this country, as long as they keep moving to the 905 and as long as they keep moving to the west, that's the trend. Each week, a lifetime in politics, since we submitted the final draft of the big shift to our editors at Harper Collins. During that time, a troublesome problem involving several Conservative senators and their expenses has metastasized into a life-threatening scandal that could prove fatal to Stephen Harper as Prime Minister. Even as the Liberal Party of Justin Trudeau enjoys levels of popular support, not since Sincere and John Crutch stepped down in 2003. I mean, the Liberals have not been that high since that point. So it's right and proper that these events have led some politicians, pundits, professors, and others to ask whether the thesis we advanced in the big shift remains valid. How can we say that the Conservative Party will be to the 21st century with the Liberal Party, Canada's natural Governing Party, west of the 20th century when the Conservatives appeared doomed to defeat in the next election? As I've already said, I'm not so sure that they're doomed to defeat based on how the numbers actually look. We believe that our thesis remains as valid today as it was when we wrote it and will remain valid for a long time to come. The big shift that we talk about refers to the massive shift in Canada's population from east to west. It refers as well to the enormous influx over the past two decades of immigrants from Asia and the Pacific region who are more Conservative in their outlook than European settlers of the 19th and 20th century and who have settled in large numbers in the suburban cities surrounding Toronto. Their Conservative values unite those immigrants and other suburban voters in Ontario with voters in rural Canada and the west. Together, these voters form a new Conservative coalition, one that is available to any politician who can legitimately appeal to their concerns. An overriding focus on the economy, a drive for smaller government, and an emphasis on law and order. It's a really simple agenda. Really simple. Now, I know everybody's talking about the middle class right now. I don't know why the Liberals have become obsessed with this but they have become obsessed with it. I'll just give you a little polling data to help you think this one through. So you go and you ask Canadians which party would do the best understands the middle class, NDP by six. Then you go and ask them which party would do the best job of taking care of the middle class if they got into power, NDP by two. Then you ask them, here's a series of government programs that we could bring into place to help the middle class. Or would you prefer a tax cut? Tax cut wins six to four. Sounds like a very convoluted message set to deal with for me. Very difficult issue set to deal with for me. I actually like the economy, smaller government, and an emphasis on law and order. If you can't sell somebody on being able to do that better than the current Conservatives with 905 immigrant voters, you're going to have a hard time beating them. Even to the extent that people are dissatisfied with Stephen Harper right now. By the way, government approval levels still over 40. I know based on doing this on an international basis we looked at 200 elections, and I think more than 30 countries at Epsos because we can do that. And we found that governments that had approval levels of 40 to 45 percent when it was an incumbent that was running again, 180 percent of the time. That doesn't mean they're going to win a majority. It might be a minority, but they still the process favors incumbents. So as I said, it's going to be a competitive election. We also say that this new coalition, especially in suburban Toronto immigrant part isn't available exclusively to the Conservatives. And this is the part that needs to be really emphasized, I think, for the other two parties. It can also be tapped by the Liberals or the NDP. If they can find a way to legitimately appeal to this group's core values. It remains our view that, given the nature of the values at play, these voters match up more easily with politicians on the right. You want to know why Rob Ford won in Toronto? This is why. And here's the truth about Rob Ford. Say what you can about his personal behavior or whatever. The election in which he won he drove up voter turnout by 10 points over the John Tory David Miller election which was the competitive election that took place before that and won half the vote. He just didn't win. He smoked George Smitherman. Wasn't even close. Is he going to win again? No. Although 40% of the population still approve of his agenda. When you put him on a ballot with anybody else he loses, Olivia Chow is actually looking pretty strong right now. But the truth is that small agenda, taking care of taxpayers' money, based on the size of government, that agenda still sells. And if Rob Ford didn't have all these personal behavior issues going on right now, he would probably win in a walk. Because that's the new Canada, folks. Not necessarily something that wins in Ottawa but it wins in Toronto. And Toronto's bigger. Didn't say it was better so it was bigger. Toronto was a great town to live in. We also argue that there is a nascent progressive coalition emerging that diligently assembled could defeat the Conservatives. But we continue to believe that as long as the progressive vote remains split between the NDP and the Liberals, especially if the New Democrats continue to enjoy a new floor of about 25% where they are consistently, which is higher than their former ceiling. They used to never get over 20. I've been doing this for a long time. The Conservative Party of Canada has a natural advantage going into any given election. Opposition is divided. Tories have a better vote with the growing part of the voter poll. To be clear, nowhere in the big shift do we say that the Conservative Party must win every election going forward, including the next one.  I mean, anything can happen. Look what's happening in Quebec now. What I would say though is that the demographic shifts that are transforming our politics give the advantage to the Conservatives today just as previous shifts once gave the advantage to the Liberals. We believe that the Conservative Coalition is now the largest, most coherent, and most stable of any available. And that coalition will only grow stronger as Canada's population continues to shift west and more new arrivals from China, India, and the Philippines flood our cities. Some critics have chastised us for what they say as a hint of triumphalism. I don't even know what that means necessarily, but in the big shift, a celebration of this new increasingly Conservative Canada. It is true that we believe our country is stronger and more confident that at any time in our history we are also aware of the new fault lines that threaten to undermine our national purpose. The conflict between the city and the suburbs, between east of what we'll call the Ottawa River Curtain and the west. Between aspiring new arrivals and sometimes resentful native-born people, we alert readers to these fault lines and warn about their potential consequences throughout the book. These are the new agenda items for the future as the country continues to transform. Finally, while we assert that the influence of the Laurentian elites as we call the traditional governing class of Central Canada is on the wane, we also greatly admire the country that they built. The debt of every Canadian to those who shaped this nation over the span of nearly 150 years is tremendous. They set up what we're now enjoying today, but as I said before, with also their embracing of liberal immigration and multiculturalism, they created the plant of the seeds of their own demise. Lawyers advise their clients to try and reach a settlement before the case goes to the jury because juries are unpredictable. So are voters. Boy, don't we know that. Anybody who did polling in British Columbia knows that for sure. Guilty a start. The jury is still out on the election of 2015. What we believe that the big shift catalogues the forces that will be at play, we strongly advise that you pay attention to them where you risk being misled by the old narratives and home truths that no longer apply. Thank you. Well, on behalf of Dr. Ruth Bell and Carleton University, I'd like to thank Daryl Bricker again for presenting a challenging and very, very important set of ideas. We're the addition, I think, to the line of Bell Lecture speakers, and we have two guests as a token of our appreciation. One from Dr. Bell, her book, Be a Nice Girl. Well, I think we need to give them a round of applause. I've always wanted to be a nice girl myself, so this is good. And this is from the Faculty of Public Affairs. Well, thank you very much.  Thank you, everybody, for taking the time to come out. I look forward to talking to you in the reception. Thank you.