Database: Collection and Organization

• A database is a tool for collecting and organizing information.
• A database is an organized collection of data including schemes, tables, queries, reports, views, and other objects.
• Data is typically organized to model aspects of reality, such as availability of hotel rooms for finding a hotel with vacancies.
• Databases can store information about people, products, orders, or anything else.
• Companies with huge amounts of data need databases to easily manipulate them.
• Many databases start as a list in a word-processing program or spreadsheet.
• As lists grow, redundancies and inconsistencies appear, making the data hard to understand and limiting search capabilities.
• There are 2 types of database storage which are via file database and via application database.

File Database

• Data is saved in a file and accessed through special libraries.
• SQLite3 has a connection library that is already in Python
• The most used type of database is SQLite3 because it is stored in every iPhone and Android device.
• File databases can be easily moved from one computer to another.

Application Databases

• A server database is a program that manages data
• All queries and requests are performed by that program.
• Application databases can be faster than file databases for big data.
• Examples include:
  • Oracle- the Mostly used commercial database.
  • MySQL, which is an open source
  • MSSQL developed by Microsoft
• PostgreSQL, an open-source database, is 5th most popular

Database Structure

• Databases are designed for storing, managing, and retrieving information.
• A server stores data in many databases which in turn store tables.
• Tables are constructed by fields which stores each row of data.
• Fields are classified by their data type, such as:
  • Integer
  • String
  • Date
  • Datetime
  • Boolean

The Tables

• For example, in the MySDU database the tables are students, course, and teachers
• The fields are:
  • Name (string/varchar)
  • Surname (string/varchar)
  • Age (integer)

SQL (Structured Query Language)

• SQL is a special language to retrieve, update, and delete data from a database.
• A SQL request is written in code that is send to a SQL server, and the response is then retrieved.

SQL Data Retrieving Example

• SELECT name, surname FROM contacts WHERE name = 'John' ORDER BY surname retrieves the name and surname fields from the contacts table where the name is John(filtering) and the output is sorted alphabetically (ordering) by surname.
• SELECT *: uses the asterisk to output all fields.

SQL (Insert, Delete, Update)

• INSERT INTO students (name, surname) values (‘Berik,'Sakenov') inserts a new student.
• DELETE FROM students WHERE name = 'Berik' deletes the student with the name Berik.
• UPDATE students SET name='Serik' WHERE name='Berik' updates a student's name from Berik to Serik.

SQL Aggregate Functions

• SELECT COUNT(*) FROM users counts the number of users.
• SELECT MIN(age) FROM users finds the minimum age among users.
• SELECT AVG(age) FROM users calculates the average age of users.
• SELECT DISTINCT(surname) FROM users selects and lists unique surnames from the users.

Using MySQL

• Install XAMPP for Foundations of web class
• Enter in browser localhost/phpmyadmin
• Links:
  • Apache Friends
  • Phone/Poor Connection

Relational Database

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