so let's go through some common database
terminology that should be useful for
you when we're working with the
databases in this section of the course
and the idea here is that it will
make it a lot easier to understand the
app when you understand what the
fundamentals are and what all the actual
various terms mean now the very basic
level the databases the container
for all the data that your store or that
resides inside now when you use the term
database you're referring to the entire
data as well as the structure its
actually stored in and in addition any
queries and views on that data now in sqlite
the entire database contents
are stored in one single file but that
isn't true of most large database
systems now the database dictionary
provides a comprehensive list of the
structures and types of data that are
used in recording the data so basically
describes all the tables and fields
within the database now more on the
specifics later but in sqlite there
is a table in each database called SQL
_lite_master that
has this information in it you can query
that table but there are commands that
do it for you so you don't have to
understand the structure of the master
table but it's there for you anyway and
as we go through that you'll see how
these commands actually work
now a table is a collection of related
data hold in a database so think of a
contact database for example that stores
the name and the address and the phone
number or perhaps your customers or what
about an invoice table that records the
invoice number in details of the invoice
so in this slide there are two tables
contacts and invoices that are used to
store information about contacts and
invoice details now sqlite
databases such a sqlite or another
database like Microsoft sql server
there are ideal for storing structured
data that can be organized neatly into
rows and columns like you can see in
these examples now with all the interest
in Big Data their now databases such as no
sql or hado that can cope with data
that doesn't have such an obvious
structure but we're going to be
restricting our use of database to
structured data
so a field is the basic unit of data in
the table so a field in a database can be
thought of probably in a similar way to
one of the variable is and you've seen
those obviously and just like a class
variable a database field has a name
and a type now the type restricts what
kind of data can be stored in the field
for example it could be a string or it
could accept numbers now many databases
also allowed date fields large text
fields and also fields where you can
store things like photographs or audio
and these field types are
often are often called blobs which is
supposed to stand for binary large
object it's a great name and I can't
help thinking that the original acronym
was something like LBO or large binary
object and they came up with a cool
sounding named afterwards
now fields are often referred to as
columns in databases now this can be
technically confusing sometimes because
if you come from an Excel background
you'll find that the definition is
probably a bit different to what you
think of as a column in the spreadsheet
the term column refers to an entire set
of data extending across many rows but
in a relational database column
generally refers to a single entry
although when talking about the
structure of a table rather than the
actual data then you could talk about a
column to hold the invoice number which
is probably closer to the spreadsheet
use of the term now when referring to
the data in the database though we're
talking about an individual item like a
base unit of data now relational
databases existed nearly 10 years before
the first spreadsheet program and we
just have to accept that column means
slightly different things in each case
but more on that shortly
now a row or record is a single set of
data for all fields that are in that
table so if you've got four columns like
the example on the screen and if your
table has an invoice number or a date a
description and an amount then a row
represents those four values for a
single invoice so it's really a
collection of all the columns that
comprise the details of one entry in
that table so the highlighted record
holds the details for invoice number two
which was a laptop costing just over a
thousand dollars sold on the
twenty-fourth of may 2016 and you can use
either the terms row or record to
identify it but the correct relational
database terminology is actually row
now a flat file database stores all
the data in a single file which can
result in a lot of duplication of data
over here
ISPs credit limit needs to be increased
so that they can purchase the monitor as
otherwise they actually go over the
limit
so in order to increase the limit the
data in three rows would have to be
modified
now as you can see every row in the
table that contains a record for isp
must be changed in order to increase the
credit limit so flat file databases are
not used very often anymore but they
were fairly popular in the early days as
they directly mapped to those card index
records that companies sometimes used
now if we were storing names addresses
and phone number type information than a
flat file database is fine for the job
and even today people still use address
books and rolex style contact systems
with each person's details on a
different card but there isn't really
much need to relate the individual cards
to each other
this works perfectly well as you can see
from this invoice example though trying
to store all the data in a single table
results in duplicate data you're using a
relational database tables can be
related to other tables which is very
useful
so continuing on with the invoice
example we can split the data out into
a customer table which contains standard
company data such as their name address
and a credit limit and another table
called invoices containing all that
customers purchases the name column in
the customer table is related to the
name column in the invoices table now in
relational database terms this is called
join in fact in this example we have a
one-to-many join because they can and
probably will be many invoice rows for
each customer using a relational model
updating a customer's credit limit
involves changing the data in just a
single row so there is a mechanism to
join these two tables to link the
individual records in each table to each
other and you often see designs were
third table is used to provide the link
as in this next slide
and it's also very common to use a
linking table to relate the data to two
other tables now here when an invoice
record is stored a new record is created
in the customer_invoices table
to link for example invoice triple 0 4
with customer isp now one advantage of
this is that the invoice table only
contains data relating to invoices the
rows are not cluttered up with
customer information of any kind not
even the customer name splitting the
data up like this is known as
normalization now database
normalization is basically the process
of removing redundant duplicated
and irrelevant data from the
tables and the more that this is done
the higher the level of normalization if
you look into a normalization you'll
find that to you can go up to level 6 6
normal form but in most practical
applications it's rare to go beyond the
third level that's an interesting
subject but the math can get a bit
horrible at the higher levels our
example and the sliders isn't quite as
normal as we should be because we've
used the customer name as the link
between the customer invoice tables and
if one of our customers change its name
which is quite a common thing to happen then
we'll have to update each of the
relevant entries in the customer
_invoices table as well now
the usual way to do with this is to use
a unique ID field that stays the same
for each customer once its allocated we
shouldn't store the customer balances in
the table as that's best calculated
when needed
so a view is a way of looking at the
data in a format similar to a table but
bringing data together from more than
one join table so in this example the
view contains columns from the customer
table and the invoices table so a view
can just contain just some columns from
a single table for example just the
description column from the invoice table
to produce a list of items that
have been sold in sqlite the data
and the view can't be updated so that means
you can't add a new row to a view and
have it place to new data into the
relevant tables now some databases such
as Microsoft sql server do allow this
but in that case we have use do have
restrictions on the columns that can and
must contain if they're used in this way
so that's not something we need to worry
about because we can't do that in
sqlite
alright so that's enough to get us
started and we're going to explore these
terms more as we go through sqlite
so coming up in the next videos we're
going to discuss a few more database
concepts and i'll be going into more
detail will be using practical examples
and actually using the sqlite
database now sqlite is designed to
be embedded in applications and is
actually a library that's called from
our application code it does ship with a
shell program that you can use to create
databases in integrate though and
we'll start up are using that to explore
the commands available in sqlite
now before we can use that shell program
we need to make sure that it's available
on your systems path so the next three
videos are going to show you how to do
that for windows mac and linux so
there's one video for each operating
system so follow the instructions in the
relevant video for your operating system
and you can skip over the videos that
aren't relevant so i'll see you in one of
those videos