EDAF75: Database Technology – Spring 2025

Announcements

Läsvecka 7

På måndag kommer vi att titta på den ordinarie tentamen förra året (mars 2024), jag tror att det är viktigt att försöka lösa uppgifterna på egen hand före genomgången.

I 'läsvecka' 8 kommer jag att gå igenom tentan från mars 2023 på tisdag, med början klockan 10:15, och tentan från mars 2022 på onsdag, med början klockan 10:15. Lokaler för dessa genomgångar anslås här på morgonen före respektive genomgång.

En kommentar om mail till LU

Mail på LU fungerar tyvärr numera väldigt dåligt, det finns lite mer information om det längst ner på denna sida.

Course overview

The main focus of this course is relational databases (RDBMS), but we'll also briefly touch upon alternatives (NoSQL) and technologies used in conjunction with databases. Looking at old exams, approximately 80% is about designing, normalizing and querying SQL databases, the remaining 20% is a mix of other things.

The course is divided into 11 sessions, where most sessions consist of one lecture, and some recommended homework – some of the homework will be discussed either during the lectures, or during QA-sessions.

We'll also have three lab exercises, at which you will get the opportunity to discuss your solutions to some homework problems with a teaching assistant.

Finally we have a project where you'll have to:

  • attend a design meeting (you and your group will peer review other groups' designs, and at the same time have your own design reviewed by other groups), and
  • hand in a working program and some documentation.

Schedule, spring 2025

There is a detailed schedule in TimeEdit.

The course is a work in progress, so I'll add material below as we go along.

Observe that the links below often stray way outside the boundaries of this course – the links are there to give you some context, they're not required reading.

Course week 1 (Jan 20 - Jan 24)

We'll talk about how to use SQL – it is a programming language which is specifically designed to make it easy to add, find and update information in relational databases.

  • Session 1 Introduction to the Relational Model, Relational Algebra, and SQL.
    • To prepare you for this session, I suggest you ponder a couple of questions.
    • The notebook I'll use can be fetched here (just unzip it into a directory in which you can run jupyter notebooks).
    • Here's the notebook as it looked afterwards, first as a notebook (copy it into the same directory in which you unzipped the original notebook), and then as a html page (if you just want to look at it, without running the cells). The few slides I used can be found here.
  • QA session: On Moodle you can sign up for a QA session on Wednesday, 12:15-… (I'll post the location of the QA session on Moodle once I've booked a room for it).
  • Session 2 Introduction to dependencies, redundance and anomalies in tables, splitting data into separate tables, and joining tables together. Different kinds of joins (inner, outer, cross, equijoins).
    • Before the lecture, you can try some of the exercises in the notebook for lecture 1.
    • The notebook I'll use can be fetched here (just unzip it into a directory in which you can run jupyter notebooks).
    • Here's the notebook as it looked afterwards, first as a notebook (copy it into the same directory in which you unzipped the original notebook), and then as a html page (if you just want to look at it, without running the cells).

Course week 2 (Jan 27 - Jan 31)

You need to sign up your lab/project group before 23:59 on Friday this week – see the top of this page for instructions on how to do it.

This week we'll look into how to create models of everyday systems, and translate those models into relational databases.

  • Session 3: E/R modelling using UML
    • The lecture will be held on Monday, 13:15 - 15:00 in E:A – to prepare for it you can read through some text here.
    • The slides I'll use for the lecture are here.
    • You can find some notes from the lecture here.
  • QA session: On Moodle you can sign up for a QA session on Tuesday, 12:15-… (I'll post the location of the QA session here once I've booked a room for it).
  • Session 4: Converting E/R models into database schemes using SQL/DDL
    • The lecture will be held on Thursday, 13:15 - 15:00 in E:A – to prepare, I suggest you try to finish the example we began on Tuesday.
    • The slides I'll use for the lecture are here.
    • You can find some notes from the lecture here.

Course week 3 (Feb 3 - Feb 7)

You need to book a meeting with a TA, so you can hand in Lab 1 this week – see the top of this page for instructions on how to do it.

We'll see how we can use SQL databases from other languages (Python and Java), we'll also see how to create a REST service, which can be used to serve various front ends with data.

  • Session 5: Calling SQL from other languages and (Python and Java)
    • The lecture will be held on Monday, 13:15 - 15:00 in E:A – I'll talk about how we can use SQL from Java and Python.
    • There are no special preparations for this lecture, other than that it would be good to brush up on your Python skills, if you want to use Python for lab 3 and the project (see link above).
    • The slides I'll use for the lecture are here.
    • You can find some notes from the lecture here.
  • QA session: On Moodle you can sign up for a QA session on Tuesday, 12:15-… (I'll post the location of the QA session here once I've booked a room for it).
  • Session 6: Databases and REST API:s
    • The lecture will be held on Thursday, 13:15 - 15:00 in E:A – I'll talk about REST services, and how we can write database-backed REST services in Java and Python.
    • The slides I'll use for the lecture are here.
    • You can find some notes from the lecture here.
  • Lab 1: SQL queries

Course week 4 (Feb 10 - Feb 14)

You need to book a meeting with a TA, so you can hand in Lab 1 this week – during the week, I'll publish a link to a website where you can do it.

This week it's time to get a better understanding of fundamental concepts such as dependencies and redundancies, and learn ways to categorize tables, and transform (normalize) them into a better shape.

  • Session 7: Normalization
    • To prepare you for this session, I suggest you try to answer a couple of questions.
    • The lecture will be held on Monday, 13:15 - 15:00 in E:A – I'll talk about functional dependencies and normalization.
    • The slides I'll use for the lecture are here.
    • You can find some notes from the lecture here.
  • QA session: On Moodle you can sign up for a QA session on Tuesday, 12:15-… (I'll post the location of the QA session here once I've booked a room for it).
  • Session 8: More normalization
    • The lecture will be held on Thursday, 13:15 - 15:00 in E:B. I'll talk more about normalization.
    • The slides I'll use for the lecture are here, and the notebook is here.
    • You can find some notes from the lecture here.
  • Lab 2: Database modeling

Course week 5 (Feb 17 - Feb 21)

You need to book a meeting with a TA, so you can hand in Lab 3 this week (see the top of this page).

This week we'll talk about how to keep the integrity of a database (to reduce the risk that it gets corrupted), and also talk about ways databases are used, and see some alternatives to SQL databases.

  • Session 9: Transactions and triggers.
    • The lecture will be held on Monday, 13:15 - 15:00 in E:A – I'll talk about transactions and triggers.
    • The slides I'll use for the lecture are here.
    • You can find some notes from the lecture here.
  • QA session: On Moodle you can sign up for a QA session on Wednesday, 12:15-… (I'll post the location of the QA session here once I've booked a room for it).
  • Session 10: Introduction to the project, indices, the query planner, NoSQL, scaling, SQL injections.
    • The lecture will be held on Thursday, 13:15 - 15:00 in E:A – I'll give a short introduction to the project, talk about indices, the query planner, NoSQL, and scaling, and then talk a little bit about SQL injection.
    • You can download the slides and the notebook I'll use.
    • You can find some notes from the lecture here.
  • Lab 3: Implementing a REST API

Course week 6 (Feb 24 - Feb 28)

  • Session 11: Graph databases.
    • On Monday, 13:15-15:00, Neo4j will give a lecture on Graph Databases in general, and Neo4j specifically.
  • Project design meeting. We'll have two alternative slots for the design meeting, we'll discuss what times to book during lecture 8 (Thursday in week 4) – your group must participate in one of the meetings.
  • QA session: On Moodle you can sign up for a QA session on Tuesday, 12:15-… (I'll post the location of the QA session here once I've booked a room for it).

Course week 7 (Mar 3 - Mar 7)

Nothing new, we'll just review the course material, and look at problems from previous exams.

  • Session 12: About the exam.
    • On Monday, 13:15-15:00 in E:A, we'll solve problems from last year's March exam

Course week 8 (Mar 10 - Mar 14)

In week 8 we can have QA sessions on Tuesday and Wednesday, 09:15 - 11:00. There I'll answer your questions, and, if there is time for it, solve problems from previous exams – you can sign up for the sessions in Moodle (I need to know how many we'll be, so I can book a properly sized room).

