1 00:00:02,070 --> 00:00:04,870 Now, we're going to start with setting up 2 00:00:04,870 --> 00:00:06,960 the database and the tables. 3 00:00:06,960 --> 00:00:08,620 And here I'm back on my Mac 4 00:00:08,620 --> 00:00:11,070 which is why this looks slightly different, 5 00:00:11,070 --> 00:00:15,200 but I am in MySQL workbench here in the end. 6 00:00:15,200 --> 00:00:17,920 And there, we still can go to the schemas tab 7 00:00:17,920 --> 00:00:22,420 to start creating databases and then tables there. 8 00:00:22,420 --> 00:00:25,660 Of course, you might still have the databases and tables 9 00:00:25,660 --> 00:00:29,220 from the previous course section and you can keep them, 10 00:00:29,220 --> 00:00:31,080 they won't interfere here, 11 00:00:31,080 --> 00:00:34,170 but you can also of course delete them if you want to 12 00:00:34,170 --> 00:00:37,180 simply with help off this MySQL workbench 13 00:00:37,180 --> 00:00:40,193 by right clicking on the tables and databases. 14 00:00:41,260 --> 00:00:42,410 Now, once you're ready, 15 00:00:42,410 --> 00:00:45,810 we can go to schemas here and create a new schema 16 00:00:45,810 --> 00:00:48,640 by simply right clicking into this field 17 00:00:48,640 --> 00:00:51,230 or using this button here, 18 00:00:51,230 --> 00:00:53,650 this create a new schema button 19 00:00:53,650 --> 00:00:56,970 and the schema is simply just a database. 20 00:00:56,970 --> 00:00:59,440 And here we wanna choose a name 21 00:00:59,440 --> 00:01:01,640 and I think blog is a quite fitting name 22 00:01:01,640 --> 00:01:05,102 because we are creating a blog database here. 23 00:01:05,984 --> 00:01:10,984 Therefore I'm going to name this blog and apply this, 24 00:01:11,110 --> 00:01:12,220 we then see the command here 25 00:01:12,220 --> 00:01:15,370 that will be executed to create this schema, 26 00:01:15,370 --> 00:01:16,420 this database, 27 00:01:16,420 --> 00:01:19,600 and hence I'll click apply and apply does as well, 28 00:01:19,600 --> 00:01:21,793 now here's my blog database. 29 00:01:22,760 --> 00:01:24,450 Now we have no tables in there yet 30 00:01:24,450 --> 00:01:26,660 and therefore by right clicking on tables 31 00:01:26,660 --> 00:01:28,950 we can create a new table 32 00:01:28,950 --> 00:01:31,420 or directly run the SQL command 33 00:01:31,420 --> 00:01:33,260 for doing so if you know that 34 00:01:33,260 --> 00:01:36,050 but here, I'm going to create a new table, 35 00:01:36,050 --> 00:01:39,950 and simply name that first table authors 36 00:01:39,950 --> 00:01:42,550 because I'll create that table first, 37 00:01:42,550 --> 00:01:44,640 and then we wanna define the columns 38 00:01:44,640 --> 00:01:46,123 that we wanna have there. 39 00:01:47,010 --> 00:01:50,220 And here, I of course will implement the columns 40 00:01:50,220 --> 00:01:51,930 I showed you on the slide, 41 00:01:51,930 --> 00:01:54,330 which means I want to have an ID column 42 00:01:54,330 --> 00:01:56,080 which is of type end, 43 00:01:56,080 --> 00:01:57,630 which is a primary key, 44 00:01:57,630 --> 00:02:00,440 So this field here should be checked, 45 00:02:00,440 --> 00:02:02,004 it should not be null, 46 00:02:02,004 --> 00:02:06,930 and I'll also check this auto increment field here 47 00:02:06,930 --> 00:02:09,600 so that the ideas automatically incremented 48 00:02:09,600 --> 00:02:13,420 and automatically set whenever we add a new entry 49 00:02:13,420 --> 00:02:16,123 and we don't have to manually manage this ID. 50 00:02:17,990 --> 00:02:20,350 Then every author should have a name, 51 00:02:20,350 --> 00:02:22,140 so I'll add a name column 52 00:02:22,140 --> 00:02:23,770 and this should be of type Varchar 53 00:02:23,770 --> 00:02:28,313 I'll just set the maximum length to 255 characters. 54 00:02:30,440 --> 00:02:34,033 And this should also not be null so I'll check this as well. 55 00:02:35,270 --> 00:02:36,540 And then last but not least, 56 00:02:36,540 --> 00:02:40,270 I wanna have an email that also will be a Varchar 57 00:02:40,270 --> 00:02:41,943 of length 255, 58 00:02:43,250 --> 00:02:44,083 like this, 59 00:02:44,083 --> 00:02:45,533 and it should also not be null. 60 00:02:47,050 --> 00:02:49,493 And that is all I want to set here. 61 00:02:51,260 --> 00:02:55,210 So with that, if I click apply, 62 00:02:55,210 --> 00:02:57,960 here's the SQL statement that will be executed 63 00:02:57,960 --> 00:03:02,740 for creating table called authors in the blog database 64 00:03:02,740 --> 00:03:05,970 with these fields and that configuration here 65 00:03:05,970 --> 00:03:09,950 and if I now click apply that's created. 66 00:03:09,950 --> 00:03:13,550 So now we have that first table here that authors table, 67 00:03:13,550 --> 00:03:16,200 now, of course we're going to create a second table 68 00:03:16,200 --> 00:03:18,303 and that's our posts table. 69 00:03:19,750 --> 00:03:22,280 And then here again, we have an ID, 70 00:03:22,280 --> 00:03:24,490 primary key not null 71 00:03:24,490 --> 00:03:26,743 and auto incrementing should be checked. 72 00:03:27,970 --> 00:03:31,280 And then we have all the fields I showed you on the slide, 73 00:03:31,280 --> 00:03:35,610 like for example, a title which is a Varchar of length 255, 74 00:03:35,610 --> 00:03:37,580 again not null, 75 00:03:37,580 --> 00:03:40,693 so it's not optional a title has to be set. 76 00:03:42,020 --> 00:03:45,450 I also want to have add summary field which is also a text, 77 00:03:45,450 --> 00:03:47,470 but a relatively short text, 78 00:03:47,470 --> 00:03:51,840 so I'll also use Varchar 255 here, 79 00:03:51,840 --> 00:03:53,650 and also set this to not null 80 00:03:53,650 --> 00:03:55,723 because it's also not optional. 81 00:03:57,170 --> 00:03:59,510 And then I'll have that body field, 82 00:03:59,510 --> 00:04:02,120 which is the full blog post text, 83 00:04:02,120 --> 00:04:04,960 and therefore here, I'll not use Varchar 84 00:04:04,960 --> 00:04:08,103 instead here, I'll just use texts like this, 85 00:04:09,100 --> 00:04:11,790 and also check not null here, 86 00:04:11,790 --> 00:04:13,850 and to text us that special data type 87 00:04:13,850 --> 00:04:16,103 which also accepts longer texts. 88 00:04:18,040 --> 00:04:20,399 Then as a next step, of course 89 00:04:20,399 --> 00:04:22,203 I also wanna have a date, 90 00:04:23,430 --> 00:04:28,430 and here the value type I'll choose is date time 91 00:04:28,720 --> 00:04:31,330 because I wanna store well the combination 92 00:04:31,330 --> 00:04:33,010 of a date and the time 93 00:04:33,010 --> 00:04:36,640 and here, I'll then go to default expression 94 00:04:36,640 --> 00:04:39,133 and set this to current_timestamp. 95 00:04:41,594 --> 00:04:44,360 Written like this current_timestamp 96 00:04:44,360 --> 00:04:49,306 so that by default this built in command is executed 97 00:04:49,306 --> 00:04:51,740 and the current timestamp is inferred 98 00:04:51,740 --> 00:04:53,270 and stored for the date 99 00:04:53,270 --> 00:04:55,910 so that we don't have to set the date manually, 100 00:04:55,910 --> 00:04:59,080 but it will be set for us whenever we add new data 101 00:04:59,080 --> 00:05:00,763 into that posts table. 102 00:05:02,360 --> 00:05:03,520 And last but not least, 103 00:05:03,520 --> 00:05:05,580 of course, we wanna establish a connection 104 00:05:05,580 --> 00:05:07,370 to the authors table 105 00:05:07,370 --> 00:05:11,370 hence I'll add a last field called author_ID, 106 00:05:11,370 --> 00:05:12,980 following that naming convention 107 00:05:12,980 --> 00:05:15,540 I outlined in the last course section, 108 00:05:15,540 --> 00:05:18,130 and this will be off type INT 109 00:05:18,130 --> 00:05:21,140 so to same type as we have for the primary key 110 00:05:21,140 --> 00:05:22,430 in the authors table 111 00:05:22,430 --> 00:05:24,240 and it should not be null 112 00:05:24,240 --> 00:05:25,530 so I'll check this 113 00:05:25,530 --> 00:05:28,170 because the author should not be optional 114 00:05:28,170 --> 00:05:30,413 every post should have an author. 115 00:05:32,350 --> 00:05:33,200 And with that, 116 00:05:33,200 --> 00:05:36,820 we implemented what we discussed on this slide before 117 00:05:36,820 --> 00:05:39,790 with the value types discussed here, 118 00:05:39,790 --> 00:05:42,940 and hence, we can now click apply here as well, 119 00:05:42,940 --> 00:05:46,320 here's the SQL statement that will be executed 120 00:05:46,320 --> 00:05:48,680 for creating that posts table 121 00:05:48,680 --> 00:05:51,580 with all the fields be discussed and set up, 122 00:05:51,580 --> 00:05:53,380 and if you now click apply here, 123 00:05:53,380 --> 00:05:55,143 that was created as well. 124 00:05:56,560 --> 00:05:57,570 And with that, 125 00:05:57,570 --> 00:06:01,270 we of course completed this second step at the bottom here, 126 00:06:01,270 --> 00:06:04,830 we created the database and the tables in it. 127 00:06:04,830 --> 00:06:07,980 The next step now is to add some initial data 128 00:06:07,980 --> 00:06:09,760 that we might want. 129 00:06:09,760 --> 00:06:13,330 And indeed there is some initial data I wanna set up 130 00:06:13,330 --> 00:06:15,530 because the blog we're going to build, 131 00:06:15,530 --> 00:06:17,830 of course, it's a fairly basic blog 132 00:06:17,830 --> 00:06:21,630 and whilst it will allow us to manage all the blog posts 133 00:06:21,630 --> 00:06:23,840 with all the crud operations, 134 00:06:23,840 --> 00:06:26,360 it will not have any functionality 135 00:06:26,360 --> 00:06:29,440 for adding or managing offers. 136 00:06:29,440 --> 00:06:33,370 That is something you could build or add as a bonus task 137 00:06:33,370 --> 00:06:36,370 but it's not something we will build here. 138 00:06:36,370 --> 00:06:39,270 Instead, it is also fair to assume 139 00:06:39,270 --> 00:06:42,080 that something like a list of offers 140 00:06:42,080 --> 00:06:45,670 might be managed by some global administrator, 141 00:06:45,670 --> 00:06:50,350 maybe directly through a tool like MySQL workbench. 142 00:06:50,350 --> 00:06:52,910 And therefore what I'll do here is 143 00:06:52,910 --> 00:06:57,760 I will add some starting offers through MySQL workbench, 144 00:06:57,760 --> 00:07:00,080 which we can then utilize later 145 00:07:00,080 --> 00:07:02,513 in our node JS driven website. 146 00:07:03,570 --> 00:07:06,860 And hence I'll click this button here 147 00:07:06,860 --> 00:07:09,493 to create a new SQL statement. 148 00:07:10,400 --> 00:07:13,740 And then the SQL statement I wanna execute here 149 00:07:13,740 --> 00:07:16,050 is insert into, 150 00:07:16,050 --> 00:07:18,520 to insert a new item 151 00:07:18,520 --> 00:07:20,960 a new record into a table, 152 00:07:20,960 --> 00:07:24,223 and I wanna insert into the authors table, 153 00:07:25,130 --> 00:07:27,560 now you can write it like this, 154 00:07:27,560 --> 00:07:29,360 if you later do get an error 155 00:07:29,360 --> 00:07:31,770 about the database not being found though, 156 00:07:31,770 --> 00:07:34,040 or no database being selected, 157 00:07:34,040 --> 00:07:35,440 you can also be very clear 158 00:07:35,440 --> 00:07:38,200 about which database holds this table 159 00:07:38,200 --> 00:07:40,990 by writing blog.authors, 160 00:07:40,990 --> 00:07:45,250 so here we say, it's the authors table in the blog database 161 00:07:45,250 --> 00:07:47,263 and we wanna insert something into it. 162 00:07:48,560 --> 00:07:51,760 Now with insert into, we then have to define the columns 163 00:07:51,760 --> 00:07:54,050 into which we wanna insert data, 164 00:07:54,050 --> 00:07:55,190 and here for me, 165 00:07:55,190 --> 00:07:58,943 that of course would be the name and the email column, 166 00:08:00,180 --> 00:08:01,390 because in authors, 167 00:08:01,390 --> 00:08:04,733 we have the name and email columns which we wanna to set. 168 00:08:06,420 --> 00:08:09,330 And then we can set the values in the same line 169 00:08:09,330 --> 00:08:11,940 or in a new line that doesn't matter, 170 00:08:11,940 --> 00:08:15,130 and here for my name, I'll add a string 171 00:08:15,130 --> 00:08:17,380 so I'll use single quotes here 172 00:08:17,380 --> 00:08:21,310 and choose Maximilian Schwarzmuller here, 173 00:08:21,310 --> 00:08:25,020 and then for the email, maybe max@test whoops, 174 00:08:25,020 --> 00:08:26,630 at test.com, 175 00:08:26,630 --> 00:08:29,033 which is of course, just some dummy email. 176 00:08:31,280 --> 00:08:36,280 And with that, if I now execute this query, 177 00:08:36,770 --> 00:08:39,320 this worked and this was inserted, 178 00:08:39,320 --> 00:08:42,950 and now I'll add a second author Manual Lorenz 179 00:08:43,929 --> 00:08:48,110 and simply use manual@test.com 180 00:08:48,110 --> 00:08:51,430 and also execute this statement, 181 00:08:51,430 --> 00:08:54,160 and with that, these authors should have been added 182 00:08:54,160 --> 00:08:58,490 as some initial data in that author's table, 183 00:08:58,490 --> 00:09:01,779 and we can verify this by selecting from authors 184 00:09:01,779 --> 00:09:05,770 or from blog.authors in case you're getting errors, 185 00:09:05,770 --> 00:09:08,610 and here, if I run this indeed in my results 186 00:09:08,610 --> 00:09:12,150 that I see the two authors with their email addresses 187 00:09:12,150 --> 00:09:14,463 and the automatically assigned IDs. 188 00:09:15,870 --> 00:09:19,290 So now we have that initial data here as well. 189 00:09:19,290 --> 00:09:23,160 And with that, we completed that third step as well. 190 00:09:23,160 --> 00:09:26,540 And therefore, now we basically practiced again 191 00:09:26,540 --> 00:09:29,520 what we learned in the last course section already, 192 00:09:29,520 --> 00:09:31,670 now we're going to move on to the fourth 193 00:09:31,670 --> 00:09:34,550 and very important step for this course section, 194 00:09:34,550 --> 00:09:36,200 where we are going to see 195 00:09:36,200 --> 00:09:39,700 how we can now write node JS express code 196 00:09:39,700 --> 00:09:43,610 that will actually then connect to our database 197 00:09:43,610 --> 00:09:47,983 and query that database from inside node JS.