Database:Introduction to Databases

From Juneday education
Jump to: navigation, search

Meta

Expand using link to the right to read the meta section with teacher's instructions.

Description

This first chapter introduces the student to the concept of databases and database management systems, as well as how to communicate with databases using the SQL query language.

Prepare

Expect this lecture to take at least 16 minutes, just to go through the slides. We recommend you to read the online theory page, review the exercises/questions (including the suggested solutions) and to watch the video. If you are unsure about the timing, write some additional exercises/questions, just in case you have time over after the lecture is finished.

The main terms and concepts covered in this lecture are:

  • Database - A set of tables for storing data in an organised way
  • Database Management System (DBMS) - A set of computer programs that controls the creation, maintenance and usage of the database
  • SQL/Structured Query Language - A Language for Working with a DBMS
  • Table - A set of related data in a database
  • Note: Sometimes we say “database” for short when we actually talk about a database management system
  • Row - Each table organizes its data in rows
  • Column - Each row consists of data in columns (a bit like a spreadsheet)
  • Questions on “type” of data might arise

Limitations

Since this probably is the first session when using these course materials (you might have started the course with your own introductory material, then you can handle this), it might be hard to require that the students have watched the video lecture in advance. Make sure to handle this. For the following chapters/topics, you may require that your students watch the video lectures in advance, so that they are better prepared for your lecture.

One possible solution is to communicate to the students that they should watch the video lecture in advance, prior to the day this first lecture is held. This could be done in relation to the course information, or, if you have an introduction or “welcome” lecture on a previous day.

In our experience, having the student watch the video lecture(s) prior to the physical lecture, makes for much more efficient use of your time with the students. When they have seen the video lecture, your own lecture can be more brief, and you have more time for opening up for questions and discussions. Of course, the same could be said about you, the teacher, also watching the video lecture(s) in advance - you will be much more prepared when using the corresponding slides in the classroom lecture.

Introduction

This first chapter introduces you to the concept of databases and database management systems, as well as how to communicate with databases using the SQL query language.

A database is a structured collection of data, organized in tables. A table, in turn, is a collection of related data organized in rows and columns.

Let's use an example (very simplified to make a point) to clear out all these terms. A book store might have a lot of data about its books, customers and suppliers. Together, these areas of data are collected and stored in a database. The database could be organized in three tables:

  • books
  • customers
  • suppliers

The first table, books, could store information about books in the store's catalog, with data about one book in each row of the table. Every row could store the following data about a book in its columns:

  • product_id
  • title
  • price
  • nr_in_stock
  • author

A listing of all the books would list the column names (as a kind of header) and all the rows of books with the book data under each column header:

+----------+------------+-----+-----------+------+
|id        |title       |price|nr_in_stock|author|
+----------+------------+-----+-----------+------+
|1         |Databases   |200.0|10         |Henrik|
+----------+------------+-----+-----------+------+
|2         |PHP         |50.0 |2          |Bengt |
+----------+------------+-----+-----------+------+
|3         |HTML        |99.0 |34         |Beata |
+----------+------------+-----+-----------+------+

The data about the book stores customers could be arranged in a table in the database with a similar structure, rows with data and columns with descriptive names such as

  • id
  • email
  • name
  • addresss

etc.

And the same could go for the suppliers. Together, the book store's data about books, customers and suppliers are called a database. And the database is organized as a set of three tables. The tables are the containers for the actual data, organized in columns and rows.

The software which controls and organizes the data in the database is called a database management system (or DBMS). Sometimes we are sloppy and call a DBMS a "database". In this course, we will use SQLite as the database management system. We will interact directly via the database management system SQLite, as we learn how to retrieve, manipulate, delete and add data to various databases managed by SQLite.

The language we will use to talk to our databases and tables is called SQL (Structured Query Language). This is a very technical way to interact with a database. You will probably learn how to create application which use SQL in other courses, but before you can do that, you need to learn the basics of databases, database management systems and the SQL language.

When the book store staff needs to access the data, they need some kind of application to interface with the data. This application could be written in some programming language, which in turn uses SQL to interact with the database management system, to access the data in the book store database. The customers of the book store, might, in turn, interface with the book store's database via a web page (written in e.g. PHP, .NET or Java). The same data can be accessed by the book store staff and the customers, using different front end applications. It is, of course, practical to have centralized the data and put in in one place. This is a typical use for databases (even if we here have simplified the picture greatly).

Set up 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.

Links

Videos

Swedish lecture videos

English lecture videos

Files

  • No files for this chapter

Further reading

Where to go next

« PreviousBook TOCNext »