Database

A database is an organized collection of data, like a digital filing cabinet. It helps us store and find information quickly. Think of it like a big library where every book (or piece of data) has its own special place and can be found easily.

Before computers, people used index cards to keep track of things like recipes, contacts, or research notes. These cards were handy but hard to sort through when you had a lot of information.

An SQL select statement and its result

Today, databases live inside computers. Small ones might sit on a single computer’s file system, while bigger ones live on groups of computers called computer clusters or in cloud storage. They can handle everything from keeping track of your school assignments to storing millions of songs or customer records.

There are different types of databases. The most common type, called relational databases, organize data into tables with rows and columns. These often use a language called SQL to talk to the database. More recently, NoSQL databases have become popular because they can handle different kinds of data and are very flexible.

Databases are important because they help us manage the huge amounts of information we create and use every day. They make sure data is safe, secure, and easy to find when we need it.

Terminology and overview

A database is a collection of organized information that we can search and use. To work with a database, we use special software called a database management system (DBMS). This software helps us store, find, and manage the data.

People often use the word "database" to talk about both the information itself and the software that manages it. Databases can be simple, like a list in a spreadsheet, or very large and complex. They help us keep track of many different kinds of information in an easy way.

History

The way we store and manage data in databases has changed a lot over time. These changes happened because we got better computers, like faster processors, more memory, better storage, and faster networks. Databases became possible when we started using devices like magnetic disks instead of old tapes.

Basic structure of navigational CODASYL database model

Early databases were organized in two main ways: the hierarchical model and the network model. These models used connections called pointers to link pieces of data together.

Then, in 1970, a new idea called the relational model was introduced by Edgar F. Codd. This model organized data into tables, making it easier to search for information. By the 1980s, computers were powerful enough to use this model widely. Today, many big companies use relational databases like IBM Db2, Oracle, MySQL, and Microsoft SQL Server.

Later, new types of databases like NoSQL and NewSQL were developed to handle large amounts of data quickly.

Use cases

Databases help organizations manage their inner workings and interact with customers and suppliers. They store important information like admin records, engineering data, and economic models.

Some examples of databases include computerized library systems, flight reservation systems, parts inventory systems, and content management systems that keep websites stored as pages in a database.

Classification terms

Databases can be grouped in different ways based on what kind of information they store, what they are used for, or how they are built. For example, some databases store text, pictures, or numbers, while others are used for banking, music, or movies.

Some special types of databases include:

In-memory databases that work very fast by keeping data in the computer's main memory.
Cloud databases that live online and can be used from any web browser.
Data warehouses that collect and organize large amounts of information for managers and experts.
Document-oriented databases that store information like papers and files.
Graph databases that use connections to show relationships between pieces of information.
Mobile databases that you can carry on phones or other portable devices.
Operational databases that keep track of everyday business details like customer information and money records.

Database management system

A database management system (DBMS) is software that helps people create, manage, and control databases. It lets users store, find, and change information. Popular examples include MySQL, MariaDB, PostgreSQL, Microsoft SQL Server, Oracle Database, and Microsoft Access.

A DBMS usually includes tools for keeping the database safe and working well. It helps manage who can see or change the data and makes sure everything stays organized. Many DBMSs also offer ways for different computers to share and use the same database.

Application

People use special programs to work with databases. These programs talk to the database through a system called a DBMS. Some programs let you type commands to find information, while others, like websites, use databases to store and search data.

Programmers write code to tell the database what to do. They use special sets of instructions, called APIs or database languages. These need to work with the DBMS. Common examples include ODBC, JDBC, and ADO.NET.

Database languages

Database languages are special types of languages designed to work with databases. They help users and programs manage and find information stored in databases. These languages can do things like control who can see the data, define how the data is organized, change or delete data, and search for specific information.

Some well-known database languages include SQL, which is widely used for managing data in tables. Other examples are OQL for object data, XQuery for XML data, and SQL/XML, which combines SQL with XQuery. These languages can also include features like managing database settings, performing calculations on data, and ensuring rules are followed.

Storage

