somehow trying to get into tech while having a lot of skill issues
Joined March 2023
- Tweets 5,024
- Following 211
- Followers 112
- Likes 49,163
777 Photos and videos
L77
data masking (protects pii):
• substitute: fake valid formats
• shuffle: reorder column data
• encrypt: ciphertext via keys
• nulling: mask with null/xxxx
• variance: ± random numeric shift
• tokenize: vault-mapped surrogates
#DBMS #SQL #Databases
L76
db encryption:
• at-rest: protects physical files.
• in-transit: ssl/tls for data in motion.
• column-level: encrypts targeted pii.
• full disk: secures all drive data.
pros: breach defense, aids compliance.
cons: overhead, key loss = data loss.
#DBMS #SQL #Databases
1
38
L76
db encryption:
• at-rest: protects physical files.
• in-transit: ssl/tls for data in motion.
• column-level: encrypts targeted pii.
• full disk: secures all drive data.
pros: breach defense, aids compliance.
cons: overhead, key loss = data loss.
#DBMS #SQL #Databases
L75
db security: role-based access control (rbac)
rbac stops permission micro-management.
flow: permissions (read) ➔ roles (admin) ➔ users.
why?
• scale: update 1 role to update all users in it.
• security: enforces least privilege.
#DBMS #SQL #Databases
1
91
L75
db security: role-based access control (rbac)
rbac stops permission micro-management.
flow: permissions (read) ➔ roles (admin) ➔ users.
why?
• scale: update 1 role to update all users in it.
• security: enforces least privilege.
#DBMS #SQL #Databases
L74
db scaling:
• vertical (scale up): add cpu/ram to 1 instance. simple, but hits hardware limits & creates a spof.
• horizontal (scale out): add servers via replication (master=writes, slave=reads) or sharding (split data across dbs).
#DBMS #SQL #Databases
2
83
L74
db scaling:
• vertical (scale up): add cpu/ram to 1 instance. simple, but hits hardware limits & creates a spof.
• horizontal (scale out): add servers via replication (master=writes, slave=reads) or sharding (split data across dbs).
#DBMS #SQL #Databases
L73
b-tree vs b tree:
• storage: b-tree stores data in all nodes. b only in leaves.
• links: b leaves form a linked list.
• queries: b optimized for range scans.
• fanout: b routing nodes hold only keys, minimizing i/o.
#DBMS #SQL #Databases
1
54
L73
b-tree vs b tree:
• storage: b-tree stores data in all nodes. b only in leaves.
• links: b leaves form a linked list.
• queries: b optimized for range scans.
• fanout: b routing nodes hold only keys, minimizing i/o.
#DBMS #SQL #Databases
L72 (3)
b-tree deletion: uses borrowing (parent drops, sibling rises) or merging.
b trees (modern dbms standard):
• internal nodes = routing (keys only).
• leaves = data linked list (fast range scans).
• higher fanout = less i/o.
#DBMS #SQL #Databases
2
50
L72 (3)
b-tree deletion: uses borrowing (parent drops, sibling rises) or merging.
b trees (modern dbms standard):
• internal nodes = routing (keys only).
• leaves = data linked list (fast range scans).
• higher fanout = less i/o.
#DBMS #SQL #Databases
L72 (2)
b-tree insertion: a bottom-up process guaranteeing strict balance.
1. traverse to correct leaf.
2. insert key in sorted order.
3. if keys > max (m-1), split the node in half.
4. promote median key to parent.
root splits increase tree height.
#DBMS #SQL #Databases
1
58
L72 (2)
b-tree insertion: a bottom-up process guaranteeing strict balance.
1. traverse to correct leaf.
2. insert key in sorted order.
3. if keys > max (m-1), split the node in half.
4. promote median key to parent.
root splits increase tree height.
#DBMS #SQL #Databases
L72 (1)
b-trees physically implement dbms indexes, providing o(log n) search, insertion, and deletion complexity.
classical b-tree block sizing equation (nodes hold keys child/data pointers):
m * pb (m - 1) * (k pd) <= b
#DBMS #SQL #Databases
2
88
L72 (1)
b-trees physically implement dbms indexes, providing o(log n) search, insertion, and deletion complexity.
classical b-tree block sizing equation (nodes hold keys child/data pointers):
m * pb (m - 1) * (k pd) <= b
#DBMS #SQL #Databases
L71 (3)
dbms indexing:
• primary: pk, sorted data (sparse).
• clustered: non-key, sorted. boosts range scans & group by.
• secondary: unsorted data (dense).
• multilevel: outer index maps inner blocks to minimize i/o.
sql: create/drop index.
#DBMS #SQL #Databases
1
3
159
L71 (3)
dbms indexing:
• primary: pk, sorted data (sparse).
• clustered: non-key, sorted. boosts range scans & group by.
• secondary: unsorted data (dense).
• multilevel: outer index maps inner blocks to minimize i/o.
sql: create/drop index.
#DBMS #SQL #Databases
L71 (2)
dbms indexing:
• dense: maps every record. fast exact-match, high storage/update cost.
• clustered: physically sorted data (primary=sparse).
• non-clustered: unsorted data (secondary=dense).
• multi-level: nested index layers.
#DBMS #SQL #Databases
3
153
L71 (2)
dbms indexing:
• dense: maps every record. fast exact-match, high storage/update cost.
• clustered: physically sorted data (primary=sparse).
• non-clustered: unsorted data (secondary=dense).
• multi-level: nested index layers.
#DBMS #SQL #Databases
L71 (1)
dbms indexing: minimizes disk i/o via a logically sorted search key & block pointer.
sparse index: stores one entry per block (usually the first search-key value). requires fewer index lookups & less storage.
#DBMS #SQL #Databases
2
90
L71 (1)
dbms indexing: minimizes disk i/o via a logically sorted search key & block pointer.
sparse index: stores one entry per block (usually the first search-key value). requires fewer index lookups & less storage.
#DBMS #SQL #Databases
L70
dbms tuning:
• sql: index where/joins, avoid select *, partition tables.
• storage: active index pages are cached in ram.
• allocation: aims for contiguous extents.
• files: heap (fast insert) or sequential (fast search).
#DBMS #SQL #Databases
1
69
L71 (1)
dbms indexing: minimizes disk i/o via a logically sorted search key & block pointer.
sparse index: stores one entry per block (usually the first search-key value). requires fewer index lookups & less storage.
#DBMS #SQL #Databases
L70
dbms tuning:
• sql: index where/joins, avoid select *, partition tables.
• storage: active index pages are cached in ram.
• allocation: aims for contiguous extents.
• files: heap (fast insert) or sequential (fast search).
#DBMS #SQL #Databases
1
26
L70
dbms tuning:
• sql: index where/joins, avoid select *, partition tables.
• storage: active index pages are cached in ram.
• allocation: aims for contiguous extents.
• files: heap (fast insert) or sequential (fast search).
#DBMS #SQL #Databases
L69
dbms recovery restores consistency post-crash.
failures: txn (logic), system (ram), media (disk).
phases: analysis (txn/page state), redo (ensure commits persist), undo (revert uncommitted).
techs: wal, shadow paging, backups.
#DBMS #SQL #Databases
1
93
L69
dbms recovery restores consistency post-crash.
failures: txn (logic), system (ram), media (disk).
phases: analysis (txn/page state), redo (ensure commits persist), undo (revert uncommitted).
techs: wal, shadow paging, backups.
#DBMS #SQL #Databases
L68
dbms concurrency:
• 2pl: growing & shrinking phases.
• strict: holds write locks to commit.
• rigorous: holds all locks to commit.
• conservative: pre-acquires all.
• timestamp: enforces order via rts/wts.
#DBMS #SQL #Databases
1
97
L68
dbms concurrency:
• 2pl: growing & shrinking phases.
• strict: holds write locks to commit.
• rigorous: holds all locks to commit.
• conservative: pre-acquires all.
• timestamp: enforces order via rts/wts.
#DBMS #SQL #Databases
L67
dbms concurrency control enforces safe, isolated execution rather than just testing for correctness.
prevents anomalies:
• lost update (ww)
• dirty read (wr)
• non-repeatable read
• phantom read
mechanisms: 2pl, timestamp, occ, mvcc.
#DBMS #SQL #Databases
1
124