13 Database

Nov 10, 2019

Chapter Overview

  • General introduction
  • Usage
  • Structure
  • Data Type
  • Flat files and Relational databases
  • Search in database

What is a database

A structured collection of data that allows people to extract information in a way that meets their needs. The data can be varied in type.

KEY FEATURE

  • Data is only stored once — no duplication
  • The same data is used by everyone

Why we used it

USAGE

  • Store information about people/things/events
    E.g. Patients in a hospital, books in a library, hotel booking

ADVANTAGE (Compare electronic database to a manual database)

  • Able to store more data
  • Fast to find a specific record
  • Fast to search for records meeting a specific criteria
  • Less time is required for data entry if typist is quick
  • Can be secured with a password
  • Easier to make backups

Structure

0_1543425516143_02_Table.png

Inside database, data is stored in tables, which consist of many records and each record has several fields.

  • Table (file): A group of related records
    E.g. Employees
  • Records (rows): A group of related fields
  • Fields (columns): Pieces of data in a record
    E.g. Last Name

Data Type

Data is categorized into types to make data processing easier, faster, safer and more accurate for the computer.

  • Text (E.g. SCIE)
  • Alphanumeric (E.g. 0478ComputerScience)
  • Number (E.g. 1210))
  • Boolean/Logical (E.g. True/False)
  • Currency (E.g. $999)
  • Date/Time (E.g. 21 Dec 2018)

Validation

Check if the data received is valid

  • Range check — check if the number is in a certain range
  • Length check — check if the length of the string is in a certain range
  • Type check — check if the input is the required type
  • Character check — check if the string contains forbidden characters
  • Format check — check if the string follows the defined pattern
  • Presence check — check if the there is an input

Flat files and Relational database

FLAT FILE

  • Only contains a single table (file) of data

RELATIONAL DATABASE

  • Contains two or more tables (file) of data, connected by links called relationships
  • The relationship is built up by primary key and foreign key
    • Primary key: A unique field within a table. Duplicates are not allowed
    • Foreign key: A field that has a primary key in another table. Duplicates are allowed
0_1543426832062_foreign_key_example.png

How to search data in a database

By creating a QUERY (A request for data/information from a database)

0_1543427023176_querying_a_database_design_view.png
  • Field: search for the data in this field
  • Table: search for the data in this table
  • Criteria: select the data that meets specific criteria (e.g. =’Home’,
Great! You've successfully subscribed.
Great! Next, complete checkout for full access.
Welcome back! You've successfully signed in.
Success! Your account is fully activated, you now have access to all content.