English version of the materials are work in progress!

Expect bugs, typos, and other issues. The English version is expected to be completed during spring 2026.

Exercise: Creating a Database with SQL

In Exercise 3.1 we defined a relational database schema for a student database. In this exercise, we will build the actual student database based on the relational database schema from the previous exercise.

In this course, SQLite software is used for creating and processing relational databases. SQLite is a database management system suitable for creating and managing small relational databases. In SQLite, databases are defined and managed with SQL query language.

This exercise is a continuation of Exercise 3.1. Do Exercise 3.1 first before starting this exercise.

Before starting the exercise, familiarize yourself with the following chapters:

Chapter 4.1, which discusses the basic idea and essential concepts of SQL language.
Chapter 4.3, which presents CREATE TABLE syntax intended for defining database structure.
Chapter 4.2, which presents syntax of SQL's Data Control Language (DCL). In this exercise, it is enough to initially glance through the chapter. You will get to practice SQL language much more extensively in Exercise 4.2.

Huomautus

Jos huomaat virheitä tai sinulla on kysyttävää tehtävistä, ota yhteyttä kurssin opettajiin

lähettämällä sähköpostia osoitteeseen itka2004-teachers@jyu.onmicrosoft.com
laittamalla viestiä kurssin Teams-ryhmään tai osallistumaalla ohjauksiin: Teams ja ohjaukset

Assignment

Let's continue with the student database worked on in Exercise 3.1. We will form an SQLite database based on the structure of the student database prepared in the previous exercise.

Follow the steps in the sections below and complete the answers in the submission boxes below based on them. You will find a check button at the end of each section with which you can test the correctness of your answer.

Defining Table Structure

# openTables

Let's start by defining a relation in the relational schema as an SQLite table.

Let's create table Faculty first, as the corresponding relation is quite simple:

Faculty (id, name)

In SQL language, a table is defined with the following syntax (see Chapter 4.3):

Based on the syntax above and the relational schema, the Faculty table is defined in SQL language as follows:

-- Way 1: Recommended way to write table definition
CREATE TABLE Faculty (
  id,
  name
);

Huomautus

Additional notes (click to open)

Every SQL command must be ended with a semicolon (;). In other words, at the end of every CREATE TABLE command there is a semicolon.
SQL keywords can be written in either uppercase or lowercase. So create table and CREATE TABLE mean the same thing. In SQLite, case sensitivity of table and column names also does not matter by default.
SQLite differs from other database management systems in that data type is not mandatory when defining tables. However, this does not necessarily apply to other products!
Column name should not have spaces (even though SQL language allows it). Replace spaces for example with an underscore (_).
In SQLite, indentation or line break usually does not matter. So the Faculty table above can be defined alternatively in the following ways
```
-- Way 2: Everything on the same line
CREATE TABLE Faculty (id, name);

-- Way 3: SQL clauses are in lowercase
create table Faculty (id, name);

-- Way 4: Line breaks and indentations randomly
create TABLE Faculty (
   id,
name);
```
All ways above to define Faculty table are allowed. Recommendation is however to use way 1, where every column is on its own line. This makes interpreting table structure easier especially when integrity constraints are defined for the table and columns.

Next, define two tables in the submission box below according to the example above: Faculty and Department. At this stage, you don't need to mark column data types or integrity constraints yet, as they are covered in the next step.

You can float the diagram editor with the Float button. Then the text moves to the right edge, and space is freed up for the editor on the right.

Harjoitus: Tietokannan luominen SQL:llä

Harjoituksessa 3.1 määrittelimme relaatiotietokannan kaavan opiskelijatietokannalle. Tässä harjoituksessa rakennamme varsinaisen opiskelijatietokannan aiemman harjoituksen relaatiotietokannan kaavan pohjalta.

Tällä kurssilla relaatiotietokantojen luomiseen ja käsittelyyn käytetään SQLite-ohjelmaa. SQLite on tietokannanhallintajärjestelmä, joka soveltuu pienten relaatiotietokantojen luomiseen ja hallintaan. SQLite:ssa tietokannat määritellään ja hallitaan SQL-kyselykielellä.

Tämä harjoitus on jatkoa Harjoitukselle 3.1. Tee ensin Harjoitus 3.1 ennen tämän harjoituksen aloittamista.

Ennen harjoituksen aloittamista tutustu seuraaviin lukuihin:

Luku 4.1, jossa käsitellään SQL-kielen perusajatusta ja olennaisia käsitteitä.
Luku 4.3, jossa esitellään tietokannan rakenteen määrittämiseen tarkoitettua CREATE TABLE -syntaksia.
Luku 4.2, jossa esitellään SQL-kielen datahallintakielen (DCL) syntaksia. Tässä harjoituksessa riittää, että aluksi silmäilet lukua. SQL-kieltä pääsee harjoittelemaan paljon laajemmin Harjoituksessa 4.2.

Huomautus

Jos huomaat virheitä tai sinulla on kysyttävää tehtävistä, ota yhteyttä kurssin opettajiin

lähettämällä sähköpostia osoitteeseen itka2004-teachers@jyu.onmicrosoft.com
laittamalla viestiä kurssin Teams-ryhmään tai osallistumaalla ohjauksiin: Teams ja ohjaukset

Tehtävänanto

Jatketaan Harjoituksessa 3.1 työstettyä opiskelijatietokantaa. Muodostetaan edellisessä harjoituksessa laaditun opiskelijatietokannan rakenteen perusteella SQLite-tietokanta.

Seuraa alla olevien osioiden vaiheita ja täydennä alla oleviin palautuslaatikoihin vastauksia niiden perusteella. Löydät jokaisen osion lopusta tarkistuspainikkeen, jolla voit testata vastauksesi oikeellisuuden.

Taulun rakenteen määritys

# openTaulut

Aloitetaan määrittelemällä jokin relaatiokaavassa oleva relaatio SQLite-tauluna.

Luodaan ensin taulu Tiedekunta, sillä sitä vastaava relaatio on melko yksinkertainen:

Tiedekunta (id, nimi)

SQL-kielessä taulu määritellään seuraavalla syntaksilla (ks. Luku 4.3):

CREATE TABLE table (
	column data_type[, 
	column data_type]*
);

