Case Study 1: Product Liking Functionality [Ecommerce]
Scenario - David is the founder and CEO of www.purhaseitnow.com. He currently sells 300,000 goods every day across several categories. On the portal, there are thousands of sellers with millions of products.
David soon realizes that his monthly sales are declining owing to the low quality of items supplied by some of the merchants. He then decided to categorize the products so that the site might propose suitable items to his consumers. He requested that his CTO, John, create the same capabilities.
John has advised him that if they enable consumers to provide feedback on the things they purchased in the form of likes and dislikes, they would be able to promote those products over similar ones.
John and Product Manager have gathered some requirements and decided to develop using Agile methodology.
Requirements:
1. Get User Details by User Id
2. Get Product Details by Product Id
3. Get all products liked by User
4. Get Product liked by Multiple Users
John is aware of RDBMS only and has suggested database schema as follows:
Tables
1. User
a. User Id
b. User Name
c. Address
2. Product
a. Product Id
b. Product Name
c. Product Description
3. User Product Likes
a. User Id (FK user table)
b. Product Id (FK product table)
c. Timestamp
Soon after, huge data got accumulated in the last table, resulting in system imbalance. They tried to apply all optimization techniques but failed to overcome the issue.
After some digging, they realized that last 2 queries were not performing good due to.
1. Tables will be huge due to large catalogue
2. Retrieval products/users will take more time
To solve this, they hired you because you have some experience in NoSQL databases. You must come up with proper database selection and schema design.
Once you have finalized design you have to:
1. Provide information about database type which you are opting RDBMS/NoSQL/GRAPH?
2. Provide information about database why you selected?
3. Provide schema details along with Primary/Partition/Composite/Clustering keys?
Extension to above problem:
4. Get all products liked by a user should also return product names
Get all user names who have liked any products
Case Study 2: DOMAIN: BANK
Problem Statement:
Our consulting firm has been retained by a major bank to help improve the scalability of their current infrastructure. There are lots of transaction logs generated by various systems. Current database MySQL is not able to handle all the logs. The Firm also wants to run some aggregation jobs.
Key issues:
You must revamp existing code and migration of existing data.
Tasks
You have given end points or log files path where data is being produced.
You have different pages on the website which can be search page, promotional page, deal of day page etc. You must use this log and design schema such that it can get daily request counts per day.
1. Number of clicks on deal of the day page with Android device on 11 May 2017
2. Number of clicks on deal of the page with IOS device on 11 May 2017
3. Number of clicks on home page with Chrome browser on 11 May 2017
4. Number of clicks on home page of Firefox browser on 11 May 2017
Case Study 3: Customer Help Desk Application
Problem Statement:
Model a Customer Help Desk application where customer complaints are logged and captured in a Cassandra column family. The Cassandra table HelpDesk shown in the following screenshot captures these details.
The columns CustomerId, TicketId, ActionTime constitute the Primary key. The column CustomerId becomes the Partition key. The records are stored in the descending order of TicketId, ActionTime. This is to make sure that the recent action details are accessible first.
Task:
1. Create a table HelpDesk as per the above requirement
2. Insert data into HelpDesk. For every record inserted, ActionTime should get the current timestamp.
3. Use the CQL command to display all the data in the specified format.
4. Write range query to retrieve data from to specific date and time. For example, between time-period 2017-11-12 19:14:00 and 2017-11-13 19:20:00
Case Study 4: Hotel Booking Application
Problem Statement:
Design a hotel room reservation application data model. Access available_rooms.csv file provided. The available_rooms.csv file contains a month’s worth of inventory for two small hotels with five rooms each.
Tasks:
1. Create a table available_rooms_by_hotel_date as per the requirement with hotel_id as the partition key, while date and room_number are clustering columns.
2. Bulk load to table available_rooms_by_hotel_data FROM available_rooms.csv
3.Display all the records in available_rooms_by_hotel_date for a particular hotel_id (ex: AZ123) and room_number (ex: 101). Remember both hotel_id and room_number are part of the composite primary key.
4. Display all records for a particular hotel between two specific date range in descending order of date.
5. Write an UDF is available which return 1 if a room is available else return 0 Make a call to the UDF to display the results for table available_rooms_by_hotel_date.
6. Create UDF/UDFs to return the total available rooms.
