Transcript Databases - dolinski.co.uk | home

Databases 101
© Dolinski 2007
What you will learn
• How relational databases work
• What are the components that make up a
database
• How to create each component
Relational Databases
• Unlike a flat file database
– Database with a single flat file table
• Address Book
• Phone numbers
• Online Recipe
• Uses lots of tables which store subject
specific information.
• This information is then linked to each other
which creates the relationships.
Relational Databases
• DVD Rental Shop
Name
Age
DVD
Price
Rating
David
15
Matrix
£3.99
PG
David
15
Mission Impossible 3
£3.99
15
Laura
14
Mean Girls
£2.99
PG
David
15
Mean Girls
£2.99
PG
• Flat file databases tend to store lots of repeated data.
• This means the databases becomes very inefficient.
• Flat files are also very restrictive. Updating a DVD’s price
means changing every single record. This can lead to
mistakes.
Relational Databases
• The solution to our problem is simple.
• Break down the tables into subject specific
categories.
• In our rental shop example we would end up
with three tables:
– Customer
– DVD
– Rental
Relational Databases
• Customer Table:
– Would store all the information we need to know
about the customer.
– This means we only need to store the customer’s
details once.
– This means that if a customer’s details change,
for example, their address, then we only need to
change one record.
Relational Databases
• DVD Table:
– This would store information about the DVD’s in
our shop.
– Again, we only need to store the information
once.
– This means that we only have to change the
details of a DVD once if they might change, for
example, its rental price.
Relational Databases
• Rental Table:
– Would store all the information about each rental.
– The rental table would store the membership
code of the customer, the DVD code and a rental
code.
– From the membership code in the rental table,
we can find the customer’s details in the
customer table, and likewise for the DVD details,
we can find the details in the DVD table by using
the DVD code.
Relational Databases
• In MS Access, we could see how our tables
were linked by using the Relationships View:
Relational Databases
• Databases have different components that
you can use.
– Tables
– Queries
– Forms
– Reports
Relational Databases
• Tables
– Used for storing data
• Queries
– Used for finding data
• Forms
– Used for entering data
• Reports
– Used for displaying data
Tables
• These are the first things you set up.
• In MS Access, you can use the design view
to set up tables quickly and easily.
• You must decide on two main things:
– Field Name
– Data Type
Tables
• Field Names are used to identify the actual
piece of data which needs to be entered.
– Name
– Address
– Phone Number
– Etc
• They are basically headings for the table.
Tables
• Data Types let us know what type of data
each field will store.
• There are lots and it is important to chose the
right one.
Tables
• Below is a list of data types that you might
use:
Data Type
What is it used for?
Text
Storing text and numbers which are not used in
calculations, e.g. telephone and house numbers.
Number
Used for storing numbers such as quantities and
ages.
Date/Time
Used for storing dates and times, e.g. Date of Births
and booking dates.
Yes/No
Used for storing yes or no data, e.g. Has the
Customer Paid?
Currency
Used for storing money values, e.g. Prices
AutoNumber
Used for storing unique values, e.g. membership
numbers.
Tables
Here is an
example of a
table in the
design View:
Tables
• Data validation is another important aspect of
table design.
• Data validation is the method of preventing
bad data from entering the system.
• For your project you will only need to show
two or three examples of your system using
data validation.
Tables
• Data validation can be used on things such
as:
– Dates
• Not allowing underage people from buying certain
products
– Numbers
• Not allowing a number to be entered below or above a
certain level e.g. Number of beds in a room
Tables
• Validation rules are entered in the validation
rule field in the table design.
• Rules can be simple things such as:
– >20

This rule means a number must be
greater than 20 when entered.
– Like “Mr” Or “Miss” Or “Mrs”

This rule means that only defined
entries can be made into the
system.
Tables
• This is an example validation rule for a customer
table.
– It defines the entries that are needed for the Title Field.
– The validation text appears when data has been entered
incorrectly or doesn’t meet the validation rule.
Queries
• These are used to find information.
• They can search through tables for data that
we ask it to find, for example:
– A name
– Records between certain dates
– Telephone numbers
Queries
• When writing a query we need to know two
things:
– What data fields we want to display
– What data we want to look for
Queries
• This is the design view for a query:
• This query will ask the user to enter a valid
customer number.
Queries
• The database will display a user input box
and the user can type in the customer
number:
• The results of the
query will be shown.
Queries
• The code to do this is:
• In the Criteria field write this:
– [Enter Customer number:]
• [This means open ended…the user defines]
Queries
• If I always wanted to find customer 0001 then
in the criteria field I would enter 0001!
• AMAZING!!!
Forms
• Forms are used for data entry.
• They should be designed to allow data to be
entered quickly and accurately.
• You can use a design wizard to help you set
up your form but you will need to change the
design to make it more user friendly.
Forms
• The wizard will produce something like this:
• This isn’t very user friendly and will probably result
in some bad data from being entered.
Forms
• Changing the design can vastly improve:
– The speed at which data is entered.
– The accuracy of the data being entered.
• Using drop down boxes and option menus
are a great way of speeding up data entry
and they provide some data validation.
Forms
You can use Drop
Down Menus
Or you can use Option
Buttons for example.
Reports
• Reports are used to output data from the database.
– Such as printing off a list of customers for example.
• Like the forms, you can use the design wizard to
get the initial layout.
• You will need to tweak the layout to maximise the
efficiency of the report.
• The wizard does a good job of wasting space!
Reports
• An example report produced by the wizard might
look like this:
• The report can be designed to output data in any
particular order.
• This report prints data out in ascending order of last
name.