SQL

MySQL

An open source relational database. A cross platform which means it runs on a number of different platforms such as Windows, Linux, and Mac OS, etc.

NoSQL

An upcoming category of Database Management Systems. Its main characteristic is its non-adherence to Relational Database Concepts. Means "Not only SQL".

MySQL storage engines-MyISAM

It was the default storage engine for MySQL prior to version 5.5. MyISAM lacks support for transactions. Its advantages over InnoDB include simplicity and high performance.

Multimodal Database

This type of data base is a type of data processing platform that supports multiple data models that define how the certain knowledge and information in a database should be organized and arranged.

Data Warehouses

This type of database facilitates a single version of truth for a company for decision making and forecasting. It is an information system that contains historical and commutative data from single or multiple sources. The concept simplifies the reporting and analysis process of the organization.

Centralized Database

This type of database is a centralized location, and users from different backgrounds can access this data. This type of computers databases store application procedures that help users access the data even from a remote location.

Document/JSON Database

This type of database is a document-oriented database and the data is kept in documented collections, usually using the XML, JSON, BSON formats. One record can store as much data as y0ou want, in any data type (or types) you prefer.

Graph Databases

This type of database is a graph-oriented database that uses graph theory to store, map, and query relationships. The kinds of computer databases are mostly used for analyzing interconnections.

OLTP Databases

This type of database is able to perform fast query processing and maintaining data integrity in multi-access environments.

Cloud Databases

This type of database is optimized or built for such a virtualized environment. There are so many advantages of a cloud database, some of which can pay for storage capacity and bandwidth. It also offers scalability on-demand, along with high availability.

Open-source databases

This type of database stored information related to operations. It is mainly used in the field of marketing, employee relations, customer service, of databases.

Server access tool

To interact with MySQL and can communicate with MySQL server. It supports multiple user connections.

Relational databases

Type of database that defines database relationships in the form of tables. It is also called Relational DBMS, which is the most popular DBMS type in the market. e.g. MySQL, Oracle, and Microsoft SQL Server database

Distributed databases

Type of database that has contributions from the common database and information captured by local computers. In this type of database system, the data is not in one place and is distributed at various organizations.

UPDATE

Updates data in a database

NULL Value

Use NULL, which helps you to specify a value that is unknown, missing, or not applicable

Disadvantage of DBMS

1. Cost of Hardware and Software of a DBMS is quite high which increases the budget of your organization 2. Most database management systems are often complex systems, so the training for users to use the DBMS is required 3. In some organizations, all data is integrated into a single database which can be damaged because of electric failure or database is corrupted on the storage media 4. Use of the same program at a time by many users sometimes lead to the loss of some data 5. DBMS can't perform sophisticated calculations

Strings

Can be either literal strings or expressions with VARCHAR or CHAR data types

Part 11 - SQL/Schema

Information and Definition Schemas

INSERT

Inserts data into the row of a table

Part 2 - SQL/Foundation

It includes central elements of SQL

Part 1 - SQL/Framework

It offers logical concepts

MySQL storage engines - InnoDB

Its default storage engine provided with MySQL as of version 5.5. Inno DB supports foreign keys for referential integrity and also supports ACID-standard transactions.

Part 13 - Java Routines and Type

Java Routines and Types: This part of routines using the Java Programming Language

Database in SQL Server

Made up of a collection of tables that stores a detailed set of structured data. It is a table that contains a collection of rows, referred to as records or tuples, and columns that are also referred to as attributes. Each column in the table is designed to store a specific type of information.

Identifiers

Names of objects in the database, like user IDs, tables, and columns

Part 10 - Object Language Bindings

Object Language Bindings: This part specifies the syntax and semantics of embedding SQL in Java

Personal Database

This type of database is used to store data stored on personal computers that are smaller and easily manageable. The data is mostly used by the same department of the company and is accessed by a small group of people.

Data Manipulation Language (DML) - Types of SQL Statements

Used for searching, inserting, updating, and deleting data

Components of a database - Database Access Language

Used to access the data to and from the database, enter new data, update already existing data, or retrieve required data from DBMS. The user writes some specific commands in a database access language and submits these to the database.

Search Conditions

Used to select a subset of the rows from a table or used to control statements like an IF statement to determine control of flow

Scaling out

When you use a relational database for massive volumes of data, the system starts getting slow in terms of response time. To overcome this, we could "scale up" our systems by upgrading our existing hardware. The alternative to the above problem would be to distribute our database load on multiple hosts as the load increases.

Comments

Another SQL element which is used to attach explanatory text to SQL statements or blocks of statements. The database server does not execute any comment

CREATE

Defines the database structure schema

Part 15 - SQL/MDA

Provide SQL support for Multi-Dimensional Arrays

Advantages of DBMS

1. DBMS offers a variety of techniques to store & retrieve data 2. DBMS serves as an efficient handler to balance the needs of multiple applications using the same data 3. Uniform administration procedures for data 4. Application programmers never exposed to details of data representation and storage 5. A DBMS uses various powerful functions to store and retrieve data efficiently 6. Offers Data Integrity and Security 7. The DBMS implies integrity constraints to get a high level of protection against prohibited access to data 8. A DBMS schedules concurrent access to the data in such a manner that only one user can access the same data at a time. 9. Reduced Application Development Time

Five types of widely used SQL queries:

1. Data Definition Language (DDL) 2. Data Manipulation Language (DML) 3. Data Control Language (DCL) 4. Transaction Control Language (TCL) 5. Data Query Language (DQL)

Important reasons for using SQL

