1
00:00:05,580 --> 00:00:10,230
so let's go through some common database
terminology that should be useful for

2
00:00:10,230 --> 00:00:13,140
you when we're working with the
databases in this section of the course

3
00:00:13,140 --> 00:00:18,090
and the idea here is that it will
make it a lot easier to understand the

4
00:00:18,090 --> 00:00:21,270
app when you understand what the
fundamentals are and what all the

5
00:00:21,270 --> 00:00:26,940
actual various terms mean now the very basic level the databases the

6
00:00:26,940 --> 00:00:32,880
container for all the data that you store
or that resides inside now when you

7
00:00:32,880 --> 00:00:37,630
use the term database you're referring
to the entire data as well as the

8
00:00:37,630 --> 00:00:43,200
structure its actually stored in
and in addition any queries and views on

9
00:00:43,200 --> 00:00:46,410
that data now in sql lite the

10
00:00:46,410 --> 00:00:51,710
the entire database contents are stored
in one single file but that isn't true

11
00:00:51,710 --> 00:00:55,710
of most large database systems now the
database dictionary provides a

12
00:00:55,710 --> 00:01:00,840
comprehensive list of the structures and
types of data that are used in recording

13
00:01:00,840 --> 00:01:06,580
the data so basically describes all the
tables and fields within the database now

14
00:01:06,580 --> 00:01:11,050
more on the specifics later but in
sql lite there is a table in each

15
00:01:11,050 --> 00:01:16,750
database called SQL_lite_master that has this information

16
00:01:16,750 --> 00:01:21,970
in it you can query that table but there
are commands that do it for you so you

17
00:01:21,970 --> 00:01:25,780
don't have to understand the structure
of the master table but it's there for

18
00:01:25,780 --> 00:01:29,050
you anyway and as we go through the
app you'll see how these commands

19
00:01:29,050 --> 00:01:32,050
actually work

20
00:01:32,620 --> 00:01:38,470
now a table is a collection of related
data hold in the database so i think of

21
00:01:38,470 --> 00:01:42,040
a contact database for example that
stores the name and the address and the

22
00:01:42,040 --> 00:01:46,630
phone number or perhaps your customers
or what about an invoice table that

23
00:01:46,630 --> 00:01:51,040
records the invoice number in details of
the invoice so in this slide there are

24
00:01:51,040 --> 00:01:56,020
two tables contacts and invoices that are
used to store information about contacts

25
00:01:56,020 --> 00:02:00,750
and invoice details now its SQL lite
databases such a sql lite

26
00:02:00,750 --> 00:02:03,210
or another
database like Microsoft sql server

27
00:02:03,750 --> 00:02:08,320
there are ideal for storing structured
data that can be organized neatly into

28
00:02:08,320 --> 00:02:12,910
rows and columns like you can see in
these examples now with all the interest

29
00:02:12,910 --> 00:02:18,290
in Big Data their databases such as no
sql or hadu that can cope with data

30
00:02:18,290 --> 00:02:21,110
that doesn't have such an obvious
structure but we're going to be

31
00:02:21,110 --> 00:02:24,110
restricting our use of database to
structured data

32
00:02:26,100 --> 00:02:31,920
so field is the basic unit of data in
the table so a field in a database can be

33
00:02:31,920 --> 00:02:35,480
thought of probably in a similar way to
what a variable is and

34
00:02:35,480 --> 00:02:39,440
you've seen those obviously in different
apps and just like a class variable a

35
00:02:39,440 --> 00:02:44,810
database field has a name and a type
now the type restricts what kind of

36
00:02:44,810 --> 00:02:49,490
data can be stored in the field for
example it could be a string or it could

37
00:02:49,490 --> 00:02:54,800
accept numbers so many databases also
allowed date fields large text fields

38
00:02:54,800 --> 00:02:59,090
and also fields where you can store
things like photographs or audio and

39
00:02:59,090 --> 00:03:03,650
these field types can
often are often called blobs which is

40
00:03:03,650 --> 00:03:08,360
supposed to stand for binary large
object it's a great name and I can't

41
00:03:08,360 --> 00:03:11,960
help thinking that the original acronym
was something like LBO for large binary

42
00:03:11,960 --> 00:03:14,960
object and they came up with a cooler name afterwards

43
00:03:16,100 --> 00:03:21,490
now fields are often referred to as
columns in databases I know this can be

44
00:03:21,490 --> 00:03:25,130
technically confusing sometimes because
if you come from an Excel background

45
00:03:25,130 --> 00:03:28,070
you'll find that the definition is
probably a bit different to what you

46
00:03:28,070 --> 00:03:32,960
think of as a column in the spreadsheet
the term column refers to an entire set

47
00:03:32,960 --> 00:03:38,240
of data extending across many rows but
in a relational database column

