Topic 3  Databases

What is a Database?

At its simplest a database is a collection of related records.  A record is set of related data, for example the name and address of a customer.  Each record has a set of attributes, for example, name, address 1, address 2, etc.

In Module 2 we saw that databases can be simple flat files, for example, a record collection.  We also saw that large flat files contained duplicated data.  There was data redundancy.  We also saw that to reduce data redundancy we used a relational database, in which related tables were linked through primary keys, which were unique identifiers for each entity (item of interest in the database).

You can make a flat-file data base with:

• a word-processor (a simple table);

• a spreadsheet;

• a flat-file database management system.

The problems with flat files are numerous:

• Data redundancy - duplication of data in each file and across files.

• Data inconsistency - data have to be changed in all files when the data in one file are changed.  Often this updating is not done, so there are several versions of the data.

• Data integrity - if there are lots of files with lots of duplicated data, an incorrect data entry can be duplicated across the system, making corrections tedious.

• Program-data dependence - every program in every department has to specify which data fields they are going to process.  If a field is changed, every program accessing that field must be altered to recognise the new format.

• Data are not sharable - data held by one department have to be copied so that they can be used by other departments.

Question 1  Give three advantages of a relational database.  ANSWER

Database Management System (DBMS)

From now on we will use the definition of a database as:

a collection of non-redundant data shareable between different applications.

In the early databases, all the data in an organisation were pooled in a central location, accessible by all applications, programs that could process the data in some way.  However there remained two big problems:

• unproductive maintenance - if a department needed to add a field to a file, all other programs accessing that file needed to be updated;

• security issues - all data, however sensitive, could be accessed by all applications.

These issues were dealt with by a layer of software that links the data with the applications, called a database management system (DBMS) of which the general scheme is like this:

The DBMS acts as an interface between the data and the applications.  The DBMS has two key features:

• Program-data independence - the storage structure of the data is hidden from each application.  In effect the data is converted into the form required by an application by the DBMS.

• There is restricted access to the data, each user having access only to those parts they need.  For example the marketing department does not need employees' payroll details.

Entity relationships

When a database is designed, a conceptual data model is produced, which considers all the data that are to be held, and what is to be done with them.  We need to remind ourselves of some key words:

• Entity - any thing of interest to the organisation holding the database, e.g. Customer ID, Supplier, Credit Limit;

• Attributes - any characteristic of an entity.  A college course might have the following attributes: A2 or AS, Length, Name, Tutor, Department.

• Relationship - link between two entities, for example student to course.  Each student does several course, while each course has many students.

There are three degrees (kinds) of relationships:

• One-to-one - for example each school has one headmaster and each headmaster has one school.

• One-to-many - for example a train has many passengers.  The relationship can be reversed; many passengers are on one train.

• Many-to-many - for example many students do many courses.

The relationships are shown in entity-relationship diagrams.  The degrees of relationship are shown by the crow's feet.

Database management systems cannot deal with many-to-many relationships.  They get round this by sing a link table, for example:

Many doctors see many patients.  The many-to-many relationship is broken by the Appointments table which is the link table.

Each patient can have many appointments, although each appointment is with one patient.  Each doctor has many appointments, although each appointment is with one doctor.  This is the simplest ER diagram for a relational database; you may well have done something like this for your project.

Normalisation

This is probably the most difficult area of database design.  Normalisation ensures that the relational database has the best possible design.  Tables should be designed so that:

• No data are unnecessarily duplicated - the same data are held on one table;

• Data are consistent across tables (if the data are not duplicated, this should not be a problem)

• The table structure should be flexible enough to allow the designer at add as many or as few items as they want.

• The structure enables the user to make all sorts of complex queries to relate data from different tables.

Here are some un-normalised data.

This table has repeated data.  Can you see where?  There is data redundancy, which on a big table will make for a big file.

A table is in First Normal Form if there are no repeating attributes or groups of attributes.  One solution might be to rewrite the table:

This is NOT in first normal form, because we still have repeating attributes.  This is a very common bear trap.  We need to break the table up:

This is a bit better, but it's not perfect.  There is still  data redundancy.  Look in the first table and you will find that the customer is being identified by the invoice number.  The customer really needs to be identified solely by the customer ID number.  To make retrieval more efficient we need to put the data into second normal form:

A table is in second normal form if it is in first normal form and no column that is not part of a primary key is dependent on only a portion of the primary key (i.e. there are no partial dependencies).

That's a bit of a mouthful but it means that all the attributes in the customer table should only be dependent on the customer ID.  So things like name, address, and telephone number are looked up using the Customer ID unique identifier, not the invoice unique identifier.  So we can break the tables up further to give an ER diagram like this:

The Order table will have two columns, Goods ID and Invoice ID.  This breaks the many to many relationship.

In third normal form, there are no non-key dependencies.  If we look in the invoice table above, we need to say that the goods are dependent only on the Goods ID, not the Invoice ID.  The supplier must be dependent only on the supplier ID and not the Goods ID.  So our normalised tables become, in third normal form:

CUSTOMER

 INVOICE

 ORDER

 GOODS

 SUPPLIER

The ER Diagram becomes:

The tables can be written in standard notation like this:

CUSTOMER (Customer_ID, Name, Address, Telep_No)

INVOICE  (Invoice_ID, Customer_ID, Invoice_date, Payment_Dueby, Total (£))

ORDER (Invoice_ID, Goods_ID, Quantity)

GOODS (Goods_ID, Supplier_ID, Price, Description)

SUPPLIER (Supplier_ID, Supplier_Name)

Notice the following:

• The names of the tables are in UPPER CASE;

• The key fields are underlined;

• Foreign keys, i.e. those from other tables are shown underlined and in italics;

• In some old database management systems, white space characters (such as space) are not allowed, so words are separated by the underscore character, _;

• Many databases use the Leszynski-Reddick naming convention, e.g. tblCustomer. This is not on the syllabus, so we won't go any further here, although you might find your teacher insisting on its use in the project.

Difficult?  Yes, it is.  Databases are hard.  Large industrial databases need highly experienced analysts to design them if they are to be effective, and such analysts are among the highest paid of the ICT professionals.  Even then they do not always get it right, and the failures of some large government ICT database projects have lead to scandals of national proportions.

Question 2 The manager of a video hire shop uses a relational database management system to operate the business.  Separate database files hold details of customers, video tapes, and loans.  Customers can hire as many films as they wish.

(a) For each of the files mentioned above, identify the key fields and list other appropriate fields that will be required to enable the system to be maintained with minimum redundancy.

(b) Draw an ER diagram for the database.

(c) Describe three advantages of using a relational database rather than a flat file information storage system. (NEAB Past Question)  ANSWER  

Presentation: Normalisation of Data

Now go on to Database Management