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

Links

• http://www.tutorialized.com/tutorial/Oracle-DBA-FAQ---Introduction-to-Oracle-SQL-Developer/12849

• https://github.com/enkidevs/curriculum/wiki/SQL-Topic