程序代写代做代考 graph data structure database 1

1
5/5/20

Practical Database Concepts Lecture 12: NoSQL and MongoDB
Santha Sumanasekara June 2020
1
A (not so) short history of DBMS
Ø http://upload.wikimedia.org/wikipedia/commons/2/22/RDBMS_tim eline.svg
2
2
1

3
5/5/20
Re-invent the wheel?
Ø In modern times, there were only very few re-inventions that made a great impact!
Ø Jet engine/ aircraft Ø Digital Camera
Ø …
Ø Is NoSQL the database equivalent to the jet engine in the aeronautical world?
Ø Why do you reinvent this new database model? 3
Re-invent the wheel?
Ø Data is everywhere. The digital transformation drives us to the frontiers that never reached.
Ø The digital transformation demands for rich content and big data!
Ø The structured database model, that catered the data management needs for almost half a century, has its inherent limitations.
4
4
2

5
5/5/20
An Example
Ø Consider the following example: what if we have a table that defines some structure and for a specific row we want to have some extra data?
Ø This is not easily done with a relational database model. 5
Another Example
Ø Consider the following two AirBnB listings:
Ø https://www.airbnb.com.au/experiences/79594 Ø https://www.airbnb.com.au/rooms/21087620
Ø How different are they?
Ø Can you easily store them in a table in a relational database?
6
6
3

7
5/5/20
Another Example
https://docs.atlas.mongodb.com/sample-data/sample-airbnb/#sample- document
7
NoSQL – Not just one model
https://en.wikipedia.org/wiki/NoSQL Ø Document databases
Ø Graph stores
Ø Key-value stores
Ø Wide-column stores
8
8
4

