Relational Database

What is Relational Database?

A relational database is a collection of data items with pre-defined relationships between them. These items are organized as a set of tables with columns and rows.

Tables are used to hold information about the objects to be represented in the database.

Each column in a table holds a certain kind of data and a field stores the actual value of an attribute.

The rows in the table represent a collection of related values of one object or entity. Each row in a table could be marked with a unique identifier called a primary key, and rows among multiple tables can be made related using foreign keys. This data can be accessed in many different ways without reorganizing the database tables themselves.

Important Aspects of Relational Database

1. Structured Query Language(SQL): SQL is the primary interface used to communicate with Relational Databases. SQL became a standard of the American National Standards Institute (ANSI) in 1986. The standard ANSI SQL is supported by all popular relational database engines, and some of these engines also have extension to ANSI SQL to support functionality which is specific to that engine. SQL is used to add, update or delete rows of data, retrieving subsets of data for transaction processing and analytics applications, and to manage all aspects of the database.

2. Data Integrity: Data integrity is the overall completeness, accuracy and consistency of data. Relational databases use a set of constraints to enforce data integrity in the database. These include primary Keys, Foreign Keys, ‘Not NULL’ constraint, ‘Unique’ constraint, ‘Default’ constraint and ‘Check’ constraints. These integrity constraints help enforce business rules on data in the tables to ensure the accuracy and reliability of the data. In addition to these, most relation databases also allow custom code to be embedded in triggers that execute based on an action on the database.

3. Transactions: A database transaction is one or more SQL statements that are executed as a sequence of operations that form a single logical unit of work. Transactions provide an "all-or-nothing" proposition, meaning that the entire transaction must complete as a single unit and be written to the database or none of the individual components of the transaction should go through. In the relation database terminology, a transaction results in a COMMIT or a ROLLBACK. Each transaction is treated in a coherent and reliable way independent of other transactions.

4. ACID Compliance: All database transactions must be ACID compliant or be Atomic, Consistent, Isolated and Durable to ensure data integrity.

   1. Atomicity requires that either transaction as a whole be successfully executed or if a part of the transaction fails, then the entire transaction be invalidated.

   2. Consistency mandates the data written to the database as part of the transaction must adhere to all defined rules, and restrictions including constraints, cascades, and triggers.

   3. Isolation is critical to achieving concurrency control and makes sure each transaction is independent unto itself.

   4. Durability requires that all of the changes made to the database be permanent once a transaction is successfully completed.

Amazon Relational Database Service(Amazon RDS)

What is Amazon RDS?

Amazon Relational Database Service(Amazon RDS) is a collection of managed services that makes it simple to set up, operate, and scale databases in the cloud. It offers scalable and cost-effective capacity while automating time-consuming administration tasks like hardware provisioning, database setup, patching, and backups. It frees you up to focus on your applications, allowing you to provide them with the high availability, security, and compatibility they require.

Amazon RDS supports an array of database engines to store and organize data.

Amazon RDS itself is not a database; It is a service used to manage relational databases.

How does Amazon RDS work?

Databases store large amounts of data that applications can draw upon to help them perform various tasks. A relational database uses tables to store data and is called relational because it organizes data points with defined relationships.

Administrators control Amazon RDS with the AWS Management Console, Amazon RDS API calls, or the AWS command-line interface. They use these interfaces to deploy database instances to which users can apply specific settings.

Amazon provides several instance types with different resources, such as CPU, memory, storage options, and networking capability. Each type comes in a variety of sizes to suit the needs of different workloads.

RDS users can use AWS Identity and Access Management to define and set permissions to access RDS databases.

Benefits of using Amazon RDS

Benefits of using Amazon RDS includes: -

1. Reduced Administration Burden: Using RDS, you can easily deploy the database from project conception to production. There is no need to install any database software and provide the infrastructure. AWS automatically installs the latest software patches to the RDS instance which you have launched.

2. Cost-effective: You just pay for what you use, and nothing more. No upfront payment is needed, just the monthly usage payment.

