1
00:00:02,370 --> 00:00:04,610
Now that we did dive into those

2
00:00:04,610 --> 00:00:07,230
basic Crat operations,

3
00:00:07,230 --> 00:00:08,320
creating, reading,

4
00:00:08,320 --> 00:00:09,960
updating and deleting,

5
00:00:09,960 --> 00:00:12,620
that we need all the time when working with data

6
00:00:12,620 --> 00:00:13,520
and databases.

7
00:00:13,520 --> 00:00:16,900
It's time to move on and work towards that

8
00:00:16,900 --> 00:00:21,170
goal that we defined earlier in this course section.

9
00:00:21,170 --> 00:00:24,770
To dive into SQL based databases

10
00:00:24,770 --> 00:00:27,120
and to practice what we learned now.

11
00:00:27,120 --> 00:00:30,060
We wanna build a database table

12
00:00:30,060 --> 00:00:32,890
or preferably multiple tables

13
00:00:32,890 --> 00:00:35,760
that allow us to store Restaurants data

14
00:00:35,760 --> 00:00:37,543
and Reviews for those restaurants.

15
00:00:38,490 --> 00:00:41,670
Now of course in the previous course lectures,

16
00:00:41,670 --> 00:00:44,600
we already did add a Restaurants table

17
00:00:44,600 --> 00:00:46,700
that holds some restaurants data

18
00:00:46,700 --> 00:00:50,240
but that's not all the data I actually wanna store.

19
00:00:50,240 --> 00:00:54,150
And I also wanna store the data a little bit differently

20
00:00:54,150 --> 00:00:56,750
and we didn't touch the reviews part at all.

21
00:00:56,750 --> 00:00:59,620
So that's also something we'll have to add.

22
00:00:59,620 --> 00:01:02,020
And now the thing here with the layout

23
00:01:02,020 --> 00:01:03,780
you see on this slide,

24
00:01:03,780 --> 00:01:06,769
is that this structure of storing the data

25
00:01:06,769 --> 00:01:08,960
is not really optimal

26
00:01:08,960 --> 00:01:11,150
because in some fields here,

27
00:01:11,150 --> 00:01:12,970
like the Address field,

28
00:01:12,970 --> 00:01:15,570
we have complex data.

29
00:01:15,570 --> 00:01:17,800
We have data that actually consists

30
00:01:17,800 --> 00:01:20,410
of multiple pieces of data.

31
00:01:20,410 --> 00:01:21,350
And data for,

32
00:01:21,350 --> 00:01:25,290
I actually don't just want to work with two tables here,

33
00:01:25,290 --> 00:01:26,940
but with more tables

34
00:01:26,940 --> 00:01:29,790
because when working with SQL

35
00:01:29,790 --> 00:01:30,700
and that is of course,

36
00:01:30,700 --> 00:01:32,920
something we'll dive way deeper into

37
00:01:32,920 --> 00:01:36,340
in the complete SQL course that we also have.

38
00:01:36,340 --> 00:01:38,810
When working with SQL you typically

39
00:01:38,810 --> 00:01:42,641
store your data in a normalized form,

40
00:01:42,641 --> 00:01:46,410
which basically means you don't wanna have any complex,

41
00:01:46,410 --> 00:01:49,980
nested or structured data in one table

42
00:01:49,980 --> 00:01:51,880
but instead you wanna split your data

43
00:01:51,880 --> 00:01:53,804
across multiple tables.

44
00:01:53,804 --> 00:01:57,980
And for example, here in this restaurants reviews example,

45
00:01:57,980 --> 00:02:01,100
we could work with multiple tables

46
00:02:01,100 --> 00:02:04,223
where we actually store our data like this.

47
00:02:05,330 --> 00:02:08,039
We could have a Restaurants table in the middle here

48
00:02:08,039 --> 00:02:11,070
where we have a unique ID and the name

49
00:02:11,070 --> 00:02:13,500
but then we don't store the address

50
00:02:13,500 --> 00:02:15,500
and also not the type here

51
00:02:15,500 --> 00:02:17,420
in that Restaurants table.

52
00:02:17,420 --> 00:02:20,020
But instead we have separate addresses

53
00:02:20,020 --> 00:02:21,930
and types tables,

54
00:02:21,930 --> 00:02:23,707
so that the address data

55
00:02:23,707 --> 00:02:27,350
is split up across multiple columns

56
00:02:27,350 --> 00:02:29,320
in a separate table.

57
00:02:29,320 --> 00:02:32,530
And we just kind of connect a single restaurant

58
00:02:32,530 --> 00:02:34,390
to a specific address

59
00:02:34,390 --> 00:02:38,050
by linking to the unique ID of an address.

60
00:02:38,050 --> 00:02:41,210
And of course you will learn how that linking works

