Database Management Systems Introduction
A Database Management System, otherwise known as a DBMS, is a system that provides an interface to manage data stored in a database. A DBMS will provide a number of features including:
- The ability to connect the DBMS to a database
- Operations to store, manipulate and retrieve data from a database
- Reporting features to report on data within a database
- Support for administration including backup, creating and managing users, access permissions, etc.
DBMS & Databases - What's the difference?
What is the difference between a DBMS and a Database? Aren't they they same thing?
A DBMS is a software program that is used to interact with databases, while the database itself is a collection of data. Analogies can be drawn between a DBMS and a Word Processor. The word processor is a software program in a similar way to the DBMS, it exists to manipulate documents. The documents themselves are similar to a database in that it contains the data manipulated by the program. Remember that the DBMS is the software, and the database is data.
There are a variety of different DBMS software packages available in the market. These have different strengths and weaknesses, but all provide the ability to manage data. Unlike word processors, it is very unlikely that one DBMS will be able to read and manipulate data from a different DBMS's database file or data store. In order to get some consistency between DBMS products there is a standard language for working with relational databases called SQL. Each DBMS provides its own implementation of this standard, meaning that there are differences but on the whole many features are consistent across products.
Often people will refer to a DBMS as a database. While this is technically incorrect, it is very commonly encountered. You need to use the context of a discussion to determine if database is being used to refer to the DBMS or the database itself.
What does a DBMS do?
Primarily the DBMS allows us to manage data in a structured way, including integrating data from a variety of sources and sharing that data amongst a number of users and applications. In order to do this effectively the DBMS must provide a wide variety of features.
- Security: The DBMS must be able to control who has access to the data stored within each database. As part of this the DBMS must be able to handle different levels of access and ability including the ability to view, change, and administer different parts of each database.
- Logical data access: The DBMS allows its users to access data in a friendly way. Users do not need to worry about where or how the data is stored. This logical structure is typically represented as data records. The DBMS will then map this logical record structure onto physical media to ensure persistence.
- Multi-user access: DBMS products are usually capable of handling more than one user at a time. Integrating a wide range of data typically means that multiple groups of users will want to access the information, possibly at the same time.
- Transactions: The DBMS will allow actions to be performed in groups in such a way that either all of the actions complete, or they all fail. This is very important when you want to perform a group of related actions on the data in the database.
- Integrity constraints: Data integrity is important, and as a centralised source of data the DBMS's ability to enforce integrity is very important. DBMS products typically support a range of integrity constraints that help ensure that the data in the database is correct.
Who uses a DBMS?
The DBMS is used by a variety of different users. Broadly speaking, the database is used by application developers, end users, and administrators.
Application developers use DBMS products to manage data for their applications. This allows them to concentrate on building the application, allowing the database to handle the data management.
End users typically make use of the data stored in databases managed by the DBMS. These users may interact with the DBMS directly, but are more likely to interact with applications developed by the application developers. These users are interested in the data, and may now be aware of the database or DBMS at all.
Administrators ensure that everything keeps working. The administrators will interact with the database to add new users, perform backups, monitor performance, and many other aspects critical to ensuring the ongoing operation of the DBMS.
Why use a DBMS?
In many software applications there will be a large amount of data that needs to be managed. While it is possible to manage this data within the application, it is easier and more productive to delegate this responsibility to a DBMS. The DBMS is designed to manage data, using it to manage your data makes good sense. Making use of a DBMS to manage your data give you the following benefits:
- Shared data, using a DBMS will enable you to share data across a range of users.
- Reduced redundancy: a centralised database and structured data can help reduce redundancy.
- Better consistency: a centralised database will help with data consistency.
- Support for Transactions: a DBMS can support transactions, allowing related actions to succeed or fail as a group.
- Better data integrity: integrity constraints on the database can help ensure that the data it contains is correct.
- Data security: security features of the DBMS will allow data within the databases to be secured appropriately.
Working with a DBMS
The DBMS is a software product that needs to be installed on a machine that is capable of servicing its requirements. Small single user DBMS products run alongside other products on the clients machine, while DBMS products aimed at servicing large numbers of users are usually installed on a dedicated database server. Once the DBMS is installed it can be used to create and manage databases.
Because the data manipulated by the DBMS is contained within databases, one DBMS installation can be used to manage a number of databases. When working in this environment you will interact with the DBMS and ask it to connect to a certain database for you. Consider the Apache Derby DBMS, the following code illustrates using this DBMS to connect to a database and then asking it to show you all of the tables that exist in that database.
local:~ user$ ij ij version 10.2 ij> connect 'jdbc:derby:test'; ij> show tables; TABLE_SCHEM |TABLE_NAME |REMARKS ------------------------------------------------------------------------ APP |PRODUCT | APP |SUPPLIER | 2 rows selected ij> disconnect; ij> connect 'jdbc:derby:testdb'; ij> show tables; TABLE_SCHEM |TABLE_NAME |REMARKS ------------------------------------------------------------------------ APP |EMPLOYEE | 1 row selected ij> disconnect; ij> exit; local:~ user$
Use this how-to and create a database using Apache Derby.
The ij program is specific to Apache Derby and is used to issue interactive commands to the DBMS. When ij is executed, the ij> prompt is used to pass commands to the Derby DBMS. The connect command asks the DBMS to connect you to a certain database. In the above example we connect to two different databases to illustrate this capability of a DBMS.
Types of DBMS
Broadly speaking there are two categories of DBMS products, desktop and server.
- A desktop DBMS is aimed toward single user desktop applications. There are a number of different desktop DBMS products including Microsoft Access, Filemaker Pro, Paradox, Lotus Approach, and Fox Pro.
- A server DBMS is designed with security, reliability, and performance in mind. These DBMS products run as services on servers, and are connected to over the network. Server DBMS products include Apache Derby, Microsoft SQL Server, Oracle, IBM DB2, Sybase ASE, Informix, MySQL, and ProgreSQL.
When selecting a DBMS you need to consider the following kinds of issues:
- What technical requirements do you have for the data?
- How much data are you going to be storing?
- What kinds of reports do you want to get?
- Who will be using the databases managed by the DBMS?
- What kind of applications?
- Who has access to these applications? Do they have different levels of access?
- What kinds of roles will these people play?
- How will the data be accessed? Mostly read, write, or balanced?
- How much do you want to spend on the DBMS?
- What kind of hardware and operating system will it be running on?
Links to topics related to databases:
Links to external articles on DBMSs:
Here are some links to existing DBMS products: