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 |
Database
Populating the tables
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.
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).
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.
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.
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.
Information from most databases can be viewed (displayed) on screen or on a report in two different formats:
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 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.
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.
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.
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
