Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

Databases > Lecture 6 > Flashcards

Lecture 6 Flashcards

1
Q

CASE example

A

SELECT *
CASE
.WHEN condition THEN text
.WHEN condition THEN text
.ELSE text
END
AS QuantityText
from OrderDetails;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

CASE can also be used to…

A

Update

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

Transactions

A
  • Make state transitions independent from atomic operations.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

Ending Transitions

A
  • You commit or rollback the transaction
  • The server shuts down, this rolls back the transaction when the server is restarted.
  • You use a schema statement, your transition is committed and a new one started.
  • You use another start transaction, your transition is committed and a new one started.
  • A deadlock is detected.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

Transactions and ACID - A

A

Atomicity

  1. Transactions are often composed of multiple statements.
  2. Each transaction is treated as a single unit, which either succeeds completely, or fails completely.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

Transactions and ACID - C

A

Consistency

  1. A transaction can only bring the database from one valid state to another valid state.

(FK, Unique Constraints, Cascades, Triggers)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

Transactions and ACID - I

A

Isolation

  • Concurrent transactions should not interfere with each other.
  1. Avoid dirty read/write
  2. Avoid non repeatable read
  3. Avoid phantom read
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

Race condition (definition)

A
  • Undesirable situation that occurs when a device or system attempts to perform two operations at the same time, but operations must be done in a proper sequence.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

Multi-user transactions - Locks intro

A

We use locks to prevent access to data undergoing transactions.

  • Write/ Read lock

Locking can also be:
- Table, page or row locks

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

OLTP

A

Online Transaction Processing

  • High volume of transactions
  • Fast processing
  • Normalised data
  • Many tables
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

OLAP

A

Online Analytical Processing

  • High volume of data
  • Slow queries
  • Denormalised data
  • Fewer tables
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

Database files

A

A database file contains descriptions of how input data is to be presented to a program from internal storage and how output data is to be presented to internal storage from a program.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

Pages

A
  • Every page contains a header of metadata about the pages contents.

Slotted ot log structured pages

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

NSM

A

N-ARY Storage Model

  • DBMS stores all attributes for a single row contiguously in a page.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

NSM Advantages and Disadvantages

A

A:

  • Fast inserts, updates, and deletes.
  • Good for queries that need the entire tuple.

Drawbacks:

  • Not good for scanning large portions of the table and/or a subset of the attributes.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

DSM

A

Decomposition Storage Model

  • The DBMS stores the values of a single attribute for all tuples contiguously in a page.
  • Also known as a column store.
17
Q

DSM Advantages and disadvantages

A

A:

  • Reduces the amount wasted I/O because the DBMS only reads the data that it needs.
  • Better query processing and data compression.

D:

  • Slow for point queries, inserts, updates, and deletes because of tuple splitting/ stitching.
18
Q

NO SQL - Flexibility

A

Business needs changes over time.

  • We can add data without altering the schema.
  • The application is required to handle old vs new data
  • If you have all control of the data and application this is not an issue.
19
Q

Aggregated-Oriented Databases

A
  • When modelling things we often combine concepts together into natural aggregates.
20
Q

Schemaless Transaction

A

But document stores do not have this concept since we cannot isolate based on a schema.

21
Q

SQL Guidelines

A
  • Strict Schema
  • Relations
  • Distribution of data over tables
  • Difficult horizontal scaling
  • Vertical scaling is easier than horizontal scaling
22
Q

NO SQL Guidelines

A
  • No schema limitations
  • No relations
  • Aggregated data
  • Handles horizontal and vertical scaling well
  • High data volume
  • Large data storage
23
Q

MongoDB

A
  • Document store
  • Stores them as BSON (Binary JSON)
  • Querying power of SQL
  • Indexing
  • Replication
  • Load balancing
24
Q

Redis

A
  • Key-value store
  • In memory database
  • Simple to use, supports replication
  • Open source
25
Q

Uses of Redis:

A

Gaming, queues, messaging, caching, streaming …

26
Q

InfluxDB

A
  • Column Store
  • Specific for time series
  • Automatic timing
  • Optimised for inserting ordered data
  • No optimised for updates/deletes
  • Streaming
27
Q

UApplications

A

Databases (9 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2025 Bold Learning Solutions. Terms and Conditions