Tietokantojen perusteet kesä 2024

Tehtävä 2: Tietokannan suunnittelu

Tehtäväsi on toteuttaa Pythonilla moduuli courses.py, jonka avulla voidaan hallinnoida yliopiston kursseihin liittyviä tietoja. Moduuli säilyttää tietoja tietokannassa courses.db.

Tietokantaan voidaan lisätä opettajia, kursseja, opiskelijoita, suorituksia ja ryhmiä. Kurssilla voi olla useampia opettajia. Ryhmässä on jokin joukko opettajia ja opiskelijoita. Suunnittele tietokanta niin, että siinä on sopivat taulut tietojen tallentamista varten. Seuraa tietokannan suunnittelussa kurssimateriaalin luvun 6 ohjeita.

Funktiossa create_tables tulee olla SQL-komennot, jotka luovat taulut. Muut create-funktiot lisäävät tietoa tauluihin INSERT-komennoilla, ja niiden tulee palauttaa lisätyn rivin id-numero. Muut funktiot hakevat tietoa tauluista SELECT-komennoilla.

Seuraavassa on pohja moduulin courses.py toteutukselle: 

import os
import os.path
import sqlite3

# poistaa tietokannan alussa (kätevä moduulin testailussa)
if os.path.exists("courses.db"):
    os.remove("courses.db")

db = sqlite3.connect("courses.db")
db.isolation_level = None

# luo tietokantaan tarvittavat taulut
def create_tables():
    pass

# lisää opettajan tietokantaan
def create_teacher(name):
    pass

# lisää kurssin tietokantaan
def create_course(name, credits, teacher_ids):
    pass

# lisää opiskelijan tietokantaan
def create_student(name):
    pass

# antaa opiskelijalle suorituksen kurssista
def add_credits(student_id, course_id, date, grade):
    pass

# lisää ryhmän tietokantaan
def create_group(name, teacher_ids, student_ids):
    pass

# hakee kurssit, joissa opettaja opettaa (aakkosjärjestyksessä)
def courses_by_teacher(teacher_name):
    pass

# hakee opettajan antamien opintopisteiden määrän
def credits_by_teacher(teacher_name):
    pass

# hakee opiskelijan suorittamat kurssit arvosanoineen (aakkosjärjestyksessä)
def courses_by_student(student_name):
    pass

# hakee tiettynä vuonna saatujen opintopisteiden määrän
def credits_by_year(year):
    pass

# hakee kurssin arvosanojen jakauman (järjestyksessä arvosanat 1-5)
def grade_distribution(course_name):
    pass

# hakee listan kursseista (nimi, opettajien määrä, suorittajien määrä) (aakkosjärjestyksessä)
def course_list():
    pass

# hakee listan opettajista kursseineen (aakkosjärjestyksessä opettajat ja kurssit)
def teacher_list():
    pass

# hakee ryhmässä olevat henkilöt (aakkosjärjestyksessä)
def group_people(group_name):
    pass

# hakee ryhmissä saatujen opintopisteiden määrät (aakkosjärjestyksessä)
def credits_in_groups():
    pass

# hakee ryhmät, joissa on tietty opettaja ja opiskelija (aakkosjärjestyksessä)
def common_groups(teacher_name, student_name):
    pass
Seuraava moduuli main.py testaa moduulia courses.py ja näyttää tarkemmin, mikä on funktioiden haluttu toimintatapa. 
import courses

courses.create_tables()

t1 = courses.create_teacher("Erkki Kaila")
t2 = courses.create_teacher("Antti Laaksonen")
t3 = courses.create_teacher("Matti Luukkainen")
t4 = courses.create_teacher("Emilia Oikarinen")
t5 = courses.create_teacher("Leena Salmela")

c1 = courses.create_course("Laskennan mallit", 5, [t1])
c2 = courses.create_course("Ohjelmistotuotanto", 5, [t2, t4, t5])
c3 = courses.create_course("Ohjelmoinnin perusteet", 5, [])
c4 = courses.create_course("Tietokantojen perusteet", 5, [t3, t5])
c5 = courses.create_course("Tietokoneen toiminta", 5, [t1])

s1 = courses.create_student("Heikki Lokki")
s2 = courses.create_student("Liisa Marttinen")
s3 = courses.create_student("Otto Nurmi")
s4 = courses.create_student("Esko Ukkonen")
s5 = courses.create_student("Arto Wikla")

courses.add_credits(s1, c2, "2020-06-01", 5)
courses.add_credits(s1, c3, "2021-01-08", 3)
courses.add_credits(s2, c5, "2022-03-23", 2)
courses.add_credits(s4, c3, "2022-01-27", 4)
courses.add_credits(s4, c4, "2021-05-05", 4)
courses.add_credits(s4, c2, "2021-10-03", 5)
courses.add_credits(s4, c5, "2021-10-04", 5)
courses.add_credits(s5, c2, "2020-12-24", 1)

courses.create_group("Basic-koodarit", [t2, t3], [s1, s2, s3])
courses.create_group("Cobol-koodarit", [t1], [s2, s4])
courses.create_group("Fortran-koodarit", [t1, t2, t3, t4, t5], [s1, s2, s3, s4, s5])
courses.create_group("PHP-koodarit", [t4, t5], [s3])

print(courses.courses_by_teacher("Leena Salmela"))
print(courses.credits_by_teacher("Leena Salmela"))
print(courses.courses_by_student("Esko Ukkonen"))

print(courses.credits_by_year(2020))
print(courses.credits_by_year(2021))
print(courses.credits_by_year(2022))

