255

10.1 Database basics

10 Database and file concepts

In this chapter you will learn about:

+ flat file and relational databases

+ relationshiptypesinarelationaldatabase:

one-to-one, one-to-many and many-to-many

+ entity relationship diagrams

+ key fields

+ referential integrity

+ the characteristics of data in unnormalised

form

+ the normalisation of data into First, Second

and Third Normal Form

+ thecomponentsofadatadictionary

+ query selection using static and dynamic

parameters

+ differentfiletypesandtheiruse

+ proprietary,genericandopensourcefile

formats

+ indexed sequential access

+ direct file access

+ hierarchical database management system

+ management information systems.

In this chapter you will learn how to:

+ create a relational database

+ assign appropriate data types and field sizes

+ createanduserelationships

+ assign key fields

+ use referential integrity

+ validateandverifydataentry

+ perform searches

+ perform calculations within a database

+ sort data

+ design and create an appropriate data entry

form

+ design and create a switchboard menu within

a database

+ importandexportdata

+ normalise a database to First, Second or

Third Normal Form

+ createadatadictionaryandselect

appropriate data types.

Before starting this chapter you should:

+ understandtheterms‘file’,‘record’and

‘field’

+ be able to create a flat-file database

+ have studied validation and verification

(Section 1.4)

+ have studied testing a spreadsheet (Section 8.2)

+ have studied the IF function in a spreadsheet

(Section 8.1)

+ have studied custom-written and off-the-

shelf software (Section 2.4).

For this chapter you will need these source files:

Q boat.png

Q ellmau.csv

Q FA-Cust1.csv

Q FA-Cust2.txt

Q FA-Invoice.csv

Q FA-Product.csv

Q freezer.png

Q jobs.csv

Q NP-Boat.csv

Q NP-Customer.csv

Q TB-Driver1.csv

Q TB-Driver2.csv

Q workers.csv

10.1 Database basics

A database is a program used to store data in a structured way. This includes the

data that is stored and the links between the data items. All databases store data

using a system of

files, records and fields. There are two types of database: a

flat-file database and a relational database.

256

10 DATABASE AND FILE CONCEPTS

10.1.1 Database types

Flat-file databases

A flat-file database stores its data in one table, which is organised using rows

and columns. For example in this database about teachers, each record (row)

in the table contains data about one person. Each column in the table contains

a field which has been given a field name and each cell in that column has the

same, predefined data type.

Teacher_ID Forename Surname Subject Room

AVA Anthony Varela Maths 51

GBA Graham Barney Science 14

JKW Jennie Kwong English 42

PTY Paul Tyrell Science 13

SJR Sarah Jordan English 43

Fields

Field name

Records

V Figure 10.1 A flat-file database table

Relational databases

A relational database stores the data in more than one linked table, within the

file. The relational database is designed so that the same data is not stored many

times. Each table within a relational database will have a key field. Most tables

will have a primary key field which holds unique data (no two records are the

same in this field) and is the field used to identify that record. Some tables will

have one or more

foreign key fields. A foreign key in one table will point to a

primary key in another table.

Table 10.1 shows the structure of a flat-file database table if we add extra data

such as the details of each student taught by each teacher, using the example

seen above.

Teacher_ID Forename Surname Subject Room Student_ID Student_FName Student_SName

AVA Anthony Varela Maths 51 G12345 Jasmine Hall

AVA Anthony Varela Maths 51 G12346 James Ling

AVA Anthony Varela Maths 51 G12348 Addy Paredes

AVA Anthony Varela Maths 51 G12349 Hayley Lemon

AVA Anthony Varela Maths 51 G12351 Jennie Campbell

GBA Graham Barney Science 14 G12345 Jasmine Hall

GBA Graham Barney Science 14 G12348 Addy Paredes

GBA Graham Barney Science 14 G12349 Hayley Lemon

JKW Jennie Kwong English 42 G12345 Jasmine Hall

JKW Jennie Kwong English 42 G12349 Hayley Lemon

JKW Jennie Kwong English 42 G12351 Jennie Campbell

PTY Paul Tyrell Science 13 G12346 James Ling

PTY Paul Tyrell Science 13 G12351 Jennie Campbell

SJR Sarah Jordan English 43 G12346 James Ling

SJR Sarah Jordan English 43 G12348 Addy Paredes

V Table10.1Aflat-filedatabasetable

257

10.1 Database basics

As it is possible for different teachers to teach in the same classroom, or for

teachers or students to have the same forename and/or the same surname, these

cannot be used as primary key fields. If the data is split into two tables, one for

the teachers and one for the students, that are linked together, it can be stored

and retrieved more efficiently, like this:

Teacher_ID Forename Surname Subject Room

AVA Anthony Varela Maths 51

GBA Graham Barney Science 14

JKW Jennie Kwong English 42

PTY Paul Tyrell Science 13

SJR Sarah Jordan English 43

V Table 10.2 Teachers’ table

Student_ID Student_FName Student_SName English Maths Science

G12345 Jasmine Hall JKW AVA GBA

G12346 James Ling SJR AVA PTY

G12348 Addy Paredes SJR AVA GBA

G12349 Hayley Lemon JKW AVA GBA

G12351 Jennie Campbell JKW AVA PTY

V Table10.3Students’table

These two tables are linked with a one-to-many relationship, because one

teacher’s record is linked to many student records. The primary key fields (which

must contain unique data) will be the Student_ID and Teacher_ID.

Flat-file database versus relational database

Using the example above, if only one of these sets of data (the teachers’ data or

the students’ data) had to be stored, then a flat-file database would be ideal as

long as there are no (or very few) repeated data items. If a database was to hold

both of these sets of data, then a relational database would be best.

A relational database is designed so that the same data is not stored many times.

If a data item is only stored once, then there is less chance of data entry errors

and less time taken to enter new data or edit existing data in a database. If data

is deleted from a database, it will also take less time to delete the data from

one table, as the same data will not need deleting several times as it would in a

flat-file database single table. It is also less likely that an item of data would be

overlooked and not deleted. In addition, when searching a large database, it is

generally quicker for the software to locate records as it will be searching through

significantly less data. However, this is not always the case; where indexed fields

are used it can be true but it is not always the case. It depends on the structure of

each database and the quantity of data being searched.

You can see, comparing Table 10.1 with Tables 10.2 and 10.3 with just five

teachers and five students, how much internal memory and external storage

space is saved simply by not storing data more than once. Imagine the space

saved for a school with 1500 students and 120 teachers, or in a national

database with data on every vehicle and driver registered in a country. It is also

much easier for users to produce reports when data is held using two or more

tables in a relational database, rather than using two or more flat-file databases.

We will use Microsoft Access to create our database. We can use this software to

create a relational database if there are two or more tables of data. If we only

have one table, we use the software to create a flat-file database.

258

10 DATABASE AND FILE CONCEPTS

10.1.2 Data types

When you create a new database table, you will set the data type for each field.

This determines how Access will store, manipulate and display the data for each

field. There are a number of data types that you can use and different packages

may have different names for them. The list below shows the generic names for

these data types, but depending upon the package used, you may have different

names. For example in Access, an alphanumeric field is called a text field. The

three main types of field are alphanumeric, numeric and

Boolean:

» Alphanumeric data can store alpha characters (text) or numeric data (numbers)

that will not be used for calculations. In Access this is called a text field.

» A numeric data type (as the name suggests) stores numeric values that may be

used for calculations. It should not store numeric data that is not used for any

calculations, like telephone numbers (data not used for calculations should be

stored as an alphanumeric data type). In Access, this is called a number field.

There are different types of numeric field including:

– Integer fields store whole numbers. In Access you can select an integer

field or a long integer field. It is wise to use a long integer field if it is

going to contain three or more digits.

–

Decimal formats, which will allow a large number of decimal places or

a specified restricted number, if this is set in the creation of the field

properties when the database is set up.

– Currency values, which allow currency formatting to be added to the

display. These include currency symbols and regional symbols. The

database does not store these as this would use up valuable storage space.

–

Date and time formats, which store a date and/or time as a number.

» A Boolean (or logical) data type stores data in a Yes/No (or True/False or 0/-1)

format.

There are other data types as well, like

AutoNumber which generates unique

numbers. Some packages like Access have long and short versions of their data

types, for example long text and short text (which are versions of alphanumeric

data types) or long number and number.

Other data types can often be found in commercial databases. Access can store

placeholders for media, such as images, documents, spreadsheets, presentations,

sound bites and video clips. All of these are stored using an

attachment data

type or using an OLE object (object linking and embedding) data type.

Media files tend to be used for web applications where a

back-end database

holds the media to be displayed in another application like a web page.

Advice

Microsoft Excel is not suitable for database tasks because you cannot define data

types.

10.1.3 Relationship types

Relationships are used to link tables together. There are three types of

relationship, one-to-one (1–1), one-to-many (1–∞) and many-to-many.

One-to-one relationship

This relationship is where each record in one table relates to only one record in

another table. For this type of relationship to exist, the linked fields in both data

tables must contain the same unique values.

259

10.1 Database basics

One-to-many relationship

This relationship is where each record in one table can relate to many records

in another table. This is where the primary key field in one table relates to the

foreign key field in the second table (see Section 10.1.4).

Many-to-many relationship

It is not possible to create a single many-to-many relationship in Microsoft Access

as this is only a theoretical type of relationship. The many-to-many relationship

is therefore created using two one-to-many relationships and a link table. For

example if a company sells many different types of products and has many

different customers, it is possible that one customer has bought different types

of product from the company and a type of product has been sold to many

different customers. In this case three tables and two relationships would be

created. The primary key field in the Customers table relates to many records in

the foreign key field within the Link table like this.

The primary key field

in the Product table

relates to many records

in the foreign key field

within the Link table

like this.

An example of the Link

table’s contents can be

seen here.

10.1.4 Key fields

Key fields are used to create the relationships between different tables in the

database. All relationships between tables use key fields. There are three types of

key field: primary key, foreign key and compound key.

Primary key

For a database table to be used as a relational table, it must have a primary key

field. Primary key fields are single fields that contain unique data and cannot

contain blank records. Because primary key fields are unique, they are also used

in many databases as indexes. An index optimises the storage of table records

within the database and helps to provide quicker searches.

Foreign key

A foreign key is a field in the table that is a primary key in another table. It links

to that primary key to form a relationship between two tables.

260

10 DATABASE AND FILE CONCEPTS

Compound key

A compound key is a primary key created using a number of foreign key fields

rather than a single foreign key, which together create unique data. In Access, no

data in any of these foreign key fields can be blank.

10.1.5 Entity relationship diagrams

The three types of entity relationship diagram (ERD) are conceptual,

logical and physical. Each ERD shows the design of the table structures and

relationships between tables within a relational database. ERD designs include:

» entities, which are objects or items to be included (often the tables to be

used)

» attributes, which are the facts about or properties of an entity (often the

fields to be used)

» relationships, which are the links between the entities.

Entities are drawn using a rectangular box with rounded corners like this.

There are different ways of displaying the attributes for each entity; the easiest to

use is as a list within the entity box like this:

Relationships are shown as lines between the entities.

One-to-one relationships in an ERD

One-to-one relationships have a single line at 90°

placed at each end of the relationship line, for

example one driver has one driver’s licence.

One-to-many relationships in an ERD

One-to-many relationships have a single line at the

‘one’ end of the relationship line and at the ‘many’ end of the relationship line

there are three lines.

For example one customer can make many purchases

from a company. This one-to-many relationship looks

like this.

Many-to-many relationships in an ERD

As we said above, although many-to-many

relationships exist (theoretically), these must be

designed as two one-to-many relationships. For example if a company sells many

different types of products and has many different customers, it is possible that a

customer has bought different types of product from the company and a type of

product has been sold to many different customers. In this case the relationships

in the entity relationship diagram look like this.

Note that relationship

lines in the ERD do not

identify the fields (called

attributes in the ERD)

used for the relationship,

unlike a sketch of

database relationships

which would need

primary and foreign keys identified.

Student

Name

Address

Telephone

Driver

Driver_ID

First_Name

Family_Name

Address

Telephone

Driver’s Licence

Driver_ID

Licence_Number

Licence_Type

Endorsements

Customer

Customer_ID

First_Name

Family_Name

Address

Telephone

Link

Customer_ID

Product_ID

Product

Product_ID

Product_Name

Price

Colour

Size

Customer

Customer_ID

First_Name

Family_Name

Address

Telephone

Purchase

Invoice_No

Cust_ID

Product_ID

Unit_Cost

Quantity

Discount_Rate

261

10.1 Database basics

Conceptual design

Conceptual design is what you do when you create a database design/model

which is not dependent upon the choice of database management system

(DBMS). A conceptual model uses information gathered from the business

requirements and is the only type of ERD that allows generalisations. In

other words, it allows ‘a kind of’ relationships, for example a rectangle is ‘a

kind of’ shape, whereas logical and physical models have to have more precise

relationship definitions. It is simpler than logical or physical design.

Logical design

Logical design is what you do when you create a database design/model where

the DBMS has already been selected. Logical models also use information

gathered from business requirements. They are more complex than a conceptual

model in that they contain a map of rules that will be followed. Column types

may be set to help the business analysis. Logical design is not about database

creation.

Physical design

Physical design is what you do when you create theactual database based on

the requirementsgathered during logical database modelling so that it can be

implemented within the DBMS. It contains more detail than the other types

of ERD, for example column types from logical designs become data types,

and field lengths are included in the design. Primary and foreign key fields are

also included. Like conceptual and logical models, the relationships are not

