This page is still under development

Relational Database

In computer science, a relational database is a database where information is organized in two-dimensional tables called relations or tables according to the model introduced by Edgar F. Codd in 1970.

The most popular database management systems since the 1980s (DBMS) have all supported the relational model as represented by the SQL language (Structured Query Language).

The most popular DBMSs include MySQL, PostgreSQL, Microsoft SQL Server, Oracle, Sybase, SAP HANA, and IBM DB2.

Relational model limit

Those DBMSs are efficient for a maximum data volume of tens or hundreds Tera Bytes. The Relational Database Management System is hosted on a single server.

Relational model tables

In the relational model, each table/relation represents one "entity type".

Let's simplify a flight database in three entities:

The Flight entity:

flight number from to departure time arrival time
EY455 SYD AUH 21h50 05h40
EY37 AUH CDG 9h10 14h35

The pilot entity:

pilot id name experience level
678 John Smith 3
888 Lee John 4

The "Assigned to" entity:

 Assigned id pilot id flight number date
01 678 EY455 31 mar 2017
02 888 EY37 01 apr 2017

Rows are called records or tuples.

Each row (each record) in a table has its own unique key called the primary key:

Rows in a table can be linked to rows in other tables by adding a column for the unique key of the linked row: such columns are known as foreign keys:

Codd showed that data relationships of arbitrary complexity can be represented by a simple set of concepts.

The software used to create, use and maintain relational databases are relational database management systems.

Virtually all relational systems use the SQL language to query databases. This language allows request operations of relational algebra such as intersection, selection and join.

Normalisation

REFER TO https://www.ntu.edu.sg/home/ehchua/programming/sql/relational_database_design.html

http://www.studytonight.com/dbms/database-normalization.php

or http://www.datanamic.com/support/lt-dez005-introduction-db-modeling.html

# MySQL

MySql is a simple SQL shell with input line editing capabilities.

Install MySql

Data type

Refer to https://dev.mysql.com/doc/refman/5.7/en/numeric-type-overview.html

SQL Basics

SQL statement ends with ;

To invoke SQL from the Linux console mysql -u root -p Then enter your Passwd

To Create a Database: CREATE DATABASE database_name;

To Grant Privileges GRANT All PRIVILEGES on database_name.* to 'user_name'@'localhost' identified By 'password';

To exit SQL console: quit

To execute SQL statements from a script from the Linux console

To import a SQL file into a database: mysql -u root -p database_name < /tmp/script.sql