Database Design for Collaborative Filtering Recommendation Engine using AdventureWorks$2019$

Objective

The objective of this assignment is to design a database schema for a plugin using the AdventureWorks$2019$ database that provides a collaborative filtering recommendation engine. The engine will use users' behavior and product preferences to predict which products users would like based on their similarity to other users. This will help you understand the principles of database design, plugin development, and integration within an existing framework.

Requirements

$1.$ Database Schema Design:

o Design a database schema that includes the necessary tables to support a collaborative filtering recommendation engine.

o Ensure that the new tables integrate with the existing AdventureWorks$2019$ database without altering its current schema.

o The schema should capture users' behavior and product preferences.

$2.$ Tables to Include:

o UserRatings: Stores user ratings for products.

o UserSimilarity: Stores similarity scores between users.

o UserInteractions: Stores user interactions with products $($e$.$g$.,$ views, clicks$).$

o ProductCategories: Stores the categories of products for better preference tracking.

$3.$ Relationships:

o Establish foreign key relationships with existing tables.

o Ensure referential integrity and $3$rd$-$form normalization.

Deliverables

$1.$ Database Schema:

o ER diagram showing the new tables and their relationships with existing tables.

o SQL scripts for creating the new tables.

o Detailed explanation of the schema design.