48
00:03:38,240 --> 00:03:40,700
generally refers to a single entry

49
00:03:40,700 --> 00:03:44,120
although when talking about the
structure of a table rather than the

50
00:03:44,120 --> 00:03:48,410
actual data then you could talk about a
column to hold the invoice number which

51
00:03:48,410 --> 00:03:53,120
is probably closer to the spreadsheet
use of the term now when referring to

52
00:03:53,120 --> 00:03:57,430
the data in the database though we're
talking about an individual item like a

53
00:03:57,430 --> 00:04:02,270
base unit of data the relational
databases existed nearly 10 years before

54
00:04:02,270 --> 00:04:05,810
the first spreadsheet program and we
just have to accept that column means

55
00:04:05,810 --> 00:04:08,810
slightly different things in each case
but more on that shortly

56
00:04:10,500 --> 00:04:15,420
now a row or record is a single set of
data for all fields that are in that

57
00:04:15,420 --> 00:04:20,640
table so if you've got four columns like
the example on the screen and if your

58
00:04:20,640 --> 00:04:25,170
table has an invoice number or a date
a description and an amount then a row

59
00:04:25,170 --> 00:04:29,190
represents those four values for a
single invoice so it's really a

60
00:04:29,190 --> 00:04:33,690
collection of all the columns that
comprise the details of one entry in

61
00:04:33,690 --> 00:04:38,430
that table so the highlighted record
holds the details for invoice number two

62
00:04:38,430 --> 00:04:42,930
which was a laptop costing just a
thousand dollars sold on the

63
00:04:42,930 --> 00:04:49,260
twenty-fourth of may 2016 and you can use
either the terms row or records to

64
00:04:49,260 --> 00:04:53,490
identify it but the correct relational
database terminology is actually row

65
00:04:53,490 --> 00:04:55,500
....

66
00:04:55,500 --> 00:05:00,570
now a flat file database stores all
the data in a single file which can

67
00:05:00,570 --> 00:05:04,380
result in a lot of duplication of data
over here

68
00:05:04,380 --> 00:05:09,030
ISPs credit limit needs to be increased
so that they can purchase the monitor as

69
00:05:09,030 --> 00:05:11,940
otherwise that can actually go over the
limit

70
00:05:11,940 --> 00:05:15,930
so in order to increase the limit the
data in three rows would have to be

71
00:05:15,930 --> 00:05:17,840
modified

72
00:05:17,840 --> 00:05:22,910
now as you can see every row in the
table that contains a record for isp

73
00:05:22,910 --> 00:05:28,520
must be changed in order to increase the
credit limit so flat file databases are

74
00:05:28,520 --> 00:05:33,080
not used very often anymore but they
were fairly popular in the early days as

75
00:05:33,080 --> 00:05:37,910
they directly mapped to those card index
records that companies sometimes used

76
00:05:37,910 --> 00:05:42,920
now if we were storing names addresses
and phone number type information than a

77
00:05:42,920 --> 00:05:47,360
flat file database is fine for the job
and even today people still use address

78
00:05:47,360 --> 00:05:52,100
books and rolex style contact systems
with each person's details on a

79
00:05:52,100 --> 00:05:56,720
different card but there isn't really
much need to relate the individual cards

80
00:05:56,720 --> 00:05:57,710
to each other

81
00:05:57,710 --> 00:06:02,900
this works perfectly well as you can see
from this invoice example though trying

82
00:06:02,900 --> 00:06:07,910
to store all the data in a single table
results in duplicate data you're using a

83
00:06:07,910 --> 00:06:12,260
relational database tables can be
related to other tables which is very

84
00:06:12,260 --> 00:06:14,240
useful

85
00:06:14,240 --> 00:06:19,160
so continuing on with the invoice
example we can split the data out into

86
00:06:19,160 --> 00:06:24,250
a customer table which contains standard
company data such as their name address

87
00:06:24,250 --> 00:06:28,460
and a credit limit and another table
called invoices containing all that

88
00:06:28,460 --> 00:06:33,680
customers purchases the name column in
the customer table is related to the

89
00:06:33,680 --> 00:06:39,180
name column in the invoices table now in
relational database terms this is called

90
00:06:39,180 --> 00:06:45,220
a join in fact in this example we have a
one-to-many join because they can and

91
00:06:45,220 --> 00:06:50,290
probably will be many invoice rows for
each customer using a relational model

92
00:06:50,290 --> 00:06:54,430
updating a customer's credit limit
involves changing the data in just a

93
00:06:54,430 --> 00:06:59,260
single row so there is a mechanism to
join these two tables to link the

94
00:06:59,260 --> 00:07:03,900
individual records in each table to each
other and you'll often see designs were