print(courses.grade_distribution("Ohjelmoinnin perusteet"))
print(courses.grade_distribution("Tietokoneen toiminta"))

print(courses.course_list())
print(courses.teacher_list())

print(courses.group_people("Basic-koodarit"))
print(courses.credits_in_groups())
print(courses.common_groups("Antti Laaksonen", "Otto Nurmi"))
Kun moduuli main.py suoritetaan, sen tulisi antaa seuraava tulostus: 
['Ohjelmistotuotanto', 'Tietokantojen perusteet']
20
[('Ohjelmistotuotanto', 5), ('Ohjelmoinnin perusteet', 4), ('Tietokantojen perusteet', 4), ('Tietokoneen toiminta', 5)]
10
20
10
{1: 0, 2: 0, 3: 1, 4: 1, 5: 0}
{1: 0, 2: 1, 3: 0, 4: 0, 5: 1}
[('Laskennan mallit', 1, 0), ('Ohjelmistotuotanto', 3, 3), ('Ohjelmoinnin perusteet', 0, 2), ('Tietokantojen perusteet', 2, 1), ('Tietokoneen toiminta', 1, 2)]
[('Antti Laaksonen', ['Ohjelmistotuotanto']), ('Emilia Oikarinen', ['Ohjelmistotuotanto']), ('Erkki Kaila', ['Laskennan mallit', 'Tietokoneen toiminta']), ('Leena Salmela', ['Ohjelmistotuotanto', 'Tietokantojen perusteet']), ('Matti Luukkainen', ['Tietokantojen perusteet'])]
['Antti Laaksonen', 'Heikki Lokki', 'Liisa Marttinen', 'Matti Luukkainen', 'Otto Nurmi']
[('Basic-koodarit', 15), ('Cobol-koodarit', 25), ('Fortran-koodarit', 40), ('PHP-koodarit', 0)]
['Basic-koodarit', 'Fortran-koodarit']
Saat 2,5 pistettä jokaisesta toimivasta funktiosta, jotka hakevat tietoa tietokannasta (alkaen funktiosta courses_by_teacher). Jotta nämä funktiot toimisivat, sinun tulee toteuttaa myös tietoa lisäävät funktiot.

Tehtävään liittyviä videoita:

Arvostelu

Seuraava moduuli main.py testaa moduuliasi courses.py arvostelua varten. Huomaa, että funktioita kutsutaan tässä eri tavalla kuin yllä olevassa esimerkissä. 
import courses

courses.create_tables()

t1 = courses.create_teacher("Erkki Kaila")
t2 = courses.create_teacher("Antti Laaksonen")
t3 = courses.create_teacher("Matti Luukkainen")
t4 = courses.create_teacher("Emilia Oikarinen")
t5 = courses.create_teacher("Leena Salmela")

c1 = courses.create_course("Laskennan mallit", 7, [t1, t3])
c2 = courses.create_course("Ohjelmistotuotanto", 9, [t1, t2, t5])
c3 = courses.create_course("Ohjelmoinnin perusteet", 8, [t2, t5])
c4 = courses.create_course("Tietokantojen perusteet", 4, [t3, t4])
c5 = courses.create_course("Tietokoneen toiminta", 6, [t5])

s1 = courses.create_student("Heikki Lokki")
s2 = courses.create_student("Liisa Marttinen")
s3 = courses.create_student("Otto Nurmi")
s4 = courses.create_student("Esko Ukkonen")
s5 = courses.create_student("Arto Wikla")

courses.add_credits(s1, c1, "2020-01-10", 1)
courses.add_credits(s1, c2, "2021-05-02", 2)
courses.add_credits(s1, c4, "2021-04-20", 5)
courses.add_credits(s2, c1, "2021-03-10", 5)
courses.add_credits(s2, c2, "2022-09-08", 5)
courses.add_credits(s3, c3, "2022-09-10", 3)
courses.add_credits(s4, c3, "2022-11-01", 3)
courses.add_credits(s4, c4, "2020-11-29", 5)

courses.create_group("Basic-koodarit", [t1, t2], [s1, s2, s3, s5])
courses.create_group("Cobol-koodarit", [t4], [s2, s4, s5])
courses.create_group("Fortran-koodarit", [], [s5])
courses.create_group("PHP-koodarit", [t1, t2, t3], [s2, s3, s4, s5])

print(courses.courses_by_teacher("Leena Salmela"))
print(courses.credits_by_teacher("Leena Salmela"))
print(courses.courses_by_student("Esko Ukkonen"))

print(courses.credits_by_year(2020))
print(courses.credits_by_year(2021))
print(courses.credits_by_year(2022))

print(courses.grade_distribution("Ohjelmoinnin perusteet"))
print(courses.grade_distribution("Tietokoneen toiminta"))

print(courses.course_list())
print(courses.teacher_list())

print(courses.group_people("Basic-koodarit"))
print(courses.credits_in_groups())
print(courses.common_groups("Antti Laaksonen", "Otto Nurmi"))
Kirjoita alla oleviin laatikoihin moduulin main.py tulostus sekä moduulin courses.py lähdekoodi. Saat arvostelussa pisteet jokaisesta tietoa hakevasta funktiosta, joka tuottaa oikean tuloksen. Jos et ole toteuttanut jotain funktiota, voit kommentoida kyseisen rivin pääohjelmassa.

Moduulin main.py tulostus

Moduulin courses.py lähdekoodi

The deadline for this task has passed but you can still check your answers

Return to task list