User:Wikifycs-boys2018/sandbox

= DATABASE =

Introduction:
A Database is a collection of information that is organised so that it can be easily accessed,managed and updated.Data is organised into rows,columns and tables, and it is indexed to make it easier to find relevant information. Data gets updated,expanded and deleted as new information is added. Databases process workloads to create and update themselves, querying the data they contain and running applications against it.

Database, also called electronic database, any collection of data, or information, that is specially organized for rapid search and retrieval by a computer. Databases are structured to facilitate the storage, retrieval, modification, and deletion of data in conjunction with various data-processing operations. A database management system (DBMS) extracts information from the database in response to queries.

Computer databases typically contain aggregations of data records or files, such as sales transactions,product catalogs and inventories, and customer profiles.Typically, a database manager provides users with the ability to control read/write access, specify report generations and analyze usage.Some databases offer ACID(Atomicity,Consistency,Isolation,Durability) compliance to guarantee that the data is consistent and and that transactions are complete.

Databases are prevalent in large mainframe systems, but are also present in smaller distributed workstations and midrange systems, such as IBM's AS/400 and personal computers.

Evolution of Databases:
Databases have evolved since their inception in the 1960s, beginning with hierarchical and network databases, through the 1980s, with the object oriented databases, and today with SQL and NoSQL databases and cloud databases.

The relational model, first proposed in 1970 by Edgar F. Codd, departed from the tradition insisting that applications should search the data by content, rather than by following links. The relational model employs set of ledger-style tables, each used for different type of entity. Only in the mid 1980s did computing hardware become powerful enough to allow wide deployment of relational systems. By the early 1990s, however, relational systems dominated in all large scale data processing applications, and as of 2018 they remain dominant. IBM DB2, Oracle, MySQL and Microsoft SQL. Servers are the top DBMS. The dominant database language, standardised SQL for the relational model, has influenced database languages for each other data models.

Object oriented were developed in the 1980s to overcome the inconvenience of object-relational impedance mismatch, which led to the coming term "Post Relational" and also the developed of hybrid object-relational databases.

The next generation of post-relational databases in the late 2000s became to known as NoSQL databases, introducing fast key-values stores and document-oriented databases. A competing "next generation" known as NewSQL databases attempted new implementations that retained the relational/SQL model while aiming to match the high performance of NoSQL compared to commercially available relational DBMS.

Types of Databases:
Relational Database:-

This type of DBMS defines database relationships in form of tables, also known as relations. Unlike network DBMS, RDBMS does not support many to many relationships.Relational DBMS usually have pre-defined data types that they can support. This is the most popular DBMS type in the market. Examples of relational database management systems include MySQL, Oracle, and Microsoft SQL Server database.

A relational database, invented by E.F. Codd at IBM in 1970, is a tabular database in which data is defined so that it can be reorganized and accessed in a number of different ways. Relational databases are made up of a set of tables with data that fits into a predefined category. Each table has at least one data category in a column, and each row has a certain data instance for the categories which are defined in the columns.

The Structured Query Language(SQL) is the standard interface for a relational database. Relational databases are easy to extend, and a new data category can be added after the original database creation without requiring that you modify all the existing applications.

Distributed Database:-

A distributed database is a database in which portions of the database are stored in multiple physical locations, and in which processing is dispersed or replicated among different points in a network. Distributed databases can be homogeneous or heterogeneous. All the physical locations in a homogeneous distributed databases system have the same underlying hardware and run the same operating systems and databases applications. The hardware, operating systems or database applications in a heterogeneous distributed databases may be different at each of the locations.

Cloud Database:-

A cloud database is a database that has been optimized or built for a virtualized environment, either in a hybrid cloud, public cloud or private cloud. Cloud databases provide benefits such as the ability to pay for storage capacity and bandwidth on a per-use basis, and they provide scalability on demand, along with high availability. A cloud database also gives enterprises the opportunity to support business applications in a software-as-a-service deployment.

In a traditional cloud model, a database runs on an IT department's infrastructure via a virtual machine. Tasks of database oversight and management fall upon IT staffers of the organization.