drawn from attribute to attribute (as you would find in a database relationship

diagram).

Task 10a

TheTawaraWidgetCompanyhasanumberofdifferentsites.Thecompanyis

going to create a database to store details of its employees, their jobs and the

sitewheretheywork.Thecompanyrequiresthesitecode,nameofthesite,

address and telephone number. It also requires for each employee: their payroll

number, name, address, job code, job description and rate of pay. All employees

doing the same job are paid at the same rate of pay.

Create conceptual, logical and physical entity relationship diagrams for this

company.

Conceptual ERD

Identify the entities from this task. These will be: the employees, their jobs and

the site where they work. Create the conceptual ERD (either hand drawn or on

thecomputer) like this.

The attributes have been

added to the diagram, using

the details given in the task

description. They are used

to identify what information

should be stored within each

entity.

Site

Site_Code

Site_Name

Site_Address

Telephone

Employees

Payroll_Number

First_Name

Family_Name

Address

Job_Code

Site_Code

Jobs

Job_Code

Job_Description

Rate_of_Pay

262

10 DATABASE AND FILE CONCEPTS

Logical ERD

Create the logical ERD in a similar way. Allow space within each entity box

for another column to the right of the attributes. Complete the entities and

attributes (as in the conceptual diagram). Add a new column to the right of the

one holding the attributes. In this new column, place the type of data that the

company holds for each entity. For each entity box, you now have the attribute

and its associated data type.

Physical ERD

The physical ERD is the one used to create the database, so must include the

entities (tables) and attributes (fields) as well as the data types, field lengths and

key fields. Both primary and foreign keys should be identified. Create this in a

similar way to the conceptual and logical diagrams. The final diagram will look

like this.

Activity 10a

TheTawaraBusCompanyuses10buseswhichithirestocustomers.Foreach

bus, it has its registration number (which is unique, seven characters long and

consists of both numbers and letters), the make and model of the bus, as well

as the number of seats on the bus. The company employs 26 drivers; each has

a payroll number and the company stores personal data including their name,

address, telephone number (stored as five numbers followed by a space then six

numbers) and date of birth. All drivers are over 25 years of age. The company

holds personal data about each customer, which includes a numeric customer

number, their name, address, telephone number and email address. Each time

abusishiredforajourneyitisgivenauniquebookingnumber.Thebusused,

driver, date and time of the journey, the customer and number of miles are

recorded.

Create conceptual, logical and physical entity relationship diagrams for this

company.

Hint: Drawing each box side by side will not work for this activity.

Alphanumeric

Currency

Site

Site_Code

Site_Name

Site_Address

Telephone

Employees

Payroll_Number

First_Name

Family_Name

Address

Job_Code

Site_Code

Jobs

Job_Code

Job_Description

Rate_of_Pay

Alphanumeric

5 PK

150

8 PK

150

2 FK

5 FK

2 PK

2 dp

Alphanumeric

Currency

Site

Site_Code

Site_Name

Site_Address

Telephone

Employees

Payroll_Number

First_Name

Family_Name

Address

Job_Code

Site_Code

Jobs

Job_Code

Job_Description

Rate_of_Pay

Alphanumeric

263

10.2 Designing the database structure

Task 10b

TheTawaraMotorCompanyisdesigningadatabasetostoreinformationoneach

employee, their job and the branch in which they work. For each employee, the

database needs to hold information on their name, their payroll number (which

is unique to them and has two letters followed by four digits, the first letter is

always T or M), the branch in which they work and the code of the job that they

perform. For each job, the job code and description must be stored along with

the rate of pay for that job. For each branch of the company, the branch number,

branch name, address and a weighting which is used to calculate salaries

will be stored. The job code and branch numbers are both single letters. The

weighting ensures that employees living in larger cities, where housing costs

are higher, receive more pay than workers in areas where housing costs are

lower.Thisisadecimalvaluebetween0and2storedtotwodecimalplaces.

Design and create this database.

10.2 Designing the database structure

Often the design of the database can be taken from the physical ERD. Before

this, it is worth creating the conceptual ERD using three entities.

10.2.1 Entity and attribute names

Each entity name may be used as a table name, so it is important to keep them

short and meaningful and for them to contain no spaces. In this task there are

three distinct sets of information used: the information about the branches, the

employees and their jobs. In this case we will use Branch, Employees and Jobs.

Each attribute name may be used as a field name, so again it is important to

keep them short, meaningful and without spaces. They must be short enough

to ensure that printouts fit on a page without wasted space but long enough to

be meaningful. A good convention is to avoid using spaces within field names as

some databases do not accept these. Assign the attributes to the entities like this.

10.2.2 Relationships

Each branch has many employees working there, so a one-to-many relationship

will be needed between these entities. As some employees may perform the

same job within the company, a one-to-many relationship will exist between

theentities for jobs and employees. Add these changes to the ERD so it looks

like this.

Branch

Code

Branch

Address1

Address2

Address3

Weighting

Employees

PayNumber

FirstName

FamilyName

JobCode

BranchCode

Jobs

JobCode

Description

RateofPay

264

10 DATABASE AND FILE CONCEPTS

10.2.3 Data types

To turn the conceptual ERD into a logical ERD, the data types need to be

considered and added. See the list of data types that could be chosen in

Section 10.1.2 above.

Advice

Remembertoonlyuseanumericfieldtypeifthedatamaybeusedfora

calculation.

Add appropriate data types to create this logical ERD.

10.2.4 Field lengths

The length of each field needs to be considered and added to the ERD. If the

data is available to examine, count the number of characters for the longest

data item and set the field length to that number. For example for first names

or family names, look for the longest possible name you can find (including

hyphenated names) and set the field length to this. Field lengths only need

to be considered for alphanumeric data types. Some of the information to

help set the field lengths is in the task, for example the payroll number has

two letters followed by four digits so will be six characters long. The branch

code and job attribute in the job table are defined as a single alphanumeric

character.

10.2.5 Key fields

To turn the ERD into a physical ERD, the key fields need to be considered and

added. The primary key field for each entity/table will be the field that contains

unique information. In the case of the Employees entity/table, you were told in

the task that the employee’s payroll number was ‘unique to them’ so this will

bean obvious choice for primary key field. For the Jobs entity/table, the job

code field will contain unique values and will be suitable to use as a primary key.

For the Branch entity/table the branch code will also contain unique values and

should become the primary key field for this table.

Alphanumeric

Currency

Branch

Code

Branch

Address1

Address2

Address3

Weighting

Employees

PayNumber

FirstName

FamilyName

JobCode

BranchCode

Jobs

JobCode

Description

RateofPay

Alphanumeric

Decimal

Branch

Code

Branch

Address1

Address2

Address3

Weighting

Employees

PayNumber

FirstName

FamilyName

JobCode

BranchCode

Jobs

JobCode

Description

RateofPay

265

10.3 Creating the database structure

Foreign key fields can be added on the JobCode and BranchCode attributes of

the Employees entity to allow the one-to-many relationships to be created, as

one branch has many employees and one job type has many potential employees.

The physical ERD will now look like this:

10.3 Creating the database structure

Open Microsoft Access and select the icon for

Blank database. Select the folder you wish to

store the database in and enter the database

name as Task_10b before clicking on the

Createbutton.

We will use the physical ERD to create this

database.

10.3.1 Create the Employees table

Select the Create tab, then double-

click on Table. Select the Home

tab, then in the Views section, the icon for

Design View.

When this is selected, the Save As window opens.

Enter the entity name Employees and click on

to name this table.

Access incorrectly assumes that

you want an ID field as a primary

key field, but the primary key for

the Employees table will be the

PayNumber. To delete the ID

field, click the right mouse button

in the ID box and from the drop-

down menu select

Delete Rows.

This warning message appears.

Click on

to delete the record.

Rename Field1 to be

PayNumber and use the drop-down menu in the

Data Type column to change the data type to Short Text if it is not already

setas that.

Advice

In Access, for alphanumeric data, use a ‘text’ field type, either Short Text or Long

Text.

Alphanumeric

Currency

Branch

Code

Branch

Address1

Address2

Address3

Weighting

Employees

PayNumber

FirstName

FamilyName

JobCode

BranchCode

Jobs

JobCode

Description

RateofPay

Alphanumeric

Decimal

1 PK

6 PK

1 FK

1 PK

266

10 DATABASE AND FILE CONCEPTS

10.3.2Setakeyfield

To set a primary key, select the field that contains unique data, then click on the

primary key icon. To set a compound key, hold down the <Ctrl> key and select

all the fields that will comprise the compound key, then click on the primary

key icon. A foreign key field is not set. However, it must contain the same data

as the primary key to which it will be related.

For this Task, from the

Design tab (which should be

open) select the Primary Key button to set this as the

primary key field for this table.

In the

Field Size box change the size from 255 to 6

characters for the field length.

Move down to the row below PayNumber and enter the

next field name,

FirstName. Use this method to add all

field names and data types to this table.

10.3.3 Input masks

An input mask will force a database user to restrict the

data that they enter into a field. It is not a validation

routine as it does not show an error message if incorrect

data is added, but it does help to reduce data entry errors.

The Task stated that the payroll number ‘has two letters followed by four digits’.

We can use an input mask to restrict the data entry for each character within

this field. Click the cursor in the

PayNumber row. In the Input Mask section,

enter ‘LL0000’ to restrict data entry to two alphanumeric characters followed

by four numeric digits. Table 10.4 shows some of the characters that can be used

within the input mask and the function of each.

Character Function

0 User must enter a number

9 User can enter a number (optional)

# User can enter a number, space, + or -

L User must enter a letter

? User may enter a letter

A User must enter a letter or number

a User can enter a letter or number

& User must enter a letter, number or space

C User can enter a letter, number or space

V Table10.4Inputmaskcharacters

10.3.4 Validate data entry

The Task stated that for the payroll number, ‘the first letter is always T or M’.

We can use a validation rule to restrict the data entry for the first character

within this field. Click the cursor in the PayNumber field. In the Validation

Rule

, enter Like "T

?????" Or Like "M?????" to restrict data entry. In

the Validation Text, enter an appropriate error message, such as The first

character must be "T" or "M". Error messages like this should always tell the

user how to correct the error.

267

10.3 Creating the database structure

A brief description can be added to each field by typing it in the Description

area. This will help the user to enter data by giving them helpful instructions

as to what is required in this field. This is not used by the database to check

the data. Instead, it gives the user a little help when using the form to enter

data. An example for the

PayNumber field is: Enter the employee’s payroll

number which is formatted as

two letters followed by four

numbers. Save the table, which

should look like this.

Advice

Herearesomequicktipsfor

setting other field types in Access.

» Tosetapercentagevalue,

select a Currency data type,

then in the Format section

select Percent.

» Tostoreadateortime,select

Date/Time data type, then

select the Format.

» ForaBooleanorlogicalfield

type select a Yes/No data type,

then

Format to choose from

Yes/No,True/FalseorOn/Off.

10.3.5CreatetheBranchtable

Create the Branch table using

a similar method to that used

for the Employees table. The

Weighting field must be ‘a

decimal value between 0 and 2

stored to two decimal places’. For

numeric fields, the

Field Size

box is used to display the type of

numeric field. Use the drop-down

menu to select Double from the

list. The number of decimal places

can be set using the

Decimal

Places section. For this Task, use

the drop-down menu to select 2

decimal places. Add the validation

rule and text as shown.

Set the

Code field as the primary

key. Save the table.

Advice

ForintegerfieldtypesinAccess,useInteger for a whole number between 0 and

255 and Long Integer if larger than that.

268

10 DATABASE AND FILE CONCEPTS

10.3.6CreatetheJobstable

Create the Jobs table using the physical ERD and a similar

method to the other tables. Set the Job field as the primary

key. Save the table. The completed Jobs table should look

similar to this.

Close all three tables before creating the relationships.

10.3.7 Create a relationship

Select the Database Tools tab, then in the Relationships

section select the Relationships icon.

This will open the Relationships window in Access. All of the tables that have

been created are visible in the

Show Table window. Select each table in turn

and click on

to add each table to the Relationships window. When

all of the tables have been added, close the Show Table window.

Advice

Whentablenamesandfieldnamesarereferredtotogether,theyareoften

shortened. This is done using notation in the form of TableName.FieldName. For

example the notation Employees.PayNumber means the PayNumber field within

the Employees table. This notation is used in the rest of this chapter.

The ERD shows there were two one-to-many relationships to create, one

between Branch.Code and the Employees table. The two foreign keys in the

Employees table are the

JobCode and BranchCode fields, so the BranchCode

field will be used. For the first FK, a relationship needs to be created between

the Branch.Code and Employees.BranchCode.

This will be a one-to-many relationship. Click on

the

Code field in the Branch table.

Drag and drop this field onto the BranchCode

field in the Employee table.

The Edit Relationships window will now

appear. Check that the correct table names and

field names are present within this window.

You will notice that Access has created this as a one-to-many

relationship type.

At this stage the relationship has not yet been created.

Advice

Ifthetwofieldsjoinedbyarelationshiparebothprimarykey

fields, then Access will create a one-to-one relationship.

10.3.8 Referential integrity

Referential integrity forces table relationships to be consistent and avoids

redundant data. This means that the data in the foreign key field must match

the data in the primary key field. It prevents a user from accidentally changing

or deleting data in one table, without the same action happening to the related

data in another table.

To enforce referential integrity between these tables, tick the check box.

269

10.3 Creating the database structure

Tick the other two check boxes for Cascade Update Related Fields and

Cascade Delete Related Records so that when you update or delete a primary

