2.3 System Design & Databases / Databases
Databases
Databases play a very important role in our computer based society, they sit behind the scenes in most applications storing all of the information that is vital for the programs function. With such an important part to play in computing, it is important that databases are set up and maintained correctly, this section will get you up to speed on what databases are and how they work.
Databases
|
What is a database?
Simply put, a database is an organised collection of data. Uses for databases? There are many uses for databases, they are literally everywhere, below are a few examples:
|
|
Database Structure
Databases store information in the form of a table. One database can hold multiple tables e.g. a school database could have one table for students and another for parents.
Here is an example database table, this table is storing employee data:
Here is an example database table, this table is storing employee data:
Fields - A field is one piece of information about a thing or object, in the above example 'First Name' is a field that simply holds an employees first name, Last name, emp id, gender and title are also fields.
Records - A record is a collection of fields for one thing, person or object. This essentially means a horizontal row of a database table. In the above example the first record would be:
Records - A record is a collection of fields for one thing, person or object. This essentially means a horizontal row of a database table. In the above example the first record would be:
Emp_Id |
Last_Name |
First_Name |
Gender |
Title |
1000 |
Torbati |
Yolanda |
F |
Programmer |
Data Types
When setting up a database, each field must be given a name and a "Data type". If you are using Microsoft Access to set up your database, the process will look like this:
This screen allows the user to enter each individual field name along with its data type. In this case, apart from Emp_Id, all fields are of the 'Text' data type. But what are data types for?
When you create a database, you must specify the type of data that will be stored in each field as it allows the computer to make estimates of the amount of memory required as well as how it can handle the data.
For example, If a computer knows that data is an integer (Number) then it can search for values greater than or less than a search term. It could not do this with a string data type which is why it is helpful for us to declare this.
Available data types:
When you create a database, you must specify the type of data that will be stored in each field as it allows the computer to make estimates of the amount of memory required as well as how it can handle the data.
For example, If a computer knows that data is an integer (Number) then it can search for values greater than or less than a search term. It could not do this with a string data type which is why it is helpful for us to declare this.
Available data types:
Data Type |
Description |
Text |
Stores a short amount of text e.g a name, address, occupation |
Integer |
Stores a whole number e.g Year group = 10 |
Dates/ Times |
Stores a date or time in a specified format e.g. 09/02/1988 |
Currency |
Stores a monetary value with the currency symbol e.g. $44 |
Boolean (Yes / no) |
Able to store one of two values e.g. yes or no, Male or Female |
Real |
Stores a number with a decimal point e.g. 5.6 |
Primary Key
Take a look at the data types image again below:
Next to the field named Emp_id you will notice that there is a small key, this key declares that Emp_id is the Primary key field.
The primary key field is one that must always be unique i.e. never repeated, this ensures that every single record has something unique that it can be identified by. This helps to ensure that when sorting or searching for a record, you find the correct one.
Examples of primary key fields are telephone numbers, Employee Id, Student Id, Car number plate, National insurance number. All of these are one of a kind pieces of information the will ensure no two records are ever exactly the same.
The primary key field is one that must always be unique i.e. never repeated, this ensures that every single record has something unique that it can be identified by. This helps to ensure that when sorting or searching for a record, you find the correct one.
Examples of primary key fields are telephone numbers, Employee Id, Student Id, Car number plate, National insurance number. All of these are one of a kind pieces of information the will ensure no two records are ever exactly the same.
Example situation:
Imagine in a large school there may be 5 students called James Jones in year 7. If a Councillor was trying contact the parents of one of the James Jones' she would go to the secretary and her to search the database for their phone number. Now if there was no unique primary key to aid this search, the secretary would be given the contact details for 5 students in year 7 called James Jones with no way to determine which is the correct number. Te remedy this you would introduce a field called "Student ID" and each student would be allocated a number that uniquely identifies them.
Imagine in a large school there may be 5 students called James Jones in year 7. If a Councillor was trying contact the parents of one of the James Jones' she would go to the secretary and her to search the database for their phone number. Now if there was no unique primary key to aid this search, the secretary would be given the contact details for 5 students in year 7 called James Jones with no way to determine which is the correct number. Te remedy this you would introduce a field called "Student ID" and each student would be allocated a number that uniquely identifies them.
Validation
Databases, as with most aspects of computing can be a victim of errors. With databases the most common error is the fact that data can be entered incorrectly.
Validation should be used on each field to ensure that data is entered correctly and in the required format.
There are many different validation rules that can be set, these are:
Validation should be used on each field to ensure that data is entered correctly and in the required format.
There are many different validation rules that can be set, these are:
Validation Rule |
Description |
Type Check |
Ensures that the data in a field must be a certain type of data e.g. Date of birth must be the date data type. |
Range Check |
Ensures that data in a field must be a value within a set range e.g. Price should be between $5 and $10. |
Format Check |
Data must be entered in a specific format e.g. The date should be entered as 'dd/mm/yy' not 'dd/mm/yyyy' . Having data entered in the same format makes sorting and searching easier. |
List Check |
Data must be chose from a pre set list of options |
Presence Check |
This ensures that data is present in a field, if the user fails to enter data they will be prompted to do so. |
Querying the Database
One of the main reasons that we use databases is that we can use them to quickly find information. In database terms, this search is called a query.
Query's in databases can be very simple e.g. searching for everybody with the first name "Sam".
They can also be more complicated e.g. searching for everybody with the name "Sam" that also has the job title "Manager" and lives in "London"
Whether its a simple or complicated query, the main advantage of a digital database over a paper version is the the query will be completed a lot faster and more efficiently.
Query's in databases can be very simple e.g. searching for everybody with the first name "Sam".
They can also be more complicated e.g. searching for everybody with the name "Sam" that also has the job title "Manager" and lives in "London"
Whether its a simple or complicated query, the main advantage of a digital database over a paper version is the the query will be completed a lot faster and more efficiently.
Search Operators
To help you write your query correctly there are a few set operators that have different meaning, these are detailed in the table below:
To help you write your query correctly there are a few set operators that have different meaning, these are detailed in the table below:
Value |
Value |
|
< |
Less than |
Age < 10 |
<= |
Less than or equal to |
Age <= 10 |
> |
Greater than |
Height > 105cm |
>= |
Greater than or equal to |
Height >= 105 |
= |
Equal to |
Cost = 100 |
<> |
Between two values |
Height 105<>160 |
These are a comprehensive set of operators which allow you to tailor your search perfectly. It is also possible to combine mote than one search term / operator.
There are two logical operators which allow you do combine search criteria, these are AND and OR
AND = Will return records where all search terms are matched
OR = Will return records where at least one search term is matched
Putting this in to practice
When using Microsoft Access to query a database the user interface will look like this...
There are two logical operators which allow you do combine search criteria, these are AND and OR
AND = Will return records where all search terms are matched
OR = Will return records where at least one search term is matched
Putting this in to practice
When using Microsoft Access to query a database the user interface will look like this...
You must then place your search criteria into the 'Criteria' Row of the correct field.
For example, If you wish to search for all cars cheaper than $5000 your search term would be <5000 and you would place it in the price field, see below:
For example, If you wish to search for all cars cheaper than $5000 your search term would be <5000 and you would place it in the price field, see below:
AND
If you wished to combine two search terms where they mus both be correct you will need to use the AND operator. In Microsoft Access you would achieve this simply by placing another search criteria in a fields criteria box.
So if for example you wanted to search for all ford cars that cost more than $16000, the search term would be:
(Model = 'Ford') AND (Price>'16000')
In access this would look like:
If you wished to combine two search terms where they mus both be correct you will need to use the AND operator. In Microsoft Access you would achieve this simply by placing another search criteria in a fields criteria box.
So if for example you wanted to search for all ford cars that cost more than $16000, the search term would be:
(Model = 'Ford') AND (Price>'16000')
In access this would look like:
OR
If you wished to combine two search terms where only one of them needs to be correct you will need to use the OR operator. In Microsoft Access you would achieve this simply by placing another search criteria in another fields criteria box, however this time you will use the row below labelled 'or'.
So if for example you wanted to search for all Ford or Fiat cars that were less than $10000, the search term would be:
(Model = 'Ford' OR 'Fiat') AND (Price < 10000)
In Access this would look like:
If you wished to combine two search terms where only one of them needs to be correct you will need to use the OR operator. In Microsoft Access you would achieve this simply by placing another search criteria in another fields criteria box, however this time you will use the row below labelled 'or'.
So if for example you wanted to search for all Ford or Fiat cars that were less than $10000, the search term would be:
(Model = 'Ford' OR 'Fiat') AND (Price < 10000)
In Access this would look like:
Exam Tips:
In the exam you may be asked to write a search criteria for a required situation e.g.
search for all Ford or Fiat cars that were less than $10000
For this the answer will look like this - (Model = 'Ford' OR 'Fiat') AND (Price < 10000)
Be careful with your placement of brackets and choice of AND v OR
You may also be provided with a table that looks like the Microsoft Access query UI as seen above. Here you will be given a table to refer to and a query to set up.
Key points.
In the exam you may be asked to write a search criteria for a required situation e.g.
search for all Ford or Fiat cars that were less than $10000
For this the answer will look like this - (Model = 'Ford' OR 'Fiat') AND (Price < 10000)
Be careful with your placement of brackets and choice of AND v OR
You may also be provided with a table that looks like the Microsoft Access query UI as seen above. Here you will be given a table to refer to and a query to set up.
Key points.
- Read the question carefully as you man not be required to include every field.
- If you are required to search using a specific field but NOT required to show it, you must not tick the "Show" box.