Introduction to databases

What is database?

• Organized collection of data
  • Telephone number catalogue
  • List of students
  • Excel spreadsheet
  • File on a disk
• Computer-based database
  • Accessibility
  • Flexibility
  • Easy data manipulation
  • Security

Brief history

• Ancient times - organizing paper documents
• 60's - computerized systems
  • network model
  • hierarchical model
• 70's - relational model
• 80's - SQL language
• 90's - Web integration
• 00's - No-SQL
• ...

Relational database

• Relations between tables
  • Primary key
    • only one per table
    • cannot be NULL
    • unique
  • Foreign key
• Associations
  • One to One - 1..1
  • One to Many - 1..*
  • None to Many - 0..*
  • Many to Many - *..*

One Customer can place orders to many Orders

Database management system

DBMS =/= Database

Database management system

PostgreSQL

pgAdmin III

Your own C program

Estonian river data

Querying database

• Structured Query Language - SQL
• Special-purpose programming language 
• ISO standard
   
• Data definition and manipulation
  • SELECT
  • CREATE
  • INSERT 
  • UPDATE
  • DELETE

Querying database

Querying database

Querying database

Examples

Examples

Examples

• GIS - Geographical Information System

Class task

http://ati.ttu.ee/~hkinks/iag0582/praktikum11/db_tutorial.pdf

-- querying all columns and all rows
SELECT * FROM river;
-- limit the rows
SELECT * FROM river LIMIT 10;
-- limit the columns
SELECT id_river, name, length_km FROM river;
-- order the data by name
SELECT id_river, name, length_km FROM river ORDER BY name ASC LIMIT 10;
-- giving alias
SELECT id_river AS id, name, length_km FROM river ORDER BY id ASC LIMIT 10;
-- filtering by condition. Shorter than 10 km and name starts with "Ke"
SELECT id_jogi, joenimi, pikkus_km FROM joe_andmed 
WHERE pikkus_km < 10 AND joenimi LIKE 'Ke%';
-- join
SELECT * FROM tableA JOIN tableB ON tableA.foreignKey = tableB.primaryKey;