What are the Logical database structures that programmers see?
In the beginning, the traditional databases typically used SQL databases to manipulate the collected data which had to predefine schema to better perform in the operations (view, select, update, and delete data). SQL database is a relational database (RDBMS) as we know that its data structure is organized in terms of tabular data. Since the data structure on RBDMS rigidly fixes table attributes and its infrastructure scalability vertically, SQL database cannot respond to the extreme popularity of internet uses and is also inefficient for big data management. Whereas NoSQL database has the ability to improve database performance in horizontal scaling by adding more servers to distribute the large data faster than SQL database.
As a result, NoSQL Database product (DBMS) or “Not only SQL” was introduced to solve the weakness of traditional database structure and data manipulation. Currently, NoSQL databases are designed to support multiple data modes such as images, videos, XML, and JSON files so that programmers can store the data in a different format. NoSQL databases offer more flexibility to programmers and users rather than traditional SQL databases because they store structured (e.g., data captured from sensors), unstructured (images, videos, etc.), and semi-structured (XML, JSON, etc.) data.
There are 4 main types of NoSQL databases.
- Document databases: to store and query JSON, XML, BSON, etc., documents. Each document is a row or a record in the database and is in the key-value format. A document stores information about one object and its related data. For instance, the following database contains three records, each one giving information about a student. For the first document, the first name is key, and Franck is its value.
- Key-value stores: The first column contains a unique key. The second column is the value for each key. The values can be in different data types, such as integer, string, and float, or more complex data types, such as image and document.
- Column-oriented databases: a collection of columns, where each column is treated separately, and the implementation logic is based on Google Big Table paper.
- Graph databases: to store, map and search relationships between nodes through edges. A node represents a data element, also called an object or entity. Each node has an incoming or outcoming edge. An edge represents the relationship between two nodes. Those edges contain some properties corresponding to the nodes they connect.
A comparison between SQL and NoSQL data types
SQL and NoSQL data types
In addition, NoSQL database supports multiple data structures causing rapid adaptation to change requirements with frequent updates and beneficially developed across the system or application which has a lot of data transactions to handle.
Integrity rules on the logical database structures (if any)
Data integrity is normally enforced in a database system by a series of integrity constraints or rules. The relational model is designed to enable the database to enforce referential integrity between tables in the database, be normalized to reduce data redundancy, and be generally optimized for storage. Relational databases typically have ACID (Atomicity, Consistency, Isolation, Durability) properties, while non-relational databases are independent of these conditions. In RDBMS, programmers manually predefine data tables and assign the relationships among them by setting constrain such as the primary key and foreign key following ACID properties as shown in the figure non-relational database does not incorporate the table model and stored data in a document file.
RDBMS and DBMS data integrity
On the other hand, NoSQL does not support ACID (atomicity, consistency, isolation, durability) as shown in the figure during transactions across multiple data types. With appropriate schema design, single-record atomicity is acceptable for lots of applications since NoSQL databases provide a variety of data models as mentioned in the data structure designed such as key-value, document, and graph. In this model, data is optimized for intuitive development and horizontal scalability. The programmers optionally adopt NoSQL database varies on the product use case as shown in the figure.
SQL vs NoSQL product use case
NoSQL databases largely find use in applications involving large data and real-time uses. It is conceptual to perform on big data without concerning data consistency (duplicated files, web posts, user comments, etc.). By this drawback, the programmer has the responsibility to prevent data integrity violations by their application configuration. However, NoSQL database allows greater data manipulation flexibility and reduces maintenance costs as your data needs change. Some NoSQL databases also support compression to reduce the storage footprint.
Database language used on the logical database structure
Typically, SQL is the standard language for Database Systems. In SQL databases, we effectively generate queries to retrieve or update the databases by using SQL language. It also allows you to make relationships between tables, which can be one-to-one, one-to-many, many-to-one, or many-to-many. All the Relational Database Management Systems (RDMS) like MySQL, MS Access, Oracle, Sybase, Informix, Postgres, and SQL Server use SQL as their standard database language. Notice that SQL restricts the user to work within a predefined tabular schema, and more care must be taken to organize and understand the data before it is used. In contrast, NoSQL languages lack the standard interface which SQL provides, so more complex queries can be difficult to execute as shown in the figure.
SQL vs NoSQL database language
NoSQL databases span a variety of data types and implementations which is encouraging greater flexibility than SQL databases. NoSQL databases utilize one of a kind and non-general dialects. There is very little consistency between NoSQL languages, as they concern a diverse set of unrelated technologies and multiple data-mode. Therefore, Many NoSQL databases provide a unique data manipulation language constrained by particular structures and capabilities. NoSQL database systems are well known in this world as shown in the figure (e.g., MongoDB, Cosmos DB, Cassandra CQL, Elasticsearch SQL, Cockroach Labs) using their NoSQL databases language such as MongoDB-supported by MQL, and Couchbase- supported by N1QL, etc.
SQL vs NoSQL database platforms
Nevertheless, NoSQL database industry cannot completely abandon SQL schema to access databases. Many NoSQL vendors are still using a variation of SQL. In MongoDB query language, we will find that it is based on the select-join-project construct, which is the foundation of relational algebra that is used in SQL. Couchbase provides a SQL-like query language – N1QL, which extends the SQL language to allow users to manipulate the hierarchical nature of the document model. Moreover, NoSQL database popularly works in web services, and web applications. Some APIs programming languages are required to manipulate databases on the server side such as Python, PHP, Go, and Ruby help databases communicate with applications at the back end of a program.
Demonstrate application examples of the NoSQL DBMS and its advantage over a conventional Relational DBMS.
In this study, I would like to demonstrate MongoDB which is the most popular NoSQL database system how is simple to use in online applications. MongoDB is acceptable for the developer to be a reliable database management system. Then I will make the comparison between MySQL database and MongoDB NoSQL database to learn more about their use case.
MongoDB is an open-source non-relational database that is variously used to develop a web application. Programmers store the data in BSON document that mostly replicates the concept of object-oriented programming to design the data collection. They do not have to concentrate on the database infrastructure because MongoDB supports unstructured data manipulation without a predefined pattern. Furthermore, its features are flexible to handle data migration in different software products. They would consider what kind of data they will collect, what functionality they will provide to a user, or what result they want to show to the client side. The significate advantage of using MongoDB is to simplify data management. It can help programmer to handle all the highly sophisticated database tasks. In a general use case, a programmer may only need to find out the connecting string to call the database and put it in their application. The rest blind side of database management is controlled by the configuration in MongoDB system.
Firstly, we make the registration on https://www.mongodb.com to apply for online database service.
MongoDB online database service
When you sign in on MongoDB, it will provide a database system on cloud service to test for free forever lifetime accessible as shown in the figure
Build a cluster to create a free database accessed on the cloud system
There are many connection methods that MongoDB provides for the user as shown in the figure. We choose the application that we will work with the database system then it provides the connection string which we will put in the application to access MongoDB database.
Build a cluster to create a free database accessed on a cloud system
-
Considering NoSQL its advantage conventional over relation database
In my opinion, there are two strong points that NoSQL database overcome SQL database systems as this following:
-
User Friendly for those who have no experience in database management.
MySQL is an open-source relational database management as the figure. The programmer has to predefine data tables and relations relying on the concept of normalization, referential integrity, and relational database design. This may need an expert or additional resource to manipulate the data and take a time to build the database system.
MySQL database design schema
In contrast, the programmer uses their familiar programming process (using JavaScript, C#, Python, etc.) to store the data in the document base and categorized them in the collection as shown in the figure. Programmers do not have to design the schema every time to correspond to the new input data even if it is a different data type.
NoSQL database design schema applied in MongoDB
- Flexible for scalability data in the future trade
As described in section 1, SQL database has a weakness in its data structure. MySQL predefines strong data integrity which has a problem with the transaction to a new system and different hardware. We can enhance the database capacity in vertical scaling such as adding more RAM, CPU, or replicated master data in a single machine. Studied on MongoDB I realized that it is outstanding in data scalability. MongoDB has provided big data feature to read and write the data in horizontal scaling. Therefore, programmers can distribute the data across many servers that are faster than SQL databases to update the unstructured data such as images, videos, documents, etc.
As shown in the figure, MySQL offers many ways to replicate a database, but all of them are slightly complex. The system doesn’t have a standard approach to database management. On the other hand, MongoDB offers programmers– a paid tool for administration and replication. The database offers a single simple way to replicate documents. We can share each router and configure servers independently.
MySQL and MongoDB NoSQL database scaling
However, both database types are developed to be compatible with every new technology nowadays since they have a different approach to store the data depending on the data application. Most the big organizations are adopting both SQL and NoSQL. A significant advantage of SQL database is strong data integrity and consistency that make a huge data easy to select from users as used in the Bank company. NoSQL database like MongoDB rapidly updates diverse data type as their flexible features are designed for development such as on the mail application, Netflix, Amazon, etc.
Here is a table overall in this study case to summarize the differences between SQL and NoSQL according to this article https://medium.com/edureka/sql-vs-nosql-db-5d9b69ace6ac
This blog is a part of my database course at Assumption University
Thank you to my advisor Prof. Dr. Suphamit Chittayasothorn.
Reference
https://www.datacamp.com/blog/nosql-databases-what-every-data-scientist-needs-to-know
https://www.talend.com/resources/sql-vs-nosql/
https://academind.com/tutorials/sql-vs-nosql
https://medium.com/nerd-for-tech/sql-vs-nosql-faef10e3852d
https://www.mongodb.com/scale/types-of-nosql-databases
https://www.mongodb.com/nosql-explained/nosql-vs-sql
https://www.mongodb.com/languages/mean-stack-tutorial
https://www.mongodb.com/compare/mongodb-mysql
https://phoenixnap.com/kb/document-database
https://jelvix.com/blog/mongodb-vs-mysql
