Question

The assignment requires reading and understanding the data model used by Cassandra and representing a given ER model with Cassandra “tables”. The decision of which tables to define is driven by the queries that need to be answered. A software company is interested in developing a Video Sharing application. They have designed a conceptual model of the data they need to handle in order to offer users these main functions:

1. The creation of channels where they can upload videos.

2. The uploading of videos.

3. A log of likes and comments made by the users on the videos.

The two main sources needed to do this project are listed below. Read the first source, i.e. the description of how Cassandra tables are built from an ER schema and a list of queries. As you will see, Cassandra defines a table for each query, unless there is already a table organized by the same attribute needed in the query.

1. The page on data modeling from the Cassandra documentation, which can be found here: https://docs.datastax.com/en/cql/3.3/cql/ddl/dataModelingApproach.html

2. The ER model for the Video Sharing problem presented in figure 1

The specific goal of this assignment is to study the ER model and queries presented in figure 1 and to propose the Cassandra tables necessary to satisfy those queries. Cassandra tables are built with some redundancy to satisfy the queries efficiently, but students must find a way of satisfying the queries creating the minimum number of Cassandra tables.

The submission for this assignment consists of:

• A list of the tables needed and for each table: the description of each table requires the name of the table, the list of attributes it holds. Indicate which of the attributes is the partition key and which are clustering columns, if any.

• Build a short document explaining the tables the team would build in Cassandra to satisfy the eight queries, with the minimum number of tables. Explain for each query which table it needs to provide the necessary information.

Creation time creates (0,255) (0, n) Subscription_time USER CHANNEL (0, n) (0, n) subscribes has Upload _time (0, n) VIDEO uploads (0, n) (0, n) likes Review 0, n) (0, n comments Attributes: USER: User_ID, First name, Last _name, Email, Gender, Country CHANNEL: Channel ID, Channel name, Description, Category, Thumbnail photo_url IDE0: Video ID, Video_title, Description, Duration, Video_url, Thumbnail_photo url List of queries . Provide channel name and thumbnail photo url of all channels in a category 2. Provide video title, duration, thumbnail photo url, and upload time of all videos uploaded in a channel. 3. Provide video title, duration, and thumbnail photo url of all videos uploaded during last month in a channel. . Provide first name, last name, and subscription time of all the subscribers of a channel. 5. List all the channels created by a user along with the creation time 6. List video url of all the videos uploaded by a user 7. List all the comments in a video 8. List video id of all the videos liked by a user Figure 1: ER schema for the Video Sharing Database

Answer 1

Tables:

User(User_ID, First_name, Last_name , Email, Gender, Country)

Channel(Channel_ID, Channel_name, Description, Category, Thumbnail_photo_url, Video_ID)

Video(Video_ID, Video_Title, Description, Duration, Video_url, Thumbnail_photo_url)

Creates(User_ID, Channel_ID, Creation_time)

Subscribes(User_ID, Channel_ID, Subscription_time)

Uploads(User_ID, Video_ID, Upload_time)

Comments(User_ID, Video_ID, Review)

Likes(User_ID, Video_ID)

Queries :

1.

Select Category, Channel_name,Thumbnail_photo_url from Channel group by Category;

2.

Select Video_Title, Duration, Video_url, Thumbnail_photo_url, upload_time from Video inner join Uploads on Video.Video_ID = Uploads.Video_ID;

3.

Select Video_Title, Duration, Video_url, Thumbnail_photo_url, upload_time from Video inner join Uploads on Video.Video_ID = Uploads.Video_ID where Month(Upload_time) = Month(GetDate()) -1;

4.

Select First_name, Last_Name, Subscription_time from User inner join Subscribes on User.User_ID = Subscribes.User_ID;

5.

Select Channel_ID, Creation_time from Creates;

6.

Select Video_url from Video inner join uploads on Video.Video_ID = uploads.Video_ID ;

7.

Select Review from Comments;

8.

Select Video_ID from Likes;

Do ask if any doubt. Please upvote.