Which Type of Database Management is MySQL

What is a Database?

A database is a collection of data. Data organize is an efficient way for data to be retrieved easily. A database is designed in such a way that it can store large amounts of data. Following are the components of a Database.

What are the components of any database?

• Data: Data can be stored in any form, like text, number, or images.
• Tables: A table consists of rows and columns. A row represents the record, and a Column represents an Attribute.
• Field: Field means Attribute. A Column of any table generally represents the attribute.
• Records: Records means the data that you enter in the table. A Row of any table represents records.
• Schema: The structure of any database is called Schema. Using a schema diagram, we can easily identify the relationship between data.
• Database Management System: This is a software that provides an interface for interacting with the database.

What is a Relational Database?

A Relational Database is a type of database. In a Relational Database, data is organized according to the relationship between data. In 1970, Edgar F. Codd introduced this concept. The following are the main features of Relational Database.

• Tables
• Rows
• Columns
• Keys
• Relationships
• Normalization

Let us explain each.

1. Tables

The data in the database is stored in a row-column fashion. Example- Below is the student table. ID, Name, birth, Gender, and CGPA are the attributes of the table. ID is a primary key.

Run the following query.

Output:

2. Rows

The row is nothing but a record.

This is a horizontal record.

3. Columns

Column is the Attribute of a table. It is a vertical record.

4. Keys

In Relational Database Management System (RDBMS), keys are used to establish relationships between columns and different tables. There are many keys, such as Primary Key, Foreign Key, Composite Key, Candidate Key, Unique Key, and Super Key.

• Primary Key

A primary key is a unique, not null, value in a table. A table can have only one primary key. The Column that is defined as a primary key cannot have duplicate value.

Example

In the above table, ID is a unique and NOT NULL value. ID is a primary key.

• Foreign Key

A foreign key in a table refers to the primary key in another table. By defining a foreign key, we can make a relationship between two tables.

Example

Student Table:

Marks Table:

In the above tables, ID is establishing a relationship between two tables. ID is a foreign key.

• Composite key

A composite key consists of multiple columns that, when combined, uniquely identify a record in a table. This is useful when a single column cannot guarantee uniqueness.

Example:

In the above table, ID and Course_ID together make a Composite Key.

• Candidate Key

A candidate key is a set of one or more Columns that can be uniquely recognized. One of the candidate keys must be a primary key.

Example:

Vehicle Table:

In the above table, License_Plate and VIN both can be candidate keys.

• Unique Key

A unique key is the same as a primary key, but a unique key can have a null value.

Example

Student Table:

In the above table, Email is a unique key, but it also has no null values.

• Super Key

A super key is a set of one or more columns that, taken together, uniquely identify a record. It may contain more columns than necessary to identify a record uniquely.

Example:

Product Table:

In the above table, Product_ID and Supplier_ID both can be super key.

5. Relationships:

Relational Database Management Systems (RDBMS) establish relationships through primary and foreign keys, ensuring data integrity and efficient retrieval. Common types include primary and foreign keys. Some common types of relationships are

• One-to-one (1:1) relationship: In the case of One-to-one (1:1) relationship, each record in the first table is similar to only one record in the second table.
• One-to-many (1:M) relationship: In the case of a One-to-many (1:M) relationship, each record in the first table is similar to the multiple records in the second table.
• Many-to-one (M:1) relationship: In the case of the Many-to-one (M:1) relationship, the many records in the first table are similar to the one record in the second table.
• Many-to-many (M: M) relationship: In the case of the Many-to-many (M: M) relationship, each record in the first table is similar to the second table and vice versa.

6. Normalization:

Normalization is a process in relational databases to reduce redundancy, dependency, and anomalies. It involves decomposing tables and reorganizing data into different forms, each with specific rules.

• First Normal Form (1NF)

In the case of the First Normal Form (1NF), each Row contains a single value. You cannot have multiple values.

Example:

• Second Normal Form (2NF)

In the case of the Second Normal Form (2NF), the table must be in 1NF and make sure that every non-key Attribute depends fully on the primary key.

After applying 2NF, the table will be:

Instructor Table:

• Third Normal Form (3NF)

In the case of the Third Normal Form (3NF), the table must be in 2NF, and discard all dependencies.

What is Relational Database Management System (RDBMS)?

A Relational Database Management System (RDBMS) is a database software. It manages relational databases. It also allows us to interact with our database. We can insert, update, or delete our data. Some Relational Database Management Systems (RDBMS) are MySQL, PostgreSQL, Oracle DB, SQL Server, and SQL Lite.

1. MySQL

MySQL is an open-source software. It is very well known for its speed and high performance. Vertical and Horizontal scaling supports both. ACID property is another great feature of MySQL.

2. PostgreSQL

PostgreSQL is a database software with advanced features like full-text search JSON support. It is open-source for standards compliance, maintains data integrity via constraints, triggers, and rules, and supports Multi-Version Concurrency Control for effective transaction processing.

