Description
Overview
This Project is worth 100 points (out of 1000) toward your final grade. Your assignment submission should be a document saved and submitted as a PDF file via the link found in the assignment section of “Week 15” in Canvas which is the same place where you found this file.
This assignment will give you hands-on practice in working with the Apache Cassandra “NoSQL” database software.
Objectives
• Students will deploy and run the Apache Cassandra NoSQL database engine
• Students will gain exposure to CQL – Cassandra Query Language
• Students will experience deployment of Apache Cassandra in a Docker container
• Students will launch and use the Cassandra CQL Shell
• Students will run CQL queries
Submission
Create a document and save it as a PDF file. Submit your document via the link in Canvas in the Week 15 materials for Homework # 6.
Your submission should contain SCREEN SHOTS from your console to verify that you have completed each step. Please number your screen shots to match each step and problem within each step if applicable.
You may work with a partner on this assignment. Each partner must submit their own submission. Be sure to include your partner’s name on your submission.
Instructions to Get Started:
Ensure you have docker up and running on your systems.
Install Cassandra via docker on your systems:
docker run cassandra
Get the container id of Cassandra from docker
docker ps -a
Database Assignment 6 1
Using the container id, open sh inside the image
docker exec -it <container-id> /bin/sh
Run cqlsh
We are now connected to the Cassandra image inside docker.
Using Real-world dataset to get started with the assignment:
1.
Download the cubnb-dataset.txt
from Canvas.
2.
Once we have the cassandra image running and are connected to the
sh terminal
inside the
image, open the cubnb-dataset.txt in a notepad/textpad and copy all the contents (Ctrl + C)
and paste the contents in the sh terminal (Ctrl + V)
3. Ensure after the completion of all the commands, you get the following output.
Database Assignment 6
2
Note: Incase of an error, please ensure you have spelt the keyspace name and the table names correctly. Keyspace Name: `cubnb`
Table Names: `tenant`, `property`, `booking`
Assignment Submission Instructions:
Go through below basic operations of Cassandra before starting with Sections 1 and 2.
Create operation commands and queries for Section 1 & 2. Please attach the screenshots (of query and output) of both the sections.
Submit the document as PDF file on Canvas.
Note: You may work with a partner on completing this assignment. However, this is an individual assignment; each one must submit your own final deliverable for this assignment.
Basic Operations of Cassandra:
1. Creating a KeySpace: A keyspace in Cassandra is a namespace that defines data replication on nodes.
Syntax:
CREATE KEYSPACE “KeySpace Name”
WITH replication = {‘class’: ‘Strategy name’, ‘replication_factor’ : ‘Number of replicas’};
Example:
Database Assignment 6
3
CREATE KEYSPACE cubnb
WITH replication = {‘class’:’SimpleStrategy’, ‘replication_factor’ : 3};
2. Using a Keyspace : You can use a created KeySpace using the keyword USE
Syntax:
USE <keyspace-name>
Example:
cqlsh> USE cubnb;
3. Creating tables in keyspace :
Syntax:
CREATE (TABLE | COLUMNFAMILY) <tablename>
(‘<column-definition>’ , ‘<column-definition>’) (WITH <option> AND
<option>)
Example:
cqlsh:cubnb>; CREATE TABLE cubnb.emp( emp_id int PRIMARY
KEY,
emp_name text, emp_city
text, emp_sal varint,
emp_phone varint
);
4. Describe a KeySpace: Verify the keyspaces and its tables.
Syntax:
desc <keyspace-name>
Example:
cqlsh:cubnb> desc cubnb;
5. Drop a KeySpace: Delete the keyspaces and its tables.
Syntax:
drop keyspace <keyspace-name>
Example:
cqlsh:cubnb> drop keyspace cubnb;
Section 1 :
Create commands for below operations as mentioned above and attach screenshots in the assignment.
1. Create a keyspace cubnb for the application having class as SimpleStrategy and replication_factor as 1.
Database Assignment 6 4
Keyspace name: cubnb.
2. Use the keyspace cubnb created above.
3. Create a table for tenant if not exists. Table name: tenant .
Columns: tenant_id (int, primary key), mobile (bigint), password (string), tenant_name (string).
4. Create a table for property if not exists. Table name: property.
Columns:
property_id
(int, primary key), property_name (string),
host_id (int, primary key),
location
(string),
type
(string), price (int), number_of_rooms
(int), max_occupancy
(int),
number_of_reviews
(int),
(set<string> type), status
(string).
amenities
5. Create a table for booking if not exists. Table name: booking .
Columns:
booking_id
(int, primary key),
property_id
(int, primary key),
total_occupancy
(int),
no_of_days
start_date
end_date
tenant_id (int, primary key),
status
(string),
(int),
(date),
(date).
Database Assignment 6
5
Section 2 :
Create queries for below questions and attach screenshots of the query and output in the assignment.
Note: WE HAVE ALREADY IMPORTED DATASET IN THE ABOVE INSTRUCTIONS SECTION
1. Get the booking_id, property_id, tenant_id and number of days a property is booked in the month of Feb ‘2022 for tenant-id 80855.
2. Get the count of properties whose reviews are more than 200.
3. List all properties from “Brooklyn” location with type as apartment and have status as available.
4. a) Get all the properties which are handled by host_id 1029.
b) Add new amenity ‘Heater’ to the above results (in a single query).
c) Show the updated results from part b query.
5. a) Add your details in tenant table with tenant_id as 3287
b) Change the password of the tenant_id 3287 from the existing to ‘password@123’ and display the change
6. Remove an amenity ‘GardenView’ for host_id 1012 and property_id 110 and display the results.
7. List the properties which have a ‘BeachView’ in its amenities and whose rent price is more than 1000.
8. Get the number of highest occupancy of properties for 8 rooms.