3. Security: Using AWS Key Management Service (KMS), you can create encryption keys for maintaining security and authorized access for your database.

4. High Availability and Durability: The automated recovery feature of RDS enables point-in-time recovery for your database instance. Multi-AZs provide high availability and durability across the globe.

5. Replication: RDS uses the Replication feature to create read replicas. These are read-only copies of database instances that applications use without altering the original production database. Administrators can also enable automatic failover across multiple availability zones through RDS Multi-AZ deployment and with synchronous data replication.

6. Scalability: It just takes a few minutes to scale your infrastructure up or down, and you can scale up to a maximum of 32 vCPUs and 244 GiB.

7. Free Tier: AWS gives you a free tier usage of Amazon RDS for 750 hours/month for 12 months.

8. Storage: RDS provides three types of storage:

   • General-purpose solid-state drive (SSD): Amazon recommends this storage as the default choice.

   • Provisioned input-output operations per second (IOPS): SSD storage for I/O-intensive workloads.

   • Magnetic: A lower cost option.

Amazon RDS Database Engines

Amazon RDS offers 6 different Database Engines: -

1. Amazon Aurora: Amazon Aurora is a MySQL and PostgreSQL-compatible relational database engine that combines the speed and availability of high-end commercial databases with the simplicity and cost-effectiveness of open source databases. Amazon Aurora provides up to five times better performance than MySQL with the security, availability, and reliability of a commercial database at one-tenth the cost.
   Refer this link to know about it more: Amazon Aurora

2. Oracle: Amazon RDS allows you to deploy multiple editions of Oracle Database in minutes with cost-efficient and re-sizable hardware capacity. You can bring existing Oracle licenses or pay for license usage by the hour. RDS frees you up to focus on application development by managing complex database administration tasks including provisioning, backups, patching, monitoring, and hardware scaling.
   Refer this link to know about it more: Oracle

3. Microsoft SQL Server: Amazon RDS for SQL Server makes it easy to set up, operate, and scale SQL Server in the cloud. You can deploy multiple editions of SQL Server including Express, Web, Standard and Enterprise. Since Amazon RDS for SQL Server provides you direct access to the native capabilities of the SQL Server, your applications and tools should work without any changes.

   Refer this link to know about it more: Microsoft SQL Server

4. MySQL: MySQL is an open-source relational database management system (RDBMS) used by a very large number of web-based applications. Amazon RDS for MySQL gives you access to the capabilities of a familiar MySQL database engine. This means that the code, applications, and tools you already use today with your existing databases can be used with Amazon RDS without any changes.
   Refer this link to know about it more: MySQL

5. PostgreSQL: PostgreSQL is a powerful, enterprise class open source object-relational database system with an emphasis on extensibility and standards-compliance. PostgreSQL boasts many sophisticated features and runs stored procedures in more than a dozen programming languages, including Java, Perl, Python, Ruby, Tcl, C/C++, and its own PL/pgSQL, which is similar to Oracle's PL/SQL.
   Refer this link to know about it more: PostgreSQL

6. MariaDB: MariaDB is a MySQL-compatible database engine which is a fork of MySQL, and is being developed by the original developers of MySQL. Amazon RDS makes it easy to set up, operate, and scale MariaDB deployments in the cloud. With Amazon RDS, you can deploy scalable MariaDB databases in minutes with cost-efficient and resizable hardware capacity.
   Refer this link to know about it more: MariaDB

Task: Create a Free tier RDS instance of MySQL

1. Create a Free tier RDS instance of MySQL

2. Create an EC2 instance

3. Create an IAM role with RDS access

4. Assign the role to EC2 so that your EC2 Instance can connect with RDS

5. Once the RDS instance is up and running, get the credentials and connect your EC2 instance using a MySQL client.

Let's start with the steps👇

Step 1: Login to AWS Console and on search bar type "RDS".

Step 2: Click on "RDS". You will see the below dashboard.

Step 3: Click on "Create database". You will see the below page.

