Relational RDBMS : MySQL, PostgreSQL and SQL SERVER

Relational RDBMS MySQL, PostgreSQL and SQL SERVER Dalila CHOUAYA 2021-2022

PLAN Introduction MySQL Definition My SQL’s Supported Data Types Advantages & Disadvantages When To Use My SQL When To Not Use My SQL Postgre SQL Definition My Postgre SQL’s Supported Data Types Advantages & Disadvantages When To Use Postgre SQL When To Not Use Postgre SQL Microsoft SQL Server Definition My Postgre SQL’s Supported Data Types Advantages // Disadvantages DB Management systems comparison Conclusion 1

Introduction The relational data model, which organizes data in tables of rows and columns, predominates in database management tools. Today there are other data models, including NoSQL and NewSQL, but relational database management systems (RDBMSs) remain dominant for storing and managing data worldwide. This presentation compares and contrasts three of the most widely implemented open- source RDBMSs : SQLServer, MySQL, and PostgreSQL. Specifically, it will explore the data types that each RDBMS uses, their advantages and disadvantages, and situations where they are best optimized. 2

MySQL According to the DB-Engines Ranking, MySQL has been the most popular open-source RDBMS since the site began tracking database popularity in 2012. It is a feature-rich product that powers many of the world’s largest websites and applications, including Twitter, Facebook, Netflix, and Spotify. Getting started with MySQL is relatively straightforward, thanks in large part to its exhaustive documentation and large community of developers, as well as the abundance of MySQL-related resources online. MySQL was designed for speed and reliability, at the expense of full adherence to standard SQL. The MySQL developers continually work towards closer adherence to standard SQL, but it still lags behind other SQL implementations. It does, however, come with various SQL modes and extensions that bring it closer to compliance. 3

MySQL MySQL has inspired a wealth of third-party applications, tools, and integrated libraries that extend its functionality and help make it easier to work with. Some of the more widely-used of these third-party tools are phpMyAdmin, DBeaver, and HeidiSQL. Unlike applications using SQLite, applications using a MySQL database access it through a separate daemon process. Because the server process stands between the database and other applications, it allows for greater control over who has access to the database. 4

MySQL MySQL’s Supported Data Types MySQL’s data types can be organized into three broad categories: numeric types, date and time types, and string types. Numeric types: 5

MySQL MySQL’s Supported Data Types Numeric types: 6

MySQL MySQL’s Supported Data Types Date and time types: 7

MySQL MySQL’s Supported Data Types String types: 8

MySQL MySQL’s Supported Data Types String types: 9

MySQL Disadvantages ★ Known limitations : Designed for speed and ease of use rather than full SQL compliance, ★ Licensing and proprietary features : Dual-licensed software, with a free and open- source community edition licensed under GPLv2 ★ Slowed development: acquired by Sun Microsystems in 2008, and later by Oracle Corporation in 2009, Advantages ★ Popularity and ease of use: there’s no shortage of database administrators who have experience working with MySQL. ★ Security : comes installed with a script that helps you to improve the security of your database by setting the installation’s... ★ Speed : Developers able to prioritize speed. ★ Replication: Supports a number of different types of replication. 10

MySQL ● Distributed operations: MySQL’s replication support makes it a great choice for distributed database setups like primary-secondary or primary-primary architectures. ● Websites and web applications: MySQL powers many websites and applications across the internet. This is, in large part, thanks to how easy it is to install and set up a MySQL database, as well as its overall speed and scalability in the long run. ● Expected future growth: MySQL’s replication support can help facilitate horizontal scaling. Additionally, it’s a relatively straightforward process to upgrade to a commercial MySQL product, like MySQL Cluster, which supports automatic sharding, another horizontal scaling process. When To Use MySQL 11

MySQL ● SQL compliance is necessary: Since MySQL does not try to implement the full SQL standard, this tool is not completely SQL compliant. If complete or even near-complete SQL compliance is a must for your use case, you may want to use a more fully compliant DBMS. ● Concurrency and large data volumes: Although MySQL generally performs well with read-heavy operations, concurrent read-writes can be problematic. If your application will have many users writing data to it at once, another RDBMS like PostgreSQL might be a better choice of database. When Not To Use MySQL 12

Postgre SQL Postgre SQL also known as Postgres, is a free and open-source relational database management system (RDBMS) emphasizing extensibility and SQL compliance. It was originally named POSTGRES, referring to its origins as a successor to the Ingres database developed at the University of California, Berkeley.In 1996, the project was renamed to PostgreSQL to reflect its support for SQL. After a review in 2007, the development team decided to keep the name PostgreSQL and the alias Postgres. It is designed to handle a range of workloads, from single machines to data warehouses or Web services with many concurrent users. It is the default database for macOS Server and is also available for Windows, Linux, FreeBSD, and OpenBSD. PostgreSQL features transactions with Atomicity, Consistency, Isolation, Durability (ACID) properties, automatically updatable views, materialized views, triggers, foreign keys, and stored procedures 13