key, Access automatically updates all records in the foreign key field/s that

reference that primary key field. Click on

to create the relationship.

The relationship diagram will now show the relationship line. As referential

integrity has been added to the relationship the diagram shows the join type on

the relationship line.

Advice

To open the Edit Relationships window and edit the relationship at any time,

double-click the left mouse button on the relationship line.

Create the relationship between Jobs.Job

and Employees.JobCode using a similar

method to that used for the Branch table.

Make sure that you enforce referential

integrity. Save the database, which will save

the relationships you have created. The

relationship diagram now looks like this.

The table structure of the database has been

created, so the validation rules and input masks need to be tested.

10.3.9 Testing the validation rules

Using the techniques learnt in Section 8.2, design your test plan to test the

validation rule set in the Employees.PayNumber and Branch.Weighting fields.

For the Employees.PayNumber field the rules set were Like "T

?????" Or Like

?????", so design your test plan as shown in Table 10.5.

Test type Validation Table.Field Employees.PayNumber

Data entry Data type Expected result Actual

result

Remedial

action

TX5443 Normal Data accepted

TA1234 Normal Data accepted

MX5443 Normal Data accepted

MA1234 Normal Data accepted

LX5443 Abnormal Validation error message

NA1234 Abnormal Validation error message

SX5443 Abnormal Validation error message

UA1234 Abnormal Validation error message

100400 Abnormal Validation error message

V Table 10.5 Sample test plan for Task 10b: Employees.PayNumber field

270

10 DATABASE AND FILE CONCEPTS

For the Branch.Weighting field the rules set were >0 And <2, so design your

test plan as shown in Table 10.6.

Test type Validation Table.Field Branch.Weighting

Data entry Data type Expected result Actual

result

Remedial

action

0.01 Normal Data accepted

1 Normal Data accepted

1.99 Normal Data accepted

0 Abnormal Validation error message

2 Abnormal Validation error message

Hello Abnormal Validation error message

-67 Abnormal Validation error message

1000 Abnormal Validation error message

V Table10.6SampletestplanforTask10b:Branch.Weightingfield

Use this test data to test both rules and complete the test plans.

10.3.10Testingtheinputmask

Using the techniques learnt in Section 8.2, design your test plan to test the

input mask set in the

Employees.PayNumber field. The input mask set was

‘LL0000’, so design your test plan as shown in Table 10.7.

Test type Input mask Table.Field Employees.PayNumber

Data entry Data type Expected result Actual

result

Remedial

action

TX0001 Normal Data accepted

MA9999 Normal Data accepted

TTT443 Abnormal Validation error message

M31234 Abnormal Validation error message

TX544 Abnormal Validation error message

000001 Abnormal Validation error message

V Table10.7SampletestplanforTask10b:Employees.PayNumberfield

Use this data to test the input mask and complete the test plans.

10.3.11Verifydataentry

As well as using validation rules and input masks, it is also important to verify

all data entry into your tables. As explained in Section 1.4, visual verification

can greatly reduce the number of data entry errors. Carefully check that the data

that has been entered into the tables matches exactly that provided in the source

document.

271

10.4 Creating a relational database from existing files

Activity 10b

Createaword-processedEvidenceDocument.Placeyournameandschool/

college name in the header of all pages and save this as Activity_10b.

In Activity 10a, you designed a database structure for the Tawara Bus Company.

Use your physical ERD to create this structure in your database package. Place

in your Evidence Document evidence of:

» allthetablesshowingallofthedatatypes

» all primary keys

» validation rules

» input masks

» relationship diagrams.

Save your database as Activity_10b.SaveyourEvidenceDocument.

10.4Creatingarelationaldatabasefrom

existing files

Sometimes you may have to create a database using given data files. In this Task,

the database structure has been designed for you as an ERD but may be given

as a data dictionary (see Section 10.11), or you may have to use normalisation to

find the data structures (see Section 10.10).

Always start by closely examining any files

given to you. When you study the files

FA-Cust1.csv and FA-Cust2.txt you will

see that the two files need merging and the

order of the fields re-arranging before the

data can be imported into a database. Excel

would be the most appropriate package for

this task. When examining these files, it is

clear that FA-Cust1 will create the first part

of the data file and FA-Cust2 the second.

Open both files in Excel (drag the .txt file

into Excel) and view them side-by-side.

Task 10c

Freeze-Airisacompanythatmakesandsellsfridgesandfreezers.IthassuppliedyouwithaphysicalERD

anditsdatafilessothatyoucancreateadatabase.Eachinvoicecontainsonlyasingleproductitembutmay

containmorethanoneofthoseitems.TheERDisshownhere.

Numeric

Alphanumeric

Numeric/Integer

Date/Time

Alphanumeric

Numeric/Percent

Product

Code

Model

Price

Capacity

Energy_Rating

Colour

Invoice

Inv_Number

Cust_ID

Product_Code

Quantity

Order_Date

Order_Time

Collect_Date

Collect_Time

Customer

Cust_ID

Given_Name

Family_Name

Address1

Address2

Address3

Discount

Alphanumeric

Currency

Numeric/Integer

Alphanumeric

5 PK

6 FK

5 FK

6 PK

ThedatatocreatethedatabaseislocatedinthefilesFA-Product.csv, FA-Invoice.csv, FA-Cust1.csv and

FA-Cust2.txt. Create this database.

272

10 DATABASE AND FILE CONCEPTS

Copy and paste the columns in FA-Cust2 so that they are in

the same order as those in the first file, like this.

Copy, from FA-Cust2, cells A2 to G22 and paste them into

cells A21 to G41 in FA-Cust1. Make sure that you do not copy row 1

as this will cause import problems later. Save this file as Cust.csv.

Create a new blank database and save this as Task_10c.

10.4.1 Create the Product table

Select the External Data tab, then New Data Source.

Select From File from the drop-down menu, then Text File

(as a .csv file format is a text format) like this.

The

Get External Data – Text File window opens. Select, in the File name

box, the file FA-Product.csv. Make sure that the radio button for Import the

source data into a new table in the current database is selected, then click

This opens the

Import Text Wizard window. As the data is a .csv file which

has the data separated (delimited) by commas, select the Delimited radio

button before clicking

In the next window, check that the radio button for Comma is used, then

tick the check box for First Row Contains Field Names, before

clicking

The next window is probably the most important.

For each field, change the field name to match that

shown in the physical ERD (or the data dictionary).

In this instance, in the

Field Name: box, change

the Product code to Code, leave the Data Type as

Short Text. As this field is to be the primary key

field, change the

Indexed: box from Yes (Duplicates

OK) to Yes (No Duplicates).

We will have to select the field lengths when the

import is complete. Click on each field in turn,

setting the field names and data types as shown in

the ERD, then click

. Access will try and

add its own primary key in an

ID field, but we need to select

the radio button for

Choose my

own primary key. Because we

changed the index to no duplicates

in the previous window, it will

recommend the

Code field. Click

. In the Import to

Table:

box, enter the table name

Product as the entity name from

the ERD and click

You do not need to save the import

steps so click <Close>.

Select the

Home tab and then open the Product table in Design View. For

each alphanumeric field set the field size to match the physical ERD. Save the

database.

273

10.5 Querying the database

10.4.2CreatetheInvoiceandCustomertables

Use the same techniques to import the files FA-Invoice.csv and Cust.csv as the

Invoice table and Customer table respectively, so that the tables look like this:

10.4.3 Create the relationships

Use the techniques learnt earlier in the chapter

to create relationships that match the ERD, as

shown here.

Save the database.

Activity 10c

YouworkforNannaPat’sBoatYard.Itmakesandsellssmallboats.Youhave

been supplied with a physical ERD and the data files NP-Boat.csv and NP-

Customer.csv.TheERDisshownbelow.

Numeric/Integer

Alphanumeric

Numeric 2 dp

Numeric/Integer

Boolean

Date/Time

Alphanumeric

Numeric/Percent

Boat

BoatID

CustID

Make

Type

Price

Length

Year

Catamaran

BuiltHere

Bought

Sold

Customer

CustomerID

FirstName

FamilyName

Telephone

Discount

6 FK

6 PK

Studythesourcefilesandusethesetocreateadatabasefromthisdiagram.

10.5 Querying the database

At this level you may be required to perform a query on a single criterion but

are more likely to be required to perform them on multiple criteria and produce

high-quality output to certain specifications. Performing searches in Access is

carried out using queries and the high-quality output is done using reports.

274

10 DATABASE AND FILE CONCEPTS

Task 10d

Use a copy of the database saved in Task 10c to produce a report for the

directors of the company. This will list the details of all the customers who

are given no discount on their orders and, for each customer, list the model/s

purchased and the price. Do not display the discount.

Copy the database saved in Task 10c and save this as Task_10d. Open this database.

As this report is to be presented to the directors of the company, it must have a

professional appearance with an appropriate title and layout. As the task requires

the details of the customers, all of the customer data should be displayed for

those who meet the search criterion. Start the task by identifying which fields

will be needed and which will not.

Look at each table and indicate which data will be required. One method is to

list all the fields and tick or highlight those required. It is debatable as to whether

the

Cust_ID field is required, but if in doubt about a field, include it anyway.

Customer table Invoice table Product table

Cust_ID



Inv_Number Code

Given_Name



Cust_ID Model



Family_Name



Product_Code Price



Address1



Quantity Capacity

Address2



Order_Date Energy_Rating

Address3



Order_Time Colour

Discount



Collect_Date

Collect_Time

V Table 10.8 Field selection for Task 10d

10.5.1 Perform a simple numeric

query on a single criterion

Select the Create tab, then in the Queries

section, select the icon for Query Wizard.

In the

New Query window, choose the

Simple Query Wizard, then click

Using Table 10.8, select from the

Tables/

Queries

drop-down list each table in turn.

You will select the ticked fields from each

table in Table 10.8. When you need to

move to a new table, just select from the

Available Fields box in turn. Select the

Discount field too, as it will be used

in the query although not displayed.

Use the right arrow key

to move the field name into the Selected Fields box.

Advice

If all fields from a table are to be placed in the query then use this button.

275

10.5 Querying the database

When all the ticked (required) fields have been selected, click . For

this query we will select the radio button for Detail then click

Advice

Wewilllookatsummaryquerieslaterin

the chapter.

For the final window, change the title of the

query to a suitable title for the report.

Select the radio button for

Modify the

query design

, then click on

The query will appear in

Design View. The

criterion for the query has not yet been

added.

The results of the query appear like this:

Move the cursor into the Discount column on the Criteria: row.

Enter the value 0 to indicate no discount. Save the query (using the save icon at the top left).

Click on the

Run icon to run the query.

276

10 DATABASE AND FILE CONCEPTS

Advice

Usethesenumericoperatorsforqueriesinvolvingnumbers.

Operator Meaning Example

= Equal to =6 Equal to 6

> Greater than >6 Greater than 6

< Less than <6 Less than 6

>= Greater than or equal to >=6 Greater than or equal to 6

<= Less than or equal to <=6 Less than or equal to 6

<> Not equal to <>6 Not equal to 6

10.5.2 Creating the report

The easiest way to create the report is to use

the Report Wizard. Select the Create tab,

and from the

Reports section select the

Report Wizard to open the Report Wizard

window like this.

Select from the Tables/Queries drop-down

list the query that we have just created.

Move all fields except for

Discount into the

Selected Fields: box, then click

View the data by Customer, then click

. There is no need to select

any groupings as Access has already added

all the Customer details into the top

group, so click

. There is no

requirement in the question to sort or

summarise the data so click

Select the layout type that you require,

but because there are a number of fields

selected, it is easier to lay out the report

with landscape orientation selected. Click

in the

Orientation section on the radio

button for Landscape, then click on

. In the final Report Wizard

window enter the title for the report; it

should be meaningful to the intended

audience (who are the directors of the

company in this question).

Click on the radio button for

Modify the

report’s design

, then click

This opens the report in design view, so it

looks like this.

277

10.5 Querying the database

This is where each control can be adjusted if data does not fit within each

control. To check if data fits within each control, select the

Home tab, in the

View section select the icon for Report View.

Report View shows none of the data is longer than the control so save the

report. It looks like this.

Activity 10d

UseacopyofthedatabasesavedinActivity10ctoproduceareportforthe

shareholders of the company. Select and list all the details for the boats that

havebeensoldandweremadeintheyear2006,aswellasthefullnameofthe

customer who bought each boat.

Task 10e

UseacopyofthedatabasesavedinTask10dtoproduceareportforthe

directors of the company. This will list the details of all the customers who

are given between 5 and 15% discount (inclusive) on their orders. For each

customer, list the model/s purchased, discount and price.

278

10 DATABASE AND FILE CONCEPTS

10.5.3Performanumericqueryusingmultiplecriteria

Create a query and report in a similar

way to the one for Task 10d. Make sure

that you select the fields from the tables

and not the query from this Task. In

the

Design View of the query, in the

Criteria: row of the Discount column,

enter the formula (in this case inequality)

>=0.05 AND <=0.15, like this.

This image only displays the first few matching records in this query.

Advice

ThisqueryusedtheBoolean operator AND to make sure that all the conditions

weremetbeforearecordwasselected.OtherBooleanoperatorsareORwhich

selects records if one of the conditions are met and NOT which selects records if

a condition is not met. Combinations of theseoperatorscanallowustoperform

complexqueries,whichwewillmeetlaterinthechapter.

Task 10f

UseacopyofthedatabasesavedinTask10etolistthedetailsofallproducts

sold where the colour is Anthracite.

