Hey, everyone. Welcome back to topic number two in our database class. In this video part two of topic two, we're going to take a look at characteristics of relations. All right. Now, at this point, let's talk about this notion of a relation and identify the properties or characteristics of a relation that distinguish it from a typical table. Now, the first thing to realize here is that a relation is a table, a two-dimensional table to be specific. So it will consist then of rows and columns. It is a matrix, a mutable two-dimensional data structure, and we're all familiar with these. You can think of it like a spreadsheet if you prefer. It's going to consist of rows like I'm drawing here, which are the horizontal data structures and we have columns like I'm drawing here, which are the vertical data structures. So in this case, I have drawn a table that consists of five columns and five rows. But the question remains, is this table a relation? That is the question. So what then makes a table or qualifies a table as a relation? So you'll see here that a relation is a table that has a certain set of characteristics. And all of these properties must be true in order for a table to qualify as a relation. And what this means from a big picture perspective is that all relations are tables, but not all tables are relations. A table must meet all of the characteristics that we see listed here in order to qualify as a relation. So let's review these in turn. First, in a relation, the rows of data each represent an instance of an entity. So what this means is that if I have an employee entity, each row of data in that table will represent one unique employee. In an employee entity, that's the thing of it is the framework for storing information. Each row in there represents an employee, which is one instance of that entity. So rows contain data about instances of the entity. Like each row is an employee or each row is a customer or each row is a product. Columns, on the other hand, are attributes of the entity. So we may have the first column might be an employee ID. And what that means is every value stored in that column is an employee ID. The second column might be a, I don't know, a name. And that means every value in that column will be a name. Third column might be a phone number. Every value in the third column will be a phone number. So columns are attributes of the entity. Rows represent instances of the entity. Now, another characteristic of a relation is we do not allow multivalued attributes. So that means each cell in the table holds a single value. And this notion of a single value, what constitutes a single value depends on the data type. If I choose a data type, let's say I choose an integer as my data type, I could store the value in there, I don't know, 536. So that could be it's three separate digits, but it's considered a single value. Because it's just a one number, even though in this case, it's composed of three separate digits. The same thing applies to, say, a text based field. So let's say, for example, that, I don't know, we want to install employee names. And that we store an employee name in our database like Daniel, right? This consists of six letters, but because we're using some kind of multi character based data type, this is still considered just a single value, right? It is a single name for purposes of meeting this definition. If we were trying to store, say, multiple names or multiple phone numbers or multiple email addresses in a single cell, then we would be violating this rule. And in that case, the table would not qualify as a relation. So each cell must hold just one value where the nature of that value depends on the data type that has been chosen for the corresponding column. Speaking of which, all values in the column need to be of the same data type. So if I have, I don't know, let's say that I have date of birth column, where I'm recording the date on which somebody was born. What that means is that every single value in that column will be a date. It will be of the same data type. Similarly, if I recorded something like, I don't know, like you're, say, an email address, that's going to be some sort of text based column, because it can hold text values. And every single value in that column would be of that same data type. We're not going to store an integer in one cell within that column and some text in another one and a date in another one after that. Right. Now they're all going to be of the same data type. So all values in the column need to have the same data type. Each column within the table needs to have a unique name. So if I have, let's say, I don't know, I'm recording email address for people. So I can have a column named email address, but I can only have one column in that table named email address. I can't duplicate the names of my columns. Now this is a common thing with which we're all familiar, like you have, for example, folders on your computer. Right. And each file that is stored inside of a folder must have a unique name. Right. Each folder must have a unique name within its parent folder and so on. This is required to ensure that we don't have any ambiguity. We need to have certainty as to what we're referring to. You cannot have two files with exactly the same name in the same folder on your computer. The same thing applies here. We cannot have two columns with exactly the same name in a table in our database. Because if we did, the database engine would have no way of distinguishing between them. If I said, give me the email address of this employee, it would say you have two columns named email address. So I don't know which one you refer to. So every column must have a unique name within its table. This is another important feature here within the table. So that means if I have tables, I can repeat the names of columns between tables. Okay. So let's say that I have an employee table and maybe the name of one of my columns there is just ID. Right. Where I keep track of my employee IDs. I might also have a customer table. And in that customer table, I can have a column named ID. Even though there is also a column named ID in my employee table. So as long as it's in a separate table, it's okay. We just can't have duplicate column names within the same table. That's disallowed. Two other properties. The order of the columns and rows does not matter. That is the way that we store data in the table. It doesn't matter. We could rearrange the columns. We could rearrange the rows without gaining or losing any information. If the order of the columns matters or if the order of the rows matters, that is, if it carries information, then the table does not qualify as a relation. The order must not matter. We should be able to randomly order the rows in the table and not lose anything. We should be able to randomly order the columns and not lose any information. And finally, no two rows within the relation can be identical. And what this means is that the combination of values across all the columns for a particular row must be unique. There must be something unique about each row that distinguishes it from every other row in the table. Let's see some examples. So here we see a table that qualifies as a relation. So it meets all of the criteria that we're just listed on the previous slide. So this is, say, an employee table. Each row in the employee table represents a single employee, an instance of our entity. So if this is an employee entity, each row holds information about just one instance of that entity. In this case, each employee, each column is an attribute of the entity. So this column contains only employee numbers. This column contains only first names and so on. Each cell contains just a single value. So here's the value 100, use F, a song, just one value per cell. The order of the columns in the rows does not matter. All right, so we could randomly reorder these four rows and we wouldn't lose or gain any information by doing so. Similarly, we could switch the order of the columns around. We could say last name employee number first name and we wouldn't be gaining or losing any information. And we can see that every single row combination of values that together comprise a row is unique. There is only one row in this table that has employee number 100 and first name, use F and last name, a song. All right, that combination of three values only exists one time in this entire table. And the same is true for every other row. So there's something about every row that makes it unique from every other row. The combination of values is unique. So this is just a simple relation. This is a table that qualifies as a relation. Here's an example of a table that is not a relation. Okay, so this is one that contains a multi-valued attribute, which means we have we're trying to store more than one value in a cell. Okay, so if we have a phone number column, there should be one phone number per cell. And here you can see we have two phone numbers that we're trying to cram in here. So this does not qualify as a relation because it violates that property. Here's another one. In this case, we have identical rows. So this table cannot qualify as a relation as well. Okay, so here you can see. In this row, we have employee number 100, phone number 3356421, last name has gone. And we have exactly that same combination of values here. So these two rows are identical, which means we have no way of telling them apart. Right, there's nothing about one of them that is different from the other. So if we have a table like this that can hold identical rows, then it is not a relation. It's just a table. All right, it's one of the challenges that we face when learning any sort of technical topic is the language. Right, every technical topic has its own jargon, so instead of specific terms that mean something in that context, that's probably different from what it means in another context. So I wanted to put these synonyms up here just so that we have a reference. And we can see that these terms are they're not technically perfectly interchangeable, but we can use them as such. Throughout most of our time together in this class, I will talk about this concept as a table. Okay. But we can also talk about it as a relation or an entity, or you might occasionally also rarely hear this same concept referred to as a file, but that's quite rare. But rarely next to that. But generally you'll hear table entity relation, they all generally refer to the same cognitive concept. The same is true of rows or records or tuples. So if I say this table contains three records, that's the same as saying it contains three rows. Tuple is a term from computer science, so we're not going to hear it or use it much in this class. But if somebody that you're talking to in, I don't know, say a work environment, study databases in the context of computer science, they may say this table contains three tuples. That means rows. And so that's our horizontal data structure, our vertical data structure. We can call it a column, we can call it a field, we can call it an attribute, we can call it a property. So there are several different words that we can use to represent this idea as well. But the point is just to make these connections in your mind that if I'm talking about a table, I'm just using a generic word for an entity or relation. If I use row, I could swap that with record and mean the same thing, or I might say column, I might say attribute, property, etc. These are all the same kinds of things. So we just grouped them together. But I will do my best remain consistent throughout the class. Most of the time, for the sake of simplicity and familiarity, I will use these three terms to describe these concepts. So I will talk about tables, rows within tables, and columns within tables.