A database management system (DBMS) allows users to create, read, update, and delete data in a database, while providing maintenance and user administration capabilities.
- [Instructor] In this course, we're going to be moving towards storing our data inside of a Database Management System. Before we can understand what a Database Management System is though, we first need to understand what a database is. At its most basic level, a database is a collection of individual data items that are stored in a highly structured way that represents a model of reality. This collection of data items can be stored in a single location or hard drive, or distributed across large networks with components in many physical locations. Regardless of where the data is physically stored, the database acts as a single entity to anyone or any system that makes use of it.
In order to make use of a database, we need a way to perform what are called CRUD tasks. That is to say, we need a way to create new data items, read and find existing data items, update data items with new information, and delete outdated data items. This is the responsibility of a database engine, a collection of programs that are able to sift through the database items and perform the required tasks, and in order for you or I, or any other end user to access the database engine, we need to go through some sort of Database Application or what's sometimes referred to as a front end.
This is where we convert our human intentions and questions about the data into code that the database engine understands and we usually do this through user-friendly input features, like buttons and text boxes, and drop-down menus, and so on. So this might seem like a bunch of moving parts that we have to contend with. It starts with a Database Application which collects user input in the form of instructions. The Database Engine translates those instructions into code that is then used to retrieve information from the database itself. Once the information is retrieved, the process reverses itself, and the database passes the information back to the engine which translates it into a format that the user can make use of, usually on screen or in a particular port.
When talking about creating a database however, we're usually talking about what's called a Database Management System or a DBMS. This is a system of tools that package up all three of these components into a single application that we can develop within. They can be a simple desktop systems like Microsoft Access, which runs on a single computer, and includes the database, the engine, and the end user interface development, all under one roof. Or they can be more complex and robust systems like SQL Server or Oracle, which largely provide a pair of database and engine but require you to develop the interface or the application in a programming language of your choice.
In addition to creating and interacting with the database, a DBMS provides maintenance and user administration capabilities. These allow you to optimize the database for speed and create routine backups and other tasks, which keep the data itself healthy and reliable. Some DBMS's also allow you to setup permissions for different users and control access to just the portions of the system that they need in order to do their jobs while keeping them out of areas that they shouldn't be accessing. So that's a quick overview of what a Database Management System is. When we throw the word relational into the mix, such as when we say, Relational Database Management System or RDBMS, than we're being specific as to the type of database that we're working with.
In this case, something called a Relational Database. In this system of storing data, we're using multiple data tables that refer to one another in order to store lots of information about a single entity. I'd like to illustrate this by thinking about how you might find a book at your local library. You might be interested in finding something out about motorcycles, so you perform a keyword search in the catalog for motorcycle. The computer returns lots of categories such as motorcycle maintenance, motorcycle clubs, motorcycle history, and so on. Digging deeper, you click on motorcycle history, and find several books by the same author.
Now you might start to wonder what other books this author has written, so you perform a new search for the author, which returns the same motorcycle books you've already found as well as additional books on antique bicycles that she's written. All of this information is related to each other. The books have their authors and the authors have written books, and you can find out different kinds of information depending on which direction you approach the problem. Now, Relational Databases have been around since the 1970s and are by far the most common type of database used today. They manage everything from small business applications to large corporate finance systems.
Their the most common type because their so flexible in the wide variety of information you can store in them, and due to their ability to quickly get that information back out in meaningful ways, but in order to take full advantage of everything that a Relational Database offers, we need to make sure that we structure our data in a very coordinated way.
Adam Wilbert covers the basics of relational database design, regardless of whether you use Access, FileMaker, Open Office, or SQL Server. Learn how to prevent data anomalies, gather requirements to plan your design, and develop a conceptual data model—translating your ideas into components like tables, relationships, queries, and views. Plus, learn about logical design considerations that can help you construct a database that is easy to maintain.
- Identify the three rules of relations.
- Summarize the four stages of developing a relational database.
- Describe a strategy one might use to ensure a database remains flexible in terms of the questions a user can ask.
- Explain how to avoid scope creep.
- Recall the characteristics of a Lookup Table.
- Recognize situations in which denormalization would be beneficial.
- Understand the types of relationships modeled by junction tables.
- Define referential integrity.