Step 4: In Choose a database creation method, choose the "Standard create" and in Engine options, select "MySQL".

Step 5: In Templates, Select "Free Tier".

Step 6: Under Settings, provide the following details:

1. DB instance identifier: mydatabase-1

2. Credentials Settings: -

   Master username: admin

   And provide the password of your choice according to the constraints mentioned.

   

Step 7: Configure other settings like storage, backups, VPC, and security groups according to your requirements.

Step 8: Review the configuration and click “Create Database”.
Note: RDS creation takes a few minutes.

Step 9: Now, Let's Create an EC2 instance named RDS.

Step 10: Configuring the security group to allow inbound traffic on the MySQL port (default is 3306).

Step 11: Create an IAM role with RDS access. Assign the role to EC2 so that your EC2 Instance can connect with RDS.

Step 12: Go to IAM Dashboard and Click on Roles.

Step 13: Click on Create Role.

Step 14: In Select trusted entity, Select AWS Service and Select the EC2 service .

Step 15: Click on Next.

Step 16: In permission policies, attach the permission AmazonRDSFullAccess.

Step 17: Click on Next.

Step 18: Name the role as “RDS-Data”.

Step 19: Click on "Create Role".

Step 20: Select your Instance > Actions > Security > Modify IAM Roles

Step 21: Select the role we have created for RDS.

Step 22: Click on "Update IAM role".

Step 23: Go to RDS Dashboard > Select the Database you created > Copy the endpoint, port, and master username.

Step 24: Click on "Continue".

Step 25: Review and then click on "Set up".

Step 26: And this can be verified under the same tab in "connected compute resource".

Step 27: Now, Let’s connect to the EC2 instance using SSH.

Step 28: And then install the MySQL client in the instance using below commands.

sudo apt-get update
sudo apt-get install mysql-client
mysql --version

Step 29: To connect to the RDS instance using the MySQL client and the endpoint address, username and password, use the below command.

mysql -h <RDS_ENDPOINT> -P <RDS_PORT> -u <MASTER_USERNAME> -p

#The below details we copied when we created the RDS instance:
#<RDS_ENDPOINT> with the endpoint of your RDS instance
#<RDS_PORT> with the port number: 3306
#<MASTER_USERNAME> with the master username: admin
# '-h' is used to specify the endpoint of MySQL server to which we want to connect (basically the host)

Step 30: Now you can verify the CRUD operations confirming the MYSQL is working properly.

And Yay!!! We have created a Free tier RDS instance of MySQL, an EC2 instance, assigned an IAM role with RDS access to the EC2 instance, and connected to the RDS instance from the EC2 instance using a MySQL client.🎉😎🎇

Conclusion

In Conclusion, Amazon Web Services(AWS) offers a robust Relational Database Service(RDS) that simplifies database management, providing a scalable and highly available solution for various database engines. RDS supports popular database engines such as MySQL, PostgreSQL, Oracle, Microsoft SQL Server, and Amazon Aurora.

Key advantages of AWS RDS include: -

1. Managed Service: RDS automates routine database tasks such as backups, patch management, and hardware provisioning, allowing users to focus on application development rather than database administration.

2. Compatibility: With support for various database engines, RDS allows users to choose the engine that best fits their application requirements. This flexibility facilitates easy migration of existing applications to the AWS cloud.

3. Monitoring and Management Tools: AWS RDS offers monitoring and logging tools, including Amazon CloudWatch, to track database performance and troubleshoot issues efficiently. The AWS Management Console provides a user-friendly interface for database configuration and management.

In this blog, We have created a Free tier RDS instance of MySQL, an EC2 instance, assigned an IAM role with RDS access to the EC2 instance, and connected to the RDS instance from the EC2 instance using a MySQL client.

Hope you find it helpful🤞 So I encourage you to try this on your own and let me know in the comment section👇 about your learning experience.✨

👆The information presented above is based on my interpretation. Suggestions are always welcome.😊

~Smriti Sharma✌