IGCSEICTCIERevision Notes14. Databases14.1 DatabasesTypes of Database

Types of Database (CIE IGCSE ICT)

Revision Note

Becci Peters

Expertise

Computer Science

Flat File Database & Relational Database

  • A database is a structured collection of data so it can be searched, sorted, filtered and analysed quickly
    • Data in a database can be any type of data including text, images, videos, sound
  • Databases use tables to store data
  • Tables have records of data represented by one row
    • In the example below, each row represents the data stored about a single customer (the customer’s record)
    • In the customer table, there are 3 records
    • Each record is divided into fields (CustomerID, FirstName, LastName, DOB and Phone Number)

A Database Table Containing Customer Details


CustomerID


FirstName


LastName


DOB

PhoneNumber

1

Andrea

Bycroft

05031976

0746762883

2

Melissa

Langler

22012001

0756372892

3

Amy

George

22111988

0746372821

  • Fields are represented by the columns in a table
    • There are 5 fields in the customer table
    • The first row in a table contains the field names which is the heading for the data stored in that field
    • Each field in a table has a data type which defines what data can be entered into that field
 
Flat File Database
Relational Database
Characteristics

  • A single table of data
  • Data separated by commas or tabs

  • Organises data into multiple tables
  • Tables linked by primary and foreign keys
Uses

  • Ideal for small datasets
  • Used in data import/export

  • Suitable for large datasets
  • Used in sectors such as healthcare and finance
Advantages

  • All records are stored in one place
  • Easier to use
  • Sorting and filtering are simpler
  • Can be used with a spreadsheet

  • Reduced data redundancy due to normalisation
  • Reduced inconsistency of data
  • Easier to edit records/record format
  • Easier to add/delete data/records
  • More complex queries can be carried out
  • Better security
  • More ability to cater for future requirements
Disadvantages
  • Data redundancy with duplicated data
  • Difficult to manage as database size grows
  • Harder to update
  • Harder to change the data format
  • Harder to produce complex queries
  • Almost no security

  • Complex to set up and manage costing time and money
  • Requires more processing power compared to flat file databases
  • Slower extraction of meaning from data
  • Less robust as each table requires a key field
    and relationships to other tables
  • More developer expertise to run the database
  • More processing power is needed for complex queries

Worked example

The owner of a gardening company is planning to create a database to store the details of all his customers. He has the choice of using a flat file database or a relational database.
Discuss the advantages and disadvantages of using a relational database rather than a flat file database.

[8]

Advantages of relational databases

Less data entry/data is stored only once / avoids duplication of data
Less inconsistency in data
Easier to edit data/records
Easier to edit data/record format
Easier to add/delete data/records
More complex queries can be carried out
Better security
More ability to cater for future requirements/expansion

Disadvantages of relational databases

More complex than a flat file database as more tables are required
Takes more time to set up
More of a reduction in performance if many tables are needed
Slower extraction of meaning from data
Less robust due to broken keys and records / each table requires a key field and relationships to other tables
More developer expertise/personnel to run the database:
More expensive to create a relational database
More processing power is needed for complex queries.

Advantages of flat file databases

All records are stored in one place
Easier to understand/use
Sorting is simpler
Filtering is simpler
Can be used with a spreadsheet / single table DBMS

Disadvantages of a flat file database

Data is more likely to be duplicated / difficult to stop duplication
Records can be duplicated and the flat file will not stop this
Harder to update
Every record in the database has to have the same fields, even though many are not used
Harder to change the data format
Harder to produce complex queries
Almost no security

Data Types

Import Data and Create Tables

  • You can import data from existing files, like .csv or .txt
  • You can use specified field names to create tables

unnamed-19

Data Types

  • Each field in a table has a data type
    • If you assigned the data type Integer to a phone number it would remove the initial 0
    • Common data types include text/alphanumeric, character, boolean, integer, real and date/time
    • Phone numbers have to be assigned the text/alphanumeric data type because they begin with a 0

Database Data Types


Data Type


Explanation


Example

Text/Alphanumeric

This data type allows letters, special characters like spaces and punctuation and numbers to be entered into a field

NG321AE

Character

This allows single characters to be entered into a field. Characters can be any alphanumeric value and can be lowercase or uppercase

A

Boolean

This data type can be used in fields where there are only two possible options. Data is stored as a 1 or 0 in the database but can be used to represent True/False or Yes/No or checked/unchecked

True/False

Integer

Only whole numbers can be entered

15

Currency

Used for monetary values

£4.75

Decimal / Real

Numbers including decimal numbers can be stored

30.99

Date/Time

Only dates or times can be entered into a field with this type. A format for the date/time can also be assigned to the field

180855

unnamed-20
Setting Data Types and Sub-Types

  • You can set appropriate data types to fields
  • You can set sub-types of numeric data including percentages, the number of decimal places

Setting Display Formats

  • You can set the display format of Boolean/logical fields to either
    • Yes/No
    • True/False
    • Checkbox
  • You can set the display format of date/time data

Exam Tip

  • Make sure you're specific which type of numeric data it should be - integer, decimal/real or currency

Worked example

A farmer has purchased a computerised milking system for her cows. She has asked a systems analyst to create a database to store details of the cows being milked.
Examples of the details of the cows which will be stored are:


Breed
Date_of_birth
Weight_of_cow
Average_milk_yield
Passport_number
Holstein 25/02/2017 725.9 24.5 998/2017
Ayrshire 15/03/2016 715.0 20.1 972/2016
Jersey 25/02/2017 732.7 25.0 971/2016
Holstein 10/10/2016 715.0 25.0 765/2016

Complete the following table by entering the most appropriate data type for each field. For any numeric field, specify the type of number.


Field name
Data type
Breed  
Date_of_birth  
Weight_of_cow  
Average_milk_yield  
Passport_number  

[5]


Field name
Data type
Breed Text [1]
Date_of_birth Date [1]
Weight_of_cow Numeric: decimal/real [1]
Average_milk_yield Numeric: decimal/real [1]
Passport_number Text [1]

You've read 0 of your 0 free revision notes

Get unlimited access

to absolutely everything:

  • Downloadable PDFs
  • Unlimited Revision Notes
  • Topic Questions
  • Past Papers
  • Model Answers
  • Videos (Maths and Science)

Join the 100,000+ Students that ❤️ Save My Exams

the (exam) results speak for themselves:

    Next topic

    Did this page help you?

    Becci Peters

    Author: Becci Peters

    Becci has been a passionate Computing teacher for over 9 years, teaching Computing across the UK helping to engage, interest and develop confidence in the subject at all levels. Working as a Head of Department and then as an educational consultant, Becci has advised schools in England, where her role was to support and coach teachers to improve Computing teaching for all. Becci is also a senior examiner for multiple exam boards covering GCSE & A-level. She has worked as a lecturer at a university, lecturing trainee teachers for Computing.