Hey, everyone. Welcome back to topic one in our database class. In this video, part eight, we'll focus on the DBMS and database applications. These are two components of a database system. So the database management system, remember this is the third component in our broader database system. We had that back here. It's a third component over here on the right side DBMS. And the DBMS, as we saw in that diagram, is the gatekeeper, right? It sits as the intermediary, the only entity between database applications and the database. So the DBMS is an extraordinarily important component, because it is the only one that has access, direct access to the data, and it is the only entity in the broader database system that can manage and control what's happening in the database. So it is the DBMS itself that serves as the administration tool for the databases to which it is connected. So we use the DBMS to create databases, to process data, run queries, do updates, delete data, et cetera. Design work is done in the DBMS. We want to create new tables or relationships between them. And administration. So tasks like backup and recovery, a security, creating user accounts, creating database rules, et cetera. These are all things that are handled by the DBMS. But there's lots and lots of things. This is not a complete list, but nevertheless should give you an impression of the broad set of responsibilities that the DBMS brings into this database, this broader database system. We can create and delete databases. We can modify existing databases by creating and deleting tables, relationships between them, but we can add and remove columns to the database. We can impose constraints, create rules that we can enforce in there. We can control concurrency, the DBMS is responsible for that. This is a scenario where we want to have many people using the same database at the same time. That's a very technically challenging thing to do. We'll learn about that once we get to the sixth topic of our class, but obviously the value of these databases increases dramatically as more and more people can use them. Imagine how valuable something like Amazon.com would be if only two people at a time could use it. The only way that we can have millions of simultaneous users is if the underlying database has the ability to control concurrency at that level. Again, I mentioned previously security, other major and critical tasks like backup and recovery. Again, these data are extraordinarily valuable. So we need to design and implement intentionally a robust backup and recovery plan. In case something goes wrong, anything from something benign, like someone accidentally spilled their coffee on the database server to some sort of natural disaster, like there's a flood or a fire or an earthquake, or there's some kind of malicious activity. Someone may try to physically steal these computers. They may try to break in and steal the data internally, delete things. So we need to have a good backup and recovery plan in place. All right, one of the things that we saw here on this slide was that the DBMS can enforce rules. Or more broadly, we label these things constraints. To a set of boundaries that the database is required to operate it. And the DBMS can enforce many different types of constraints. One type of constraint, for example, might be what we call a range constraint. I don't know, imagine that you have a year column. Let's make it a little more specific. Let's say a year of birth. In what year was a one of your customers born? Now, we in order to try to protect the quality and the integrity of the data in our database, we might decide to set a reasonable range of values for the year of birth. And I'm sure many of you have seen this sort of thing. What's realistic these days for a minimum? I don't know, maybe we say, 100 might be a minimum year of birth for our current customers. There weren't going to be too many people alive today that were born before 1900. And then we might set some realistic upper limit 2021. So we could set a range of values like this and say that when the customer is providing their year of birth, it must fall somewhere between these two boundaries. And we again, we do this to try to protect the integrity, the quality of the data in the database. We don't want someone to be able to say, for example, that they were born in the year 63, or that they were born in the year 41,208. Like these are obviously invalid values for a year of birth. So we can't have that, we can't have that. We want to try to protect the quality of the data by implementing these types of constraints. So a range constraint is one type, as we'll learn throughout the class, there are many more. But for now, I'll briefly introduce the notion of a referential integrity constraint, since this is a useful concept as well. So one of the great things that these relational databases can do is that they can, optionally, enforce what is called a referential integrity constraint. And what this does, put simply, is it ensures that if we're going to try to link the rows of data in tables together, based on matching pairs of values, this just ensures that those connections we're trying to establish make sense. And the making sense is an interesting concept, but I'll try to illustrate this in a way that, maybe I can go back far enough to recover my previous drawing in a way that will hopefully clearly reveal what is going on when we talk about referential integrity. So let's return to this. Now, what referential integrity says, put simply, is that if we are going to try to connect the rows of data in one table to the rows of data in another table, like this connection, for example, and this one here, then we need to have appropriate matching values. So what does that mean? Let me just see if I can't illustrate this in a way that will be perfectly clear. All right, so imagine, we have our enrollment table here, and we're going to try to add a new record to this table. And if we do something like this, let's say that to Dan, there's a new record, and Dan is going to take class number 101. Let me keep my trying tools, sorry about that. Dan takes class 101. Now, from a referential integrity perspective, this is great. This is perfectly acceptable, because when I try to add this new row to my enrollment table, the case is going to check, and it's going to say, okay, the proposed value for customer ID is one, and it's going to look over here and say, hey, is there a one in this table? And in this case, it's going to say, yes, that exists. So it's fine. It's a valid value. And the same thing would apply to our check of this value. So we try to say that, so this customer is taking course 101, so we'll look over here and say, is course 101 a valid course? That is does it exist, and in this case it does. This is fine, this is good. However, if we try to do something like this, let's say that we decide that to Dan, sorry, I'm sorry, I'm sorry, we'll say, Dan is going to take class 103. In this case, this would violate the referential integrity constraint, because there is no course 103 up here. So with referential integrity on, if you tried to enter these data, this row of data into the enrollment table, the database is not going to allow you to do that. It's going to yell at you, and it's going to say, hey, genius, you can't put these values in here, because there isn't a course ID 103. So this is the whole concept of referential integrity, is that we need to have appropriate values in the related tables in order for data to be entered, as we see here, and this would not be allowed, but this one up here would be allowed. And I hope you can appreciate how having this automated capability can really help to prevent a lot of data entry errors. So it's just one of the many mechanisms that the DBMS provides for helping us to protect the quality and the integrity of the data in the database. So I would similarly, if I tried to do something like this, let's say that I had 102, which is a valid course, but I put in, I don't know, customer ID 10, which is not a valid customer. There's no customer number 10 over here. So the database would the raise an error there as well. Kind of say, hey, genius, there is no customer number 10, so you can't do that. Thereby protecting the quality of the data here in my enrollment tape. So we want the data in there to remain of high quality, which means we have to have matching values. We can't allow something like that. All right, so our final element of these database systems are the database applications. And this is simply the computer programs that our users are using in order to accomplish some kind of goal. Could be a work related goal, recreational goal, some practical life goal, I don't know, doing online grocery shopping or something like that. Hey, but these are just the apps that we as humans use and the application programming interfaces that machines use in order to accomplish some sort of task or achieve some sort of goal. And it is these applications that can talk to the DBMS. The users themselves are not allowed to talk to the DBMS directly. We go through a database application program and that program talks to the DBMS, which in turn talks to the database. So database applications do lots and lots of things. Again, we all use these almost every day of our lives. If you're sending email, that's a database application. If you're doing online shopping, that's a database application. If you're listening to music on your phone, that's a database application. And if you're sending text messages, if you're, I don't know, Netflix, right? These are all database applications. Ways that allow you to ultimately interface with the data layer is data stored in some database somewhere. So yeah, lots and lots of things go on with these database applications. Hello, just some visualizations to help solidify these ideas in your minds. I'm sure that most of you have seen something like this at some point in your lives, right? Just enter some data, right? Enter your username, enter a password, confirm your password, enter an email address, just a little data entry form. Okay, so this would be an example of a part of some database application. And then you can imagine that the values we fill in here, like username and password and the email address, assuming that they survive the validation process, these will ultimately end up stored in some database somewhere. So the database application, probably when this is submitted, would make a request of the DBMS and check to see if, hey, does this email address already exist? Maybe it does some other tests to survive a security process. Is this a sufficiently complicated password, et cetera? If so, they will then store that information in the appropriate location in the database so that it can be used at a later point for some business purpose. Just these types of interface forms are parts of database applications. Here's another one. I pulled this from Wikipedia and we've all seen these things, these sort of auto complete options. So you start typing a few letters and it's going to give you a list of matching options, right? And these are just done with very simple, SQL-based searches. And so I just, once I have a sufficient number of characters out here, it might be two, it might be three, I start passing those characters, using a wildcard search to the underlying database by means of a SQL query. And the matches come back, which I can then display here to allow my user to have a better experience on the website. If you're wondering Wikipedia, which is one of the most heavily trafficked websites in the world, he uses a database called MySQL on the backend, which is a relational database. And it's completely free. So it's pretty amazing. You have this open source database. I know that there are commercial versions of it now, but Wikipedia being a nonprofit company uses that to run one of the most visited websites in the world. But this is another example of database applications. This is a database application whenever you're using Wikipedia. So you have to have reports, right? These types of things are common. They help human beings to make decisions. They're used to record summary information, et cetera. These days, again, a lot of this is done dynamically in the form of managerial dashboards.