What is a database?

  • Collection of interconnected information (data)
  • Logical collection of information with a particular meaning
  • Allows to select, insert, update and delete data
  • Database is always built to some application it serves
  • Stored information has usually a target group or applications who utilizes it

Database products

  • There is no one right product for database installation
  • Usually database product is chosen by the requirements of the environment (for example Microsoft, open source systems etc.) or other requirements for the database (for example availability or amount of data to be stored in to the database)
  • Below is the list of some of the common relational database products
    • Microsoft SQL
    • Oracle Database
    • MySQL
    • IBM Db2
    • Amazon Aurora
    • PostgreSQL
    • MariaDB
    • SQLite

Benefits of using the database

  • Database offers many benefits for the user
  • These will apply whether the database will be used directly through DBMS (Database Management System) or indirectly through application utilizing the database
  • Benefits:
    • Fast search
      • Data retrieval is fast compared to Excel or old fashioned paper archive for example
    • Availability
      • Data storage can be accessed by multiple users simultaneously
    • Mobility
      • Data can be accessed anywhere
    • Flexibility
      • Data visualization or presentation can be customized for different purposes and users
      • Good example of this are the database views
    • Longevity
      • Usually there is a way to regularly backup database data
      • This greatly increases the life time of the stored data
    • Cost-effectiveness
      • Low costs while taking into account the great amount of data
    • Security
      • Data will be secured from unauthorized persons
    • Adaptability
      • Data customization directly through DBMS or through application utilizing the database is easy

Who needs a database?

  • Database will be useful for anyone with the following needs:
    • Store a great amount of data
    • Save time and utilize the data effectively
    • Fast sorting for data
    • Versatile presentation for data
    • Creating reports from manipulated data

Comparing database to a simple list

  • Database offers a much more cleaner view without any duplicated data compared to simple list
  • Example 1: Employees should be listed under projects A and B.

Database example

Database example

Simple list example

Simple list example

  • As can be seen from these examples, in simple list there will be duplicated data when person and person's information will be stored multiple times under different projects

  • When using a database, only references are made between two entities (Projects and Employees)

  • Example 2: Project information will be stored including project's project manager

Simple list example

Simple list example 2

  • Duplicate data is marked in red color
  • When David would leave the company, a new project manager information needs to be filled
  • Basically this requires the change in eight lines on the list

Database example

Database example 2

  • With database there is only need to update six fields of information
  • Another great feature with database is that project manager information is connected using key values and thus project manager can only be added to Project table when the person in that role is stored in the parent table (Project manager)

Database Management System (DBMS)

  • Database environment has the following layers:

    • Applications that utilize the database
    • Database management system (DBMS)
    • Database (DB)

  • Below is the presentation of these layers

DB environment layers

  • Database Management System is a system, which allows the creation of database and its structure as well as manipulation of stored data
  • DBMS has the following features:
    • Create a new database and tables
    • Data manipulation (insert, update, delete)
    • Multiple user interaction in database simultaneously
    • Maintenance of database structure
    • Backup and restoration of data
    • Etc.

What is included in database?

  • User data
    • Data stored by the user
  • Meta data
    • Structure of the database
    • Datatypes for the stored data
  • Indexes
    • Faster data retrieval from the database
  • System data
    • For increasing the performance
    • Database operation monitoring
  • Application metadata
    • Data stored by applications utilizing the database

Database classification

  • Type
    • Personal database
    • End-user database
    • Commercial database
    • NoSQL database
    • Operational database
    • Relational database
  • Amount of users
    • One user
    • Multiple users
  • Purpose
    • General management systems
    • Application specific databases

  • Architecture

    • Centralized vs distributed
    • Homogenous vs heterogenous

  • During this course we focus mainly on relational databases

  • Comprehensive list of todays relational database systems can be found from here

Database location

  • Database can be either located in local computer or in network
  • In network installation database can be reached by network attached applications and users from different computers
  • Database can be distributed (one database is distributed in different geographical locations)
  • Local database
    • Application, Database management system and database are in the local computer
    • Can only be accessed locally
  • Network attached database
    • Application is on different computer and database management system and database on one computer typically

DB location options

  • Applications connect to database server in order to perform CRUD (Create, Read, Update and Delete) operations for data in database server
  • All queries from client side go through DBMS