Introducing Better Botz

Better Botz believes that robots can help improve the health, prosperity, and wellbeing of human beings across the world. The company seeks to build Better Botz for a better future!

For example, a personal care robot can help disabled or elderly people retrieve objects, carry groceries, navigate their home and community, and complete essential daily tasks. Business-oriented robots can be built for tunneling or mining applications, surveying dangerous areas, completing minor surgery, or traversing great distances without tiring.

Better Botz supports learning and teaching through hands-on interactions, so they sell kits to build your robot using predefined blueprints. Alternatively, you can purchase individual components to customize your robot for various applications. Pretty cool, right?

The Growth Problem

Business is booming, and Better Botz needs a simple application to replace their online ordering platform for accepting orders. Customers can either purchase a robot kit or individual components to build their own robot. The problem is, Better Botz is so focused on designing and building robots that they don’t have time to build the ordering application. Here’s where you come in! 

Better Botz is looking for a savvy software developer (that’s you) to design and build an online ordering application to provide users with a seamless shopping and checkout experience. In this initial post, we’ll review the available data, create a database to store the data, and generate two tables that are crucial to solving this problem.

With each subsequent post, we’ll dig deeper into different data sets and expand our application, integrating different technologies as we go. 

The Data

Initially, we’ll be focusing on two tables: Products and Orders. Products can either be individual parts (arms/legs, torso, head, and so on) or complete robot kits. Orders are just a list of Products that customers want to purchase. 

Before creating the tables, we need somewhere to put them. We need a database that is lightweight, performant, and scalable to expand with our application. The Astra database from DataStax checks all of those boxes, and also has a Free tier that you can use for zero dollars while building your application. 

Goal 1: Create a DataStax Astra database on the Free tier

Use DataStax Astra to create a database on the Free tier:

1. On the database login page, select Register. 
2. Verify your account using the link from the verification email.
3. Follow the prompts to create a database:
   1. Choose the Free Tier as the Compute Size.
   2. Select a region where you want to create the database.
   3. Enter the database details, using betterbotz as the keyspace name.

After your database is created, you’re ready to create some tables!

Goal 2: Create tables in your database

Because your database runs on Apache Cassandra, we’ll be using the Cassandra Query Language (CQL) to create tables. If you’ve worked with relational databases like MySQL and Postgres, CQL is similar to SQL. The main difference is that Apache Cassandra databases are non-relational, so they use different concepts to store and retrieve data. 

1. In your database, select the CQL Console tab to open the CQL SHell (CQLSH).
2. When prompted, enter the database username and password you set when creating the database.
   
   After entering the user credentials, the CQLSH prompt displays:
   
   Connected to caas-cluster at caas-cluster-caas-dc-service:9042.
[cqlsh 6.8.0 | DSE 6.8.0.77 | CQL spec 3.4.5 | DSE protocol v2]
Use HELP for help.
dbuser@cqlsh>
    
3. At the CQLSH prompt, copy and paste the following CREATE TABLE statements.

Note: If you named your keyspace something other than betterbotz when creating your database, change betterbotz in the following USE statement to match your keyspace name, such as USE mykeyspace. This statement switches to your keyspace before running the CREATE TABLE statements.

USE betterbotz;

CREATE TABLE IF NOT EXISTS products (
  id uuid,
  name text PRIMARY KEY,
  description text,
  price decimal,
  created timestamp
);

CREATE TABLE IF NOT EXISTS orders (
  id uuid,
  address text,
  prod_id uuid,
  prod_name text,
  description text,
  price decimal,
  sell_price decimal,
  customer_name text PRIMARY KEY
);

These statements create two tables in your keyspace, which is like a bucket for your tables:

• The first statement creates a products table with several fields that define an individual product.
• The second table creates an orders table that includes things like product ID, product name, sell price, and other elements of an order.

Goal 3: Insert some data in your tables

With your tables in place, you can insert some data.

In the CQLSH prompt for your database, copy and paste the following INSERT statements to insert data into the products table while using your betterbotz keyspace. Remember that if you named your keyspace something other than betterbotz, you’ll need to change the betterbotz keyspace name in the following USE statement.

INSERT INTO products (  id ,  name ,  description,  price,  created ) VALUES (31047029-2175-43ce-9fdd-b3d568b19bb0,'Heavy Lift Arms','Heavy lift arms capable of lifting 1,250 lbs of weight per arm. Sold as a set.',4199.99,'2019-01-10 09:48:31.020+0040') IF NOT EXISTS;

