Hey everyone, welcome. In this 8 video, in topic 2 of our database class, we're going to take a look at null values and their role in database design. Next, let's talk about null values. The idea with the null value is that no data exists in a cell. It's just an empty cell. It's like a vacuum in space. There's theoretically nothing in there. This is something where, occasionally, we need to use null values, but I want to make sure that you understand that this empty value, a null value is different for a concept of a zero, a space or white space character, an empty string, etc. These are all something. These all have meaning, but a null value is the absence of data. It's saying that no data value exists for this particular cell in the table. Now, null values in the relational database world are a bit tricky because they can be ambiguous. What I mean by that is that if a value in a table is null, there's an empty cell in there, it may have, there may be empty for several different reasons. This is where this ambiguity comes from. The ambiguity here is why is this value missing? Why is there no value in this cell? Possibilities are maybe that value does not exist. In the system that we use in the United States for street addresses, just as one example, sometimes there is a second line on somebody's street address. Maybe you live at, I don't know, 123 Madison Avenue. That's the first line of the street address, and the next line might be apartment 4B or unit 17, or unit 4, unit 3. The second line of the address is there to be used as necessary. But there are many addresses, many street addresses that don't require a second line. So maybe your address is 4, 5, 6 Martin Luther King Boulevard, and that's it. So you only need that one street address line. In this case, if we were trying to design an address table, we may have that second line of the address allow null values, because there is a possibility that a person does not need to enter a value on the second line of the address. If their street address doesn't have a second line, it doesn't require one. So in that case, the value of the column does not exist, and that's the reason that values of that attribute would be null. Another possibility for null values is that the value hasn't been decided yet. So we just saw an example of adding a new employee to our employee database. Maybe in our world, in our company, we hire people, and then we put them through, say, a training program before we ultimately decide the department in which they will work. So in that case, we may want to record information about them being an employee in the employee table. So we just hired them, so now they are our employee. But so we don't yet know in which department they will ultimately work. So in that case, maybe we leave the department ID empty, because it has not yet been decided. It will be decided in this scenario after the results of the employee's training program. Another possibility is that the value of the column is unknown. This happens all the time in the era of big data. It happens all the time in scientific research. We just don't know the value of something to happen all the time in market research. So if you're into marketing or something, maybe you're doing a survey of your customers in a particular market segment, and you send out a survey, a questionnaire, and the person decides not to answer a few questions on there. So you just have to record those values as empty in your database or in your spreadsheet. So in that case, the value of the column is unknown. So lots of different reasons why values may be legitimately, doesn't exist, hasn't yet been decided, or we just don't know what the value is. So, more this reason, and because of the resulting ambiguity, we want to use or allow null values to exist on a very limited and carefully decided basis. Generally, our default position should be that null values are not allowed. So when we're designing our tables, we should, by default, assume that all values will be provided, and we need a good reason for saying that values can be null, just as an example of this. Let me switch back over here. And let's take a look at the design of our employee table. So I'll pull up the design view, and that you can see we have this column here, and the design view in SQL Server Management Studio, that specifies whether or not null values are allowed. And you can see in this case, null values are not allowed for the primary key, which is always true. A primary key by definition cannot be null because one null value is equal to another null value. Right there, it's like saying one equals one or zero equals zero. So if that possibility exists, the column cannot possibly be a primary key, because it could potentially have two rows that have the same value, both null. First name is required in this world. Last name is required. The date that the employee was hired is required. But you can see here, you can see there, that null values are allowed for the department ID. And this is a business choice. Right. What this says is that in my business, it's okay for me to have employees that are not currently assigned to work in a department. So again, this helps us to start to see how the structure of the database supports or constrains what is allowed to happen in the actual business. Right. So if in our world, we have some sort of onboarding training process for new employees, and we don't decide the department in which they will work until they've finished their training, then we need to ensure that department ID values in the employee table can be null, because once we hire an employee, we won't know in which department they will work until they've completed the training process. If, however, in our company, we always hire people to work in specific departments. So for example, I'm hiring you to be an accountant, and I know you're going to work in the accounting department, or I'm hiring you to be a designer, and I know you're going to work in my marketing department. In that case, if that's our business rule, we want to disallow null values for the department ID, because every employee that we hire will at the time of hire be assigned to a department. So we should always have a value for department ID. Right. So it depends on how our business runs. And more generally, I can say that this principle, if you expand this concept out in your minds, it tells you that once you have a solid understanding of database design, then you can look at the design of a database, and you can see in that design how the underlying business operates. I could say, okay, this business, for example, is allowed to have employees that don't work in a department, or this business requires every employee to work in exactly when department. These are things that you can learn by understanding data modeling. And by the time we finish up our class, you will certainly have sufficient skills to be able to gain that kind of insight based on a data model alone. Look at any company's data model, and you will have a great deal of understanding of how that company operates, what its rules are, what are its business rules, policies, practices, etc. So generally speaking then, again, we want to default to a situation where null values are not allowed, and we only want them to be allowed if we have a particular business reason for doing so. Naturally, if we're talking about a foreign key, and the relationship between tables is optional, then the natural outcome of that is values of the foreign key column must be allowed to be null. So let me just illustrate that visually over here in SQL Server. So in this current design, you can see that we have a department that are connected to employees, and they're linked together by matching values of department ID. So let's keep that in mind. However, if I take a detailed look at the design of the employee table, we can see that currently null values are acceptable for department ID. This means that I can have employees that are not assigned to a department. Another way of thinking about that is that this relationship that we see between the employee table and department table, that relationship is optional. So if this relationship is optional, that is, if an employee is not required to work in a department, then the way that is implemented is values of the department ID are allowed to be null. In that case, an employee in the employee table is not necessarily connected to a department over here in the department table, and that lack of connection would be indicated by a null value for department ID. So when nulls are allowed, as they currently are in this design, I could, for example, go into the employee table and set one of these. Here's my employee. And we can set it to null. Let us just complaining about should be allowed. Let me try this in the employee. We'll put Dr Cooper back in here. It may be because I didn't save the changes. That happens quite frequently when I'm switching back and forth between diagram mode and this design mode. So we know all these values are required. All right. There we see we have a, we've committed that row and we have a null value in there. So that value is acceptable here because we are allowing null values. So this employee is not currently connected to any department because we allowed null values for the department ID.