9
5/5/20
NoSQL Database Types
Ø Document databases: pair each key with a complex data structure known as a document. Documents can contain many different key-value pairs, or key- array pairs, or even nested documents.
9
{
“_id”: “10006546”,
“listing_url”: “https://www.airbnb.com/rooms/10006546”, “name”: “Ribeira Charming Duplex”,
“summary”: “Fantastic duplex apartment with three bedrooms,
located in the historic area of Porto, Ribeira (Cube)…”, “interaction”: “Cot – 10 € / night Dog – € 7,5 / night”, “house_rules”: “Make the house your home…”,
“property_type”: “House”,
“room_type”: “Entire home/apt”, …

Benefits of NoSQL
Ø Dynamic Schemas
Ø Relational databases require that schemas be defined before
you can add data.
Ø NoSQL databases are built to allow the insertion of data without a predefined schema.
Ø Nicely fits with agile development process.
Ø Data validation is delegated to the application.
10
10
5

11
5/5/20
Benefits of NoSQL
Ø Auto-sharding
Ø Partitioning can be done in the relational database systems, however, they require quite sophisticated mechanisms to ensure joins etc are executed efficiently.
Ø NoSQL databases, on the other hand, usually support auto- sharding, meaning that they natively and automatically spread data across an arbitrary number of servers, without requiring the application to even be aware of the composition of the server pool.
1 1
Benefits of NoSQL
Ø Replication
Ø Most NoSQL databases also support automatic database replication to maintain availability in the event of outages or planned maintenance events.
Ø Unlike relational databases, NoSQL databases generally have no requirement for separate applications or expensive add- ons to implement replication.
12
12
6

13
5/5/20
The name of the database was derived from the
word humongous to support the idea of processing large amount of data.
MongoDB
What a name for a database!
13
MongoDB
Ø MongoDB is a cross-platform document-oriented database
Ø MongoDB uses JSON-like documents. A document is a record (sort of like a row in a structured table). For example, if we have a database for users, a document would be one individual user.
Ø A document is made out of a list of key-value pairs.
14
14
7

5/5/20
MongoDB – similar but different!
Relational
database
table
row
column
CREATE TABLE people ( user_id Varchar(30), age Number,
status char(1), PRIMARY KEY (user_id)
);
MongoDB
database
collection
document
field
db.people.insertOne( { user_id: “abc123”, age: 55,
status: “A”
})
15
15

Demo time!
MongoDB MongoDB Compass
16
8

17
5/5/20
Insert a document
Ø In SQL:
Ø In MongoDB Shell:
INSERT INTO people (user_id, age, status) VALUES (‘A1234’,23,’A’);
db.people.insertOne(
{ user_id: ”A1234″, age: 23, status: “A” }
)
17
Insert a document
Ø In SQL:
Ø In MongoDB Compass:
INSERT INTO people (user_id, age, status) VALUES (‘A1234’,23,’A’);
18
18
9

5/5/20
A complex insertion
Ø In SQL: We cannot insert composite objects or arrays into tables
INSERT INTO people (user_id, name, address, age, sports) VALUES (‘A1234’,’Sam’, {1}{Happy St}{Happyville}{3999},
23,[’AFL’, ‘Cricket’, ’Rugby’]);
Ø In MongoDB Shell:
Address is a composite
19
db.people.insertOne(
{ user_id: ”A1234″, name: ”Sam”,
address: { no: 28, street: “Happy St”, suburb: “Happyville”},
object. Sports is an array.
age: 23, sports: [“AFL”, “Cricket”, “Rugby”] } )
19
A complex insertion
Address is a composite object. Sports is an array.
Ø In MongoDB Compass:
20
20
10

21
5/5/20
Find a document
Ø In SQL we use SELECT to find/ retrieve data. In MongoDB, we use find() method.
db.<>.find (<>, <>)
This specifies the conditions. Similar to WHERE clause in SQL
Ø Next few slides demonstrate how queries and projections are formed.
This specifies which fields to
display. Similar to SELECT 21 clause in SQL.
Find a document
Ø In SQL:
Ø In MongoDB Shell: db.people.find()
SELECT *
FROM people;
22
22
11

23
5/5/20
Find some fields in a document
Ø In SQL:
Ø In MongoDB Shell:
Leave this empty, as there isn’t any filtering condition.
SELECT ROWID, user_id, status
FROM people;
db.people.find(
{ },
{ user_id: 1, status: 1 }
)
If you do not want to see the
document ID, add _id: 0 to the projection (field list).
23
Find a document
Ø In SQL:
Ø In MongoDB Compass:
SELECT ROWID, user_id, status
FROM people;
24
24
12

5/5/20
Filter a document
filtering condition.
Ø In SQL:
Ø In MongoDB Shell:
SELECT user_id, status
FROM people
WHERE status = ‘A’;
db.people.find(
{ status: “A” },
{ user_id: 1, status: 1, _id: 0 }
)
25
25
Filter a document
Ø In SQL:
Ø In MongoDB Compass:
SELECT user_id, status
FROM people
WHERE status = ‘A’;
26
26
13

27
5/5/20
Filter a document – with multiple conditions
Ø In SQL:
Ø In MongoDB Shell:
AND is the default.
SELECT *
FROM people
WHERE status = ‘A’ AND
age = 25;
db.people.find(
{ status: “A”,
age: 25 } )
27
Filter a document
Ø In SQL:
Ø In MongoDB Compass:
SELECT *
FROM people
WHERE status = ‘A’ AND
age = 25;
28
28
14

29
5/5/20
Filter a document – with OR conditions
Ø In SQL:
SELECT *
FROM people
WHERE status = ‘A’ OR
age = 25;
Ø In MongoDB Shell:
OR is applied to an array of conditions. [ ] denotes an array.
db.people.find(
{ $or: [ { status: “A” } , { age: 25 } ] }
)
29
Filter a document – with OR conditions
Ø In SQL:
Ø In MongoDB Compass:
SELECT *
FROM people
WHERE status = ‘A’ OR
age = 25;
30
30
15

5/5/20
Filter a document – with “Not Equal”
Ø In SQL:
Ø In MongoDB Shell:
$ne means “not equal”
SELECT *
FROM people
WHERE age <> 25;
db.people.find(
{ age: { $ne: 25 } }
)
31
31
Filter a document – middle-aged people!
Ø In SQL:
Ø In MongoDB Shell:
db.people.find(
{ age: { $gt: 35, $lte: 60 }}
$gt: greater than
$gte: greater than or equal $lt: less than
$lte: less than or equal
SELECT *
FROM people
WHERE age > 35 AND age <= 60; ) 32 32 16 33 5/5/20 Filter a document – Partial Matches Ø In SQL: Ø In MongoDB Shell: db.people.find( {name: /Sam/} ) More complex regular expressions are possible. To be discussed later. E.g. {name: {$regex: /Sam/ } } SELECT * FROM people WHERE name LIKE ‘%Sam%; 33 Filter a document – Using Composite Fields Ø In SQL: Cannot be done! Ø In MongoDB Shell: SELECT * FROM people WHERE address.suburb = ‘Happyville’; db.people.find( {“address.suburb”: “Happyville”} ) Make sure to use double quotes. 34 34 17 5/5/20 Filter a document – Using Array Values Ø In SQL: Cannot be done! Ø In MongoDB Shell: SELECT * FROM people WHERE sport = [‘AFL’, ‘Cricket’] db.people.find( {sports:"AFL", "Cricket"} ) Return documents that exactly contain this array. 35 35 Aggregations -- count() Ø In SQL: Ø In MongoDB Shell: db.people.find().count() SELECT COUNT(*) FROM people; Object-oriented. Two ways to do it: db.collection.find().count() db.collection.count() 36 36 18 37 5/5/20 Sorting Ø In SQL: Ø In MongoDB Shell: db.people.find().sort({name: 1}) db.people.find().sort({name: -1}) 37 Descending order SELECT * FROM people ORDER BY name; Sorting Ø In SQL: Ø In MongoDB Compass: SELECT * FROM people ORDER BY name; 38 38 19 39 5/5/20 Further Readings Ø https://docs.mongodb.com/manual/reference/sql-comparison/ Ø https://docs.mongodb.com/manual/tutorial/insert-documents/ Ø https://docs.mongodb.com/manual/tutorial/query-documents/ 39 Life after coronavirus/ Practical Database Concepts! Ø Let’s recap what we have achieved (academically/ life experience). Ø Also what’s not achieved. 40 40 20 41 5/5/20 — Menti-time! Menti.com Code: to be supplied — Questions? 42 21