Hardware & Software Shop Database

In this exercise you will use the information that you found in the Spreadsheet exercise, building PCs, to create a database of the shop items. You wil use Miscrosoft Access.

By the end of this lesson you will be able to:

• Define the terms Database, Record and Field
• Define the following data types: Text (Alphanumeric), Integer, Real, Date, Currency, Boolean, Attachment
• Select an appropriate data type for a given field
• Understand the need to create a data structure that uses less memory
• Estimate the length of a field
• Select an appropriate validation rule for a given field
• Create a table in a database with an appropriate data structure for the given data

When selecting the length of a text field you should refer to your spreadsheet tio check the length of the longest item.

Key terms:

database, data dictionary, field, record, table, data type, number, integer, single, currency, text/alphanumeric, Boolean, key, primary key

Task: Create a Database

Create a database definition in Access. You wil need these fields (ID, item name, item code, item manufacturer, item type, item price, number in stock, picture). You often take ID as the primary key but in this case the item code will be unique and will do the job. The primary key of a table is a unique identifier for each record.

Create a Form

When you are sure that the database is complete you can create a form - use Create/Form. This is very quick. Note that the form only accepts data after it has been saved - close the form (Ctrl-W), save it and then re-open it.

You can now add data items. You could import from your spreadsheet but it is safer to copy and paste the items. To add a picture you must:

Find a suitable image from the internet and save it in a folder such as My Pictures

In the Access form click on the paper clip floating over the Picture field and then use the dialogue box to select the image you want - it should appear in the box. Don't worry about the size of the image in the box: it doesn't matter for this part of the exercise.

Searches and Database Queries

Starter

Imagine a customer walks into the shop with a specific issue. Complete the sentences below; each of which should reflect a realistic query a customer may have.

Find all CPUs made by _______

Have you got any processors for under £__

Have you got any _________ made by _____

How much do your _______ cost?

What is the average cost of a ____________?

What is the cheapest _________ that you stock?

These questions are answered by searches or queries on the database.

Task: compose three simple searches of your own before you run them on your database.

Find

Click on the column you want to search in and then click on the binoculars.

Filter

Click on the column you want to search in and then click on the Filter button. Click on the Text/Numbers filter button and choose the relation you want to use:

Numbers:

• Equals
• Not equal
• Less than
• Greater than
• Between

Text:

• Equals
• Does not equal
• Begins with
• Does not begin with
• Contains
• Does not contain
• End with
• Does not end with

When you complete the filter only records that meet your criteria will be visible. To restore the hidden records click the Filter button.

Queries

To construct a query click on Create/Query Design. Add the table from the list and click Close. Drag the fields from the field list into the grid and enter criteria such as "Manufacturer = Intel" (under CPU) or "<100" for price. Save each query as you complete them.

To make an OR or AND query enter more than one criterion in a column ("Intel OR AMD") or add criteria to more than one column ("Price < 100 AND Manufacturer = Intel").

Task: Compose two OR and two AND queries of your own and run them on the database. Save the completed queries.