Databases (TDA357, DIT621)

Welcome!

Visit the course home page for up-to-date information.

• chalmers.instructure.com/courses/8724
• Lectures, exercises, and other course material
• Lab assignment
• Announcements, invitation to Slack
• Discussions

Databases (TDA357, DIT621)

Teachers

• Thomas Hallgren
• Alejandro Gomez
• Agustín Mista
• Yasmeen Emampoor
• Benjamin Sannholm

Databases (TDA357, DIT621)

Student representatives

• Chalmers	GU
  Adrian Eliasson (TKDAT) Kasper Hall (TKDAT) Carl Jendle (MPDSC) Albin Johansson (TKITE)	… … … …

• They will be listed on the course home page.
• Also need volunteer student representatives for GU students.

Today

• General information
• Course overview
• Gentle introduction
• (This introduction is based on slides by Jonas Duregård)

Course organization

• Lectures:
  • 2 per week (Mondays and Thursdays)
• Exercise sessions:
  • 1 per week (Wednesdays)
• Supervised lab sessions:
  • 3 per week (Mondays, Thursdays, Fridays)

Lab Assignment

• One assignment with 4 parts.
• Submit your solution to each part in Fire.
  • Demonstrate part 4 during a lab session before you submit it.
• Deadlines visible in Fire and listed on the course home page.
• Work in pairs.
  • Today is a good opportunity to find someone to work with.

Fire

• Submit your work in Fire.
• Fire is separate from Canvas, you need to register separately.
• Use the same name and personal number as in Ladok when you register.
• Use the correct course code.
  • (TDA357 for Chalmers students, DIT621 for GU students)
• Create groups with two members.
  • The first member creates the group, the second member joins the group by using the group's join-code.

Communication and Getting Help

• Use the lab sessions to get help with the assignment.
• Use exercise sessions increase your confidence in particular topics before working on the assignment.
• Use Slack for quick questions, chats and direct messages.
• Use Discussions in Canvas for longer questions.
• Teachers and students can also reached through their Inbox in Canvas.

Examination

The course has two parts

• A lab assignment (graded pass/fail).
• A written exam (graded fail/3/4/5 or fail/G/VG).
• Both must be passed to pass the course.
• The grade of the exam will be your course grade.

Literature

• Databases in 144 Pages by Jyrki Nummenmaa and Aarne Ranta
  • Lecture notes created specifically for this course,
  • to be published as a book.
• Database System: The Complete Book (1200 pages)
  • by Hector Garcia-Molina, Jeffrey D. Ullman, and Jennifer Widom.
  • Paper copies and eBook available in the Chalmers Library.
  • Reading it from cover to cover may not be the most productive use of your time...

Why a whole course on databases? (1)

Short answer

• Databases are everywhere!
• It would be a challenge to go a single day of your life without causing a lot of databases updates.

Why a whole course on databases? (2)

Long answer

Databases are used in

