Seed Data - Sidduri2025/RehabEdge GitHub Wiki

Seed Data

Wiki Integration: Functional Requirements List

Functional Requirements List (Iteration 2)

Project Title: RehabEdge – Remote Physical Therapy with Edge AI (Group – 04)

Overview

The RehabEdge system supports doctors and patients through remote therapy, exercise monitoring, and AI-based posture analysis. To safely test these features, a complete set of seed data has been created.

This data simulates real-world scenarios such as user accounts, exercise plans, session logs, and AI reports without using any production information.

Database Schema

From the Functional Requirements List, the key entities identified are:

Entity Purpose
UserCredentials Stores encrypted login credentials for all users
Doctor Stores doctor profiles, specialization, and availability
Patient Stores patient profiles, contact details, and assigned doctor
Exercise Stores exercise catalog and AI model references
Session Records session details and metadata
Notification Stores reminders and alerts for patients
Report Stores generated progress reports
DataStorage Stores system backup metadata

Seed Data File

The SQL file creates all tables and inserts dummy data for testing.
It is committed in the main GitHub repository and verified locally using MySQL.

1. UserCredentials:

Field Name Data Type Description Constraints
UserID Integer(10) Unique identifier for each user Primary Key
Email Varchar(255) User’s email address Not Null, Unique
Password Varchar(255) Encrypted password Not Null
Role Varchar(20) Defines role (Doctor/Patient/Admin) Not Null

Seed Data Insertion:

INSERT INTO UserCredentials VALUES (1,'[email protected]','hash$priyaa1','salt$1','Doctor',TRUE), (2,'[email protected]','hash$arjuna2','salt$2','Doctor',TRUE), (3,'[email protected]','hash$aaravp3','salt$3','Patient',FALSE), (4,'[email protected]','hash$diyam4','salt$4','Patient',FALSE), (5,'[email protected]','hash$admin5','salt$5','Admin',TRUE);

Notes:

  • Passwords are stored in encrypted format (e.g., hashed with a salt).

  • Role field determines system access permissions.

  • Email addresses are unique to prevent duplicate account creation.

2. Doctor

Field Name Data Type Description Constraints
DoctorID Integer(10) Unique identifier for doctor Primary Key
Name Varchar(255) Full name of doctor Not Null
Specialization Varchar(255) Doctor’s area of expertise Not Null
Experience Integer(10) Years of experience
Availability Integer(10) Availability status (e.g., 1=Available, 0=Unavailable)
UserID Integer(10) Links to UserCredentials Foreign Key (UserCredentials.UserID)

Seed data Insertion:

INSERT INTO Doctor VALUES

(101,1,'Dr. Priya Sharma','Orthopedic',12,'Mon/Wed/Fri 10:00-14:00'),

(102,2,'Dr. Arjun Rao','Physiotherapy',9,'Tue/Thu 09:00-12:00; Sat 10:00-13:00');

Notes:

  • UserID establishes a foreign key link between the Doctor and UserCredentials tables for secure login association.

  • Availability specifies each doctor's working days and time slots for scheduling patient sessions.

  • Specialization identifies the medical expertise area, helping assign suitable doctors to patients.

3. Patient

Field Name Data Type Description Constraints
PatientID Integer(10) Unique identifier for patient Primary Key
Name Varchar(255) Full name of patient Not Null
DateOfBirth Integer(10) Patient’s birth date (or stored as Date)
Contact Varchar(10) Patient’s contact number
AssignedDoctorID Integer(10) Doctor assigned to this patient Foreign Key (Doctor.DoctorID)
ReminderPreference Varchar(255) Preferred method for reminders (SMS/Email)
UserID Integer(10) Links to user credentials Foreign Key (UserCredentials.UserID)

Seed Data Insertion:

(201,3,'Aarav Patel','1993-08-17','+1-206-555-0110',101,'Daily'),

(202,4,'Diya Mehta','1997-03-04','+1-425-555-0142',102,'Weekly');

