Use a database to meet organisational needs
Uses for databases 2
Design a database 3
Database tables 3
Designing your tables 3
A flat-file database 4
A relational database 4
Principles for designing a database 5
Create a database 7
Creating tables 7
Editing, adding and deleting data 8
Sort, filter and query data 9
Sort data 9
Filter data 9
Query data 10
Field properties 11
Field size 11
Format 11
Use queries 13
Use wizards to create forms, reports and queries 14
Use Help resources 15
Summary 16
Check your progress 16
Uses for databases
A database is like an electronic filing system. Databases are widely and commonly employed within businesses and organisations for storing, organising, searching, retrieving and displaying data (or information). Some examples of databases include:
· library databases that store millions of entries and access citations from hundreds of publications
· airline reservation systems that can be accessed simultaneously from locations all over the world to place passengers on hundreds of flights on different dates
· banking systems that keep account of millions of financial transactions
· Web search engines like Google that index and record billions of web pages and make them accessible within seconds (at the last count they claim 4,285,199,774 web pages)
· hospital medical systems that keep track of thousands of patient records
· business inventory systems that range from a product catalogue of a small online store to a company like Boeing that keeps track of millions of aircraft components.
Today there are three main types of databases. The dominant model today is the relational database, which was originally proposed and developed by researchers at IBM in the late 1960s and early 1970s.
There are many relational database management packages available for a range of operating systems. Packages include Microsoft Access, Paradox, Oracle, DB2 and SQL Server. The most common relational database management package used in the home and small business is Microsoft Access. However, you should be aware that:
· Microsoft Access is not an industrial strength system that, say, Google would use to index 4 billion web pages. Nevertheless it is quite adequate for smaller tasks.
· Many other database applications (some of them free) are available for other operating systems on Macintosh, Unix and Linux platforms.
Design a database
Database tables
Within a database file, a table is used is a way of grouping (or storing) related data. Within a table, data is organised into fields (columns) and records (rows). Tables store information in a database similar to the way a spreadsheet stores information. Many tables can be stored in a single database file.
When creating a database, you enter the data into a table (or tables). Once data is stored in the tables, you can use the database functions to search, sort, select, display, print, etc, the data you have stored. However, these functions work in relation to the table structure you set up. So it is critical that your table design (how many tables there are, and the fields and records there are in each table) is right for the functions you want to perform and the results you want to get.
So the first step in designing a database is deciding how to sort your data into separate ‘storage containers’ which will be the tables.
Basic functions of a database
In the finished database, the data in a table can be viewed, added to or updated using a database form.
Specific data can be searched for and selected according to specific criteria by performing a query.
The data in tables can be displayed, formatted and printed in customised ways by creating reports.
Designing your tables
Determining the tables you require can be the trickiest step in the database design process. That’s because the results you want from your database, the reports you want to print, the forms you want to use, and the questions you want answered, don’t necessarily provide clues about the structure of the tables that produce them.
Newcomers to database design are often tempted to lump all their data together in one table. This is rarely a good idea unless the information you want to store is simple and relates to the same subject.
A flat-file database
A flat file database refers to a database consisting of a single table. A flat file database is used when the information in each field is all related to one thing or entity. For example, it makes sense to build a flat-file database if you only want to store people’s contact details – their names, email addresses, postal addresses and telephone numbers. This is because the information is all about the same subject, that is, people’s contact details.
A relational database
A relational database consists of several tables; each table consisting of a different type of information or subject. By having one of the fields common to more than one table we are able to relate information in one table to information in another table. Hence, the name relational database.
For example, a retailer may use a relational database that consists of two tables named: ‘Products’ and ‘Suppliers’.
The ‘Products’ table could include a record about each product in stock – product name, description, number of units in stock, price and a number to identify the supplier of that product.
The ‘Suppliers’ table could include a record about each of the suppliers of the products – a unique number to identify them, their name, address and telephone.
Figure 1: A database consisting of two tables: Products and Suppliers. Notice that both tables have a SupplierID field.
In the above example, there is one field that is common to both tables: the suppliers’ identification number. This field is named the same in both tables as ‘SupplierID’. Therefore, the suppliers’ identity represents a link between these two tables; as the suppliers are what relate the two tables. This is useful for the retailer who needs to look up and contact a supplier when they see that they are low in stock of a particular product.
Note that each piece of information must be entered and stored in only one table, so it only needs to be updated in one place. This is more efficient, and it also eliminates the possibility of duplicate entries that contain different information. For example, for tables relating to the retailer’s customers, you would want to store each customer’s address and telephone number only once, in one table. You could store customer orders in a different table, so that you could delete one order and still maintain the customer’s contact details. At any one time the retailer can use the database to retrieve and view all the information about one customer – their contact details and the orders they have placed.
Principles for designing a database
Get familiar with these broad principles for designing a database before you go on to practise in detail the steps of creating a database. You should return to these principles throughout the planning, development and fine tuning stages of developing a database.
The broad steps in designing a database for a client are:
1 Determine the purpose of your database. You do this by talking to people who will use the database. Brainstorm the questions you and they would like the database to answer. Sketch out the reports you’d like it to produce. Gather the forms they currently use to record their data. Research well-designed databases similar to the one you are designing.
2 Work out the tables you need in your database. This can be done with pencil and paper and refined later.
3 Determine the names of the fields you need in each table. Each field name should be as descriptive (but succinct) as possible. For example, rather than Name use First Name and/or Last Name.
4 Identify the field or fields with unique values in each record. Each record must be able to be identified as different from every other record. The unique field/s is/are called the primary key.
5 Determine relationships between tables. Now that you’ve divided your information into tables and identified primary key fields, you need a way to tell your database how to bring related information back together again in meaningful ways. To do this, you define relationships between tables in a database.
6 Refine your design. After you have designed the tables, fields, and relationships you need, it’s time to study the design and detect any flaws that might remain. It is easier to change your database design now than it will be after you have filled the tables with data.
7 Enter the data. When you are satisfied that the table structures meet the design principles described here, then it’s time to go ahead and add all your existing data to the tables. You can then create any queries, forms, reports, data access pages, macros, and modules that you may want.
Create a database
The process of creating a database is rather specific to your database package. Regardless of which package you are using you need to have a design first. This means you need to have worked out in advance what tables you need, what fields are in each table and what types of data you intend for each field.
For more information and steps on using your database package to create a basic database, you can download tutorials and go to websites listed under Research in this Learning pack. You should also use the built-in Help resources in your package to guide you through the detailed steps.
For example, Microsoft Access provides two methods to create a database:
1 The Database Wizard allows you to create (in one operation) the required tables, forms, and reports for the type of database you choose. This is the easiest way to start creating your database. You can modify and extend your database at any time after it has been created.
2 You can create a blank database and then add the tables, forms, reports, and other objects later. This is the most flexible method, but it requires you to define each database element separately.
Creating tables
You can create tables independently of setting up an entire database. You may also need to add tables to an existing database, as your data and needs grow and change.
For more information and steps on using your database package to create tables, you can download tutorials and go to websites listed under Research in this Learning pack. You should also use the built-in Help resources in your package to guide you through the detailed steps.
For example, in Microsoft Access there are three ways to create a table:
1 Create a table by entering data in a datasheet.
2 Create a table from scratch.
3 Create a new table from existing data.
Editing, adding and deleting data
You can also modify the data within your database tables in order to fine tune and update data.
For more information and steps on using your database package to edit, add and delete data, you can download tutorials and go to websites listed under Research in this Learning Pack. You should also use the built-in Help resources in your package to guide you through the detailed steps.
Sort, filter and query data
Sorting, filtering and performing a query allow you to view records in a table in a different way either by reordering all of the records in a table or viewing only those records in a table that meet certain criteria that you specify.
Sort data
When sorting we usually pick one field as the basis of the sort. If the field is a text field then a sort in alphabetical order or reverse alphabetical order makes sense.
If we have some sort of numeric field, then some type of numeric sort is most useful, eg highest to lowest or earlier to later (dates and times).
In either case we want to obtain information that is ordered in some sort of logical sequence.
Filter data
Filtering is a computer term for applying some sort of condition/s to the information that is to be retrieved or displayed. For example, suppose we had a table of employees and the table included a date of hire field. We might be interested in a query that listed all employees with 20-year service to the company on a certain date so that they could be issued with a gift from the company. In terms of the company database, this involves comparing the hireDate with the currentDate. One way of stating this is:
if the (hireDate + 20 >= currentDate) display the record
Applying a condition or set of conditions in this way is called filtering.
As with many of the database operations, the precise way this is done depends on your database package.
Query data
You can use queries to view, change, and analyse data in different ways.
The most common type of query is a select query. A select query retrieves data from one or more tables by using criteria you specify, and then displays it in the order you want.
More help and information
For more information and steps on sorting, filtering and performing a query, you can download tutorials and go to websites listed under Research in this Learning pack. You should also use the built-in Help resources in your package to guide you through the detailed steps.
Field properties
Each field of a table has property settings that you can change to alter the way the field looks and behaves. The properties available for a field are dependent on the field’s data type (eg numbers, dates and times).