OpenSourceVarsity

pdf icons

An Introduction to MySQL

What is database

A database is normally defined as a collection of coherent, meaningful data. The phrase collection of coherent data needs to have a point of reference to be understood. A simple example of a collection of coherent, meaningful data could be described as a residential postal address, which would contain:

  • A building name
  • A flat number in the building (if it is co-op housing society rather than a bungalow)
  • A road name
  • An area name
  • A state name
  • A pin code
  • A country name (This is optional, but necessary if the letter is destined to a foreign country)

Projecting this idea further, multiple addresses kept together in one place, such as an address book, could be termed as a coherent collection of data.

Thus an address book can be conceptualized as a database.  The postal addresses held within the address book, being the data that fills the database.

What is Database Management Systems (DBMS)

To be able to successfully design and maintain databases we have to do the following:

  • Identify which part of the world’s data is of interest to us
  • Identify what specific objects in that part of the world’s data are of interest
  • Identify a relationship between the objects

Hence, the objects, their attributes and the relationship between them (that are of interest to us) are stored in the database that is designed, built and populated with data for a specific purpose.

Software houses took up the challenge of designing a system that would help in managing data in such a database. These systems were called Database Management Systems (DBMS). DBMS is a system that allows inserting, updating, deleting and processing of data. Some of the DBMS developed by software houses were Oracle, Ingress, Sybase, Dbase 3+, Foxbase, Foxpro, MS Access, Dataease, Dataflex, Advanced Revelation, and so on.

Benefits of DBMS

  • The amount of redundant data (i.e. unnecessary data) within the data stored in a DBMS is much less when compare to other data storage systems
  • No more data inconsistencies
  • Stored data can be shared by a single or multiple users
  • Standards for capturing, storing and manipulating user data can be defined and followed
  • Data integrity can be maintained. Data integrity refers to the problem of ensuring that database contains only accurate data
  • Security of user data is relatively simple to implement when compared to other data storage systems
  • Data independence can be achieved, i.e. data and programs that manipulate the data are two different entities

What is a relational database management system (RDBMS)

A Relational Database Management System (RDBMS) is a database management system (DBMS) that is based on the relational model as introduced by Dr. Edgar F. Codd. Strictly speaking it should also satisfy Codd’s 12 rules, but in practice there is no DBMS that satisfies all these rules.

In fact, most successful DBMS that are considered to be relational violate the relational model in several important ways, including the Structured Query Language (SQL). However, most database practitioners and researchers use the term in a loose way such that most databases that support SQL are included.

Relational Database Management Systems (RDBMS) stores data in the form of related tables. RDBMS are powerful because they require few assumptions about how data is related or how it will be extracted from the database. As a result, the same database can be viewed in many different ways.

An important feature of relational systems is that a single database can be spread across several tables. This differs from flat-file databases, in which each database is self-contained in a single table.

The first released RDBMS that was a relatively faithful implementation of the relational model was the Multics Relational Data Store first sold in 1978. Others have been Berkeley Ingres, QUEL and IBM BS12.

Today, popular commercial RDBMS for large databases include Oracle, Microsoft SQL Server, Sybase SQL Server, and IBM’s DB2. The most commonly used free to use, enterprise strength, RDBMS are MySQL, PostgreSQL.

What is MySQL

Modern day web sites need database management systems to manage their dynamic content. DBMS systems store all of their data, and allow easy data manipulation.

ANSI SQL (i.e. Structured Query Language) is a language that all DBMS products must understand to be labeled as a DBMS system. A really powerful ANSI SQL compliant database server is MySQL.

MySQL is currently the most popular, open source, enterprise strength, database server in existence.

How MySQL works

MySQL is a database server program (often called a database engine).  The MySQL server and its associated data tables can both be located on a single computer, but can the database engine can ‘serve’ the data held in its tables to a variety of locations. Please refer to Diagram 1.

diagram1
Diagram 1

The MySQL Server is installed on a Server and can be accessed directly via a MySQL client, which sends ANSI SQL statements to the MySQL Server for processing and then displays the output of that processing to a user.

MySQL clients can be:
A Local Client – a program on the same machine as the MySQL server.

An example of this is the command line MySQL client being used in the rest of the MySQL lessons. There are also third party MySQL clients that have GUI’s via which ANSI SQL can be delivered to the MySQL engine running in the computer’s memory.

A Remote Client – a program on a different machine that can connect to the server and run SQL statements.

The remote client can be a simple character based client or a third party GUI based MySQL client.

The Features and Benefits of MySQL

Scalability and Flexibility Run anything from…

  • Deeply embedded applications with a footprint of just 1MB, or
  • Massive data warehouses holding terabytes of information
High Performance
  • Table and Index Partitioning
  • Ultra-fast load utilities
  • Distinctive memory caches
  • Full-text indexes, and more
High Availability
  • Run high-speed master/slave replication configurations with Row-Based and Hybrid Replication
  • Specialized Cluster servers offering instant failover
Robust Transactional Support
  • Complete ACID (atomic, consistent, isolated, durable) transaction support
  • Unlimited row-level locking
  • Distributed transaction capability, and
  • Multi-version transaction support
Web and Data Warehouse Strengths
  • High-performance query engine
  • Tremendously fast data insert capability, and
  • Strong support for specialized web functions, like fast full text searches
Strong Data Protection
  • Powerful mechanisms for ensuring only authorized users have access
  • SSH and SSL support safe and secure connections
  • Powerful data encryption and decryption functions
Comprehensive Application Development
  • Support for stored procedures, triggers, functions, views, cursors, ANSI-standard SQL, and more
  • Plug-in libraries to embed MySQL database support into nearly any application
Management Ease
  • Use Event Scheduler automatically schedule common recurring SQL-based tasks to execute on the database server
  • Average time from software download to complete installation is less than fifteen minutes
Open Source Freedom and 24 x 7 Support
  • Around-the-clock support and indemnification available through MySQL Network
  • Enterprise quality and enterprise ready, from installation to support
Lowest Total Cost of Ownership
  • Save on database licensing costs and hardware expenditures, all while cutting systems downtime
April 15, 2016
Design by Ivan Bayross and Meher Bala © 2017 All Rights Reserved
X