Transcript 05-Types_of_databasesx


You will be able to:
› Explain how databases may be stored in
more than one physical location and how
distribution may be carried out using
different approaches:
 Partitioned (Horizontal and Vertical)
 Duplicated databases
 Central databases with remote local indexes

As previously mentioned, databases are the
backbone of most, if not all, companies.

The wealth of information stored on a
database system can sometimes be
overwhelming and sometimes irrelevant to
some people.

Databases can be setup in different ways
and different solutions are appropriate in
different situations.

You are probably used to working with one
database.

This database will store all the tables and all of
the data.

However, there are methods which allow us to
split databases into different parts and to store
those parts in different physical locations.

However, a good database system will not
make the user aware of this.

In a partitioned database, the database is split
into different parts.

Each part is stored in a different physical ‘site’.

Each site can be on a different part of a server
or on separate computers.

There are two types of partitioning:
› Horizontal
› Veritcal

Data is partitioned based on records.

For example, an estate agency has
many outlets across the country.

Instead of having one central database,
the estate agency have split their
database based on the agency and the
location of the houses.
UK houses and
flats.
Imagine all the records for all the
houses that are for sale or rent are
stored in one database. Filtering
houses would take longer
because of the larger number of
records. Also, returning results
would be longer because the
request has further distance to
travel.
Instead of saving all these records
in one database you could split
the records into their own
database based on their
locations.
Leicester
Each branch now has its own
database.
Coventry
If someone wanted to buy a
house in their area, they would
walk into their branch and the
estate agent could search their
database quickly.
Birmingham
Leicester
Coventry
Birmingham
If someone wanted to move area,
the estate agent would need to
request data from another
branch’s database.

This type of partition splits data across sites based on
the fields.

For example, a small chain of shops which specialises
in rare, one off, products may have different
departments which are in different locations.

Each shop stocks different items as most of their
products are one off or bespoke.

These departments include the head office,
purchasing and the actual shop.
Head Office
Purchasing
Shop
The head office stores information
about what products are stocked
in different shops.
They are uninterested in the prices
or who the supplier is.
Head Office
Purchasing
Shop
The purchasing department is
interested in making orders to
suppliers when they receive an
order request from a shop.
Head Office
The vendor is interested in stocking
particular items and knowing:
Purchasing
Shop
-How much stock they have
-How much to sell the stock for
ItemID
ItemName
Branch
Supplier
Quantity
AB111
Mega Old
Clock
Leicester
Ye Oldie
Clocks
1
£995
AC222
Wobbly Chair
Leicester
Steady on
2
£569
AD333
Grand Piano
Coventry
Chop Sticks
1
£5995
AE444
Old Picture
Birmingham
Arts R Us
1
£9995
…
…
…
…
…
…
…
…
…
…
…
…
All databases use this field
Head Office
Purchasing
Shop
Price

Thinking back to our estate agency
example with horizontal partitions…

Each branch had only part of all the
possible records.

With replicated databases, each branch
would have a complete copy of the
entire database.
This is the full version of the
database.
UK houses and
flats.
Horizontal Partition means each
branch has only some of the
database.
Leicester
UK houses and
flats.
Coventry
Birmingham
Replicated databases means all
branches have a full copy of the
original database.
UK houses and
flats.
Leicester
Coventry
Birmingham

This database is stored in one place and
one place only.

For example, a bank stores all the
records of its customers in one database.

The problem is each branch has to
search the database every time it wants
to find a customer’s records.

However, to speed things up, each
branch keeps a database which stores
the customers account number and an
index number.

Now, when a branch requests data from
the head office server, it can tell the
server exactly where the record can be
found.

Think of it like a library system.

All the books are in one big room.

To find the book you were looking for you
would have to search all the titles on the
shelf until you found the book you wanted.

However, if you knew the index number of
the book you could go straight to that
section of the shelf and pick it up.
Head Office
Index
Account #
Surname
Forename
Balance
1
01234567
Beale
Ian
£300
…
…
…
…
…
5000
12345678
Butcher
Frank
-£34.83
5001
98765432
Mitchell
Peggy
£923
…
Leicester Branch
Account #
Index
01234567
1
…
…
12345678
5000
98765432
5001

Describe how data can be distributed
using horizontal partitioning.

Describe the difference between
horizontal and vertical partitioning.

How could local indexes be used with a
centralised database?

Well now you know about different ways of
distributing a database…

But you still need to know when it is
appropriate to use those types of database.

Read through pages 147-150 and answer the
questions at the end.

Those pages are packed full of advantages
and disadvantages of using different types of
database.