Database storage is where all the information in a database is kept. It includes everything needed to understand and use the data, like details about how the data is organized and what it means. This storage helps make sure the database can be used for a long time and works well.

Some databases can store data in different ways to make searching faster. They might keep copies of often-used information to avoid having to calculate it every time. This can make things quicker but also uses more space. Databases can also keep copies of data in different places to make sure information is still available even if part of the system stops working.

Security

Database security is about keeping data safe from anyone who shouldn’t see or change it. This includes stopping both deliberate and accidental unauthorized access to the database. For example, it makes sure that only the right people can see certain information, like payroll details or medical records.

To manage this, database owners set up rules about who can access what. These rules can be for individual users or groups, and they help protect sensitive data. Security also includes keeping track of who looked at or changed the data, which helps spot any unusual activity. This way, organizations can guard their important information from hackers and other threats.

Transactions and concurrency

Further information: Concurrency control

Database transactions help keep data safe and correct, even if something goes wrong like a crash. A database transaction is a group of actions done to the database, such as reading or writing information. These actions are treated as one complete task.

Transactions follow four important rules called ACID: atomicity, consistency, isolation, and durability. These rules make sure that transactions are reliable and protect the data.

Migration

Sometimes, people want to move a database from one type of software to another. This can happen for reasons like saving money or getting better features. Moving a database can be tricky and expensive, especially for big databases. Usually, the company that makes the new software provides tools to help make the move easier. The goal is to keep everything working just like before, even while changing the software that manages the database.

Building, maintaining, and tuning

After a database is designed for an application, the next step is to build it. A database management system (DBMS) is chosen to help create and manage the database. This system provides tools for database administrators to set up the data structures and adjust important settings.

Once the database is set up, it is filled with the necessary data to start working. After this, regular maintenance is needed. This can include adjusting settings for better performance, updating data structures, and adding new features to keep the database running smoothly.

Backup and restore

Main article: Backup

Sometimes, we need to return a database to an earlier version. This might be because of mistakes or errors in the database. To prepare for this, we make backups. Backups are copies of the database saved at certain times. When we need to go back to an older version, we use these backup copies to restore the database to that earlier state.

Static analysis

Static analysis is a way to check software for problems before it is run. It can also be used with databases to make sure queries are safe. One method, called abstract interpretation, helps create approximations of data to understand how queries work. This can be useful for keeping databases secure, like controlling who can see certain information.

Miscellaneous features

Database management systems often have extra features to help users. For example, database logs Database logs keep track of what actions have been performed, which can be useful for reviewing past activities. Some systems also include tools to create graphs and charts, especially when dealing with large collections of data.

Another important feature is the query optimizer Query optimizer, which helps make searches faster by choosing the best way to get the information needed. There are also tools to help design databases, monitor their performance, and manage how they use computer resources. Some newer systems aim to combine all these tools into one easy-to-use package, similar to ideas from software development called "DevOps for database".

Design and modeling

When designing a database, the first step is to create a model that shows what kind of information the database will hold. One common way to do this is by using an entity–relationship model, which helps organize data into groups and shows how these groups are connected. For example, this model can show if a person can have more than one phone number or if different packaging types mean different products.

After creating this model, it is turned into a blueprint for how the data will actually be stored in the database. This step is called logical database design. The most common way to store data today is using the relational model, which organizes data into tables.

Finally, designers decide how to make the database work well, including making it fast, secure, and easy to recover if something goes wrong. This step is called physical database design. It also includes setting up who can see or change the data, ensuring that important information is protected.

Models

Collage of five types of database models

A database model is a plan that decides how data is stored and organized. The most popular model is the relational model, which uses tables to store information.

Common models for databases include:

Specialized models are made for specific types of data, like:

Research

Database technology has been studied and developed since the 1960s by both universities and companies like IBM Research. Researchers explore ideas such as different ways to organize data, methods to handle many users at once, and ways to make searching for information faster.

There are special magazines and yearly meetings where experts share their newest ideas about databases, such as the ACM Transactions on Database Systems and gatherings like ACM SIGMOD.