Microsoft Access 2021: Database Design Principles

Name: Microsoft Access 2021: Database Design Principles
Author: John Varan

Learn how to work with null types, calculated values, and how to normalize a database.

Preview the first lesson free — get full access to all 6 lessons.

Course: On-Demand

Hello, welcome to Knowledge City's course on Microsoft Access 2021, Intermediate. My name is John Veron, and in these lessons, you will learn about database design principles. In this lesson, I will show you about the difference between null and empty values and how to properly work with them. A term that you will encounter often when working with databases is null. In the context of a database, a null value usually means that there is no value. It is the absence of a value. This is obviously only applicable to columns that are not required, as a required column, by definition, means that it cannot have a null value. However, in some cases, things get a little more complicated. Sometimes a null can also represent the idea that the value exists but is unknown. So therefore, a null usually means the value doesn't exist, but sometimes it can mean the value might exist but is currently unknown. To make things even more confusing, this definition can mean different things based on the data type of the column and also what it represents in the real world. Let's look at these different scenarios so we can try to make sense of them and clear things up. Let me just create a sample table here and I'll just call it sample. So first let's look at the case where the data type is a date. For example, it could represent a date of birth and this will be date time. If this table holds a list of people, then it's logical that null would represent that the date of birth exists but is not known. Obviously, that's because everybody has a birth date. So the concept of the birth date not existing would not make sense. However, if this table is used to hold something like a list of clients, which could be a mix of real people and companies, then the null could represent that the date of birth does not exist for a company or that it does exist but is not known for a person. As you can see, the context of what the data represents in the real world affects the definition of null. Next, let's look at an example where the data type is a number. Rename this to number of orders and make this type number. In this case, the value we put in here represents the number of orders placed by a client. This is a little different from the data type date time case because while there isn't a data date to represent nothing for numbers, there is. We can use zero. That means in this situation, a null should mean that the number of orders is unknown while a value of zero should mean that the client has no orders. I say should because that's how proper database design should be but it can also be common practice for people to leave this value as null when there are no orders. While doing this isn't necessarily wrong, it does result in losing the distinction between the value being zero and unknown. It is more clear to maintain this distinction in case it would be needed in the future for queries and reports. Finally, let's look at an example of a string data type. This can be referred to by different names depending on the database you're using. string, text, varchar, nvarchar, and others. In Microsoft Access, it is called either short text or long text. Both of those behave the same for the purposes of this example. So let's say this column is a middle name and we'll use short text. In this case, the column represents a middle name and null value here would represent that the person's middle name is not known, or that it's unknown whether they have a middle name. An empty string would indicate that it is known that the person does not have a middle name. An empty string is analogous to the value of zero for numbers, but it looks more confusing because there is no visual symbol to represent it. And unfortunately, Microsoft Access displays both empty strings and nulls the same way, just as a blank. Hopefully, it is now clear what null means in different contexts. If not, try to go back and repeat some of the examples on your own. Thanks for watching. Stay tuned for the next lesson where I will show you how to design database tables for calculated values.

Intermediate Provider : John Varan 6 Lessons · 34m in English

Course Description

Even if you understand the basics of how databases work, applying that knowledge in real world contexts presents a new set of questions. For example: what kind of data should be stored in your database? What are null values, and how are they different from empty values? What is database normalization, and why is it useful? The better you understand how different kinds of data functions work, the more prepared you will be to build a database that works. In these lessons you will learn several pieces of crucial database know-how that will help you better address real-world data storage challenges.

In this module, you will be introduced to a few of the most common techniques of efficient database design and how to best use them. You will learn the difference between null and empty values, and how to design tables to deal with calculated values. We’ll also go over database normalization. You will learn what normalization is, why it’s useful, and the most important stages of normalization– first normal form, second normal form, and third normal form– and how to implement each.

What You'll Learn

Normalize a database and apply first, second, and third normal form
Design tables that handle calculated values
Work with null values when designing tables
Distinguish between null values and empty values
Apply common techniques of efficient database design in Microsoft Access 2021

Key Takeaways

Database normalization is a technique whose most important stages are first normal form, second normal form, and third normal form.
Null values and empty values are different, and the course explains how to tell them apart.
Tables can be designed to deal with calculated values as part of efficient database design.
Understanding how different kinds of data functions work helps prepare you to build a database that works.
The course introduces common techniques of efficient database design and how to best use them.

Frequently Asked Questions

What does this course cover?

It covers common techniques of efficient database design in Microsoft Access 2021, including the difference between null and empty values, designing tables for calculated values, and database normalization through first, second, and third normal form.

Who is this course for?

It is intended for learners who already understand the basics of how databases work and want to apply that knowledge to real-world data storage challenges.

What is database normalization in this course?

Normalization is a database design technique the course explains in terms of what it is, why it is useful, and its most important stages: first normal form, second normal form, and third normal form, along with how to implement each.

What lessons are included?

The lessons are Null vs. Empty Values, Calculated Values, Normalization, First Normal Form, Second Normal Form, and Third Normal Form.

What skills will I gain?

Skills include Database Architecture, Database Design, Database Modeling, Database Programming, Microsoft Access, and Microsoft Data Access Components.