Database Systems

Definition - Relational Database

A relational database is a large collection of data items and links between them (1 mark), structured in such a way that allows it to be accessed by a number of different application programs. (2nd mark) or use of primary and foreign keys.

CUSTOMER (CustomerID, Name, contact no, address1, address2, etc.)

ORDERS (OrderID, Date, StockID, CustomerID)

PUPIL [Pupil Code, Name, Form, DOB, Gender]

SUBJECT (Subject Code, Pupil Code, Teacher Code, Time, Day, Room)

PATIENT (Patient Code, Name, Address, DOB, Gender)

APPOINTMENT(Appointment Code, Patient Code, Doctor Code, Time, Date, Room)

STAFF (Staff no, Name , expertise, pager no, contact no, Ward no)

PATIENT (Patient no, Name, illness, admission date, consultant, address, contact, Ward no)

A holiday cottage hire company uses a relational database management system for organising its bookings.  Customers only ever hire one cottage at a time.

COTTAGE (CottageID, Type, Name, Area, Postcode, Number OfBedrooms, CostPerDay)

CUSTOMER (CustomerID, surname, firstname, phone, email)

HIRE (HireID, CustomerID, CottageID, DataArrive, NoNights)

Flat File Databases

A college keeps details of students, staff and courses in a file. Part of this file is shown below.  

As this data is NOT normalised it causes problems e.g.

KEY TERMS

Data Redundancy 

This is where data is duplicated causing unnecessary waste of storage space. In a flat-file database details about such information as customer details will be duplicated. In a well designed relational database there should be no ‘repeating attributes', no piece of data should be unnecessarily repeated.

• Example - the lecturer name H Smith' is repeatedly stored in the above data.

• Data redundancy is where you store an item of data more than once / A company may hold its data in different files.  

• This is wasteful because some data may need to be input twice and if data is changed in one it will need to be changed in the other. 

• Data which is repeated unnecessarily is called redundant data.

Data integrity

The integrity of data is the correctness, i.e. the extent to which it truthfully represents the original real information.

One of the problems of maintaining integrity arises when updating occurs, and every record has to be changed in a flat-file database, if one record was left unchanged the data would no longer be wholly correct. In a relational database you only have to change data in one table and all other references in any other table will automatically be changed.

Data Consistency 

When data is held in more than one file it should be stored in a consistent way. A date field could be stored in file as a text field but in another field as a date/time field and the data would be incompatible. In a relational database because the attributes of any one entity are contained within one file, there is no risk of the same attribute being stored in a different format in a different file (Spelling mistakes in names). Data is inconsistent if data is stored more than once there may be differences [e.g. in spelling] or transcription errors.

• Example J Evans might be mistyped as 'J Evins' or the course code 186 might be mistyped as 816. The computer will not know which one is correct.

• Data consistency is the relationship between the input data, the processed data and the output data as well as other related data.

• If the system is working properly the data will be correct at each stage and is said to be consistent.

 OR 

• Data consistency is using one file to hold a central pool of data. / A company may hold all its customer data in one file. 

• This avoids the need to input data twice so that if data is changed in one file it won’t need to be changed in another and remains consistent.

OR 

• Data being inconsistent in a flat file due to possibility of different formats etc 

• and being consistent in a RDBMS as each record is only stored once so cannot have different attributes

Data Independence 

• Data independence  is where the the data and the applications/programs used to access it are independent/separate.

• New applications can be developed to access the data without changing the data / New systems can still use existing data. 

Relational databases are more secure than a flat file approach because:-

• • Hierarchy of passwords

  • Storage of data separate to programs

  • Access rights to parts of the program