General Informations
- SSD: ING-INF/05
- ECTS: 8
- Official information sheet of the course
- Professor: Lia Morra
- Assistant lecturers: Davide Calandra, Daniele Apiletti
Announcements
- Announcements are posted on the Teaching Portal (Portale della Didattica)
- All discussions about the course can be opened on Piazza.
Timetable
| Type | Day of the week | Time | Classroom |
| Lecture | Monday | 13:00-16:00 | Room R2 |
| Lecture | Tuesday | 8:30-11:30 | Room R3 |
| Lab | Friday | 10:00-11:30 | Laib1 |
| Lab | Friday | 17:30-19:00 | Laib1 |
Material
- Introduction to the course (pdf)
- Introduction to databases (pdf)
- Relational data model (pdf)
- Database design
- Relational algebra (pdf)
- SQL language
- Fundamentals (pdf, pdf6x)
- Nested queries (pdf, pdf6x)
- Advanced queries (pdf, pdf6x) [Updated 18/04/2023]
- Set operators (pdf, pdf6x)
- Update commands (pdf, pdf6x)
- Managing tables (pdf)
- Advanced SQL (pdf): view, transactions, access control, physical design, index management
- SQL for applications
- Triggers
- Triggers (pdf)
Classroom exercises
| Topic | Text | Solution |
| DesignER tool presentation | Link to presentation | |
| Relational model | Relational model exercises | Relational model exercises commented [Updated 13/03/2023] |
| Conceptual design | Entity relationship exercises | Entity relationship exercises solution (pdf) [Updated 13/03/2023] Entity relationship exercises solutions (Designer) [Updated 13/03/2023] ER restructuring and translation (pdf) |
| Conceptual and logical design | Exercises part I [Updated 13/03/2023] Exercises part II [Updated 28/03/2023] | Solutions: ex1 (plant wholesaler) ex5 (babyparking) |
| Relational algebra | Algebra part 1, Algebra part 2 | Solution part 1, Solution part 2 |
| SQL | SQL exercises part I SQL exercises part II SQL exercises part III [Updated 26/05/2023] | SQL examples (with solutions) SQL exercises part I (solutions) SQL exercises part II (partial solutions) [Updated 26/05/2023] |
| Triggers | Ranking exercise Scholarship exercise | Ranking draft solution Scholarship draft solution |
Lab practice
Groups are divided by the surname initials.
| Student Group | Day of the week | Time | Classroom |
| Team A: A-KAM | Friday | 10:00-11:30 | LAIB1 |
| Team B: KAR-Z | Friday | 17:30-19:00 | LAIB1 |
| Lab date | Topic | Material |
| 24/03/2023 | Relational database design | Lab exercises (text) Solution (pdf) Introduction to the DesignER tool available at https://designerdb.io/ |
| 21/04/2023 | SQL part I | Lab: text – solution – database Software : ORACLE APEX. Oracle Apex (online version) available at https://apex.oracle.com/ |
| 28/04/2023 | SQL part II | Lab: text – solution – database |
| 05/05/2023 | SQL part III | Lab: text – solution – ER diagram (designer) |
| 12/05/2023 | SQL part IV (Triggers) | Lab: text – solution – database |
| 19/05/2023 | Streamlit part I | Lab: text – database |
| 26/05/2023 | Streamlit part II (HW) | Lab/homework: text – database |
Software and installation guides
- Installation guides, to work on the lab practices you can use oracle directly on your PC or online through Oracle APEX. We suggest you to use the online option, following the guide below:
Practical sessions schedule
The schedule of the Practical sessions may be subject to variation. In this case, students will receive timely communication during the lessons. Materials for each session will be published in the corresponding week.
Homework
| HOMEWORK | Assignment | Due Date |
| Homework 1: Database Design | HW 1 Text | 13/4/2023 |
| Homework 2: Relational Algebra | HW 2 Text | 10/05/2023 |
| Homework 3: SQL | HW 3 Text | 28/05/2023 |
| Homework 4: Streamlit | HW 4 Text – DB | 25/06/2023 |
Rules
To obtain the points associated with the home assignments, students must observe the following terms:
- Complete all the points of the exercises in the homework assignment.
- Prepare a file in PDF (unless otherwise noted) for the submission.
- Name the file as HomeworkN_Surname_Name_StudentId.pdf whereStudentId, Surname and Name should be replaced with student information
- N: the character following Homework should be replaced with the number of the submitted homework (e.g., ‘1’ for homework no. 1)
- Load the file on the Teaching Portal (Portale della didattica) in the section Work Submission (Elaborati) before the deadline. Multiple loadings for the same student and/or for the same homework are not allowed.
- Since uploaded files are automatically processed, naming the file with a wrong name implies the cancellation of the related homework submission.
- The upload date shown on the Teaching portal is considered for the evaluation.
- Since uploaded files are automatically processed, the upload after the deadline implies the cancellation of the related homework submission.
- During the upload procedure a description (“Descrizione”) field is required. Insert the name of the uploaded file, without extention (according to the rules described above).
- Only for students with no access to the course page on the didactic portal, the homework may be submitted before the deadline by sending an email to Davide Calandra, cc: Lia Morra
- Discuss the homework with a positive evaluation on the fixed date (announcement will be published).
Results
Past Exams
Additional past exams are available on the Exercise platform
- 2015-07-03 [Text]
- 2015-07-17 [Text][Draft solution]
- 2014-07-17 [Algebra and SQL][ER]
- 2014-07-03 [Text] [Draft solution]
- 2014-01-23 [Algebra and SQL] [Draft Solution], [ER ][Draft solution]
- 2013-05-24 [Text][Draft solution]
- Theory questions [Text]
- [NEW] 2022-06-21 [Text&Sol]