DATA 210 Exam #3

How to format the output for easier readability in MongoDB:

use the funtion .toArray() OR pretty()

How to delete an entire database;

DROP DATABASE <database_name>;

Is an empty string in PostgreSQL the same as null?

no

$ne

not equal

How to change the current database in MongoDB (database doesn't need to exist before using the command):

use database_db2

How do you display the numeric encoding of a file in terminal?

xxd -b <file_name> (in a text file, each character will be replaced with its ASCII code representation in binary)

Foreign Key Constraints:

-Used to establish and enforce a relation between two columns in two tables. -A link is created between two tables when the column that holds the primary key value for one table is referenced by the column in another table.

Views

-Views are pseudo-tables (not real tables) -A view can represent a subset of a real table. -A view can represent joined tables. -Because views are assigned separate permissions, we can use them to restrict table access so that users see only specific rows or columns of a table.

Address of an Array:

= (address of first element) + (size of an element * index)

What is MongoDB?

A NoSQL, documented-oriented database.

What is JSON?

A way to display object-style data that is human readable and structured.

How to delete everything (all the rows) from a table:

DELETE FROM <table_name>;

How to delete a table and everything that depends on it? (i.e. Views)

Drop ... CASCADE;

$nin

Not equal to any of the items in a particular list.

sort({"<field_name>":-1})

Sorts the list of the returned object in a descending order.

All elements in an array must be of the same data type.

True

How to show the current tables;

\dt

How to show the current database that's being used in MongoDB;

db

How to delete an entire collection in MongoDB:

db.collection.drop()

To view table content, we use the 'SELECT' statement:

SELECT * FROM countries;

When to use the CHECK keyword

When enforcing value constraints for table entries.

Primary Key Constraints:

-Primary keys must be unique. -A table can contain only one primary key constraint.

Indexes/Indices

-Speeds up queries performed on columns.

fineOne()

-This method is used to return a single document that satisfies the specified query criteria. -If multiple documents satisfy the query, this method returns only the first document/object/record.

How to show existing databases in MongoDB:

show dbs

What is a transaction?

-A transaction is a unit of work that is performed on a database. -Transactions are units or sequences of work accomplished in a logical order, whether in a manual fashion by a user or automatically by some sort of database program.

What is BSON?

-Binary JSON -Maps the content of JSON files to a special and more structured binary format. (helps speed up the search requests)

What are JSON's 6 data types?

1. Strings 2. Numbers 3. Boolean (true/false) 4. null 5. Arrays 6. Object/Documents

MongoDB benefits:

1. Useful when database is distributed over multiple machines. 2. Helps make app development easier and more elegant.

How to delete an entire table:

DROP TABLE cities;

How to create a new database:

CREATE DATABASE data210;

What are the two ways of scaling our program when our data becomes large?

Horizontal and Vertical.

'btree'

Index constraint type/data-structure created on primary keys.

Horisontal Scaling:

Means that you scale by adding more machines into your pool of resources.

Vertical Scaling:

Means that you scale by adding more power (CPU, RAM) to an existing machine.

sort({"<field_name":1})

Sorts the list of the returned objects in an ascending order.

What makes arrays different than other lists/collections?

The elements of the array are stored contiguously in the RAM. (makes accessing elements by their indices very fast.)

skip()

This method will control where MongoDB begins returning results.

dbStats()

This method will return statistics/information about the current database.

What are the four standard properties of transactions?

[ACID] 1. Atomicity: Ensures that all operations within the work unit are completed successfully; otherwise the transaction is aborted at the point of failure and previous operations are rolled back to their former state. 2. Consistency: Ensures that the database properly changes states upon a successfully committed transaction. 3. Isolation: Enables transactions operate independently of and transparent to each other. 4. Durability: Ensures that the result or effect of a committed transaction persists in case of a system failure.

PostgreSQL

[the following questions are related to PostgreSQL]

MONGODB

[the following terms/questions involve mongoDB]

How to change a database:

\connect data210;

How to show description of a table:

\d employee

How to show all commands:

\h

How to show the databases:

\list or \l

How to quit:

\q

How to delete the current database in MongoDB:

db.dropDatabase()

How to get the count of all records in a table in MongoDB:

db.my_table.count()

How to list all records in a table in MongoDB:

db.my_table.find()

How to insert a table/file in our current database in MongoDB:

db.my_table.insert({"f_name": "Adam", "l_name": "Smith"})

How to remove entire documents/objects in MongoDB that have "day"="Thur":

db.tip.remove({"day":"Thur"})

To remove the index from a particular field in MongoDB:

db.tips.dropIndex({"bill":1})

How to add an index to a particular field ("bill") in MongoDB:

db.tips.ensureIndex({"bill":1})

How to show our tables/files/collections in our current database in MongoDB:

show tables OR show collections