Databases 1.3/1.4

Concurrency

the ability of multiple users to access and edit a file simultaneously

Vendor Lock In

reliance on a specific vendors product that causes inability or difficulty to switch

SQL Standard aims to prevent

UUID

entirely unique identifier that can be used across multiple systems

Common Table Expression (CTE)

temporary name for a query to make results more readable

Referential Integrity

rules ensuring that relationships between data remain constant and valid

Decision Support System (DDS)

tool that helps to make business decisions using data in the main databases as well as data warehouses

allows for what-if situations to aid decision making or for managing the data held in the database

Memory Bottleneck

occurs when the database does not have enough RAM allocated for the processes it needs to execute

results in slow query execution and increased disk I/O because frequently used data cannot be stored in memory

Memory Leak

bug that occurs when memory is allocated to the database but not released back to the OS once it has been used

results in increasing amounts of memory being allocated to the same db even when workload is steady

Query Optimizer

tool that identifies the best way to execute a query

most often lowest cost, sometimes set to use a specific index

I/O Bottleneck

occurs when there is too much read/write data to be handled by one disk

RAID arrays can help

Isolation Levels

to what degree an active transaction needs to be separated from concurrent transactions

low levels may offer better performance and less latency but may also cause anomalies between concurrent transactions

Database Locking

when a record is being accessed by one user, no other user can may changes to it

used in multiuser environments

Disk Caching

improving performance by saving frequently accessed data instead of pulling from physical storage each time

In-Memory Database

database that is stored entirely on RAM, low latency

periodically writes to disk for storage

Sharding

breaking up a database into parts that make up a whole

each part has it’s own storage for more efficient queries

Connection Pooling

technique used to improve performance by reusing existing database connections instead of constantly creating new ones

connections are distributed to clients as needed

Indexing Bottleneck

when the process of maintaining and updating indexes becomes a performance bottleneck

too many indexes, poor index design

Validation Rule

constraint that prevents invalid, inaccurate, or inconsistent data from populating a database

necessary during data migration

Data Masking

replacing original data with fictional but realistic data so developer can work on migration without reveling sensitive information