Relational Databases#
A relational database is a type of database management system (DBMS) that is based on the relational model of data. In a relational database, data is organized into tables, and the tables are related to each other through the use of keys. This allows for the creation of complex, multi-dimensional data structures, and for the efficient querying and manipulation of data. Relational databases are widely used for a range of applications, from simple, single-user applications to large-scale, enterprise-level systems. Some examples of popular relational database management systems (RDBMS) include Microsoft SQL Server, Oracle Database, and MySQL. Relational databases are an important tool for storing and managing structured data, and they are the most common type of DBMS in use today.
SQL#
SQL (Structured Query Language) was first developed in the 1970s, and the first version of the SQL standard was published by the American National Standards Institute (ANSI) in 1986. SQL is a domain-specific language for managing data in relational databases, and it is widely used for a range of data management tasks, such as querying, inserting, updating, and deleting data. SQL has evolved over time, with new versions of the standard being published by ANSI and other organizations, and many different implementations and variations of SQL have been developed by different vendors. SQL remains a popular and widely used language for data management, and it is an essential skill for many data professionals and database administrators.
OLAP vs OLTP#
OLAP (Online Analytical Processing) and OLTP (Online Transaction Processing) are two different types of systems that are used to manage and analyze data.
OLAP is a type of system that is designed for analytical purposes and is used to support business intelligence and reporting activities. It is used to support multi-dimensional analysis of data, such as querying, drill-down, slice-and-dice, and pivot analysis. OLAP systems typically use a star or snowflake schema and are optimized for read-only access. They store historical data that allows users to analyze data over time and support complex queries.
OLTP, on the other hand, is a type of system that is designed for transactional purposes and is used to manage and store the day-to-day operations of an organization. It is optimized for high-concurrency and high-throughput, handling a large number of short, online transactions such as inserting, updating and deleting data. OLTP systems typically use a normalized data model and are optimized for write access. They store current data that allows users to manage, process and retrieve data quickly.
In summary, OLAP systems are optimized for analytical tasks, such as data mining, reporting, and forecasting, while OLTP systems are optimized for transactional tasks, such as inserting, updating, and deleting data. They have different design considerations and are used for different purposes.