k47fwWP4

1-- MySQL Workbench Forward Engineering
2
3SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
4SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
5SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
6
7-- -----------------------------------------------------
8-- Schema KeithWebster_MDV229_Database
9-- -----------------------------------------------------
10
11-- -----------------------------------------------------
12-- Schema KeithWebster_MDV229_Database
13-- -----------------------------------------------------
14CREATE SCHEMA IF NOT EXISTS `KeithWebster_MDV229_Database` DEFAULT CHARACTER SET utf8 ;
15USE `KeithWebster_MDV229_Database` ;
16
17-- -----------------------------------------------------
18-- Table `KeithWebster_MDV229_Database`.`time_tracker_users`
19-- -----------------------------------------------------
20CREATE TABLE IF NOT EXISTS `KeithWebster_MDV229_Database`.`time_tracker_users` (
21  `user_id` INT NOT NULL,
22  `user_password` VARCHAR(10) NOT NULL,
23  `user_firstname` VARCHAR(25) NOT NULL,
24  `user_lastname` VARCHAR(25) NOT NULL,
25  PRIMARY KEY (`user_id`))
26ENGINE = InnoDB;
27
28
29-- -----------------------------------------------------
30-- Table `KeithWebster_MDV229_Database`.`tracked_calendar_days`
31-- -----------------------------------------------------
32CREATE TABLE IF NOT EXISTS `KeithWebster_MDV229_Database`.`tracked_calendar_days` (
33  `calendar_day_Id` INT NOT NULL,
34  `calendar_numerical_day` INT NOT NULL,
35  PRIMARY KEY (`calendar_day_Id`))
36ENGINE = InnoDB;
37
38
39-- -----------------------------------------------------
40-- Table `KeithWebster_MDV229_Database`.`tracked_calendar_dates`
41-- -----------------------------------------------------
42CREATE TABLE IF NOT EXISTS `KeithWebster_MDV229_Database`.`tracked_calendar_dates` (
43  `calendar_date_Id` INT NOT NULL,
44  `calendar_date` DATE NOT NULL,
45  PRIMARY KEY (`calendar_date_Id`))
46ENGINE = InnoDB;
47
48
49-- -----------------------------------------------------
50-- Table `KeithWebster_MDV229_Database`.`days_of_week`
51-- -----------------------------------------------------
52CREATE TABLE IF NOT EXISTS `KeithWebster_MDV229_Database`.`days_of_week` (
53  `day_Id` INT NOT NULL,
54  `day_name` VARCHAR(10) NOT NULL,
55  PRIMARY KEY (`day_Id`))
56ENGINE = InnoDB;
57
58
59-- -----------------------------------------------------
60-- Table `KeithWebster_MDV229_Database`.`activity_categories`
61-- -----------------------------------------------------
62CREATE TABLE IF NOT EXISTS `KeithWebster_MDV229_Database`.`activity_categories` (
63  `activity_category_Id` INT NOT NULL,
64  `category_description` VARCHAR(25) NOT NULL,
65  PRIMARY KEY (`activity_category_Id`))
66ENGINE = InnoDB;
67
68
69-- -----------------------------------------------------
70-- Table `KeithWebster_MDV229_Database`.`activity_descriptions`
71-- -----------------------------------------------------
72CREATE TABLE IF NOT EXISTS `KeithWebster_MDV229_Database`.`activity_descriptions` (
73  `activity_description_Id` INT NOT NULL,
74  `activity_description` VARCHAR(25) NOT NULL,
75  PRIMARY KEY (`activity_description_Id`))
76ENGINE = InnoDB;
77
78
79-- -----------------------------------------------------
80-- Table `KeithWebster_MDV229_Database`.`activity_times`
81-- -----------------------------------------------------
82CREATE TABLE IF NOT EXISTS `KeithWebster_MDV229_Database`.`activity_times` (
83  `activity_time_Id` INT NOT NULL,
84  `time_spent_on_activity` DOUBLE NOT NULL,
85  PRIMARY KEY (`activity_time_Id`))
86ENGINE = InnoDB;
87
88
89-- -----------------------------------------------------
90-- Table `KeithWebster_MDV229_Database`.`activity_log`
91-- -----------------------------------------------------
92CREATE TABLE IF NOT EXISTS `KeithWebster_MDV229_Database`.`activity_log` (
93  `Id` INT NOT NULL AUTO_INCREMENT,
94  `user_Id` INT NULL,
95  `calendar_day` INT NULL,
96  `calendar_date` INT NULL,
97  `day_name` INT NULL,
98  `category_description` INT NULL,
99  `activity_description` INT NULL,
100  `time_spent_on_activity` INT NULL,
101  PRIMARY KEY (`Id`),
102  INDEX `activityCategory_idx` (`category_description` ASC),
103  INDEX `activityDescription_idx` (`activity_description` ASC),
104  INDEX `date_idx` (`calendar_date` ASC),
105  INDEX `dayOfWeek_idx` (`day_name` ASC),
106  INDEX `timeSpent_idx` (`time_spent_on_activity` ASC),
107  INDEX `user_idx` (`user_Id` ASC),
108  INDEX `numericalDay_idx` (`calendar_day` ASC),
109  CONSTRAINT `activityCategory`
110    FOREIGN KEY (`category_description`)
111    REFERENCES `KeithWebster_MDV229_Database`.`activity_categories` (`activity_category_Id`)
112    ON DELETE NO ACTION
113    ON UPDATE NO ACTION,
114  CONSTRAINT `activityDescription`
115    FOREIGN KEY (`activity_description`)
116    REFERENCES `KeithWebster_MDV229_Database`.`activity_descriptions` (`activity_description_Id`)
117    ON DELETE NO ACTION
118    ON UPDATE NO ACTION,
119  CONSTRAINT `timeSpent`
120    FOREIGN KEY (`time_spent_on_activity`)
121    REFERENCES `KeithWebster_MDV229_Database`.`activity_times` (`activity_time_Id`)
122    ON DELETE NO ACTION
123    ON UPDATE NO ACTION,
124  CONSTRAINT `dayOfWeek`
125    FOREIGN KEY (`day_name`)
126    REFERENCES `KeithWebster_MDV229_Database`.`days_of_week` (`day_Id`)
127    ON DELETE NO ACTION
128    ON UPDATE NO ACTION,
129  CONSTRAINT `user`
130    FOREIGN KEY (`user_Id`)
131    REFERENCES `KeithWebster_MDV229_Database`.`time_tracker_users` (`user_id`)
132    ON DELETE NO ACTION
133    ON UPDATE NO ACTION,
134  CONSTRAINT `date`
135    FOREIGN KEY (`calendar_date`)
136    REFERENCES `KeithWebster_MDV229_Database`.`tracked_calendar_dates` (`calendar_date_Id`)
137    ON DELETE NO ACTION
138    ON UPDATE NO ACTION,
139  CONSTRAINT `numericalDay`
140    FOREIGN KEY (`calendar_day`)
141    REFERENCES `KeithWebster_MDV229_Database`.`tracked_calendar_days` (`calendar_day_Id`)
142    ON DELETE NO ACTION
143    ON UPDATE NO ACTION)
144ENGINE = InnoDB;
145
146
147SET SQL_MODE=@OLD_SQL_MODE;
148SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
149SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
150
151-- -----------------------------------------------------
152-- Data for table `KeithWebster_MDV229_Database`.`time_tracker_users`
153-- -----------------------------------------------------
154START TRANSACTION;
155USE `KeithWebster_MDV229_Database`;
156INSERT INTO `KeithWebster_MDV229_Database`.`time_tracker_users` (`user_id`, `user_password`, `user_firstname`, `user_lastname`) VALUES (1, 'password', 'studentFirst', 'studentLast');
157INSERT INTO `KeithWebster_MDV229_Database`.`time_tracker_users` (`user_id`, `user_password`, `user_firstname`, `user_lastname`) VALUES (2, 'password', 'admin', 'admin');
158INSERT INTO `KeithWebster_MDV229_Database`.`time_tracker_users` (`user_id`, `user_password`, `user_firstname`, `user_lastname`) VALUES (3, 'password', 'Instructor', 'Instructor');
159
160COMMIT;
161
162
163-- -----------------------------------------------------
164-- Data for table `KeithWebster_MDV229_Database`.`tracked_calendar_days`
165-- -----------------------------------------------------
166START TRANSACTION;
167USE `KeithWebster_MDV229_Database`;
168INSERT INTO `KeithWebster_MDV229_Database`.`tracked_calendar_days` (`calendar_day_Id`, `calendar_numerical_day`) VALUES (1, 1);
169INSERT INTO `KeithWebster_MDV229_Database`.`tracked_calendar_days` (`calendar_day_Id`, `calendar_numerical_day`) VALUES (2, 2);
170INSERT INTO `KeithWebster_MDV229_Database`.`tracked_calendar_days` (`calendar_day_Id`, `calendar_numerical_day`) VALUES (3, 3);
171INSERT INTO `KeithWebster_MDV229_Database`.`tracked_calendar_days` (`calendar_day_Id`, `calendar_numerical_day`) VALUES (4, 4);
172INSERT INTO `KeithWebster_MDV229_Database`.`tracked_calendar_days` (`calendar_day_Id`, `calendar_numerical_day`) VALUES (5, 5);
173INSERT INTO `KeithWebster_MDV229_Database`.`tracked_calendar_days` (`calendar_day_Id`, `calendar_numerical_day`) VALUES (6, 6);
174INSERT INTO `KeithWebster_MDV229_Database`.`tracked_calendar_days` (`calendar_day_Id`, `calendar_numerical_day`) VALUES (7, 7);
175INSERT INTO `KeithWebster_MDV229_Database`.`tracked_calendar_days` (`calendar_day_Id`, `calendar_numerical_day`) VALUES (8, 8);
176INSERT INTO `KeithWebster_MDV229_Database`.`tracked_calendar_days` (`calendar_day_Id`, `calendar_numerical_day`) VALUES (9, 9);
177INSERT INTO `KeithWebster_MDV229_Database`.`tracked_calendar_days` (`calendar_day_Id`, `calendar_numerical_day`) VALUES (10, 10);
178INSERT INTO `KeithWebster_MDV229_Database`.`tracked_calendar_days` (`calendar_day_Id`, `calendar_numerical_day`) VALUES (11, 11);
179INSERT INTO `KeithWebster_MDV229_Database`.`tracked_calendar_days` (`calendar_day_Id`, `calendar_numerical_day`) VALUES (12, 12);
180INSERT INTO `KeithWebster_MDV229_Database`.`tracked_calendar_days` (`calendar_day_Id`, `calendar_numerical_day`) VALUES (13, 13);
181INSERT INTO `KeithWebster_MDV229_Database`.`tracked_calendar_days` (`calendar_day_Id`, `calendar_numerical_day`) VALUES (14, 14);
182INSERT INTO `KeithWebster_MDV229_Database`.`tracked_calendar_days` (`calendar_day_Id`, `calendar_numerical_day`) VALUES (15, 15);
183INSERT INTO `KeithWebster_MDV229_Database`.`tracked_calendar_days` (`calendar_day_Id`, `calendar_numerical_day`) VALUES (16, 16);
184INSERT INTO `KeithWebster_MDV229_Database`.`tracked_calendar_days` (`calendar_day_Id`, `calendar_numerical_day`) VALUES (17, 17);
185INSERT INTO `KeithWebster_MDV229_Database`.`tracked_calendar_days` (`calendar_day_Id`, `calendar_numerical_day`) VALUES (18, 18);
186INSERT INTO `KeithWebster_MDV229_Database`.`tracked_calendar_days` (`calendar_day_Id`, `calendar_numerical_day`) VALUES (19, 19);
187INSERT INTO `KeithWebster_MDV229_Database`.`tracked_calendar_days` (`calendar_day_Id`, `calendar_numerical_day`) VALUES (20, 20);
188INSERT INTO `KeithWebster_MDV229_Database`.`tracked_calendar_days` (`calendar_day_Id`, `calendar_numerical_day`) VALUES (21, 21);
189INSERT INTO `KeithWebster_MDV229_Database`.`tracked_calendar_days` (`calendar_day_Id`, `calendar_numerical_day`) VALUES (22, 22);
190INSERT INTO `KeithWebster_MDV229_Database`.`tracked_calendar_days` (`calendar_day_Id`, `calendar_numerical_day`) VALUES (23, 23);
191INSERT INTO `KeithWebster_MDV229_Database`.`tracked_calendar_days` (`calendar_day_Id`, `calendar_numerical_day`) VALUES (24, 24);
192INSERT INTO `KeithWebster_MDV229_Database`.`tracked_calendar_days` (`calendar_day_Id`, `calendar_numerical_day`) VALUES (25, 25);
193INSERT INTO `KeithWebster_MDV229_Database`.`tracked_calendar_days` (`calendar_day_Id`, `calendar_numerical_day`) VALUES (26, 26);
194
195COMMIT;
196
197
198-- -----------------------------------------------------
199-- Data for table `KeithWebster_MDV229_Database`.`tracked_calendar_dates`
200-- -----------------------------------------------------
201START TRANSACTION;
202USE `KeithWebster_MDV229_Database`;
203INSERT INTO `KeithWebster_MDV229_Database`.`tracked_calendar_dates` (`calendar_date_Id`, `calendar_date`) VALUES (1, '2016-01-10');
204INSERT INTO `KeithWebster_MDV229_Database`.`tracked_calendar_dates` (`calendar_date_Id`, `calendar_date`) VALUES (2, '2016-01-11');
205INSERT INTO `KeithWebster_MDV229_Database`.`tracked_calendar_dates` (`calendar_date_Id`, `calendar_date`) VALUES (3, '2016-01-12');
206INSERT INTO `KeithWebster_MDV229_Database`.`tracked_calendar_dates` (`calendar_date_Id`, `calendar_date`) VALUES (4, '2016-01-13');
207INSERT INTO `KeithWebster_MDV229_Database`.`tracked_calendar_dates` (`calendar_date_Id`, `calendar_date`) VALUES (5, '2016-01-14');
208INSERT INTO `KeithWebster_MDV229_Database`.`tracked_calendar_dates` (`calendar_date_Id`, `calendar_date`) VALUES (6, '2016-01-15');
209INSERT INTO `KeithWebster_MDV229_Database`.`tracked_calendar_dates` (`calendar_date_Id`, `calendar_date`) VALUES (7, '2016-01-16');
210INSERT INTO `KeithWebster_MDV229_Database`.`tracked_calendar_dates` (`calendar_date_Id`, `calendar_date`) VALUES (8, '2016-01-17');
211INSERT INTO `KeithWebster_MDV229_Database`.`tracked_calendar_dates` (`calendar_date_Id`, `calendar_date`) VALUES (9, '2016-01-18');
212INSERT INTO `KeithWebster_MDV229_Database`.`tracked_calendar_dates` (`calendar_date_Id`, `calendar_date`) VALUES (10, '2016-01-19');
213INSERT INTO `KeithWebster_MDV229_Database`.`tracked_calendar_dates` (`calendar_date_Id`, `calendar_date`) VALUES (11, '2016-01-20');
214INSERT INTO `KeithWebster_MDV229_Database`.`tracked_calendar_dates` (`calendar_date_Id`, `calendar_date`) VALUES (12, '2016-01-21');
215INSERT INTO `KeithWebster_MDV229_Database`.`tracked_calendar_dates` (`calendar_date_Id`, `calendar_date`) VALUES (13, '2016-01-22');
216INSERT INTO `KeithWebster_MDV229_Database`.`tracked_calendar_dates` (`calendar_date_Id`, `calendar_date`) VALUES (14, '2016-01-23');
217INSERT INTO `KeithWebster_MDV229_Database`.`tracked_calendar_dates` (`calendar_date_Id`, `calendar_date`) VALUES (15, '2016-01-24');
218INSERT INTO `KeithWebster_MDV229_Database`.`tracked_calendar_dates` (`calendar_date_Id`, `calendar_date`) VALUES (16, '2016-01-25');
219INSERT INTO `KeithWebster_MDV229_Database`.`tracked_calendar_dates` (`calendar_date_Id`, `calendar_date`) VALUES (17, '2016-01-26');
220INSERT INTO `KeithWebster_MDV229_Database`.`tracked_calendar_dates` (`calendar_date_Id`, `calendar_date`) VALUES (18, '2016-01-27');
221INSERT INTO `KeithWebster_MDV229_Database`.`tracked_calendar_dates` (`calendar_date_Id`, `calendar_date`) VALUES (19, '2016-01-28');
222INSERT INTO `KeithWebster_MDV229_Database`.`tracked_calendar_dates` (`calendar_date_Id`, `calendar_date`) VALUES (20, '2016-01-29');
223INSERT INTO `KeithWebster_MDV229_Database`.`tracked_calendar_dates` (`calendar_date_Id`, `calendar_date`) VALUES (21, '2016-01-30');
224INSERT INTO `KeithWebster_MDV229_Database`.`tracked_calendar_dates` (`calendar_date_Id`, `calendar_date`) VALUES (22, '2016-01-31');
225INSERT INTO `KeithWebster_MDV229_Database`.`tracked_calendar_dates` (`calendar_date_Id`, `calendar_date`) VALUES (23, '2016-02-01');
226INSERT INTO `KeithWebster_MDV229_Database`.`tracked_calendar_dates` (`calendar_date_Id`, `calendar_date`) VALUES (24, '2016-02-02');
227INSERT INTO `KeithWebster_MDV229_Database`.`tracked_calendar_dates` (`calendar_date_Id`, `calendar_date`) VALUES (25, '2016-02-03');
228INSERT INTO `KeithWebster_MDV229_Database`.`tracked_calendar_dates` (`calendar_date_Id`, `calendar_date`) VALUES (26, '2016-02-04');
229
230COMMIT;
231
232
233-- -----------------------------------------------------
234-- Data for table `KeithWebster_MDV229_Database`.`days_of_week`
235-- -----------------------------------------------------
236START TRANSACTION;
237USE `KeithWebster_MDV229_Database`;
238INSERT INTO `KeithWebster_MDV229_Database`.`days_of_week` (`day_Id`, `day_name`) VALUES (1, 'Monday');
239INSERT INTO `KeithWebster_MDV229_Database`.`days_of_week` (`day_Id`, `day_name`) VALUES (2, 'Tuesday');
240INSERT INTO `KeithWebster_MDV229_Database`.`days_of_week` (`day_Id`, `day_name`) VALUES (3, 'Wednesday');
241INSERT INTO `KeithWebster_MDV229_Database`.`days_of_week` (`day_Id`, `day_name`) VALUES (4, 'Thursday');
242INSERT INTO `KeithWebster_MDV229_Database`.`days_of_week` (`day_Id`, `day_name`) VALUES (5, 'Friday');
243INSERT INTO `KeithWebster_MDV229_Database`.`days_of_week` (`day_Id`, `day_name`) VALUES (6, 'Saturday');
244INSERT INTO `KeithWebster_MDV229_Database`.`days_of_week` (`day_Id`, `day_name`) VALUES (7, 'Sunday');
245
246COMMIT;
247
248
249-- -----------------------------------------------------
250-- Data for table `KeithWebster_MDV229_Database`.`activity_categories`
251-- -----------------------------------------------------
252START TRANSACTION;
253USE `KeithWebster_MDV229_Database`;
254INSERT INTO `KeithWebster_MDV229_Database`.`activity_categories` (`activity_category_Id`, `category_description`) VALUES (1, 'Working Out');
255INSERT INTO `KeithWebster_MDV229_Database`.`activity_categories` (`activity_category_Id`, `category_description`) VALUES (2, 'Work');
256INSERT INTO `KeithWebster_MDV229_Database`.`activity_categories` (`activity_category_Id`, `category_description`) VALUES (3, 'Class 1');
257INSERT INTO `KeithWebster_MDV229_Database`.`activity_categories` (`activity_category_Id`, `category_description`) VALUES (4, 'Class 2');
258INSERT INTO `KeithWebster_MDV229_Database`.`activity_categories` (`activity_category_Id`, `category_description`) VALUES (5, 'Sleep');
259INSERT INTO `KeithWebster_MDV229_Database`.`activity_categories` (`activity_category_Id`, `category_description`) VALUES (6, 'Relaxation');
260
261COMMIT;
262
263
264-- -----------------------------------------------------
265-- Data for table `KeithWebster_MDV229_Database`.`activity_descriptions`
266-- -----------------------------------------------------
267START TRANSACTION;
268USE `KeithWebster_MDV229_Database`;
269INSERT INTO `KeithWebster_MDV229_Database`.`activity_descriptions` (`activity_description_Id`, `activity_description`) VALUES (1, 'Playing Games');
270INSERT INTO `KeithWebster_MDV229_Database`.`activity_descriptions` (`activity_description_Id`, `activity_description`) VALUES (2, 'Doing Homework');
271INSERT INTO `KeithWebster_MDV229_Database`.`activity_descriptions` (`activity_description_Id`, `activity_description`) VALUES (3, 'Driving to Work');
272INSERT INTO `KeithWebster_MDV229_Database`.`activity_descriptions` (`activity_description_Id`, `activity_description`) VALUES (4, 'Driving from Work');
273
274COMMIT;
275
276
277-- -----------------------------------------------------
278-- Data for table `KeithWebster_MDV229_Database`.`activity_times`
279-- -----------------------------------------------------
280START TRANSACTION;
281USE `KeithWebster_MDV229_Database`;
282INSERT INTO `KeithWebster_MDV229_Database`.`activity_times` (`activity_time_Id`, `time_spent_on_activity`) VALUES (1, 0.25);
283INSERT INTO `KeithWebster_MDV229_Database`.`activity_times` (`activity_time_Id`, `time_spent_on_activity`) VALUES (2, 0.5);
284INSERT INTO `KeithWebster_MDV229_Database`.`activity_times` (`activity_time_Id`, `time_spent_on_activity`) VALUES (3, .75);
285INSERT INTO `KeithWebster_MDV229_Database`.`activity_times` (`activity_time_Id`, `time_spent_on_activity`) VALUES (4, 1);
286INSERT INTO `KeithWebster_MDV229_Database`.`activity_times` (`activity_time_Id`, `time_spent_on_activity`) VALUES (5, 1.25);
287
288COMMIT;
289
290
291-- -----------------------------------------------------
292-- Data for table `KeithWebster_MDV229_Database`.`activity_log`
293-- -----------------------------------------------------
294START TRANSACTION;
295USE `KeithWebster_MDV229_Database`;
296INSERT INTO `KeithWebster_MDV229_Database`.`activity_log` (`Id`, `user_Id`, `calendar_day`, `calendar_date`, `day_name`, `category_description`, `activity_description`, `time_spent_on_activity`) VALUES (1, 1, 1, 1, 1, 1, 1, 1);
297
298COMMIT;