10.5.4 Perform a simple alphanumeric query on a single

criterion

Create a query and report in a similar way to the one for

Task 10e. Select all the fields from the Product table

only. In the Design View of the query, in the Criteria:

row of the Colour column, enter the text Anthracite.

As you press the

<Return/Enter> key, Access places

speech marks around the text like this.

279

10.5 Querying the database

Task 10g

UseacopyofthedatabasesavedinTask10ftolistthedetailsofallproducts

sold where the colour is Anthracite and the energy rating is A++.

10.5.5 Perform an alphanumeric query using multiple

criteria

AND query

Create a query and report in a similar way to the one for

Task 10f. Select all the fields from the Product table only.

In the

Design View of the query, in the Criteria: row

of the Colour column, enter the text Anthracite. In the

same Criteria: row of the Energy_Rating column, enter

the text

"A++" This time, type the double speech marks

yourself as the plus signs confuse Access. It should look like

this.

Task 10h

UseacopyofthedatabasesavedinTask10gtolistthedetailsofallproducts

sold where the colour is Anthracite or Silver.

OR query

Create a query and report in a similar way to the one for

Task 10g. Select all the fields from the

Product table

only. There are two methods for performing this query.

The first uses the Boolean operator OR. In the Design

View

of the query, in the Criteria: row of the Colour

column, enter the text Anthracite OR Silver. Access

places the speech marks around the two colours like this.

280

10 DATABASE AND FILE CONCEPTS

The second method places each part of the Boolean

statement on a new row. In the Design View of the query,

in the

Criteria: row of the Colour column, enter the

text Anthracite. Directly below this in the or: row of the

Colour column, enter the text Silver, so that it looks like

this.

Both methods give the same result.

NOT query

The same results can be achieved using a NOT query.

As the company only makes models in white, silver and

anthracite, selecting models where the colour is NOT

white would also work. Create a new query and report

in a similar way to the one for Task 10g. Select all the

fields from the Product table only. In the Design

View

of the query, in the Criteria: row of the Colour

column, enter the text Not White. Access adds the

speech marks like this.

Wildcard searches allow us to search for part of a string. The * symbol is used

to denote the wildcard. If the search criterion is like M* it will find all items

starting with the letter M, if it was Med* it would find all items starting with

the letters Med. For searches that ‘ends in’, the asterisk appears first, so *t

would find all the items ending in t. If an item can appear anywhere in the field,

then *X* would find the letter X anywhere within the field.

Task 10i

UseacopyofthedatabasesavedinTask10htolistthedetailsofallproducts

sold where the model name:

» endswithCool(toindicateitisafridge).

» startswithM(toindicateitisamedium-sizeditem).

» containsL(toindicateitisalargeritem).

Create three new queries and reports in a similar way to

the one for Task 10h. Select all the fields from the

Product

table only. Open the query Design View for each query

and move the cursor into the Criteria: row of the Model

column. For the first query, enter the text *Cool. Access

changes this to a ‘Like’ function so the criteria and result

look like this.

281

10.5 Querying the database

For the second query, enter the text M*, which again

becomes a ‘Like’ function so the criteria and result look

like this.

For the second query, it would appear sensible to enter

the text *L*, which again becomes a ‘Like’ function

like this.

When this is tested it does not give the

correct results.

10.5.6 Complex queries using

multiple criteria

Because some of the fridges have the letter

L at the end of their model name, these

records are also included. To remove these,

we must use more complex criteria that will

allow the letter L in the

Model field but

will not output Models which start with

the letter M or the letter S. Enter this text

into the

Criteria: row:

(Like "*L*" AND NOT Like "M*") AND (Like "*L*" AND NOT Like "S*").

This solution works but other criteria such as ‘Like "*L*" AND NOT Like "*l"’

would not work because records X0007–X0009 contain L at both the start and

finish of each model name.

Task 10j

UseacopyofthedatabasesavedinTask10itoproduceareportforthedirectors

of the company. This must contain the details of customers whose family name

starts with the letter S or T, who live in Tawara and do not have 20% discount.

282

10 DATABASE AND FILE CONCEPTS

Use the same method as the previous tasks for selecting the data from the

Customer table. Use an appropriate name for the query. In the Design View of

the query, in the top

Criteria: row of the Family_Name column, enter the text

S* OR T*. In the same row of the Address3 column, enter the text Tawara

and in the Discount column enter NOT 0.2, like this.

To sum up, a simple query involves a search on one field only without the use of

Boolean operators. A complex search involves a search on more than one field or

the use of Boolean operators or a combination of both.

10.5.7 Date query

When dates are used within queries the dates must be placed within hash

symbols so that Access treats the data as a date rather than as a calculation, for

example #31/12/2020#.

Task 10k

UseacopyofthedatabasesavedinTask10jtocreateareportforthemanager,

listing the invoice number, customer name, all product details, the order date

and quantity ordered for all invoices where the order was placed in November or

December 2019.

Use the same method as the previous tasks for

selecting the data from the

Customer, Product

and Invoice tables. Use an appropriate name

for the query. In the Design View of the query,

in the top Criteria: row of the Order_Date

column, enter the text >#31/10/2019# AND

<#01/01/2020# like this.

283

10.5 Querying the database

Task 10l

UseacopyofthedatabasesavedinTask10ktocreateareportforthemanager,

listingtheinvoicenumber,theorderdateandtime(inhh:mmformat)ofall

orders placed in the afternoon.

10.5.8 Time query

Use the same method as the previous

tasks for selecting the data from the

Invoice table. Use an appropriate name

for the query. In the

Design View of

the query, in the top Criteria: row of

the

Order_Time column, enter the text

>#12:00:00#, like this.

This lists the 31 orders that were placed during the afternoon. You will notice

that the time is displayed in hh:mm:ss format both in the query and in the

report as we did not specify the time format when we assigned data types to the

field. To change the format of the displayed data in the report, open the report

Design View, select the control for Order_Time and in the Format tab

select the drop-down menu for the

Format box and choose the required format,

in this case Short Time to display as hh:mm.

Advice

Tolocaterecordsinadatefieldwheretherecordisblank,setthecriteriato

IS NULL.

Activity 10e

UseacopyofthedatabasesavedinActivity10dtoproducethefollowingreports

for the shareholders of the company. Find the:

» namesandemailaddressesofthecustomerswithaphonenumberthat

starts with 001223.

» detailsoftheunsoldpowerboatsthatwerebuiltatthisyardafter2012.

» make,type,price,yearanddatetheboatwasboughtforallboatsboughtin

theyear2020.

284

10 DATABASE AND FILE CONCEPTS

10.5.9 Static and dynamic parameter queries

Static parameter queries

The reports we have just created are based

upon static parameter queries. This means

that the parameter that the query uses (in

Task 10d this was the value 0), has been

set as a fixed value within the query. It

can only be changed by recreating the

query or editing its structure.

Dynamic parameter queries

Rather than creating a new query each time and changing the value/s within

the query, we can create a dynamic parameter query. As the query is run, it asks

the user to enter a value or formula which it then uses to select the appropriate

records.

Task 10m

UseacopyofthedatabasesavedinTask10ltoproduceareportforthe

directors of the company which will prompt the user to enter the discount

criteria so as to select the customers who meet the given discount criteria, and

ach

hese

ustomers

isplay

heir

etails,

odel/s

urchased,

scount

rice.

You will notice the similarity between this Task and Task 10d which required a

discount set to 0.

Use the same method as the previous

tasks, applying appropriate names to the

query and the report. In the

Design

View of the query, in the Criteria: row

of the Discount column, enter the text

that will be used to instruct the user

what to enter for the parameter query,

for example:

[Enter the discount rate for this search].

This text must be entered inside square brackets.

Create the report using this query. When the report is opened,

the query is run and this window appears.

Enter the criteria into the box. To recreate Task 10d, enter 0.

This query will only accept a single value as the parameter.

Task 10n

UseacopyofthedatabasesavedinTask10mtoproduceareportforthe

directors of the company. The query will select the details of all the customers

who are given between two (inclusive) discount values entered at run-time by

the user. For each customer, list the model/s purchased, discount and price.

You will notice the similarity between

this Task and Task 10e which required a

discount of between 5% and 15%. This

task requires two parameters, so the new

parameter query will look like this.

285

When this query is run, both

parameter boxes will appear in turn.

To recreate Task 10e, enter 0.05 for the

lower discount rate and enter 0.15 for

the higher discount rate.

The result should be the same data as

Task 10e.

Task 10o

UseacopyofthedatabasesavedinTask10ntoproduceareportforthe

directors of the company. The query will select the details of all the models in

two colours selected by the user.

You will notice the similarity between this Task and Task 10h which

required all products sold where the colour is Anthracite or Silver.

This task requires two parameters so the new parameter query will

look like this.

When this query is run, both parameter

boxes will appear in turn. To

recreate Task 10h, enter Anthracite

for the first colour and Silver for

the second colour.

Task 10p

UseacopyofthedatabasesavedinTask10otocreateareportforthemanager,

listing the invoice number, customer name, all product details, the order date

and quantity ordered for all invoices where the order was placed between two

dates input by the user.

You will notice the similarity between this Task and Task 10k which

required orders placed in November or December 2019. This task

requires two parameters so the new parameter query will look like this.

You will notice that the dates entered into the parameter query do not

have to have # symbols around them like static date queries. When this

query is run, both parameter boxes will appear in turn. To recreate

Task 10k, enter the last day in

October 2019 for the start date

and enter the first of January 2020

for the end date.

To sum up, in a static query

every time that the query is run

it will search for the same data.

If different data is to be searched

for, the user would need to redesign the query to change the data in the criteria.

Every time a parameter query is run a dialogue box appears asking the user to

type in the data they are looking for. This would save the time of designing the

query every time different data is required. In addition, a dynamic parameter

query often requires the user to have less technical knowledge.

10.5 Querying the database

286

10 DATABASE AND FILE CONCEPTS

Activity 10f

UseacopyofthedatabasesavedinActivity10etocreateparameterqueriesthat

canbeusedtofind:

» boats of a specified type

» boatsofaspecifiedtypecostinglessorequalthanaspecifiedprice

» boatsofaspecifiedtypemadeafteroronaspecifiedyear

» catamarans made in a specified year.

Usetheseparameterqueriestolistthemake,type,priceandyearofthe:

» yachts

» powerboatscostinglessthan£40000

» yachts made after the year 2011

» catamarans made in the year 2015.

10.5.10 Nested queries

The term ‘nested query’ means running one or more queries based on the

results of a previous query. This is often required when data is selected in a

query and then used for a different type of query, for example producing a query

to summarise data, based upon the results from a simple or complex query.

Task 10q

UseacopyofthedatabasesavedinTask10ptocomparetheaveragepricesof

fridges and freezers made by this company.

Although this appears a relatively easy Task, there are several stages that need

to be completed in order to succeed. To gather the correct data, we need to use

the data from the Product.Price field together with the data from the Product.

Model

field to produce a field called Type which will only be calculated at

run-time. Using this method saves you having an extra field stored permanently

in the database and having duplicated data, as this information is held in the

Model name. All model names with Cool in them are fridges and all model

names with Freeze in them are freezers.

10.5.11Fieldscalculatedatrun-time1

Create a new query using the simple query wizard

(as in previous tasks), selecting only the fields

Type

and Price. Name this query Type and Price. In

the

Design View of the query, in the Field: row of

a new column, enter the text

Type: to create a new

field called Type. This field only appears when this

query is run and is not stored in the database.

Editthis text to become:

Type: R ig ht([Model],6)

whichadds a formula or calculation for this field. This

extracts the last six characters of the model field like this.

The results work well for all the freezers (although it

wouldbe better if it displayed ‘Freezer’ rather than

‘Freeze’), but it leaves extra characters on the fridges

(anduses ‘Cool’ rather than ‘Fridge’), as can be seen here.

A conditional formula needs adding to this field so that

if the result is ‘Freeze’ it displays ‘Freezer’, and if not it

displays ‘Fridge’.

287

10.5 Querying the database

10.5.12 IIF function

The IIF function works in the same

way as the IF function in Excel. Edit

the function in the

Type field to:

Type: IIF(RIGHT([Model],6)="Freeze", "Freezer","Fridge") like this.

Test your results by clicking on

the

Runicon. If the results appear

correct,then remove the tick from

the Model field so that it is not displayed

in the query (even though it will be

used for the calculation). Run the query

againto see the results, which should

looklike this.

Save this query, which will be the first stage of the nested query.

10.5.13 Summarise data in queries

Select the Create tab, then the Query Wizard icon, choose the

SimpleQuery Wizard, then click

. Select from the Tables/

Queries

drop-down list the Query: Type and Price, then move

boththeavailable fields into the Selected Fields box,

then click

.Fromthe next window select

the radio button forSummary data.

The

Summary Options button becomes visible so

click on this.

This task requires ‘the average prices of fridges

and freezers’ so select the tick box for Avg, then

click

to return to the previous window.

Click

, apply an appropriate name for

your query,for example Average prices of fridges

and freezers

, then click

. The average

pricesare shown like this.

This method can be used to summarise data with

thesum, average, maximum and minimum values

aswell as counting the number of items in each

category.

10.5.14 Crosstab queries

Data can also be summarised using a crosstab query which

displays results in a similar format to a pivot table in a

spreadsheet.

Task 10r

Use a copy of the database saved in Task 10q to count the number of people

with each discount value in each area identified in the Address3 field. Use the

Discount values as column headings.

288

10 DATABASE AND FILE CONCEPTS

