Introduction to Databases

What is a Database?

A database is an accessible store of data. A database system is a database together with hardware and software for adding, deleting, updating, and querying the database.

A “real” database system takes care of the (multi-user) concurrency, (physical) distribution, security, logging, and fault tolerance concerns, allowing you to focus (almost) entirely on your business logic.

Let’s Get Right To It

Let’s see two cool web apps for exploring two different databases: Redis and Postgres.

Try Redis

Go to Try Redis. Enter TUTORIAL in the prompt. Take the tutorial.

SQL Fiddle

Go to SQL Fiddle. Select PostgreSQL from the drop down. Paste the following into the Schema Panel (I took the sample data from here and ported it from MySQL to Postgres.)

create table Author (
    id serial primary key,
    name varchar(70) not null,
    country varchar(100) not null);

insert into Author (name, country) values
    ('J.D. Salinger', 'USA'),
    ('F. Scott. Fitzgerald', 'USA'),
    ('Jane Austen', 'UK'),
    ('Scott Hanselman', 'USA'),
    ('Jason N. Gaylord', 'USA'),
    ('Pranav Rastogi', 'India'),
    ('Todd Miranda', 'USA'),
    ('Christian Wenz', 'USA');

create table Book (
    id serial primary key,
    title varchar(50) not null);

insert into Book (title) values
    ('The Catcher in the Rye'),
    ('Nine Stories'),
    ('Franny and Zooey'),
    ('The Great Gatsby'),
    ('Tender id the Night'),
    ('Pride and Prejudice'),
    ('Professional ASP.NET 4.5 in C# and VB');

create table BookAuthor (
    bookId integer not null,
    authorId integer not null,
    primary key (authorId, bookId),
    foreign key (authorId) references Author(id),
    foreign key (bookId) references Book(id));

insert into BookAuthor (bookId, authorId) values
    (1, 1), (2, 1), (3, 1), (4, 2), (5, 2), (6, 3),
    (7, 4), (7, 5), (7, 6), (7, 7), (7, 8);

Click on the Build Schema button.

Try out the following queries (use the Run SQL button to execute):

select id, title from Book;

select name
from Author
where country not in ('India', 'USA');

select b.title, a.name
from Book b
join BookAuthor ba
on b.id = ba.bookId
join Author a
on ba.authorId = a.id;

select b.title, count(ba.authorId) as numberOfAuthors
from Book b
join BookAuthor ba
on b.id = ba.bookId
group by b.title
order by 2 desc, 1 asc;

An Overview of the Field

Let’s step back for a second and get the big picture (yes, in a slide show):

Topics in Databases

Here are some things database people are into:

• Databases
  • Why?
  • Models
    • Modern
      • Key-Value
      • Document
      • Relational
      • Graph
    • Niche
      • Object
      • Triple Store
      • Deductive
      • Knowledge Base
      • Column Store
    • Obsolete
      • Hierarchical
      • Network
  • Design
    • DDLs and DMLs
    • Schema
    • Schemaless
    • Design Tools
  • Programming
    • Query Languages
    • Lightweight Library
    • ORM
  • Theory
    • ACID and BASE
    • Realtime vs. Batch
    • Relational Theory
      • Relational Algebra
      • Relational Calculus
      • Normalization Theory
  • Physical
    • Layout of data onto physical media
    • Indexing
    • Transactions and Concurrency
    • Recovery and Fault Tolerance
    • Main Memory Databases
    • Distribution
      • Replication
      • Sharding
      • Horizontal Scaling
  • Security
    • Cryptology
    • Access Control
    • Statistical Security
    • Flow Control
  • Big Data
    • Warehousing
    • Data mining
    • Web search
    • Analytics
    • Products
      • Hadoop, HBASE
      • Spark
      • Cassandra
      • Voldemort
      • Pokémon or Big Data?
  • Additional Topics
    • Media storage: image, video, audio
    • Spatial and Temporal databases
    • Historical databases
    • Time travel
    • Administration Tools
    • Query Optimizers

Wikpedia has a fairly decent outline of the field.