Transcript 3.1.5: Relational Database Concepts


To provide you with an overview of the aspects that
make up a relational database.

This includes:
›
›
›
›
›
›
›
›
›
›
Tables
Records
Fields
Data types
Keys
Composite primary key
Secondary key
Entities
Data dictionary
Parameter queries

Imagine a database is a filing cabinet.

Each drawer stores information about a particular
thing.

A drawer is known as a
.

Imagine a database is a filing cabinet.

Each drawer stores information about a particular
thing.

Imagine a database is a filing cabinet.

Each drawer stores information about a particular
thing.

Because each table stores data about one
particular thing we call it an
.

Because each table stores data about one
particular thing we call it an
.

Because each table stores data about one
particular thing we call it an
.

Each drawer contains a
and each record is
about one thing, for example:
› One particular teacher
› One particular student

Each record is broken down into
.

Each record is broken down into
.

Each field has its own
.

A data type represents the type of data that
should be stored in that field.

There are lots of different data types and each one is suitable
for storing a different type of information.

Some of the more common data types include:
 Used for storing any type of data that uses characters, numbers, symbols
etc.
 Used for storing a number that you would perform a calculation on.
 Generates a number automatically.
 Used for storing dates and times.
 Used for storing money values.
 Used for storing one of two possible options (Yes/No, Boy/Girl, On/Off).

Each record should have a
.

A primary key is a piece of data that is
.

In this example, the student’s username is their unique key. No other student
can have the same username meaning it is unique.
which is

You can sometimes make a
in
.
the data

Instead of using a username to make a primary key, a school
could combine a student’s name with the first line of their
address to form a unique key.

This method
though.

Using this example, you can probably see that if a student had a brother or
sister their
...

Which is why it is sometimes better to just stick with a separate field such as
username!

These are attributes that are designed as an alternate means
of accessing data.

This attribute is not necessarily unique either.

For example, a library my use ISBN numbers as a primary key,
but they may also store the book’s Dewey number.

You could still use the Dewey Decimal System to find a book
but the database might not use this as a primary key.

This is because they may use the same Dewey number for a
different book if it is located on a different floor.
Floor 2: A2 Revision
ISBN: 978-0340966518
DDS: 150.01
The library has two floors.
The first floor is used to store
books relating to AS
subjects.
The second floor is used to
store books relating to A2
subjects.
Floor 1: AS Revision
ISBN: 978-0340958285
DDS: 150.01
Each subject has a main
code.
ICT is 150, Maths is 151 etc.
Each subject has a number
of books.

Whenever you create a database you need to
keep a second ‘database’ which stores data
about the structure and contents of the main
database.

It should include data about:
› What tables the database has and what their relationship
›
›
›
›
to other tables is (more on relationships later).
The structure of each field
Validation rules
The number of records being saved
Etc…

We know that tables store data…and in some
database applications, such as MS Access, you can do
some basic sorting (e.g. alphabetical).

But sometimes you need to find certain data based on
different criteria.

For example, an internet service provider may want to
find all customers who have not paid their bills yet.

They could then go on to filter these results further by
looking for all customers who have had their first
warning and still not paid.
A query can be used to find, sort and filter
data.
This data can then be used to find new data,
e.g. total number of outstanding payments.
It can also be used for things like mail merge,
e.g. sending second warnings to customers.

A simple queries uses one criteria value, for
example:
› Find all male students.

A complex query uses two more criteria values, for
example:
› Find all male students who were born after 1995.

A parameter allows you to dynamically query data.

They allow you to cut down on the number of queries
you have to create.

For example, you may have one query which finds all
male students and another query that finds all female
students.

If you create a parameter query you could have the
query ask you what criteria to use.
› If you type in Male it will find all male results.
› If you type in Female it will find all female results.