About databases

A database is used to store, organise, categorise, search, and present data in a specified format.  Databases are extremely versatile and can be used for many information based applications such as: address book / contact database stock inventory tracking work activities and possibly project management personnel database knowledge management system financial data transactions and accounting marketing and sales database

A database consists of the following key components: tables to hold the data / information queries to filter and sort the data to your criteria forms for user friendly input, editing and viewing of data reports to summarise the data based on your criteria A database may consist of multiple tables, queries, forms and reports. Where more than one table exists relationships are set up between tables to indicate how records in different tables are related.

A database table is used to store data in a structured format.  Tables can be visualised as a grid of columns and rows.  Each row represents one record in the database.  Within each record their are fields (the columns in this analogy) which contain particular values that are the same type as the field type.  See table example below.

Fields types usually consist of the following: number (e.g. 12345) date / time (e.g. 04 May 2000 19:05:01) text - a string of characters up to a defined maximum length (e.g. This is my text string) boolean value (e.g. true or false, or yes or no) memo - a block of text of any size, but it doesn't support many search or ordering features, unlike the text type

Type size and format can also be specified for some field types, as illustrated below: number can be an integer (e.g. 123) or a floating point number (e.g. 2.34); a long integer will represent values from approximately - 2 billion to +2 billion, and a double will represent a huge range of floating point values date / time formats can include things such as 04 May 2000, 04-05-00, 19:05:01 and 07:05 text strings have a defined length which has to be set large enough to hold the longest string of text you anticipate in that field; if you intend to search for text in a particular field then you can make it an indexed field (which speeds up the search process though it takes up more space)

Field examples are shown below.  Each field has a type and a name by which it is referred in the database.   Field Name Field Type Number in Stock number Unit Cost number Date Delivered date/time Date of Expiry date/time Part Code text Part Description text Fragile boolean (yes/no) Handling Instructions memo

In the above example the two number fields would be integer (possibly long integer).  Though if you wanted the Unit Cost represented as pounds instead of pence the field type would be a floating point type (single or double). For dates it is good practice to select a long date format that spells out the month and uses 4 digits for the year (e.g. 04 Feb 2000). Part Code would probably be indexed to allow quick searches on this field.

Here is a table example based on the above field examples.   Number in Stock Unit Cost Date Delivered Date of Expiry Part Code Part Description Fragile Handling Instructions 98 54 2 Feb 2000 6 Jun 2001 PX-45 widget socket sleeve No mount over widget socket with google fixer 732 85 4 Jan 2000 10 Feb 2001 PD-22 widget socket Yes be careful not to damage alignment tags 45 36 3 Apr 2000 17 Nov 2000 PX-25 fixer tappit 15mm No use align tool 4 89 9 Dec 1999 30 Jun 2000 PH-4 special fixer tappit 20mm No use align tool Note that an actual database package would probably display each record on one line only.

A query is used to organise and show data in a specified way.  A query is simply a view of the contents of a table - though records shown in a query can be modified too.  It may show all the records in a table or only those that match your criteria. Queries can filter the data in a table to show only those records that match your criteria.  For example: show only records that start with "PX" in their Part Code show only records where Number In Stock is less than 10 show records where the Expiry Date has passed show all Fragile parts show all parts that start "PX" and are Fragile Queries can also sort the records they show by your criteria.  For example: sort records by Part Code (in ascending order) sort records by Unit Cost (in descending order: expensive 1st) sort by Part Code and Expiry Date

A form is used to view, edit, add and delete data.  A form is based on the contents of a table or query, or a combination of tables and queries. A form shows the fields of the current record in a nicely laid out format and should be easy for the computer operator to use.

Form example   Number In Stock: 98 Part Code: PX-45 Part Description: widget socket sleeve Fragile: no Handling Instructions: mount over widget socket with google fixer |<< < [1] > >>|  *

Record navigation At the bottom of the form there may (depending on your database package) be buttons to navigate between the records.  Such buttons may include rewind to the 1st record, go to previous or next record, and go to the last record.  You may also be able to type in the record number you want to jump to.  There may also be a button to create a new record.

Field navigation To move the cursor between fields on the form you can use the tab key.  So for example, on the above form you would enter the Number In Stock value then press the tab key and enter the Part Code, etc.  To move back one field you can hold down the shift key and press tab (back-tab).

