What is a relational database and why use it?

Bim Building Information Modeling Software
Written by Marion Desvignes
Database
0 comment(s)
July 22, 2021

Home » Database » What is a relational database and why use it?

What is a relational database and why use it? Database management systems (SGDB or DBMS) are computer programs that allow users to interact with a database. A DBMS allows users to control access to a database, write data, run queries, and perform any other task related to database management.

To perform any of these tasks, the SGDB must have some sort of underlying model that defines how data is organized. A relational model is an approach to organizing data that has found wide use in database software since its initial conception in the late 1960s. So much so that, as of this text, four of the five SGDBs the most popular are relational.

This conceptual article describes the history of the relational model. How relational databases organize data, and how they are used today.

What is a database?

A database is a set of information organized to be easily consulted, managed, and updated. Within a database, the data are structured in lines, columns, and tables. They are indexed so that they can be easily found using computer software. Each time new information is added, the data are renewed and possibly erased.

They are responsible for establishing, updating, or deleting data themselves. They also search the data they comprise upon user request, and launch applications from the data.

Databases are used by many enterprises in all industries. Airlines use them to manage reservations. Manufacturing companies use them to manage production. Hospitals use them for medical records, and insurance companies use them for legal records. The largest databases are usually used by government agencies, large corporations, or universities.

Databases are plentiful online. They’re stowed as files or as a set of files on a magnetic disk, a cassette tape, optical disk, or other types of storage device. Traditional (hierarchical) databases are organized by fields, records, and files. A field is a single piece of information. A record is a collection of fields. A file is a collection of records.

The history of the database

The history of databases dates back to the 1960s, with the advent of network databases and hierarchical databases. In the 1980s, object-oriented databases arose. The major databases today are SQL, NoSQL, PostgreSQL – the one SeveUp App is working with- and online databases.

It is also possible to classify the databases according to their content: bibliographic, texts, numbers, or images. However, in computer science, databases are generally classified according to their organizational approach. There are many different types of databases: relational, distributed, cloud, NoSQL…

In the case of a large database, multiple users must be able to sway the information it contains quickly and at any time. In addition, large companies tend to accumulate many independent files including linked files or even intersecting data. For data analysis, it is necessary that data from multiple files can be linked. This is why different types of databases have been developed to meet these requirements: text-oriented, hierarchical, network, relational, object-oriented …

Different types of database-management

Hierarchical database

Hierarchical databases are among the oldest database-management. Within this category, records are organized in a tree structure. Each level of records results from a set of smaller categories.

Network database

Network databases are also among the oldest. Rather than providing unique links between different datasets at various levels, network databases create multiple links between sets by placing links, or pointers, on one set of records or another. The speed and versatility of network databases have led to massive adoption of this type of database within companies or in the field of e-commerce.

Text-oriented database

A text-oriented database, or flat-file database, comes in the form of a file (a table) in .txt or .ini format. A flat file is a text file or a file that unites text with a binary file. Usually, in these databases, each row has only one record. Most PC databases are text-oriented databases.

Distributed database

A distributed database is a database-management in which certain divisions are stored in several physical locations. Administering is distributed or replicated between different points in a network.

Distributed databases can be homogeneous or heterogeneous. In the case of a homogeneous distributed database system, all physical locations run with the same hardware and run under the same operating system and the same database applications. On the contrary, in the case of a heterogeneous distributed database, the hardware, operating systems, and database applications can fluctuate between different physical locations.

Cloud database

In this context, it is optimized or directly created for virtualized environments. It can be a private cloud, a public cloud, or a hybrid cloud.

Cloud databases suggest several benefits such as the ability to pay for storage capacity and bandwidth based on usage. In addition, it is possible to change the scale on request. These databases also offer higher availability.

NoSQL database

NoSQL databases are useful for large, distributed datasets. This is because relational databases are not designed for big data, and data sets that are too large can cause performance issues.

If a business needs to analyze large amounts of unstructured data or data stored on multiple virtual cloud servers, the NoSQL database is ideal. With the rise of Big Data, NoSQL databases are used more and more.

oriented databases are better suited for storing this type of content.

Rather than being organized around actions, object-oriented databases are organized around objects. Likewise, instead of being organized around logic, they are organized around data. For example, a media recording within a relational database can be defined as a data object rather than an alphanumeric value.

Object-oriented database

Objects created using object-oriented programming languages are typically stored on relational databases. However object-oriented databases are better suited for storing this type of content.

