Relational and non-relational databases

A comprehensive understanding of these two types of databases, in the context of cloud-based workloads, is vital for candidates preparing for the AWS Certified Developer – Associate (DVA-C02) exam. This article will attempt to clarify the key differences between relational and non-relational databases and their respective roles within an AWS architecture.

Relational Databases

Relational databases (RDBMS) are based on the relational model introduced by E.F. Codd. In an RDBMS, data is structured into tables, similar to spreadsheets, each one representing different entities and relationships. Each row stands for unique records, and each column stands for attributes.

For example:

Customer Table

Order Table

Relational databases use SQL (Structured Query Language) for defining and manipulating the data. For instance, a simple SQL query can be:

SELECT Customer Name
FROM Customer Table
WHERE Customer ID = 1;

This query traverses the ‘Customer Table’ and fetches the ‘Customer Name’ where the ‘Customer ID’ is 1.

In AWS, the relational database service is known as Amazon RDS. It supports six database engines: Amazon Aurora, PostgreSQL, MySQL, MariaDB, Oracle Database, and SQL Server.

Non-Relational Databases

Non-Relational databases, also known as NoSQL databases, emerged to address the scalability and big data performance issues that are less effectively managed by relational databases. They store data in a non-tabular form and are designed to handle unstructured data.

The most common types of non-relational databases include:

• Document databases store data in documents similar to JSON (JavaScript Object Notation) objects. Each document can have a different structure.
• Key-value stores are the simplest NoSQL databases. Every single item in the database is stored as an attribute name, or key, together with its value.
• Wide-column stores store data in tables, rows, and dynamic columns.
• Graph databases are built to handle data whose relations are well represented as a graph and has elements which are interconnected, with an undetermined number of relations between them.

AWS provides several non-relational database services, including Amazon DynamoDB for key-value databases, Amazon DocumentDB for document databases, and Amazon Neptune for graph databases.

Factors to Decide Which Database to Use

When deciding which type of database to use, several factors come into play:

• Schema flexibility: Non-relational databases are more flexible as they allow for varied data structures, while relational databases require a clearly defined schema.
• Scalability: Non-relational databases are highly scalable and provide superior performance for big data applications.
• Data consistency: Relational databases ensure strong consistency thanks to ACID transactions (Atomicity, Consistency, Isolation, Durability), while most NoSQL databases offer eventual consistency.
• Query capability: SQL databases might be a better fit if complex queries will be made frequently.

For the AWS Certified Developer – Associate (DVA-C02) exam, understanding the best use cases, benefits, and limitations of both relational and non-relational databases is crucial. Weaving this understanding into your application’s architecture can make all the difference in passing the exam and becoming an AWS certified developer.

Practice Test

AWS DynamoDB is an example of a relational database. True/False?

• True
• False

Answer: False.

Explanation: AWS DynamoDB is a non-relational database, specifically a key-value storage that supports rich data types and flexible schemas.

Which of the following AWS services is a relational database service?

• A. Redshift
• B. DynamoDB
• C. RDS
• D. ElastiCache

Answer: C. RDS.

Explanation: The Amazon Relational Database Service (RDS) is a managed service that makes it easy to set up, operate, and scale a relational database in the cloud.

Data in a Non-relational database is typically stored in:

• A. Row-wise format
• B. Block format
• C. Column-wise format
• D. Document or wide column format

Answer: D. Document or wide column format.

Explanation: Non-relational databases or NoSQL databases can store data in various formats – like key-value, wide column, graph, or document (a popular choice for storing unstructured data).

MySQL is a non-relational database. True/False?

• True
• False

Answer: False.

Explanation: MySQL is a popular open-source database and it is a relational database.

Non-relational databases are best suited for data which are:

• A. Structured
• B. Semi-structured
• C. Unstructured
• D. Only A & B

Answer: C. Unstructured.

Explanation: Non-relational databases are designed to handle unstructured data which is dynamic in nature.

All NoSQL databases do not require fixed table schemas. True/False?

• True
• False

Answer: True.

Explanation: Many NoSQL databases allow you to store and process data without requiring a defined schema first.

AWS RDS supports which of the following databases?

• A. Oracle
• B. MySQL
• C. PostgreSQL
• D. All of the above

Answer: D. All of the above.

Explanation: AWS RDS supports multiple relational databases including MySQL, Oracle, and PostgreSQL.

Amazon Redshift is a/an:

• A. Relational database service
• B. Non-relational database service
• C. Data warehousing service
• D. All of the above

Answer: C. Data warehousing service.

Explanation: Amazon Redshift is a fully managed petabyte-scale data warehouse service.

The horizontal scaling capability in NoSQL databases is referred to as:

• A. Sharding
• B. Joining
• C. Partitioning
• D. Scaling

Answer: A. Sharding.

Explanation: Sharding is a method used for distributing data across multiple machines and is very common in NoSQL databases.

NoSQL databases are normally used when a large amount of data must be indexed and queried. True/False?

• True
• False

Answer: True.

Explanation: NoSQL databases are built to enable the storage and retrieval of data in a manner that allows for high-performance, high availability, and easy scalability. They are an excellent choice when dealing with large amounts of data that needs to be indexed and queried.

Interview Questions

What characterize relational databases?

Relational databases are characterized by structured data, use SQL for querying data and have a predetermined schema which requires manual updates when information changes.

What characterize non-relational databases?

Non-relational databases, also known as NoSQL, are characterized by flexibility as they store unstructured data and have no predetermined schema, giving them the ability to collect and process a wide variety of data.

Give examples of non-relational databases that are used on AWS?

Some examples of non-relational databases used in AWS include DynamoDB, SimpleDB and DocumentDB.

What are some advantages of non-relational databases over relational databases?

Non-relational databases are more flexible, easier to scale, and better suited to handle complex, unstructured data compared to relational databases.

How is data stored in a relational database?

In a relational database, data is stored in tables that are made up of rows and columns, and these rows and columns are linked, or related, to one another.

What is a use-case scenario for using a relational database in AWS?

A use-case for a relational database in AWS could be an e-commerce application where consistency and transactions are highly important, for instance, the inventory management system.

What is Amazon DynamoDB?

Amazon DynamoDB is a fully managed NoSQL database service provided by AWS. It provides fast and predictable performance with seamless scalability.

What is Amazon RDS?

Amazon RDS (Relational Database Service) is a web service that makes it easier to set up, operate, and scale a relational database in the cloud.

Can you name a few relational databases that are compatible with AWS RDS?

AWS RDS supports several relational database engines, including MySQL, PostgreSQL, MariaDB, Oracle, and SQL Server.

What is the central concept in a relational database?

The central concept in a relational database is the “relation” (a mathematical term for table). Data is stored in tables and the relationship between data is stored in either a single table or the relationship is defined between several tables.

What is AWS DMS?

AWS DMS (Database Migration Service) helps you migrate databases to AWS quickly and securely. The source database remains fully operational during the migration, minimizing downtime.

What do you mean by ACID properties in a relational database?

ACID stands for Atomicity, Consistency, Isolation, Durability. These properties define the key characteristics of a transaction in the realm of database systems ensuring reliability.

What is AWS Athena?

AWS Athena is an interactive query service that makes it easy to analyze data in Amazon S3 using standard SQL.