Before starting a crosstab query, visualise how you want the results to appear.

Work out what row headings, column headings and type of summary data

you require and prepare a small hand-drawn sketch of how you would like the

outputto look. In this case the

Discount field should be the column headings (as

specified in the Task). The sketch should look similar to the one in Figure10.2.

Customer.Discount

Customer.Address

Count function on any customer field

V Figure 10.2 Planning for a crosstab query

To create this table, select the Create tab,

then the Query Wizard icon. From the

New Query window, select the Crosstab

Query Wizard

then click

. For

this query we only need data from the

Customer table so select

Table: Customer

in the top box, then click

Figure 10.2 has only the

Address3 field

as a row heading so move that field from

the Available Fields: list into the Selected

Fields:

box, then click

Select the

Discount field for the column

headings as shown in Figure 10.2 then click

. For the calculation, select any

field in the

Fields: box (in this case Cust_

ID has been used) and in the Functions:

box select the Count function. The tick

in the check box for row sums can be

removed if these are not required.

You can check that the

Sample: diagram matches Figure 10.2 like this.

Click

, give the query an appropriate name

Number with each discount address areas,

then click

. The resulting crosstab may

look similar to this.

Task 10s

CreateanewdatabasefortheTawaraBusCompanytoholdinformationon

its drivers using the files TB-Driver 1.csv and TB-Driver2.csv,whichwillbe

merged into a single table. Identify any duplicate records.

Activity 10g

UseacopyofthedatabasesavedinActivity10ftocreateatwo-dimensionalgrid

showing, for all boats that have not been sold but were made after the year 2010,

thenumberofeachtypeofboatinstockandtheyeartheseweremade.Donot

show any row or column totals.

289

10.5 Querying the database

Start by examining the data files; they

both have identical structures so can be

imported as a single table. Open Access and

click on the

Blank database icon. Select

the folder and add the filename Task_10s

before clicking the

Create button. Select

the

External Data tab, then the New Data

Source icon, From File, then Text File.

Locate the first source file TB-Driver1.csv

so it appears in the File name: box.

Make sure the radio button for Import

the source data into a new table in the

current database

is selected, then click

. Import the data into the database

but let Access select the primary key (if you

select the primary key on the ID field this

will automatically stop any duplicate records on the second import). Repeat this

import for the file TB-Driver2.csv, this time selecting the

Append radio button.

To test if there is duplicated data in this table, we will use a find duplicates

query.

10.5.15Findduplicatesqueries

Duplicate records can sometimes appear in a table, often where more than one

user has input data into an Access database or where two or more external data

files have been joined (appended) into a single table. To check for duplicate data

in a table, first close the table if it is open. From the

Create tab, select the Query

Wizard icon. From the first New Query window select the Find Duplicates

Query Wizard

then click

. Select the table (there is only one so it should

already be selected) then click

Ignore the ID1 field; as this is the primary

key field, it cannot contain duplicate

values. Move the other four fields from the

Available fields: box into the Duplicate-

value fields: box, then click

The results look like this.

One copy of each record should be deleted (and this query run again) to make

sure that the table contains no duplicate data. This is a useful tool for checking

that we have normalised the database, as we shall see later in the chapter.

10.5.16Fieldscalculatedatrun-time2

Task 10t

UseacopyofthedatabasesavedinTask10rtodisplaydetailsoffridgesand

freezers,aswellasthepricepercubiccentimetreofeachitem.

Create a new query using the Simple Query Wizard which contains all the

details of the Product table and open this query in Design View.

Calculating the price per cubic centimetre involves taking the

contents of the

Price field and dividing this by the Capacity field.

Add a new field called Price_per_CC that is calculated at run-time

by entering the text

Price_per_CC: [Price]/[Capacity].

290

10 DATABASE AND FILE CONCEPTS

Create a report for this query. In the Design View of the report, select the

Price_per_CC field, right mouse click and select Properties from the bottom

of the drop-down list. In the

Format tab change the Format to Currency and

the Decimal Places to 2 so that the report looks similar to this:

Activity 10h

UseacopyofthedatabasesavedinActivity10gtocreateareportforyour

manager that displays the make, type, price, length and price per foot for each

yacht and power boat.

Task 10u

UseacopyofthedatabasesavedinTask10ttodisplaytheinvoicenumber,

productmodel,quantity,orderdateandtime,collectiondateandtime,customer

name, and number of days taken from placing the order until it was collected.

Create a new query using the Simple Query Wizard which

contains all the fields specified in the Task. Open this query

Design View. Calculating the number of days between

the order and collection involves subtracting the order date

from the collection date. Add a new field called Days that is

calculated at run-time by entering the text

Days: [Collect_Date]-[Order_Date] like this.

Create the report for this query which should look similar to this. When this

report is examined it is clear that one order either took a very long time to

process or there was an error in the data entry.

Task 10v

UseacopyofthedatabasesavedinTask10utodisplaytheinvoicenumber,product

model, quantity, order date and time, collection date and time, customer name, and

how many days have passed since the order was collected from the company.

Using similar methods to those described in Task 10u, create and open the

query in

Design View. Calculating the number of days that have passed since

the item was collected requires Access to use today’s date for the query (which is

accessed using the NOW() function) and subtract the collection date from this.

We only require the days part of this calculation so must select the integer part

(using the INT function), as the decimal part holds the time values. Add a new

291

10.5 Querying the database

field called Days that is calculated at run-time by entering the text

Days: INT(Now()-[Collect_Date]) like this.

Create the report for this query which should look similar to

this,although the days value will be different depending upon

today’s date.

10.5.17Calculatedcontrols

Calculations can also be performed within a report, often to calculate the

sum, average, maximum or minimum values of the selected data or to count

the number of items selected within the report. All of these functions can be

produced within the report in Access.

Task 10w

OpenthefilesavedinTask10v.EditacopyofthereportcreatedforTask10tto

display, at the bottom of the report:

» theaveragepriceperCC

» the maximum and minimum price per CC

» thenumberofitemsinthisreport.

Copy, paste and rename the report you created for

Task 10v. Open this report in

Design View.

Click the left mouse button on the bottom edge

of the Report Footer and drag this down about

twocentimetres, so that this footer is now visible.

Select the Design tab, move to the Controls

section and select the Text Box icon.

Move down into the

Report Footer and click the

mouse button and drag to place a new control, in

this case a text box directly below the Price_per_

CC column. This positioning is important as this

control will be used to calculate the average Price_

per_CC

for the data in this report.

Activity 10i

UseacopyofthedatabasesavedinActivity10htocreateareportforyour

manager that selects all the boats that were bought by the company and have

not yet been sold. Display the make, type, price, the date bought, today’s date,

andthenumberofdaysthatboathasbeenheldinstock.

292

10 DATABASE AND FILE CONCEPTS

If the Property Sheet is not showing,

right click the mouse button on the

Unbound control that you have just

created, then select Properties from

the bottom of the drop-down menu. In

the

Property Sheet, select the All tab,

find the

Control Source section and

type the formula

=AVG([Price_per_CC]) into this row.

The round brackets are part of the

AVG function; the square brackets tell

Access that this is a field. Format this

control to Fixed format with 2 decimal

places like this.

The

Control (in this case Text19) and

Property Sheet will change to this.

Click in the label for this control (in

this case called Label20) and change

the

Caption like this.

Stretch the label so that all the

Captiontext is fully visible, like this.

Change to the Report View and make sure that the control is in the correct

place and appears to give the right answer (it is not too large or too small).

Rather than repeating this process three more times, it will be quicker to copy

and paste these controls and edit each one to give the required results. Use the

lasso tool to highlight both the Text Box and its Label. Use <Ctrl C> to copy,

then use <Ctrl V> to paste the copies of these controls. Using <Ctrl V> pastes

the new controls directly under the existing ones and you do

not need to reorganise the controls. It also extends the bottom

of the

Report Footer as needed. If you right mouse click and

use

Paste from the drop-down menu, this pastes the controls

in the top-left-hand corner of the Report Footer and you then

have to drag and position each set of controls. Repeat <Ctrl V>

until you have four sets of controls like this:

In the last three controls containing labels, change the

Captions to Maximum price_per_CC, Minimum

price_per_CC and Number of items. Select the second

Text Box (for the maximum price per CC) and change the

formula so that it becomes =MAX([Price_per_CC]). Change

the formula for the minimum price per CC so that it becomes

=MIN([Price_per_CC]). In the final control to count the

number of items, change the formula so that it becomes

=COUNT([Price_per_CC]). The controls should look like this:

Advice

The SUM function can be used to calculate the total for any field, using the same

method as

AVG, MAX, MIN and so on.

In the Property Sheet pane for the final Text Box, change the Format back from

Currency to a General Number. Set the Decimal Places for this control to 0.

293

10.6 Sorting and grouping data

Checkthe layout and calculations in ReportView.

The completed calculations look like this.

Advice

Makesurethateachcontroliswideenoughtoshow

the formula in full.

10.5.18 Query selection

Selection of the correct type of query from numeric, alphanumeric, date and type,

simple, complex, nested and crosstab to answer a specific question is critical. If the

question requires summary data, consider if a two-dimensional table is required.

If it is, then a crosstab query is the most appropriate to use; if it is not, then one

(or more if nested queries are required) simple (or complex) query should enable

any other questions to be answered. If only one field needs searching then it will

be a simple query but if more than one field needs to be searched on it is called a

complex query. It could be that the same criterion is to be used every time. If you

are searching for the number of people who live in Bengaluru, a static query should

be used. However, there will be times when the criterion needs to be changed every

time the query is run. For example, you may need to find the number of people

who live in different cities. In this case a dynamic query should be used. If data is to

be entered by the user when a query is run, then a dynamic query is required but if

the criteria need embedding into the query, so that the same query is run repeatedly

even if the data in the tables change, then use a static query.

Data can be sorted into order, or collected into groups. Although Access has the

ability to sort your data in both tables and queries, it is often easier to save the

sorting until the report is created. Both sorting and grouping can be included in

the report wizard, or they can be added to a report in design view.

10.6 Sorting and grouping data

10.6.1Sortingdatausingthereportwizard

Task 10x

OpenthefilesavedinTask10w.Createanewcopyofthe

reportforTask10t,sortingthedataintodescendingorder

of price, then into ascending order of capacity.

Use the query created in Task 10t. To produce this report,

select the Create tab and click on the Report Wizard

icon. In the Tables/Queries box select the query used for

Task10t. Select all fields using the double arrow key and

then click on

twice to obtain this view:

Activity 10j

OpenacopyofthedatabasesavedinActivity10i.Editacopy

of the report created for Activity 10h to display for the yachts

andpowerboatsatthebottomofthereportthe:

» averagepriceofaboat

» averagepriceperfoot

» maximumandminimumpriceofaboat

» numberofboatsinthisreport.

Make sure that you use appropriate formatting for all data.

294

10 DATABASE AND FILE CONCEPTS

Use the drop-down lists to select the Price field, then the Capacity field. For the

Price field, click on

to the right of this field and it will toggle (change) to

. When these field have been set as shown, click on .

Run through the final stages of the wizard, giving this report a suitable name. This

process is the same for other data types such as dates. You will notice that the report

wizard changes the order of the fields to place the sorted fields first, like this:

10.6.2Sortingdatausingdesignviewinareport

Task 10y

OpenthefilesavedinTask10x.EditthereportcreatedforTask10w,sorting

data into descending order of price, then into ascending order of capacity.

Open the report created in Task 10w in Design View. In the Grouping &

Totals section select the Group & Sort icon.

This opens the

Group, Sort and Total pane at the bottom of the window.

Select the button for Add a sort. Select the Price field from the drop-down

list, then change the sort from ‘from smallest to largest’ to from largest to

smallest using the second drop-down menu. Add a second sort using the Add

a sort

button. Select the Capacity field from the drop-down list; there is no

need to change the sort type as it is already from smallest to largest (to match

the ascending order required by the Task). The pane looks similar to this:

This has sorted the data as required but has not changed the order of the fields

in the report like this:

Activity 10k

OpenacopyofthedatabasesavedinActivity10j.Editacopyorrecreatethe

report created for Task 10h sorted into ascending order of type and then

descending order of price.

10.6.3 Grouping data

Grouping allows data to be collected and displayed together, not only as

individual records but also with totals for each group of data.

Task 10z

OpenthefilesavedinTask10y.Createareportforthemanagerthatdisplaysallthe

details of all the products. Group this report by product type (whether the product is

afridgeorfreezer).Includetheaveragepriceofallfridgesandfreezers.

295

10.6 Sorting and grouping data

Create a new query using the Simple Query

Wizard which contains all the fields from the

Product table and a calculated field called Type,

as we did in Task 10q, using the formula Type:

IIF(RIGHT([Model],6)="Freeze","Freezer","Fridge")

Save this query with an appropriate name. Select the

Create

tab, then Report Wizard to create a new report, then

select the query you have just saved, moving all fields from

the Available Fields: to the

Selected Fields: box then click

. The Task requires us to group the report by

product type, so double-click the left mouse button on

Type

to move it as a new group like this.

Click

three times, give the report an appropriate

name, select Modify the report’s design, then click

. Check all data within all fields is fully visible,

then (if the Group, Sort and Total pane is not visible) select

the

Group & Sort icon. In the Group, Sort and Total

pane select the option for More alongside the grouping.

This extends the grouping options for

Type like this.

Select the drop-down menu for

with no totals like this.

In the

Totals section, edit the Total On box to become Price

and the Type to be Average. Tick the box where you would like

these average values to appear, in this case in the group footer.

