WEBVTT

1
00:00:01.730 --> 00:00:06.380
so let's go through some common database
terminology that should be useful for

2
00:00:06.380 --> 00:00:09.290
you when we're working with the
databases in this section of the course

3
00:00:09.290 --> 00:00:14.240
and the idea here is that it will
make it a lot easier to understand the

4
00:00:14.240 --> 00:00:17.420
app when you understand what the
fundamentals are and what all the

5
00:00:17.420 --> 00:00:23.090
actual various terms mean now the very basic level the databases the

6
00:00:23.090 --> 00:00:29.030
container for all the data that you store
or that resides inside now when you

7
00:00:29.030 --> 00:00:33.780
use the term database you're referring
to the entire data as well as the

8
00:00:33.780 --> 00:00:39.350
structure its actually stored in
and in addition any queries and views on

9
00:00:39.350 --> 00:00:42.560
that data now in sql lite the

10
00:00:42.560 --> 00:00:47.860
the entire database contents are stored
in one single file but that isn't true

11
00:00:47.860 --> 00:00:51.860
of most large database systems now the
database dictionary provides a

12
00:00:51.860 --> 00:00:56.990
comprehensive list of the structures and
types of data that are used in recording

13
00:00:56.990 --> 00:01:02.730
the data so basically describes all the
tables and fields within the database now

14
00:01:02.730 --> 00:01:07.200
more on the specifics later but in
sql lite there is a table in each

15
00:01:07.200 --> 00:01:12.900
database called SQL_lite_master that has this information

16
00:01:12.900 --> 00:01:18.120
in it you can query that table but there
are commands that do it for you so you

17
00:01:18.120 --> 00:01:21.930
don't have to understand the structure
of the master table but it's there for

18
00:01:21.930 --> 00:01:25.200
you anyway and as we go through the
app you'll see how these commands

19
00:01:25.200 --> 00:01:28.200
actually work

20
00:01:28.770 --> 00:01:34.620
now a table is a collection of related
data hold in the database so i think of

21
00:01:34.620 --> 00:01:38.190
a contact database for example that
stores the name and the address and the

22
00:01:38.190 --> 00:01:42.780
phone number or perhaps your customers
or what about an invoice table that

23
00:01:42.780 --> 00:01:47.190
records the invoice number in details of
the invoice so in this slide there are

24
00:01:47.190 --> 00:01:52.170
two tables contacts and invoices that are
used to store information about contacts

25
00:01:52.170 --> 00:01:56.900
and invoice details now its SQL lite
databases such a sql lite

26
00:01:56.900 --> 00:01:59.360
or another
database like Microsoft sql server

27
00:01:59.900 --> 00:02:04.470
there are ideal for storing structured
data that can be organized neatly into

28
00:02:04.470 --> 00:02:09.060
rows and columns like you can see in
these examples now with all the interest

29
00:02:09.060 --> 00:02:14.440
in Big Data their databases such as no
sql or hadu that can cope with data

30
00:02:14.440 --> 00:02:17.260
that doesn't have such an obvious
structure but we're going to be

31
00:02:17.260 --> 00:02:20.260
restricting our use of database to
structured data

32
00:02:22.250 --> 00:02:28.070
so field is the basic unit of data in
the table so a field in a database can be

33
00:02:28.070 --> 00:02:31.630
thought of probably in a similar way to
what a variable is and

34
00:02:31.630 --> 00:02:35.590
you've seen those obviously in different
apps and just like a class variable a

35
00:02:35.590 --> 00:02:40.960
database field has a name and a type
now the type restricts what kind of

36
00:02:40.960 --> 00:02:45.640
data can be stored in the field for
example it could be a string or it could

37
00:02:45.640 --> 00:02:50.950
accept numbers so many databases also
allowed date fields large text fields

38
00:02:50.950 --> 00:02:55.240
and also fields where you can store
things like photographs or audio and

39
00:02:55.240 --> 00:02:59.800
these field types can
often are often called blobs which is

40
00:02:59.800 --> 00:03:04.510
supposed to stand for binary large
object it's a great name and I can't

41
00:03:04.510 --> 00:03:08.110
help thinking that the original acronym
was something like LBO for large binary

42
00:03:08.110 --> 00:03:11.110
object and they came up with a cooler name afterwards

43
00:03:12.250 --> 00:03:17.640
now fields are often referred to as
columns in databases I know this can be

44
00:03:17.640 --> 00:03:21.280
technically confusing sometimes because
if you come from an Excel background

45
00:03:21.280 --> 00:03:24.220
you'll find that the definition is
probably a bit different to what you

46
00:03:24.220 --> 00:03:29.110
think of as a column in the spreadsheet
the term column refers to an entire set

47
00:03:29.110 --> 00:03:34.390
of data extending across many rows but
in a relational database column

