Relational Databases

Relational Databases

1. Flat file databases- all the data is held in a single table

   • The process of unnecessarily duplicating data is called data redundancy.
   • Data inconsistency – errors in the values or format of data that should be identical

2. In a relational database the data is grouped into entities and stored in multiple linked tables.

   • An entity is a “real world thing” about which data can is held in a database
     • In a relational database, each entity corresponds to a separate table in the database.
   • Attributes are the facts, aspects, properties, or details about an entity
     • In a database, each attribute corresponds to a separate field in the database.
   • The key field (sometimes called a primary key) is used to store an attribute that makes that particular entity entry in the database unique.
     • The tables in a relational database are linked by the key field in one table being added as a foreign key in another table to form a relationship between the entities.

3. Advantages

   • No data redundancy
   • No data inconsistency
   • Flexibility

4. Components

   • Tables
     • Records
       • Each record (row) in a relational database table corresponds to an instance of an entity.
     • Fields
       • An attribute is a piece of information or a characteristic of an entity. Attributes of entities are represented in database tables by fields (columns). A field stores one item of data for a record.
   • Relationships between entities (tables) using primary keys and foreign keys
     • Problems with data inconsistency and data integrity are avoided because there is only one set of data
   • Forms
     • A form is used for data INPUT and for displaying and editing selected data, rather than having to use a database table directly. When edited, the changes update the database table.
     • Adavantages over a table as a user interface
       • Data can be laid out in a meaningful way with clear labels rather than just columns of data and field names.
       • Font colour, type and size can be customised to improve clarity.
       • Images can be included on forms, both as fields and as graphics such as logos.
       • Drop-down list boxes can speed up data input as well as reducing errors.
   • Reporting facilities
     • Data OUTPUT. It can either be displayed on a screen or more typically be printed.
       • Professional
       • Images and charts
       • Group/ sort data
   • Query facilities
     • To update the details of a specific record or group of records
     • To calculate totals based on the information held in records
     • To identify a group of records that share a certain attribute
     • SQL (Structured Query Language)
       • SELECT* FROM Student WHERE ID= '2209'
   • Modules
     • Packages of code that database developers can use to add extra functionality to a relational database. Each module is a collection of procedures that are stored together as a unit in the database.

5. Relationships

   • One-to-one
   • One-to-many
   • Many-to-many

6. Data types

   • This helps check for the wrong type of data being entered (validation). For example, a database will not allow you to store text in a field of data type: 'integer'.
   • Data types also help to make sure that the data is stored as efficiently as possible. For example a field of data type 'integer' requires less storage space to hold the value 2 compared a field of data type “real”.
   • Examples
     • String/Text
       • Note, telephone 'numbers' are always stored as text, this allows a leading zero, spaces, brackets etc.
     • Number
       • Range checks
       • Arithmetic operations
     • Real
     • Boolean
     • Date/Time
   • Data types also help ensure that data can be sorted correctly.

7. Validation cannot check that data is actually correct, only that it is reasonable or sensible data. The process of checking that data is actually correct is called verification.