CH14 Flashcards
What is another term used to refer to a field?
Foreign Key
Your database has grown over time, and you need to improve query performance. What should you do? (Choose Two)
A. Perform a Database Dump
B. Remove infrequently accessed data.
C. Create an Index for Queried Data
D. Create a Foreign Key
B. Remove infrequently accessed data.
C. Create an Index for Queried Data.
You have increased the amount of RAM available for your cloud-based database instance. Which term describes this Action?
Scaling-Up
You have increased the number of compute nodes for your cloud-based database instance. Which term describes this action?
Scaling-Out
You have configured a database in the headquarters office located in Houston, Texas. Your company has office in major cities across Canada and the United STates. Users in Eastern Canada complain about slow query access to the database, while Texas users enjoy fast performance. What should you do?
A. Place a Read Replica in Eastern Canada
B. Create an Index
C. Place a writeable replica in Eastern Canada
D. Create a Foreign Key
A. Place a Read Replica in Eastern Canada
Which unit of measurement relates to disk throughput?
A. MHz
B. GHz
C. IOPS
D. Uptime
C. IOPS
Input and Output Operations per Second - is a unit of measurement related to disk performance; more IOPS means better performance.
Which type of database does not use a rigid schema?
A. Relational
B. On-Premises
C. Cloud
D. NoSQL
D. NoSQL
Which type of database uses a rigid schema?
A. Relational
B. On-Premises
C. Cloud
D. NoSQL
A. Relational
What statement regarding NoSQL database is correct? (Choose Two)
A. Each row must store the same type of data.
B. They are less scalable than relational databases.
C. Each row can store different types of data.
D. They are more scalable than relational databases.
C. Each row can store different types of data.
D. They are more scalable than relational databases.
Which of the following is a NoSQL database?
A. Microsoft SQL Server
B. MongoDB
C. Oracle DB
D. MySQL
B. MongoDB
Databases?
Databases store data. They can be used to store banking records, grocery store inventory lists, income tax filings, smartphone app configuration settings - DBs are everywhere. Database storage is Persistent, meaning the data will still be there even if you reboot the DB server.
Flat-File Storage?
Simple data storage, such as a Text File, is referred to as Flat-File storage.
Database Structure?
Think of Databases as Containers, within which Tables are used to store Records, also called Rows. Each Row in the Table consists of Fields, or Columns, that store small pieces of data such as First Name, Last Name, Date of Birth, and so on. Each column can have a maximum length such as ten characters, a requirement of data entry, and a data type such as INT (Integer) or CHAR (Character) and other Properties.
DB Containers > Tables > Records/Rows > Fields/Columns
Breaking a Record into Fields means Searching/Indexing your DB will be quicker.
Indexes are normally built on one or more Fields to speed up ordering and searching.
Database Scalability?
Means making sure it can accommodate current and future storage requirements as well as provide the best possible performance for data reads and writes.
Managed Service?
You can provision databases quickly without having to install anything, called Managed Service. Managed Services make provisioning resources such as databases quick and easy, thus allowing DB builders to focus on solving business problems instead of on the underlying technology.
Scaling Out?
Also called Scaling Horizontally, means adding compute nodes to a DB replica to support a busy computing environment. This can be done manually or automatically in response to preconfigured thresholds (autoscaling).
Scaling Up?
Or Vertical Scaling is when you increase the underlying computer horsepower, like adding more RAM for example.
Acronym: IOPS?
Input and Output Operations Per Second (IOPS) is a unit of measurement related to storage devices. More IOPS is better, but more IOPS means more cost.
Database Persistence?
Persistent Storage - Storing database data on disk is referred to as Persistent Storage, because the data hangs around even between system reboots.
Nonpersistent Storage - When the data is in Memory Cache, it is referred to as Nonpersistent Storage.
Considerations when choosing a DB Solution?
Type of DB? - Relational or NoSQL
Location? - On-Premises or in the Cloud
Security? - The ability to encrypt data at rest and in transit.
Isolation? - Deployed into it’s own Network
Vertical Scaling? - CPUs, RAM, Disk IOPS
Horizontal Scaling? - Additional Database server or replicas.
Relational Databases?
Uses a Structured Schema, or Blueprint, of how data should be stored through column (or field) definitions. With a structured schema, all rows (records) within the table follow the same rules for what type of data can be stored.
Examples:
Oracle DB
Microsoft SQL Server
MySQL
PostgreSQL
Foreign Key?
Related Database tables are joined using some kind of Unique Identified called a Foreign Key, which is a combination of one of the more fields (Columns) in both tables.
Relational DBs VS NoSQL?
Foreign Keys apply to Relational Databases, NoSQL databases are different in that they are NOT designed based on table relationships but instead are based on the queries that will be run.
Relational DB’s follow a strict schema, where NoSQL doesn’t require it (Can be Semistructured or Not Structured at all)
NoSQL benefits more towards Scalability and Performance, better for Big Data than Relational DBs.
NoSQL DBs?
NoSQL is NOT as rigid about the definition of (schema) of how data will be stored. There might be a Semistructured Schema, or NO Schema at all.
The benefits of NoSQL is Scalability and Performance > Relational Databases.
Examples:
Oracle NoSQL
MongoDB
CouchDB
IBM Domino
Database Creation and Management?
Regardless of Relational or NoSQL, the creation methods are pretty much the same.
Manual - You can use Commands such as Microsoft SQL Server CREATE DATABASE command, or you can use the GUI tools.
Scripted - You can create or acquire a script that will create the DB and even populate it with data.
Template - Cloud providers such as Microsoft Azure support resource deployment using Azure Resources Manager (ARM) templates which also support parameters for reusability. The equivalent solution available through AWS uses CloudFormation templates.
Virtual Machine Image - You can use an image that already includes the DB engine, this is prevalent in the cloud.
Database Creation/Management COMMANDS?
Alter - Modifies the design of an existing Relational DB or Table
Drop - Deletes an existing DB or Table
Grant/Revoke - Adds or Removes permissions for DB objects
DB Connectivity Ports?
By default:
Microsoft SQL Server listens to TCP Port 1433.
MySQL listens to TCP Port 3306.
Firewalls need to allow this inbound traffic for successful connection.
Database Hardening?
Hardening a database server involves:
Changing default configuration settings
Applying OS and DB updates
Enabling anti-malware solutions
Adhering to the Principle of Least Privilege (PoLP) - PoLP states that only the permissions required to complete a task should be granted, and Nothing More.
Database network transmissions can be protected through the use of Encrypted Virtual Private Network (VPN) tunnels or Secure Sockets Layer/Transport Layer Security (SSL/TLS) security certificates.
Protecting Data can be done in many ways. MicrosoftSQL for example supports Transparent Data Encryption (TDE).
Manipulating Data?
Manual Data Entry
Importing a supported file such as CSV file or backup file.
Programmatically from another app component locally or over the network.
INSERT INTO relational database command
UPDATE relational database command
Update Queries
Querying and Removing Data?
Over time data is no longer required and can be either deleted or archived for long-term storage. Another reason data eventually gets removed from a DB is because it can slow down DB actions such as updates and queries.
Common Data Removal and Backup Methods: Manual Data Deletion Automated Achieving Delete Queries Exporting a Database Dump to a File Database Local or Cloud Backup
Relational Databases can use the SELECT Command to filter data.