48
00:03:34.390 --> 00:03:36.850
generally refers to a single entry

49
00:03:36.850 --> 00:03:40.270
although when talking about the
structure of a table rather than the

50
00:03:40.270 --> 00:03:44.560
actual data then you could talk about a
column to hold the invoice number which

51
00:03:44.560 --> 00:03:49.270
is probably closer to the spreadsheet
use of the term now when referring to

52
00:03:49.270 --> 00:03:53.580
the data in the database though we're
talking about an individual item like a

53
00:03:53.580 --> 00:03:58.420
base unit of data the relational
databases existed nearly 10 years before

54
00:03:58.420 --> 00:04:01.960
the first spreadsheet program and we
just have to accept that column means

55
00:04:01.960 --> 00:04:04.960
slightly different things in each case
but more on that shortly

56
00:04:06.650 --> 00:04:11.570
now a row or record is a single set of
data for all fields that are in that

57
00:04:11.570 --> 00:04:16.790
table so if you've got four columns like
the example on the screen and if your

58
00:04:16.790 --> 00:04:21.320
table has an invoice number or a date
a description and an amount then a row

59
00:04:21.320 --> 00:04:25.340
represents those four values for a
single invoice so it's really a

60
00:04:25.340 --> 00:04:29.840
collection of all the columns that
comprise the details of one entry in

61
00:04:29.840 --> 00:04:34.580
that table so the highlighted record
holds the details for invoice number two

62
00:04:34.580 --> 00:04:39.080
which was a laptop costing just a
thousand dollars sold on the

63
00:04:39.080 --> 00:04:45.410
twenty-fourth of may 2016 and you can use
either the terms row or records to

64
00:04:45.410 --> 00:04:49.640
identify it but the correct relational
database terminology is actually row

65
00:04:49.640 --> 00:04:51.650
....

66
00:04:51.650 --> 00:04:56.720
now a flat file database stores all
the data in a single file which can

67
00:04:56.720 --> 00:05:00.530
result in a lot of duplication of data
over here

68
00:05:00.530 --> 00:05:05.180
ISPs credit limit needs to be increased
so that they can purchase the monitor as

69
00:05:05.180 --> 00:05:08.090
otherwise that can actually go over the
limit

70
00:05:08.090 --> 00:05:12.080
so in order to increase the limit the
data in three rows would have to be

71
00:05:12.080 --> 00:05:13.990
modified

72
00:05:13.990 --> 00:05:19.060
now as you can see every row in the
table that contains a record for isp

73
00:05:19.060 --> 00:05:24.670
must be changed in order to increase the
credit limit so flat file databases are

74
00:05:24.670 --> 00:05:29.230
not used very often anymore but they
were fairly popular in the early days as

75
00:05:29.230 --> 00:05:34.060
they directly mapped to those card index
records that companies sometimes used

76
00:05:34.060 --> 00:05:39.070
now if we were storing names addresses
and phone number type information than a

77
00:05:39.070 --> 00:05:43.510
flat file database is fine for the job
and even today people still use address

78
00:05:43.510 --> 00:05:48.250
books and rolex style contact systems
with each person's details on a

79
00:05:48.250 --> 00:05:52.870
different card but there isn't really
much need to relate the individual cards

80
00:05:52.870 --> 00:05:53.860
to each other

81
00:05:53.860 --> 00:05:59.050
this works perfectly well as you can see
from this invoice example though trying

82
00:05:59.050 --> 00:06:04.060
to store all the data in a single table
results in duplicate data you're using a

83
00:06:04.060 --> 00:06:08.410
relational database tables can be
related to other tables which is very

84
00:06:08.410 --> 00:06:10.390
useful

85
00:06:10.390 --> 00:06:15.310
so continuing on with the invoice
example we can split the data out into

86
00:06:15.310 --> 00:06:20.400
a customer table which contains standard
company data such as their name address

87
00:06:20.400 --> 00:06:24.610
and a credit limit and another table
called invoices containing all that

88
00:06:24.610 --> 00:06:29.830
customers purchases the name column in
the customer table is related to the

89
00:06:29.830 --> 00:06:35.330
name column in the invoices table now in
relational database terms this is called

90
00:06:35.330 --> 00:06:41.370
a join in fact in this example we have a
one-to-many join because they can and

91
00:06:41.370 --> 00:06:46.440
probably will be many invoice rows for
each customer using a relational model

92
00:06:46.440 --> 00:06:50.580
updating a customer's credit limit
involves changing the data in just a

93
00:06:50.580 --> 00:06:55.410
single row so there is a mechanism to
join these two tables to link the

94
00:06:55.410 --> 00:07:00.050
individual records in each table to each
other and you'll often see designs were

95
00:07:00.050 --> 00:07:03.690
third table is used to provide the link
that's in this next slide

