Skip to main content
Loading Explore guides…
Database Concepts · Acedra
Home
Explore
Computer Science
Database Concepts
Sign in to save
Chat
If YouTube asks you to sign in, open the video on YouTube.
Watch on YouTube
Reload player
Transcript
59 segments
Copy
Hide video
0:03
Welcome everyone. This is Dr. Soper And today we will be going through our first lecture for our database class. I need to operate under the assumption that not everyone in the class has had experience with databases in the past. And so this first lecture is intended to bring everyone up to the same basic level of understanding with respect to several important database concepts. We have several different objectives, which we will seek to achieve in this lecture.
0:36
These are listed on the screen right now. First, we want to try to understand why storing data in a list is not necessarily a very good idea. It can cause many different types of problems. Second, we want to see if we can gain some insight into why an organization might want to use a database. Third, we will see how the notion of related tables of data, which forms a core part of the relational database model, provides a basis for eliminating
1:11
many of the problems which are associated with storing data in a list. We'll also explore the various components and elements that comprise a database or a database system. And we will learn about the purpose of something that we call a DBMS, a database management system. And along the way, we're going to explore some of the functions that a robust database application can provide to us. What, then, is the purpose of a database? Well, it's important to remember that a database does not
1:46
have just a single purpose. Instead, there are several key advantages that databases provide. First, a database provides a repository for storing data. That's kind of implicit in the name. Database implies that we have a place to store data. However, what might not be so obvious is that databases provide an organizational structure for data. That is, we don't just have a place to store data, but the database also provides an organized structure into which those data can be placed.
2:24
Finally, a database provides us with a mechanism for interacting with our data. Now, interacting with data can generally be described in four different operations. Here they're listed as querying, creating, modifying, and deleting data. But there's another more interesting acronym, which may help you to remember this. And that acronym is CRUD, C-R-U-D. This stands for create, read, update, and delete. These are the four basic operations that we can use when interacting with data.
3:09
A key point to remember here is that, in business, there are many natural, hierarchical relationships among data. For example, a customer can place many orders. Another way of saying that is many different orders can be associated with the same customer. Or another example is a department can have many different employees, but a given employee might work in one, and only one department. So these are hierarchical relationships among the data.
3:49
And a relational database allows us to model and represent these relationships. Let's take a look at a list of data. What I mean by a list here is a simple, two-dimensional table of data. And in this table, we store information that is important to us for some reason. An example might be, say that we have many different projects in our company, and we want to keep track of who the project manager is for each project. Therefore, for each project, we may
4:27
track the project manager's name, their ID number, and maybe their phone extension within our company. Now, ordinarily this wouldn't be such a big problem. But imagine if the same person, the same project manager, is simultaneously managing multiple projects. In that case, using a simple list, the project manager's information would necessarily appear on the list multiple times. Now, what is the problem with this? Well, there isn't really any major problem,
5:06
aside from the fact that it's redundant. Another way of saying that is we are using more space than is necessary to record which project manager is associated with which projects. Another problem with storing data in a list is that the list of data may contain more than one concept or more than one theme. I want you to remember this idea of a business concept, because we will see it again and again throughout our course. A business theme or concept refers
5:44
to a number of different attributes or properties that are all naturally related to one type of business entity. An example might be an employee. An employee is a business concept. Employees have different attributes that we may want to track for each employee in our organization. For example, we may want to track the employee's ID number, the employee's name, the employee's salary. Other examples of business concepts or business themes
6:23
include things like departments, products, customers, orders, locations, and so forth. Returning to lists, a major problem is with these multiple themes. That is, for each row of the list, we might be recording information about more than one of these business concepts. As an example, consider our list of project managers. If we also wanted to include project information-- that is, perhaps, a project name, a project ID, any additional information about the project--
7:05
we might store that in the same row along with the project manager. Aside from redundancy, as I mentioned earlier, the problem here is not necessarily how we are storing this information, but what might happen to the information if it is stored in this way. When we store information in a list, we introduce the possibility of something called data anomalies. And specifically, there are three types of these anomalies. Here they're listed as a deletion problem, an update
7:43
problem, and an insertion problem. Later in our course, we will refer to these as deletion anomalies, update anomalies, and insertion anomalies. So the structure of a list and its associated problems of redundancy and multiple themes can produce a situation in which these modification problems potentially could occur. So let's take a look at some of these modification issues. Here we have a list of data, which contains information on students. So we have a student's last name, first name,
8:21
their email address. And then we see who that student's advisor is. So the last name of the student's advisor, the email of the student's advisor, and the department in which they work, along with some additional information. Let's take a look at how some of these modification anomalies might emerge. In the first case, imagine that we want to change a student's advisor. And for this example, we're going to change Advisor Baker to Advisor [? Tiang. ?] When we do this, not only are we going
8:56
to have to update the last name of the advisor in the list, but to maintain the overall quality of the data, a concept that we call data integrity, we are going to have to also update the advisor's email address. Note, in this case, that we do not need to update the department or the administrator's last name, because those are the same for advisors [? Tiang ?] and Baker. However, if, for some reason, we wanted to change the student's advisor from, say, Baker to Valdez,
9:34
well, now not only do we need to update the last name and the email address, but we also need to update the department and the admin last name attributes as well. Just to do something as simple as changing the student's advisor with this list approach requires that four separate pieces of information be updated. So this is what we might call an update anomaly or an update problem. Next, let's look at a deletion problem. Imagine that our student, listed here as Chip Marino,
10:14
decides to drop out of the university. So we need to remove Chip from our list of students. Now, look what happens if we delete this row of data. When the row of data is deleted, not only do we lose the student's information, but we've also lost information about the advisor and the department in which that advisor works. That is, you will notice that Advisor [? Tran ?] is not currently assigned to advise any other students. So when we delete this row of data, we may entirely lose the knowledge
10:54
that Advisor [? Tran ?] even exists. And that can be a problem. In this case, this is called a deletion problem or a deletion anomaly. Finally, let's look at an insertion anomaly. Let's say that at our university, we decide that we want to add a new department. So we create a biology department. Well, this is fine. We add information to our list. We now have a biology department and an administrator's last name. However, we now have all of this missing data in our table. There are empty cells.
11:37
There's no information for a student. There's no information for an advisor. All we have here is information for the department. This means that data are missing, and we're not efficiently utilizing our available storage space. These are some of the problems with these lists. Now, remember, earlier I mentioned that in the business world there are complex relationships among different types of data. For example, a department may have many employees who work in that department.
12:16
Or a product may be assembled from many different components. Or a customer might place many different orders with our company. So there are these natural complexities that arise in business. And relational databases, as we will see, not only solve the problems that are associated with lists, but also allow us to model these natural relationships among business data. Now, I've been using this term relational database. So it would probably be a good idea for us
12:55
to consider what a relational database actually is. On a very basic level, relational databases store data in a table. Now, a table is simply a two-dimensional grid of data that contains columns, and it contains rows. The convention in the relational database world is that the columns represent different attributes of an entity. And each row in the table represents an instance of the entity. So for example, if I have an employee table
13:39
with an employee ID, an employee name, and an employee telephone number, we would list those three attributes-- ID, name, and telephone number-- as columns in the table. And then each row in the table would represent an individual employee.
14:02
Again, we said that there are these natural relationships among different business concepts out there in the business world. In a relational database, data for each of these concepts is stored in its own table. So I may have an employee table. I may have a department table. And then I can create a relationship between those tables, which will allow me to figure out which employees work in which departments. So a good way of thinking about this
14:38
is instead of storing all of the information in one big list, we break the list apart into separate pieces according to which information is associated with which business theme or business concept. Therefore, all of the information associated with an employee might go into an employee table. And all of the information associated with a department might go into a department table. Although, inside a relational database, information about different business concepts or business
15:17
themes is stored in separate tables, it may be necessary-- say, for reporting purposes-- for us to reassemble all of the data from these different tables back into a list. Now, we can accomplish this in the relational database world by performing something called a join operation. That is, we join the tables together. Now, a very, very important concept for you to understand is that in the relational database world, we link the records in different tables together using matched pairs of values.
16:06
These matched pairs of values allow us to associate a row in one table with one or more rows in another table. For example, if we have a project table and we have a customer table, and we use an attribute called a customer ID to uniquely identify each customer, then what we can do is we can place a customer ID attribute in the project table. And we can then use the ID of the customer in each table to link related rows together. Now, of course, you may be listening to this,
16:55
and you may think, why do we want to do all of this extra work? And it is true that relational databases are more complicated than storing data in a list. However, as we will see, relational databases have many, many advantages over a list. Among these, first, a relational database minimizes data redundancy. In the examples we saw earlier, we saw that there exists with lists the possibility to have all sorts of redundant data. Relational databases eliminate this.
17:36
Relational databases also allow us to model the natural relationships that exist among business concepts in the real world. And there are several other advantages as well, including providing a solid basis from which to do things like generate reports or build user interface forms. I mentioned a few minutes ago that there are four basic ways of interacting with data-- create, read, update, and delete. In the relational database world, we have access to something called the Structured Query
18:17
Language, often called SQL, or SEQUEL, if you like. And this is the primary tool that we can use for performing these four basic operations on our data. In a few weeks, we will begin learning the Structured Query Language. This is an extremely valuable skill for you to develop. If you are able to learn the Structured Query Language well, then you will be able to sit down and work with virtually any modern database with a very, very short learning curve.
18:57
It doesn't matter if you need to work with a SQL Server database, or an Access database, or an Oracle database, or a DB2, or a MySQL database, or even a database for, say, a mobile device. Most databases, including all of those that I mentioned, support the Structured Query Language. So if you can learn the Structured Query Language, you will be very well positioned to work with almost any database. Even though we're still early in our course,
19:31
I want to give you an example of the Structured Query Language now so that you can see that it's really not that difficult to understand. This is certainly not a full scale computer programming language. The Structured Query Language was designed from the ground up to be simple to use and to be simple to understand.
19:56
So what we have here is an example where we have three tables. There is a customer table, and a course table, and an enrollment table. So let's say that these are art courses. We have customers who have signed up to take different art courses to see, perhaps, if they can learn to paint or they can learn to sculpt. Now, although we start with three tables, if we need to combine all of the information together in order to restore the original list structure of the data, we can do that
20:40
by using something called a SQL SELECT statement. Here we see an example of such a statement. I know this looks complicated. But hopefully, you are able to read this even right now at the beginning of our course and get a good idea for what this statement is supposed to do. So let's take a look. We are selecting the customer's last name, the customer's first name, the customer's phone number, and the date of the art course, the amount paid out of the enrollment table,
21:21
the course itself, and the total course fee from our three different tables-- customer, enrollment, and course. We are then linking the related records in each table together using these matched pairs of values that I mentioned earlier. In the first case, we are linking the customer table to the enrollment table using matched pairs of customer numbers. And in the second case, we're linking the course table to the enrollment table using matched pairs of course numbers.
21:58
What this will do for us is restore the original list of data. So you can see our list. It contains the customer's last name and first name, customer's phone number, the course date, the amount that the customer has paid so far, the course they are taking, and the total course fee. Now, the SQL statement that we saw was a SELECT statement. And that is a statement which just pulls data from the database. There are many other things that we can do with SQL, like deleting data, inserting new data,
22:40
updating data in the database, and so forth. And we will learn how to do all of those various tasks when we study the Structured Query Language in more depth in a few weeks. Now I would like to turn our attention to some conceptual information. Let's focus on a database system. It's important to remember that a database system is not just the database itself. Rather, it exists in a broader hierarchy, which includes users, application software programs which
23:22
rely on the database, something called a database management system, a DBMS, and then, of course, the database itself. The broader picture of a database system includes these four components. Here we see the relationships between the four components of a database system. On the far left, we see users. Now, users are people, just like you or me, who might need to use the data in a database. But a user doesn't necessarily have to be a human being.
24:02
It is possible, for example, for other software programs to be users of our database as well. Now, you will see that users interact with database applications. These are the programs that rely on the database. So if we are a human user, we might, for example, use a website, which interacts with a database. That website could be considered a database application. You will notice that the database application talks to the DBMS. The DBMS, the database management system, acts as a gatekeeper.
24:48
All of the information flowing in or out of the database must travel through the DBMS. It is a critical mechanism for maintaining the quality of the data in the database. We do not allow our users or our database applications to directly access the data in the database. To work with the data, to access those data, database applications must go through the database management system. And then, finally, far over on the right side of our screen, we see the database itself.
25:31
And this is where we actually store the raw data in their separate tables. So what do the users of a database system do? Well, they do many different things. You and I are users of database systems on almost a daily basis, even if we don't realize it. So we can look up our checking account balances online. We can produce reports. We can seek information on Wikipedia. All of these are examples of us as users interacting with database applications.
26:10
A few interesting things to note about the database itself is that it is a self-describing collection of related records. What this means is that the database does not just contain the data, but it also contains a definition of its own structure. Databases also contain metadata. The simplest way to understand metadata is that metadata are data which describe other data. And in the context of the database world, metadata are data which describe the structure of the data in the database.
26:52
So for example, I may say that an employee table contains an attribute called Employee ID, and that ID value is an integer. That knowledge that the Employee ID value is an integer is metadata. And of course, databases allow us to establish relationships between tables. So that means the tables within a database are related to each other according to the relationships that we establish. So it's important to remember that a database does not just contain the data that we have put into it.
27:35
A database also contains metadata, which describe our data. It contains, or potentially can contain, what we might refer to as overhead data. These are tables of data that are used to improve performance or track the status of the database or how users are interacting with the database. And one of the major types of these overhead data are called indexes. Later in our course, we will examine database indexes. And of course, databases can also contain application metadata.
28:19
So these are data values that are used by the applications which rely upon the database. An application, for example, might store user preferences in the database. Now, to reiterate, a DBMS, or a database management system, serves as a gatekeeper or an intermediary between the database and the applications which rely upon that database. The purpose of the DBMS is to control and manage the operations of the database. The DBMS is a special kind of software program, which
29:02
is used to not only create databases, but also to process and administer those databases. So a DBMS is a type of application program. But it is not the database. The DBMS interacts with the database, but it itself is not the database. What, then, can a database management system do for us? Well, the answer is it can do many different things, as we see here on your screen. We can use the DBMS to create a new database. And then, within that database, we
29:41
can create tables and establish relationships between tables. We can read, write, edit, and delete data using the DBMS. We can establish rules which regulate and control the data in the database. The DBMS provides an additional layer of security for us. And it also provides mechanisms through which we can backup our data and recover our data if anything ever goes wrong. So the DBMS is a very critical piece of software. Now, businesses often operate with many different types
30:27
of business rules or constraints. And one of the great things about working with databases is that the DBMS can establish and enforce many different types of constraints. One of the most useful types of constraint that a DBMS can enforce is called a referential integrity constraint. The purpose of a referential integrity constraint is to ensure that values that we are entering in one table have matching values in another table. Now, that's a very abstract way of trying
31:07
to convey a simple concept. As an example, imagine that we have a customer table and a project table. And we are trying to enter information about a new project in the project table. And we specify that the customer ID that is associated with this project is customer number 5. Referential integrity means that the database will actually check in the customer table to see if customer number 5 exists. If customer number 5 does not exist, then the database will not allow us to add that customer
31:52
ID to our project table. And in this way, I hope you can see it helps to maintain the quality of the data in the database, what we call data integrity.
32:06
Now, the fourth component of our database system were database applications. And database applications are simply those software programs that need to use or work with data in the database. So these might be data driven websites. They might be mobile apps. They might be desktop applications or productivity software. Just remember that these applications are not allowed to talk to or use the database directly. But rather, they must go through the DBMS to work with or use those data.
32:49
Finally, for this lecture, I want to just talk about some of the different types of database systems that are available. First, we can consider something called a personal database system. These are very small databases. They are typically used by a single user at a time. They are not complicated. They are simple in design. And they're not really suitable for supporting medium or large businesses. They might be used by very small businesses just out of convenience.
33:27
But as those companies grow, they will, at some point, certainly need to choose a more robust database solution. An example of a personal database system might be Microsoft Access. Convenient, easy to use, but not very powerful. One of the interesting characteristics of personal database systems is that they often contain the capability to build a database application, or at least interfaces to the database within the DBMS. So in Microsoft Access, for example,
34:09
I can create data entry forms or generate reports for my Microsoft Access database. That is, it kind of combines the database application and the database management system into a single entity, conceptually speaking. On the other hand, we have a class of database systems that are called Enterprise-Level database systems. These are databases that are used by medium and large size companies to support their operations. And it is Enterprise-Level databases
34:46
that we will learn to use and interact with in this class. Compared to personal database systems, Enterprise-Level databases are designed to support many different users at the same time. And this could be thousands or tens of thousands of users all at the same time. Enterprise-Level databases also serve data to more than one application. For example, if we are a retailer like Target or Walmart, we may have a database that provides data to our website
35:30
and also provides data to the dashboards that are used by our managers. Enterprise-Level databases are commonly spread across multiple physical computing devices. So many different computers. So a single database might be running on many physical servers. Enterprise-Level databases can also be geographically distributed. So I might have part of my database in my corporate offices in Los Angeles, another part in Beijing, another part in Berlin in Germany.
36:15
And Enterprise-Level database management systems support more than one database. So within the same database management system, we might, for example, have our operational database, which allows us to run our business and keep track of transactions in real time. And we might also create a data warehouse or data marts. And they can all be managed by the same Enterprise-Level database management system. These are large databases. As an example, consider an ERP system like SAP.
36:56
A typical ERP implementation will have thousands of tables, all related to each other in some way. An Enterprise-Level database management system can handle this level of complexity with relative ease. So here we see a graphical example of these Enterprise-Level database systems. The DBMS, again, serves as an intermediary or a gatekeeper between the databases and all of the various database applications that want to rely on the data in those databases. And they can be mobile applications,
37:38
applications that are written in Java, or C#, or even web applications. It might be ASP.NET or PHP apps. Just as examples of some commercial DBMS products, again, I mentioned that personal or desktop DBMS products might include Microsoft Access. Whereas, Enterprise-Level products include SQL Server, which is what we will be using in this class, Oracle, MySQL, and DB2. MySQL, if you're interested, is a completely free and open source database. Well, my friends, thus ends our first lecture
38:28
for our database class. I hope you learned something. And come back soon, and we'll begin our exploration of our next topic. Until then, have a great day.
Here to help you learn
Sign in to keep a private conversation with your course tutor.
Ask me anything about the material…
Sign in to chat