http://www.inf.unibz.it/~calvanese/teaching/20-21-idb/
Free University of Bozen-Bolzano
Faculty of Computer Science
Bachelor in Computer Science
Home page of the course
Introduction to Databases
A.Y. 2020/2021
News
Course
Description
Objectives. Students attending this course will have acquired the
techniques and methods to address problems of database design, and to make use
of the basic functionalities (definition, update, and querying of the database)
of database management systems in the context of development and deployment of
information systems. In addition, students will be able to develop applications
that programmatically interact with a database management system. The course
explicitly refers to relational databases and to the corresponding database
management systems based on the SQL language. However, the taught methods and
principles are of a more general nature, and can be applied also in those
contexts where data models and database systems different from relational ones
are adopted.
Prerequisites. Students should have a solid mathematical foundation and
be familiar with the basic programming concepts, and with basic data structures
(arrays, lists, trees) and algorithms that operate on them (visits, search,
updates). These prerequisites are covered in the following courses: Analysis,
Introduction to Programming, and Programming Project.
Attendance. Attendance is not compulsory, but non-attending students
have to contact the lecturer at the start of the course or before starting
their studies and the project work to agree on the modalities of the
independent study.
Teaching material
- Introduction to Databases (Course Slides).
Diego Calvanese, Giuseppe De Giacomo, Maurizio Lenzerini. 2020.
The slides will be made available during the course and can be downloaded
in MS Teams from the Class
Materials folder in the "2020/21 - Introduction to Databases" MS Team.
- Esercises solved in class.
The exercises are assigned for the lab hours, and the solutions are
made available in the week after the lab in the same
Class
Materials folder in the "2020/21 - Introduction to Databases" MS Team.
- Database Management Systems (3rd edition). Raghu Ramakrishnan,
Johannes Gehrke. McGraw-Hill, 2005.
Available at University Library Bozen: 13-Textbook Collection (ST 271).
The textbook is suggested, but not strictly necessary.
For Students Who Have to Take the 8CFU Exam
For students enrolled in the old BSc in Computer Science and Engineering, who
still have to take the 8 CFU Exam for the Database Systems course, the
written exam will include also questions that cover the additional topics for 2
credit points of "Physical Data Storage" (covered in Chapter 6 of
Database System Concepts) and of "Transaction Management",
"Recovery", and "Multi-User Synchronization"
(covered in Chapters 8-10 of Database System Concepts).
The reference textbook for Chapters 6 and 8-10 is:
Database System Concepts (4th edition).
Silberschatz, Korth, Sudarshan. McGraw Hill, 2002.
Available at University Library Bozen: 13-General Collection (ST 271)
The material for Chapters 6 and 8-10 is covered at the proper level of detail
necessary for the preparation of the exam also in the slides
that Prof. Sven Helmer used when teaching the Database Systems
course until Academic Year 2017/2018. These slides are not available anymore
on the Web, but students who would like to receive an electronic copy can
contact me via email.
General Information
-
Office hours
-
Tutor: Davide Lanti
-
Schedule: The course is taught online via MS Teams in the 1st semester:
from 6 October 2020 to 20 January 2021.
-
Lectures:
- Tuesday 8:00-10:00
- Wednesday 10:00-12:00
- Labs: Tuesday 14:00-16:00
See also the
on-line
timetable page for changes.
-
All lectures and labs will be video-recorded. All information concerning
the personal data processing can be found under the following
link: https://guide.unibz.it/en/covid-19/. If a students
wants to speak in videocall, they need to first type the following
sentence in the chat: "Read the privacy notice, Agreed to the
recording". In any case, the participation indicates the consent to be
included in the registration of the lesson.
-
Additional teaching material
Information about the Exam
- Exam Dates and Modality
- Winter session: Tuesday, 9/2/2020, 10:00-12:30,
online written exam via Zoom (students must log into the Zoom session
already at 9:00)
- The project discussion takes place on Monday, 8/2/2020 starting
at 9:00.
- The deadline for handing in the project is Friday, 5/2/2020 at
23:59.
- Summer session: Tuesday, 6/7/2020, 10:00-12:30,
online written exam via Zoom (students must log into the Zoom session
already at 9:30)
- Autumn session: TBD
- Exam Mark
-
The final mark will be based on:
- a project [30% of mark]
(see below for more information about the
project), and
- a final written exam [70% of
mark].
The final mark is computed as a weighted average of the exam mark
(70%) and the project mark (30%).
-
To be admitted to the written exam (2), the student must have discussed
the project (1), and the project must have been evaluated positively.
In other words, without having passed the project, the written
exam cannot be taken.
-
To pass the exam, the student has to pass also the written exam (2), in
addition to the project (1).
-
At the written exam, which lasts at least 2 hours, the student will
have to carry out the design of a small database, following a given
specification. Moreover they will have to write down some SQL queries,
and possibly answer in written form some questions about the topics
covered in the course.
-
In case of a positive mark, the project mark will count for all 3
regular exam sessions of the Academic Year (i.e., if the student fails
or does not take the written exam, they keep the project mark and only
need to retake the final exam).
-
Requirements for the Online Written Exam via Zoom
To be able to take the online written exam, (ideally) each student needs
the following:
-
The student-card with a recognizable picture (or an ID with
photo).
-
A room where the student can work for the whole duration of the exam
without interruption and without anyone present or entering the
room.
-
A laptop or desktop PC with working microphone and
webcam, and with a working installation of the zoom.us
application. Notice that the browser version of zoom.us is not
sufficient.
If for some reason the student does not have a laptop or desktop PC
with working microphone and webcam available at the date of the exam,
they can also use a mobile phone. However, in that case, there should
be no laptop or other PC on the desk where the student takes the exam.
-
An Internet connection that can sustain a continuous video
connection for the whole duration of the exam.
-
A desk that (ideally) faces a wall, and where the student can
set up their laptop so that the webcam can view the desk where they
work, the head, and the surroundings, notably the door of the room.
For that, the camera should be some distance away from where the
student is writing the exam.
-
Sufficient white paper and a pen, to write the exam. Use only
white paper, and not checkered or striped paper, since
text written on that kind of paper is much less readable when
scanned.
-
A mobile phone with camera or a scanner to scan the
written exam solution and send it via email. Ideally, when a scanner
is not available, the student should use an app like Office Lens
(available for free on iPhone and possibly also Android) that converts
the picture of the exam solution into a pdf file (as opposed to
a jpeg file).
-
Exam Procedure for the Online Written Exam via Zoom
-
The student must install in advance the zoom.us
application on the laptop used for the exam. This is
mandatory, since only the zoom.us application allows to simultaneously
share the screen and activate the webcam, and both are needed during
the exam. Without the possibility of simultanously sharing the
screen and activating the webcam, the student is not allowed to take the
exam. Also, the student needs to create in advance a (free)
account on Zoom using the full name and surname, so that when the
student is connected the real name is shown.
-
Using their laptop, the student connects via Zoom one hour
before the time scheduled for the start of the exam, using the
link provided in MS Teams.
Notice that the student should connect one hour in advance, since we
need some margin for the setup. In this way the proper exam can
start at the scheduled time at latest (possibly also earlier).
Also students who have to take only the "Database Systems" exam should
connect via Zoom one hour before the time scheduled for the start of
the exam, even if they have to take only the 2CFU part of the exam.
After the 6CFU part (which is the same as for the students who take the
"Introduction to Databases" exam) and after all exams for that part
have been handed in, there will be a short break (5-10 minutes), and
then the 2CFU part of the exam can start.
-
I check online the student's identity. For that the student shows
themself and their student card (or ID) in the webcam of the laptop.
With the webcam the student should show also the room in which they are
working, and the working place.
-
The student sets up the webcam as described above and leaves video and
audio on (background noise should be avoided, e.g., by keeping the
windows in the room closed). The student also shares the full screen
of the laptop (the Zoom connection is set up in such a way that
multiple participants can share their screen, and I can switch at any
moment between the shared screens).
-
I share with the students via Zoom a link to the text of the exam,
which is a pdf file that can be displayed on the monitor. If the
student has a printer available in the room, they might also print out
the exam sheet. The student should not display anything else on their
laptop apart from the file with the text of the exam. The time starts
from the moment the student receives the link.
-
The student writes the exam solution on paper (exactly as they would do
in class). While doing so, they should not consult any material or
interact in any way (physically, virtually, via chat, etc.) with
anybody. If there are questions, they can be asked online.
Importantly: every sheet of paper that is used must be numbered with
clearly visible consecutive numbers in the upper right corner.
Everything that is written (also rough copies) should be numbered.
What should not be evaluated should just be crossed through.
-
At the end of the exam, when the time is over, the student stops
writing, lays down the written solutions as a bundle of sheets of paper
in the proper order, and does not touch them anymore, until it is their
turn to send them.
-
Each student communicates the number of used exam solution sheets, by
writing in the exam chat:
Name Surname:
number-of-exam-solution-sheets
E.g.,: Paolo Rossi: 8
-
Each student either scans or takes with their mobile phone pictures of
all solution sheets, in the order in which they are numbered. The
student has to scan/take the pictures of all exam sheets, and their
number must obviously be equal to the number written in the chat at
point 7. Ideally, all scanned pages/pictures should be combined in a
single pdf file. As soon as the student is finished, they send an
email with the pdf file as attachment to
calvanese@inf.unibz.it
with the
subject: Introduction to
Databases exam - Name Surname, StudentID
The student
must use their unibz email address to send the email, not another
account (such as gmail).
-
I check that I have correctly received the email, and that the attached
pdf file contains the correct number of pages, and confirm the correct
receipt by replying via email.
-
When the student has received the email confirmation, the exam is
finished and they can disconnect from the Zoom session.
- Rules for the Project
-
The project can be developed either alone, or in a group of two.
-
When the project is developed in a group of two, the numbers given
below as upper bounds should be considered as lower bounds.
-
The project is based on the design of a simple database,
following a specification of the requirements that the
students provide on their own, and that must be discussed and
agreed upon with the lecturer/TA. As a starting point for the
discussion, the student should ideally provide an initial written
description of the application domain (the specification of the
requirements) and a draft of the ER schema that is as complete as
possible (with respect to the specification). By "draft" we intend the
schema with the entities and relationships and just the main
attributes, typically the identifiers. The diagram can be drawn by
hand and does not need to be fancy, but should be readable. Typically,
in the initial discussion and also in the initial desing phases, the
diagram gets revised, so initially it is not worth spending too much
time on the layout or graphical appearance. Alternatively, for the
discussion, the students can also present just the draft of the ER
diagram, and describe the application domain in the meeting. However,
trying to write up the specification of the application domain is a
good exercise to better understand which information the database
should store. And this specification is in any case required as part
of the project documentation.
-
Students are strongly encouraged to confirm with the lecturer/TA also
the final version of the ER diagram, before they start
working on the subsequent phases of the design of their database. They
are also encouraged to use the office hours to clarify any doubts they
might have about the various design phases. This helps to avoid that
serious problems with their design get discovered only when the project
is discussed as part of the exam. This could cause the project to be
evaluated negatively, which would prevent the student from taking the
written exam.
-
Roughly speaking, the specification of the requirements should satisfy
the following criteria:
-
It should be based on a domain containing between 6 and 10 main
conceptual entities (i.e., without counting sub-entities that
appear in ISAs or generalizations).
-
There should be some structure in the set of entities, i.e., the ER
schema should in addition contain a few ISAs and/or
generalizations.
-
There should be sufficient structure in the relationships, which
usually means that the representation of the ER schema as a graph
(where the nodes are given by the entities and relationships, and
the edges are given by the participation of entities in
relationships) should contain some cycles.
-
The schema should contain cardinality constraints on the
participation of entities to relationships that are different from
the default (0,n).
-
The schema should contain at least one optional attribute and at
least one multi-valued attributes.
-
There should be some external constraints, that cannot be
represented in the ER model.
-
The specification should include an indication about the volumes
for the various entities and relationships (pay attention to the
coherence between the volumes and the cardinality constraints of
the ER schema).
-
The specification should include a workload of the most common
queries and operations (between 5 and 10) that are of interest in
the modeled domain, with an indication of their frequency.
-
The student should carry out the design of the database according to
the requirements, following the methodology presented in the course,
and consisting of the following phases:
-
Conceptual design, producing the following documents:
-
structured and organized requirements;
-
glossary of terms (typically optional, but required for this
project);
-
diagram of the conceptual schema;
-
data dictionary of the conceptual schema (listing the entities,
relationships, and external constraints - the data dictionary
for the attributes can be omitted);
-
table of volumes and table of operations according to the
foreseen application load.
-
Restructuring of the conceptual schema, producing the
restructured conceptual schema (diagram and data dictionary), the
corresponding table of volumes, and the access tables to assess the
cost of the various operations.
-
Direct translation to the relational model, producing the
relational schema with external constraints and the specification
of the application load in terms of the relational schema.
-
Restructuring of the relational schema taking into account
the application load, producing the restructured relational schema
with external constraints and the specification of the application
load in terms of the restructured relational schema. The reasons
for the restructuring steps on the relational schema should be
stated explicitly, by referring to the operations and the
corresponding access tables.
-
Specification of the database in SQL, by defining relations
with suitable constraints (capturing keys, foreign keys, and tuple
constraints) and stored procedures with triggers to implement
additional constraints (inclusions, external constraints).
-
The student should also develop a simple application (typically in
Java) that implements some interaction with the database via a textual
or graphical user interface.
The application should:
-
allow the user to answer the queries that are part of the workload
(typically by instantiating some parameters with values provided as
input);
-
allow the user to perform some basic insertions and/or updates of
(some of) the data in the database, asking the user for input;
-
catch the exceptions that might be generated by JDBC due to
possible mistakes in the interaction with the database, so that the
application does not terminate prematurely with an error.
-
The documentation produced for the project should include:
-
a single pdf file containing a textual description of the
requirements (of roughly a page) and the documents for phases 1-4
above.
Remember to include at the beginning of the pdf file a
header or a title page with your name, student number, the title of
your project, and the date.
Remember also to number the pages
of the pdf file (the page numbers are important for the evaluation,
so that we can refer to them for comments);
-
one or more SQL files containing the specification of the database
(phase 5 above);
-
one or more files (typically in Java) containing the application
that interacts with the database. The functionality of the
application will be reviewed when the students discuss their
project.
-
All documents have to be bundled in a single ZIP file and have to be
uploaded to MS Teams within the "Introduction to Databases Project"
assignment.
-
The deadline for submitting the project is, unless announced otherwise,
at 23:59 two days before the day set for the discussion of the projects
(see below).
-
Rules for the Project Discussion
-
The discussion of the IDB projects typically takes place one or two
days before the date set for the written exam, and will be announced on
MS Teams before each exam session.
-
One or two days before the project discussion, we will post in MS Teams
and on the course web page a schedule that assigns a specific time slot
to the discussion of each project. Specifically, the discussion of the
projects will be scheduled in the order in which they have been
submitted to MS Teams (considering the last modification time), where
projects submitted first will be discussed first. The discussion of
each project will last between 15 and 30 minutes.
-
In the case of an online discussion via MS Teams:
-
Students should be ready to share their screen on MS Teams in order
to show the different parts of the project documentation:
- the requirements,
- the conceptual schema (diagram and constraints),
- the restructured conceptual schema (diagram and constraints),
- the relational schema (with constraints) resulting from the
direct translation,
- the restuctured relational schema (with constraints).
-
In order to discuss the various phases of the development of the
database design, and illustrate how the output of one phase is used
to generate the output of the next phase, it is convenient if the
two outputs can be visualized at the same time on the screen.
Therefore, for the purpose of the discussion, each part should be
saved on a separate pdf file, so that two different parts can be
visualized on the screen at the same time (e.g., the restructured
ER schema and the relational schema resulting from the direct
translation).
-
The laptop used to connect to MS Teams should contain a functioning
version of the application they have developed for the project, so
that we can verify its funtionality during the project discussion.
-
In the case of a face-to-face discussion:
-
Students should bring to the discussion a printout of (at least)
the following parts of the project documentation:
- the requirements,
- the conceptual schema (diagram and constraints),
- the restructured conceptual schema (diagram and constraints),
- the relational schema (with constraints) resulting from the
direct translation,
- the restuctured relational schema (with constraints).
The remaining documentation can be consulted from the pdf file, and
does not need to be printed out.
-
It is preferred if the sheets of paper with the documentation above
are not bound but are kept loose, since this facilitates the
assessment of the correctness of the various design phases. To
avoid confusion, please don't forget to number the pages.
-
Students should also bring a laptop with a functioning version of
the application they have developed for the project, so that we can
verify its funtionality during the project discussion.
teaching page of Diego Calvanese
Last modified:
Tuesday, 6-Jul-2021 0:07:21 CEST