Computerised Databased

In the early days of computing, individual applications were developed independently within an organisation. Each separate application had its own data files – usually referred to as flat files - which were accessed by a number of programs. A flat file database is one where each record contains the complete information about one thing, e.g. a student, an enrolment, course details, and tutor details.

There would be duplication or redundancy of data: fields such as tutor name would be stored in many records. This would lead to inconsistency which occurs where two or more entries for a data item are different, e.g. the tutor name may be changed in one record, but remain the same in all other records.

Gradually, more complex filing methods were developed, and from these database systems were evolved. In a database system, data is kept separate from the application programs, and a Database Management System (DBMS) works between them. This is a suite of complex software which controls all aspects of database management, e.g. definition, creation, maintenance, access, interrogation, and security. It works automatically without the users knowing how. It provides its own simple user interface which enables non-specialist users to easily enter data and carry out queries.

Advantages of databases over other storage systems

• data only has to be entered once, is stored only once and all applications share this pool of data. This ensures consistency of data and avoids redundancy of data, which occurs when data is duplicated unnecessarily.

• validation checks can be performed on the data as it is entered.

• tables are linked, so that data changed in one table is automatically changed in other tables.

• it is easy to change the structure of the database, without affecting any of the programs that access the data. This is known as data independence. This is achieved by storing the data separately from the programs that access it. The addition of an extra field or an extra table will not affect the programs that access the data.

• complex searches can be carried out easily and access to the information is very rapid

• access can be restricted to authorised users, thus helping to protect the integrity (the accuracy and correctness) of the data stored in the database. Referential integrity ensures that when changes are made to the data in a linked field in one table, then all the records in another table that are linked to it will also be changed.

Disadvantages of the database approach

• if the file server containing the database fails, then none of the applications that use the data can be used, and complex procedures are required to ensure that lost data can be recovered

• A DBMS is a large program which may require much greater storage capacities

• Databases design requires expertise, and if not done well, the new system may fail to meet the users’ objectives

• Security needs to be considered carefully as all the data is now held centrally

• Users of the system will need careful training and this can be expensive

A relational database is a complex database structure to hold a variety of different data in tables which are related or linked through the use of key fields.

Key Fields

The primary key field is used to uniquely identify each record (row) in a database, e.g. customer number, order number. It may consist of more than one field, in which case it is referred to as a ‘composite’ primary key.

A foreign key field is a field that is common to two tables; in one table it is the primary key and in the other table it is an ordinary field. They are used to link tables.


A relationship is the link between two tables (entities), from the primary key of one table, to a field in another table, which then becomes a foreign key.


As well as having a primary key, which determines the order of the data in a relational database, indexes - or secondary keys - can be specified. Each index allows faster access to the data. However, they slow down data entry and editing, because the indexes have to be updated each time a record is added or deleted.

Indexes can also be used to sort the records for viewing the data in a particular order, or for producing reports in a particular order, such as “surname”.


Normalisation aims to improve the logical design by making it free of redundant data and flexible enough to permit the future addition of entities, fields and relationships.

A database designer would normalise a database in order to:

• Reduce data duplication/redundancy

• Improve data integrity (reduce inconsistencies)

• Reduce the risk of data loss during updating

• Provide program/date independence

There are several levels of normalization, of which only the first three are considered here: • First Normal Form (1NF)

• Second Normal Form (2NF)

• Third Normal Form (3NF)


A table is in first normal form if it contains no repeating data item groups.

A table is in second normal form if no field that is not part of the primary key is dependent on only part of the primary key (i.e. contains no partial dependencies).

A table is in third normal form if it is already in second normal form and it has no ‘non-key dependencies’, in other words all the data items depend on the whole key and nothing but the key.

Whenever the tables in a logical design have undergone the normalisation process they are said to be normalised. To demonstrate 1NF, 2NF and 3NF we will apply the rules for each to the following set of un-normalised data (flat file) on students and courses. The primary key in this imaginary table is StudNo, and can be represented using the following standard notation:

STUDENT(StudNo, Surname, Forename, Address, gender, CrseCode, CrseTitle, TutNo, TutName)

First Normal Form (1NF)

A table is in first normal form if it contains no repeating data item groups. We can see that StudNo, Surname, Forename, Address and gender are repeated unnecessarily. These fields need to be in a table of their own, which we will call STUDENT, and the remaining fields will be stored in a new table, which we will call ENROLMENT.

The key field of the original entity is always included as a field of any new table, in order to relate the records, although it is not essential for it to form part of the key of the new entity.

In the enrolment table neither StudNo nor CrseCode, on its own, uniquely identifies an individual enrolment. A composite key, using both fields, is used to uniquely identify a single enrolment,. The standard notation for these tables is shown below:

