Hey, everyone, welcome back in this video, part seven of topic four in our database class. I'm going to talk about binary relationships and importantly, cardinalities and their application in our entity relationship models. Let's see some binary relationships and we'll take a look at these cruise foot symbols and we'll see how they can be used to convey design information easily to the viewer who in this case is us. So, this first example, we have a one to one binary relationship. That's one to one because both ends of this relationship line are indicating the concept of one. And in this case, we're looking at a relationship between an employee and a locker. So, if we read the relationship in this way, we would say each employee has one locker or is assigned to one locker because the end of this line here indicates the concept of one. And if we read the same relationship in the opposite direction, that is in the direction from locker to employee, we would say something like each locker is assigned to or belongs to one and only one employee. So, a locker in this example cannot be assigned to more than one employee at a time and that employee cannot have more than one locker at a time based on the design that we see conceptually illustrated here. I do want to just take half a step back for a moment and I want you all to understand that when you look at a single relationship line like you see here, there's just one relationship line connecting these tables. Whenever we see that in a binary relationship, we need to keep in mind that we can read that relationship in two different directions. And I'm using the verb read here. I don't know if there's a better one, but maybe describe. But the whole idea is that this relationship is not just static but it just depends on our perspective. If we're looking at it. Our perspective is from the direction of employee to locker, then we would describe the relationship in one way, we would say like an employee has a locker. But if we look at that same connection in the opposite direction, that is in the direction from locker to employee, we would say something like each locker is assigned to one employee. So this idea of reading a relationship in both directions is important. It helps us to understand to more fully understand the nature of the connection between the two entities that are involved in the binary relationship. So that's a one to one easy enough. Here we see it one to many. One to many's are by far the most common type of relationship that we see, at least on average in entity relationship diagrams. And the idea here is to convey the idea or convey the notion that each entity instance in one of these entities is potentially related to multiple instances in the other entity. So in our little world here, what we're seeing is over seeing illustrated is a connection between employees and departments and this is indicating to us this relationship line conceptual relationship line that we see here. But this is a one to many relationship in the direction from department to employee. So if we read this relationship in that direction, we would say each department has many or potentially has many employees, maybe is allowed to have many employees would be a better way of understanding it in this particular business context. Each department is allowed to have many employees and we have our symbol for many here if we read this same relationship in the opposite direction that is in the direction from employee to department, we would say each employee belongs to or works in just one department. And we need to consider both directions if we want to fully understand the nature of the relationship between these two entities in the direction from department to employee, it's a many that is each department can have many employees in the direction from employee to department, it is a one. Each employee works in just one department. Here is next we're going to see a conceptual many to many relationship. And again, I want to I know I've said it before, I need to just say this over and over and over again, this is a concept notion of many to many, but we had not actually implement that. So that is a complete entity relationship model would never contain something like this because it simply cannot be implemented as a physical database. Right, we understand it easily as humans. Like the relationship here between items and suppliers is many to many. But we cannot implement this directly in a real world database so conceptually though it's easy for us to understand. We think of the relationship in both directions and the direction from item to supplier. This tells us that each item can have many suppliers. And if we look at the same relationship in the direction from supplier to item, we say each supplier can provide us with or supply us with many different items. Conceptually as humans it's easy for us to understand this notion of many to many, but we cannot implement this directly in a real world relational database. Instead, whenever we encounter a many to many situation like this to actually implement it, we need to add a third table. To sit between the other two tables. So let me just illustrate that here with this particular example since it's currently on our minds. So we have items and we have suppliers. Right, and then these are related to each other so here's my items over here. And these are my suppliers. That's supposed to be a supplier. And there we go. And currently we have this kind of design which shows them conceptually related as a many too many now we cannot again actually implement this instead. But we need to do to implement this conceptual many too many in a real world database is we need to add a third table. So let's take a look at that sort of design here. So let's take a look at the table in between. And we'll interconnect these with relationship lines. We'll see that the many side flips to touch the table in between. Now I will label the table. That's a line that's Eminem can borrow from me label the tape. And there's a lot of items there on the left suppliers over here on the right. And in the middle we might have this third table might be cleverly named something like I don't know items supplier. The combination of the two attached tables. Okay, so in summary then. This is easy for us to understand as humans there's a many too many relationship between items and suppliers. However, we are unable to implement that directly in a relational database to implement it. What we need to do is add a third table, a lookup table and intersection table. That will allow us to keep track of the connections between items and suppliers. So again, you can imagine some sort of design here where if we see it from a data perspective. Where it might look say a little something like this. So our item supplier table might have an item ID. And a supplier ID. And if we believe it at that, just that simple idea and filling in these things we might have like item one is provided by supplier. And the supplier. And item two is provided by supplier number 10. And item three is provided by supplier 11. And item four is provided by supplier number. 11 and just for the sake of. Comprehensive example item two. Is also provided by supplier number 11. So in this case, we use this intersection table item supplier, which we're showing here in the middle as a way of physically implementing this conceptual many to many relationship. So you can see as illustrated here that by using this lookup table in the middle, I can figure out which items are supplied by which suppliers. So if I ask the database, hey, database, which suppliers provide me with item number two. Then I can look out here in my item supplier table and say, is this item number two, no, is this item two, yes, who are they? What about this one, no, this one, no, this one, yes, okay. So I can then say, you associate its supplier IDs are 10 and 11. Like I then follow that relationship over to the supplier table and find out more information about suppliers 10 and 11 should I choose to do so. So this is how we actually implement these many to many relationships. And we'll see that again and again, again, during our remaining time together. All right. At this point, we can move beyond the conceptual relationships that were illustrated in the past several slides. And we can instead enhance our capabilities by learning about cardinalities. So we'll enhance our ability to convey design information and we'll also gain more subtle database design capability by learning about cardinalities. So when we are looking at the relationship between two entities, that relationship, as we saw in the past several slides is typically named classified or described by its cardinalities. Now, cardinality, as I would hear is a word that means count, for example, the number of items in a set. So this kind of comes out of set theory, cardinality refers to the number of items in a set. And what we're doing here is we're using this term to refer to the number of items that are allowed to participate in a relationship between two entities. Now, when we are describing a relationship between two entities, we will typically loosely rely on its maximum cardinality for that description. Okay. So if I say that a relationship between two entities is, I don't know, one to many, like what we see here between employee and apartment, those are the maximum cardinalities that we're referring to. Right, the one is the maximum for one part of the relationship and the many is the maximum for the other. So we're saying that the maximum number of employees that can work in a department is many. Okay, that's the upper limit. Whereas the maximum number of departments in which a specific employee can work is one. That's the upper limit. So if I loosely say something like this is a one to many relationship, those concepts, those cardinalities of one and many are the maximum cardinalities, telling us the largest number of rows in one entity that are allowed to be related to or connected to a number of rows in the other. So the maximum cardinality here between department and employee is many indicating that a department can have a maximum of many employees and the maximum number of departments in which an employee can work is one. So maximum cardinalities are great. Importantly, they indicate the maximum number of entity instances that may may participate in a relationship. Because it's an upper limit. We're not requiring that a specific number participate in the instance of the relationship. So for example, if I have a one to many relationship like I see here, this does not require that each department have many employees. It's a May, it's an option. A department may have many employees, but it might have just one or maybe it has none. Maybe it's an old department that we don't have anymore and we just keep it in the database for historical reasons. So it's an upper limit right it's not a requirement. We could, for example, maybe we're a small company and we have just one person who works on marketing. So this sort of design would allow our marketing department to have many people, but it does not require the marketing department to have many people. So it's perfectly acceptable to have just one person in the marketing department with this kind of database design. So that upper limit, the maximum cardinality is a May, right, it specifies conceptually what the upper limit is allowed to be but does not wire the upper limit to be that low. So maximum cardalities will always be one of two things. One or many. So pretty small set of things that you need to remember there right maximum cardinality can be one or it can be many. That's it. So as it says here we can enforce the maximum cardinalities by using the unique status in a foreign key. And I'll return to this concept in a moment when we take a look at minimum cardinalities, but I think it'll be important for us to understand that how we actually can enforce these notions of one or many for maximum cardinalities in our physical databases. When you build a database in SQL Server, if you want something to have a relationship of one, then we would implement a unique requirement. If many are allowed, then we would not implement a unique requirement. So we'll see that momentarily. But for now let's move on and talk about minimum cardinalities. So a minimum cardinality then is this notion of the smallest number of rows in one table that must must participate in a relationship with another table. So this is a must in contrast to the may which we saw with the maximum cardinality. And we specify a minimum cardinality we're specifying the minimum number of rows in one table that must participate in a relationship with another table. Now again, we have just two options for minimum cardinality, those being zero or one. And what this means is optional or mandatory. So another way of thinking about this is our null values allowed or not. That will determine the minimum cardinality for a relationship. And as you can see here we enforce minimum cardinalities based on the null status of a foreign key. So let's take a look at these concepts then in an illustrated way so that we can understand what we're talking about here when we say maximum cardinalities are enforced by the unique status on the foreign key. And minimum cardinalities are enforced by the null status on the foreign key. So let me just string up a little blank screen upon which we can draw and we will visit a good friend of ours a familiar example that we've seen many many many times. And that is the relationship between an employee and a department. Now we should be very familiar with this by now having seen it so many times. And let's say that we have a one to many as we see here and then we'll take a look at some data. So just for the sake of keeping things simple, let's imagine that we have an employee ID as our primary key. And we'll have a department ID as our foreign key in the employee table. And then over here the department table will have just a department ID. So here remember last time we learned about this idea of just depicting simply our entities with only the keys and you can think of that's what's going on here. This shows us how these things are interconnected. So I can then fill in some data values. Maybe we have partment 101 102 and 103. We don't need to provide any other non key data because we're just interested in the connections here right i'm not interested in the name of the department or the location or maybe the departments phone number. Similarly i'm not interested in my employees names or what their phone numbers are email addresses or anything like that. So let's first take a look at maximum cardinalities and we said that to remember this is I mean, label this so that we all can keep this clearly in our minds in this particular example. Department ID in the employee table is a foreign key. So this is the foreign key in the relationship between employee and department and it is the. unique status and the null status of this attribute department ID that will be used to enforce the minimum and maximum cardinalities. So let's start with the maximum cardinality which we currently see illustrated here as many. So, what does it mean from a data perspective. If i'm going to allow a many relationship between departments and employees, what it means is that a specific department ID. Can be duplicated. Multiple times. In my foreign key column. Okay, so maybe i don't know employees one in three work in department 101. And maybe employees two and six and five work in 102. And then maybe employee number four works in department 103. So we have some duplicate values here in our department ID column. Right, we can see easily right 101 is listed here 101 is listed here that's fine here got some 102s that's good so the point is that the same value appears multiple times in this column and that is. From a data perspective, what a many looks like as a maximum cardinality right. Means that we can have multiple values right each department can appear each department ID can appear multiple times over here in the department ID column as a foreign key value in the employee table. So, tying this back to the slide. What this is saying then is that to have a many. We cannot enforce a unique constraint on this column. Right, it must be non unique if we were to like employee ID is unique right there's no duplicate values there. But department ID must be non unique in order for the maximum cardinality to be a many. If we were to enforce a constraint on this column that its values be unique then suddenly the maximum cardinality is no longer many it becomes one. Okay, so let's imagine what that looks like. I make this a unique column. So let's say that we're going to make that unique what the effect will be is first it will change our. Get rid of that line but. Very precise it will change our maximum cardinality to one. That is unique and I won't be able to have any duplicate values over here. Because if it's unique that means each value appearing in the column can only appear once. All right, so if department ID is unique then. I can only have one department 101. I can only have one department 102. I can only have one department 103 in this column that we see here. So if for my foreign key department ID if I force this to be unique it means that the maximum cardinality is one. This is how we enforce that that maximum cardinality. Now let's consider the concept of a minimum cardinality and minimum cardinalities can be either zero or one. Now what we're seeing remember the minimum cardinality tells us the minimum number of rows in one table that must be involved in a relationship with another table. Okay, must be connected to some other one. Now what is currently illustrated here is if you're thinking of null status is that null values are allowed in this design. Because we can imagine that these are just empty cells here right null values. So if null values are allowed what that means is that the minimum cardinality is zero. Because it's saying that it's possible for an employee to exist without in this case having a department there's not a requirement that an employee work in a department. So the minimum number of departments that must be connected to an employee is zero. So minimum of zero. So now imagine a world where we do not allow null values. And if we do not allow null values then what we currently see illustrated cannot exist instead we would be required to have a value in every cell here for the foreign key for a department ID. Okay, so we might have something that's like this. So if we disallow null values that is if we say department IDs are required for employees, then what the effect is that we have a minimum cardinality of one. Right, because every employee must have a department. So the minimum cardinality there is one. Interesting. So hopefully you can see through this illustration how the use of null status allows us to enforce minimum cardinalities. That is our null values allowed or not. If they are allowed, the minimum cardinality is zero. If they are not allowed, the minimum cardinality is one. And similarly how we can use the unique status of the foreign key to department ID in this example. As a way of enforcing the maximum cardinality. If the foreign key is unique, then the maximum cardinality must be one. If the foreign key is not unique, then the maximum cardinality must be many. That's how we can actually enforce it in our physical database designs. So we're turning to our slides. So again, just connecting this visually to many people are visual learners. So those concepts were noted here on this slide. Maximum cardinalities are enforced by the foreign keys unique status. And then once we talk about minimum cardinalities, that same concept was noted here. Minimum cardinalities are enforced by the foreign keys null. I find this stuff interesting. Somebody figured all this out. And we can use these designs to enforce the number of instances of one entity that are allowed to participate with the other.