Yllä olevan syntaksin ja relaatiokaavan perusteella Tiedekunta-taulu määritellään SQL-kielellä seuraavasti:

-- Tapa 1: Suositeltu tapa kirjoittaa taulun määrittely
CREATE TABLE Tiedekunta (
  id,
  nimi
);

Huomautus

Lisähuomioita (avaa klikkaamalla)

Jokainen SQL-kielen komento tulee päättää puolipisteeseen (;). Toisin sanoen, jokaisen CREATE TABLE -komennon lopussa on puolipiste.
SQL-kielen avainsanat voi kirjoittaa joko isolla tai pienellä. Siis create table ja CREATE TABLE tarkoittavat samaa asiaa. SQLite:ssä myöskään taulujen ja sarakkeiden nimien kirjainkoolla ei ole oletuksena merkitystä.
SQLite poikkeaa muista tietokannanhallintajärjestelmistä siten, että taulujen määrittelyn yhteydessä tietotyyppi ei ole pakollinen. Tämä ei kuitenkaan välttämättä päde muissa tuotteissa!
Sarakkeen nimessä ei tule olla välilyöntejä (vaikka SQL-kieli sen salliikin). Korvaa välilyönnit esimerkiksi alaviivalla (_).
SQLite:ssä sisennyksellä tai rivinvaihdolla ei yleensä ole merkitystä. Niinpä yllä oleva Tiedekunta-taulu voidaan määritellä vaihtoehtoisesti seuraavilla tavoilla
```
-- Tapa 2: Kaikki samalla rivillä
CREATE TABLE Tiedekunta (id, nimi);

-- Tapa 3: SQL-määreet ovat pienellä
create table Tiedekunta (id, nimi);

-- Tapa 4: Rivinvaihdot ja sisennykset satunnaisesti
create TABLE Tiedekunta (
   id,
nimi);
```
Kaikki yllä olevat tavat määritellä Tiedekunta-taulu ovat sallittuja. Suositus on kuitenkin käyttää tapaa 1, jossa jokainen sarake on omalla rivillään. Tämä helpottaa taulun rakenteen tulkitsemista erityisesti silloin, kun taululle ja sarakkeille määritellään eheysrajoitteita.

Määritä seuraavaksi alla olevaan palautuslaatikkoon kaksi taulua yllä olevan esimerkin mukaisesti: Tiedekunta ja Laitos. Tässä vaiheessa ei tarvitse vielä merkitä sarakkeiden tietotyyppejä eikä eheysrajoitteita, sillä niitä käsitellään seuraavassa vaiheessa.

Voit kelluttaa kaavioeditorin Kelluta-painikkeella. Silloin teksti siirtyy oikeaan laitaan, ja editorille vapautuu tilaa oikealle.

# 780197.ex41-step1

Defining Table Primary Key and Foreign Key