3. Oracle DB

Oracle DB is a highly scalable, secure, high-availability, comprehensive data warehousing, business intelligence, and analytics product with robust security features, high availability through Oracle Real Application Clusters, and extensive support options for PL/SQL, a procedural language extension to SQL.

4. SQL Server

With other Microsoft products, business intelligence features, scalable capabilities, robust security features, full-text search capabilities, and graph database support for efficient text searching, modelling, and querying graph data, the Microsoft Ecosystem software offers tight integration.

5. SQL Lite

SQLite is a self-contained, serverless, zero-configuration database engine advisable for single-user applications and lightweight situations. On various platforms, requires minimal setup and administration, and supports ACID transactions, it works seamlessly for data consistency and reliability.

In this tutorial, we will discuss MySQL as a Relational Database Management System (RDBMS)

Historical Background of MySQL:

MySQL, founded in 1995 in Sweden, was initially developed by Unireg and later by MySQL. In 2000, it became open source, with Marten Mickos as CEO in 2001. MySQL expanded its headquarters to the USA and Sweden in 2002. In 2003, it partnered with SAP, resulting in several features. In 2005, MySQL Network was launched, and in 2008, it was acquired by Sun Microsystems.

Features of MySQL:

1. Easy to access

Since MySQL is open-source, any person can download, use, and modify the software. It can be easy to use and provided free of cost. MySQL's source code can be accessed for study and customization according to needs. It makes use of the GPL, or GNU General Public License, which provides restrictions for what is permissible and not permissible with the program.

2. Rapid and Trustworthy

MySQL effectively saves information in memory to ensure consistency and prevent duplication. MySQL enables rapid access to and manipulation of data.

3. Adaptable

The capacity of a system to work well with large or small groups of machines and other types of data is referred to as scalability. The MySQL server was created for handling big databases.

4. Data Formats

Numerous data types are supported, including float (FLOAT), double (DOUBLE), character (CHAR), variable character (VARCHAR), text, date, time, datetime, timestamp, year, signed and unsigned integers, and many more.

5. Character Groups

It is compatible with other character sets, such as German, Ujis, Latin 1 (cp1252 character encoding), other Unicode character sets, and so forth.

6. Be protected

As a result of its customizable password system that verifies the password according to the host before allowing access to the database, it offers a safe interface. When the password is being connected to the server, it is encrypted.

7. Support with big databases

Large databases, with up to 5,000,000,000 rows, 150,000-200,000 tables, and 40-50 million records, are supported by this software.

Architecture of MySQL (Client-Server Model)

MySQL architecture is the same as the client-server model. The MySQL database is mainly divided into three parts: the client, the server, and the Storage layer.

Client Layer

The client Layer is the highest layer among all the layers. The Client Layer gets a request from the user via a command prompt or GUI and passes it to the server. Responsibilities of the client layers are:

• Managing Connections: The server passes a request to the client, and the client accepts that connection at that time. After the connection, the client will generate its thread.
• Verification: If the client is connected with the server, it will verify using username and password.
• Provide security: After the verification, the authorized server will interact with the MySQL database.

Server Layer

The Server Layer is the second layer after the client layer. This layer deals with all the logical functions of the database. Sometimes, it is called the Barin of MySQL Architecture. The following are the parts of the server layer.

• Managing Thread: If a client passes a request to the server and the server will accept that request, then the server layer receives the thread connection module.
• Parser: A parser will generate a parse tree structure as an output from the input.
• Optimizer: After the parse tree, various optimization techniques will take place.
• Query Cache: The Query Cache stores the complete result set for an inputted query statement, and the MySQL Server consults it even before parsing.
• Buffer and Cache: The Query Cache buffers user queries, checking if they are available in the cache and providing output without interfering with Parser or Optimizer.
• Table Metadata Cache: The metadata cache is a memory area used to track information on databases, indexes, or objects, with a larger size corresponding to the number of open databases or objects.
• Key Cache: Using a key cache to identify objects uniquely, Edge servers ordinarily cache content based on the unified resource path and a query string.

Storage Layer

Making it a widely used RDBMS, MySQL's unique Storage Engine Layer is preferred by developers. Disparate types of storage engines like InnoDB, MyISAM, NDB, and Memory are used for discrete situations.

Components of MySQL

MySQL is a popular open-source relational database management system (RDBMS). The main components of MySQL are -

• MySQL Server

The MySQL Server is the main element of MySQL advertised to the client. It has a responsibility to action the query and manage the transaction.

• Storage Engine

MySQL guards multiple storage engines to store, retrieve, and index data. Each storage engine has its connotation. Some familiar storage engines are InnoDB, MyISAM, MEMORY, etc.

• Connectors

The connectors are the software entrails. Between MySQL servers and other podiums, it provides an interface. Some common adapters are MySQL Connector/J:Java, MySQL Connector/C++: C++, etc.