Never confuse education with intelligence, you can have a PhD and still be an idiot.
- Richard Feynman -



Difference between revisions of "Introduction to Databases"

From Juneday education
Jump to: navigation, search
(Initial revision)
 
(Reading tips: Added link to www.sqlite.org/omitted.html and www.sqlite.org/quirks.html)
 
(87 intermediate revisions by 2 users not shown)
Line 1: Line 1:
= This book is a work in progress =
+
= This book is continuously updated =
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]].
+
This book is edited every year according to feedback we get from e.g. course evaluations and student input. Feel free to dive in and start reading anyway. Of course all feedback is welcome by the [[Authors]].
 +
 
 +
'''''Update:''' We have started to refactor code examples to [https://www.sqlstyle.guide/ www.sqlstyle.guide].''
 +
 
 
= Introduction =
 
= 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).
+
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: [[Authors#Rikard_Fr.C3.B6berg|Rikard Fröberg]] and [[Authors#Henrik_Sandklef|Henrik Sandklef]]
 
Regards from the authors: [[Authors#Rikard_Fr.C3.B6berg|Rikard Fröberg]] and [[Authors#Henrik_Sandklef|Henrik Sandklef]]
 +
 
= Before reading this book =
 
= Before reading this book =
 
== Intended audience ==
 
== Intended audience ==
 
Any one with basic knowledge of computing and IT, who wants to learn the basics of using databases.
 
Any one with basic knowledge of computing and IT, who wants to learn the basics of using databases.
 
== Requirements ==
 
== 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]].
+
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]].
= Chapters (Rough outline - subject to changes in the future)=
+
 
 +
== Your environment ==
 +
