A database system is an overall collection of different database software components and databases containing the following parts:
- Database application programs
- Client components
- Database server(s)
- Databases
A database application program is special-purpose software that is designed and implemented by users or by third-party software companies. In contrast, client components are general-purpose database software designed and implemented by a database company. By using client components, users can access data stored on the same computer or a remote computer.
The task of a database server is to manage data stored in a database. Each client communicates with a database server by sending user queries to it. The server processes each query and sends the result back to the client.
In general, a database can be viewed from two perspectives, the users’ and the database system’s. Users view a database as a collection of data that logically belong together. For a database system, a database is simply a series of bytes, usually stored on a disk. Although these two views of a database are totally different, they do have something in common: the database system needs to provide not only interfaces that enable users to create databases and retrieve or modify data, but also system components to manage the stored data. Hence, a database system must provide the following features:
- Variety of user interfaces
- Physical data independence
- Logical data independence
- Query optimization
- Data integrity
- Concurrency control
- Backup and recovery
- Database security
The following sections briefly describe these features.
1. Variety of User Interfaces
Most databases are designed and implemented for use by many different types of users with varied levels of knowledge. For this reason, a database system should offer many distinct user interfaces. A user interface can be either graphical or textual. Graphical user interfaces (GUIs) accept user’s input via the keyboard or mouse and create graphical output on the monitor.
A form of textual interface, which is often used by database systems, is the command-line interface, where the user provides the input by typing a command with the keyboard and the system provides output by printing text on the computer monitor.
2. Physical Data Independence
Physical data independence means that the database application programs do not depend on the physical structure of the stored data in a database. This important feature enables you to make changes to the stored data without having to make any changes to database application programs. For example, if the stored data is previously ordered using one criterion, and this order is changed using another criterion, the modification of the physical data should not affect the existing database applications or the existing database schema (a description of a database generated by the data definition language of the database system).
3. Logical Data Independence
In file processing (using traditional programming languages), the declaration of a file is done in application programs, so any change to the structure of that file usually requires the modification of all programs using it. Database systems provide logical data independence—in other words, it is possible to make changes to the logical structure of the database without having to make any changes to the database application programs. For example, if the structure of an object named PERSON exists in the database system and you want to add an attribute to PERSON (say the address), you have to modify only the logical structure of the database, and not the existing application programs. (The application would have to be modified to utilize the newly added column.)
4. Query Optimization
Most database systems contain a subcomponent called an optimizer that considers a variety of possible execution strategies for querying the data and then selects the most efficient one. The selected strategy is called the execution plan of the query. The optimizer makes its decisions using considerations such as how big the tables are that are involved in the query, what indices exist, and what Boolean operator (AND, OR, or NOT) is used in the WHERE clause. (This topic is discussed in detail in Chapters 19 and 26.)
5. Data Integrity
One of the tasks of a database system is to identify logically inconsistent data and reject its storage in a database. (The date February 30 and the time 5:77:00 p.m. are two examples of inconsistent data.) Additionally, most real-life problems that are implemented using database systems have integrity constraints that must hold true for the data. (One example of an integrity constraint might be the company’s employee number, which must be a five-digit integer.) The task of maintaining integrity can be handled by the user in application programs or by the database management system (DBMS). As much as possible, this task should be handled by the DBMS. (Data integrity is discussed in two chapters of this book: declarative integrity in Chapter 5 and procedural integrity in Chapter 14.)
6. Concurrency Control
A database system is a multiuser software system, meaning that many user applications access a database at the same time. Therefore, each database system must have some kind of control mechanism to ensure that several applications that are trying to update the same data do so in some controlled way. The following is an example of a problem that can arise if a database system does not contain such control mechanisms:
- The owners of bank account 4711 at bank X have an account balance of $2000.
- The two joint owners of this bank account, Mrs. A and Mr. B, go to two different bank tellers, and each withdraws $1000 at the same time.
- After these transactions, the amount of money in bank account 4711 should be $0 and not $1000.
All database systems have the necessary mechanisms to handle cases like this example. Concurrency control is discussed in detail in Chapter 13.
7. Backup and Recovery
A database system must have a subsystem that is responsible for recovery from hardware or software errors. For example, if a failure occurs while a database application updates 100 rows of a table, the recovery subsystem must roll back all previously executed updates to ensure that the corresponding data is consistent after the error occurs. (See Chapter 16 for further discussion on backup and recovery.)
8. Database Security
The most important database security concepts are authentication and authorization. Authentication is the process of validating user credentials to prevent unauthorized users from using a system. Authentication is most commonly enforced by requiring the user to enter a (user) name and a password. This information is evaluated by the system to determine whether the user is allowed to access the system. This process can be strengthened by using encryption.
Authorization is the process that is applied after the identity of a user is authenticated. During this process, the system determines what resources the particular user can use. In other words, information about a particular entity is available only to principals that have permission to access that entity. (Chapter 12 discusses these concepts in detail.)
Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.