Common Storage Devices

(Also known as Storage Media)

• Magnetic disk (including floppy disk and hard disks)

• Magnetic tape

• CD-ROM (including CD-R, CD-RW)

•  Zip Disks

• DAT

Some storage devices allow you to skip through your file to find the place you want (Direct Access Media). Other media devices require you to go through all the data to find the piece you want.

Files

Files are made up of records, records are made up of fields

Files - Records - Fields

A Field is:

A Record is:

A File is:

Logical vs. Physical File Organisation

The physical storage of a file is how it is stored on the storage medium.

The logical storage of a file is how it looks to the user when it is being processed.

File Access Methods

A serial access file has data stored in the order in which it was written. New data goes to the end of the file. To read a record from the file it is necessary to read through all of the records from the start of the file until reaching the required record.

A sequential access file has data stored in the order of a key field.

An indexed sequential file stores data in the order of a key field, but also has an index holding the key field values and the address at which they are stored. This allows both sequential and direct access.

A direct access file is one where any record can be accessed without having to access other records first. This is also known as Random Access.

NB Direct Access Files can only be stored on Direct Access Media.

Why are files stored on a tape cartridge always serial access?

Advantages of Direct Access over Sequential Access

Records can be accessed in any chosen order

Records do not have to be put into any order when the file is created

Selected records can be accessed far more quickly from direct access files.

Advantages of Sequential Access over Direct Access

Sequential Files can be stored on Tape as well as disk

It is easier to write programs to handle sequential files

Advantages of Sequential Access over Serial Access

Updating master files with transaction files is made more easy using sequential files since they are both in the same order to begin with

What would be the best method of access to find one record in a large file - sequential or direct?

Why choose different methods of file access?

Some file organisations are better than others for particular tasks. These are some of the reasons why a particular file organisation may be chosen:

The number of records to be accessed - If not many records are to be accessed, direct access is more efficient. If most records are to be accessed during processing then sequential access is better. This file activity is measured by hit rate. (See later)

The size of the file - In large files sequential searches take a long time, so direct access is better. In small files time delay is less important, so sequential access is acceptable.

The type of application - Sequential access is usually suitable for batch applications, but on line applications usually require direct access to give a fast response time.

The type of storage media - Magnetic tape is a serial access medium so direct access cannot be used.

Relational Databases

Information is very valuable and must be organised so that it can be accessed as easily and quickly as possible. A database not only stores the data, but also organises the data and controls access to it with a program called the Database Management System (DBMS).

Flat files

The earliest data storage computer systems used flat files- A fiat file is like information stored in a grid or table.

Each row in the table contains a record —information about a particular person or thing.

Each column in the file contains information on one field, for example Name, Type, and so on.

Primary key

No two records in a file can be the same or it will lead to confusion. For example, if two people have the same name, there must be some other means of identifying which record refers to which person.

Therefore each record has to have a unique identifier - something that makes it different from all the other records - called the primary key.

A persons surname cannot be used as a primary key because:

• It may not be unique — there are many people with the name Smith
• it may change (for example if the person marries) and you must never alter the primary key
• it maybe lengthy and so there is more chance of typing it incorrectly
• the database software creates an index of key fields and the shorter the key field, the smaller the index, so the sorting and searching operations will be performed faster — a surname can be long

It is a good idea to create a special field to act as the primary key. Sometimes there is an obvious candidate, for example in a garage keeping a file on cars it repairs, the registration number would be an ideal primary key.

Why are flat files inefficient?

In flat files data tends to be stored in several places. For example, in a school information system information about teachers may be stored on the file for classes, as well as on an administration file holding employment information.

This is very inefficient because repeating data wastes disk space. It could also lead to inconsistent data if the teacher's address was stored differently in the two files.

We could store the information more efficiently by using a database with two files. The class table and the teacher table.

Entity Relationship Diagram

This diagram shows the relationship between the teacher and the class. The diagram shows that a teacher can teach more than one class. It also shows that each class can only be taught by one teacher.

We can find out the name and address of each teacher from the teacher table

How is a database different from a flat file?

There can be more than one table in a database. A flat file database has only one table. Each table in a database contains information about an entity, for example teacher, class, and so on

What is a relational database?

In a relational database the tables are related. This means the tables are linked together in some way. For example in the school database, we can create a relationship between the teacher number field in the teacher table and the class code field in the class table.

In the class table, class code is the primary key as it uniquely identifies the class. However it is not unique in the teacher table as one teacher can teach more than one class. By looking in the class table we can find out the teacher code of the teacher who takes that class. By looking in the teacher table we can find out more information about the teacher.

Flat files or databases?

Flat files have been used by computers for many years. They are usually used for one particular specific purpose. For example, a company might maintain an employee file used to produce a pay slip. The personnel department might also have a file of employees' records, which holds some different data.

When information is held on separate files in different parts of an organisation, difficulties arise. Information on one file might be up-dated but not on the other, leading to inconsistencies. Most businesses now use databases to organise their data more efficiently and to give flexibility.

The file approach is program-oriented; the needs of the program determine how it is stored. The database approach is data-oriented, the type of data determines how it is stored. This gives it a number of advantages over a file-based system.

Advantages of the database approach