Rather than being organized around actions, object-oriented databases are organized around objects. Likewise, instead of being organized around logic, they are organized around data. For example, a media recording within a relational database can be defined as a data object rather than an alphanumeric value.

Graph-oriented database management system

A graph-oriented database, or graph, is a type of NoSQL database that uses graph theory to store, map, and perform queries on relationships between data. Graph databases are made up of nodes and edges.

Each node represents an entity, and each edge represents a connection between nodes. Graph databases are gaining popularity in the field of interconnection analysis. For example, companies can use a graph database to mine data on their customers from social networks.

More and more often, once separate database management systems are combined electronically into larger collections known as Data Warehouses. Businesses and governments then use data mining software to analyze different aspects of the data. For example, a government agency may do this to investigate a company or individual that has purchased a large amount of equipment, even if the purchases are scattered across the country or spread across multiple subsidiaries.

SQL database

SQL database is a relational database

Is SQL a relational database?

Relational databases were invented in 1970 by E.F. Codd of IBM. These are tabular documents in which the data is defined to be reachable and restructured in different ways.

Relational databases are made up of a set of tables. Within these tables, the data is classified by category. Each table has at least one column corresponding to a category. Each column contains a certain amount of data corresponding to this category.

The standard API for relational databases is Structured Query Language (SQL). Relational databases are easily expandable, and new categories of data can be added after creating the original database without needing to modify any existing applications.

In the very specific category of Open Source relational databases, there are three main references: MySQL, MariaDB, and PostgreSQL. These three management systems have several points in common: a very active support community, open code allowing users to modify them as they wish according to their needs, and free (for developers).

MySQL is a relational database management system (RDBMS) based on SQL (Structured Query Language). This RDBMS is compatible with all platforms: Linux, UNIX, and Windows. It can be used with many applications, but it is most often associated with web applications.

The Swedish company MySQL AB originally created MySQL. However, in 2008, Sun Microsystems bought it before it was acquired by Oracle in 2010. Developers can still use MySQL under the GPL license, but companies must purchase a commercial license from Oracle.

MySQL is also a component of the LAMP enterprise software suite, a web development platform using Linux as the operating system, Apache as the webserver, MySQL as the RDBMS, and PHP as the object-oriented scripting language.

PHP is indeed the most popular language for web development. It is a free, open-source, server-side language, which means that the code is executed on the server. The combination of PHP and MySQL allows you to create any type of site, from a simple contact form to a company portal.

PHP also allows you to connect a web script to a MySQL database. This is an essential step, essential to be able to make requests.

Why is it called a relational database

History of the relational model

In the late 1960s, Edgar F. Codd, a computer scientist at IBM, developed the relational database management model. Codd’s relational model allowed individual records to be associated with more than one table, thus allowing “many-to-many” relationships between data points in addition to “one-to-many” relationships. allowed greater flexibility than other existing models when it came to designing database structures, meaning that relational database management systems (RDBMS) could meet a much wider range of requirements. business needs.

Codd proposed a language for managing relational data, known as Alpha, which influenced the development of subsequent database languages. Two of Codd’s colleagues at IBM, Donald Chamberlin, and Raymond Boyce, have created such an Alpha-inspired language. They called their language SEQUEL, an anagram of Structured Query Language, but due to an existing trademark, they shortened their language’s name to SQL (more formally called Structured Query Language).

Due to hardware constraints, the first relational databases were exceedingly slow, and it took some time for the technology to become mainstream. But by the mid-1980s, Codd’s relationship model was implemented in a number of commercial database management products from IBM and its competitors. These companies have also followed IBM’s lead in developing and implementing their own dialects of SQL. By 1987, the American National Standards Institute and the International Organization for Standardization had both ratified and published standards for SQL, thereby consolidating its status as an accepted language for managing RDBMS.

The use of the relational model in several industries has led to its recognition as a standard model of data management. Even with the rise of various NoSQL databases in recent years, relational databases remain the dominant tools for storing and organizing data.

How relational databases organize data

Now that you have a general understanding of the history of the relational model, let’s take a closer look at how the model organizes data.

The central concept of the database relational schema is relationship. It was the British mathematician and theorist Edgar F. Codd who invented relational models. According to him, a relation represents a set of entities with the same properties. Each relationship is made up of a series of data records (called tuples) whose values are assigned to certain attributes.

The attributes that a relation contains and the data type to which the values assigned to the attributes correspond are defined using a relation diagram according to the following syntax:

R = (A1: Typ1, A2: Typ2,…, An: Typn)

