Year 9 Databases

DATABASES

WHAT IS A DATABASE?

A database is an organised collection of information (or data) on a particular subject (books, customers, products etc). Putting information into a computer database gives you far more flexibility in arranging, displaying and printing that information. A database is organised to make retrieving specific informationeasy.

The parts of a database:

Files: Think of a box of filing cards like those sometimes used in libraries. A computers equivalent of the complete box is a file. A collection of related data; for example, a student file would contain details of all students in a school.

Records: Within the library card box are the individual cards, each of which contains information about one book. In a computer database, the cards are known as records and most databases will display one record on the screen at a time, just as if you were browsing through the cards in a card box. A set of related information about a thing or individual.

Fields:

On each card will be a number of different items of information: a library card might list the title, author, and publisher. Each item of information on the computer record is known as a field, and it consists of two parts: the field name and the field data. The field names are Title Author or Publisher. They are the same on each record and indicate what the field data represents.

Microsoft Access Database Manager:

Access is a software application used to create and manage a computerized database. In Access, a data base consists of the following objects:

  • A table is a database object that stores data in rows (records) and columns (fields). The data is usually about a particular subject, such as employees or sales.

CREATING A NEW DATABASE

  • Select Microsoft Access
  • Select Blank Database
  • In the File name box, type the name of your database.
  • Select Create

CREATING A TABLE

  • Select Create table in Design view.
  • Complete field name, data type, and field size.

To save a Table:

  • Select File from the Main Menu
  • Select Close
  • Write the name of the table.
  • Select O.K

VALIDATION:

Validation is the process of detecting any data that is, incomplete or unreasonable. A computer can be programmed to validate the data entered; if the data supplied is invalid the program can display a message on screen asking for a correct data entry.

To validate fields in an existing TABLE:

  • Select the table
  • Click Design
  • Click on the field to validate
  • Click on validation rule in field properties
  • Complete the validation rule, you may go to the expression builder byclicking on
  • Click on validation text
  • Complete the error message for an invalid entry

HOW TO DEFINE A PRIMARY KEY:

A primary key is a field that uniquely identifies each record.

HOW TO CREATE A LOOKUP FIELD (combo box):

There are 2 ways to do this:

1)Using an additional table

Create an additional table containing the possible values (E.g.: in the school’s database the House field values would be Fleming, Dodds, Brown or Monteith).

In the Design View of the Main table, for the field that uses the combo box (e.g.: House), the Data Type must be Lookup Wizard….

Follow the steps of the wizard

Choose the 1st option to look up the values in the additional table

2)Create the list of values directly in the wizard

In the Design View of the Main table, for the field that uses the combo box (eg: House), the Data Type must be Lookup Wizard….

Follow the steps of the wizard

Choose the 2nd option to type in the values that you want

HOW TO CREATE A FORM

In this way you can see all the fields of a table arranged more like the printed tracking sheet than like a spreadsheet.

  • Click the Forms button in the left menu
  • Choose the Create form by using wizard
  • Complete the dialogue box.

HOW TO MAKE A QUERY

A query is used every time you want to interrogate or search a database or table.

  • Select the QUERIES button in the left menu
  • Select the table or tables you wish to interrogate and click Add.
  • Select Close.
  • Complete the table below in the following boxes: Field, Sort, Show & Criteria.
  • Select Query / Run or

How to specify different criteria for a query:

  • The value in a field is equal to a specific value: Example: The following query will return the records for the Author “Charles Dickens”

  • The value in a field starts with a set value: Example: Books whose Title start with C.
  • The value in a field falls within a range of values (only numeric fields):

Complete the criteria for Books edited between the years 1930 and 1950.

  • The value in a field may be from a list of values:

Complete the criteria for Books that are “novel” or “short stories”.

  • Variable criteria:

Complete the criteria for Books that are a specific genre set when the Query is run.

HOW TO MAKE A REPORT

With a Report you have greater flexibility to present summary information. You can include totals. It is important to group and sort information. Create Reports using the wizard and read carefully each step.

HOW TO INSERT A PICTURE IN A DATABASE
  • Add a field name Picture and define it as OLE Object
Open the Table and in the column picture click the right button of the mouse
  • Select Insert /Object
HOW TO STRETCH A PICTURE IN A FORM OR REPORT:
  • Open the design view of the form or report
  • Click the right button of the mouse where the picture should appear
  • Select Properties

Change the size mode from clip to stretch

HOW TO ADD A HEADER & FOOTER IN A FORM:
  • Open the design view of the form
  • Drag the Detail Tab down from the Form Header thus creating a space for the header.
  • Select the Toolbox Label icon Aa and place it on the Form Header
  • Type in the title and format it as you like.
  • Apply the same steps for the footer.

Ways to perform calculations in a query

There are many types of calculations you can perform in a query. For example, you can calculate the sum or average of the values in one field, multiply the values in two fields, or calculate the date three months from the current date.

To display the results of a calculation in a field, you can use a predefined calculation that Microsoft Access provides or custom calculations you define.

Use the predefined calculations, called "totals," if you want to compute the following amounts for groups of records or for all the records combined: sum, average, count, minimum, maximum, standard deviation, or variance. You choose one totals calculation for each field you want to calculate.

Example: Calculations on all records:

To do this open Exercise 1 (Software packages). Create a new query in design view for the table software packages.

4For the field you want to calculate, Type add it to the next column and in the Totals row select the function count.

5Run the query, your output should be as follows:

Type / CountOfType
Desktop Publishing / 2
Graphics / 2
Word Processing / 2

Using a calculated field:

For custom calculations, you need to create a new calculated field directly in the design grid. You create a calculated field by entering an expression into an empty Field cell in the query design grid.

Steps To create a calculated field:

  1. In the design grid you give the calculated field a name followed by a colon.
  2. You click on the expression builder icon.
  3. In the expression builder you build the formula necessary to calculate the value for the desired field:

To calculate the value in stock for each product of the Software packages create a calculated field in a query where the value is obtained multiplying price by stock.

WHAT IS A RELATIONAL DATABASE?

A Relational Database is a Database that consists of several files, which are connected, in some way. For instance, in a video library there could be three:

  • A Video file, to hold details of the videos kept, such as video title, actors, duration.
  • A Member file to hold all the members’ details such as name, address.
  • A Rental file to hold all the details of the rentals, such as video number, member’s number and date borrowed)

All three files (or databases) are related, but you need at least onecommon field in all three files to be able to work satisfactorily.A relational database management system (RDMS) is a program that enables you to organize data stored in tables in a database.

Page 1