planning/db.sql at main · primal-development/planning · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS `Athlete`;
DROP TABLE IF EXISTS `PlannedActivity`;
DROP TABLE IF EXISTS `Activity`;
DROP TABLE IF EXISTS `Groups`;
DROP TABLE IF EXISTS `Sport`;
DROP TABLE IF EXISTS `athlete_plannedActivity`;
DROP TABLE IF EXISTS `presences`;
DROP TABLE IF EXISTS `group_athlete`;
SET FOREIGN_KEY_CHECKS = 1;

CREATE TABLE `Athlete` (
    `athlete_id` INTEGER NOT NULL AUTO_INCREMENT,
    `athlete_name` VARCHAR(30) NOT NULL,
    `athlete_lastname` VARCHAR(30) NOT NULL,
    `email_address` VARCHAR(30) NOT NULL,
    `pass_hash` VARCHAR(300) NOT NULL,
    `refresh_token` VARCHAR(50),
    PRIMARY KEY (`athlete_id`)
);

CREATE TABLE `PlannedActivity` (
    `activity_id` INTEGER NOT NULL AUTO_INCREMENT,
    `activity_name` VARCHAR(30) NOT NULL,
    `duration` TIME NOT NULL,
    `activity_description` VARCHAR(80),
    `activity_date` DATE,
    `time_of_day` VARCHAR(1),
    `activity_img` BLOB,
    `sport_id` INTEGER NOT NULL,
    `L1` TIME,
    `L2` TIME,
    `L3` TIME,
    `L4` TIME,
    `L5` TIME,
    `core` TIME,
    `arms` TIME,
    `legs` TIME,
    `agility` TIME,
    `technique` TIME,
    PRIMARY KEY (`activity_id`)
);

CREATE TABLE `Activity` (
    `activity_id` INTEGER NOT NULL AUTO_INCREMENT,
    `activity_name` VARCHAR(30) NOT NULL,
    `start_time` DATETIME NOT NULL,
    `duration` TIME NOT NULL,
    `activity_description` VARCHAR(80),
    `distance` DOUBLE,
    `elevation` INTEGER,
    `avg_velocity` VARCHAR(10),
    `athlete_id` INTEGER NOT NULL,
    `sport_id` INTEGER NOT NULL,
    `L1` TIME,
    `L2` TIME,
    `L3` TIME,
    `L4` TIME,
    `L5` TIME,
    `core` TIME,
    `arms` TIME,
    `legs` TEXT,
    `agility` TIME,
    `technique` TIME,
    PRIMARY KEY (`activity_id`)
);

CREATE TABLE `Groups` (
    `group_id` INTEGER NOT NULL AUTO_INCREMENT,
    `group_name` VARCHAR(30) NOT NULL,
    `trainer_id` INTEGER NOT NULL,
    PRIMARY KEY (`group_id`)
);


CREATE TABLE `Sport` (
    `sport_id` INTEGER NOT NULL AUTO_INCREMENT,
    `sport_name` VARCHAR(30) NOT NULL,
    PRIMARY KEY (`sport_id`),
    UNIQUE (`sport_name`)
);

CREATE TABLE `athlete_plannedActivity` (
    `athlete_id` INTEGER NOT NULL,
    `activity_id` INTEGER NOT NULL,
    PRIMARY KEY (`athlete_id`, `activity_id`)
);

CREATE TABLE `presences` (
    `athlete_id` INTEGER NOT NULL,
    `activity_id` INTEGER NOT NULL,
    PRIMARY KEY (`athlete_id`, `activity_id`)
);

CREATE TABLE `group_athlete` (
    `group_id` INTEGER NOT NULL,
    `athlete_id` INTEGER NOT NULL,
    PRIMARY KEY (`group_id`, `athlete_id`)
);

ALTER TABLE `PlannedActivity` ADD FOREIGN KEY (`sport_id`) REFERENCES `Sport`(`sport_id`);
ALTER TABLE `Activity` ADD FOREIGN KEY (`athlete_id`) REFERENCES `Athlete`(`athlete_id`);
ALTER TABLE `Activity` ADD FOREIGN KEY (`sport_id`) REFERENCES `Sport`(`sport_id`);
ALTER TABLE `Groups` ADD FOREIGN KEY (`trainer_id`) REFERENCES `Athlete`(`athlete_id`);
ALTER TABLE `athlete_plannedActivity` ADD FOREIGN KEY (`athlete_id`) REFERENCES `Athlete`(`athlete_id`);
ALTER TABLE `athlete_plannedActivity` ADD FOREIGN KEY (`activity_id`) REFERENCES `PlannedActivity`(`activity_id`);
ALTER TABLE `presences` ADD FOREIGN KEY (`athlete_id`) REFERENCES `Athlete`(`athlete_id`);
ALTER TABLE `presences` ADD FOREIGN KEY (`activity_id`) REFERENCES `PlannedActivity`(`activity_id`);
ALTER TABLE `group_athlete` ADD FOREIGN KEY (`athlete_id`) REFERENCES `Athlete`(`athlete_id`);
ALTER TABLE `group_athlete` ADD FOREIGN KEY (`group_id`) REFERENCES `Groups`(`group_id`);