INSERT INTO products (  id ,  name ,  description,  price,  created ) VALUES (31047029-2175-43ce-9fdd-b3d568b19bb1,'Precision Action Arms','Arms for precision activities in manufacturing or repair. Sold as a set.',12199.99,'2019-01-10 09:28:31.020+0040') IF NOT EXISTS;

INSERT INTO products (  id ,  name ,  description,  price,  created ) VALUES (31047029-2175-43ce-9fdd-b3d568b19bb2,'Medium Lift Arms','Medium lift arms capable of lifting 850 lbs of weight per arm. Sold as a set.',3199.99,'2019-01-10 09:23:31.020+0040') IF NOT EXISTS;

INSERT INTO products (  id ,  name ,  description,  price,  created ) VALUES (31047029-2175-43ce-9fdd-b3d568b19bb3,'Drill Arms','Arms for drilling into surface material. Sold as a set. Does not include drill bits.',2199.99,'2019-01-10 09:12:31.020+0040') IF NOT EXISTS;

INSERT INTO products (  id ,  name ,  description,  price,  created ) VALUES (31047029-2175-43ce-9fdd-b3d568b19bb4,'High Process AI CPU','Head processor unit for robot with heavy AI job process capabilities.',2199.99,'2019-01-10 18:48:31.020+0040') IF NOT EXISTS;

INSERT INTO products (  id ,  name ,  description,  price,  created ) VALUES (31047029-2175-43ce-9fdd-b3d568b19bb5,'Basic Task CPU','Head processor unit for robot with basic process tasks.',899.99,'2019-01-10 18:48:31.020+0040') IF NOT EXISTS;

INSERT INTO products (  id ,  name ,  description,  price,  created ) VALUES (31047029-2175-43ce-9fdd-b3d568b19bb6,'High Strength Torso','Robot body with reinforced plate to handle heavy workload and weight during jobs.',2199.99,'2019-01-10 18:48:31.020+0040') IF NOT EXISTS;

INSERT INTO products (  id ,  name ,  description,  price,  created ) VALUES (31047029-2175-43ce-9fdd-b3d568b19bb7,'Medium Strength Torso','Robot body to handle general jobs.',1999.99,'2019-01-10 18:48:31.020+0040') IF NOT EXISTS;

INSERT INTO products (  id ,  name ,  description,  price,  created ) VALUES (31047029-2175-43ce-9fdd-b3d568b19bb8,'Precision Torso','Robot torso built to handle precision jobs with extra stability and accuracy reinforcement.',8199.99,'2019-01-10 18:48:31.020+0040') IF NOT EXISTS;

For each successful INSERT statement, you’ll see a message that the statement succeeded:

 [applied]
-----------
   True

Your products table has some data in it that we can work with, so let’s check it out!

Goal 4: Retrieve data from your database

Let’s view all of the available products in your products table so that we know what the data looks like. Run the following SELECT statement to view the name, description, and price of all products in the products table:

SELECT name, description, price FROM betterbotz.products ;

The results are formatted neatly, and show the name of each product, a description, and the price. This result is a good start, but as our product table grows, we don’t want to return the entire result set every time and have to sift through the results.
 

For example, if we wanted to retrieve only the record for the Heavy Lift Arms product, we can take our previous query and add a WHERE clause to specify the criteria we want to match. We expand our query to say:

“Get the name, description, and price from my products table, WHERE the name matches a specific criteria”:

SELECT name, description, price FROM betterbotz.products WHERE name = 'Heavy Lift Arms';

There it is! We returned only the record that we wanted instead of a complete list of every product in the table. Knowing how to ask for the data that you want to retrieve is crucial, because what good is a database if you can’t easily get what you need?

Our orders table is currently empty, but we’re going to fix that in the next post

 

Recap

In this first post, you learned about Better Botz and the problem you can help solve. To lay the foundation for your online ordering application, you created a database in the cloud, created two tables, inserted data into those tables, and learned how to retrieve data that you want to view. That’s a great start!

In the next post, we’ll create a sample website built with Node.js that also uses Express. We’ll add more data to your existing cloud database and learn about Node.js as we continue to build the Better Botz online ordering application.

 

References

If you want to explore on your own, here are some links to learn more about CQL and CQLSH

• CQL command quick reference
• CQLSH command reference
   

Next Post >