Notes:

  • AssignedDoctorID links each patient to their respective doctor, ensuring proper care coordination and medical supervision.

  • UserID connects patient records to login credentials in the UserCredentials table, maintaining secure access and identity mapping.

  • ReminderPreference defines how patients receive therapy reminders (e.g., SMS or Email), supporting personalized communication.

4. Exercise

Field Name Data Type Description Constraints
ExerciseID Integer(10) Unique identifier for each exercise Primary Key
Name Varchar(255) Exercise name Not Null
Description Varchar(255) Description of the exercise
TargetBodyPart Varchar(255) Body part targeted by the exercise
DifficultyLevel Varchar(255) Difficulty (Beginner, Intermediate, Advanced)
AIModelReference Varchar(255) Reference to AI model used for tracking

Seed Data Insertion:

INSERT INTO Exercise VALUES

(301,'Shoulder External Rotation','TheraBand rotation to improve shoulder mobility','Shoulder','Moderate','pose-v2.1-shoulder'),

(302,'Quad Sets','Isometric quad activation for knee stability','Knee','Easy','pose-v2.1-knee');

Notes:

  • TargetBodyPart specifies which body area the exercise focuses on, helping personalize rehabilitation plans.

  • AIModelReference links each exercise to the corresponding AI model used for motion tracking and posture analysis.

  • DifficultyLevel categorizes exercises by intensity to match the patient's physical condition and progress stage.

5. SessionData

Field Name Data Type Description Constraints
SessionID Integer(10) Unique identifier for session Primary Key
PatientID Integer(10) Linked patient ID Foreign Key (Patient.PatientID)
DoctorID Integer(10) Linked doctor ID Foreign Key (Doctor.DoctorID)
ExerciseID Integer(10) Linked exercise ID Foreign Key (Exercise.ExerciseID)
DateTime DATETIME Date and time of session
Duration Integer(10) Duration in minutes
AccuracyScore Integer(10) AI accuracy score for posture
FeedbackNotes Varchar(255) Doctor’s feedback notes
VideoLink Varchar(255) Link to recorded session

Seed Data Insertion:

INSERT INTO Session VALUES

(501,201,101,301,'2025-10-24 10:15:00',28,86,'Form improving; minor shoulder elevation','s3://rehabedge/videos/201_20251024.mp4'),

(502,202,102,302,'2025-10-25 09:05:00',24,91,'Good quad activation; hold times consistent',NULL);

Notes:

  • PatientID links each notification to a specific patient, ensuring personalized reminders and alerts are delivered correctly.

  • Type identifies the purpose of each message (e.g., reminder for upcoming sessions or alert for missed activities).

  • Status tracks whether the patient has viewed the message, supporting effective communication and follow-up monitoring.

6. NotificationDetails

Field Name Data Type Description Constraints
NotificationID Integer(10) Unique identifier for notification Primary Key
PatientID Integer(10) Linked patient ID Foreign Key (Patient.PatientID)
Message Varchar(255) Notification content Not Null
Type Varchar(255) Notification type (Mobile/Email)
Status Varchar(255) Sent/Pending status
ScheduledTime DATETIME Time notification is scheduled

Seed Data Insertion:

INSERT INTO Notification VALUES

(601,201,'Your rehab session is scheduled today at 10:00 AM.','Mobile','Sent','2025-10-24 09:30:00'),

(602,202,'Missed session yesterday. Please complete today.','Email','Pending','2025-10-26 08:00:00');

Notes:

  • PatientID establishes a link between each notification and the corresponding patient to ensure personalized communication.

  • Type defines the purpose of the notification (e.g., reminder for upcoming sessions or alert for missed therapy).

  • Status indicates whether the notification has been sent, read, or is still pending, helping track message delivery and engagement.

7. Report

Field Name Data Type Description Constraints
ReportID Integer(10) Unique identifier for report Primary Key
PatientID Integer(10) Linked patient Foreign Key (Patient.PatientID)
DoctorID Integer(10) Linked doctor Foreign Key (Doctor.DoctorID)
GeneratedDate DATETIME Date report generated
SummaryText Varchar(255) Summary of progress
AccuracyTrend Varchar(255) Trend in exercise accuracy
AdherenceScore Integer(10) Score for patient adherence

