Transcript What is a database?

DATABASE
• What exactly is a database
• How do databases work?
• What's the difference between a spreadsheet
database and a "real" database?
What is a database?
"A database is an organized collection of
information".
Database Basics
• You use databases all the time, although you
might not realize it! For example:
– Telephone directories
– The list of contacts on your phone
– Online product catalogues and shops
Database Basic
• The important thing about databases is the way
that the information is organized.
• Let's look at a simple telephone directory as an
example. It has entries like this:
– [Tupou] [Sione][Hala Hekoni][Ma’ufanga Tt ][21333]
• You could simply enter all the information as
shown above and then print your directory.
Problem
• The problem is that if you wanted to do
something else with the data - for example,
– organise it in a different way
– find all the people in the Maufanga area
– or publish a different version of the directory,
• you would have a lot of work to do
Database Basic
The listing consists of five different pieces of
information:
[Last name] [First name] [Address (Road)][Area [(Village)] [Phone number ]
– We refer to each of these bits of data as a field.
– Each group of fields belonging to the same item is
called a record.
– And each collection of records of the same type of
data is called a table.
Flat File Database
• Information above can be stored in a
spreadsheet:
• One worksheet is a table
• Each row in the worksheet is a record
• Each column is a field
– The structure described above is what we refer to
as a "flat-file" database: it's a one-dimensional
collection of data about one topic.
– fine for many purposes
Relational Database
• That's why we have relational databases.
• So that information such as discussed in slide
#5 would be very easy to used
Relational Database
• In a RD, we need to maintain more complex
structures in which data from two or more
tables is linked together.
• examples are:
– Families (parents and children)
– Products and parts
– Manufacturers and Products
– Classes and Students
– Customers and Invoices
You might be tempted to think?
• "Why can't I just add more fields if I need more
information?"
– For example, if your database contains information
about products and you want to add a list of the
components that make up each product, why not just
add fields for "component 1", "component 2" and so
on?
• Well, yes, you can, but you'll run into limitations
such as:
– Some products might have only one component;
another might have 20. How do you know how many
fields to add?
You might be tempted to think?
• What if the same component is used in a
number of different products?
• What if you wanted to find a particular
component?
– You would have to search all those additional
component fields, because you wouldn't know
which one it might be in!
You might be tempted to think?
• What if you wanted to produce a report listing
all your components and their prices?
– That would be extremely difficult.
That's why we have relational databases
• In the parts and components example, we
would have two tables: Parts and
Components.
• The basic product details would go into fields
in the Products table, and all the details about
each component would go into a record in the
Components table.
Important thing needed
• One more important thing is needed: something
to tell the database how the records are linked
together - in other words, which components
belong to which products.
• For this purpose, each record must have a unique
identifier of some sort.
• It can be simply a sequential number, or an
alphanumeric code of some sort that you devise.
• The import things are that it must be UNIQUE for
each record and it must never change.
Simple Relationship between products and components tables
This illustrates a "many-to-one" relationship: many components can be
related to one product via the product id. So, to find all the components that
comprise the product whose ID number is 1234, you would search the
components table for all records whose product id is 1234. There might be
just one, or there might be dozens.
But what if the same component can be used in a number of
different products?
That's what we call a many-to-many relationship: many components can be
related to many products. To facilitate this, we need to introduce an additional
table just to keep track of those relationships.
Relations
• The relations table would contain one record
for each link between a product and a
component. With this sort of structure, you
can easily produce reports showing all the
components belonging to a product, or all the
products in which a particular component is
used.
A way forward
• DB Guide
http://www.tihe.org/Accounting/NOS215%2021610/DB%20Guide.pdf
• Start with “ first step with Access”
http://www.projectwoman.com/articles/05Access.ht
m
• Database Design
http://redmondmag.com/articles/2001/04/01/datab
ase-design-101.aspx
• OR ANY RESOURCES YOU LIKE …..
Activities
• You should have done practical 1 – 3
• DB Designing
http://www.tihe.org/Accounting/DB
Designing