On the first topic page of this course material, you will find [[Database:Introduction_to_Databases#Set_up_your_environment|instructions for setting up your environment]].
 +
 
 +
=Chapters=
 +
(Rough outline - subject to changes in the future)  
 
* [[Database:Introduction to Databases]]
 
* [[Database:Introduction to Databases]]
 +
* [[Database:Exercise - Introduction to Databases]]
 
* [[Database:Retrieving rows of data - SQL SELECT]]
 
* [[Database:Retrieving rows of data - SQL SELECT]]
 +
* [[Database:Exercise - Retrieving rows of data - SQL SELECT]]
 
* [[Database:Getting started with SQLite]]
 
* [[Database:Getting started with SQLite]]
 +
* [[Database:Exercise - Getting started with SQLite]]
 +
** [[SQL_SELECT_Check_Answers]] (check your progress answers)
 +
** [[Database:MariaDB show syntax|Some MariaDB equivalents of .schema .databases .tables etc]]
 
* [[Database:Changing rows of data - SQL UPDATE]]
 
* [[Database:Changing rows of data - SQL UPDATE]]
 +
* [[Database:Exercise - Changing rows of data - SQL UPDATE]]
 +
** [[SQL_UPDATE_Check_Answers]] (check your progress answers)
 
* [[Database:Deleting rows of data -SQL DELETE]]
 
* [[Database:Deleting rows of data -SQL DELETE]]
 +
* [[Database:Exercise - Deleting rows of data -SQL DELETE]]
 +
** [[SQL_DELETE_Check_Answers]] (check your progress answers)
 
* [[Database:Adding rows with new data - SQL INSERT]]
 
* [[Database:Adding rows with new data - SQL INSERT]]
* [[Database:Assignment 1]]
+
* [[Database:Exercise - Adding rows with new data - SQL INSERT]]
 +
** [[SQL_INSERT_Check_Answers]]
 +
* [[Database:Assignment 1 - The basics]]
 
* [[Database:Combining rows of data from related tables - SQL JOIN]]
 
* [[Database:Combining rows of data from related tables - SQL JOIN]]
 +
** Extra material: Decomposing a table: [https://vimeo.com/couchmode/channels/1373390 Databases - decomposing (Full playlist)] | [https://vimeo.com/267584351 Databases - decomposing 1/3] | [https://vimeo.com/267584386 Databases - decomposing 2/3] | [https://vimeo.com/267584369 Databases - decomposing 3/3] | [[:Media:Database - Decomposing a table.pdf|Database - Decomposing a table (PDF)]]
 +
* [[Database:Exercise - Combining rows of data from related tables - SQL JOIN]]
 +
** [[Database:MariaDB ALTER TABLE syntax|ALTER TABLE MariaDB version]]
 +
* [[Database:NULL-representing lack of value|NULL - representing the lack of value]]
 
* [[Database:Adding constraints to prevent garbage data]]
 
* [[Database:Adding constraints to prevent garbage data]]
 +
** [[Database:MariaDB constraints triggers|MariaDB version (optional reading)]]
 +
* [[Database:Exercise - Adding constraints to prevent garbage data]]
 +
** [[SQL_constraints_Check_Answers]]
 +
** [[:Media:DB SQLite-Simulating enums.pdf|Simulating enum types in SQLite as a form of constraint (PDF/extra lecture)]]
 +
** [[Database:MariaDB Enum|MariaDB/MySQL syntax for enum types (optional reading)]]
 
* [[Database:Scraping data from the web]]
 
* [[Database:Scraping data from the web]]
 +
* [[Database:Exercise - Scraping data from the web]]
 
* [[Database:Assignment 2 - JOINs and Constraints]]
 
* [[Database:Assignment 2 - JOINs and Constraints]]
==Book companion - TBD==
+
 
We might add some extra reading material here.
+
==Book companion/Extra material==
 +
{{SmallInfo|text=This book uses SQLite as the engine. You will find pointers to other resourses when the syntax differs a lot.|width=250}}
 +
* [[:Media:Getting started with MariaDB.pdf|Getting started with MariaDB]] - Extra material for students/readers who wish to learn also a little about MariaDB/MySQL
 +
* [[Database:MariaDB show syntax|Some MariaDB equivalents of .schema .databases .tables etc]]
 +
* [[:Media:Getting started with postgresql.pdf|Gettring started with PostgreSQL]] - Extra material for students/readers who wish to learn also a little about PostgreSQL
 +
* [[:Media:Extra-lecture-Create-table.pdf|Short presentation on CREATE TABLE syntax (PDF)]] [https://vimeo.com/253775086 Databases - create statement (video)]
 +
* Extra lecture: [https://vimeo.com/couchmode/channels/1369913 Databases - WHERE clause (Full playlist)] | [https://vimeo.com/266664127 Databases - where 1/2] | [https://vimeo.com/266664136 2/2] | [[:Media:Database_SQL_-_WHERE_clause.pdf |SQL WHERE clause (PDF)]]
 +
* [[Database:MariaDB ALTER TABLE syntax|ALTER TABLE MariaDB version]] - Extra material for students/readers who wish to learn also a little about MariaDB/MySQL
 +
* Decomposing a table (Extra lecture for the JOIN chapter) [https://vimeo.com/couchmode/channels/1373390 Databases - decomposing (Full playlist)] | [https://vimeo.com/267584351 Databases - decomposing 1/3] | [https://vimeo.com/267584386 Databases - decomposing 2/3] | [https://vimeo.com/267584369 Databases - decomposing 3/3] | [[:Media:Database - Decomposing a table.pdf|Database - Decomposing a table (PDF)]]
 +
* [[:Media:ExtraJoins.pdf|Extra lecture on JOINs (pdf)]]
 +
** Extra joins, Video (1/2) [https://vimeo.com/249813551 SQL Extra - Extra Joins 1]
 +
** Extra joins, Video (2/2) [https://vimeo.com/249813539 SQL Extra - Extra Joins 2]
 +
 
 +
There are "check your progress" questions added to most exercise chapters. The suggested solutions with explanations can be found here:
 +
* [[SQL_SELECT_Check_Answers]] (check your progress answers)
 +
* [[SQL_UPDATE_Check_Answers]] (check your progress answers)
 +
* [[SQL_DELETE_Check_Answers]] (check your progress answers)
 +
* [[SQL_INSERT_Check_Answers]] (check your progress answers)
 +
* [[SQL_constraints_Check_Answers]] (check your progress answers)
 +
 
 +
Example exam with questions and answers can be found here:
 +
* [[:Media:Tentagenomgång.pdf|A really, really old exam on databases (and JDBC)]]
 +
 
 +
If you want to read all slides (as per 2019-01-03) from the course material in one file (four miniature slide pages on each slide), you can get it [[:Media:Introduction_to_Databases_all_slides_4_per_page.pdf|here]].
 +
 
 +
===Reading tips===
 +
* The [http://sqlite.org/ SQLite web site] is an important resource as a companion for this wiki book.
 +
* If you want to move on to MariaDB, you should check out the [https://mariadb.com/ official web site for MariaDB].
 +
* Some notes on SQLite3 and the ALTER TABLE syntax and other things that are missing or different from other DBMSs:
 +
** [https://www.sqlite.org/omitted.html www.sqlite.org/omitted.html]
 +
** See also: [https://www.sqlite.org/quirks.html www.sqlite.org/quirks.html]
 +
 
 +
===Reading tips (Swedish)===
 +
* For Swedish readers, we recommend the book [https://www.studentlitteratur.se/#9789144044491/Databasteknik Databasteknik (Studentlitteratur)] which is a quite complete book on database technology
 +
* Also for Swedish readers, we recommend the web course related to the above book at [http://www.databasteknik.se/webbkursen/ http://www.databasteknik.se/webbkursen/] which covers a subset of the chapters of the related book above
 +
We might add some more extra reading material here in the future.
 +
 
 
=What this book doesn't pretend to be=
 
=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.
 
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.
Line 30: Line 96:
  
 
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.
 
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=
 +
* [[Databaser_15p_Webbutvecklare]] (part of the program Webbutvecklare) 2018
 +
* [[Utveckling_mot_databas_-_ITHS_-_2018]]
 +
* [[Systemintegration-Yrgo-2018]]
 +
* [[TIG058]] (Swedish university course, Göteborgs universitet) 2018, 2019
 +
* [[Databaskunskap för mjukvarutestare]] (Swedish course for [http://iths.se/courses/mjukvarutestare/ ITHS Mjukvarutestare]) 2016 and 2017
 +
* Yrgo - Databaser 15p (part of the program Webbutvecklare) (Autumn 2017)
 +
==Chapter completeness (for teachers)==
 +
{|class="wikitable"
 +
!style="text-align:left;"|Chapter
 +
!style="text-align:left;"|Meta section completed
 +
!style="text-align:left;"|Text and examples
 +
!style="text-align:left;"|Videos (number of)
 +
!style="text-align:left;"|Slides
 +
!style="text-align:left;"|Exercises or questions
 +
|-style="color: green;"
 +
|[[Database:Introduction to Databases]]
 +
|style="color: green;"|Yes
 +
|style="color: green;"|Medium
 +
|style="color: green;"|One (Swedish)<br>One (ENG)
 +
|style="color: green;"|One slides
 +
|style="color: green;"|5 (2 A4 pages)
 +
|-
 +
|[[Database:Retrieving rows of data - SQL SELECT]]
 +
|style="color: green;"|Yes
 +
|style="color: orange;"|Medium
 +
|style="color: green;"|One (Swedish)<br>4 (ENG)<br>Three extra (ENG)
 +
|style="color: green;"|One slides + one extra
 +
|style="color: green;"|12 (5 A4 pages)
 +
|-
 +
|[[Database:Getting started with SQLite]]
 +
|style="color: green;"|Yes
 +
|style="color: green;"|Yes
 +
|style="color: green;"|One (Swe)<br>One live (Swe)<br>4 (Eng)
 +
|style="color: green;"|One slides
 +
|style="color: green;"|15 (10 A4 pages)
 +
|-
 +
|[[Database:Changing rows of data - SQL UPDATE]]
 +
|style="color: green;"|Yes
 +
|style="color: green;"|Yes
 +
|style="color: green;"|One (Swe)<br>One live (Swe)<br>2 (Eng)
 +
|style="color: green;"|One slides
 +
|style="color: green;"|9 (6 A4 pages)
 +
|-
 +
|[[Database:Deleting rows of data -SQL DELETE]]
 +
|style="color: green;"|Yes
 +
|style="color: orange;"|Short
 +
|style="color: green;"|One (Swe)<br>One live (Swe)<br>1 (Eng)
 +
|style="color: green;"|One slides
 +
|style="color: green;"|10 (5 A4 pages)
 +
|-
 +
|[[Database:Adding rows with new data - SQL INSERT]]
 +
|style="color: green;"|Yes
 +
|style="color: orange;"|Short
 +
|style="color: green;"|One (Swe)<br>One live (Swe)<br>1 (Eng)
 +
|style="color: green;"|One slides
 +
|style="color: green;"|8 (9 A4 pages)
 +
|-
 +
|[[Database:Combining rows of data from related tables - SQL JOIN]]
 +
|style="color: green;"|Yes
 +
|style="color: green;"|YES
 +
|style="color: orange;"|One (Swe)<br>One live (Swe)<br>A few extra on decomposing
 +
|style="color: green;"|One slides + One extra
 +
|style="color: green;"|12 (9 A4 pages)
 +
|-
 +
|[[Database:NULL-representing lack of value|NULL - representing the lack of value]]
 +
|style="color: green;"|Yes
 +
|style="color: green;"|YES
 +
|style="color: green;"|3 videos(English)
 +
|style="color: green;"|One slides
 +
|style="color: green;"|5<br>on the same page<br>as the lecture
 +
|-
 +
|[[Database:Adding constraints to prevent garbage data]]
 +
|style="color: green;"|Yes
 +
|style="color: green;"|YES
 +
|style="color: orange;"|Two videos(Swe)<br>One extra on enum (Eng)
 +
|style="color: green;"|One plus one slides
 +
|style="color: green;"|8 (10 A4 pages)
 +
|-
 +
|[[Database:Scraping data from the web]]
 +
|style="color: green;"|Yes
 +
|style="color: green;"|Yes
 +
|style="color: orange;"|Four videos(Swe)
 +
|style="color: green;"|One slides
 +
|style="color: green;"|6 (13 A4 pages)
 +
|-
 +
|}
 +
Completeness degree:
 +
 +
''This course material '''88%''' (44/50) completed (for English audiences). That measure is counting: Meta-section, intro text/examples, English videos, slides, exercises''
 +
 +
''The course material '''94%''' (47/50) completed (for Swedish audiences). That measure is counting: Meta-section, intro text/examples, Swedish videos, slides, exercises''
 +
* Meta-section completeness: 100% (10/10)
 +
* Text/examples completeness: 70% (7/10)
 +
* English videos completeness: 70% (7/10)
 +
* English or Swedish video completness: 100% (10/10)
 +
* Slides completeness: 100% (10/10)
 +
* Exercises completeness: 100% (10/10)

Latest revision as of 13:12, 11 February 2019

This book is continuously updated

This book is edited every year according to feedback we get from e.g. course evaluations and student input. Feel free to dive in and start reading anyway. Of course all feedback is welcome by the Authors.

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

On the first topic page of this course material, you will find instructions for setting up your environment.

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:

If you want to read all slides (as per 2019-01-03) from the course material in one file (four miniature slide pages on each slide), you can get it 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 (ENG)
One slides 5 (2 A4 pages)
Database:Retrieving rows of data - SQL SELECT Yes Medium One (Swedish)
4 (ENG)
Three extra (ENG)
One slides + one extra 12 (5 A4 pages)
Database:Getting started with SQLite Yes Yes One (Swe)
One live (Swe)
4 (Eng)
One slides 15 (10 A4 pages)
Database:Changing rows of data - SQL UPDATE Yes Yes One (Swe)
One live (Swe)
2 (Eng)
One slides 9 (6 A4 pages)
Database:Deleting rows of data -SQL DELETE Yes Short One (Swe)
One live (Swe)
1 (Eng)
One slides 10 (5 A4 pages)
Database:Adding rows with new data - SQL INSERT Yes Short One (Swe)
One live (Swe)
1 (Eng)
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 5
on the same page
as the lecture
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 Yes Four videos(Swe) One slides 6 (13 A4 pages)

Completeness degree:

This course material 88% (44/50) completed (for English audiences). That measure is counting: Meta-section, intro text/examples, English videos, slides, exercises

The course material 94% (47/50) completed (for Swedish audiences). That measure is counting: Meta-section, intro text/examples, Swedish videos, slides, exercises

  • Meta-section completeness: 100% (10/10)
  • Text/examples completeness: 70% (7/10)
  • English videos completeness: 70% (7/10)
  • English or Swedish video completness: 100% (10/10)
  • Slides completeness: 100% (10/10)
  • Exercises completeness: 100% (10/10)