Search / Filter Within a form you may also have the option to filter the records so that the form only shows records that meet your criteria (this is similar to queries).  For example, you may only want to show all records that have less than 10 items in stock. There may also be an option to search for a record that contains a specific value in one or any of its fields.  For example, you may want to find a record that contains "PX-45" in the Part Code field. If these features are supported in your database look for them in the menu bar or on the toolbar.  Tip: if you leave your cursor over a toolbar button it will usually tell you what the button's function is.  Remember that there is also the help feature (if things get really bad).

A report is used to show data (typically from a group of records) in a specified way and is suitably formatted for printing.  Reports can be generated to provide effective management information on virtually all aspects of a business (provided the data exists in the database of course). A report is simply a view of the contents of a table or query, or a combination of tables and queries.  It may show all the records in a table/query or only those that match your criteria.  Similarly, you can show all fields or just a few fields. Reports can also be used to group data into categories and to provide summaries.  Summary functions may include totals, minimum and maximum values, and averages. For example, you may want a report that lists all records grouped by their Fragile status (e.g. all fragile items shown first), and sorted in order of their Expiry Date, and have separate totals calculated for the number of fragile and non-fragile items in stock, along with the total of all items in stock.

Example   Part Number Part Description Expiry Date No. In Stock ___Fragile: Yes PX-998 flange joint 25mm 2 Feb 2000 5 PX-001 pivot lobe X type 4 Apr 2000 10 sub-total 15 ___Fragile: No PY-123 widget cone Y 3 Mar 2000 1 PN-090 zoom flange 25mm 6 Sep 2000 53 Z-457 triangulation flange 10 Nov 2000 7 sub-total 61 grand-total 76

Graphics Reports may (depending on your database package) also be capable of producing graphical representations of numerical data, portrayed as a bar chart, line graph or pie chart.

Export In addition to being printed out, reports can also be exported to other file formats and other programs.  So you may, for example, export the report into a word-processor or into a spreadsheet.  (Note that you can also export the raw data in tables and queries.)

Relationships If you are interested in more advanced databases that do have more than one table read this section.  If not then skip it. When a database contains more than one table the tables usually have a relationship between them.  Relationships are used to indicate which record in one table is related to another record in another table.  To do this the related records need to have the same value in one of their fields and these two fields are used to define the relationship. For example, imagine a Parts (in stock) table and a Suppliers table.  One of the fields in the Parts table may be the Part_Code.  If there is also a field called P_Code in the Suppliers table that holds the part code (using the exact same coding system) then we can use these two fields to define a relationship between the two tables (Parts and Suppliers).  Once we have told the database that these two fields are related it knows that any records (in the two tables) that have the same value in these fields are related. Parts.Part_Code is related to Suppliers.P_Code So whenever you make reference to a field from the Parts table and a reference to another field in the Suppliers table the database knows how to find the corresponding (related) records in both tables.  In other words it will be records that contain the same value in the fields Part_Code and P_Code.

Example Which records are related in the two tables below? Table: Parts Record No. Part_Code Part_Description No_In_Stock 3 PA-010 widgety fixer 10mm 6 4 PX-44 do-dah 25 mm 5 5 PX-56 din-do-dah 30mm 6 Table: Suppliers Record No. Supplier P_Code Days_To_Delivery 4 Alan & Joe's Z-006 56 5 Imperial Co. PX-60 14 6 M&D Ltd. PX-44 30 7 SNE Fjord PT-17 25 Remember that the table relationship has been defined between the fields Part_Code and P_Code.  Matching values must exist in these fields for records to be related. So, which records are related in the two tables? (and for the corresponding part, how many are in stock and how long will it take for more to be delivered?)

One to many relationships In the above example it is possible that more than one supplier can provide a given part.  So for example if we have Part_Code "PX-45" in stock and in the table Parts it may be that two different suppliers can deliver that part.  Therefore, in the Suppliers table their would be two records that had a value of "PX-45" in their P_Code field. So in this example, rather than one record in the Parts table matching just one record in the Suppliers table it matches two.  This is called a one to many relationship. Here's how this would probably be set up in our example database.  The Part_Code field would be an indexed field in our Parts table (and probably the primary key - meaning records are sorted based on the values in this field).  The P_Code field in the Suppliers table would also be indexed (but probably not a primary key).  The database would then be told that the Part_Code field is on the "one" side of the relationship and the P_code field is on the "many" side of the relationship.  Thus a one-to-many relationship is defined.