Introduction To Databases

Introduction To Databases

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

TypeDay of the weekTimeClassroom
LectureMonday13:00-16:00Room R2
LectureTuesday8:30-11:30Room R3
LabFriday10:00-11:30Laib1
LabFriday17:30-19:00Laib1

Material

  • Introduction to the course (pdf)
  • Introduction to databases (pdf)
  • Relational data model (pdf)
  • Database design
    • Entity-Relation Model (pdf, pdf6x) [Updated 03/03/2023]
    • Time representation (pdf, pdf6x) [Updated 05/03/2023]
    • Logical Design (pdf, pdf6x) [Updated 10/03/2023]
    • Conceptual design example (pdf, pdf6x)
    • Logical design example (pdf, pdf6x)
    • Normalization (pdf, pdf6x)
  • 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
    • Web applications (pdf)
    • Streamlit (pdf)
    • Live coding (pdf)
  • Triggers
    • Triggers (pdf)


Classroom exercises

TopicTextSolution
DesignER tool presentationLink to presentation
Relational modelRelational model exercisesRelational model exercises commented [Updated 13/03/2023]
Conceptual designEntity relationship exercisesEntity 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 designExercises part I [Updated 13/03/2023]
Exercises part II [Updated 28/03/2023]
Solutions: ex1 (plant wholesaler)
ex5 (babyparking)
Relational algebraAlgebra part 1, Algebra part 2Solution 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]
TriggersRanking exercise
Scholarship exercise
Ranking draft solution
Scholarship draft solution


Lab practice

Groups are divided by the surname initials.

Student GroupDay of the weekTimeClassroom
Team A: A-KAMFriday10:00-11:30LAIB1
Team B: KAR-ZFriday17:30-19:00LAIB1
Lab dateTopicMaterial
24/03/2023Relational database designLab exercises (text)
Solution (pdf)
Introduction to the DesignER tool available at https://designerdb.io/

21/04/2023SQL part I Lab: textsolution database
Software : ORACLE APEX. Oracle Apex (online version) available at https://apex.oracle.com/
28/04/2023SQL part IILab: text solution database
05/05/2023SQL part IIILab: text solution ER diagram (designer)
12/05/2023SQL part IV (Triggers)Lab: text solution database
19/05/2023Streamlit part ILab: textdatabase
26/05/2023Streamlit part II (HW)Lab/homework: textdatabase
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

HOMEWORKAssignmentDue Date
Homework 1: Database DesignHW 1 Text13/4/2023
Homework 2: Relational AlgebraHW 2 Text10/05/2023
Homework 3: SQL HW 3 Text28/05/2023
Homework 4: StreamlitHW 4 TextDB25/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