61
00:02:41,210 --> 00:02:44,630
and how you can query related data like that

62
00:02:44,630 --> 00:02:46,710
later in this section here.

63
00:02:47,933 --> 00:02:50,500
And we could also outsource the restaurant type

64
00:02:50,500 --> 00:02:53,530
into a separate table as simply so

65
00:02:53,530 --> 00:02:56,600
that we can't enter any type as

66
00:02:56,600 --> 00:02:58,020
a type for a restaurant.

67
00:02:58,020 --> 00:03:01,860
But that we have a table with predefined restaurant types,

68
00:03:01,860 --> 00:03:04,190
like German, Italian Indian,

69
00:03:04,190 --> 00:03:06,590
and we just link to one of these types

70
00:03:06,590 --> 00:03:08,683
when we create a new restaurant.

71
00:03:09,650 --> 00:03:11,940
And similarly for the reviews,

72
00:03:11,940 --> 00:03:13,840
we could have a standalone table

73
00:03:13,840 --> 00:03:16,510
and we would link a single review

74
00:03:16,510 --> 00:03:18,218
to a specific restaurant.

75
00:03:18,218 --> 00:03:21,963
Again by specifying the ID of that restaurant.

76
00:03:23,110 --> 00:03:25,420
And there would be different ways

77
00:03:25,420 --> 00:03:27,540
of storing this exact data.

78
00:03:27,540 --> 00:03:30,998
You could possibly even split it across more tables

79
00:03:30,998 --> 00:03:33,890
but this is a table design,

80
00:03:33,890 --> 00:03:35,660
a database design

81
00:03:35,660 --> 00:03:39,790
that would be quite common for this exact use case.

82
00:03:39,790 --> 00:03:41,270
And that's why I'm showing it here

83
00:03:41,270 --> 00:03:44,140
because this is a key thing to note

84
00:03:44,140 --> 00:03:47,610
when working with SQL databases.

85
00:03:47,610 --> 00:03:49,760
You work with multiple tables.

86
00:03:49,760 --> 00:03:52,240
You have a lot of involved tables

87
00:03:52,240 --> 00:03:54,840
where every table stores

88
00:03:54,840 --> 00:03:56,800
quite simple data,

89
00:03:56,800 --> 00:03:59,910
and then you'll link those tables together,

90
00:03:59,910 --> 00:04:04,080
typically by relating to the ID of items

91
00:04:04,080 --> 00:04:05,270
in another table.

92
00:04:05,270 --> 00:04:07,963
Like I'm showing it here on this slide.

93
00:04:09,320 --> 00:04:12,140
Now of course, we're going to implement this layout

94
00:04:12,140 --> 00:04:14,670
and this structure over the next lectures.

95
00:04:14,670 --> 00:04:16,410
And you will also learn how you

96
00:04:16,410 --> 00:04:20,339
can then query data across multiple tables.

97
00:04:20,339 --> 00:04:21,730
So how we could, for example,

98
00:04:21,730 --> 00:04:24,660
say that we want to get a specific restaurant

99
00:04:24,660 --> 00:04:27,670
and the address data that belongs to it

100
00:04:27,670 --> 00:04:29,060
but to get started,

101
00:04:29,060 --> 00:04:31,620
here's a little challenge for you.

102
00:04:31,620 --> 00:04:36,620
I want you to add such a new addresses table

103
00:04:37,260 --> 00:04:41,350
and I want you to create it with the commands

104
00:04:41,350 --> 00:04:44,840
or approaches you saw over the previous lectures.

105
00:04:44,840 --> 00:04:47,120
And I want you to add these columns,

106
00:04:47,120 --> 00:04:48,940
which you see on this slide.

107
00:04:48,940 --> 00:04:49,960
unique ID's,

108
00:04:49,960 --> 00:04:50,793
streets,

109
00:04:50,793 --> 00:04:51,626
street number,

110
00:04:51,626 --> 00:04:56,270
to this addresses table with appropriate data types.

111
00:04:56,270 --> 00:04:57,860
Think about the kind of data

112
00:04:57,860 --> 00:04:59,550
that will be stored in the columns

113
00:04:59,550 --> 00:05:02,272
and then pick the appropriate data types

114
00:05:02,272 --> 00:05:05,940
from the types you learned about over the last lectures.

115
00:05:05,940 --> 00:05:08,610
And then once you're done in the next lecture,

116
00:05:08,610 --> 00:05:11,460
we'll build this Addresses table together,

117
00:05:11,460 --> 00:05:14,090
so that you can compare your solution to mine

118
00:05:14,090 --> 00:05:17,140
and we're then also going to adjust

119
00:05:17,140 --> 00:05:19,340
and add all the other tables

120
00:05:19,340 --> 00:05:21,500
and make sure that we can populate

121
00:05:21,500 --> 00:05:23,373
all those tables with data.