Databases and sql. Lecture 9

What is database?
A database is a tool for collecting and organizing information.
A database is an organized collection of data. It is the collection of schemes, tables, queries, reports, views and other objects. The data is typically organized to model aspects of reality in a way that supports processes requiring information, such as modelling the availability of rooms in hotels in a way that supports finding a hotel with vacancies.

What information to store in database?
Databases can store information about people, products, orders, or anything else.
Where it is used?
any company has huge amounts of data, so they need to manipulate them easily

What we can use instead of database?
Many databases start as a list in a word-processing program or spreadsheet. As the list grows bigger, redundancies and inconsistencies begin to appear in the data. The data becomes hard to understand in list form, and there are limited ways of searching or pulling subsets of data out for review.

Types of database
There are two types of database storage:
via file database
via application database

File database
All data is saved in file and can be accessed through special libraries
As example:
SQLite3 (connection library is already in python)
the most used type of database, since it is stored in every IPhone and Android
if you want to use sqlite3. check tutorial http://www.blog.pythonlibrary.org/2012/07/18/python-a-simple-step-by-step-sqlite-tutorial/
Advantages: easily can be moved from one computer to another

Application databases
Server database is a program that manages data
And all queries, requests are performed by that program
Advantages: can be more faster than file database for big data

Server database, examples
Oracle. Mostly used commercial database
MySQL (open-source) 2nd mostly used database
MSSQL - developed by Microsoft
PostgreSQL (open-source database, 5th by popularity)

Database structure
Databases are designed to offer an organized mechanism for storing, managing and retrieving information.
Server stores many databases
Database stores tables
Tables are constructed by fields
Table saves each data in a row
Fields have type. e.g. integer, string, date, datetime, boolean

Tables: example
Database is MySDU
Tables are students, course, teachers
Fields are name (string/varchar), surname (string/varchar), age (integer)

SQL
SQL - structured query language
SQL is special language to retrieve, update, delete data from database
How does it work:
we write SQL request in code that sends it to SQL server and then retrieve response

SQL data retrieving: example
SELECT name,surname
FROM contacts
WHERE name='John'
ORDER BY surname
fields to output, put * to output all fields
table that is retrieved
Filtering results by setting conditions
Output result sorted by specified field

SQL insert, delete, update
INSERT INTO students (name,surname) values (‘Berik’,’Sakenov’)
DELETE FROM students WHERE name = ‘Berik’
UPDATE students SET name=’Serik’ WHERE name=’Berik’

SQL aggregate functions
SELECT COUNT(*) FROM users
SELECT MIN(age) FROM users
SELECT AVG(age) FROM users
SELECT DISTINCT(surname) FROM users

To use MySQL
Install XAMPP (you will be using it in next semester for Foundations of web) http://www.apachefriends.org/en/xampp.html
enter in browser localhost/phpmyadmin
phone/poor connection:
https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_columns

Saving all information in one table

System of related tables
Minimum redundancy
Referential integrity
Database keys
Relational databases store information in atomic tables
Relational database