Database1

Database Concepts

MySQL v5.7 (RDBMS)
Oracle 11g(ORDBMS)
MongoDB (NoSQL DBMS)

DBMS(Database Management System): Software that helps to manage data

Some Important Definitions

RDBMS: Relational Database Management System
ORDBMS: Object Relational DBMS (RDBMS+OODBMS)
OODBMS: Object Oriented DBMS
Database: Collection of Large amounts of data.

ANSI definition of DBMS : collection of programs that allows you to perform CRUD operations of data.

Computer: Processing(work done by computer) raw data to meaningful data/ processed data/ information.

DBMS vs RDBMS

Difference in Nomenclature

Field – attribute/column
Record – tuple/row/entity
File – table/relation

Relationship between two file is maintained programmatically.
vs
Relationship between 2 tables can be specified at the time of table creation using constraint.

More Programming
vs
Less Programming

More time required for s/w development
vs
Less time require for s/w development

Let’s take an example of Server with Employee data is located in Delhi and Client in Pune,
Now In DBMS to access Employees with Salary > $1000, we have to copy files to Pune then after processing, send the data back to Delhi (Server location)
vs
In RDBMS we can process data on server only.

High Network Traffic
vs
Low Network Traffic

Slow & expensive
vs
Faster (in terms of network speed) and cheaper (in terms of hardware cost, network cost, infrastructure cost)

Processing on Client Machine
vs
Processing on Server Machine(Client-Server Architecture)

Most of the RDBMS Support Client-Server Architecture. (exception is MS Access->local Database on same machine)

File level Locking(not suitable for multi-user)
vs
Row level Locking(internally table is not a file, internally every row is a file)

Suppose there is a Server located in Delhi, with Pune and Hyderabad as client, now in DBMS is Pune tries to update any table in Server then that table is locked for Hyderabad
vs
In RDBMS only row is locked .

Distributed databases not supported
vs
Most of the RDBMS support Distributed Databases (Banking system is an example of Distributed Databases)

No security (of data)

DBMS is dependent of OS for security
DBMS allows access to the data through the OS
Security is not a built-in feature of DBMS
vs
Multiple Levels of Security
Logging in Security (MySQL database username and password)
Command level Security (permission to issue MySQL commands)

create table, create function, create user, etc

Object level Security (access to table and objects of other users)

Various RDMBS available:

Informix (fastest in terms of processing speed)
Oracle (most popular RDBMS)
works on 113 OS
63% of world commercial DB market in Client-Server Environment
86% of world commercial DB market in the Internet Environment
Sybase (Good RDBMS)
MS SQL Server
Only works with Windows OS
MS Access (Single User)
DB2 (MainFrame Computer from IBM)
Postgres (Open source)

_ Our Focus will be on MySQL _

MySQL

Launched by Swedish company in 1995.
MySQL is a open-source RDBMS (most widely used open-source RDBMS).
Part of *LAMP open-source web-development.
Sun Microsystems acquired MySQL in 2008
Oracle Corporation acquired Sun Microsystems in 2010.

Various S/W development tools from MySQL

MySQL Command Line Client (client S/W)

Used for running SQL commands
Character based (text based)
Interface with database

MySQL Workbench (client S/W)

Used for running SQL commands
GUI based interface with database

MySQL Pl

MySQL Programming Language
Used for database Programming

MySQL Connectors

for database connectivity (JDBC, ODBC, Python, C, C++ etc)

MySQL for Excel

import, export, and edit MySQL data using MS Excel

MySQL Notifier

Start-up and Shutdown the MySQL database

MySQL Enterprise Backup

export and import of table data
used to take backups and restore from the backups

MySQL Enterprise High Availability

for replication (also know as data mirroring) concept of standby database

MySQL Enterprise Encryption

used to encrypt table data

MySQL Enterprise Manager

for performance monitoring, and performance tuning

MySQL Query Analyzer

for query tuning

SQL

Structured Query Language
Commonly pronounced as “Sequel”
conforms to ANSI standards (e.g 1 character = 1 Byte)
Conforms to ISO standards (for QA)
Common for all RDBMS(hence also known as RDBMS)
Initially founded by IBM (1975-77)
Initially known as RQBE (Relational Query by Example)
IBM gave RQBE free of cost to ANSI
ANSI renamed RQBE to SQL (Now, controlled by ANSI6)
In 2005, source code of SQL was rewritten in Java(100%)

Every row in Table is a file, Table is not a file which helps us achieve row-level locking.

Divisions of SQL

DDL (Data Definition Language): Create, Drop, Alter
DML (Data Manipulation Language): Insert, Update, Delete
DCL (Data Control Language): Grant, Revoke
DQL (Data Query Language): Select

Rules for Table Names, columns names and variable names

Oracle: Max 30 characters MySQL: Max 64 characters
A-Z, a-z, 0-9 allowed
Has to begin with an alphabet
Special characters, $, #, _ allowed
In MySQL to use reserved characters such as # int tableName and column Name, enclose it in backquotes(`).
EMP#`
134 reserved words not allowed

Under Linux & Unix, table_name and column_name are case-sensitive. But in Windows & macos are not case-sensitive