``{#openIntegrity Constraints plugin="jsrunner" id="NqEyoKZ1br1Q" nocache="true"} groups: - Anonymous showInView: true button: Avaa osatehtävä "Integrity Constraints" fields: - 947075.ex31-step3.ALL=ex31-step3 - 947075.ex41-step1.ALL=ex41-step1 - 947075.ex41-step2.ALL=ex41-step2 - 947075.ex41-step3.ALL=ex41-step3 - 947075.ex41-step4.ALL=ex41-step4 - 947075.ex41-step5.ALL=ex41-step5 program: |!! if ("<span class="error" ng-non-bindable>Thebelongsfilter requiresnocache=true` attribute." !== "True") { let v = tools.getValue("ex31-step3", undefined); if (!v) { tools.println("Complete Exercise 3.1 first before starting this exercise!"); return; }

v = tools.getValue("ex41-step1", undefined); if (!v) { tools.println("Fill and save the "Tables" subtask answer before opening this subtask!"); return; } } gtools.outdata.saveAreaVisibility = false; gtools.outdata.areaVisibility = { "osa2": true, }; !! ```

Then let's define primary key and foreign keys for tables Faculty and Department.

Primary key (see Chapter 4.3 and Chapter 3.1) is used to identify every row in the table. On every row of the table, the primary key value must be different. In SQL language, a primary key can be defined in two ways: in connection with column definition or after columns. In this exercise, we focus especially on the latter notation way:

In this exercise, table primary key is defined after column definitions with PRIMARY KEY clause. Remember that a primary key can consist of one or more columns.

Let's examine relation Faculty. From the relation schema, we see that the relation's primary key has only one attribute: id. So let's add primary key definition to Faculty table definition:

CREATE TABLE Faculty (
  id,
  name,
  PRIMARY KEY (id)
);

Foreign key (see Chapter 4.3 and Chapter 3.1) defines a column or columns whose values correspond to primary key or unique column values of some other table. The purpose of a foreign key is to enable describing relationships between rows of different tables.

Defining a foreign key is not fundamentally mandatory in SQL language, but with it the database management system facilitates reference management and integrity monitoring. Different database management systems might offer slightly different features when a foreign key is marked in a table. For example, SQLite:

knows when adding table rows to verify that values of columns belonging to foreign key really refer to some existing row of another table (for example prevents adding products to an order that do not exist);
knows to automatically update foreign key values if changes happen in the referenced table (for example updates product ID in all tables if it changes for a product);
knows to ensure that rows of referenced table cannot be accidentally deleted if there is a reference to them in some other table (for example prevents accidental deletion of product information if someone has ordered it).

By default, SQLite does not do any verifications of foreign key correctness, unless foreign key support is separately turned on. In this material, checking foreign key correctness is always on in all tasks. If you make your own SQLite database, see official instruction on foreign keys.

Foreign key definition syntax is slightly more extensive than primary key syntax:

Huomautus

Additional notes (click to open)

Note the connection to foreign key notation in relation schema.

For example, SQL clause corresponding to notation Table1.column -> Table2.column is
```
FOREIGN KEY (column) REFERENCES Table2 (column)
```
The table to whose columns the foreign key refers is often called parent table. Correspondingly, the table to which the foreign key is defined is often called child table. In the example above, Table1 is child table and Table2 is parent table.
In SQL language, multiple columns can belong to the same foreign key. Also multiple separate foreign keys can be defined.

For example, if relational schema has notations:

Table1.column1 -> Table2.ref_column1
Table1.column2 -> Table2.ref_column2

In SQL language they could be defined as two separate foreign keys as follows:
```
-- Two separate foreign keys
FOREIGN KEY (column1) REFERENCES Table2 (ref_column1),
FOREIGN KEY (column2) REFERENCES Table2 (ref_column2)
```
In this case column1 refers to some row of table Table2 through ref_column1, and column2 refers to some (possibly different) row of table Table2 through ref_column2.

If on the other hand one wants to define one composite foreign key, where combination of column1 and column2 refers to some row of table Table2, the following way is used:
```
-- One composite foreign key
FOREIGN KEY (column1, column2) REFERENCES Table2 (ref_column1, ref_column2)
```
This means that combination of values of columns column1 and column2 in Table1 refers to corresponding combination (ref_column1, ref_column2) which identifies a row in table Table2. i.e. values of column1 and column2 together refer always to one and same row in table Table2.

Choose the way to use depending on whether it is a composite foreign key or multiple separate foreign keys.
With additional clauses (ON UPDATE and ON DELETE) one can tell how SQLite should act if rows to which child table rows refer are modified or deleted in parent table (see also additional information on maintaining referential integrity in Chapter 3.1). Alternatives are:
- RESTRICT: Prevents operation if it violates referential integrity.
- SET NULL: Sets referring columns to NULL values in child table.
- CASCADE: Cascades changes: row deletion in parent table deletes also referring rows in child table; update of referenced key in parent table updates also referring key values in child table.
- SET DEFAULT: Sets referring columns in child table to their defined default values.
If additional clauses are not given, SQLite uses NO ACTION action by default. This action is similar to RESTRICT, i.e., it prevents operation if it violates referential integrity.

Let's examine relation Department. From relation schema we notice that relation has a foreign key:

Department.faculty_id -> Faculty.id

In this case reference's parent table is Faculty and child table is Department. Let's think how one should proceed if faculties were modified or deleted:

If a faculty was deleted, departments might remain without a faculty. In ER diagram it was however required that a department always belongs to some faculty. Therefore it is reasonable that faculties should not be allowed to be deleted if departments belong to it. In other words, before deleting a faculty it is on database user's responsibility to move departments to another faculty.
Faculty id is a unique and permanent value, so one can assume it is always the same. Therefore changing of faculty id does not need to be separately handled with ON UPDATE clause.

Based on this, let's change table Department to following form:

CREATE TABLE Department (
  id,
  name,
  faculty_id,
  FOREIGN KEY (faculty_id) REFERENCES Faculty (id)
    ON DELETE RESTRICT
);

Complete definitions of tables Department and Faculty created in previous part by adding primary keys and possible foreign keys to them.

You can float the diagram editor with the Float button. Then the text moves to the right edge, and space is freed up for the editor on the right.

Taulun perusavaimen ja viiteavaimen määritys

# openEheysrajoitteet

Määritetään sitten perusavain ja viiteavaimet tauluille Tiedekunta ja Laitos.

Perusavainta (ks. Luku 4.3 ja Luku 3.1) käytetään yksilöimään jokainen taulussa oleva rivi. Taulun jokaisella rivillä perusavaimen arvon tulee olla erilainen. SQL-kielessä perusavain voidaan määrittää kahdella tavalla: sarakkeen määrittelyn yhteydessä tai sarakkeiden jälkeen. Tässä harjoituksessa keskitymme erityisesti jälkimmäiseen merkintätapaan:

CREATE TABLE table (
    column data_type[, 
    column data_type]*[,
    PRIMARY KEY (column[, column]*)]?
);

Tässä harjoituksessa taulun perusavain määritetään sarakkeiden määrittelyn jälkeen PRIMARY KEY -määreellä. Muista, että perusavaimeen voi kuulua yksi tai useampi sarake.

Tarkastellaan relaatiota Tiedekunta. Relaation kaavasta nähdään, että relaation perusavaimessa on vain yksi attribuutti: id. Lisätään siis perusavaimen määrittely taulun Tiedekunta määritelmään:

CREATE TABLE Tiedekunta (
  id,
  nimi,
  PRIMARY KEY (id)
);

Viiteavain (ks. Luku 4.3 ja Luku 3.1) määrittää sarakkeen tai sarakkeita, joiden arvot vastaavat jonkin toisen taulun perusavaimen tai yksilöllisen sarakkeen arvoja. Viiteavaimen tarkoitus on mahdollistaa eri taulujen rivien välisten suhteiden kuvaaminen.

Viiteavaimen määritys ei ole lähtökohtaisesti pakollinen SQL-kielessä, mutta sen avulla tietokannanhallintajärjestelmä helpottaa viitteiden hallintaa ja eheyden valvontaa. Eri tietokannanhallintajärjestelmät saattavat tarjota hieman erilaisia ominaisuuksia, kun viiteavain merkitään tauluun. Esimerkiksi SQLite:

osaa taulun rivejä lisätessä varmistaa, että viiteavaimeen kuuluvien sarakkeiden arvot todellakin viittaavat toisen taulun johonkin olemassa olevaan riviin (esimerkiksi estää, ettei tilaukseen lisätä tuotteita, joita ei ole olemassa);
osaa automaattisesti päivittää viiteavainten arvot, jos viitatussa taulussa tapahtuu muutoksia (esimerkiksi päivittää tuotteen tunnuksen kaikissa tauluissa, jos se vaihtuu tuotteella);
osaa varmistaa, että viitatun taulun rivejä ei voi vahingossa poistaa, jos niihin on viittaus jossain toisessa taulussa (esimerkiksi estää, ettei tuotteen tietoja voi vahingossa poistaa, jos joku on tilannut sitä).

Oletuksena SQLite ei tee mitään varmistuksia viiteavaimen oikeellisuudesta, ellei viiteavaintukea ole kytketty erikseen päälle. Tässä materiaalissa viiteavainten oikeellisuuden tarkistus on aina päällä kaikissa tehtävissä. Jos teet oman SQLite-tietokannan, katso virallinen ohje viiteavaimista.

Viiteavaimen määrityksen syntaksi on hieman perusavaimen syntaksia laajempi:

CREATE TABLE table (
    column data_type[, 
    column data_type]*[,
    PRIMARY KEY (column[, column]*)]?[,
    FOREIGN KEY (column[, column]*) REFERENCES parent_table (column[, column]*)
      [ON UPDATE [RESTRICT|SET NULL|CASCADE|SET DEFAULT]]?
      [ON DELETE [RESTRICT|SET NULL|CASCADE|SET DEFAULT]]?
    ]*
);

Huomautus

Lisähuomioita (avaa klikkaamalla)

Huomaa yhteys relaatiokaavassa olevaan viiteavaimen merkintään.

Esimerkiksi merkintää Taulu1.sarake -> Taulu2.sarake vastaava SQL-määre on
```
FOREIGN KEY (sarake) REFERENCES Taulu2 (sarake)
```
Taulua, jonka sarakkeisiin viiteavain viittaa, kutsutaan usein isäntätauluksi. Vastaavasti taulua, johon viiteavain määritellään, kutsutaan usein renkitauluksi. Yllä olevassa esimerkissä Taulu1 on renkitaulu ja Taulu2 on isäntätaulu.
SQL-kielessä samaan viiteavaimeen voi kuulua useampi sarake. Myös useita erillisiä viiteavaimia voidaan määritellä.

Esimerkiksi, jos relaatiokaavassa on merkinnät:

Taulu1.sarake1 -> Taulu2.viitattu_sarake1
Taulu1.sarake2 -> Taulu2.viitattu_sarake2

SQL-kielessä ne voitaisiin määritellä kahtena erillisenä viiteavaimena seuraavasti:
```
-- Kaksi erillistä viiteavainta
FOREIGN KEY (sarake1) REFERENCES Taulu2 (viitattu_sarake1),
FOREIGN KEY (sarake2) REFERENCES Taulu2 (viitattu_sarake2)
```
Tällöin sarake1 viittaa johonkin taulun Taulu2 riviin viitattu_sarake1:n kautta, ja sarake2 viittaa johonkin (mahdollisesti eri) taulun Taulu2 riviin viitattu_sarake2:n kautta.

Jos taas halutaan määritellä yksi yhdistetty viiteavain, jossa sarakkeiden sarake1 ja sarake2 yhdistelmä viittaa taulun Taulu2 johonkin riviin, käytetään seuraavaa tapaa:
```
-- Yksi yhdistetty viiteavain
FOREIGN KEY (sarake1, sarake2) REFERENCES Taulu2 (viitattu_sarake1, viitattu_sarake2)
```
Tämä tarkoittaa, että sarakkeiden sarake1 ja sarake2 arvojen yhdistelmä Taulu1:ssä viittaa vastaavaan yhdistelmään (viitattu_sarake1, viitattu_sarake2), joka yksilöi rivin taulussa Taulu2. Eli sarake1:n ja sarake2:n arvot yhdessä viittaavat aina yhteen ja samaan riviin taulussa Taulu2.

Valitse käytettävä tapa sen mukaan, onko kyseessä yhdistetty viiteavain vai useampi erillinen viiteavain.
Lisämääreillä (ON UPDATE ja ON DELETE) voi kertoa, miten SQLite:n tulee toimia, jos isäntätaulussa muokataan tai sieltä poistetaan rivejä, joihin renkitaulun rivit viittaavat (katso myös lisätietoja viite-eheyden säilyttämisestä Luvussa 3.1). Vaihtoehdot ovat:
- RESTRICT: Estää operaation, jos se rikkoo viite-eheyttä.
- SET NULL: Asettaa viittaavat sarakkeet NULL-arvoisiksi renkitaulussa.
- CASCADE: Vyöryttää muutokset: isäntätaulun rivin poisto poistaa myös viittaavat rivit renkitaulusta; isäntätaulun viitatun avaimen päivitys päivittää myös renkitaulun viittaavat avainarvot.
- SET DEFAULT: Asettaa renkitaulun viittaavat sarakkeet niiden määriteltyihin oletusarvoihin.
Jos lisämääreitä ei anneta, SQLite käyttää oletuksena NO ACTION -toimintoa. Tämä toiminto on samankailtainen kuin RESTRICT, eli se estää operaation, jos se rikkoo viite-eheyttä.

Tarkastellaan relaatiota Laitos. Relaation kaavasta huomataan, että relaatiolla on viiteavain:

Laitos.tiedekunta_id -> Tiedekunta.id

Tässä tapauksessa viitteen isäntätaulu on Tiedekunta ja renkitaulu on Laitos. Mietitään, miten tulisi menetellä, jos tiedekuntia muokattaisiin tai poistettaisiin:

Jos tiedekunta poistettaisiin, laitoksia saattaisi jäädä ilman tiedekuntaa. ER-kaaviossa kuitenkin vaadittiin, että laitos kuuluu aina johonkin tiedekuntaan. Siksi on järkevää, että tiedekuntia ei saisi poistaa, jos siihen kuuluu laitoksia. Toisin sanoen, ennen tiedekunnan poistoa tietokannan käyttäjän vastuulla on siirtää laitokset toiseen tiedekuntaan.
Tiedekunnan id on yksilöllinen ja pysyvä arvo, joten sen voi olettaa olevan aina sama. Siksi tiedekunnan id:n muuttumista ei tarvitse erikseen käsitellä ON UPDATE -lausekkeella.

Tämän perusteella muutetaan taulu Laitos seuraavaan muotoon:

CREATE TABLE Laitos (
  id,
  nimi,
  tiedekunta_id,
  FOREIGN KEY (tiedekunta_id) REFERENCES Tiedekunta (id)
    ON DELETE RESTRICT
);

Täydennetään edellisessä osassa luotujen taulujen Laitos ja Tiedekunta määritykset lisäämällä niihin perusavaimet ja mahdolliset viiteavaimet.

Voit kelluttaa kaavioeditorin Kelluta-painikkeella. Silloin teksti siirtyy oikeaan laitaan, ja editorille vapautuu tilaa oikealle.

# 780197.ex41-step2

Defining Column Data Types and Integrity Constraints

``{#openData Types plugin="jsrunner" id="WoGHoD4Nbm9n" nocache="true"} groups: - Anonymous showInView: true button: Avaa osatehtävä "Data Types" fields: - 947075.ex31-step3.ALL=ex31-step3 - 947075.ex41-step1.ALL=ex41-step1 - 947075.ex41-step2.ALL=ex41-step2 - 947075.ex41-step3.ALL=ex41-step3 - 947075.ex41-step4.ALL=ex41-step4 - 947075.ex41-step5.ALL=ex41-step5 program: |!! if ("<span class="error" ng-non-bindable>Thebelongsfilter requiresnocache=true` attribute." !== "True") { let v = tools.getValue("ex31-step3", undefined); if (!v) { tools.println("Complete Exercise 3.1 first before starting this exercise!"); return; }

