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: Introduction to Normalization
This is the last guided exercise of the course. In the previous guided exercises, we designed, defined, and implemented a simple relational database for maintaining a student registry at a university.
In this exercise, we will do a final check on the created relational database by checking its normalization.
Usually, following the transformation rules presented in Chapter 3.2 already yields well-normalized relations (usually 3NF or even BCNF). This task is indeed quite short, because as a result of successful design and transformation, our student registry is already largely normalized 🥳. However, let's ensure this with a separate examination.
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 5.1: especially definition of functional dependency
- Chapter 5.2: definitions of 1NF, 2NF, 3NF, BCNF, and 4NF in main points
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. This time we examine the database's normal forms.
Follow the steps in the sections below and complete the answers in the submission boxes below based on that. A model answer is found in connection with each task.
Functional Dependencies
Before examining normalization, we must search for functional dependencies in every relation schema. Functional dependency is defined in Chapter 5.1. You can see a quick definition below:
Quick definition of functional dependency
Simply put, a value of relation attribute X corresponds to at most one value of attribute Y. For example, in a patient database, a social security number would always correspond to one person, meaning person is functionally dependent on social security number. Correspondingly, based on a person's name, one might not necessarily find out one specific person, as there can be namesakes with the same name. Thus, a person is usually not functionally dependent on their name.
Functional dependency is denoted as follows:
{X} -> {Y}
If attribute X identifies multiple attributes, they can be denoted as follows:
{X} -> {A, B, C}
In the above then A, B and C are functionally dependent on X. If in addition to attribute X another attribute is needed to identify attribute Y, it can be denoted as follows:
{X, Z} -> {Y}
In the notation above, both attribute X and attribute Z values are needed together to identify the value of attribute Y.
As a recap: in this task the following notations are used:
{X} -> {Y}: attributeYis functionally dependent on attributeX{X} -> {A, B, C}: attributesA,BandCare functionally dependent on attributeX- This means the same as
{X} -> {A},{X} -> {B}and{X} -> {C}
- This means the same as
{X, Z} -> {Y}: attributeYis functionally dependent on the combination of attributesXandZ
Examining functional dependencies requires that the domain is known well enough. At this stage at the latest, additional assumptions have to be made.
In the case of the student database, we are lucky. We got into a meeting with the university administration, where they clarified the following things related to students and the university:
- Some students might share their email address; for example, some couples do so.
- However, students do not share their phone number, i.e., it is always unique.
- Multiple students can live at the same address (for example in a shared apartment).
- There can be namesakes in the university, i.e., students with the same name.
- Course code is usually a string, e.g. "ITKA2004".
- Different departments might have courses with the same name with different course codes, e.g. "TJTA3001 Bachelor's Seminar" and "TEKA005 Bachelor's Seminar".
- However, the names of courses within the same department are unique.
- Department names are wanted to be kept unique. I.e. there can be only one "Department of Mathematics" in the whole university.
- At the university level, faculty names are unique. I.e. there cannot be two "Faculties of Information Technology".
- A student can take an exam for the same course many times.
Based on this information, we can start examining functional dependencies in the student database.
Let's go through every relation now and find all non-trivial functional dependencies (i.e. all those that cannot be derived or simplified based on inference rules presented in Chapter 5.1).
Save your answer in the end. You can view the model answer after saving by clicking the "Show model answer" button above the task.
You can detach the submission box to float by pressing the "Float" button.
Harjoitus: Johdatus normalisointiin
Tämä on viimeinen kurssin ohjattu harjoitus. Edellisissä ohjatuissa harjoituksissa suunniteltiin, määriteltiin ja toteutettiin yksinkertainen relaatiotietokanta opiskelijarekisterin ylläpidolle yliopistossa.
Tässä harjoituksessa tehdään viimeinen tarkistus luodulle relaatiotietokannalle tarkastamalla sen normalisointia.
Yleensä seuraamalla Luvussa 3.2 esitettyjä transformointisääntöjä saadaan jo valmiiksi hyvin normalisoituja relaatioita (yleensä 3NF tai jopa BCNF). Tämä tehtävä onkin varsin lyhyt, sillä onnistuneen suunnittelun ja transformoinnin seurauksena opiskelijarekisterimme on jo hyvin pitkälti normalisoitu 🥳. Varmistetaan asia kuitenkin vielä erillisellä tarkastelulla.
Tämä harjoitus on jatkoa Harjoitukselle 3.1. Tee ensin Harjoitus 3.1 ennen kuin aloitat tämän harjoituksen.
Ennen harjoituksen aloittamista tutustu seuraaviin lukuihin:
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ä
Jatketaan Harjoituksessa 3.1 työstettyä opiskelijatietokantaa. Tällä kertaa tarkastellaan tietokannan normaalimuotoja.
Seuraa alla olevissa osioissa olevia vaiheita ja täydennä alla oleviin palautuslaatikoihin vastauksia sen perusteella. Mallivastaus löytyy jokaisen tehtävän yhteydessä.
Funktionaaliset riippuvuudet
If a set of attributes X of relation R identifies another set of attributes Y of the same relation, it is said that a functional dependency holds in the relation
{X} -> {Y}
In a functional dependency, X is the determinant attribute set. It is said that "Attributes Y are functionally dependent on attributes X" or "Y is functionally dependent on X". More generally, one can say that "X determines Y".
In other words, for every tuple where attributes X have a certain value a, attributes Y have at most one value b.
Ennen normalisoinnin tarkastelua meidän tulee etsiä jokaisesta relaatiokaavasta funktionaalisia riippuvuuksia. Funktionaalinen riippuvuus määritellään Luvussa 5.1. Voit vielä katsoa pikaisen määrittelyn alta:
Funktionaalisen riippuvuuden pikainen määritelmä
If a set of attributes X of relation R identifies another set of attributes Y of the same relation, it is said that a functional dependency holds in the relation
{X} -> {Y}
In a functional dependency, X is the determinant attribute set. It is said that "Attributes Y are functionally dependent on attributes X" or "Y is functionally dependent on X". More generally, one can say that "X determines Y".
In other words, for every tuple where attributes X have a certain value a, attributes Y have at most one value b.
Yksinkertaisesti sanoen relaation attribuutin X jotakin arvoa vastaa korkeintaan yksi attribuutin Y arvo. Esimerkiksi potilastietokannassa henkilötunnusta vastaisi aina yksi henkilö, eli henkilö on funktionaalisesti riippuvainen henkilötunnuksesta. Vastaavasti henkilön nimen perusteella ei välttämättä saisi selville yhtä tiettyä henkilöä, sillä samalla nimellä voi olla kaimoja. Henkilö ei siis yleensä ole funktionaalisesti riippuvainen nimestään.
Funktionaalinen riippuvuus merkitään seuraavasti:
{X} -> {Y}
Jos attribuutti X yksilöi useita attribuutteja, ne voidaan merkitä seuraavasti:
{X} -> {A, B, C}
Yllä olevassa siis A, B ja C ovat funktionaalisesti riippuvaisia X:stä. Jos attribuutin X lisäksi tarvitaan vielä jokin toinen attribuutti, jotta attribuutti Y voidaan yksilöidä, voidaan merkitä seuraavasti:
{X, Z} -> {Y}
Yllä olevassa merkinnässä tarvitaan siis sekä attribuutin X että attribuutin Z arvot yhdessä, jotta voidaan yksilöidä attribuutin Y arvo.
Kertauksena: tässä tehtävässä käytetään seuraavia merkintöjä:
{X} -> {Y}: attribuuttiYon funktionaalisesti riippuva attribuutistaX{X} -> {A, B, C}: attribuutitA,BjaCovat funktionaalisesti riippuvaisia attribuutistaX- Tämä tarkoittaa samaa kuin
{X} -> {A},{X} -> {B}sekä{X} -> {C}
- Tämä tarkoittaa samaa kuin
{X, Z} -> {Y}: attribuuttiYon funktionaalisesti riippuva attribuuttienXjaZyhdistelmästä
Funktionaalisten riippuvuuksien tarkastelu edellyttää, että kohdealuetta tunnetaan riittävän hyvin. Viimeistään tässä vaiheessa joudutaan tekemään lisäoletuksia.
Opiskelijatietokannan tapauksessa meillä käy onni. Pääsimme tapaamiseen yliopiston hallinnon kanssa, jossa he tarkensivat seuraavia opiskelijoihin ja yliopistoon liittyviä asioita:
- Jotkut opiskelijat saattavat jakaa sähköpostiosoitteensa; esimerkiksi jotkut pariskunnat tekevät niin.
- Opiskelijat eivät kuitenkaan jaa puhelinnumeroaan, eli se on aina yksilöllinen.
- Samassa osoitteessa voi asua useampi opiskelija (esimerkiksi soluasunnossa).
- Yliopistossa voi olla kaimoja eli samannimisiä opiskelijoita.
- Kurssikoodi on yleensä merkkijono, esim. "ITKA2004".
- Eri laitoksilla saattaa olla samannimisiä kursseja eri kurssikoodeilla, esim. "TJTA3001 Kandidaattiseminaari" ja "TEKA005 Kandidaattiseminaari".
- Saman laitoksen kurssien nimet ovat kuitenkin yksilöllisiä.
- Laitosten nimet halutaan pitää yksilöllisinä. Eli esimerkiksi koko yliopistossa saa olla vain yksi "Matematiikan laitos".
- Yliopiston tasolla tiedekuntien nimet ovat yksilöllisiä. Eli ei voi olla kahta "Informaatioteknologian tiedekuntaa".
- Opiskelija voi tenttiä saman kurssin monta kertaa.
Näiden tietojen perusteella voidaan lähteä tarkastelemaan funktionaalisia riippuvuuksia opiskelijatietokannassa.
Käydään nyt läpi jokainen relaatio ja etsitään kaikki epätriviaalit funktionaaliset riippuvuudet (eli kaikki ne, joita ei voi johtaa tai yksinkertaistaa Luvussa 5.1 esitettyjen päättelysääntöjen perusteella).
Tallenna vastauksesi lopuksi. Voit katsoa mallivastauksen tallentamisen jälkeen napsauttamalla tehtävän yläpuolella olevaa "Näytä mallivastaus" -painiketta.
Voit irrottaa palautuslaatikon kelluvaksi painamalla "Kelluta" -painiketta.
Examination of Normal Forms
Based on the previous, we found the following non-trivial functional dependencies:
Opiskelija:
{id} -> {sähköpostiosoite, osoite, aloitusvuosi, etunimi, sukunimi, laitos_id}
Kurssi:
{kurssikoodi} -> {nimi, laajuus, laitos_id}
{laitos_id, nimi} -> {kurssikoodi, laajuus}
Laitos:
{id} -> {nimi, tiedekunta_id}
{nimi} -> {id, tiedekunta_id}
Tiedekunta:
{id} -> {nimi}
{nimi} -> {id}
Opiskelija_puhelinnumerot:
{puhelinnumero} -> {opiskelija_id}
Tenttii:
{opiskelija_id, kurssi_kurssikoodi, päivämäärä} -> arvosana
Let's review the normalization of relations based on these dependencies.
Do as follows:
- Look at the definition of each normal form below as well as helper rules.
- Check that every relation satisfies the definition. Here you can for example draw dependencies on paper and look through them one by one.
- Answer the multiple-choice question below the definition.
First Normal Form (1NF)
In other words, a relation is in 1NF if no attribute of the relation contains lists and they have clear data types.
In practice, all relations correctly transformed from an ER diagram are always in 1NF. This is because multi-valued attributes are transformed into their own relation.
So here we can state immediately that the student database satisfies 1NF.
Second Normal Form (2NF)
In other words, a relation is in 2NF if all following things hold:
- The relation is in 1NF.
- If the relation's primary key has only one attribute, the relation is in 2NF.
- If the relation has no attributes not belonging to the primary key, the relation is in 2NF.
- If the relation's primary key has multiple attributes, then one cannot remove any attribute from the primary key such that some attribute would still be dependent on such part of the primary key.
Third Normal Form (3NF)
In other words, a relation is in 3NF if all following conditions are met:
- The relation is in 2NF.
- No attribute not belonging to the primary key depends on another attribute not belonging to the primary key.
- Exception: if the attribute not belonging to the primary key is a candidate key (i.e., it identifies all other attributes of the relation), then this functional dependency is allowed.
Note especially the mentioned exception. For example in the student database in the Laitos relation the following functional dependencies hold:
{id} -> {nimi, tiedekunta_id}
{nimi} -> {id, tiedekunta_id}
Attribute nimi does not belong to the primary key, so in principle dependency {nimi} -> {tiedekunta_id} is transitive and thus would not satisfy 3NF. However, nimi is a candidate key, i.e., it identifies all other attributes of the relation (in the same way as the primary key). In 3NF such dependency is allowed, and thus the Laitos relation satisfies 3NF requirements.
Boyce/Codd Normal Form (BCNF)
In other words a relation is in BCNF if it is in 3NF and in every functional dependency the determinant part is a candidate key of the relation.
For example, in the student database the functional dependencies of the Kurssi relation are:
{kurssikoodi} -> {nimi, laajuus, laitos_id}
{laitos_id, nimi} -> {kurssikoodi, laajuus}
In the first dependency {kurssikoodi} is the primary key (and thus a candidate key). In the second dependency {laitos_id, nimi} is a candidate key (this can be deduced for example so that all attributes of the relation laitos_id, nimi, kurssikoodi and laajuus are mentioned in the dependency). Since the determinant parts of both functional dependencies are candidate keys, the relation is in BCNF.
Fourth Normal Form (4NF)
Note that 4NF relates to multi-valued dependency, not functional dependency. Multi-valued dependency {X} ->> {Y} means in practice that one value of determinant set X can correspond to one or more specific values of attribute Y, regardless of other attributes.
4NF is broken very rarely. It mainly relates only to situations, where one tries to model multiple N:M relationships with a single relation. If a relation is created by transforming an ER diagram correctly, the main requirement of 4NF usually holds.
In other words a relation is in 4NF if it is in BCNF and if one of the following conditions is met:
- The relation does not model an N:M relationship.
- The relation models only one N:M relationship.
- The relation models more than one N:M relationship, but the relationships rely on each other.
In practice, by transforming ER diagrams correctly it is principally impossible to get a relation, which would be in BCNF but not in 4NF. However, this can happen for example if transforming complicated tertiary N:M relationships or if there are errors in transformation. For this reason, 4NF is worth checking always just in case.
Finally
When you are ready, you can continue to other exercises.
Phew! This was the last guided exercise of the course. There will be no more student database 🙂.
This exercise was a "small" light ending, revisiting a bit what was achieved in previous exercises. At the same time, we got acquainted a bit with normal forms and general rules of thumb related to them.
Finally, I ask you to leave some feedback on the guided exercises of this course (Exercises 2.1, 3.1, 4.1 and 5.1). How did the whole idea seem in general? How did you feel you benefited from the guided exercises (or did you feel at all)? Suggestions for further development of guided exercises (if you haven't mentioned them already in feedback for previous tasks)?
Normaalimuotojen tarkastelu
Edellisen perusteella löysimme siis seuraavat epätriviaalit funktionaaliset riippuvuudet:
Opiskelija:
{id} -> {sähköpostiosoite, osoite, aloitusvuosi, etunimi, sukunimi, laitos_id}
Kurssi:
{kurssikoodi} -> {nimi, laajuus, laitos_id}
{laitos_id, nimi} -> {kurssikoodi, laajuus}
Laitos:
{id} -> {nimi, tiedekunta_id}
{nimi} -> {id, tiedekunta_id}
Tiedekunta:
{id} -> {nimi}
{nimi} -> {id}
Opiskelija_puhelinnumerot:
{puhelinnumero} -> {opiskelija_id}
Tenttii:
{opiskelija_id, kurssi_kurssikoodi, päivämäärä} -> arvosana
Katsotaan läpi relaatioiden normalisointia näiden riippuvuuksien perusteella.
Tee seuraavasti:
- Katso kunkin normaalimuodon määritelmä alta sekä apusäännöt.
- Tarkista, että jokainen relaatio täyttää määritelmän. Tässä voit esimerkiksi piirtää riippuvuudet paperille ja katsoa ne yksitellen läpi.
- Vastaa määritelmän alla olevaan monivalintatehtävään.
Ensimmäinen normaalimuoto (1NF)
A relation is in 1NF if it satisfies the requirements of a relation:
- A suitable primary key must be selected for the relation.
- The order of tuples (i.e., rows) must not have significance for the data.
- Each attribute (i.e., column) must have one clear data type. Mixing different types of data in the same attribute is forbidden.
- Each attribute is atomic, i.e., does not contain multiple values.
Toisin sanoin relaatio on 1NF:ssä, jos mikään relaation attribuutti ei sisällä listoja ja niillä on selkeät tietotyypit.
Käytännössä kaikki ER-kaaviosta oikein transformoidut relaatiot ovat aina 1NF:ssä. Tämä johtuu siitä, että moniarvoiset attribuutit transformoidaan omaksi relaatiokseen.
Siispä tässä voidaan todeta heti, että opiskelijatietokanta täyttää 1NF:n.
Toinen normaalimuoto (2NF)
A relation is in 2NF, if it satisfies the following requirements:
- The relation is in 1NF.
- No attribute not belonging to the primary key depends only on a part of the primary key, but on the whole primary key.
In other words, if a functional dependency {X} -> {Y} holds, where X has attributes belonging to the relation's primary key and Y does not belong to the primary key, then attributes belonging to the primary key cannot be removed from X such that the functional dependency would still hold. Such a functional dependency is called a full functional dependency.
Toisin sanoin relaatio on 2NF:ssä, jos kaikki seuraavat asiat pätevät:
- Relaatio on 1NF:ssä.
- Jos relaation perusavaimessa on vain yksi attribuutti, relaatio on 2NF:ssä.
- Jos relaatiossa ei ole perusavaimeen kuulumattomia attribuutteja, relaatio on 2NF:ssä.
- Jos relaation perusavaimessa on useampi attribuutti, niin perusavaimesta ei saa voida poistaa jotain attribuuttia siten, että jokin attribuutti olisi edelleen riippuvainen tällaisesta perusavaimen osasta.
Kolmas normaalimuoto (3NF)
A relation is in 3NF, if all following requirements hold:
- The relation is in 2NF.
- All attributes not belonging to the relation's primary key depend only, solely, and strictly on the primary key and not on any other attribute.
Exception: If the determinant attribute set of a functional dependency is a candidate key of the relation (i.e., it identifies all attributes of the relation), it does not violate 3NF.
Toisin sanoin relaatio on 3NF:ssä, jos kaikki seuraavat ehdot täyttyvät:
- Relaatio on 2NF:ssä.
- Mikään perusavaimeen kuulumaton attribuutti ei riipu jostain toisesta perusavaimeen kuulumattomasta attribuutista.
- Poikkeus: jos perusavaimeen kuulumaton attribuutti on avainehdokas (eli se yksilöi relaation kaikki muut attribuutit), tällöin tämä funktionaalinen riippuvuus on sallittu.
Huomaa erityisesti mainittu poikkeus. Esimerkiksi opiskelijatietokannassa Laitos-relaatiossa pätevät seuraavat funktionaaliset riippuvuudet:
{id} -> {nimi, tiedekunta_id}
{nimi} -> {id, tiedekunta_id}
Attribuutti nimi ei kuulu perusavaimeen, joten periaatteessa riippuvuus {nimi} -> {tiedekunta_id} on transitiivinen eikä siten täyttäisi 3NF:ää. Kuitenkin nimi on avainehdokas, eli se yksilöi relaation kaikki muut attribuutit (samalla tavalla kuin perusavain). 3NF:ssä tällainen riippuvuus on sallittu, ja siten Laitos-relaatio täyttää 3NF:n vaatimukset.
Boyce/Codd-normaalimuoto (BCNF)
A relation is in BCNF if the following requirements hold:
- The relation is in 3NF.
- All attributes of the relation depend only, solely, and strictly on some candidate key and not on any other attribute.
In other words, functional dependencies where the determinant set is not some candidate key of the relation are forbidden.
Toisin sanoin relaatio on BCNF:ssä, jos se on 3NF:ssä ja jokaisessa funktionaalisessa riippuvuudessa määräävä osa on relaation avainehdokas.
Esimerkiksi opiskelijatietokannassa Kurssi-relaation funktionaaliset riippuvuudet ovat:
{kurssikoodi} -> {nimi, laajuus, laitos_id}
{laitos_id, nimi} -> {kurssikoodi, laajuus}
Ensimmäisessä riippuvuudessa {kurssikoodi} on perusavain (ja siten avainehdokas). Toisessa riippuvuudessa {laitos_id, nimi} on avainehdokas (tämän voi päätellä esimerkiksi niin, että rippuvuudessa on mainittu relaation kaikki attribuutit laitos_id, nimi, kurssikoodi ja laajuus). Koska molempien funktionaalisten riippuvuuksien määräävät osat ovat avainehdokkaita, relaatio on BCNF:ssä.
Neljäs normaalimuoto (4NF)
A relation is in 4NF, if all following requirements are met:
- The relation is in BCNF.
- The determinant attribute set of all non-trivial multi-valued dependencies of the relation must be a candidate key of the relation.
In other words, if there is a multi-valued dependency {X} ->> {Y} in the relation, then attribute set X must be a candidate key of the relation.
Huomaa, että 4NF liittyy moniarvoiseen riippuvuuteen, ei funktionaaliseen riippuvuuteen. Moniarvoinen riippuvuus {X} ->> {Y} tarkoittaa käytännössä, että määräävän joukon X yhtä arvoa voi vastata yksi tai useampi tietty attribuutin Y arvo, riippumatta muista attribuuteista.
4NF rikotaan hyvin harvoin. Se liittyy pääsääntöisesti vain tilanteisiin, joissa yhdellä relaatiolla yritetään mallintaa useampaa N:M-suhdetta. Jos relaatio on luotu transformoimalla ER-kaavio oikeaoppisesti, 4NF:n päävaatimus yleensä pätee.
Toisin sanoin relaatio on 4NF:ssä, jos se on BCNF:ssä ja jos yksi seuraavista ehdoista täyttyy:
- Relaatio ei mallinna N:M-suhdetta.
- Relaatio mallintaa vain yhden N:M-suhteen.
- Relaatio mallintaa useamman kuin yhden N:M-suhteen, mutta suhteet riippuvat toisistaan.
Käytännössä ER-kaavioita oikeaoppisesti transformoimalla on periaatteessa mahdotonta saada relaatiota, joka olisi BCNF:ssä muttei 4NF:ssä. Näin voi kuitenkin käydä esimerkiksi, jos transformoidaan monimutkaisia tertiäärisiä N:M-suhteita tai jos transformoinnissa on virheitä. Tästä syystä 4NF kannattaa tarkistaa aina varmuuden vuoksi.
Lopuksi
Kun olet valmis, voit jatkaa eteenpäin muihin harjoitustehtäviin.
Huh! Tämä oli kurssin viimeinen ohjattu tehtävä. Enempää opiskelijatietokantaa ei enää tule 🙂.
Tämä tehtävä oli "pieni" loppukevennys, jolla kerrattiin hieman, mitä kaikkea edellisissä tehtävissä saatiin aikaan. Samalla tutustuttiin hieman normaalimuotoihin ja niihin liittyviin yleisiin nyrkkisääntöihin.
Pyydän lopuksi jättämään hieman palautetta tämän kurssin ohjatuista tehtävistä (Tehtävät 2.1, 3.1, 4.1 ja 5.1). Miltä koko ajatus ylipäätään vaikutti? Miten koit hyötyväsi ohjatuista tehtävistä (vai koitko ollenkaan)? Ehdotuksia ohjattujen tehtävien jatkokehitykseen (jos et ole maininnut niitä jo aiempien tehtävien palautteissa)?
These are the current permissions for this document; please modify if needed. You can always modify these permissions from the manage page.