Select the

Design tab, move to the Controls section and

select the Text Box icon.

Move down into the

Type Foote r, click the mouse button and

drag to place a new label to the left of the Average price control.

Change the Caption of this label to Average price. Save this

report which should look like this.

Activity 10l

OpenacopyofthedatabasesavedinActivity10k.Createareport

for the manager that displays all the details of all the boats that

are unsold, grouped by type then by catamaran, then sorted in

ascending order of price, and calculate a grand total and the total

price for each type.

296

10 DATABASE AND FILE CONCEPTS

10.7Creatingadataentryform

Task 10aa

OpenthefilesavedinTask10z.Addnewdataentryformstocollectdataforall

fields in the Customer and Product tables. These will be used by employees of

the company.

The best way to create a data entry form is

to select the

Create tab, then click on the

Form Wizard.

The Form Wizard window opens. Select

the table which holds the fields that

you will include in the form. It is often

sensible to have a new form for each table, but

if a form needs fields from more than one table

then place these fields in a query and select

the query. For this Task we will selectthe

Customertable first. Move all fields across

fromthe Available Fields: to the Selected Fields:

box like this.

Then click

. Choose that layout of the

screen that you require (I chose Columnar for

this Task) then click

again. Set the

title of the form to Customer (Access may do

this for you) then click

to open the

form like this.

The bottom of the form has a navigation bar

which can be used to move from record to record

likethis:

Consider the person who will use the form. This Task states that the form

will be used by employees, who will almost certainly have had some training,

so less detailed instruction about form completion will be required than for

a form to be completed by, for example, the customer themselves. In the case

of a customer entering data rather than an employee, instead of using labels

containing field names, each label must describe the data to be entered and

how to enter the data for each field. The form design must include appropriate

font styles and sizes, with easy-to-read text, taking account of the user’s age (for

example a database for younger children will require simple language and large,

simple font styles).

Go to first

record

Current

record

New

record

Last

record

297

10.7 Creating a data entry form

Script/cursive fonts which are more difficult to read should not be used unless

specifically required by the company. Spacing between fields is vital if a form is

to contain different blocks of information. For example in this form a block for

the name, one for the address and one for the discount would be appropriate,

with similar fields grouped together on the form. It is also important to assign

suitable character spacing for each data item on the form. Access initially sets the

length of all alphanumeric (text) fields to the field size (length) assigned when

the table is created. However, we can see from this example that the

Discount

field control is far too large for the data that will be stored. It is important to

use the white space on the form to separate these grouped areas but also to

make sure that there are no large areas of white space on the finished form.

With all these considerations in mind we will edit this form to make it more

suitable for the employees to use. The title of the form gives no instruction to

the user as to its purpose. Change from

Form View to Design View. Edit the

Caption in the Form Header from Customer to an appropriate description like

‘Form to enter customer details’.

Add some instructions for the user (this is more important if other people are

using the form, rather than just employees). Drag the cursor so that it lassos

all the controls together for each group on the form. Increase the white space

between the groups. Increase the font size of all controls from 11 points to 14

points (as there is no shortage of space on the form). Reduce the size of the

control for the data entry on the

Discount field. All labels in the Detail section

can have the text right-aligned (change this in the Properties window after

lassoing all the labels). Edit the Caption for the label used for the

Cust_ID field to make it more meaningful, for example ‘Customer

ID number’. Add a label to the right of this field and add

appropriate text. The final stage must be to make sure the person

entering the data knows that the

Cust_ID field is the key field and

cannot be left blank. Select the label icon and drag a new label onto

the form to create a new control. Type the text into this control.

With the control selected, right mouse click and select

Properties;

these can be used to change the font colour to red. Thedesign view of the form

has now been changed from this to this:

This diagram shows the edited form in

Form View.

298

10 DATABASE AND FILE CONCEPTS

Repeat this process for the Product table to create

the second data entry form. There are no obvious

groups for this data although appropriate lengths

for the controls will be needed. Again, because the

data entry will be completed by an employee, there is

no need to give details of data formats (for example

the product codes are all the letter X followed by

four digits). Apart from adding validation rules to

the tables, there are two fields where data entry can

be quicker, and data entry errors can be reduced,

by developing the controls within the form. The

Energy_Rating could be restricted to either A+ or

A++, as the company only makes items with these

ratings, and the Colour could be White, Silver or

Anthracite (but extra colours may be added later).

In order to do this, we will replace the controls for

Energy_Rating and Colour with list boxes. Use the

lasso method to move these two controls and their

labels down; you may need to drag the form footer

lower to create the space, like this.

From the

Design tab, in the Controls section, select

the drop-down list for

More options.

Select from this drop-down list the icon for

a List Box.

Advice

Usingalistboxwillrestrictdataentrytoonlyitemsfromagiven(or

stored)list;ifyourequireausertobeabletoenterotherdata,choosea

combo box.

Click and drag to draw the list box

control onto the form. The List Box

Wizard

opens for this. For the energy

rating there are only two options,

which are unlikely to change, so we

will select I will typein the values

that I want

, then click

Enter the values like this.

Click

and select the radio

button for Store the value in this

field: before selecting the Energy_

Rating

field from the drop-down list.

Click

and type Energy

rating for the list box label, then click

. As the company may add

extra colours to its range of products,

we will create a new table called

Colour and store this in the database. Create a single primary key field called

Colourandenter each colour (Anthracite, Silver and White) as new records in

this table.

299

10.7 Creating a data entry form

Add a new list box control to the form and when

the wizard opens, select I want the list box to get

the values from another table or query

, then click

. Select the option for Table: Colour

then click

. Select this field, then click

three times. Select the radio button

for Store the value in this field: before selecting

the

Colour field from the drop-down list. Click

and type Colour for the list box label,

then click

. Test the form in Form View

to make sure the correct data is being displayed for

this record, return to

Design View and remove the two original

controls and their labels so that your form may look like this.

10.7.1Creatingasub-form

Task 10ab

OpenthefilesavedinTask10aa.AddanewdataentryformfortheInvoice

table which contains a sub-form for the Customer table. These will be used by

employees of the company.

To complete this Task, we will place a

customer’s form inside a new invoice form.

If we use the existing

Customer form

some labels would be inappropriate. Make

a copy of the Customer form, rename it

Customer_SubForm and edit it to look

like this.

Create a new data entry form for the

Invoice table using the method above.

This time we will group the form into

the order details, collection details and

customer details. The customer details

will be held in a sub-form. For each

section of the form, add a new label to

help the user find the data on the form

easily. These labels will display

Order

details, Collection details and Customer

details. With the form in Design View,

to add the Customer sub-form select the

SubForm icon.

Click and drag the form box below the

Customer details label, allowing plenty

of space for all fields within the Customer

table to be visible.

300

10 DATABASE AND FILE CONCEPTS

From the SubForm Wizard select the radio

button for Use an existing form and select the

Customer_SubForm, then click

. Select

the radio button for Choose from a list, then

Show Customer for each record in Invoice using

Cust_ID, then click

. Name this sub-

form

Customer_SubForm, then click

The Invoice form looks like this.

You will notice that the background colour of

three labels has been changed (in the properties) to

clearly identify the groups of data within the form.

Using radio buttons

Task 10ac

OpenthefilesavedinTask10ab.Editthedatabasesothatthecapacityofthe

product can be added using a radio button rather than typing the data. The

capacity of all models is 185, 200, 240, 250, 300, or 400 cubic centimetres. Edit

thedataentryformfortheProducttabletouseradiobuttonstoselectoneof

these values.

Open the Product form in Design View. Delete the control and label for Capacity.

Select the Design tab, then the Option Group icon from the Controls menu.

Click in the Detail section and drag the Option Group frame, making it large

enough so that a number of radio buttons and their labels can be placed within

it. The

Option Group Wizard window opens. Enter the

label names in the table like this.

Click

, make sure the radio button for No, I

don’t want a default

is selected, then click

Edit the values for each button so that they are 185, 200,

240, 250, 300 and 400 respectively, then click

Select the radio button for

Store the value in this field:

and select the Capacity field from the drop-down list,

then click

. Leave the selection on Option

buttons

, then click

. Change the caption to

Capacity of the

product

, then click

. The

Design View of the

form will look like

this and the edited

formin Form View

will look similar to

this.

301

10.8 Designing a switchboard

10.8Designingaswitchboard

A switchboard (or menu) can be added to your database, so that it appears when

the database is opened. This makes it easier for the user to add, edit or search

for data. Once the switchboard has been designed and created, users with little

database experience can usually use the database with ease. The switchboard

will often include a title, instructions for the user and a number of navigation

buttons to open forms and reports. It is important to design the switchboard so

that it is easy to read, with well-spaced-out buttons and no large areas of white

space. Company logos or other images can be included on the switchboard.

Grouping similar items together is desirable.

Switchboards are usually designed on paper before they are created.

Task 10ad

OpenthefilesavedinTask10ac.Designandcreateaswitchboardthat

allowsuserstoenteroreditdetailsofcustomers,invoicesandproductsas

well as produce reports to display the models that are fridges, the products

made in Anthracite and the price per cubic centimetre for each product.

Display the image freezer.png ontheswitchboard.Savethedatabaseas

Task_10ad.

This switchboard will

be designed to group

the title, instructions,

forms, reports and

other items into

different areas of the

switchboard. As the

image freezer.png is

required, the aspect

ratio of this image may

determine the page

layout, in this case it has

portrait orientation so

would ideally fit down

the left or right side

of the form. The form

design may look like

this.

Even though this is only

a very rough sketch, it

allows the person creating the switchboard to plan the relative amounts of white

space required for this design.

Activity 10m

OpenacopyofthedatabasesavedinActivity10l.Createasingledataentryform

with a sub-form. Make sure that the forms include efficient methods of data

entrythatwillhelptoreducedataentryerrors.Discountvaluesof0,5and10%

are offered to customers; three types of boat are sold at the moment, but other

types may be sold in the future.

Freeze – Air

Instructions for using this switchboard

Add/Enter data

Customer

Invoice

Products

Reports

Fridges

Products in Anthracite

Price per CC

Navigation

Exit switchboard Close DB

Image:

freezer.png

302

10 DATABASE AND FILE CONCEPTS

10.8.1Creatingaswitchboard

Although Access has a Switchboard Manager, it is often quicker and easier to

create your own switchboards/menus using Access forms. To create this form,

select the Create tab, then the Form Design icon. The blank form is displayed

and Access defaults to the Design tab. For the page title, a text box is required

so select the

Label control.

Drag the cursor over the page where this text is to be placed and enter the text

‘Freeze-Air Main menu’. On the right in

Property Sheet (click the right mouse

button and select Properties if this is not visible) select the Format tab and

enter an appropriate

Font Size. Use

the other property settings to adjust

the text alignment, foreground and

background colours, like this.

Use similar techniques to add the instructions and three group titles. From the

Controls section, select the icon for a Button

and drag out the rectangle to make the shape of a button. As you release the

drag handle the Command Button Wizard opens.

This command button will link to the Customer

form so in the

Categories: box select Form

Operations and in the Actions: box select Open

Form, then click

Select the Customer form, then click

Select the radio button for

Open the form and

show all records, then click

. Select

the radio button for Text and enter the text

Customers (there is no need to add a form asthis

button sits in the forms group of the menupage),

then click

. Enter a meaningfulname

for this control likeCustomers, then click

. The button is created,

but it may need editing to give it the

appearance that you require. Resize

the control using the drag handles.

In the

Properties Sheet add a

border, change theforeground and

backgroundcolours, font size andtext

alignment as required, likethis.

Repeat this process for the other two

form controls, selecting the Invoice

and Product forms for the appropriate control. Use similar methods to add the

three buttons for the reports, selecting

Report Operations and Open Report

before selecting the appropriate report for each button.

The Exit menu button is created using a Form Operation to Close Form. The

final button used to close the database is created in the Categories: box using

Application and Quit Application.

303

10.9 Importing and exporting data

The image freezer.png is added to the form using the image icon.

Select this icon and drag the cursor to create the size of the image frame. As

you release the drag handle the

Insert Picture window opens. Select the image

freezer.png then click on

. The Design View and finished form will

look like this.

To improve the database, controls can be added to the other three forms to

close the form and allow a user to return to this main menu from each form.

Save the database as Task_10ad.

10.9 Importing and exporting data

10.9.1 Importing data

We learnt how to import data at the start of the chapter. The only input formats

that you are likely to experience are comma separated values (.csv) and text (.txt)

files; both have similar properties. Should source data be presented in

rich text

format (.rtf), it will need to have all formatting removed so that it can be saved

in text (.txt) format before it can be imported.

10.9.2 Exporting data

Tables, queries and reports can all be exported from Access for use in other

applications. Make sure that the object to be exported (it is the same for tables,

forms, reports and so on) is not open within the database. Click the right mouse

button on the object to be exported, then select the Export option from the

drop-down menu like this.

When you click on

Export, a sub-menu with the type of export appears. Select

the file type you require from this list, which is likely to be .pdf or .rtf format

for a completed document. If the exported data is to be used for another

purpose (for example as a source data for a mail merge) it may be exported in

.txt or even Excel format. If the data exported is required in .csv format, select

the option for .txt and change the file extension from .txt into .csv. In each case,

you must enter an appropriate file name then click on

Activity 10n

OpenacopyofthedatabasesavedinActivity10m.Designandcreatea

switchboardthatallowsuserstoenteroreditdetailsofboatsandcustomers,

as well as produce reports to display the boats bought in the year 2020 and the