v = tools.getValue("ex41-step2", undefined); if (!v) { tools.println("Fill and save the "Integrity Constraints" subtask answer before opening this subtask!"); return; } } gtools.outdata.saveAreaVisibility = false; gtools.outdata.areaVisibility = { "osa3": true, }; !! ```

Next, let's define more precise data types for table columns.

By default, one can store any data in SQLite table columns. In other words, for example SQLite allows that in Student table there are rows where start_year is 'cat' or that course credits is 1.1.1996. To avoid errors when storing data to tables, it is worth defining a specific data type for every column.

Data types differ somewhat between different database management systems. Fundamentally all database management systems supporting SQL language support data types below (see Chapter 4.3).

There are of course other data types in different systems. For example SQLite supports in addition to mentioned above a general BLOB type, to which one can store any binary data (e.g. image files or other documents).

Also integrity constraints can be given to columns, with which one can influence what values may be in the column. Most common integrity constraints are presented below (see Chapter 4.3).

Both data type and integrity constraint are defined in connection with every column:

CREATE TABLE table (
    column data_type[ integrity_constraint[ integrity_constraint]*][, 
    column data_type[ integrity_constraint[ integrity_constraint]*]]*
);

Let's examine table Faculty again and set data types to its columns. Based on relation schema, ER diagram, and original description (see Exercise 2.1), we can deduce following things about columns:

For column id, type INTEGER i.e. integer would suit best. Integer is small-sized, easy to handle, and it is easy to give a unique number ID to different faculties. Because column belongs to primary key, it is already unique and does not allow NULL values.
For column name, type TEXT i.e. text-format column would suit best. We don't know beforehand how long faculty names can be, so there is no reason to limit its length. In addition to this, it can be assumed that all faculties must have a name (i.e. it cannot be empty, NOT NULL), and that every faculty name must be unique (UNIQUE), i.e. two different faculties cannot have the same name.
- Bonus info: In many sources VARCHAR is proposed as type for text. In practice nowadays in database management systems strings unlimited in length (TEXT) and variable-length strings (VARCHAR with suitable length attribute) are often stored in similar way, so TEXT and VARCHAR unlimited in length (or VARCHAR with large length attribute) are often functionally very similar. This is so for example in SQLite and PostgreSQL. In contrast, in MySQL, TEXT and VARCHAR types have clearer differences regarding data storage and handling. When building a real database it is always important to familiarize oneself with data types offered by said system and their properties.

Based on this, let's modify Faculty table to following form:

CREATE TABLE Faculty (
  id INTEGER,
  name TEXT NOT NULL UNIQUE,
  PRIMARY KEY (id)
);

Thus we got the final definition of Faculty table.

Modify tables Faculty and Department in the submission box below by adding suitable data types and possible integrity constraints to their columns.

You can float the diagram editor with the Float button. Then the text moves to the right edge, and space is freed up for the editor on the right.

Sarakkeiden tietotyyppien ja eheysrajoitteiden määritys

# openTietotyypit

Seuraavaksi määritellään tarkemmat tietotyypit taulujen sarakkeille.

Oletuksena SQLite-tauluissa sarakkeisiin voi tallentaa mitä tahansa dataa. Toisin sanoen esimerkiksi SQLite sallii, että Opiskelija-taulussa on rivejä, joissa aloitusvuosi on 'kissa' tai että kurssin laajuus on 1.1.1996. Jotta vältytään virheiltä dataa tauluihin tallennettaessa, kannattaa jokaiselle sarakkeelle määrittää tarkka tietotyyppi.

Tietotyypit eroavat jonkin verran eri tietokannanhallintajärjestelmien välillä. Lähtökohtaisesti kaikki SQL-kieltä tukevat tietokannanhallintajärjestelmät tukevat alla olevia tietotyyppejä (ks. Luku 4.3).

Data Type	Description	Usage Example	Example Column	Example Value
CHAR, CHARACTER	fixed-length string	`CHAR(10)`	`SSN`	`'111111-XXX'`
VARCHAR, CHARACTER VARYING	variable-length string	`VARCHAR(20)`	`first_name`	`'Seppo'` or `'Viljami'`
INTEGER, INT	integer	`INT`	`birth_year`	`1990`
NUMERIC	(decimal) number (integer part and decimal part possibly limited)	`NUMERIC(7,2)`	`monthly_salary`	`2600.12`
BOOLEAN	truth value	`BOOLEAN`	`order_confirmed`	`TRUE`
DATE	date	`DATE`	`order_date`	`'2025-01-12'`
TIMESTAMP	timestamp	`TIMESTAMP`	`payment_time`	`'12:00:00'`
ARRAY	list	`INTEGER[7]`	`lotto_numbers`	`[1, 2, 3, 4, 5, 6, 7]`
TEXT	text-format column	`TEXT`	`review`	`'Kissa istuu puussa.'`

Eri järjestelmissä on toki muitakin tietotyyppejä. Esimerkiksi SQLite tukee yllä mainittujen lisäksi yleistä BLOB-tyyppiä, johon voi tallentaa mitä tahansa binääridataa (esim. kuvatiedostoja tai muita dokumentteja).

Myös sarakkeille voi antaa eheysrajoitteita, joilla voi vaikuttaa siihen, mitä arvoja sarakkeessa saa olla. Yleisimmät eheysrajoitteet on esitelty alla (ks. Luku 4.3).

DEFAULT clause sets a default value for the column.
NOT NULL clause forbids null values from the column.
UNIQUE clause guarantees that all values of the column are unique.
CHECK clause can be used to check that column values follow given conditional expression.

Sekä tietotyyppi että eheysrajoite määritellään jokaisen sarakkeen yhteydessä:

CREATE TABLE taulu (
    sarake tietotyyppi[ eheysrajoite[ eheysrajoite]*][, 
    sarake tietotyyppi[ eheysrajoite[ eheysrajoite]*]]*
);

Tarkastellaan taas taulua Tiedekunta ja asetetaan sen sarakkeille tietotyypit. Relaatiokaavan, ER-kaavion sekä alkuperäisen kuvauksen (ks. Harjoitus 2.1) perusteella voidaan päätellä seuraavat asiat sarakkeista:

Sarakkeelle id sopisi parhaiten tyyppi INTEGER eli kokonaisluku. Kokonaisluku on pienikokoinen, helppo käsitellä, ja eri tiedekunnille on helppo antaa yksilöllinen numero-ID. Koska sarake kuuluu perusavaimeen, se on jo yksilöllinen eikä salli NULL-arvoja.
Sarakkeelle nimi sopisi parhaiten tyyppi TEXT eli tekstimuotoinen sarake. Emme tiedä ennalta, kuinka pitkiä tiedekuntien nimet voivat olla, joten sen pituutta ei ole syytä rajata. Tämän lisäksi voidaan olettaa, että kaikilla tiedekunnilla on oltava nimi (eli se ei voi olla tyhjä, NOT NULL), ja että jokaisen tiedekunnan nimen on oltava yksilöllinen (UNIQUE), eli kahdella eri tiedekunnalla ei voi olla samaa nimeä.
- Bonustieto: Monissa lähteissä tekstin tyypiksi ehdotetaan VARCHAR. Käytännössä nykyään tietokannanhallintajärjestelmissä pituudeltaan rajoittamattomat merkkijonot (TEXT) ja vaihtelevan pituiset merkkijonot (VARCHAR sopivalla pituusmääreellä) tallennetaan usein samankaltaisella tavalla, joten TEXT ja pituudeltaan rajoittamaton VARCHAR (tai VARCHAR suurella pituusmääreellä) ovat usein toiminnallisesti hyvin samankaltaisia. Näin on esimerkiksi SQLite:ssa ja PostgreSQL:ssä. Sitä vastoin MySQL:ssä TEXT- ja VARCHAR-tyypeillä on selkeämpiä eroja datan tallennuksen ja käsittelyn kannalta. Oikeaa tietokantaa rakentaessa on aina tärkeää tutustua kyseisen järjestelmän tarjoamiin tietotyyppeihin ja niiden ominaisuuksiin.

Tämän perusteella muokataan Tiedekunta-taulu seuraavaan muotoon:

CREATE TABLE Tiedekunta (
  id INTEGER,
  nimi TEXT NOT NULL UNIQUE,
  PRIMARY KEY (id)
);

Näin saatiin lopullinen Tiedekunta-taulun määritys.

Muokkaa alla olevassa palautuslaatikossa taulut Tiedekunta ja Laitos lisäämällä niiden sarakkeisiin sopivat tietotyypit sekä mahdolliset eheysrajoitteet.

Voit kelluttaa kaavioeditorin Kelluta-painikkeella. Silloin teksti siirtyy oikeaan laitaan, ja editorille vapautuu tilaa oikealle.

# 780197.ex41-step3

Entering and Searching Data

# openData

Let's try at this stage to enter model data to the two created tables and execute simple search queries.

New rows can be entered to table using INSERT command (see Chapter 4.2):

Based on this example let's add a couple of rows to tables Faculty and Department. Let's invent imaginative names and IDs for faculties and departments.

INSERT INTO Faculty VALUES (1, 'Faculty of Mathematics and Science');

INSERT INTO Department VALUES (1, 'Department of Mathematics', 1);
INSERT INTO Department VALUES (2, 'Department of Physics', 1);

Huomautus

Additional notes (click to open)

When entering rows one can define columns to which values are entered. For example adding faculty above could be written more precisely:
```
INSERT INTO Faculty (id, name)
VALUES (1, 'Faculty of Mathematics and Science');
```
This notation way is useful in cases where one does not want to or cannot give values to all columns. For example in SQLite if primary key is integer, ID column value can be left out, in which case database management system gives it automatically next free integer:
```
-- Note: giving only name. If id is INTEGER PRIMARY KEY,
-- SQLite sets primary key for the row automatically.
-- NOTE: This does not necessarily work directly in all database management systems!
INSERT INTO Faculty (name)
VALUES ('Faculty of Education and Psychology');
```

Some database management systems (like SQLite) allow adding multiple rows to same table with one INSERT command as follows:

INSERT INTO Faculty (id, name) -- It is usually good to define column names even if they could be left out.
VALUES (1, 'Faculty of Mathematics and Science'),
       (2, 'Faculty of Education and Psychology');

Order of commands can matter! SQL language is always executed command at a time, from top to bottom. If there is a foreign key in table referring to another table, one must first add rows to the table referred to, and only after that rows containing the reference.

For example the example below DOES NOT WORK, because foreign key constraint Department.faculty_id -> Faculty.id is in effect:

-- DOES NOT WORK: Here we try to add a row to Department table, whose faculty_id column 
-- is set to value 1. However, in Faculty table there is not yet a row whose id would be 1,
-- because it is added only later.
-- Faculty must thus be already added before adding departments that
-- belong to said faculty!
INSERT INTO Department (id, name, faculty_id) VALUES (1, 'Department of Mathematics', 1);
INSERT INTO Department (id, name, faculty_id) VALUES (2, 'Department of Physics', 1);

INSERT INTO Faculty (id, name) VALUES (1, 'Faculty of Mathematics and Science');

In other words, departments cannot refer to such faculties that do not exist yet at the moment of adding department.

When data has been added, it can be searched with SELECT command. SELECT is SQL language's most versatile command: with it one can search table rows based on different conditions, choose specific columns, join data from different tables, execute calculations with column values and group rows together. SQL language's SELECT command is gone through in more detail in Chapter 4.2 and Exercise 4.2.

Let's get to know the simplest form of search statement in this task: search based on simple condition.

In SELECT command, searched columns (column1, column2, ...), table (table) from which data is searched, and optional WHERE condition (conditional_expression) with which result set is limited are defined.

For example searching all faculties succeeds with command:

SELECT id, name    -- Fetching columns id and name...
FROM Faculty;   -- ...from table Faculty.

Correspondingly names (without IDs) of all departments can be searched as follows:

SELECT name       -- Selecting column name...
FROM Department;      -- ...from table Department.

Next add the faculties and their departments listed below to the database. Give suitable unique IDs to faculties and departments (or let SQLite create them automatically).

Faculty: Faculty of Mathematics and Science
- Department: Department of Mathematics
- Department: Department of Physics
- Department: Department of Chemistry
- Department: Department of Biological and Environmental Science
Faculty: Faculty of Education and Psychology
- Department: Department of Teacher Education
- Department: Department of Education
- Department: Department of Psychology

Finally, search names of all faculties and names of all departments to ensure that addition succeeded. Do not search their IDs or other columns.

You can float the diagram editor with the Float button. Then the text moves to the right edge, and space is freed up for the editor on the right.

Datan syöttäminen ja hakeminen

# openData

Kokeillaan tässä vaiheessa syöttää mallidataa kahteen luotuun tauluun sekä suorittaa yksinkertaisia hakukyselyjä.

Uusia rivejä voi syöttää tauluun käyttäen INSERT-käskyä (ks. Luku 4.2):

INSERT INTO table [(column[, column]*)]? 
VALUES (value[, value]*);

Tämän esimerkin perusteella lisätään pari riviä tauluihin Tiedekunta ja Laitos. Keksitään tiedekunnille ja laitoksille mielikuvitukselliset nimet ja ID:t.

INSERT INTO Tiedekunta VALUES (1, 'Matemaattis-luonnontieteellinen tiedekunta');

INSERT INTO Laitos VALUES (1, 'Matematiikan laitos', 1);
INSERT INTO Laitos VALUES (2, 'Fysiikan laitos', 1);

Huomautus

Lisähuomioita (avaa klikkaamalla)

Rivejä syötettäessä voi määrittää sarakkeet, joihin arvot syötetään. Esimerkiksi yllä oleva tiedekunnan lisääminen voitaisiin kirjoittaa tarkemmin:
```
INSERT INTO Tiedekunta (id, nimi)
VALUES (1, 'Matemaattis-luonnontieteellinen tiedekunta');
```
Tämä merkintätapa on hyödyllinen tapauksissa, joissa kaikille sarakkeille ei haluta tai voida antaa arvoja. Esimerkiksi SQLite:ssä jos perusavaimena on kokonaisluku, voidaan ID-sarakkeen arvo jättää antamatta, jolloin tietokannanhallintajärjestelmä antaa sille automaattisesti seuraavan vapaan kokonaisluvun:
```
-- Huomaa: annetaan vain nimi. Jos id on INTEGER PRIMARY KEY,
-- SQLite asettaa riville perusavaimen automaattisesti.
-- HUOM: Tämä ei välttämättä suoraan toimi kaikissa tietokannanhallintajärjestelmissä!
INSERT INTO Tiedekunta (nimi)
VALUES ('Kasvatustieteiden ja psykologian tiedekunta');
```

Jotkin tietokannanhallintajärjestelmän (kuten SQLite) sallivat usean rivin lisäämisen samaan tauluun yhdellä INSERT-käskyllä seuraavasti:

INSERT INTO Tiedekunta (id, nimi) -- Sarakkeiden nimet on yleensä hyvä määritellä vaikka niitä voisikin jättää pois.
VALUES (1, 'Matemaattis-luonnontieteellinen tiedekunta'),
       (2, 'Kasvatustieteiden ja psykologian tiedekunta');

Käskyjen järjestyksellä voi olla merkitys! SQL-kieltä suoritetaan aina käsky kerrallaan, ylhäältä alas. Jos taulussa on viiteavain, joka viittaa toiseen tauluun, on ensin lisättävä rivit siihen tauluun, johon viitataan, ja vasta sen jälkeen rivit, jotka sisältävät viittauksen.

Esimerkiksi alla oleva esimerkki EI TOIMI, koska viiteavainrajoite Laitos.tiedekunta_id -> Tiedekunta.id on voimassa:

-- EI TOIMI: Tässä yritetään lisätä Laitos-tauluun rivi, jonka tiedekunta_id-sarakkeeseen 
-- asetetaan arvo 1. Kuitenkaan Tiedekunta-taulussa ei ole vielä riviä, jonka id olisi 1,
-- koska se lisätään vasta myöhemmin.
-- Tiedekunnan on siis oltava jo lisättynä ennen kuin lisätään laitoksia, jotka
-- kuuluvat kyseiseen tiedekuntaan!
INSERT INTO Laitos (id, nimi, tiedekunta_id) VALUES (1, 'Matematiikan laitos', 1);
INSERT INTO Laitos (id, nimi, tiedekunta_id) VALUES (2, 'Fysiikan laitos', 1);

INSERT INTO Tiedekunta (id, nimi) VALUES (1, 'Matemaattis-luonnontieteellinen tiedekunta');

Toisin sanoen, laitokset eivät voi viitata sellaisiin tiedekuntiin, joita ei ole vielä olemassa laitoksen lisäyshetkellä.

Kun dataa on lisätty, sitä voidaan hakea SELECT-käskyllä. SELECT on SQL-kielen monipuolisin käsky: sillä voi hakea taulun rivejä erilaisten ehtojen perusteella, valita tietyt sarakkeet, yhdistää dataa eri tauluista, suorittaa laskutoimituksia sarakkeiden arvoilla ja ryhmitellä rivejä yhteen. SQL-kielen SELECT-käskyä käydään läpi tarkemmin Luvussa 4.2 sekä Harjoituksessa 4.2.

Tutustutaan tässä tehtävässä yksinkertaisimpaan hakulauseen muotoon: haku yksinkertaisen ehdon perusteella.

UPDATE table
SET column = expression[, column = expression]*
[WHERE condition[ operator condition]*]?;

SELECT-käskyssä määritellään haettavat sarakkeet (sarake1, sarake2, ...), taulu (taulu), josta dataa haetaan, ja valinnainen WHERE-ehto (ehtolauseke), jolla rajataan tulosjoukkoa.

Esimerkiksi kaikkien tiedekuntien hakeminen onnistuu komennolla:

SELECT id, nimi    -- Haetaan sarakkeet id ja nimi...
FROM Tiedekunta;   -- ...taulusta Tiedekunta.

Vastaavasti kaikkien laitosten nimet (eikä ID:itä) voi hakea seuraavasti:

SELECT nimi       -- Valitaan sarake nimi...
FROM Laitos;      -- ...taulusta Laitos.

Lisää seuraavaksi tietokantaan alla luetellut tiedekunnat ja niiden laitokset. Anna tiedekunnille ja laitoksille sopivat yksilölliset ID:t (tai anna SQLite:n luoda ne automaattisesti).

Tiedekunta: Matemaattis-luonnontieteellinen tiedekunta
- Laitos: Matematiikan laitos
- Laitos: Fysiikan laitos
- Laitos: Kemian laitos
- Laitos: Bio- ja ympäristötieteiden laitos
Tiedekunta: Kasvatustieteiden ja psykologian tiedekunta
- Laitos: Opettajankoulutuslaitos
- Laitos: Kasvatustieteiden laitos
- Laitos: Psykologian laitos

Lopuksi, hae kaikkien tiedekuntien nimet ja kaikkien laitosten nimet varmistaaksesi, että lisäys onnistui. Älä hae niiden ID:itä tai muita sarakkeita.

Voit kelluttaa kaavioeditorin Kelluta-painikkeella. Silloin teksti siirtyy oikeaan laitaan, ja editorille vapautuu tilaa oikealle.

# 780197.ex41-step4

Exercise: Defining Other Tables

# openDatabase

Now go through the rest of the relations in relation schema and define tables corresponding to them in SQL language. Give tables and their columns suitable integrity constraints (primary key, foreign key, mandatory column) and define suitable data types for columns.

Then try adding data to every table. You can decide yourself what data and how much of it you add. There should be at least one row in every table.

Tips

Remember that order of adding rows can matter due to referential integrity check! For example if table has foreign key, when adding rows there must already exist a row in the referenced table (parent table) to which is referred. It is worth starting addition of rows from tables that do not have foreign keys, and proceed then to tables referring to them.

If you get error message like following:
```
FOREIGN KEY constraint failed
```
it means that you are trying to add a row whose foreign key value does not exist yet in parent table.

For example departments always belong to faculties, so before adding department one must add faculty to which department belongs. Correspondingly, before students can be added, one must add department to which student belongs. With same logic exam results can be added only to such course codes that exist in Course table.
For date and time, data type DATETIME is suitable. Its values should be written as string in format 'YYYY-MM-DD HH:MM:SS', for example '2026-04-24 11:56:48'. Correspondingly type DATE means date without time and TIME only time.
Note that automatic checker knows how to check your answer only if table and column names are exactly same as in relation schema.
In this target area (university student register) it is reasonable for course code to be a string (TEXT). Course codes are usually of format ITKP102, TJTA2020, ITKA2004. The checker of the submission box below assumes that course code is a string.

You can float the diagram editor with the Float button. Then the text moves to the right edge, and space is freed up for the editor on the right.

Harjoitus: Muiden taulujen määritys

# openTietokanta

Käy nyt läpi loput relaatiokaavan relaatiot ja määrittele niitä vastaavat taulut SQL-kielellä. Anna tauluille sekä niiden sarakkeille sopivat eheysrajoitteet (perusavain, viiteavain, sarakkeen pakollisuus) ja määritä sarakkeille sopivat tietotyypit.

Kokeile sitten lisätä dataa jokaiseen tauluun. Saat itse päättää, mitä dataa ja kuinka paljon sitä lisäät. Jokaisessa taulussa tulee olla vähintään yksi rivi.

Vinkkejä

Muista, että rivien lisäysjärjestyksellä voi olla väliä viite-eheyden tarkistuksen takia! Esimerkiksi jos taululla on viiteavain, rivejä lisättäessä viitatussa taulussa (isäntätaulussa) tulee olla jo olemassa rivi, johon viitataan. Rivien lisääminen kannattaa aloittaa tauluista, joissa ei ole viiteavaimia, ja edetä sitten tauluihin, jotka viittaavat niihin.

Jos saat seuraavanlaisen virheilmoituksen:
```
FOREIGN KEY constraint failed
```
se tarkoittaa, että yrität lisätä rivin, jonka viiteavaimen arvo ei ole vielä olemassa isäntätaulussa.

Esimerkiksi laitokset kuuluvat aina tiedekuntiin, joten ennen laitoksen lisäämistä on lisättävä tiedekunta, johon laitos kuuluu. Vastaavasti, ennen kuin opiskelijoita voi lisätä, on lisättävä laitos, johon opiskelija kuuluu. Samalla logiikalla tenttisuorituksia voi lisätä vain sellaisille kurssikoodeille, jotka ovat olemassa Kurssi-taulussa.
Päivämääräksi ja kellonajaksi sopii tietotyyppi DATETIME. Sen arvot tulee kirjoittaa merkkijonona muodossa 'VVVV-KK-PP HH:MM:SS', esimerkiksi '2026-04-24 11:56:48'. Vastaavasti tyyppi DATE tarkoittaa päivämäärää ilman kellonaikaa ja TIME pelkkää kellonaikaa.
Huomaa, että automaattitarkistin osaa tarkistaa vastauksesi vain, jos taulujen sekä sarakkeiden nimet ovat täsmälleen samat kuin relaatiokaavassa.
Tässä kohdealueessa (yliopiston opiskelijarekisteri) kurssikoodin on järkevää olla merkkijono (TEXT). Kurssikoodit ovat yleensä muotoa ITKP102, TJTA2020, ITKA2004. Alla olevan palautuslaatikon tarkistin olettaa, että kurssikoodi on merkkijono.

Voit kelluttaa kaavioeditorin Kelluta-painikkeella. Silloin teksti siirtyy oikeaan laitaan, ja editorille vapautuu tilaa oikealle.

# 780197.ex41-step5

Finally

When you are ready, you can continue forward to other exercises.

You can give a short self-assessment of your success and general feedback on the exercise below.

# palaute

Lopuksi

Kun olet valmis, voit jatkaa eteenpäin muihin harjoitustehtäviin.

Voit antaa lyhyen itsearvion onnistumisestasi ja yleisesti palautteen harjoituksesta alla.

# palaute