How relational and non-relational database works?

  Relational Database (e.g. SQL) Non-Relational Database (e.g. DynamoDB)
Performance
  1. Databases are optimized for storage.
  2. Performance generally depends on the disk subsystem.
  3. Developers must know database implementation details.
  4. Developers and database administrators must optimize queries, indexes, and table structures in order to achieve peak performance.
  1. DynamoDB is optimized for computing.
  2. Performance mainly depends on underlying hardware and network latency.
  3. As a managed service, DynamoDB insulates you and your applications from these implementation details.
  4. Developers can focus on designing and building robust, high-performance applications.
Scaling
  1. It is easiest to scale up with faster hardware. It is also possible for database tables to span across multiple hosts in a distributed system, but this requires additional investment.
  2. Relational databases have maximum sizes for the number and size of files, which imposes upper limits on scalability.
  1. DynamoDB is designed to scale out using distributed clusters of hardware. This design allows increased throughput without increased latency.
  2. Customers specify their throughput requirements, and DynamoDB allocates sufficient resources to meet those requirements. There are no upper limits on the number of items per table, nor the total size of that table.
Data Access
  1. SQL (Structured Query Language) is the standard for storing and retrieving data.
  2. Relational databases offer a rich set of tools for simplifying the development of database-driven applications, but all of these tools use SQL.
  1. You can use the AWS Management Console or the AWS CLI to work with DynamoDB and perform ad hoc tasks.
  2. Applications can leverage the AWS software development kits (SDKs) to work with DynamoDB using object-based, document-centric, or low-level interfaces.
Data Model
  1. The relational model requires a well-defined schema, where data is normalized into tables, rows, and columns. In addition, all of the relationships are defined by tables, columns, indexes, and other database elements.
  1. DynamoDB is schemaless. Every table must have a primary key to uniquely identify each data item, but there are no similar constraints on other non-key attributes. DynamoDB can manage structured or semi-structured data, including JSON documents.
Optimal Workloads
  1. Ad hoc queries; data warehousing; OLAP (online analytical processing).
  1. Web-scale applications, including social networks, gaming, media sharing, and IoT (Internet of Things).
Tools for Accessing the Database
  1. Most relational databases provide a command line interface (CLI) so that you can enter ad hoc SQL statements and see the results immediately.
  1. In most cases, you write application code.
  2. You can also use the AWS Management Console or the AWS Command Line Interface (AWS CLI) to send ad hoc requests to DynamoDB and view the results.
Connecting to the Database
  1. An application program establishes and maintains a network connection with the database.
  2. When the application is finished, it terminates the connection.
  1. DynamoDB is a web service, and interactions with it are stateless.
  2. Applications do not need to maintain persistent network connections.
  3. Instead, interaction with DynamoDB occurs using HTTP(S) requests and responses.
Authentication
  1. An application cannot connect to the database until it is authenticated.
  2. The RDBMS can perform the authentication itself, or it can offload this task to the host operating system or a directory service.
  1. Every request to DynamoDB must be accompanied by a cryptographic signature, which authenticates that particular request.
  2. The AWS SDKs provide all of the logic necessary for creating signatures and signing requests. For more information, see Signing AWS API Requests in the AWS General Reference.
Authorization
  1. Applications can only perform actions for which they have been authorized.
  2. Database administrators or application owners can use the SQL 'GRANT' and 'REVOKE' statements to control access to database objects (such as tables), data (such as rows within a table), or the ability to issue certain SQL statements
  1. In DynamoDB, authorization is handled by AWS Identity and Access Management (IAM).
  2. You can write an IAM policy to grant permissions on a DynamoDB resource (such as a table), and then allow IAM users and roles to use that policy. IAM also features fine-grained access control for individual data items in DynamoDB tables. For more information, see Authentication and Access Control for Amazon DynamoDB.
Sending a Request
  1. The application issues a SQL statement for every database operation that it wants to perform.
  2. Upon receipt of the SQL statement, the RDBMS checks its syntax, creates a plan for performing the operation, and then executes the plan.
  1. The application sends HTTP(S) requests to DynamoDB.
  2. The requests contain the name of the DynamoDB operation to perform, along with parameters. DynamoDB executes the request immediately.
Receiving a Response
  1. The RDBMS returns the results from the SQL statement.
  2. If there is an error, the RDBMS returns an error status and message.
  1. DynamoDB returns an HTTP(S) response containing the results of the operation.
  2. If there is an error, DynamoDB returns an HTTP error status and message(s).