Laboratory 6 - DATABASE CHALLENGE (1)
.docx
keyboard_arrow_up
School
Conestoga College *
*We aren’t endorsed by this school
Course
DATA MODEL
Subject
Information Systems
Date
Feb 20, 2024
Type
docx
Pages
5
Uploaded by AdmiralTeam13438
NoSQL Databases – Data Challenge
This assignment is based on the practice from last class and students are expected to apply the knowledge gained during the class into this lab exercise. You are expected to have a DataStax account set-up and know how to navigate the screens.
Students are expected to work on their own and make the best attempt to troubleshoot any problems faced during the execution. Please follow the instructions below:
Challenge 1 (0.5 Point)
1.
From the CQL Console, demonstrate that this is your own database by displaying:
a.
The host
b.
The version SHOW VERSION
c.
Your userid within the command prompt
d.
Displaying the Cassandra ingress.
2.
Take a screenshot as this is Screenshot 1 required for the assignment! <Screenshot 1>
(This is the initial screenshot that you get when you log into the Cassandra Command Prompt)
Challenge 2 (0.5 Point)
1.
Load the Dataset ‘Restaurant Locations’
2.
Create 2 Keyspaces – One named ‘Favourites’ and the other named ‘New’
3.
From the CQL Console list all the information available on the ‘Restaurant Locations’ database.
4.
From the CQL Console, display all the information that is currently inside the ‘Restaurant Database’ 5.
Take a screenshot from the screen with the final record displayed on the screen. <Screenshot 2>
Challenge 3 (2 Points)
3.
From the CQL Console, create a new table with the following information:
a.
Table name: conestoga_mastery – this should be created in the ‘Favourites’ Keyspace you created in Challenge 2.
b.
Create a table with the following specifications:
i.
Column 1: The column should be named id and should be text
ii.
Column 2: The column should be named client
and should be text
iii.
Column 3: The column should be named date and should be date
iv.
Column 4: The column should be named visits
and should be an integer
v.
Column 5: The column should be named satisfied
and should be text
4.
From the CQL Console, ensure the table was created properly by using the appropriate command as illustrated during the lab class.
<Screenshot 3>
5.
Now it is time to add some data into the table, as follows:
Id
Client
Date
Visits
Satisfied?
10
John Lennon
January 15, 1977
7
Yes
11
Eddie Murphy
August 7, 1988
5
No
12
Tom Cruise
February 8, 2003
1
No
13
Justin Trudeau
October 31, 2019
0
No Answer
14
Marilyn Monroe
March 3, 1966
2
Yes
6.
From the CQL Console, demonstrate that the information was inserted properly into the table as
illustrated during the lab class.
<Screenshot 4>
7.
Perform the following queries and collect a screenshot of each one:
a.
List all the clients that had a total of 0 visits <Screenshot 5>
b.
List all the clients that had less than 3 visits <Screenshot 6>
c.
List all the clients that had exactly 5 visits <Screenshot 7>
Challenge 4 (2 Points)
Practice 3: Online Retail Store Inventory Management (Case Study)
Introduction:
In this case study, we will explore the use of Cassandra for managing inventory in an online retail store. The system needs to handle a large volume of products and efficiently manage stock availability. You will learn how to create a keyspace, design tables to store product and inventory information, insert and update data, perform queries to retrieve inventory status, and manage stock levels. This case study will help you understand how Cassandra can be used for scalable inventory management in real-world scenarios.
Scenario:
You have been tasked with developing an inventory management system for an online retail store that sells various products. The system needs to track product details such as SKU, name, price, and category, as well as maintain stock levels for each product. You will utilize Cassandra to build the necessary data model and implement the required functionality. This includes creating keyspaces and tables, inserting sample product data, querying product information, updating stock levels, and handling stock replenishment.
Let's proceed with the step-by-step instructions for the lab.
Step 1: Start Cassandra
- Start the Cassandra service or launch the Cassandra server on your local machine.
Step 2: Launch the Cassandra Query Language Shell (cqlsh)
- Open a terminal or command prompt and launch the cqlsh tool to interact with Cassandra.
Step 3: Create a Keyspace
- Use the following command to create a keyspace:
CREATE KEYSPACE retail_inventory WITH replication = {'class': 'SimpleStrategy', 'replication_factor': 1};
This creates a keyspace named "retail_inventory" with a replication factor of 1.
Step 4: Use the Keyspace
- Switch to the newly created keyspace using the following command:
USE retail_inventory;
This sets the active keyspace for your subsequent queries.
Step 5: Create Product and Inventory Tables
- Design the necessary tables to store product and inventory information. Use appropriate data types for each column, such as SKU (text), name (text), price (decimal), category (text), quantity (int), and last_updated (timestamp).
Example:
CREATE TABLE products (
sku text PRIMARY KEY,
name text,
price decimal,
category text
);
CREATE TABLE inventory (
sku text PRIMARY KEY,
quantity int,
last_updated timestamp
);
Display the Table
<Screenshot 8>
Step 6: Insert Product Data
- Insert sample product data into the product table using the INSERT command. Include information such
as SKU, name, price, category, and initial stock quantity.
Example:
INSERT INTO products (sku, name, price, category) VALUES ('P001', 'Product A', 19.99, 'Electronics');
INSERT INTO products (sku, name, price, category) VALUES ('P002', 'Product B', 9.99, 'Clothing');
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help