• Data independence. Any changes to the structure of a database, for example adding a field or a table, will not affect any of the programs that access the data. In a file-based system, a minor change in a file structure may require a lot of reprogramming of all the programs that access this file.
• Data consistency. Each data item is stored only once, however many applications it is used for. There is no danger of an item, such as an employee’s change of address, being up-dated on one system and not on another. If this happened the data would not be consistent.
• No data redundancy. Redundancy occurs when data is duplicated unnecessarily. In a file-based system, the same information may be held on several different files, wasting space but making up-dating more difficult.
• More information available to users. In a database system, all information is stored togethe’ centrally. Authorised users have access to all this information. In a file-based system data is held in separate files in different departments, sometimes on incompatible systems.
• Ease of use. The DBMS provides easy-to-use queries that enable users to get instant answers. In a file-based system a query would have to be specially written by a programmer.
• Greater security and integrity of data. The DBMS will ensure that only authorised users are allowed access to the data. Different users can have different access privileges, depending (In their needs. In a file-based system using a number of files it is difficult to control access

Disadvantages of the database approach

• Bigger computers and greater risk. A DBMS is a large program that may require a larger disk and a bigger, more powerful computer than a file-based system. Storing all the information in one database can be dangerous if the database system fails. All departments of the organisation will be affected, not just a single department. Complex procedures are required to ensure that lost data can be recovered.
• Greater complexity. As databases have more uses, they get more complex and their design is more difficult. This requires considerable expertise and if not done well, the new system may fail to satisfy the user’s needs.
• Possible inefficieney or poor performance. A tailor-made file-based system may be more efficient than a database.

NB

a table is another name for a file in a database

a relationship is a link between two tables

an entity is a subject about which data is stored

Types of relationship

A relational database consists of a number of entities or tables which are related in some way. Entities can be related in any one of three ways:

• one-to-one
• one-to-many
• many-to-many

Examples

Each product in a supermarket has a bar code. The relationship between product name and its bar code is one-to-one.

One supermarket company has many stores. This is an example of a one-to-many relationship.

There are many different products, each on sale in many different stores. This is an example of many-to-many relationship.

Database Normalisation

Normalisation means structuring the database in the most efficient manner. In a relational database it is not possible to have many-to-many relationships as these will lead to ambiguities.

Tables should be structured in such a way that

• there is no redundant data, that is there is no unnecessary duplication
• data is consistent throughout the database
• many-to-many relationships are avoided
• the structure of each table is flexible enough to allow you to enter as many or as few items as you want
• the structure should enable a user to make all kinds of complex queries relating data from different tables.

Database administration

1. The database administrator (DBA) is the person responsible for maintaining the database. The administrator’s tasks include the following:
2. The design of the database and monitoring its performance. If problems arise appropriate changes must be made to the database structure,
3. Keeping users informed of changes in the database structure that will affect them.
4. Maintenance of the data dictionary for the database.
5. Implementing access privileges — specifying what a user can access and/or change.
6. Protecting confidential information.
7. Allocating passwords to each user.
8. Providing training to users in how to access and use the database.
9. Ensuring adequate back-up procedures exist to protect data.

The Data Dictionary

The data dictionary is information about the database, such as:

• what tables are included and fields in these tables name and description of each data item
• the characteristics of each data item, such as its length and data type
• any restrictions on the value of certain fields the relationships between data items
• control information such as who is allowed access to files
• whether users can change data or only read it

The Database Management System (DBMS)

The DBMS is the program that provides an interface between the database and the user in order to make access to the data as simple as possible, it has several other functions:

1. Data storage, retrieval and up-date. Users to store, retrieve and up-date information as easily as possible. These users are not computer experts and do not need to be aware of the internal structure of the database or how to set it up.
2. Creation and maintenance of the data dictionary
3. Managing the facilities for sharing the database. Many databases need a multi-access facility. Two or more people must be able to access a record simultaneously and to up-date it without a problem.
4. Back-up and recovery. information in the database must not be lost in the event of system failure
5. Security. The DBMS must check passwords and allow appropriate privileges.

Database security

As we have already seen data stored in a database is very valuable. Good security to prevent loss, theft or corruption of data is vital. Relational databases such as Microsoft Access and Paradox are multi-access databases. This means that on a network, more than one user may access the same database at the same time, How can the software cope with two or more users opening the same file, making alterations and yet maintain the integrity of the database?

Relational databases provide different methods of database security:

1. The simplest method is to set a password for opening the database. Once set, a password must be entered whenever the database is opened. Only users who type the correct password will be allowed to open the database. The password will be encrypted so that it can’t be accessed simply by reading the database file. Once a database is open, all the features are available to the user. For a database on a stand alone computer, setting a password is normally sufficient protection.
2. A more flexible method of database security is called user-level security, which is similar to the sort of security found on networks. Users must type a username and password when they load the DBMS. The database administrator will allocate users to a group. Different groups will be allowed to see different parts of the database.

Databases or a manual system?

Why should a video shop use a database? Could a manual system be better than a database system? Both systems record details of members and who has hired which video. (This could easily be done in a paper-based system by using a list of all videos and the name of the hirer written next to it.)

A manual system is cheaper, unlikely to break down and requires little training. However the computerised system will probably be better for the following reasons:

Management information is automatically gathered, for example details of each hiring, financial details, how many times a customer has hired a video and how many times a video has been hired. These figures can be used in preparation of accounts or to analyse which videos are most popular.

Better service to customers. Using a bar code reader to enter the video code and the member code is very quick. Queues at the counter will be shorter.

Details of members and videos can be found and printed quickly.

The names and addresses of members can be used for advertising purposes in a direct-mail shot. The database can be queried to come up with a list of people who haven’t hired a video for six months and a letter written offering them a discount if they hire a film this week. The letter can be personalised using the mail-merge from a word-processing package.

Similarly automatic reminders can be sent out to members who have not returned a video by the due date.

The database can be extended to include the member’s date of birth. The computer can be used to ensure that a member is old enough to hire, say, an 18 video.