NoSQL Database:-

NoSQL databases are useful for large sets of distributed data. NoSQL databases are effective for big data performance issues that relational databases aren't built to solve. They are most effective when an organization must analyze large chunks of unstructured data or data that's stored across multiple virtual servers in the cloud.

NoSQL encompasses a wide variety of different database technologies that were developed in response to the demands presented in building modern applications:


 * Developers are working with applications that create massive volumes of new, rapidly changing data types — structured, semi-structured, unstructured and polymorphic data.
 * Long gone is the twelve-to-eighteen month waterfall development cycle. Now small teams work in agile sprints, iterating quickly and pushing code every week or two, some even multiple times every day.
 * Applications that once served a finite audience are now delivered as services that must be always-on, accessible from many different devices and scaled globally to millions of users.
 * Organizations are now turning to scale-out architectures using open source software, commodity servers and cloud computing instead of large monolithic servers and storage infrastructure.

Object-oriented Database:-

Items created using object-oriented programming languages are often stored in relational databases, but object-oriented databases are well-suited for those items. An object-oriented database is organized around objects rather than actions, and data rather than logic. For example, a multimedia record in a relational database can be a definable data object, as opposed to an alphanumeric value.

There is currently no widely agreed-upon standard for what constitutes an OODBMS, and OODBMS products are considered to be still in their infancy. In the meantime, the object-relational database management system (ORDBMS), the idea that object-oriented database concepts can be superimposed on relational databases, is more commonly encountered in available products. An object-oriented database interface standard is being developed by an industry group, the Object Data Management Group (ODMG). The Object Management Group (OMG) has already standardized an object-oriented data brokering interface between systems in a network.

An object database stores complex data and relationships between data directly, without mapping to relational rows and columns, and this makes them suitable for applications dealing with very complex data. Objects have a many to many relationship and are accessed by the use of pointers. Pointers are linked to objects to establish relationships. Another benefit of an OODBMS is that it can be programmed with small procedural differences without affecting the entire system.

Graph Database:-

A graph-oriented database, or graph database, is a type of NoSQL database that uses graph theory to store, map and query relationships. Graph databases are basically collections of nodes and edges, where each node represents an entity, and each edge represents a connection between nodes.

Graph databases are growing in popularity for analyzing interconnections. For example, companies might use a graph database to mine data about customers from social media.Independent of the total size of your dataset, graph databases excel at managing highly-connected data and complex queries. With only a pattern and a set of starting points, graph databases explore the neighboring data around those initial starting points — collecting and aggregating information from millions of nodes and relationships — and leaving any data outside the search perimeter untouched.As with most technologies, there are few different approaches to what makes up the key components of a graph database. One such approach is the property graph model, where data is organized as nodes, relationships, and properties (data stored on the nodes or relationships).

The various reasons for which we require databases are:
To manage large chunks of data: Yes, you can store data into a spreadsheet, but if you add large chunks of data into the sheet, it will simply not work.

For instance: if your size of data increases into thousands of records, it will simply create a problem of speed.

Accuracy: When doing data entry files in a spreadsheet, it becomes difficult to manage the accuracy as there are no validations present in it.

Ease of updating data: With the database, you can flexibly update the data according to your convenience. Moreover, multiple people can also edit data at same time.

Security of data: There is no denying the fact that your data is less secure in spreadsheets. Anyone can easily get access to file and can make changes to it. With databases you have security groups and privileges you set to restrict access.

Data integrity: Data integrity also becomes a question when storing data in spreadsheets. In databases, you can be assured of accuracy and consistency of data due to the built in integrity checks and access controls.

TO END THIS DATABASE IS USED FOR STORAGE PURPOSE,SECURITY PURPOSE AND ALSO FOR BEST PERFORMANCE. The most simple answer is we need databases b/c they organize data in a manner which allows us to query data, sort data, and manipulate data in various ways.

= Query =

Definition - What does Query mean?
A query is a request for data or information from a database table or combination of tables. This data may be generated as results returned by Structured Query Language (SQL) or as pictorials, graphs or complex results, e.g., trend analyses from data-mining tools.