95
00:07:03,900 --> 00:07:07,540
third table is used to provide the link
that's in this next slide

96
00:07:09,940 --> 00:07:14,350
and it's also very common to use a
linking table to relate the data to two

97
00:07:14,350 --> 00:07:20,560
other tables now here when an invoice
record is stored a new record is created

98
00:07:20,560 --> 00:07:26,200
in the customer_invoices table
to link for example invoice triple 0 4

99
00:07:26,200 --> 00:07:31,300
with customer isp now one advantage of
this is that the invoice table only

100
00:07:31,300 --> 00:07:36,100
contains data relating to invoices the
rows are not cluttered up with

101
00:07:36,100 --> 00:07:41,020
customer information of any kind not
even the customer name splitting the

102
00:07:41,020 --> 00:07:44,320
data up like this is known as
normalization now database

103
00:07:44,320 --> 00:07:48,940
normalization is basically the process
of removing redundant duplicated and

104
00:07:48,940 --> 00:07:53,650
irrelevant data from the
tables and the more that this is done

105
00:07:53,650 --> 00:07:58,180
the higher the level of normalization if
you look into a normalization you'll

106
00:07:58,180 --> 00:08:03,910
find that you can go up to level 6
normal form but in most practical

107
00:08:03,910 --> 00:08:07,990
applications it's ready to go beyond the
third level that's an interesting

108
00:08:07,990 --> 00:08:11,950
subject but the math can get a bit
horrible at the higher levels our

109
00:08:11,950 --> 00:08:15,520
example on the slide isn't quite as
normal as we should be because we've

110
00:08:15,520 --> 00:08:19,240
used the customer name as the link
between the customer and invoice tables

111
00:08:19,240 --> 00:08:23,590
and if one of our customers change its
name which is quite common thing happen

112
00:08:23,590 --> 00:08:28,390
then we'll have to update each of the
relevant entries in the customer

113
00:08:28,390 --> 00:08:32,770
_invoices table as well the
usual way to do with this is to use a

114
00:08:32,770 --> 00:08:39,010
unique ID field that stays the same for
each customer once its allocated we

115
00:08:39,010 --> 00:08:42,549
shouldn't store the customer balances in
the table has that's best calculated

116
00:08:42,549 --> 00:08:45,720
when needed

117
00:08:45,720 --> 00:08:51,150
so a view is a way of looking at the
data in a format similar to a table but

118
00:08:51,150 --> 00:08:56,670
bringing data together for more than one
joint table so in this example the view

119
00:08:56,670 --> 00:09:01,980
contains columns from the customer table
and the invoices table so a view can

120
00:09:01,980 --> 00:09:05,730
just contain just some columns from a
single table for example just the

121
00:09:05,730 --> 00:09:09,390
description column from the invoice table to produce a list of items that

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

123
00:09:15,690 --> 00:09:19,800
that you can't add a new row to a view and have it place to new data into the

124
00:09:19,800 --> 00:09:24,540
relevant tables some databases
such as Microsoft sql server do allow

125
00:09:24,540 --> 00:09:29,580
this but in that case you do have
restrictions on the columns that can and

126
00:09:29,580 --> 00:09:33,870
must contain if they're used in this way
that's not something we need to worry

127
00:09:33,870 --> 00:09:36,420
about because we can't do that in sql lite

128
00:09:36,420 --> 00:09:39,750
alright so that's enough to get started
and we're going to explore these terms

129
00:09:39,750 --> 00:09:44,700
more as we go through sql lite so
coming up in the next videos we're going

130
00:09:44,700 --> 00:09:49,470
to discuss a a few more database concepts
and i'll be going into more detail will

131
00:09:49,470 --> 00:09:54,090
be using practical examples and actually
using the sql lite database now

132
00:09:54,090 --> 00:09:59,130
sql lite is designed to be embedded
applications and is actually a library

133
00:09:59,130 --> 00:10:04,440
that's called from our application code
it does ship with the shell program that

134
00:10:04,440 --> 00:10:08,670
you can use to create databases in
integrate them though and we'll start by

135
00:10:08,670 --> 00:10:12,870
using that to explore the commands
available in sql lite now before we

136
00:10:12,870 --> 00:10:16,500
can use that shell program we need to
make sure that it's available on your

137
00:10:16,500 --> 00:10:20,880
systems path so the next three videos
are going to show you how to do that for

138
00:10:20,880 --> 00:10:25,140
windows mac and linux so there's one
video for each operating system so

139
00:10:25,140 --> 00:10:28,860
follow the instructions in the relevant
video for your operating system and you

140
00:10:28,860 --> 00:10:32,640
can skip over the videos that aren't
relevant so i'll see you in one of those

141
00:10:32,640 --> 00:10:33,060
videos