Changes between Initial Version and Version 1 of DatabaseCreation

Timestamp:

05/26/26 09:17:48 (13 hours ago)

Author:

231550

Comment:

--

DatabaseCreation

@@ +1 @@
+-- Category
+CREATE TABLE Category (
+    category_id BIGSERIAL PRIMARY KEY,
+    name VARCHAR(255),
+    ParentCategory_id BIGINT,
+    FOREIGN KEY (ParentCategory_id) REFERENCES Category(category_id) ON DELETE SET NULL ON UPDATE CASCADE
+);
+
+-- Nutrient
+CREATE TABLE Nutrient (
+    id BIGSERIAL PRIMARY KEY,
+    nutrient VARCHAR(255),
+    CONSTRAINT uq_nutrient_name UNIQUE (nutrient)
+);
+
+-- Food
+CREATE TABLE Food (
+    id BIGSERIAL PRIMARY KEY,
+    name VARCHAR(255),
+    calories_per_100g INT4,
+    category_id BIGINT,
+    CONSTRAINT chk_food_calories CHECK (calories_per_100g >= 0),
+    FOREIGN KEY (category_id) REFERENCES Category(category_id) ON DELETE SET NULL ON UPDATE CASCADE
+);
+
+-- ServingSize
+CREATE TABLE ServingSize (
+    id BIGSERIAL PRIMARY KEY,
+    label VARCHAR(50),
+    amount NUMERIC(6, 2),
+    unit VARCHAR(10),
+    FoodId BIGINT,
+    CONSTRAINT chk_servingsize_amount CHECK (amount > 0),
+    FOREIGN KEY (FoodId) REFERENCES Food(id) ON DELETE CASCADE ON UPDATE CASCADE
+);
+
+-- Food_nutrients
+CREATE TABLE Food_nutrients (
+    id BIGSERIAL PRIMARY KEY,
+    FoodId BIGINT,
+    NutrientId BIGINT,
+    amount NUMERIC(8, 2),
+    unit VARCHAR(10),
+    CONSTRAINT chk_foodnutrients_amount CHECK (amount >= 0),
+    FOREIGN KEY (FoodId) REFERENCES Food(id) ON DELETE CASCADE ON UPDATE CASCADE,
+    FOREIGN KEY (NutrientId) REFERENCES Nutrient(id) ON DELETE RESTRICT ON UPDATE CASCADE
+);
+
+-- Activity
+CREATE TABLE Activity (
+    id BIGSERIAL PRIMARY KEY,
+    name VARCHAR(255),
+    CONSTRAINT uq_activity_name UNIQUE (name)
+);
+
+-- Muscle
+CREATE TABLE Muscle (
+    id BIGSERIAL PRIMARY KEY,
+    name VARCHAR(50),
+    CONSTRAINT uq_muscle_name UNIQUE (name)
+);
+
+-- Exercise
+CREATE TABLE Exercise (
+    id BIGSERIAL PRIMARY KEY,
+    name VARCHAR(100),
+    calories_per_minute NUMERIC(5, 2),
+    activity_id BIGINT,
+    CONSTRAINT chk_exercise_calories CHECK (calories_per_minute >= 0),
+    FOREIGN KEY (activity_id) REFERENCES Activity(id) ON DELETE SET NULL ON UPDATE CASCADE
+);
+
+-- Exercise_muscles
+CREATE TABLE Exercise_muscles (
+    id BIGSERIAL PRIMARY KEY,
+    Exercise_id BIGINT,
+    Muscle_id BIGINT,
+    FOREIGN KEY (Exercise_id) REFERENCES Exercise(id) ON DELETE CASCADE ON UPDATE CASCADE,
+    FOREIGN KEY (Muscle_id) REFERENCES Muscle(id) ON DELETE RESTRICT ON UPDATE CASCADE
+);
+
+-- Roles
+CREATE TABLE Roles (
+    id BIGSERIAL PRIMARY KEY,
+    name VARCHAR(255),
+    CONSTRAINT uq_roles_name UNIQUE (name)
+);
+
+-- User
+CREATE TABLE "User" (
+    id BIGSERIAL PRIMARY KEY,
+    username VARCHAR(255),
+    email VARCHAR(255),
+    password VARCHAR(255),
+    height INT4,
+    weight INT4,
+    gender VARCHAR(20),
+    created_at TIMESTAMP,
+    CONSTRAINT uq_user_email UNIQUE (email),
+    CONSTRAINT chk_user_email CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
+    CONSTRAINT chk_user_gender CHECK (gender IN ('male', 'female'))
+);
+
+-- User_roles
+CREATE TABLE User_roles (
+    id BIGSERIAL PRIMARY KEY,
+    user_id BIGINT,
+    role_id BIGINT,
+    FOREIGN KEY (user_id) REFERENCES "User"(id) ON DELETE CASCADE ON UPDATE CASCADE,
+    FOREIGN KEY (role_id) REFERENCES Roles(id) ON DELETE RESTRICT ON UPDATE CASCADE
+);
+
+-- Achievements
+CREATE TABLE Achievements (
+    id BIGSERIAL PRIMARY KEY,
+    name VARCHAR(255),
+    requirement_type VARCHAR(50),
+    target_value NUMERIC(8, 2),
+    period_type VARCHAR(50),
+    Activity_id BIGINT,
+    CONSTRAINT chk_achievements_target CHECK (target_value > 0),
+    CONSTRAINT chk_achievements_period CHECK (period_type IN ('daily', 'weekly', 'monthly', 'all_time')),
+    CONSTRAINT chk_achievements_reqtype CHECK (requirement_type IN ('count', 'duration', 'calories', 'streak')),
+    FOREIGN KEY (Activity_id) REFERENCES Activity(id) ON DELETE SET NULL ON UPDATE CASCADE
+);
+
+-- User_achievements
+CREATE TABLE User_achievements (
+    id BIGSERIAL PRIMARY KEY,
+    user_id BIGINT,
+    achievement_id BIGINT,
+    dateEarned DATE,
+    FOREIGN KEY (user_id) REFERENCES "User"(id) ON DELETE CASCADE ON UPDATE CASCADE,
+    FOREIGN KEY (achievement_id) REFERENCES Achievements(id) ON DELETE CASCADE ON UPDATE CASCADE
+);
+
+-- Reminder
+CREATE TABLE Reminder (
+    id BIGSERIAL PRIMARY KEY,
+    dateTime TIMESTAMP,
+    UserId BIGINT,
+    type VARCHAR(20),
+    enabled BOOL,
+    CONSTRAINT chk_reminder_type CHECK (type IN ('meal', 'exercise', 'water')),
+    FOREIGN KEY (UserId) REFERENCES "User"(id) ON DELETE CASCADE ON UPDATE CASCADE
+);
+
+-- BodyMeasurements
+CREATE TABLE BodyMeasurements (
+    id BIGSERIAL PRIMARY KEY,
+    User_id BIGINT,
+    weight NUMERIC(5, 2),
+    waist NUMERIC(5, 2),
+    body_fat NUMERIC(5, 2),
+    dateTime TIMESTAMP,
+    CONSTRAINT chk_bm_weight CHECK (weight > 0),
+    CONSTRAINT chk_bm_bodyfat CHECK (body_fat BETWEEN 0 AND 100),
+    CONSTRAINT chk_bm_waist CHECK (waist > 0),
+    FOREIGN KEY (User_id) REFERENCES "User"(id) ON DELETE CASCADE ON UPDATE CASCADE
+);
+
+-- ActivityLogs
+CREATE TABLE ActivityLogs (
+    id BIGSERIAL PRIMARY KEY,
+    dateTime TIMESTAMP,
+    UserId BIGINT,
+    Duration_minutes INT4,
+    Calories_burned NUMERIC(8, 2),
+    ExerciseId BIGINT,
+    CONSTRAINT chk_activitylogs_duration CHECK (Duration_minutes > 0),
+    CONSTRAINT chk_activitylogs_calories CHECK (Calories_burned >= 0),
+    FOREIGN KEY (UserId) REFERENCES "User"(id) ON DELETE CASCADE ON UPDATE CASCADE,
+    FOREIGN KEY (ExerciseId) REFERENCES Exercise(id) ON DELETE RESTRICT ON UPDATE CASCADE
+);
+
+-- FoodLogs
+CREATE TABLE FoodLogs (
+    id BIGSERIAL PRIMARY KEY,
+    quantity NUMERIC(10, 2),
+    unit VARCHAR(10),
+    meal_type VARCHAR(20),
+    dateTime TIMESTAMP,
+    UserId BIGINT,
+    FoodId BIGINT,
+    CONSTRAINT chk_foodlogs_quantity CHECK (quantity > 0),
+    CONSTRAINT chk_foodlogs_mealtype CHECK (meal_type IN ('breakfast', 'lunch', 'dinner', 'snack')),
+    FOREIGN KEY (UserId) REFERENCES "User"(id) ON DELETE CASCADE ON UPDATE CASCADE,
+    FOREIGN KEY (FoodId) REFERENCES Food(id) ON DELETE RESTRICT ON UPDATE CASCADE
+);
+
+-- Streak_type
+CREATE TABLE Streak_type (
+    id BIGSERIAL PRIMARY KEY,
+    name VARCHAR(255),
+    description VARCHAR(255),
+    metric_type VARCHAR(50),
+    CONSTRAINT chk_streaktype_metric CHECK (metric_type IN ('steps', 'calories', 'workouts')),
+    CONSTRAINT uq_streak_type_name UNIQUE (name)
+);
+
+-- Streak
+CREATE TABLE Streak (
+    id BIGSERIAL PRIMARY KEY,
+    User_id BIGINT,
+    Streak_type_id BIGINT,
+    current_count INT4,
+    longest_count INT4,
+    last_updated DATE,
+    CONSTRAINT chk_streak_current CHECK (current_count >= 0),
+    CONSTRAINT chk_streak_longest CHECK (longest_count >= 0),
+    CONSTRAINT chk_streak_counts CHECK (longest_count >= current_count),
+    FOREIGN KEY (User_id) REFERENCES "User"(id) ON DELETE CASCADE ON UPDATE CASCADE,
+    FOREIGN KEY (Streak_type_id) REFERENCES Streak_type(id) ON DELETE RESTRICT ON UPDATE CASCADE
+);
+
+-- Diet
+CREATE TABLE Diet (
+    id BIGSERIAL PRIMARY KEY,
+    name VARCHAR(255),
+    calorie_target INT4,
+    protein_target INT4,
+    carb_target INT4,
+    fat_target INT4,
+    CONSTRAINT chk_diet_calories CHECK (calorie_target > 0),
+    CONSTRAINT chk_diet_protein CHECK (protein_target >= 0),
+    CONSTRAINT chk_diet_carb CHECK (carb_target >= 0),
+    CONSTRAINT chk_diet_fat CHECK (fat_target >= 0)
+);
+
+-- User_Diets
+CREATE TABLE User_Diets (
+    id BIGSERIAL PRIMARY KEY,
+    user_id BIGINT,
+    Diet_id BIGINT,
+    start_date DATE,
+    end_date DATE,
+    CONSTRAINT chk_diet_dates CHECK (end_date >= start_date),
+    FOREIGN KEY (user_id) REFERENCES "User"(id) ON DELETE CASCADE ON UPDATE CASCADE,
+    FOREIGN KEY (Diet_id) REFERENCES Diet(id) ON DELETE RESTRICT ON UPDATE CASCADE
+);
+
+-- Diet_Exercise_Targets
+CREATE TABLE Diet_Exercise_Targets (
+    id BIGSERIAL PRIMARY KEY,
+    frequency_per_week INT4,
+    DietId BIGINT,
+    ExerciseId BIGINT,
+    CONSTRAINT chk_det_frequency CHECK (frequency_per_week BETWEEN 1 AND 7),
+    FOREIGN KEY (DietId) REFERENCES Diet(id) ON DELETE CASCADE ON UPDATE CASCADE,
+    FOREIGN KEY (ExerciseId) REFERENCES Exercise(id) ON DELETE RESTRICT ON UPDATE CASCADE
+);
+
+-- Diet_foods
+CREATE TABLE Diet_foods (
+    id BIGSERIAL PRIMARY KEY,
+    unit VARCHAR(10),
+    amount NUMERIC(10, 2),
+    DietId BIGINT,
+    FoodId BIGINT,
+    CONSTRAINT chk_dietfoods_amount CHECK (amount > 0),
+    FOREIGN KEY (DietId) REFERENCES Diet(id) ON DELETE CASCADE ON UPDATE CASCADE,
+    FOREIGN KEY (FoodId) REFERENCES Food(id) ON DELETE RESTRICT ON UPDATE CASCADE
+);
+
+-- Goal
+CREATE TABLE Goal (
+    id BIGSERIAL PRIMARY KEY,
+    name VARCHAR(255),
+    diet_id BIGINT,
+    FOREIGN KEY (diet_id) REFERENCES Diet(id) ON DELETE RESTRICT ON UPDATE CASCADE
+);
+
+
+-- User_goals
+CREATE TABLE User_goals (
+    id BIGSERIAL PRIMARY KEY,
+    user_id BIGINT,
+    GoalId BIGINT,
+    start_date DATE,
+    end_date DATE,
+    CONSTRAINT chk_usergoals_dates CHECK (end_date >= start_date),
+    FOREIGN KEY (user_id) REFERENCES "User"(id) ON DELETE CASCADE ON UPDATE CASCADE,
+    FOREIGN KEY (GoalId) REFERENCES Goal(id) ON DELETE RESTRICT ON UPDATE CASCADE
+);
+
+ALTER TABLE "User" ALTER COLUMN height SET NOT NULL;
+ALTER TABLE "User" ALTER COLUMN weight SET NOT NULL;
+ALTER TABLE "User" ALTER COLUMN gender SET NOT NULL;
+
+ALTER TABLE Food ALTER COLUMN category_id SET NOT NULL;
+
+ALTER TABLE FoodLogs ALTER COLUMN UserId SET NOT NULL;
+ALTER TABLE FoodLogs ALTER COLUMN FoodId SET NOT NULL;
+
+ALTER TABLE ActivityLogs ALTER COLUMN UserId SET NOT NULL;
+ALTER TABLE ActivityLogs ALTER COLUMN ExerciseId SET NOT NULL;
+ALTER TABLE ActivityLogs ALTER COLUMN Calories_burned SET NOT NULL;
+
+ALTER TABLE BodyMeasurements ALTER COLUMN User_id SET NOT NULL;
+
+ALTER TABLE Reminder ALTER COLUMN UserId SET NOT NULL;
+ALTER TABLE Reminder ALTER COLUMN enabled SET NOT NULL;
+
+ALTER TABLE Streak ALTER COLUMN User_id SET NOT NULL;
+ALTER TABLE Streak ALTER COLUMN Streak_type_id SET NOT NULL;
+
+ALTER TABLE User_achievements ALTER COLUMN user_id SET NOT NULL;
+ALTER TABLE User_achievements ALTER COLUMN achievement_id SET NOT NULL;
+ALTER TABLE User_achievements ALTER COLUMN dateEarned SET NOT NULL;
+
+ALTER TABLE User_roles ALTER COLUMN user_id SET NOT NULL;
+ALTER TABLE User_roles ALTER COLUMN role_id SET NOT NULL;
+
+ALTER TABLE User_goals ALTER COLUMN user_id SET NOT NULL;
+ALTER TABLE User_goals ALTER COLUMN GoalId SET NOT NULL;
+
+ALTER TABLE User_Diets ALTER COLUMN user_id SET NOT NULL;
+ALTER TABLE User_Diets ALTER COLUMN Diet_id SET NOT NULL;
+
+ALTER TABLE Diet_Exercise_Targets ALTER COLUMN DietId SET NOT NULL;
+ALTER TABLE Diet_Exercise_Targets ALTER COLUMN ExerciseId SET NOT NULL;
+ALTER TABLE Diet_Exercise_Targets ALTER COLUMN frequency_per_week SET NOT NULL;
+
+ALTER TABLE Diet_foods ALTER COLUMN DietId SET NOT NULL;
+ALTER TABLE Diet_foods ALTER COLUMN FoodId SET NOT NULL;
+ALTER TABLE Diet_foods ALTER COLUMN unit SET NOT NULL;
+
+ALTER TABLE ServingSize ALTER COLUMN FoodId SET NOT NULL;
+ALTER TABLE ServingSize ALTER COLUMN unit SET NOT NULL;
+
+ALTER TABLE Food_nutrients ALTER COLUMN FoodId SET NOT NULL;
+ALTER TABLE Food_nutrients ALTER COLUMN NutrientId SET NOT NULL;
+
+ALTER TABLE Exercise_muscles ALTER COLUMN Exercise_id SET NOT NULL;
+ALTER TABLE Exercise_muscles ALTER COLUMN Muscle_id SET NOT NULL;
+
+ALTER TABLE Goal ALTER COLUMN diet_id SET NOT NULL;
+
+ALTER TABLE Streak_type ALTER COLUMN description SET NOT NULL;
+
+ALTER TABLE Exercise ALTER COLUMN activity_id SET NOT NULL;