• WWW (Most web sites use a database under the hood. LAMP, WordPress).
• Finance (has driven the development of databases since the 60's).
• Industry: Production control, Test data, Inventories, Sales, …
• Research: Sensor data, Biological data.
• Government: Demographical data, SPAR, …
• Public Transport: Open Data from Västtrafik.

What is a Database?

• Wide sense:
  • Any collection of data that can be accessed digitally and is:
    • Structured – data is stored in efficient structures
    • Persistent – data is not lost without deliberate action
    • Mutable – data can be added/deleted/modified
• Slightly more useful:
  • A database is a collection of data managed by a specialized software called a Database Management System (DBMS)

Database Management Systems

Some popular DBMSs

• IBM DB2
• Microsoft SQL Server
• Oracle
• PostgreSQL
• MySQL
• MariaDB
• SQLite

Why not use a file system?

• File systems are structured, persistent and mutable
• … but can be inefficient and 'bulky' to work with directly,
• … forces you to think about a lot of low-level issues that are already solved in a DBMS.

Modern DBMS

• Handle persistent data.
• Give efficient access to huge amounts of data.
• Guarantees integrity constraints on data.
• Handles transactions and concurrent access to data.

Relational Databases

• This is the main topic of this course.
• A database is basically a bunch of tables with columns and rows.
• Can also be viewed as mathematical relations.
• Can require significant design work.
• SQL is a standardized language for manipulating relational databases.
  • Common language supported by lots of different DBMS.
  • Create, manipulate and query databases.
  • Arguably one of the most used computer languages in existence.
  • Fancy people pronounce it "Sequel".

The birth of the relational model

• Unlike many discoveries, it is easy to pinpoint when databases started.
• This is the first line from Edgar F. Codd's 1970 paper titled A Relational Model of Data for Large Shared Data Banks:
  • "Future users of large data banks must be protected from having to know how the data is organized in the machine [...]"
• In a single paper, Codd outlines much of what we teach in this course today

Other database models

• The relational data model and SQL is so prevalent that other approaches are commonly referred to as NoSQL‐databases
• Semi‐structured hierarchical models (XML, JSON, ...)
• Key‐value stores (Oracle NoSQL, Riak, Cassandra, ...)
  • Easily distributed across multiple computers/data centers
  • Very simplistic data model (maps and lists)
• Usually NoSQL‐databases are easier to design, sometimes more efficient, but also more limited when it comes to integrity constraints.

Course Objectives

You will learn how to

• Design a database
• Construct a database from a schema
• Use a database through queries and updates
• Use a database from an external application

Course Objectives

Design

When the course is through, you should know how to

• Given a domain, design a database that correctly models the domain and its constraints
• ”We want a database that we can use for scheduling courses and lectures. This is how it’s supposed to work: …”

Course Objectives → Design

• code → cName code, time → rName rName, time → code
• Construct Entity-Relationshop diagrams (ER)
• Determine Functional Dependencies (FD)
• Compute normal forms

  

Course Objectives

Construction

When the course is over, you should know how to

• Given a database schema with related constraints, implement the database in a relational DBMS
• Courses(code,cname) Rooms(rname) Lectures(room,time,course) room -> Rooms.rname course -> Courses.code

  ⇒

Course Objectives

Usage

When the course is over, you should know how to

• query a database for relevant data using SQL

• SELECT time, room
    FROM Lectures
    WHERE course = 'TDA357';
  

• modify the contents of a database using SQL

• INSERT INTO Rooms VALUES ('HB2');
  

Course Objectives

Applications

When the course is over, you should know how to

• connect to and use a database from external applications
• Class.forName("org.postgresql.Driver"); Properties props = new Properties(); props.setProperty("user", user); props.setProperty("password", pwd); conn = DriverManager.getConnection(db, props);

• import Databases.HDBC.Postgres
  import Databases.HDBC
  
  main =
    do conn <- connectPostreSQL "dbname=countries"
       run conn "UPDATE Currencies SET value=10.61 WHERE code='EUR'" []
       commit

Lab assignment overview

Goal: Construct a "student portal" application in Java+SQL

• Part 1: Starting with a domain description and a draft schema, implement the schema in a database and write queries (views) for common operations
• Part 2: Use systematic design methods to add a few features, and find and eliminate flaws in the original schema
• Part 3: Create triggers to further improve the database
• Part 4: Connect to your database from a simple Java Application, use JSON to send your data to a web client

Tools

Database Management System

PostgreSQL (a.k.a. Postgres) (www.postgresql.org)

• The DBMS we will be using in the course.
• Free, open-source software that works on most operating systems.
• Available on Chalmers computers (not the latest version).
• Weird name? INGRES → Post Ingres → Postgres.

Tools

Other tools

• Dia (dia-installer.de)
  • The recommended editor for ER‐diagrams.
  • Also free, open-source.
  • Available on Chalmers computers (I hope).
• You may want to have a text editor that supports SQL syntax highlighting.
• See the Tools page.

The End of the Introduction

• Questions?
• What's next? See the course home page.