En kommentar om mail till LU

Normalt skickar vi veckan före kursstart ut ett mail med information till alla som anmält sig till kursen, men det fungerar dessvärre inte längre.

I början av 2024 stängde nämligen Lunds universitet av de IMAP(s) och SMTP(s) servrar man tidigare hade, och tvingade istället på oss ett fruktansvärt dåligt webmail-GUI som kostar oss massor av extra tid, och dramatiskt ökar både risken att vi missar inkommande mail och att vi skickar mail till fel mottagare. Det gör oss dessutom mer och mer beroende av ett amerikanskt företag, vars verksamhet vi har väldigt lite koll på.

Tidigare år kunde jag skicka mail till samtliga grupper med hjälp av enkla Python-scripts, det går inte längre – när jag i en kurs i höstas skulle skicka mail till projektgrupperna var jag tvungen att klicka 17 gånger för var och en av de drygt 50 grupperna, och minst ett av mailen kom aldrig fram (jag missade förmodligen något av de 850 musklick som jag behövde göra).

Det nya klick-baserade mail-systemet gör det dessutom väldigt mycket svårare att hålla koll på mail-kedjor – tidigare kunde jag läsa och skriva mina mail inifrån Emacs, och då var det enkelt att automatisera hantering/arkivering av mail-kedjor för projektgrupperna, och enkelt att hålla koll på vilka mail som hade besvarats. Idag måste jag klicka runt i ett GUI som kanske skulle fungera för någon som vill skicka mail till sina närmaste kollegor, men som absolut inte fungerar för att hantera studenter i en kurs med nära 200 deltagare i 50 grupper. Dessutom varnar det nya systemet inte om era mail inte kommer fram (ni får inget meddelande om ni skulle använda en felaktig adress), och periodvis kan jag inte komma åt min inkorg.

Jag beklagar verkligen de problem som det nya mail-systemet riskerar att medföra (missade mail, felskickade mail och fördröjd återkoppling) – institutionen har bett att få tillbaka IMAP och SMTP, men ingenting har hänt hittills.

Det är dessutom inte ovanligt att studenter skickar mail till mig med frågor om innehållet i EDAF75. Jag skulle verkligen önska att jag hade möjlighet att svara på individuella mail-frågor om kursinnehållet, men att skriva mail-svar tar mycket längre tid än att svara på frågor direkt (i synnerhet med det nya mailsystemet) – dessutom är mail en väldigt dålig kanal att förklara på, både för att det är lätt att missförstå nyanser i frågor, och för att det är omöjligt att se om ni förstår svaret, och vilken del av svaret som kanske behöver förtydligas.

Så en kurs med ca 200 kursdeltagare per lärare skalar väldigt vädigt dåligt för mail-undervisning – om jag bara lägger 10-15 minuter på att läsa en mailfråga och skriva ett svar, så skulle det ändå bli mer än en arbetsvecka för en fråga per student.

Du är hjärtligt välkommen med frågor antingen till våra frågestunder (se Moodle), eller i samband med föreläsningar (både i pausen och efteråt).