Week 9: NoSQL Design
NoSQL, Database Design & MongoDB
Introduction To NoSQL
- NoSQL systems have emerged as a result of needing to store vast amounts of data such as emails, tweets and pictures, and SQL has a service overload as it is a powerful query language with concurrency control which most applications don't actually need
- Many large companies like Amazon, Facebook and Google have created their own NoSQL databases. These databases vary in terms of the data storing mechanism, for example, document-based, column-based, key-value based and graph-based storage. Some examples of famous NoSQL database systems are displayed below:
- There are
several characteristics to NoSQL systems related to distributed databases and data
modelling, some of which are listed below:
- Horizontal Scalability
- Sharding Of Files
- Schemaless
- Availability, Replication and Eventual Consistency
- Less Powerful Query Language
Types Of NoSQL Systems & The CAP Theorem
- There are four
common categories to NoSQL systems:
-
Key-Value NoSQL -
- Examples: Redis, Cassandra and Riak
- Values, which can be records, objects, documents or data structures, are identified by a unique key and these systems are ideal for shopping carts and for developing caching mechanisms
-
Document-Based NoSQL -
- Examples: MongoDB, CouchDB and Amazon DocumentDB
- Data is stored in documents using data formats like JSON (JavaScript Object Notation) where documents are accessed by a unique identifier and these systems are ideal for catalogs, e-commerce and book databases
-
Column-Based NoSQL -
- Examples: Google BigTable, Facebook Cassandra and Apache HBase
- Tables are partitioned by their columns, each of which is stored in a separate file and these systems are ideal for managing large data storage
-
Graph-Based NoSQL -
- Examples: Neo4J, FlockDB and Amazon Neptune
- Entities are stored as nodes and the relations between entities as edges, where each node and edge has a unique identifier which allows for graph traversal and these the systems are ideal for social network applications
-
Key-Value NoSQL -
- The CAP theorem
forms the basis for selecting a NoSQL database based on the system's requirements. The
theorem states that it is not possible to guarantee all the desirable properties
simultaneously, so we can only achieve a maximum of two out of the three guarantees for a
database, which are described below:
- Consistency - Implies all clients see the same data at the same time
- Availability - Implies all working nodes return a valid response for any request, without failing or exception
- Partition Tolerance - Implies the system continues to operate despite network failures
Introduction To MongoDB & Document-Based NoSQL
- MongoDB is a general purpose, document based database system where data is stored in JSON documents and it is currently the most popular NoSQL DBMS
- Below is a table displaying the difference in terminology between SQL and MongoDB:
- JSON is an open, human and machine readable standard used to transmit data objects composed of field-value pairs
- MongoDB represents JSON documents in a binary-encoded format called BSON (Binary JSON) which is lightweight, traversable and efficient
- Each document in MongoDB requires a unique _id as the first field since it serves as the primary key and if the _id field is omitted when inserting a document, a unique ObjectID is automatically assigned
| SQL Terms | MongoDB Terms |
|---|---|
| Database | Database |
| Table | Collection |
| Row | Document |
| Column | Field |
| Index | Index |
| Table Joins | Lookup |
| Primary Key | Primary Key |
MongoDB Documents & Commands
Basics About Documents
Data is stored in field-value pairs:
"name": "Joe Smith"
Fields are separated by commas:
"name": "Joe Smith", "course": "COMP23111", "score": 80.78
Objects (documents) are encapsulated in curly braces:
{"name": "Joe Smith", "course": "COMP23111", "score": 80.78}
Arrays are stored in brackets:
{"name": "Joe Smith", "courses": ["COMP23111", "COMP26120"]}
You can embed documents within a document:
{"name": "Joe Smith", "courses": {"course1": "COMP23111", "course2":
"COMP26120"}}
Creating Documents
Insert a single document into a collection:
db.collectionName.insertOne(<document>)
Insert many documents into a collection:
db.collectionName.insertMany([<document 1>, <document
2>, ...])
Insert an Audi A4 with registration number OE08KSO to the
vehicle collection:
db.Vehicles.insertOne({"Make": "Audi", "Model": "A4", "Registration Number":
"OE08KSO", "Type": "Car"})
Reading Documents
| Operator | Description |
|---|---|
| $eq | Matches values equal to a specified value |
| $lt | Matches values less than a specified value |
| $lte | Matches values less than or equal to a specified value |
| $gt | Matches values greater than a specified value |
| $gte | Matches values greater than or equal to a specified value |
| $ne | Matches values not equal to a specified value |
| $in | Matches any of the values specified in an array |
| $nin | Matches none of the values specified in an array |
The query specifies the condition using the operators from
the table above, and projection (which is optional) specifies which fields to
retrieve from the documents that meet the condition
Read documents from a collection:
db.collectionName.find(<query>, <projection>)
Display workers who are working for a rate of at most £8.72
per hour:
db.Workers.find({"Wage Rates.Rate Per Hour": {"$lte": 8.72}})
Display only the names of items from the menu that are below
£7.99:
db.Menu.find({"Price" : {"$lt": 7.99}}, {"Name":1})
Display the orders placed on 9th July 2023 which were at
least £30:
db.Orders.find({"Date" : {"$eq": "2023-07-09"}, "Payment.Amount": {"$gte": 30}})
Updating Documents
The filter denotes which document(s) to select for
updating, update action specifies which fields to modify and what value to
set them, and options (which is optional) is used to state additional actions
to perform
Updating a single document in a collection:
db.collectionName.updateOne(<filter>, <update
action>, <options>)
Updating many documents in a collection:
db.collectionName.updateMany(<filter>, <update
action>, <options>)
Update the orders whose status were 'Pending' to
'Paid':
db.Orders.updateMany({"Status": {"$eq": "Pending"}}, {"$set": {"Status":
"Paid"}})
Deleting Documents
The filter denotes which document(s) to select for
deleting, and options (which is optional) is used to state additional actions
to perform
Deleting a single document in a collection:
db.collectionName.deleteOne(<filter>, <options>)
Deleting many documents in a collection:
db.collectionName.deleteMany(<filter>, <options>)
Delete all the orders whose status are 'Rejected':
db.Orders.deleteMany({"Status": {"$eq": "Rejected"}})
Basic MongoDB Practice
Note: For the practice questions below, you will be able to view a sample solution query after 5 attempts are made.
Lets begin by practising how documents are represented in MongoDB. Start off by creating a JSON document for a student which must have a 'Name', 'Address', 'Degree Title' and 'Degree Classification' where the address field is composed of 'Street', 'City' and 'Post Code':
Note: You don't need to create an ID field for any of the tasks below as this is normally automatically generated and assigned to a document- We'll move on now and practice inserting into a MongoDB NoSQL database. Insert into the 'Allergens' table a new document where the name of the allergy is 'Soy' and the description (which is an optional field) is 'Allergic reactions to soy are typically mild, but all reactions can be unpredictable':
- Now lets look at how we search a collection to find field values. Based on the example document of a customer displayed above, from the 'Customers' collection, find the email address and phone numbers of customers from London who placed an order before June 2023 which cost at least £30:
- Finally, to wrap up the set of MongoDB practical questions, continuing with the 'Customers' collection for which an example document is shown above, write a query to delete all of the customers who used cards issued by 'American Express' to pay for their orders:
Customers Document
{
"_id": 1,
"First Name": "Joe",
"Last Name": "Smith",
"Phone Number": "0745354821",
"Email Address": "joe.smith@example.com",
"Orders": [
{
"_id": 1,
"Date": "2023-07-09",
"Time": "14:20",
"Status": "Rejected",
"Address": {
"_id": 1,
"Street": "Oxford Road",
"City": "Manchester",
"Post Code": "M13 9PL"
},
"Payment": {
""_id"": 1,
"Amount": 37.25,
"Card Issuer": "VISA"
}
},
{
"_id": 2,
"Date": "2023-07-09",
"Time": "14:25",
"Status": "Paid",
"Address": {
"_id": 2,
"Street": "Oxford Road",
"City": "Manchester",
"Post Code": "M13 9PL"
},
"Payment": {
"_id": 2,
"Amount": 37.25,
"Card Issuer": "Mastercard"
}
}
]
}
"_id": 1,
"First Name": "Joe",
"Last Name": "Smith",
"Phone Number": "0745354821",
"Email Address": "joe.smith@example.com",
"Orders": [
{
"_id": 1,
"Date": "2023-07-09",
"Time": "14:20",
"Status": "Rejected",
"Address": {
"_id": 1,
"Street": "Oxford Road",
"City": "Manchester",
"Post Code": "M13 9PL"
},
"Payment": {
""_id"": 1,
"Amount": 37.25,
"Card Issuer": "VISA"
}
},
{
"_id": 2,
"Date": "2023-07-09",
"Time": "14:25",
"Status": "Paid",
"Address": {
"_id": 2,
"Street": "Oxford Road",
"City": "Manchester",
"Post Code": "M13 9PL"
},
"Payment": {
"_id": 2,
"Amount": 37.25,
"Card Issuer": "Mastercard"
}
}
]
}
Practice Question
Which of the following categories are common to NoSQL systems?
Designing Document-Based Databases In NoSQL
- Collections (tables) in NoSQL don't require a document schema by default thus some documents in a collection may have keys which have been omitted in other ones. Hence, the data type of each field in a collection may differ across documents
- NoSQL databases
allow for normalised and denormalised schemas which are known as embedding and referencing
respectively in MongoDB:
-
Embedding -
- Captures the relationship between data by storing all the data in a single document by embedding documents in a field or array within a document itself
-
Below is an example of how an array of product documents are embedded within
an order document as one order contains one or many products
--- Orders Document ---
{
"_id": 1,
"First Name": "Joe",
"Last Name": "Smith",
"Products": [
{
"_id": 1,
"Name": "M2 Mac Mini",
"Description": "8GB Unified Memory + 512GB SSD Storage",
"Price": 849.00
},
{
"_id": 2,
"Name": "M2 iPad Pro",
"Description": "12.9-Inch Display + 1TB Storage In Space Grey",
"Price": 2049.00
}
],
"Address": {
"_id": 1,
"Street": "Oxford Road",
"City": "Manchester",
"Post Code": "M13 9PL"
}
}
-
Referencing -
- Captures the relationship between data by including links/references of one document in another
-
Below is an example of how a customers details and their order are stored in
separate documents and the customer_id is referenced in the orders
document to uniquely identify who the order belongs to
--- Customers Document ---
{
"_id": 3,
"First Name": "Joe",
"Last Name": "Smith",
"Address": {
"_id": 1,
"Street": "Oxford Road",
"City": "Manchester",
"Post Code": "M13 9PL"
}
}--- Orders Document ---
{
"_id": 1,
"customer_id": 3,
"Products": [
{
"_id": 1,
"Name": "M2 Mac Mini",
"Description": "8GB Unified Memory + 512GB SSD Storage",
"Price": 849.00
},
{
"_id": 2,
"Name": "M2 iPad Pro",
"Description": "12.9-Inch Display + 1TB Storage In Space Grey",
"Price": 2049.00
}
]
}
-
Embedding -
- Below are rules
of thumbs we may consider when deciding whether to embed or reference:
-
Embedding is appropriate when -
- Related items are frequently used/fetched together
- There is a one-to-one relationship between documents
- Data doesn't change/grow much
- Related documents have a similar volatility
-
Referencing is appropriate when -
- Embedding results in substantial data duplication
- Embedded documents grow
- There is a many-to-many relationship between documents
- Fast writing speed is required hence less traversing of documents is needed
-
Embedding is appropriate when -
End Of Week Quiz
Note: A score of 70% or higher would mean you
have successfully completed the week 9 workshop