Postgre SQL Postgres is capable of efficiently handling multiple tasks at the same time, a characteristic known as concurrency. It achieves this without read locks thanks to its implementation of Multiversion Concurrency Control (MVCC), which ensures the atomicity, consistency, isolation, and durability of its transactions, also known as ACID compliance. PostgreSQL isn’t as widely used as MySQL, but there are still a number of third-party tools and libraries designed to simplify working with with PostgreSQL, including pgAdmin and Postbird. 14

Postgre SQL’s Supported Data Types PostgreSQL supports numeric, string, and date and time data types like MySQL. In addition, it supports data types for geometric shapes, network addresses, bit strings, text searches, and JSON entries, as well as several idiosyncratic data types. Numeric types: Postgre SQL 15

Postgre SQL’s Supported Data Types Character types: Postgre SQL 16

Postgre SQL’s Supported Data Types Date and time types: Postgre SQL 17

Postgre SQL’s Supported Data Types Geometric types: Postgre SQL 18

Postgre SQL’s Supported Data Types Network address types: Postgre SQL Bit string types: 19

Postgre SQL’s Supported Data Types Text search types: Postgre SQL JSON types: 20

Postgre SQL’s Supported Data Types Other data types: Postgre SQL 21

Postgre SQL Disadvantages ★ Memory performance : For every new client connection, PostgreSQL forks a new process. Each new process is allocated about 10MB of memory, which can add up quickly for databases with lots of connections. ★ Popularity : Although more widely used in recent years, PostgreSQL historically lagged behind MySQL in terms of popularity. Advantages ★ SQL compliance : Supports 160 out of the 179 features required for full core SQL. ★ Open-source and community- driven : Developed by a large and devoted community. ★ Extensible : Users can extend PostgreSQL programmatically and on the fly through its catalog-driven operation and its use of dynamic loading. 22

Postgre SQL ● Data integrity is important: PostgreSQL has been fully ACID-compliant since 2001 and implements multiversion currency control to ensure that data remains consistent, making it a strong choice of RDBMS when data integrity is critical. ● Integration with other tools: PostgreSQL is compatible with a wide array of programming languages and platforms. This means that if you ever need to migrate your database to another operating system or integrate it with a specific tool, it will likely be easier with a PostgreSQL database than with another DBMS. ● Complex operations: Postgres supports query plans that can leverage multiple CPUs in order to answer queries with greater speed. This, coupled with its strong support for multiple concurrent writers, makes it a great choice for complex operations like data warehousing and online transaction processing. When To Use PostgreSQL 23

Postgre SQL ● Speed is imperative: At the expense of speed, PostgreSQL was designed with extensibility and compatibility in mind. If your project requires the fastest read operations possible, PostgreSQL may not be the best choice of DBMS. ● Simple setups: Because of its large feature set and strong adherence to standard SQL, Postgres can be overkill for simple database setups. For read-heavy operations where speed is required, MySQL is typically a more practical choice. ● Complex replication: Although PostgreSQL does provide strong support for replication, it’s still a relatively new feature and some configurations — like a primary-primary architecture — are only possible with extensions. Replication is a more mature feature on MySQL and many users see MySQL’s replication to be easier to implement, particularly for those who lack the requisite database and system administration experience. When Not To Use MySQL 24

Microsoft SQL Server Microsoft SQL Server is a relational database management system developed by Microsoft. As a database server, it is a software product with the primary function of storing and retrieving data as requested by other software applications—which may run either on the same computer or on another computer across a network (including the Internet). Microsoft markets at least a dozen different editions of Microsoft SQL Server, aimed at different audiences and for workloads ranging from small single-machine applications to large Internet-facing applications with many concurrent users. 25

Microsoft SQL Server MsSQL’s Supported Data Types 26

Disadvantages ★ Cost-consuming : Being mostly used at enterprise scale, MSSQL Server remains one of the most expensive solutions. ★ Unclear and floating license conditions. ★ Complicated tuning process : For those beginners who have to operate heavy data sets, working with query optimization and performance tuning may be problematic. Advantages ★ Variety of versions : Provides a wide choice of different options with diverse functionalities. ★ End-to-end business data solution : Developed by a large and devoted community. ★ Rich documentation and community assistance. ★ Cloud database support : Being a part of the consistent Microsoft ecosystem, MSSQL can be integrated with Microsoft cloud, Azure SQL Microsoft SQL Server 29

DB management systems comparison DB management systems comparison 30

Conclusion Today, SQLite, MySQL, and PostgreSQL are the three most popular open- source relational database management systems in the world. Each has its own unique features and limitations, and excels in particular scenarios. There are a quite a few variables at play when deciding on an RDBMS, and the choice is rarely as simple as picking the fastest one or the one with the most features. 31

Relational RDBMS : MySQL, PostgreSQL and SQL SERVER

More Related Content

What's hot

Similar to Relational RDBMS : MySQL, PostgreSQL and SQL SERVER

Recently uploaded

Relational RDBMS : MySQL, PostgreSQL and SQL SERVER