grouped report for all unsold boats. Apply an appropriate colour scheme and

display the image boat.jpg on the switchboard.

304

10 DATABASE AND FILE CONCEPTS

10.10 Normalising data

Normalisation is a technique used to reduce the duplication of data in a

relational database. It helps to:

» organise data in an efficient way

» remove redundant (duplicated) data

» make sure that only related data is stored in a table.

This technique is a multi-step process, where each step has a rule that improves

the efficiency of the database. These rules are called the Normal Forms,

numbered from 0 to 5. We will only study un-normalised data (0NF) to Third

Normal Form (3NF). Fourth and Fifth Normal Form (4NF and 5NF) are

beyond the scope of this book and exam.

10.10.1 Un-normalised Form (0NF or UNF)

If a database is not normalised it is called an un-normalised database, often

shortened to 0NF or sometimes UNF. This is often a flat-file database (a

single table) that contains duplicated data (which is called redundant data) and

complex data structures (more than one item of data, otherwise known as non-

atomic data) stored within a single field.

Task 10ae

Changethefollowingdataonstudents,theirhouseandtheircourses,intoFirst,

Second and Third Normal Form.

Student name House Course

Jane Smith York Computing, Mr Brown, Room 53, Maths, Miss White,

Room 104, Statistics, Miss White, Room 104

Ruksana Patel Lancaster Business, Mrs Grey, Room 3, Maths, Miss White, Room

104, Science, Mr Green, Room 24

Jamal Aziz York Computing, Mr Brown, Room 53, Maths, Miss Black, Room 102

Jet Li Xa Lancaster Maths, Miss Black, Room 102, Science, Mr Green, Room 24

Jane Smith Edinburgh Statistics, Miss White, Room 104

Each teacher teaches in a single classroom.

Save your work at each stage as Task 10ae_1, Task 10ae_2 and Task 10ae_3.

Create a new word-processed document which contains the data structures for

this task. Save this as Task 10ae_1.

10.10.2 First Normal Form (1NF)

The rules for a database normalised to 1NF are:

» All data is stored in a database table.

» A unique key must exist in each table (often a primary or compound key).

» Only atomic data is stored (which is data stored to the lowest level of data

and cannot be broken down any further).

» Each field has a unique name.

» Each record is unique (so there are no repeated rows in any table and a

primary/compound key exists in each table).

» There are no repeating groups of columns.

To convert the data in Task 10ae into 1NF we must first ensure that a unique

key exists in each table. There are two students with the same name. Looking

at this data it is possible (although unlikely) that two students with identical

names, houses and courses could exist, so a new field is required as a primary

key field. We will call this field

St_ID. Each St_ID will be a unique number.

305

10.10 Normalising data

The data given contains non-atomic data. The student’s name can be split into

their Forename and their Surname. The course details can be split into the

Subject, Teacher and Room. Each course should also have its own unique ID

(the data presented is not unique, for example Mr Brown may take two different

Computing classes in the same classroom at different times). We will call this field

Course_ID. For the key to be unique we have a compound key with both St_ID

and Course_ID.

This data would therefore satisfy First Normal Form as all data is in a table,

there are no repeating groups of columns, each record is unique, each field has

a unique name, and all data is atomic. There are two key fields, one for the

students and one for the courses.

St_ID Forename Surname House Course_ID Subject Teacher Room

0001 Jane Smith York 1042 Computing Mr Brown 53

0001 Jane Smith York 1400 Maths Miss White 104

0001 Jane Smith York 9961 Statistics Miss White 104

0002 Ruksana Patel Lancaster 9606 Business Mrs Grey 3

0002 Ruksana Patel Lancaster 1400 Maths Miss White 104

0002 Ruksana Patel Lancaster 5000 Science Mr Green 24

0003 Jamal Aziz York 1043 Computing Mr Brown 53

0003 Jamal Aziz York 1402 Maths Miss Black 102

0004 Li Xa Jet Lancaster 1401 Maths Miss Black 102

0004 Li Xa Jet Lancaster 5000 Science Mr Green 24

0005 Jane Smith Edinburgh 9961 Statistics Miss White 104

Save your document.

Why is this data in First Normal Form (1NF) and not in Second Normal

Form (2NF)?

This data is not in 2NF because the:

» Subject, Teacher or Room fields are not dependent on the St_ID field

» Forename, Surname or House fields are not dependent on the Course_ID

field.

10.10.3SecondNormalForm(2NF)

The rule for a database normalised to 2NF is that the table must be in 1NF and any

non-key attributes that only depend on part of the table key are placed in a new table.

Looking at this data, it is grouped into two types of data, the data relating to

each course and the data relating to each student. The data must be split into

three tables: courses, students and a table to link the two to meet 2NF. The first

table will relate to the courses.

Course_ID Subject Teacher Room

1042 Computing Mr Brown 53

1043 Computing Mr Brown 53

1400 Maths Miss White 104

1401 Maths Miss Black 102

1402 Maths Miss Black 102

5000 Science Mr Green 24

9606 Business Mrs Grey 3

9961 Statistics Miss White 104

306

10 DATABASE AND FILE CONCEPTS

You will notice that we have sorted the data to help ensure there are no

duplicated records. The second table will consist of the students.

St_ID Forename Surname House

0001 Jane Smith York

0002 Ruksana Patel Lancaster

0003 Jamal Aziz York

0004 Li Xa Jet Lancaster

0005 Jane Smith Edinburgh

The third table will link each student to each course.

St_ID Course_ID

0001 1042

0001 1400

0001 9961

0002 1400

0002 5000

0002 9606

0003 1043

0003 1402

0004 1401

0004 5000

0005 9961

Save your document as Task 10ae_2.

Why is this data in Second Normal Form (2NF) and not Third Normal

Form (3NF)?

This data is not in 3NF, because the Teacher and Room fields are dependent

on each other, as each teacher always teaches in the same room.

10.10.4 Third Normal Form (3NF)

The rule for a database normalised to 3NF is to make sure that any non-key

attributes that are more dependent on other non-key attributes than the table

key, are removed to a new table.

Looking at this data, the course table must be split into two tables: one for the

courses and one for the teachers to meet 3NF.

The first table will relate to the courses.

Course_ID Subject Teach_ID

(Foreign

)

1042 Computing T2

1043 Computing T2

1400 Maths T5

1401 Maths T1

1402 Maths T1

5000 Science T3

9606 Business T4

9961 Statistics T5

307

10.10 Normalising data

The second table will relate to the teachers.

Teach_ID Teacher Room

T1 Miss Black 102

T2 Mr Brown 53

T3 Mr Green 24

T4 Mrs Grey 3

T5 Miss White 104

The fourth table will link each student to each course.

St_ID Course_ID

0001 1042

0001 1400

0001 9961

0002 1400

0002 5000

0002 9606

0003 1043

0003 1402

0004 1401

0004 5000

0005 9961

Save your document as Task 10ae_3.

Sometimes when you change the data into one type of form, it is also set at the

next level. For example, if data was changed into 2NF, it may also set as 3NF

without any other changes required, if it also meets the rules for 3NF.

The third table will relate to the students.

St_ID Forename Surname House

0001 Jane Smith York

0002 Ruksana Patel Lancaster

0003 Jamal Aziz York

0004 Li Xa Jet Lancaster

0005 Jane Smith Edinburgh

Activity 10o

Change the following data on customers, the company they work for and their

contact details, into First, Second and Third Normal Form.

Customer

name

Company Contact details (3 x address, zip code, 2 x telephone

numbers)

Surjan Patel Binaccount Manley Hall, Manley, Tawara, 4303, 05551 275236, 05551

175394

Jay Murray Tawara Bus

Company

14 Main Street, Grovecourt, Tawara, 4303, 05551 245975,

05551 175401

Lucy Murray Easy Doors 41 Dilbridge Road, Dockside, Port Peppard, 4302, 05553

128835, 05553 245046

Surjan Patel DBM Systems 56 Green Lane, , Littleton, 4254, 05554 168431, 05554 179139

Joseph

Norris

Tawara Bus

Company

14 Main Street, Grovecourt, Tawara, 4303, 05551 245975,

05551 175401

Victoria

Wilkins

Tawara Bus

Company

14 Main Street, Grovecourt, Tawara, 4303, 05551 245975,

05551 175401

Save your work at each stage as Activity 10o_1NF, Activity 10o_2NF and

Activity 10o_3NF.

308

10 DATABASE AND FILE CONCEPTS

10.10.5Advantagesanddisadvantagesofnormalisingdata

Less money needs to be spent on storage as the file size is smaller due to there

being no redundant data, but the resulting larger numbers of tables require

more relationships to be designed. This takes more time, when designing a large

database, as well as requiring workers to have greater knowledge. This, in turn,

may lead to workers having to be brought in from other companies and adds to

the cost.

It groups data logically as well as reducing inconsistent data in tables by

enforcing referential integrity, but having more tables than an unnormalised

database makes it difficult to monitor where particular data is.

With the larger number of tables, setting up complex queries can be more

difficult, although searching on one table will be much faster as there is less data

to go through. The processing of data can be slower with a greater number of

tables and links to navigate.

Any changes which are needed in certain records can be made automatically to

any related records.

With no duplicated data there will be fewer errors in the data and making

changes to a table is easier as there is less data to alter.

Removing non-atomic data may not always be a good idea if putting data into

separate fields serves no useful purpose.

10.10.6NormaliseadatabasetoaspecifiedNormalForm

While this can be completed to 1NF or 2NF, most tasks are likely to require a

solution to 3NF.

Task 10af

Allemployeeswhoworkintheskischool,arestaurantorhotelinEllmau,

Austria are to have their details stored in a database. All database field names

must be short, meaningful, consistent in style, and contain no spaces or

underscores.

Using suitable software, open and examine the data in the files ellmau.csv and

jobs.csv.

Use this data to create a relational database normalised to 3NF.

Save your database as Task 10af.

Open and examine the files ellmau.csv and jobs.csv in Excel. In the jobs data file,

the column headings that will become field names are long and contain spaces

and so do not meet the requirements of the question. These can be replaced

with shorter text like JobCode and Description before resaving the data file in

a new folder (never overwrite the original source files in case you need to refer

to them again). The data in this file is all atomic and does not need to be split

into other columns. In the ellmau data file, the employee’s names are not atomic

data, as they contain both forename and surname. This needs splitting into two

columns. Insert two new columns before column A. In cell L2, place the formula

=LEFT(C2,FIND(":",C2)-1) to extract the forename of this employee. In

cell M2, place the formula =RIGHT(C2,(LEN(C2)-FIND(":",C2)-1))

to extract the surname for this employee. Replicate these formulae down

to row 171. Copy the contents of cells L2 to M171, move the cursor into

cell A2, right click the mouse button and from the drop-down menu,

selecttheicon for

Paste Special (values).

309

10.11 Creating a data dictionary

Advice

Adifferentwayofsplittingthenamesintotwocolumnsistoinsertanewcolumn

between A and B. Highlight column A and select from the

Data section, Text to

Columns

.SelectDelimited, then a colon as the delimiter.

As there is no key field on this table (because theoretically there could be two

people with the same names, dates of birth and jobs), insert a new column A

and add an ID column. Add a unique number for each employee in this column.

Enter in cell B1 the text Forename and in cell C1 the text Surname. Delete

columns D, M and N. Edit cell E1 to

JobCode, cell F1 to Employer, cell

G1 to EmpAdd1, cell H1 to EmpAdd2, cell I1 to EmpType and cell J1 to

EmpCode. This data is now structured in 1NF. Save the file.

To change this into 2NF, it needs splitting into a table for each employee

and for each employer. Save the file as two different files, one with the name

Employer and one with the name Employee. Open the Employee file and

delete columns F to I inclusive. Save this file. Open the Employer file and delete

columns A to E inclusive. Move the new column E so that it becomes column

A, as this is the code for each employer. Sort the data into ascending order on

the EmpCode column (do not include row 1 in your sorted data). Keep one row

for each EmpCode and delete any other rows containing that code so that no

duplicated rows are present. Savethe file.

To change this into 3NF, the data in the Employer file must be split into two

tables, as the EmpType column contains only three values, which would be better

placed in a separate table with a simplified key. Save a copy of this file as Ty pe.

Open the Type file, delete columns A to D and insert a new column A. Delete

all duplicated rows of data. In cell A1, add the text ‘EmpTypeCode’ and copy the

first letter from the data in column B into the cells in column A, like this.

Save this file. In the Employer file, replace

in column E the text Hotel with the letter

‘H’, Restaurant with the letter ‘R’ and Ski

school with the letter ‘S’. Save this file. Use

these data files to create a new relational

database with four tables like this.

10.11Creatingadatadictionary

A data dictionary is a file (often a document) containing descriptions of and

information about the structure of data in a database. It is often designed

before a database is created as an alternative to an entity relationship

diagram.

10.11.1Componentsofadatadictionary

The data dictionary should always contain the table names, field names, data

types (and sub-types where appropriate), field size (for alphanumeric fields),

the primary key and foreign key fields, and other metadata which could

include input masks, validation rules or default values, if applicable. Some of

this data, for example data types and field lengths, can be worked out from

data files that have been given to you.

310

10 DATABASE AND FILE CONCEPTS

10.11.2 Create a data dictionary and select appropriate

data types

Task 10ag

UsingthedatabaseyoucreatedinTask_10af,createaword-processeddata

dictionary for this database. Save your database and your data dictionary with

the filename Task _10ag.

To create this database to 3NF, four data tables are required. These relate to

the employee, the employer, jobs and type of employer. For each database table,