One of several different query languages may be used to perform a range of simple to complex database queries. SQL, the most well-known and widely-used query language, is familiar to most database administrators (DBAs).

The query database feature is equal in necessity to data storage capability. Thus, a number of query languages have been developed for different database engines and purposes, but SQL is by far the most ubiquitous and well-known. In fact, rookie database administrators often are surprised when they learn about the existence of other query languages, somewhat akin to how native English-speaking children are perplexed when hearing a foreign language for the first time. The element of surprise in both scenarios leads to a better understanding of other languages.

Query languages generate different data types according to function. For example, SQL returns data in neat rows and columns and is very similar to Microsoft Excel in appearance.

= Introduction to SQL = Structure Query Language(SQL) is a database query language used for storing and managing data in Relational DBMS. SQL was the first commercial language introduced for E.F Codd's Relational model of database. Today almost all RDBMS(MySql, Oracle, Infomix, Sybase, MS Access) use SQL as the standard database query language. SQL is used to perform all types of data operations in RDBMS.

SQL Command
SQL defines following ways to manipulate data stored in an RDBMS.

DDL: Data Definition Language
This includes changes to the structure of the table like creation of table, altering table, deleting a table etc.

All DDL commands are auto-committed. That means it saves all the changes permanently in the database.  Syntax for these commands are as follows :

1) Create Statement:

 * Creating a database:


 * Creating a table: Note: Constraints(such as primary key, null, not null, etc.) are optional and not necessary for table creations whereas column name and its datatype must be defined for each column you enter while using the create statement.

2) Alter statement:

 * Altering a table by adding a column to it:
 * Altering a table by altering/modifying (changing datatype of) a column in it. Note: Oracle(prior to version 10G ) and MySQL have the keyword MODIFY COLUMN instead of ALTER COLUMN and Oracle version 10G onward has the keyword MODIFY instead of ALTER COLUMN.
 * Altering a table by dropping/deleting a column: Note: Some database systems don't allow column deletion.

3) Truncate statement:

 * Delete data from table but not the table itself:

4) Drop statement:

 * Removes the entire table:

5) Rename statement:

 * Rename a table:

DML: Data Manipulation Language
DML commands are used for manipulating the data stored in the table and not the table itself.

DML commands are not auto-committed. It means changes are not permanent to database, they can be rolled back.


 * Select specific columns from a table:
 * Select specific columns from a table:

2) Insert statement:

 * Inserting values for all columns in table:
 * Inserting values in specific columns:

3) Update statement:

 * Modify existing records in a table: Note: If the WHERE clause is not mentioned; all the values of the column will be changed.

4)Delete Statement:

 * Delete an existing row/record from a table: Note: If the WHERE clause is not mentioned; all the records will be deleted.

TCL: Transaction Control Language
These commands are to keep a check on other commands and their affect on the database. These commands can annul changes made by other commands by rolling the data back to its original state. It can also make any temporary change permanent.  Syntax for these commands are as follows: 

1) Commit Statement:

 * To save changes permanently:

2) Rollback statement:

 * To undo changes:

3) Savepoint statement:

 * Rollback to a certain savepoint instead of rolling back all the prior changes:

Note: Not every database storage engine supports TCL. When creating tables with MySQL, you cannot use the commit,rollback and other TCL commands on a table/relation from a database as the default engine used by MySQL is myISAM which makes non-transaction tables. However we can get around this by changing the storage engine to one which creates transaction tables(e.g. InnoDB), hence allowing us to use TCL on that table from the database.

DCL: Data Control Language
Data control language are the commands to grant and take back authority from any database user.  Syntax for these commands are as follows: 

1) Grant statement:

 * Used to give privileges to an user.

2) Revoke statement:

 * Used to take back privileges from an user.

DQL: Data Query Language
Data query language is used to fetch data from tables based on conditions that we can easily apply.  Syntax for these commands are as follows :

1) Select statement:

 * Select all columns from a table:


 * Select specific columns from a table: