Introduction to Databases

From Juneday education
Jump to: navigation, search

This book is a work in progress

This book is being written and is not yet completed. Feel free to dive in and start reading anyway. Of course all feedback is welcome by the Authors.

TODO: Update the code examples to use a more consistent coding style. Replace camel case names and use underscore lower case names, but most importantly, make the code examples more consistent.

Update: We have started to refactor code examples to www.sqlstyle.guide.

Introduction

This book is an introductory book to the concept of databases. It can be used for self-studies, or as course literature for a basic database course (a course which serves as an introduction to databases and SQL). The book , exercises and video lectures use a command line interface with bash and SQLite3.

Regards from the authors: Rikard Fröberg and Henrik Sandklef

Before reading this book

Intended audience

Any one with basic knowledge of computing and IT, who wants to learn the basics of using databases.

Requirements

This book assumes you have knowledge of the working from the command line with a shell like bash. We also assume that you have a basic understanding of the standard streams and how they can be redirected or joined with pipes. If you feel that you need to freshen up your Bash skills before starting with this book, we recommend our Bash book, Bash-introduction.

Your environment

Your environment for the Introduction to databases programming book consists of the following parts:

  • Additional software (see below)

Additional software for intro-db

Automatic installation of additional software

You will also need some additional software in this course which you can install in any of the two ways using a terminal:

Download script and execute
  1. Download our boot strap script for this course jd-intro-db.sh:
    curl https://raw.githubusercontent.com/progund/utils/master/bin/jd-intro-db.sh -o jd-intro-db.sh
  2. make the script executable
    chmod a+x jd-intro-db.sh
  3. and execute it (with verification - recommended)
    ./jd-intro-db.sh --verify to skip verification, simply enter ./jd-intro-db.sh
Execute from web

curl https://raw.githubusercontent.com/progund/utils/master/bin/jd-intro-db.sh | bash

Manual installation of additional software

The script above uses files containing a list of packages to install. Here are links to the packages file for each platform we support. Install the packages in these files:

Adjusting your software

MacOS/ Homebrew

If you're using Homebrew you need to do the following steps to get a fresh version of SQLite:

$ brew link sqlite
Warning: sqlite is keg-only and must be linked with --force
Note that doing so can interfere with building software.

If you need to have this software first in your PATH instead consider running:
  echo 'export PATH="/usr/local/opt/sqlite/bin:$PATH"' >> ~/.bash_profile

Copy/paste the last line in a terminal

$ echo 'export PATH="/usr/local/opt/sqlite/bin:$PATH"' >> ~/.bash_profile

Next time you start bash you should get the version that comes with Homebrew.

Chapters

(Rough outline - subject to changes in the future)

Book companion/Extra material

Information
This book uses SQLite as the engine. You will find pointers to other resourses when the syntax differs a lot.

There are "check your progress" questions added to most exercise chapters. The suggested solutions with explanations can be found here:

Example exam with questions and answers can be found here:

Reading tips

Reading tips (Swedish)

We might add some more extra reading material here in the future.

What this book doesn't pretend to be

This book doesn't claim to be computer science or theoretical or even very technical. We strive to give the reader a practical introduction to what databases are, what database management systems are and how to use SQL to retrieve and manipulate data. We will not touch upon theoretical frameworks such as relational theory, relational algebra or set theory. Heck, we aren't even teaching logic. We believe that the reader is perfectly capable of getting a grasp of the basics behind databases and SQL without too much theoretic excursions. We are even inclined to think that understanding the basics of SQL and relational databases on this introductory level isn't simplified by introducing mathematics and computational models.

We focus on the practical and - as usual with our books - base a lot of the teaching through the use of exercises and assignments.

A warning and disclaimer; Some readers with a theoretical background might be offended by our avoiding such terminology as "relational model", "tuples", "Cartesian product", "Relational Calculus", "Cardinality", or, even "Projection"! This book is not for them. There are numerous theoretical books for those who have a strong need for more mathematical or philosophical descriptions of database usage.

Courses using this book

Chapter completeness (for teachers)

Chapter Meta section completed Text and examples Videos (number of) Slides Exercises or questions
Database:Introduction to Databases Yes Medium One (Swedish) One slides 5 (2 A4 pages)
Database:Retrieving rows of data - SQL SELECT Yes Medium One (Swedish)
One extra (EN)
One slides + one extra 12 (5 A4 pages)
Database:Getting started with SQLite Yes Hardly any One (Swe)
One live (Swe)
One slides 15 (10 A4 pages)
Database:Changing rows of data - SQL UPDATE Yes Short One (Swe)
One live (Swe)
One slides 9 (6 A4 pages)
Database:Deleting rows of data -SQL DELETE Yes Short One (Swe)
One live (Swe)
One slides 10 (5 A4 pages)
Database:Adding rows with new data - SQL INSERT Yes Short One (Swe)
One live (Swe)
One slides 8 (9 A4 pages)
Database:Combining rows of data from related tables - SQL JOIN Yes YES One (Swe)
One live (Swe)
A few extra on decomposing
One slides + One extra 12 (9 A4 pages)
NULL - representing the lack of value Yes YES 3 videos(English) One slides 0
Database:Adding constraints to prevent garbage data Yes YES Two videos(Swe)
One extra on enum (Eng)
One plus one slides 8 (10 A4 pages)
Database:Scraping data from the web Yes Hardly any Four videos(Swe) One slides 6 (13 A4 pages)