Hey, welcome back. We are continuing our adventures in topic one of our database class. In this video, part seven, we'll discuss users and databases, which are two of the four components of a database system. Beginning with users to reiterate, users do not necessarily need to be human. It's very common these days for machines to be users, creating this service oriented architecture out there, where organizations are loosely coupled to each other by means of APIs and things like that. So in that case, lots and lots of these users are machines bots rather than human users. But what do users do when interacting with these database systems? Generally, they will use one of these database applications, be it a website, an app on their phone, where installed on their laptop or desktop computer for the purpose of keeping track of things. You can imagine on your phone, you have things like your contacts, people that you know, and each of those people has things, little items of information that you want to record about them. Maybe they're telephone number email address, if you have a photo of them in there or additional personal information, like their birthdays, etc. These are commonly stored in contact lists and phones now. We have a database then of contacts that you were calling or texting application is relying on in order to keep track of this information. I mean, similarly, if you're using a database application like Amazon, you're doing some online shopping, we use it to record things like our mailing address, payment methods, past orders, and so on. Amazon uses the applications to manage things like inventory and so on. So they're all talking to the same underlying database. So this is what users are up to with these database applications. And we as humans typically do this through user interface forms. So we can enter information and read information that may be an interface on your phone and maybe an interface that's rendered inside of a web browser, it may be some custom design software, or it could be something as simple as a console input. If you've ever worked with Linux or OS power shell, these types of tools, that's perfectly possible to interact with the computer. Just by sending it text commands, you don't necessarily need to have buttons to click on or screens to scroll through on your phone. It has lots of different ways of interfacing with the computer as a human user. But machines will, with our database applications in a different way. Typically, these days it is done in the form of some standardized application programming interface, like a RESTful API using techniques or technologies like JSON as the underlying carrier for information across the network. And then also humans like to produce reports, particularly managers. This doesn't necessarily mean that we're generating a report to be printed out or displayed or discussed at some kind of company meeting. These days reports also refer to dynamically generator reports that we're viewing, for example, inside of a web browser. So a lot of these sorts of managerial dashboards and things that are and then created using tools like Tableau, using tools like Power BI, are considered reports as well. So these kind of show in real time what is happening inside the organization. And of course, these types of dashboards, which we can classify as reports, are relying on data from a database in order to generate the lovely visualizations and interactive drill down tools and things like that can appear in these Tableau or Power BI types of dashboards. So this is what our users are up to. On the other end, remember we have these four components. So we looked first at users over here on the far left. Next, we'll look at the database, which is over there on the far right. And we'll look at the two connecting pieces in between. So what is a database? Well, a database is it's not just a place for storing data. It has additional properties as well. For example, databases are self-describing. And in the context of the relational database world, it's the data that we're storing are a collection of related records. Okay, so these are two properties of databases that are worthy of a little more discussion. Let's first take a look at the self-describing property. So when we say that a database is self-describing, what I mean by that is that the database does not just contain the data, but it also contains the definition of its own structure. So we learned last time that one of the basic principles that underlies relational databases is this notion of having separate tables where each table is used to store information about a single business concept or theme. And that these tables can then be related to each other. So when we talk about things like tables or the attributes or columns that comprise a table, this is the structure of the database. And this information about the structure of the database is carried as part of the database itself. Now a technical term, let's use for this concept is metadata. Generally speaking, metadata are data that describe other data. Let's say you can think of it that way. And in our case in the context of databases, these metadata can include things like, hey, what are the tables that are in this database? Which columns or attributes are a part of each table for each of those columns? So what is the name of the column? What is its data type? What type of data can it hold? Do we allow null or empty values to be stored in there? Do we have any other sorts of constraints, say an acceptable range of values or maximum or minimum length of values that we store in there? So these are all metadata that are describing the structure of the data in our database. So the database then is not just the data themselves, but it's also the description of those data, the definition of the structure and it's carried as part of the database. And again, this database in relational databases consists of a collection of related records. So we can think about the relationships in these databases in two different ways. One way is a conceptual understanding of these relationships. And the other is the true physical connections that exist in the database. So let's talk first about concepts. Now conceptually, when we talk about relationships in a database, we're talking about heebles in the database being related to each other. So we're talking about to remember each table holds information about a single business concept or a single theme. So from a conceptual perspective, when we talk about relationships, we're talking about the way in which these concepts are connected. So I might say something like maybe we have two concepts, two tables in the database, like customers and orders. And we can say that the relationship between them is that customers are connected to orders in such a way that each customer can place many orders with us. But each individual order is linked to or associated with just one customer. So in this case, we're talking about conceptually that there's this connection between customers and orders, or we might talk about another very common one, which would be like employees and departments. So most many companies are organized in the form of departments that might have like a marketing department, human resources, finance, accounting, etc. So in this case, we can talk about the relationships between say employees and departments in a company. And again, conceptually, we would say that the nature of the relationship between employees and departments is that depending on the way that we structure our company, we may say that each department can have many employees in it, but each employee can work in just one department. So that might be the way that we run our business. And we could encode that, embed that, record that knowledge about how we run our business in the actual structure of the database. But the point here is that we're talking about these relationships conceptually. We're saying a department can have many employees. We're not actually talking about what is going on inside the database itself to establish and maintain those connections. That instead is the physical or the perspective that to the in practice perspective. So what is happening inside the database itself to enable these types of conceptual relationships is that we have these connections between rows and different tables. So a row in one table may be related to or connected to one or more rows in some other table. And as we saw last time, the primary mechanism that we use to enable these row to row connections is matching pairs of values. So if we have something like a department table and an employee table, and we want to record the department in which each employee works, assuming that we have some unique identifier for each department, say a department ID, then what we would do is we would add a department ID column into our employee table so that we would have a place to store the ID of the department in which each employee works. And then if we wanted to know, hey, what department does this employee work, all we have to do look at that employee's department ID and then go look in the department table for the matching department ID, which would be a row in that table. So conceptually, we talk about tables being related to each other. A department can have many employees, a customer can place many orders. A product can have many suppliers. These are all conceptual relationships. But in practice, we need to remember that underlying this, we actually have rows of data in these tables, and those rows of data are connected to other rows of data in the database by means of these matching pairs of values. So that's what's actually happening in the database. So conceptual understanding of these relationships versus the physical reality of what's going on in the database. So databases, just to summarize this discussion here, can hold more than just the data. Obviously, the data are important. As I've mentioned several times now, in many modern organizations, these are the most valuable assets that the company has, the data that they need to be highly protected. But the database also contains other things like metadata, data describing the structure of the database relationships, constraints, and so on. We can also have other types of data like indexes, which we will learn about as our final topic in the class. But for now, we can say that these indexes provide us with a mechanism that allows us to very quickly locate what it is we're looking for inside of a database table. So the concept is very similar to the index that you might find, say at the back of a textbook. So if I take any random topic, I say, okay, I want to know about using our database classes. An example, I say, I want to know about entity relationship diagrams. Now, you can quickly find that information in your textbook by going to the index at the back. And you know that it's alphabetized. So you can quickly locate entity relationship diagrams in the index. And then it will tell you which page to look at in order to find that information. That identical concept is implemented in relational databases in the form of database indexes. And just like when you have a textbook that has an index, it's going to make the textbook a little bit longer. You have to have additional pages in the book in order to hold that index at the back. The same concept applies in the relational database world in order to have an index. We're going to require some extra storage space. And that is part of the database itself, which is data. And we can also store application metadata in the database. So these can be things that select user preferences. We store in the database. We can store information that the DBMS needs in the database. So there might be some system databases, for example, that hold information that is used by the DBMS to figure out how to do its task. Examples of this type of information might be things like local data, like what to time zone am I working in? What are the symbols used here? These types of things that might be useful to data driven application. So databases contain more than just the data. Lots of other things going on in there as well. Now here is an example of one type of metadata. And this is meant to be useful to we humans. And it's just a database relationship diagram. So this is a graphical method that we have for showing how the various tables in a database are related to one another. So as we can see here, we have the three tables. There's a customer table over here on the left and a course table here on the right. This is drawn from our art course example from last time. And in the middle, we have this enrollment table, which allows us to keep track of which customers are enrolled in which classes. And we see that we have these little line, a connection between tables. And so the tables themselves in these diagrams are represented just as some sort of rectangle. Here's a table. Here's a table. All right, here's our third table over here, the course table. And that we represent the relationships between them using these little relationship lines. So this tells me that there's a relationship between customers and enrollments and there's a relationship between courses and enrollments. But there is no direct relationship between customers and courses. There's no line connecting those two tables together. So my only relationship exists here in customer being connected to enrollments and here in course being connected to enrollment. Now in this sort of a design, again, the idea is to have this third table in the middle. This is called a lookup table, or in this case, it's technically an associative entity. But this sort of structure allows us to implement what is conceptually known as a many to many relationship. So you can imagine if we're, I don't know, maybe we're, we run an art store and we do some art classes. Maybe we do drawing classes, painting classes and so on. Now conceptually, we want to have a world where each customer could take many of our classes, make sense. And similarly, we want to have a world where each class could be taken by many different customers at the same time. So conceptually, this is a many to many relationship. And the way that we actually are able to implement these types of relationships in the relational database world is through the use of a lookup table, like what we see here. Now if we wanted to just take a look at this from a data perspective, it might be a little more useful in helping us understand what's happening out here. I imagine that instead of looking at this in diagram form that we're looking at this in the form of the data themselves. So what I'll do here is just bring up a blank screen and draw some pretty pictures for us. All right. So let's begin with our customer table. Just make it a better color that'll better on the black background here, maybe a yellow. All right. So we have our customer table. And for the sake of simplicity, like it's spelled, let's imagine that we just have a couple of columns in this table, like the customer's ID and their name. And then over here on the right, maybe we have another table. This will be our course table for our art classes. And again, just to keep things simple, I'll say that we have, I don't know, course ID. And I don't know, maybe a name of the course. And there we go. And let's fill in some, I can really spell well that I can. That let's fill in some data over here so that we can have a little discussion. All right. Let's see if I can fix my little spelling mistake, custom or ID. All right. So let's imagine then we have some data here and I'll just keep it simple. So I have customer or one and two, three. And like I mentioned, last time, I like to use short names because otherwise, it takes me a long time to write them out when I'm using this little tool here will use my set of common short names. Dan and Chi and, oh, not bad. Something like Jen. There we go. And we'll have some courses. So just to keep things distinct, I'll give these course IDs, like 101 and 102 and so on. So I'll help a little later when we're trying to sort these things out. And I don't know, maybe this is paint. How to paint sounds like an art class painting, paint. And our paint class and this will be our drawing class. So very simple. Now, if we want to have a world where each of these customers could potentially take several courses and each course can have several customers, what I'm going to need is a third table that will sit in between these two. It will allow us to keep track of the relationships between customers and courses. That is, which customer has taken, which course. So from our purposes, we can call this enrollment. Or we could call it like customer course or something like that. And just to keep things very simple, see what we do is we record the IDs of the customers and we record the IDs of the courses that they've taken. It's not like what I go out there and fill in these data. I now have a design. I could conceivably, just put some boxes around these. I could conceivably have any customer take any number of courses and any course contain any number of customer. So imagine we have these three tables, we're seeing it from a data perspective rather than from a diabetic perspective like we saw previously. But we would say, okay, what class has Dan take and maybe Dan took the drawing class. So we record Dan's customer ID and course ID. And then we say maybe she took the painting class and the drawing class. And then we say maybe Jen down here at the bottom also took the drawing class. All right. So these in mind, we can now see how from a data perspective, this sort of structure allows us to create a design where each customer could potentially take multiple classes. We see that illustrated here with number two, Chi. So these records belong to Chi in the enrollment table. And that we could then say Chi is taking a painting class and Chi is taking a drawing class. Right. So each customer can potentially take multiple classes as illustrated here. And we can look at it in the opposite direction as well. We could say each course can have multiple customers. All right. So maybe our drawing class, which is course number 102, is linked to these records over here in the enrollment table. And from there, we could find that to Dan and Chi and Jen all took this drawing class. So again, this is the strategy that we used to implement a conceptual many to many relationship. In this case, where we want each customer to be able to take many courses and each course to have many customers. This table in between is called like a lookup table. You'll also hear it sometimes called an intersection table. And as we'll learn a little later in our class, under certain conditions, this can also be called an associative entity. That's for a future day.