The relationship schema (R) includes attributes A1 through An. A data type (Type1, Type2, etc.) is assigned to each attribute.

In order to be able to clearly represent the assignment of the individual values of a tuple to the attributes defined in the relational schema, a classic concept of information organization is used in the relational database model: the table. So a relational database is nothing more than a collection of related tables.

Tables are ordered diagrams made up of horizontal rows and vertical columns that collect and display information in an ordered form. Each row in a database table corresponds to a tuple. The values of the listed tuples are assigned to the attributes defined in the relation schema via the columns of the table.

Database relational scheme

A column is the smallest organizational structure in a relational database and represents the different facets that define the records in the table. Hence their more formal name, attributes. You can think of each tuple as a single instance of any type of people, objects, events, or associations that the table contains. These instances can be things like a company’s employees, a company’s online sales, or lab test results. For example, in a table containing teacher records from a school, tuples can have attributes like name, subjects, start_date, etc.

When you create columns, you specify a data type that dictates the type of entries allowed in that column. RDBMSs often implement their own unique data types, which may not be directly interchangeable with similar data types in other systems. The most common data types include dates, strings, integers, and Booleans.

In the relational model, each table contains at least one column that can be used to uniquely identify each row, called a primary key. This is important because it means users don’t have to know where their data is physically stored on a machine, instead, their DBMS can track every record and send it back on an ad hoc basis. This means that the records have no defined logical order and that users have the option of returning their data in any order or through any filters they wish.

Today, electronic data management is dominated by the relational database model. The most commonly used relational database management systems (RDBMS) are listed in alphabetical order:

Db2: Db2 is one of IBM’s proprietary relational DBMSs available to commercially licensed users.

Microsoft SQL Server: Microsoft’s SQL language database management system is available under a pay-per-user license.

 MySQL: MySQL is the most widely used open-source RDBMS in the world. Since its acquisition by Oracle, MySQL has been marketed under a dual license. The original developer community is continuing the project under the name MariaDB.

PostgreSQL: With PostgreSQL, users can access a free object-relational database management system (RDBMS). Further development is done by an open-source community.

Oracle Database: The relational database management system of the company of the same name Oracle is marketed under a proprietary license for a fee.

SQLite: SQLite is a public domain library containing a relational database management system.

What is the difference between a database and a relational database?

It can be tempting to see NoSQL databases as a replacement for relational databases. However, there may be room for both of these types of technology in most businesses.

SQL and NoSQL databases handle information in different ways and support different types of workloads. Rather than taking the place of relational databases, NoSQL databases allow companies to aim for new goals and meet new challenges.

It is important to know in which situation to use a NoSQL database, and in which situation it is better to use a relational database. Depending on the nature of the workload and the underlying data, a relational or NoSQL solution should be used.

Thus, NoSQL databases are best suited for large datasets that are frequently exposed to new information, where records have varying structures that do not match relational models well.

Another reason that non-relational databases won’t replace relational databases is that relational database vendors have improved their products to make them suitable for big data applications. Not only do these two technologies still have their place in business, but there is also room for both of them to continue to grow on their own.

What are the advantages & disadvantages of relational databases?

Relational databases have several advantages over traditional databases.

Structural independence: The relational database is only about data and not about a structure. This can improve the performance of the model.

Ease of use: The relational model is very intuitive to use because it is made up of organized tables of rows and columns.

Query capability: It allows a high-level query language like SQL to avoid complex navigation in the database.

Data independence: The structure of a database can be modified without having to change the application.

Data redundancy: A relational database ensures that no attributes are repeated.

However, it is important to keep in mind that relational databases can sometimes be slow and not very scalable:

Some relational databases have limits on the length of the fields used.

Relational databases can sometimes become complex as the number of data increases and the relationships between data items becomes more complex.

Complex relational database systems can lead to isolated databases where information cannot be shared from one system to another.

Conclusion

The relational pattern of databases is clear, mathematically sound, and has been proven in practice for over 40 years. However, storing data in structured tables does not meet all the requirements of modern information technology.

Administering large amounts of data for large data analyzes and storing abstract data types mostly push traditional relational systems to their limits. This is where specialized systems such as object-oriented databases or concepts developed as part of the NoSQL movement score points. However, the relational database model cannot be completely amortized. Relational databases offer many advantages, especially in business areas where transaction data processing is at the forefront.

Thanks to SeveUp App and our IFC converter we transform the .ifc bim model into a relational database which allows us to keep 100% of the relationships and thus to be able to visualize the IFC on Power BI thanks to our embedded 3D viewer.

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

Share