1. It helps users to access data in the RDBMS system. 2. It helps you to describe the data. 3. It allows you to define the data in a database and manipulate that specific data. 4. With the help of SQL, you can create and drop databases and tables. 5. SQL offers you to use the function in a database, create a view, and stored procedure. 6. You can set permissions on tables, procedures, and views.

Optimizations are based on two key factors:

1. Making the right choices when defining the database structure 2. Applying the most appropriate methods to read the data

Relational databases have the following advantages over NoSQL databases:

1. SQL (relational) databases have a mature data storage and management model. This is crucial for enterprise users. 2. SQL database supports the notion of views that allow users to only see data that they are authorized to view. The data that they are not authorized to see is kept hidden from them. 3. SQL databases support stored procedure SQL which allows database developers to implement a part of the business logic into the database. SQL databases have better security models compared to NoSQL databases.

Important components included in the SQL process

1. SQL Query Engine 2. Optimization Engines 3. Query Dispatcher 4. Classic Query Engine

Important components included in the SQL process are:

1. SQL Query Engine 2. Optimization Engines 3. Query Dispatcher 4. Classic Query Engine

MySQL Benefits

1. Supports multiple storage engines each with its own specifications while other systems like SQL server only support a single storage engine. 2. Has high performance compared to other relation database systems. This is due to its simplicity in design and support for multiple-storage engines. 3. Cost effective, it's relatively cheaper in terms of cost when compared to other relational database. 4. Cross-platform - MySQL works on many platforms which means it can be deployed on most machines.

Database Management System (DBMS)

A collection of programs that enable its users to access databases, manipulate data, report, and represent data. It also helps to control access to the database.

SQL

A language to operate databases. Includes database creation, deletion, fetching rows, modifying rows, etc. An ANSI (American National Standards Institute) standard language.

NoSQL database

A non-relational database that scales out better than relational databases and are designed with web applications in mind. They do not use SQL to query the data and do not follow strict schemas like relational models. With NoSQL, ACID(Atomicity, Consistency, Isolation, Durability) features are not guaranteed always.

Components of a database - Data

A raw and unorganized fact that is required to be processed to make it meaningful. Data can be simple at the same time unorganized unless it is organized. Generally, data comprises facts, observations, perceptions, numbers, characters, symbols, images, etc.

Components of a database - Procedure

A set of instructions and rules that help you to use the DBMS. It is designing and running the database using documented methods, which allows you to guide the users who operate and manage it.

Database

A systematic collection of data. They support electronic storage and manipulation of data. Database make data management easy.

MySQL Workbench

A visual database designing and modeling access tool for MySQL server relational database. It facilitates creation of new physical data models and modification of existing MySQL databases with reverse/forward engineering and change management functions. Its purpose is to provide the interface to work with databases more easily and in a more structured way.

Part 9 - Management of External Data

Adds syntax and definitions to SQL/Foundation, which allow SQL access to non-SQL data sources (files)

Data Definition Language (DDL) - Types of SQL Statements

Allows you to create objects like Schemas, Tables in the database

Data Control Language (DCL) - Types of SQL Statements

Allows you to manipulate and manage access rights on database objects

Main components of a database - Hardware

Consists of physical, electronic devices like computers, I/O devices, storage devices, etc. This offers the interface between computers and real-world systems.

Keywords

Each SQL statement contains single or multiple keywords

Data

Facts related to any object in consideration

Expressions

Formed from several elements, like constants, SQL operators, column names, and subqueries.

DELETE

Removes one or more rows from a table

DROP

Removes tables and databases

Part 14 - SQL/XML

SQL and XML

SELECT

Selects the attribute based on the condition described by the WHERE clause

Query

Set of instruction given to the database management system. It tells any database what information you would like to get from the database.

Special Values

Should be used in expressions and as column defaults when building tables

Database Management System (DBMS)

Software used to store and manage data. It guarantees quality, durability, and confidentiality of information.

Structured Query Language (SQL)

Standard language for data manipulation in a DBMS. It is used to talk to the data in a DBMS.

Part 4 - Persistent Stored Modules

Stored routines, external routines, and procedural language extensions to SQL

Variables

Sybase IQ supports local variables, global variables, and connection-level variables

Relational Database Management Systems (RDBMSs)

The most popular type of DBMS. The database consists of a structured set of tables and each row of a table is a record.

SQL

The standard language for dealing with Relational Databases (MySQL, Oracle, MS SQL Server, Sybase, etc). Can be used to insert, search, update, and delete database records.

Components of a database - Software

This is a set of programs used to manage and control the overall database. This includes the database software itself, the Operating System, the network software used to share the data among users, and the application programs for accessing data in the database.

Part 12 - SQL/Replication

This project began in 2000. This part helps to define the syntax and semantics to allow the definition of replication schemes and rules.

Part 3 - SQL/CLI

This standard includes central elements of SQL

Hierarchical

This type of DBMS employs the "parent-child" relationship of storing data. Its structure is like a tree with nodes representing records and branches representing fields. The windows registry used in Windows XP is a hierarchical database example.

Network DBMS

This type of DBMS supports many-to-many relations. It usually results in complex database structures. RDM Server is an example of datbase management system that implements the network model.

Object-oriented Databases

This type of computers database supports the storage of all data types. The data is stored in the form of objects. The objects to be held in the database have attributes and methods that define what to do with the data. e.g. PostgreSQL

NoSQL Databases

This type of database is used for large sets of distributed data. There are a few big data performance problems that are effectively handled by relational databases. This type of computers database is very efficient in analyzing large-size unstructured data.