What is a "database"?
In general, a database is a collection of organized information.
A file cabinet might be considered a database. It might be fair to call my grandmother's recipe box a database. After all, it is a collection of recipes that are organized by name.
These days, however, when we say database, we are normally speaking about a computer database - a set of digital files that contain a collection of organized information.
Depending on the specific product, database files may be stored on your desktop, phone or a remote server. How the database files are formatted differs with each database brand. They are often not compatible. (Many other devices may also contain databases: network routers and switches, personal medical devices, smart cars, TV set-top boxes, refrigerators, washers, etc. )
Database files are not meant to be used directly. In other words, we do not open a database file and read or update the information. Instead, we use a software application and user interface (UI) to interact with a data engine - a software application that interacts with the database files and interprets our requests to add, update, delete or see the data. In most cases, the data engine does not have its own UI; it is simply a gateway and translator, that converts data requests into database instructions and then relays the results.
The term "database" can be confusing because it is common to refer to the database engine software as the "database", without making a distinction between the database itself, the UI and the database engine. Many database products have their own preferred UI (or User Interface) software that is commonly referred to as "the database".
There are many different database and database engine products for various hardware and software environments. Popular commercial databases for the PC Windows market include
- Microsoft SQL Server (MSSQL)
Additional databases that are Linux-focused included Teradata and Adabas. There are also desktop databases, such as Microsoft Access and FileMaker, as well as mainframe databases such as IBM D2 and SAP.
Most of these products pair a user interface (UI) application with the database engine and database files. There are also UI applications such as Toad and HeidiSQL, that do not have a companion database, and can be used with different brands such as MSSQL and MySQL.
It is important to make a distinction between the database "engine" and the UI. Applications, such as a web site, communicate directly with the database base engine. The UI is not used or needed; in fact, it may not even be installed in a database server. Developers use the UI to work with the database. They may also use an application, instead of the preferred UI for that product.
It is also important to realize that in modern computing, it is much more common to rely on a cloud database – a database available from a provider on the Internet, such as Microsoft Azure, Amazon Web Services (AWS), and other providers such as SmarterASP. In these cases, the database files and the database engine exist on remote servers, and we interact with them using desktop or web software.
We may also write programs that interact with the database engine using an API (Application Programming Interface). An API is a data interface used by programs to exchange information. The difference between a UI and an API is that we normally "see" (or hear) and interact with a UI using a device such as desktop, phone or web app, ATM screen, etc.; where a computer program interacts with an API only through code – without any visual or audio component.
What are some examples of databases?
- The list of settings on a laptop or for a browser
- A contact list on your phone
- A song list
- List of students attending this school
- Recipes on a web site
- (Almost everything on most web sites...)
There are several common database types. The differences are in how the data is structured, the purpose and features of the database, and how communication with the database is managed.
Database types are most easily discussed in terms of "models". A model lets us use a diagram or drawing to illustrate how the database looks. The primary database models that are commonly discussed are:
- flat-file database
- hierarchical database
- relational database
- object-oriented database
- nosql database
A flat file database is simply one that has a simple structure, such as a list of data in a text file. These are still common, especially for small applications, such as a contact list on a phone. Flat-file databases are easy to implement, but do not scale well to be useful for large applications. An example of a flat-file database could be a list of contacts on a phone.
Hierarchical databases are now rare in real-world computing. They were popular during the early history of computing, but are now rarely as efficient as other models. One exception is the Windows Registry. A hierarchical database consists of primary records (or keys), that have a series of secondary records linked to them. As with the Windows Registry, there may be multiple levels of the hierarchy, and each record is referenced by a key or value name. Some hierarchical databases are still used in mainframe applications.
Relational databases are the most common database model in use today. Nearly every commercial database for the PC and cloud markets is a relational database. Relational databases use datasets called "tables", organized in "rows" and "columns".
Object-Oriented databases are a specialized type of database that manage "objects", rather than tables. Objects might include complicated structures like an automobile, that itself is comprised of other objects (an engine, wheels, power train...). See Object-Oriented Database (OODB)
NOSQL databases (also referred to as NOT-ONLY SQL) describe a category of specialized databases that do not strictly adhere to only managing tables. Various brands may target one or more object type, such as documents, graphs or key-values. One general feature of NOSQL databases are the ability for objects of the same type to contain different attributes. See What are NoSQL databases?
This course focuses on Microsoft's SQL Server (MSSQL) product, which is one of the most popular and powerful commercial relational databases. It will also explore mySQL, and how it differs from MSSQL.
The Purpose of a Database
Database make the world go round. Well, not really - but they make the Internet work. They make almost every computer, every app and every web site we use work the way they do.
When you click a link for https://www.youtube.com/, what happens?
- The computer system checks to see if it knows where youtube.com is (uses a database)
- If not, it sends a request to find it (where a domain server uses a database to return an answer)
- The computer sends an HTML request out across the network (where every single router uses a database to find other routers on the network)
- Once the request reaches youtube.com, it checks to see if you are registered and logged in (using a database)
- Then it displays a list of popular videos (using a database)
- If you select one, it finds the correct video (using a database) and then displays it (where the video itself may actually be pulled from a database)
- And yes, all the links to other sites, ads, images, etc., all come from databases.
So, you can see that databases are a critical part of anything related to computers or the Internet.