> For the complete documentation index, see [llms.txt](https://hanfak.gitbook.io/workspace/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://hanfak.gitbook.io/workspace/persistance1/databases.md).

# Databases

* Generally, all apps use a database to persist data.
  * Either for the running of the app (quartz scheduler),
  * for storing information to perform some logic later on,
  * for storing information to record workflow, or
  * audit information of a workflow for reporting purposes

## What

## Types

* Relational Databases
* NoSQL

## Key Understandings behind databases

These facts about computers influence the design of databases:

* You cannot do much with data on disk until you read it to memory.
* You cannot do much with data in memory until you read it to the CPU cache and registers.
* Reading from disk (SSD) is 2000 times slower than reading from memory.
* Reading from memory is 200 times slower than reading from the CPU cache (L1d)
* Disk is limited.
* Memory is scarce.
* CPU caches are scarcer.
* If you read a chunk of bytes to memory, use it fully, Or else some other chunk will take its place.
* If you read a chunk of bytes to the CPU try to use it fully. Or else some other chunk will take its place.

## Areas

### SQL commands

* Queries
  * select
    * <http://www.java67.com/2018/02/why-you-should-not-use-select-in-sql.html>
  * joins
  * where
  * alias
* Inserts
* update
* Delete
  * rows
  * users
  * cascade
* Table creation
  * Foreign keys
  * primary keys
  * composite keys
  * junctions
  * combining
    * one - one
    * one - many
    * many - many
  * constraints
  * indexes
    * <https://8thlight.com/blog/kyle-annen/2018/10/09/an-introduction-to-database-indexing.html>
  * tablespaces
  * partitions
    * by reference

#### links

* <https://www.oracle-dba-online.com/sql/oracle\\_sql\\_tutorial.htm>
* <https://www.w3schools.com/sql/>
* <https://www.tutorialspoint.com/oracle\\_sql/index.asp>
*

### PL/SQL

#### Links

* <http://dba.fyicenter.com/faq/oracle/oracle\\_introduction\\_pl\\_sql.html>
* <https://www.tutorialspoint.com/plsql/index.htm>
* <http://plsql-tutorial.com/>
* <http://www.plsqltutorial.com/>
* <http://dba.fyicenter.com/faq/oracle/oracle\\_pl\\_sql\\_working\\_with\\_procedure.html>
* <http://dba.fyicenter.com/faq/oracle/oracle\\_pl\\_sql\\_working\\_with\\_database\\_objects.html>
* <http://dba.fyicenter.com/faq/oracle/oracle\\_pl\\_sql\\_working\\_with\\_cursors.html>

### Users, permissions

### Editioning and views

* <https://oracle-base.com/articles/11g/edition-based-redefinition-11gr2>

#### Cross edition triggers

* <https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11\\_QUESTION\\_ID:2076251000346161474>

### Oracle developer GUI

* worksheets
* connecting to databases
* search through tables
* ordering in tables
* Viewing views
  * Must set edition to see the views

#### Links

* <http://www.oracle.com/technetwork/developer-tools/sql-developer/getting-started-155046.html>
* <http://www.devshed.com/c/a/oracle/how-to-use-the-oracle-sql-developer-tool/>
* <http://www.tutorialized.com/tutorial/Oracle-DBA-FAQ---Introduction-to-Oracle-SQL-Developer/12849>

### CQRS

* Only perform queries and and inserts, useful for event sourcing

### Datawhare housing

* creating views

### design

* <https://martinfowler.com/articles/evodb.html>
* <http://databaserefactoring.com/SplitColumns.html>

#### Books

* [database refactoring](https://martinfowler.com/books/refactoringDatabases.html)

## Links

* <http://www.tutorialized.com/tutorial/Oracle-DBA-FAQ---Introduction-to-Oracle-SQL-Developer/12849>
* <https://github.com/enkidevs/curriculum/wiki/SQL-Topic>