# 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
