Neo4j Database and Design Paper Explain the need of Neo4j NoSQL database and design a database (Data Base Name: Football Club World Ranking) using Neo4j fo

Neo4j Database and Design Paper Explain the need of Neo4j NoSQL database and design a database (Data Base Name: Football Club World Ranking) using Neo4j for managing information about Football Club World Ranking (Club, Country and Points). Introduction to SQL ,
NoSQL ,BigSQL
Dr. Anand Singh Rajawat
Assistant professor
Shri Vaishnav Institute of Information Technology
Shri Vaishnav Vidyapeeth Vishwavidyalaya-Indore, India
Email id: anandsrajawat@svvv.edu.in
Agenda
What is SQL?
• Why SQL?
• Parts of SQL language
o Data-definition language (DDL)
o Data-manipulation language (DML)
o Data Control Language(DCL)
oTCL(transaction Control Language)
•
2
SQL RDBMS Concepts
• Data Types
• Limitations of Relational databases
•
What is SQL?
SQL is Structured Query Language,
which is a computer language for
storing, manipulating and retrieving
data stored in relational database.
• SQL is the standard language for
Relation Database System. All relational
database management systems like
“MySQL, MS Access, Oracle, Sybase,
Informix, postgres and SQL Server” use
SQL as standard database language
SQL query
•
Parser +
Optimizer
Query Language
Processor
File Manager
+
Transaction
manager
DBMS Engine
Physical Database
3
Why SQL?
•
•
•
•
•
4
Allows users to access data in relational database management systems.
Allows users to describe the data.
Allows users to define the data in database and manipulate that data.
Allows to embed within other languages using SQL modules, libraries & precompilers.
Allows users to create and drop databases and tables.
SQL language has several parts
Data-definition language (DDL)
• Interactive data-manipulation language (DML)
• Data Control Language(DCL)
•
•
5
TCL(transaction Control Language)
Data-definition language (DDL)
Statements used to define the database structure or schema.
Some examples:
• CREATE – to create objects in the database
• ALTER – alters the structure of the database
• DROP – delete objects from the database
• RENAME – rename an object
Note : Download Sqlit-3.11.2
Website Link : https://sqlitebrowser.org/dl/
6
Data-definition language (DDL)
CREATE – to create objects in the database
CREATE TABLE name_of_table
(name_of_column column_datatype
)
Example : CREATE TABLE Train_Times
(
start_location varchar(75),
destination varchar(75),
departs time,
arrives time
);
7
Data-definition language (DDL)
ALTER – alters the structure of the database
To add a new column,
Exaple : ALTER TABLE name_of_table
ADD name_of_field data_type
Example :
ALTER TABLE Train_Times
ADD runs_at_weekend char(1);
•
8
Data-definition language (DDL)
DROP – delete objects from the database
DROP COLUMN runs_at_weekend;
• DROP TABLE name_of_table
DROP TABLE Train_Times
•
9
Data-definition language (DDL)
RENAME – rename an object
ALTER TABLE table_name RENAME TO new_table_name;
•
10
Interactive data-manipulation language (DML)
DML- Data Manipulation Language: Statements used for managing data within schema
objects.
• SELECT – retrieve data from the a database
• INSERT – insert data into a table
• UPDATE – updates existing data within a table
• DELETE – deletes all records from a table, the space for the records remain .
11
Interactive data-manipulation language (DML)
SELECT – retrieve data from the a database
• This command allows database users to retrieve the specific information they desire
from an operational database.
• It returns a result set of records from one or more tables.
• SELECT Command has many optional clauses are as stated below:
•
Clause
Description
WHERE
It specifies which rows to retrieve.
GROUP BY
It is used to arrange the data into groups.
HAVING
It selects among the groups defined by the GROUP BY clause.
ORDER BY
It specifies an order in which to return the rows.
AS
It provides an alias which can be used to temporarily rename tables or
columns.
12
Interactive data-manipulation language (DML)
SELECT * FROM ;
SELECT * FROM Train_Times;
13
Interactive data-manipulation language (DML)
INSERT COMMAND
INSERT command is used for inserting a data into a table.
Using this command, you can add one or more records to any single table in a
database.
It is also used to add records to an existing code.
Syntax:
INSERT INTO (`column_name1` , `column_name2`
, . . . , `column_name_n` ) VALUES (`value1`, `value2`, . . . ,
`value n`);
14
Interactive data-manipulation language (DML)
UPDATE COMMAND
UPDATE command is used to modify the records present in existing table.
This command updates existing data within a table.
It changes the data of one or more records in a table.
Syntax:
UPDATE
SET
WHERE condition;
15
Interactive data-manipulation language (DML)
DELETE COMMAND
• DELETE command is used to delete some or all records from the existing table.
• It deletes all the records from a table.
•
•
Syntax:
DELETE FROM WHERE ;
16
Data Control Language(DCL)
GRANT-gives user’s access privileges to database.
• REVOKE-withdraw user’s access privileges given by using the GRANT
•
17
Data Control Language(DCL)
GRANT command gives user’s access privileges to the database.
This command allows specified users to perform specific tasks.
Syntax:
GRANT
ON
TO ;
18
Data Control Language(DCL)
REVOKE command is used to cancel previously granted or denied permissions.
This command withdraw access privileges given with the GRANT command.
It takes back permissions from user.
Syntax:
REVOKE
ON
FROM ;
19
TCL(transaction Control Language)
COMMIT– commits a Transaction.
• ROLLBACK– rollbacks a transaction in case of any error occurs.
• SAVEPOINT–sets a savepoint within a transaction.
• SET TRANSACTION–specify characteristics for the transaction.
•
20
TCL(transaction Control Language)
COMMIT– commits a Transaction.
• COMMIT command saves all the work done.
• It ends the current transaction and makes permanent changes during the
transaction.
• Syntax:
commit;.
•
21
TCL(transaction Control Language)
ROLLBACK COMMAND
• ROLLBACK command restores database to original since the last COMMIT.
• It is used to restores the database to last committed state.
• Syntax:
ROLLBACK TO SAVEPOINT ;
•
22
TCL(transaction Control Language)
SAVEPOINT COMMAND
• SAVEPOINT command is used for saving all the current point in the processing of a
transaction.
• It marks and saves the current point in the processing of a transaction.
Syntax:
SAVEPOINT
•
23
TCL(transaction Control Language)
SET TRANSACTION
SET TRANSACTION is used for placing a name on a transaction.
Syntax:
SET TRANSACTION [Read Write | Read Only];
24
SQL RDBMS Concepts
•
•
•
•
•
•
25
Define RDBMS
Define table
Define field
Define record or row
Define column
Define NULL value
Define RDBMS
Relational Database Management System. RDBMS is the basis for SQL and for all
modern database systems.
Example
• MS SQL Server
• IBM DB2
• Oracle
• MySQL
• SQLite
• Microsoft Access.
•
26
Define field
•
27
Every table is broken up into smaller entities called fields. The fields in the train
timetable table consist of Start, Destnation , Departs and Arrives.
Define table
The data in RDBMS is stored in database objects called tables. The table is a
collection of related data entries and it consists of columns and rows.
• Example:
A simple train timetable could look something like the table shown below:
•
28
Define record or row
A record, also called a row of data, is each individual entry that exists in a table.
• A records is a horizontal entity in table
•
29
Define column
•
30
A column is a vertical entity in a table that contains all information associated with a
specific field in a table.
Define NULL value
•
31
A NULL value in a table is a value in a field that appears to be blank, which means a
field with a NULL value is a field with no value.
SQL RDBMS Concepts
SQL Constraints: (applied on columns)
•
•
•
•
•
NOT NULL Constraint
UNIQUE Constraint
PRIMARY Key
FOREIGN Key
CHECK Constraint
Data Integrity:
•
•
•
•
32
Entity Integrity: There are no duplicate
rows in a table
Domain Integrity: Enforces valid
entries for a given column by restricting
the type
Referential Integrity: Rows cannot be
deleted which are used by other
records
User-Defined Integrity: Enforces some
specific business rules
Data Types:
33
Limitations of Relational databases
Define structure and schema
• Relational database systems provides consistency and integrity of data by
enforcing ACID properties (Atomicity, Consistency, Isolation and Durability )
• Most of the applications store their data in JSON format and RDBMS don’t provide
you a better way of performing operations such as create, insert, update, delete etc
•
on this data.
34
Introduction to NoSQL
•
•
•
•
•
•
35
Introduction to NoSQL
The Evolution of NoSQL
Why NoSQL?
Use NoSQL datastores
NoSQL Data Store Types
Advantages and Disadvantages of
NoSQL Databases
• Features of NoSQL
• SQL vs NoSQL
•
Introduction to NoSQL
NoSQL: Not Only SQL
• A NoSQL (originally referring to “non SQL” or “non relational”) database
• NoSQL is a non-relational DBMS, that does not require a fixed schema, avoids joins,
and is easy to scale.
• The purpose of using a NoSQL database is for distributed data stores with
humongous data storage needs.
• NoSQL is used for Big data and real-time web apps.
For example, companies like Twitter, Facebook, Google collect terabytes of user data
every single day.
•
36
The Evolution of NoSQL
•
•
•
•
•
•
•
37
1998- Carlo Strozzi use the term NoSQL for his lightweight, open-source relational
database
2000- Graph database Neo4j is launched
2004- Google BigTable is launched
2005- CouchDB is launched
2007- The research paper on Amazon Dynamo is released
2008- Facebooks open sources the Cassandra project
2009- The term NoSQL was reintroduced
Why NoSQL?
The concept of NoSQL databases became popular with Internet giants like Google,
Facebook, Amazon, etc. who deal with huge volumes of data.
• The system response time becomes slow when you use RDBMS for massive volumes
of data.
• To resolve this problem, we could “scale up” our systems by upgrading our existing
hardware. This process is expensive.
• The alternative for this issue is to distribute database load on multiple hosts
whenever the load increases. This method is known as “scaling out.”
•
38
Use NoSQL datastores
Avoidance of Unneeded Complexity
• High Throughput
• Horizontal Scalability and Running on
Commodity Hardware
• Avoidance of Expensive ObjectRelational Mapping
•
39
Complexity and Cost of Setting up
Database Clusters
• Compromising Reliability for Better
Performance
• The Current “One size fit’s it all”
Databases Thinking Was and Is Wrong
•
Introduction to NoSQL Database
40
Key-Value NoSQL Database
Document store NoSQL
database
Column (Family) NoSQL
Databases
Graph base NoSQL database
Redis
MongoDB
Bigtable
Neo4J
Oracle NoSQL Database
DocumentDB
Cassandra
Infinite Graph
Voldemorte
CouchDB
HBase
OrientDB
Aerospike
Couchbase
Vertica
FlockDB
Oracle Berkeley DB
OrientDB
Druid
Microsoft Azure Cosmos DB
Key-Value Database
A key-value database (also known as a key-value store and key-value store database)
is a type of NoSQL database that uses a simple key/value method to store data.
• Examples of Key-Value Stores
Key
Value
•
41
City
New York City
State
New York
Country
USA
Key-Value Database be used for
For example, key-value stores can be useful for storing things such as the following.
General Web/Computers
•
•
•
•
•
User profiles
Session information
Article/blog comments
Emails
Status messages
Ecommerce
•
•
•
•
•
•
•
•
42
Shopping cart contents
Product categories
Product details
Product reviews
Networking/Data Maintenance
Telecom directories
Internet Protocol (IP) forwarding
tables
Data deduplication
Use cases:
Here are some popular use cases of the key-value databases:
• For storing user session data
• Maintaining schema-less user profiles
• Storing user preferences
• Storing shopping cart data
43
Limitations of key-value databases
There are a lot of key-value databases. Bellow there are some general limitations
which are in line with the general idea of this database type. For a given database
some of them may not be true.
• The only way to look up values is by key.
• Range queries are not supported.
• There is no standard query language comparable to SQL for relational databases. In
consequence queries from one key-value database may not be portable to the other.
•
44
Document store NoSQL database
Document store NoSQL databases are similar to key-value databases in that there’s a
key and a value.
• Data is stored as a value. Its associated key is the unique identifier for that value.
• The difference is that, in a document database, the value contains structured or
semi-structured data.
• This structured/semi-structured value is referred to as a document and can be in
XML, JSON or BSON format.
•
45
Document store NoSQL database Example
•
Examples of document store NoSQL databases are
MongoDB
DocumentDB
CouchDB
MarkLogic
OrientDB
46
Use cases
Document store databases are preferable for:
• E-commerce platforms
• Content management systems
• Analytics platforms
• Blogging platforms
47
Limitation
Document store NoSQL databases are not the right choice
• if you have to run complex search queries or
• if your application requires complex multiple operation transactions.
48
Column (Family) Databases
A column store database is a type of database that stores data using a column
oriented model.
• A column store database can also be referred to as a:
• Column database
• Column family database
• Column oriented database
• Wide column store database
• Wide column store
• Columnar database
• Columnar store
49
Column store NoSQL database Example
•
Examples of column store NoSQL databases are
Column (Family)
NoSQL Databases
Bigtable
Cassandra
HBase
Vertica
Druid
50
Use cases:
Developers mainly use column databases in:
• Content management systems
• Blogging platforms
• Systems that maintain counters
• Services that have expiring usage
• Systems that require heavy write requests (like log aggregators)
51
Limitation
Column store databases should be avoided if you have to use complex querying or if
your querying patterns frequently change.
• Also avoid them if you don’t have an established database requirement, a trend
which we are beginning to see in new systems.
•
52
Graph base NoSQL database
Graph databases are basically built upon the Entity – Attribute – Value model.
• Entities are also known as nodes, which have properties. It is a very flexible way to
describe how data relates to other data.
• Nodes store data about each entity in the database, relationships describe a
relationship between nodes, and a property is simply the node on the opposite end
of the relationship.
• Whereas a traditional database stores a description of each possible relationship in
foreign key fields or junction tables, graph databases allow for virtually any
relationship to be defined on-the-fly.
•
53
Graph base NoSQL database Example
Graph base NoSQL
database
Neo4J
Infinite Graph
OrientDB
FlockDB
Microsoft Azure
Cosmos DB
54
Use cases
Graph base NoSQL databases are usually used in:
• Fraud detection
• Graph based search
• Network and IT operations
• Social networks, etc
55
Limitation
Lack of high performance concurrency
• Lack of standard languages
• Lack of parallelism.
•
56
Classifications – Categorization and Comparison
57
When should NoSQL be used:
•
•
•
•
•
58
When huge amount of data need to be stored and retrieved .
The relationship between the data you store is not that important
The data changing over time and is not structured.
Support of Constraints and Joins is not required at database level
The data is growing continuously and you need to scale the database regular to
handle the data.
Benefits of NoSQL
•
•
•
•
•
59
Elastic Scaling
RDBMS scale up – bigger load , bigger
server
NO SQL scale out – distribute data
across multiple hosts seamlessly
DBA Specialists
RDMS require highly trained expert to
monitor DB
NoSQL require less management,
automatic repair and simpler data
models
• Big data :Huge increase in data RDMS:
capacity and constraints of data
volumes at its limits
• NoSQL designed for big data Schema
agnostic
•
Benefits of NoSQL
Schema agnostic
• Scalability
• Performance
•
60
High availability
Global availability
Advantages of NoSQL:
High scalability :
• Vertical scaling
• Horizontal scaling
•
61
High availability :Auto replication feature in NoSQL databases
Disadvantages of NoSQL
Narrow focus
• Open-source
• Management challenge
•
62
GUI is not available
• Backup
•
•
Large document size
Features of NoSQL
Non-relational
• Schema-free
• Simple API
• Distributed
•
63
SQL vs NoSQL:
•
•
•
•
•
64
Relational Database Management
System (RDBMS)
These databases have fixed or static or
predefined schema
These databases are not suited for
hierarchical data storage.
These databases are best suited for
complex queries
Vertically Scalable
•
•
•
•
•
Non-relational or distributed database
system.
They have have dynamic schema
These databases are best suited for
hierarchical data storage.
These databases are not so good for
complex queries
Horizontally scalable
NoSQL Contenders
Open Source
•
•
•
•
•
•
•
65
MongoDB
Cassandra
Hadoop/Hbase
CouchDB
Riak
Neo4J
Couchbase
Commercial
SimpleDB
• Azure Table Storage
• Google App Engine Data Storage
• Riak
• InterSystems Cache
• Mark Logic Server
• Berkely DB
• Infinite Graph
•
Neo4J
Neo4j is a NoSQL database. It is highly scalable and schema-free. It’s world most
popular graph database management system.
• Neo4j was developed by Neo technology and called an ACID-compliant transactional
database with native graph storage and processing.
•
66
Features of Neo4j
Flexible Schema: Neo4j follows a data model called graph model. The graph contains
nodes and the nodes are connected to each other. Nodes and relationships store
data in key-value pairs known as properties.
• ACID Property: Neo4j supports full ACID properties (Atomicity, Consistency, Isolation
and Durability).
• Scalability: Neo4j facilitates you to scale the database by increasing the number of
reads/writes, and the volume without affecting the data integrity and the speed of
query processing.
• Reliability: Neo4j provides replication for data safety and reliability.
•
67
Features of Neo4j
Cypher Query Language: Neo4j provides a powerful declarative query language
called Cypher Query language. It is used to create and retrieve relations between
data without using the complex queries like Joins.
• Built-in Web applications: Neo4j also provides a built-in Neo4j browser web
application which can be used to create and retrieve your graph data.
• GraphDB: Neo4j follows Property Graph Data Model.
•
68
Neo4j Advantages
•
•
•
•
•
69
It is very easy to represent connected data.
It is very easy and faster to retrieve/traversal/navigation of more Connected data.
It represents semi-structured data very easily.
Neo4j CQL query language commands are in humane readable format and very easy
to learn.
It uses simple and powerful data model.
downloading Neo4j Desktop
Link: https://neo4j.com/download/
70
71
72
73
Open the Neo4j Browser
74
75
Installation and working of Couchbase
76
•
You can install Couchbase Server on Linux, Micro…
Purchase answer to see full
attachment