96
00:07:06.090 --> 00:07:10.500
and it's also very common to use a
linking table to relate the data to two

97
00:07:10.500 --> 00:07:16.710
other tables now here when an invoice
record is stored a new record is created

98
00:07:16.710 --> 00:07:22.350
in the customer_invoices table
to link for example invoice triple 0 4

99
00:07:22.350 --> 00:07:27.450
with customer isp now one advantage of
this is that the invoice table only

100
00:07:27.450 --> 00:07:32.250
contains data relating to invoices the
rows are not cluttered up with

101
00:07:32.250 --> 00:07:37.170
customer information of any kind not
even the customer name splitting the

102
00:07:37.170 --> 00:07:40.470
data up like this is known as
normalization now database

103
00:07:40.470 --> 00:07:45.090
normalization is basically the process
of removing redundant duplicated and

104
00:07:45.090 --> 00:07:49.800
irrelevant data from the
tables and the more that this is done

105
00:07:49.800 --> 00:07:54.330
the higher the level of normalization if
you look into a normalization you'll

106
00:07:54.330 --> 00:08:00.060
find that you can go up to level 6
normal form but in most practical

107
00:08:00.060 --> 00:08:04.140
applications it's ready to go beyond the
third level that's an interesting

108
00:08:04.140 --> 00:08:08.100
subject but the math can get a bit
horrible at the higher levels our

109
00:08:08.100 --> 00:08:11.670
example on the slide isn't quite as
normal as we should be because we've

110
00:08:11.670 --> 00:08:15.390
used the customer name as the link
between the customer and invoice tables

111
00:08:15.390 --> 00:08:19.740
and if one of our customers change its
name which is quite common thing happen

112
00:08:19.740 --> 00:08:24.540
then we'll have to update each of the
relevant entries in the customer

113
00:08:24.540 --> 00:08:28.920
_invoices table as well the
usual way to do with this is to use a

114
00:08:28.920 --> 00:08:35.160
unique ID field that stays the same for
each customer once its allocated we

115
00:08:35.160 --> 00:08:38.700
shouldn't store the customer balances in
the table has that's best calculated

116
00:08:38.700 --> 00:08:41.870
when needed

117
00:08:41.870 --> 00:08:47.300
so a view is a way of looking at the
data in a format similar to a table but

118
00:08:47.300 --> 00:08:52.820
bringing data together for more than one
joint table so in this example the view

119
00:08:52.820 --> 00:08:58.130
contains columns from the customer table
and the invoices table so a view can

120
00:08:58.130 --> 00:09:01.880
just contain just some columns from a
single table for example just the

121
00:09:01.880 --> 00:09:05.540
description column from the invoice table to produce a list of items that

122
00:09:05.540 --> 00:09:11.840
have been sold now in SQL lite the data
in a view can't be updated so that means

123
00:09:11.840 --> 00:09:15.950
that you can't add a new row to a view and have it place to new data into the

124
00:09:15.950 --> 00:09:20.690
relevant tables some databases
such as Microsoft sql server do allow

125
00:09:20.690 --> 00:09:25.730
this but in that case you do have
restrictions on the columns that can and

126
00:09:25.730 --> 00:09:30.020
must contain if they're used in this way
that's not something we need to worry

127
00:09:30.020 --> 00:09:32.570
about because we can't do that in sql lite

128
00:09:32.570 --> 00:09:35.900
alright so that's enough to get started
and we're going to explore these terms

129
00:09:35.900 --> 00:09:40.850
more as we go through sql lite so
coming up in the next videos we're going

130
00:09:40.850 --> 00:09:45.620
to discuss a a few more database concepts
and i'll be going into more detail will

131
00:09:45.620 --> 00:09:50.240
be using practical examples and actually
using the sql lite database now

132
00:09:50.240 --> 00:09:55.280
sql lite is designed to be embedded
applications and is actually a library

133
00:09:55.280 --> 00:10:00.590
that's called from our application code
it does ship with the shell program that

134
00:10:00.590 --> 00:10:04.820
you can use to create databases in
integrate them though and we'll start by

135
00:10:04.820 --> 00:10:09.020
using that to explore the commands
available in sql lite now before we

136
00:10:09.020 --> 00:10:12.650
can use that shell program we need to
make sure that it's available on your

137
00:10:12.650 --> 00:10:17.030
systems path so the next three videos
are going to show you how to do that for

138
00:10:17.030 --> 00:10:21.290
windows mac and linux so there's one
video for each operating system so

139
00:10:21.290 --> 00:10:25.010
follow the instructions in the relevant
video for your operating system and you

140
00:10:25.010 --> 00:10:28.790
can skip over the videos that aren't
relevant so i'll see you in one of those

141
00:10:28.790 --> 00:10:29.210
videos