Seed Data Insertion:

INSERT INTO Report VALUES

(701,201,101,'2025-10-26','Steady improvement in shoulder ROM; continue same plan','80,83,86',88,'https://app.rehabedge/reports/701\'),

(702,202,102,'2025-10-26','Knee stability progressing; consider progressing difficulty in 1 week','87,89,91',92,'https://app.rehabedge/reports/702\');

Notes:

  • PatientID and DoctorID create a relationship linking reports to the correct patient and supervising doctor for progress tracking.

  • AccuracyTrend stores performance trends (e.g., increasing or decreasing AI accuracy scores) to analyze improvement over time.

  • AdherenceScore measures how consistently a patient follows their prescribed therapy plan, supporting evaluation of treatment effectiveness.

8. DataStorage

Field Name Data Type Description Constraints
StorageID Integer(10) Unique identifier for backup Primary Key
BackupDate DATETIME Date of system backup
FilePath Varchar(255) Path where backup is stored
EncryptionStatus Varchar(255) Encryption applied (Yes/No)
BackupType Varchar(255) Full or Incremental backup
ResponsibleAdmin Varchar(255) Admin who handled backup

Seed Data Insertion:

INSERT INTO DataStorage VALUES

(801,'2025-10-26 02:00:00','azure://rehabedge/backups/full_20251026.bak','Encrypted','Full','Admin Ops'),

(802,'2025-10-26 08:00:00','azure://rehabedge/backups/inc_20251026_0800.bak','Encrypted','Incremental','Admin Ops');

Notes:

  • FilePath stores the exact location of backup files, allowing easy retrieval and restoration of data when needed.

  • EncryptionStatus ensures each backup is securely stored, protecting sensitive patient and system information.

  • BackupType distinguishes between Full and Incremental backups, optimizing storage space and maintaining reliable system recovery processes.

Loading Instructions (MySQL Only):

Note: Ensure MySQL is installed on your computer before proceeding.

Follow the installation steps below if MySQL is not yet installed.

🧩 MySQL Installation Steps(Operating System Windows11)

  1. Choose the correct installer:

  • Download MySQL Installer for Windows.

  • Select version 8.0.44(Currently latest version).

  • Select Operating Systems Microsoft windows.

  • Download(WINDOWS (X86, 32bit), MSI installer).

  • Click "No thanks, just start my download."

  1. Run the installer and select:

  • Setup type: Developer Default (includes MySQL Server )

  • Click Next → Execute → Next to install dependencies.

  1. During configuration:

  • Keep Port 3306 (default)

  • Set a root password (remember it. you'll need it later)

  • Leave "Windows Service Name" as MySQL80

  • Click Execute → Finish

  1. Verify installation:

  • Open Command Prompt

  • Type:

  • mysql -u root -p

  • Enter your root password.
    If you see the mysql> prompt, MySQL is installed successfully.

Step 1: Open Command Prompt as Administrator

Step 2: Log in to MySQL

mysql -u root -p

  • -u root specifies that you are logging in as the root user.

  • -p tells MySQL to prompt for your password.

  • Enter your MySQL root password and press Enter.

Step 3: Create the Database

CREATE DATABASE rehabedge;
USE rehabedge;
  • This creates a new database named rehabedge and switches into it.

Step 4: Load the seed data

• Download the file seed_data_RehabEdge.sql from the GitHub repository

  • and load it using the command:

mysql -u root -p rehabedge < "C:\Users\S578137\Downloads\seed_data_RehabEdge.sql"

• Enter your root password again when prompted.

• This command will execute all table creation and insert statements from the file automatically.

Step 5: Verify

  • Example:

SHOW TABLES;
SELECT COUNT(*) FROM Doctor;
SELECT COUNT(*) FROM Patient;

Step 6: Exit MySQL


EXIT;
  • Type EXIT; to leave MySQL.

RESULT:

  • The database rehabedge is now created and loaded with the seed data successfully.