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'll 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 at the very basic level, the database is the
container for all the data that you store, or that resides inside it. Now when you
use the term database, you're referring to the entire data, as well as the
structure it's actually stored in, and in addition, any queries and views on
that data. Now in SQLite, which is the database used for Android apps,
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, it 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. Now 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 the app, you'll see how these commands
actually work.
Now, a table is a collection of related data held in a database. So think of
a contact database, for example, that stores the name and the address and the
phone number, of 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 its SQL databases such as SQLite, again used
in Android app development, or another database like Microsoft SQL Server -
they're 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, there are now databases such as No SQL or Hadoop, 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 a table. So a field in a database can be
thought of, probably in a similar way, to what an instance variable in Java is. And
you've seen those obviously in different apps, 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 a 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 can 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 L.B.O, for Large Binary
Object, and they came up with a cooler sounding name afterwards.
Now Fields are often referred to as columns in databases. I know 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. Now in a 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 a 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 thousand dollars, sold on the
twenty-fourth of may 2016. And you can use either of 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. Now here,
ISP's credit limit needs to be increased, so that they can purchase the monitor, as
otherwise, they'd 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, then 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. Now as you can see from this invoice example, though, trying
to store all the data in a single table results in duplicate data. Now 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
customer's 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
a Join. In fact, in this example we have a one-to-many join because there 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'll often see designs where a
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 0004
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 normalisation. 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 normalisation. If you look into normalisation, you'll
find that you can go up to level 6 - Sixth Normal Form - but in most practical
applications it's ready to go beyond the third level. Now it's an interesting
subject but the maths can get a bit horrible at the higher levels. Our
example on the slide isn't quite as normal we should be, because we've
used the customer name as the link between the customer and invoice tables.
Now if one of our customers changes 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 deal with this is to use a
unique ID field, that stays the same for each customer once its allocated. Now 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 Joined 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 Invoices table to produce a list of items that
have been sold. Now in SQLite, the data in a view can't be updated. So that means
that you can't add a new row to a view and have it place the new data into the
the relevant tables. Now some databases such as Microsoft SQL Server do allow
this, but in that case, the views do have restrictions on the columns they 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 a few more database concepts and I'll be going into more detail. We'll
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 and interrogate them, though, and we'll start off by
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
system's 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.