Database - How To Movies
 
1. Setting up and creating tables and fields 2. Formatting Fields. 3. Creating Primary Key and Relationships 4. Searching and Creating Reports 5. Editing Reports
 
Home DatabaseUsing Information Internet NAB/Coursework Revision Legal Responsibilities

Database

Populating the tables

Input

Input is the word used to describe the entering of data into a computer.  When a database is created, data is required to be input into the database.


Entering data into the tables is known as ‘populating’ the tables. The most important aspect of this stage is ensuring the data is entered accurately.


The validation settings in the data dictionary help to ensure that data is valid, or sensible. For example, required fields will all have values (presence check), number values will be in the range specified (range check), while some values may be selected from a list of choices (restricted choice check).


However, there is no guarantee that even valid data is correct! You must make sure that the data entered is checked to see that it is correct. This is called verification.


Most occurrences of incorrect data in databases are due to human error, usually as a result of mistakes in inputting data. Some ways of ensuring that data is correct include:

Manipulating the data

The real value of a database lies in what you can do with the data once it has been entered and stored. Organisations store vast amounts of data in databases, and a well-designed database can produce valuable information that a human operator would find difficult or impossible to produce.

Databases are good at performing four main tasks:

Searching records

Querying is sometimes known as ‘searching’ or ‘finding’.

The output from a search or find is the records which contain the information specified for the search.  The number of records found can be as low as zero, or as high as the number of records in the database file.  For example if a customer asked a car salesman “What Rover cars do you have in stock?”, then using a computerised database, a query could be set up to search every record for the word “Rover”, and the records that had the word “Rover” in the make field would be presented on screen.

The query sentence created would be similar to:

Search the field Make to contain the word Rover.

These records could then be viewed.  This is known as a simple search, as the query uses one field.

Searching is the process of selecting records from a table or combination of tables. To perform the search, three items must be identified.

  1. Which fields will be used to identify the records required?
  2. What are the search conditions for identifying the records required?
  3. Which fields will be displayed?

Search conditions contain Boolean operators. These are shown in the table below:

Operator Meaning Example Matches
=
equal to Age = 16 Surname = 'Smith' People aged 16 People called Smith
<>
not equal to Height < > 1.70 Certificate < > ‘PG’ People smaller or taller than 1.7m Films which are not PG certificate
>
greater than or after Age > 17 Surname > 'N' Date of Birth > 01/05/1952 People older than 17 Surnames in the second half of the alphabet (starting N-Z) People born after 1 May 1952
<
less than or before Height < 1.9 Surname < 'N' Date of Birth < 31/06/1990 People shorter than 1.9m Surnames in the first half of the alphabet (beginning A-M). People born before 31 June 1990
>=
greater than or equal to or after and including Age >= 17 Postcode >= 'EH30' Date of Birth >= 01/05/1952 People aged 17 or older Postcodes beginning EH30 or greater People born on or after 1 May 1952
<=
less than or equal to or before and including Height <= 1.95 Postcode <= 'EH20' Date of Birth <= 30/06/1990 People 1.9m or less in height Postcodes before EH20 (beginning EH1 to EH19) People born on or before 30 June 1990

A complex search involves more than one search condition (and usually more than one field).

Using boolean operators AND, OR and NOT

When two or more fields are queried at the same time this is known as a compound (or complex) search.

If the customer asked “What Rover cars do you have for sale with under 40,000 miles on the ‘clock’?”, a query would need to be set up involving two fields - Make and Mileage.  The field Make would need to be searched for the word “Rover”, AND the field Mileage would need to be searched for those less than 40,000 miles.  The records which would result from this search would be those which were both Rover and had less than 40,000 miles on the ‘clock’. 

The query sentence created would be similar to:

Search the field Make to contain the word Rover AND the field Mileage to be less than 40000.

Another boolean operator OR can also be used to search for data.  For example if all cars priced more than £20,000 OR a Jaguar are required, then the query sentence would be made up as follows:

Search the field Price to be greater than 20,000 OR the field make to contain the word Jaguar.

The character used for greater than is >.

This search would produce the records of all cars over 20,000 (including makes other than Jaguar) and all Jaguars (even if they are costing less than £20,000).

