Notes

-- Create the database
CREATE DATABASE IF NOT EXISTS competitive_programming;

-- Use the database
USE competitive_programming;

-- Create the users table
CREATE TABLE IF NOT EXISTS users (
username VARCHAR(255) PRIMARY KEY, -- Username serves as the unique identifier
password VARCHAR(255) NOT NULL -- Password is required
);

-- Create the modules table
CREATE TABLE IF NOT EXISTS modules (
module_name VARCHAR(255) PRIMARY KEY, -- Module name serves as the unique identifier
price INT NOT NULL -- Price of the module
);

-- Create the purchases table
CREATE TABLE IF NOT EXISTS purchases (
username VARCHAR(255) NOT NULL, -- Username of the user who purchased the module
module_name VARCHAR(255) NOT NULL, -- Name of the purchased module
PRIMARY KEY (username, module_name), -- Prevent duplicate purchases
FOREIGN KEY (username) REFERENCES users(username) ON DELETE CASCADE,
FOREIGN KEY (module_name) REFERENCES modules(module_name) ON DELETE CASCADE
);