create a table in your word-processing software. Add the table name to the top

of the table and list all the field names in the left column like this:

Table name: Jobs

Field name Data type Field

size

Key

field

Metadata (including: input mask,

validation rules, default value, etc.)

JobCode Alphanumeric/Text

Description Alphanumeric/Text

Examine the data for this table and it can be seen that the data for both fields

contains only letters, so an alphanumeric data type will be needed. For each

field, identify the number of characters for the longest item of data. For the

JobCode field, there are two data items which are three characters in length;

these are ‘TBI’ and ‘TSI’, so record the field size as three characters and set

the field size to this length in the database. For the

Description field, the

longest data item is ‘Trainee Snowboard Instructor’ which is 28 characters in

length, so record the field size as 28 and set the field size to this length in the

database.

Advice

Youwillneedtoremoveanyrelationshipstoothertablesinyourdatabasebefore

editingthefieldsizewithinthistable.Thesecanberecreatedwhenthefieldsizes

have all been set.

The primary key field for this table will be the JobCode, because it contains

unique data, so indicate in the key field column that this is the primary key.

Asthere is no other metadata for this table (input masks and validation rules

would not be appropriate and no field requires default values), complete the

tablelike this:

Table name: Jobs

Field name Data type Field size Key field Metadata

JobCode Alphanumeric/Text 3 PK

Description Alphanumeric/Text 28

10.12Fileanddatamanagement

Program and data files are stored with different file types and each file is stored

with a file extension, which informs the operating system which program to use

to open the file. File formats fit into different categories, including proprietary,

open source and generic file types.

311

10.12 File and data management

10.12.1Differentfiletypesandtheiruses

Program files are normally saved with .exe file extensions. These are often

opened by clicking on a program icon; examples include Microsoft Word, Access

and Excel.

Proprietary file formats are formats belonging to a company, organisation

or individual and are often specific to a program created and copyrighted by

that company, for example current versions of Microsoft Word save files with

a .docx file extension (earlier versions of the program were saved with a .doc

extension). These files contain your data (in this case documents) but they are

stored in the formats of the particular package and contain more information

than just the contents that you can see when the document is opened. The

exact details of the data stored, the encoding and its structure within the file

are often kept secret and/or patented by the company or organisation which

created the software. Proprietary software allows only people with licences to

use it.

Open source file formats are formats used for storing data that are published

for anyone to use. They can be opened by both proprietary and open source

software. The details of the way the data is stored are available for all users

and software developers, rather than just the organisation which created the

structure. Open source file formats tend to be free, because not everyone can

afford proprietary software, and the code is open to inspection and sometimes

to amendment without breach of copyright legislation. Users can transfer files

from a work computer to a home computer using open source software more

easily, particularly if they do not have proprietary software on their home

computer.

Generic file formats allow you to save files so that they can be opened on

any platform, for example fields created on a PC can be read/imported on an

Apple Mac, mobile phone and so on, and vice versa. However, the files may not

contain all the formatting that can be saved in a package-specific format and it is

not always possible to open proprietary file formats on other platforms.

Common generic text files include:

» Comma separated values: These files have a .csv file extension. This file type

takes data in the form of tables (that could be used with a spreadsheet or

database) and saves it in text format, separating data items with commas.

» Text: These files have a .txt file extension. A text file is not formatted and can

be opened in any word-processor software.

» Rich text format: These files have an .rtf file extension. This is a text file type

that saves some of the formatting within the text.

Common generic image files include:

» Graphics interchange format (GIF): These files have a .gif file extension. This

format stores still or moving images and is an efficient method of storing

images using a smaller file size, particularly where there are large areas of

solid colour. It is widely used in web pages.

» Joint photographic expert group (JPEG): These files have a .jpg (or sometimes

a .jpeg) file extension. This format stores still images but not moving images.

It is an efficient method of storing images using a smaller file size and is widely

used in web pages.

312

10 DATABASE AND FILE CONCEPTS

» Portable document format (PDF): These files have a .pdf file extension.

This is a document which has been converted into an image format. It allows

documents to be seen as an image so they can be read on most computers.

The pages look just like they would if they were printed but can contain

clickable links and buttons, form fields, video and audio. In pdf format, you

can protect a document to stop others from editing it.

» Portable network graphics (PNG): These files have a .png file extension. It is

a file format that compresses graphics (image) files without any loss of image

quality. It was created to replace graphics interchange format and is now the

most used lossless image compression format on the internet.

» Moving Pictures Experts Group layer 4 (MPEG-4): These files have

an .mp4 file extension. It is not a single file format, but is a multimedia

container which is used for storing video files, still images, audio files,

subtitles and so on. This container is often used to transfer video files on the

internet.

Common generic audio files include:

» Moving Pictures Experts Group layer 3 (MPEG-3): These files have an

.mp3 file extension. It is a compressed file format used for storing audio files.

This format cannot store still or moving images. The file sizes are relatively

small but have high quality, which makes it suitable for use on the internet.

Common generic files used for website authoring include:

» Cascading style sheet: These files have a .css file extension. This is a

stylesheet which is saved in cascading stylesheet format and is attached to

one or more web pages (often written in HTML) to define the page’s colour

scheme, fonts and so on.

Hypertext Markup Language (HTML): These files have an .htm (or sometimes

an .html) file extension. This is a text-based language used to create markup that a

web browser will be able to interpret to display information on a web page.

Common generic compressed files include:

» Roshal archive (RAR): These files have a .rar file extension. This is a

container which can hold almost any file type in a compressed format. It

is used to reduce the number of bytes needed to save a file, either to save

storage space or to reduce transmission time. It was developed for Windows

by a Russian software engineer named Eugene Roshal and takes its acronym

from Roshal ARchive.

» Zip: These files have a .zip file extension. This is a container which can hold

almost any file type in a compressed format. It is used to reduce the number

of bytes needed to save a file, either to save storage space or to reduce

transmission time.

10.12.2 Sequential access

Before studying the indexed sequential method of accessing data, it is important

that we understand what is meant by sequential access. In a sequential file,

records are stored one after the other, in the order in which they were added to

the storage medium, usually magnetic tape. To read data from or write data to

tape, sequential files must be used.

313

10.12 File and data management

ID Customer name Country

7 Andreza Bistene USA

3 Joseph Pinkerton USA

8 Hannah Joseph USA

10 Tyler Moncrieff USA

6 Javier Hernandes Chile

4 Nabil Mohammed Brasil

5 Luis Nova Venezuela

9 Addy Addu Chile

1 Pingu Morales Brasil

2 João Sousa Brasil

V Table10.9Unorderedfile

ID Customer name Country

1 Pingu Morales Brasil

2 João Sousa Brasil

3 Joseph Pinkerton USA

4 Nabil Mohammed Brasil

5 Luis Nova Venezuela

6 Javier Hernandes Chile

7 Andreza Bistene USA

8 Hannah Joseph USA

9 Addy Addu Chile

10 Tyler Moncrieff USA

V Table 10.10 Ordered file

In an ordered file, the records are put in the order of a key field such as

customer ID, as shown above. In an unordered file, the records are not in any

particularorder.

There are a number of disadvantages to using sequential files. For example,

the only way to add new records to a sequential file is to store them at the end

of the file. A record can only be replaced if the new record is exactly the same

length as the original. Records can only be updated if the data item used to

replace the existing data is exactly the same length.

The processing of records in a sequential file is slower than with other types of

file. In order to process a particular record, all the records before the one you

want have to be read in sequence until you get to the one you want. The use

of sequential files is recommended only for those types of application where

most or all the records have to be processed at one time. (See Chapters 1 and 4

for further details on the use of master and transaction files, which would use

sequential access.)

10.12.3 Indexed sequential access

Indexed sequential files are stored in order, and unlike sequential access

files which are stored on tape, these are stored on disk to allow some form

of direct access. Each record consists of fixed-length fields. This is a leftover

from the use of magnetic tapes where records had to be stored in the order

they were written to the file. The use of ordering facilitates faster access,

because a table of indexes is used to allow the search to jump to a particular

place on thediskrather than going through all the other records to get to the

rightpoint.

There are two ways that records can be arranged in a sequential file. One

way is to have the records in some sort of order using a key field. A key field

is one which is unique to every record, that is every record has a different

value in that field. This is called ordered sequential. Alternatively, the records

might be arranged with no thought given to their order, so they appear to be

unordered.Whether the file is ordered or unordered affects the way in which

the data is processed, as well as the type of processing that can be used. An

unordered sequential file is often referred to as a serial file, as the only method

for retrieving information is to go through each record one byone.

314

10 DATABASE AND FILE CONCEPTS

With an indexed sequential system, the records are in order. For example if a

database of employees was stored in order of surname (although this is unlikely

unless it was part of a compound key field) and you wanted to find a record

starting with the letter ‘F’, such as Fullman, then the place for ‘F’ is looked up

in the table of indexes and the disk head is moved to the index point which is

the start of the‘F’s. It makes the searching faster. Although all the Fs would

still need to be searched to get to Fullman, the computer does not need to

search through any of the surnames beginning ‘A’ to ‘E’.

Banks use sequential access systems for batch-processing cheques. This system

would have to be at least indexed sequentially for faster access to records for

online banking. Indexed sequential files are used with hybrid batch-processing

systems, such as employee records. The index allows for direct access when

individual records are required for human resource/personnel use. The records

are held sequentially to allow for serial access when producing a payroll, since all

records are processed one after the other.

10.12.4 Random access

Random access (which is sometimes called direct data access) is the quickest

form of access. It does not matter whereabouts in the file the desired record is; it

will take the same amount of time to access any particular record. Each record is

fixed length and each has a key. The computer looks up the key and goes to the

appropriate place on the disk to access it.

10.12.5Hierarchicaldatabasemanagementsystems(DBMS)

Hierarchical DBMS was developed in the 1960s and has a tree-like structure,

where data is stored as records connected together through links. This allows

fast access to data, because large amounts of data are bypassed as you go down

the levels of its structure.

10.12.6 Management information systems

A management information system (often shortened to MIS) is a computer-

based tool that organises and evaluates data for an organisation. It can be a

single software program or in a larger organisation is likely to be a number

of products used together. It often includes the management of web content,

documents, records, learning and the content used for learning. The features of

a good MIS are that it will be:

» flexible in the analysis and evaluation of data from many sources and in many

different ways

» easy to use without computer expertise

» versatile enough to support different skills and knowledge

» collaborative to generate communication between managers and other staff

within a company.

Many organisations use MIS for:

» process control

» human resource management

» accounting and finance

» sales and marketing

» inventory control

» office automation

» enterprise resource planning

» management reporting.

315

Management information systems can help a company to run more efficiently,

because they provide details about the past performance of the company

as well as how it is performing at the moment and, most importantly, they

help to predict how well the company may do in the future. An MIS will

help managers make decisions about actions they may need to take. It is a

computer-based system that provides managers with the tools to manage

their departments. An MIS is made up of a number of components, such

as the hardware resources used within the system, software in the form of

decision support systems, people management applications, and also project

management systems. Within the MIS are systems designed specifically

for managing the marketing aspects of the business as well as accounting

information systems which have a variety of accounting functions. There

are also systems used for personnel aspects of an organisation which store

information about employees. A company using an MIS tends to have a

management information system manager. Their job involves running the

company’s information and technology systems. Their role typically involves

analysing business problems as well as designing and maintaining the computer

applications which are needed to solve the company’sproblems.

An MIS helps with all aspects of project management and database retrieval

applications. Information retrieval is the ability to take different types of data

in the storage media and to produce information in a meaningful format. A

properly designed storage and retrieval system performs searches efficiently

and accurately. In some cases, it even suggests alternative courses of action for

management to take. Department managers will use management information

systems to gather and analyse information about various aspects of the

organisation such as personnel, sales, revenue, and production. Management

information systems are used to produce reports and charts about the different

aspects of the company at regular intervals for department managers and

directors. Reports are produced using a reporting tool which provides quick

access to summarised reports coming from all departments. These reports help

them evaluate their company’s performance. The comparison of regular reports

to previous reports, as well as using graphs, helps managers to detect trends in

profits and sales.

Examination-style questions

All workers in one town in Austria are to have their data stored in a central

database. This will be used to create a number of reports, which must be

produced to a professional standard.

All reports must be designed to fit on an A4 page in portrait orientation and be

a single page wide. All text must be at least 12 points high. Display all currency

values in euros with two decimal places.

All field names must be short, meaningful, consistent in style, and may contain

neither underscores nor spaces.

1 Using suitable software, open and examine the data in the file workers.csv.

Use this data to create a small efficient relational database normalised

to the 3NF.

[52]

10.12 File and data management

316

10 DATABASE AND FILE CONCEPTS

2 A New Year Awards Ceremony for ‘Unsung Heroes’ will take place on 1

January next year where awards will be presented to workers who, on

today’s date, will have been at the same place of employment for more

than25years,arenotanytypeofchefandhavenotpreviouslybeengiven

an award.

Create this report so that address lines 1 and 2 are omitted and so data is

grouped and formatted like this:

Thetitlehasbeenblurredoutbutshouldbeawhitesans-seriffont

on a black background.

[22]

Createareporttodisplayonlytheworkerswhoaretraineesbutdo

notworkattheSkischule.Identify,foreachjobdescribed,howmany

trainees work in each place of employment. The report must have

an appropriate title, be in a tabular format and display the place of

work as row headings. Do not include totals for each place of work.

[7]

Save your database with the filename ‘Austria’ and export this report in

portable document format with the filename ‘Trainee’.

[1]