##

  • Table
  • Filters
  • Delete, Truncate
  • Joins
  • Data types
  • Function
  • Nulls
  • Integrity Constraints
  • Sequences
  • Synonyms
  • Set operations
  • Subqueries
  • Views
  • Virtual Columns
  • Indexes
  • Table Partitions
  • Materialized Views
  • Global Temporary Tables
  • Analytical Functions
  • Large Objects

Database normalization, shows how well designed your database is normal forms Codd normal forms

Entities and their relationships

User, Story are objects We need to define their relationships so we can feed User and Relationship objects according to their relationship

Every organization has an Entity-Relationship model of their business logic

Every ER model has an equivalent relation (table) in the database ER model is just a theoretical abstraction of the relationship ER and OOP are compatible too, what’s Entity in ER is Object in OOP

Entity has attributes, entity becomes table in database Attributes becomes the columns So if User is an Entity, its attributes are email, password, profile_picture, interests, etc.

Function Independence means that we choose an attribute (column) and make it a function For example, Users(email) = row For every email, there must be a single row

An attribute is the Primary Key of the relationship

We could add another entity, Story The tags of the story and interests of the User can come from the same set This is called Referential Integrity Between two Entities, tags of Story and interests of Users will have to come from the same list or the Referential Integrity is compromised

When we find this kind of column, we call it the Foreign Key of the table

In order to vet Referential Integrity, we setup database constraints

A primary key uniquely identifies each record within its own table, while a foreign key links records between tables by referring to the primary key of another table.

“could not inspect JDBC autocommit mode”

One possible reason:

  1. stale connections

Database Theory

Relational Algebra

SQL

Interviewer: “Tell me everything you know about SQL.” Me: “INSERT UPDATE DELETE DROP” (this is a joke)

SQLite

sqlite3 yourdb.db

To dump title of notes:

SELECT title FROM notes;

To dump title of notebooks:

SELECT title FROM folders;

To find duplicate value:

SELECT title, COUNT(*) c FROM notes GROUP BY title HAVING c > 1;

To find out structure:

PRAGMA table_info(notes);

To find out tables:

.tables

MySQL

13.37 – stopping mysqld:

  • Turn it off in Desktop Search in control center.
  • Edit: /home/sif/.config/akonadi/akonadiserverrc
  • Change it to: StartServer=false
  • Then: chmod -x /etc/rc.d/rc.mysqld
  • Log out then back in.

PostgreSQL

  • brew install postgresql
  • pg_ctl -D /usr/local/var/postgres start && brew services start postgresql
  • psql postgres
  • createuser sif
  • ALTER ROLE sif CREATEDB;
  • createdb sifapps -U sif

If it refuses to shut down..

launchctl unload -w ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist 
rm ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

Or:

launchctl unload -w ~/Library/LaunchAgents/org.postgresql.postgres.plist
rm ~/Library/LaunchAgents/org.postgresql.postgres.plist

NoSQL

MongoDB

Apache Cassandra

NoSQL

GoldenGate

“Oracle GoldenGate is a software product that allows you to replicate, filter, and transform data from one database to another database.”