The DBMS relational database management system is essentially nothing more than a computerized system that allows you to store data. Users are provided with the means to perform several types of operations on data in the database or to manage its structure. DBMSs are classified according to structures.
Development history
The RDBMS relational database was invented in the early 1970s by E. F. Codd, a young IBM scientist and programmer. In a special article on RDBs, he proposed moving from storing data in hierarchical structures to organizing them in tables that have rows and columns.
By the 1960s, a huge amount of data had been collected stored on the new mainframe computers in the world, many of which were IBM System 360 computers. This has become a problem for the further development of digital technologies. Mainframe calculations were expensive, often costing hundreds of dollars per minute. A significant part of these costs was the complexity associated with database management (DB).
In 1973, the San Jose lab, now Almaden, began to develop a program called System R (relational) in order to apply the theory of relations using what is called industrial implementation. This quality has become decisive in determining which DBMSs are called relational. As a result of this project, a revolutionary new storage system was invented that became the basis for IBM's success.
Don Chamberlin and Ray Boyce invented SQL for structured data, which is the most widely used today. Patricia Selinger has developed a cost-based optimizer that makes working with relational databases more cost-effective and efficient. And Raymond Laurie invented a compiler that saves database query procedures for future use.
In 1983, IBM introduced the second family of DB2 relational DBMSs for data management. Today, DB2 continues to make billions of transactions every day, being IBM's most successful software product. According to Arvind Krishna, general manager of IBM Information Management, DB2 continues to be a leader in innovative relational database (RDB) software.
Dr. Codd, known to his colleagues as Ted, was awarded the title of IBM Fellowship in 1976, and in 1981, the Computing Technology Association awarded him a Turing Prize for his contributions to the development of RDB.
Principles of Creation
Each table, which is also called a relation in the relational database of a DBMS, contains one or several categories of data in the attribute columns. Each row, called a record, or tuple, contains a unique instance of data or a key for the categories identified by the columns. A table has a unique primary key that identifies the information in it. Table communication is established using foreign keys that reference the primary keys of another table.
For example, a typical relational database database of business orders has a table that describes the client, with columns for name, address, phone number, and other information. The following has an order: product, customer, date, sale price, and so on. The RDB user gets an idea of the database in accordance with his needs. For example, a branch manager might like to view or report on all customers who bought goods after a certain date. A financial services specialist in the same company from the same tables receives a report on the bills that must be paid.
Terms and Types
Relational DBMSs include tables containing rows and columns. When creating the RDB, the range of possible values in the data column and additional restrictions that can be applied to this value are determined. For example, a customer domain can resolve up to 10 possible names, but in one table you can limit it to only three of these customer names. Two limitations relate to data integrity, as well as primary and foreign keys. The integrity of the object ensures that the primary key is unique and that the value is not equal to zero. Referential integrity requires that each value in a foreign key column be found in the primary key of the table from which it originated.
There are a number of categories of databases: from simple flat files that are not related to NoSQL, to newer graph files, which are considered even more relational than standard ones. The flat file database consists of a single table that has no relationship, usually text files. It allows users to specify data attributes, such as columns and types, in relational DBMSs.
Alternative structures
The NoSQL database is an alternative to the RDB, which is especially useful for working with large sets of distributed data.
The graph database goes beyond the traditional models of relational data based on columns and rows. NoSQL has nodes and edges that represent connections between data relationships and discover new ones between them. Graph databases are more complex than RDBs, and therefore their use includes fraud detection mechanisms or web recommendations.
Relational DBMS Examples
SQLite is a popular open source SQL database. The software can store the entire database in one file. The most significant advantage that it provides is that all data can be stored locally without connecting to the server. SQLite has become popular for databases in mobile phones, PDAs, MP3 players, set-top boxes and other electronic gadgets.
MySQL is another popular open source relational SQL database model. It is commonly used in web applications and is often available using PHP. Its main advantages are ease of use, affordability, reliability. Some of the drawbacks are that it suffers from poor performance when scaling, open source development has lagged since Oracle took control of MySQL and does not include some advanced features.
PostgreSQL is an open source relational SQL database model that is not controlled by any corporation. It is usually used to develop web applications. PostgreSQL is a simple, reliable and low-cost program with a large community of developers. It has additional functions in the form of foreign key support, without requiring complex configuration. Its main drawback is that it works more slowly than other databases, such as MySQL. It is also less popular than MySQL, which makes it difficult for hosts or service providers that offer managed PostgreSQL instances to access.
RDBMS Management System
RDBMS is a relational database management system developed by EF Codd from IBM and is able to create, modify and administer RDBs. Many existing databases today are a continuation of this century-old model. Stored data is processed using relational operators in the RDBMS.
SQL is used as a database query language - it is a logical group of data. It contains a set of related table and index spaces. Typically, a database contains all the data associated with a single application or related group. For example, there may be a salary or inventory database.
Differences between RDBMS and a regular DBMS
The DBMS stores the data in the form of files, while the RDBMS stores the data in the form of a table. The DBMS allows you to normalize the data, and RDBMS maintains a connection between the data stored in its tables. A regular DBMS does not provide links. It just stores the data of its files. The structured approach of RDBMS supports distributed RDB as opposed to a conventional database management system. The DBMS is focused on a wide range of applications, its features allow it to be used all over the world.
Features of RDBMS:
- The overall implementation of the column as well as multi-user access are included in the RDBMS functions.
- The potential of this model of relational DBMS was more than justified by modern application possibilities.
- Better security is provided by creating tables.
- Some tables may be protected by the system.
- Users can set barriers to access to content. This is very useful in companies where the manager can decide what data is provided to employees and customers. Thus, you can configure an individual level of data protection.
- Meeting future requirements as new data can easily be added to existing tables and aligned with previously available content. This is a feature that is not found in any flat file database.
Structural table
A table is a logical structure made up of rows and columns. Rows do not have a fixed order, so if you are extracting data, you may need to sort them. The order of the columns is indicated when the table is created by the DBA. At the intersection of each column and row is a specific data item called a value, or more precisely, an atomic value. The table is called the high-level classifier of the owner user ID, followed by the table name, for example TEST.DEPT or PROD.DEPT.
There are several types of tables:
- A base that is created and contains persistent data.
- Temporary, in which the intermediate results of the query are stored.
Table Elements:
- The columns have an ordered set: DEPTNO, DEPTNAME, MGR, and ADMK DEPT. All of them must be the same data.
- Rows - each contains data for one department.
- The values at the intersection of the column and row. For example, PLANNING is the value of the DEPT NAME column in a row for department B01.
An index is an ordered set of pointers to rows in a table. Unlike table rows that are not in a specific order, the DB2 index must always maintain order.
The index is used for two purposes:
- To increase the speed of obtaining data values.
- For uniqueness.
By creating an index by employee name, you can get data for that employee faster than scanning the entire table. In addition, by creating it, DB2 will ensure that each value is unique. Creating an index automatically creates an index space, a data set that contains it.
Key keys
A key is one or a series of columns that are identified as such when created in the definition of referential integrity. A table has only one primary key, because it defines an entity. There are requirements for him:
- It must matter, that is, not be null.
- It must have a unique index.
- You can have more than one unique key in a table.
- Foreign key - a foreign key specified in the referential integrity constraint so that its existence depends on the primary or parent key.
Network Database Model
This database allows records to have many parent and child formats that can visualize them as a network structure. In contrast, in a hierarchical element, a relational DBMS has a number of child and one parent. In fact, the network model is very similar to the hierarchical one, being a subset of it. However, instead of using a single parent, the network model uses set theory to provide a tree hierarchy. The exception is that you can have more than one parent on child tables.
Advantages of a network database:
- Conceptually simple and easy to develop.
- Access to data is simpler and more flexible with respect to the hierarchical model and does not allow a member to exist without a parent.
- It can process complex data due to its many-to-many relationship. This allows a more natural modeling of relations between records or objects of a relational DBMS, as opposed to hierarchical.
- Due to its flexibility, it moves more easily and finds information in a network database.
- This structure isolates control programs from complex physical data.
Object oriented system
In an object-oriented database, all data is an object. They can be related to each other by the “is part” relationship to represent larger constituent elements.
For example, data describing a car can be stored as part of a specific engine, chassis, gearbox, steering system, etc. Object classes can form a hierarchy in which individual objects inherit properties from the objects above. For example, all objects of the class "motor transport" will have an engine (truck, car or plane). Similarly, engines are also data objects, and the engine attribute of a particular vehicle will be a reference to a specific engine object.
Multimedia databases in which voice, music and video are stored together with traditional textual information serve as the basis for viewing data in the form of objects. Such object-oriented databases are becoming increasingly important as their structure is the most flexible and adaptable. The same applies to the database of images, photographs or maps. The future of database technology is usually perceived as an integration of relational and object-oriented models.
Design process
Database design is more an art than a science, as the user will have to make many decisions. Databases are usually configured for a specific application. There are no two identical user applications, and therefore, there are no two identical databases. Guidelines usually indicate what should not be done, although the choice ultimately depends on the designer.
Design Algorithm:
- Determine the purpose of the database for requirements analysis.
- Gather requirements. They collect data, organize tables, and indicate primary keys.
- One or more columns are selected as the so-called primary key to identify rows.
- Create relationships between tables. The strength of a relational database is in the relationship between tables. The most important aspect in the development of the RDB is to identify the relationships between them.
- You must select the required data type for a specific column. Typically, data types contain: integers, a string (or text), a date, time, binary code, a collection, such as an enumeration and a set.
- Refine the design by adding more columns.
- Create a new table for optional data using a one-to-one relationship.
- Break a large table into two smaller tables.
- Normalization rules are applied to check if the database is structurally correct and optimal.
- An index can be defined for a single column, a set of columns called a composite index, or a part of a column called a partial index. You can create more than one index in a table. For example, if you often look for a customer using either customer Name or phone Number, you can speed up the search by building an index on the customer Name column, as well as phone Number.
- Most DBMSs automatically build the index on the primary key.
Creating an Access Database
When using the relational Access DBMS, you cannot just start the data entry process. It is necessary to apply the design of the RDB by dividing the information block into a number of tables. They are joined using relational joins when the field of one matches the field of the other table.
Database creation algorithm:
- Pre-determine the data and make a list of required fields (pieces of information) using different types of data.
- Eliminate extra fields. It is not allowed to store the same information in more than one place. In the case when you can calculate one field using another, save one.
- Organize the field. They are formed according to the description, in connection with which each group is converted into a table.
- Add code tables with abbreviations.
- Include a table of names and two-letter codes in the database.
- Choose a primary key.
- Link tables.
Thus, we can conclude that the main advantages of RDBs are that they allow users to simply classify and store data, are easily expandable, and are independent of physical organization. After creating the original database, you can add a new data category without changing all existing applications.