Another boolean operator NOT can be used.  For example if all cars priced over £22,000, but not Mercedes cars were required, then the NOT operator would be required.  The query sentence would be made up as follows:

Search the field Price to be greater than 22,000 NOT including the field Make to contain the word Mercedes.

Reporting

Imagine a database file of cars for sale.  The fieldnames may be as follows:

Make     Model     Registration     Mileage     Price     Price Paid     Colour   

This report is a printout in column format, as the data is displayed as concisely as possible without unnecessarily repeating fieldnames for each record.  A report may contain all fields, or only specific fields. 

It is also possible to print a report containing only the fields chosen by the database user - for example a report containing only the fields Make, Model, Registration and Price Paid could be printed for management, but on a report that a customer may see, it would be important not to include the field Price Paid as the customer may feel as though they were being overcharged if the Price Paid by the garage for the car was much lower than the car was priced for sale at!

Sorting records

Computers can very quickly sort large amounts of information into order.  For example a database of cars for sale in a garage could be sorted into alphabetical order of make of car; or into numerical order of price.  This would make it easy for a sales person to look up a particular make of car for a customer who requested for example a ‘Ford’ or ‘Rover’ car. 

If the file was sorted into numerical order of price, then a sales person could quickly look up cars within a certain price range on a report (printout) for a customer.  The output of a sorting operation is the whole file in a specified order.


It is often useful to arrange the information in the database in some kind of order. A sorting operation is performed to achieve this. To perform a sort, two items must be identified.

  1. Which field will be used to decide the order of records? This is called the sort key.
  2. For the sort key, will the order of sorting be ascending or descending?

For example:

A very common way of ordering records relating to people is in alphabetical order. To achieve alphabetical ordering requires the records to be sorted in ascending order of surname.

A complex sort involves more than one sort condition involving two or more fields. The main sort key is called the primary sort key, and the second one is called the secondary sort key.

Viewing

Information from most databases can be viewed (displayed) on screen or on a report in two different formats:

Record or Page format is viewing a record in the same format as the data is entered.  Sometimes there will be one record on each page, sometimes more.  This will depend on how many fields are in the record.

Column format is viewing many records at once, in columns, with the fieldnames at the top of each column.  Here is an example of an extract of a database file displayed in column format:

Surname First Name Address 1 Town Postcode
Bayne Andrew 12 Hill Terrace Aberdeen AB3 4RF
Campbell christine 21 Park Road Portlethen AB4 3DD
Davidson Gavin 8 Laurel Drive Aberdeen AB2 6YU
Frame Carol 21 Laurel Road berdeen AB2 7YH
Francis James 44 Hillside Portlethen AB4 9YR

Navigating

Navigating means finding your way around a database system.  How the software allows you to move around and see other records is very important.  Some simply let you move up and down pages until you come to the record you want; others provide ways of jumping to any record or going immediately to the top or bottom of the datafile.

Editing

In a specialised database such as the database of cars for sale, it is important to be able to edit (change) data, for example if the garage reduces the price of a car, then that car’s price field then needs to be updated with the different price.

Customising

Most software packages will allow you to customise the way you use it.  For example, many applications have toolbars positioned somewhere on the screen, which provide a faster way of editing data, rather than finding your way through menus.  These toolbars could be customised to only have certain buttons (options) on them, or they could be displayed in a different position on the screen, depending on the user’s preferences.

Automating

The options available to a software user are vast, and only the most popular options will be available on a toolbar.  Therefore, to use other features, it may be necessary to find your way through menus to get the required feature.  It would be desirable to make the process of carrying out the instruction faster.  This can often be done by using ‘hot keys’, where a specific action such as selecting all the text in a document can be done by simply holding down the Control Key and pressing the A key.  Data entry may be partially automated using pop-up menus, where the user only needs to click on the appropriate data for it to be entered in the field.  Macros may be used to automate by combining a number of actions together into one keystroke.

Formatting

Most software packages will allow you to format data to a style of your choice, for example applying £, $ or % signs to numbers.  Data can also be aligned (positioned) to the left, right or centre of a document / column, and in the case of text, fully aligned - with a straight margin down both sides. 

Download and complete Worksheet 3


 

 



 

Intermediate II Home
Database | Using Information | Internet | NAB/Coursework| Revision
Log into Glow | School Website | Contact Us