STUDENT(StudNo, Surname, Forename, Address, gender) ENROLMENT(StudNo, CrseCode, CrseTitle, TutNo, TutName)

Second Normal Form (2NF)

A table is in second normal form if it is first normal form and has no fields that aren't dependent on the whole of the key In the Enrolment table, it can be seen that TutNo and TutName each depend on both parts of the key. For example, if a student enrols on more than one course, he has a different tutor for each course. To identify a tutor in an enrolment record, requires specification of both values (StudNo and CrseCode). But, CrseTitle is not dependent on the whole of the key, only on CrseCode. So we can remove this field (CrseTitle), and put it in a new table (COURSE), together with the key on which it depends (CrseCode).

STUDENT(StudNo,Surname, Forename, Address, gender) ENROLMENT(StudNo, CrseCode, TutNo, TutName) COURSE(CrseCode, CrseTitle)

Third Normal Form (3NF)

A table is in third normal form if it is already in second normal form and there are no fields that are dependent on other fields that are not part of the key In the ENROLMENT table, we can see that TutName depends on TutNo, so we need to remove these fields, and place them in a new table, called TUTOR

Views of the Database

The RDBMS provides tools such as queries and reports which allow for certain items to be selected from within tables. This allows different users to have different “views” of the data e.g. in a GP surgery, a receptionist’s view would only allow access to data such as patient name and address, whereas the doctor would have a view of all data. The advantages are that this is a form of security, and hides sensitive parts of tables from certain users. It also allows the data to be “seen” in different ways by different classes of users.

Database Security Many organisations keep a central pool of data using a RDBMS, the security of which is critical to the organisation. Use of the system must therefore be within the framework of adequate security measures that will ensure that: 1. an item of data is only accessed by those persons who are authorised to do so

2. once the data has been stored inside the computer system, certain measures are taken to prevent it from being corrupted, lost or destroyed by any means.

Preventing unauthorised access – normally under the control of the DBMS, and is set up by the database administrator. Some systems will allow several different levels of security ranging from high to low, and each range would be applied to functions such as add, amend, delete and view/read data (see also “Views” above), e.g. some users may have read only access whereas other users may have read and write access. Data may also be encrypted to prevent it being understood by unauthorised persons, and passwords may be used to gain access to the database.

Physical security needs to be provided for the data so that it will be possible to recover from errors if a disk becomes corrupted, or to restore the whole database in the event of a fire etc. Backup copies need to be kept securely, and physical access to hardware needs to be restricted by locks on doors etc.

Query Languages

A query is a question asked of the data in a database. A query language is a simplified programming language that allows a non-skilled person to extract information from the database. There are two main forms of query languages: SQL and QBE. Structured Query Language (SQL) consists of a small number of commands which must be carefully constructed just as any other programming language. SQL allows for very complex queries to be built. An example of an SQL command is:

SELECT tblProducts.ProductDetails, tblProducts.ProductUnit, tblProducts.ProductPrice FROM tblProducts WHERE 1)

(tblProducts.ProductDetails) Like “
  • Pens *”));
Query by example (QBE) provides a simple way of running queries without having to worry about the construction of commands. Inexperienced users can select fields from the tables and indicate which fields are required, specify the criteria, and any sort parameters by clicking boxes and windows. ==Data Dictionary== This is a file containing descriptions of, and other information about, the structure of the data held in a database. It will also show which programs and reports in the database system use the data. It is not usually accessible to the users. It is a tool for the manager of the database, for example when they need to alter the way the data is stored. ==Database Administrator== This is the person who is given overall responsibility for the structure and control of the information in an organisation’s database. The role would normally involve: • design and creation of the database • monitoring the performance • keeping users informed of changes • allocating passwords and access rights • maintaining the data dictionary • assessing and organising training for users • setting up procedures for security and backup ==Data Warehousing== A data warehouse is a large collection of data stored together for further processing. This data, particularly when analysed with other data, can provide useful information for future planning. Much of the data collected by large businesses with many sites is used immediately for a specific purpose and is stored where it is collected and used. Since the data is only being used for further analysis, it does not have to be complete or up to date. ==Data mining== Data mining is the analysis of a large amount of data in a data warehouse to provide new information, or to find patterns or trends in the data. For example, by using loyalty cards, which connect purchases to a particular customer, supermarkets can gather information about the buying habits of individual customers, and help with the placement of products in the store. All of this will lead to an overall increase in sales. Combining all the information about customers helps them establish long term trends. An insurance company might use a data warehouse to check previous claims for a customer, and compare this with information from other insurance companies’ data warehouses to investigate fraud. Computer Science

QR Code
QR Code database_system_work (generated for current page)