Home page of the course
Introduction to Databases

News

• 16/1/2020: The exercise assignment for Exercise 11 (exercises on the ER model) is available in OLE.
• 16/1/2020: The exercise assignment for Exercise 10 (preparation for the exam) is available in OLE.
• 16/1/2020: The exercise assignment for Exercise 9 (restructuring of the logical schema) is available in OLE.
• 16/1/2020: The solutions for Exercise 8 (direct translation to the relational model) are available in OLE.
• 10/1/2020: The exercise assignment for Exercise 8 (direct translation to the relational model) is available in OLE.
• 8/1/2020: The second draft of the lecture notes for Part 6 (logical database design) available in OLE is actually the final version, and the file has been renamed (although the content has not been modified).
• 7/1/2020: A second draft version of the lecture notes for Part 6 (logical database design) is available in OLE.

• Introduction to Databases (Course Slides). Diego Calvanese, Giuseppe De Giacomo, Maurizio Lenzerini. 2019.
  The slides will be made available during the course and can be downloaded from the course page in the OLE system.
• Esercises solved in class.
  The exercises will be assigned for the exercise hours, and the solutions will be made available in the following week on the course page in the OLE system.
• 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.

Database System Concepts (4th edition). Silberschatz, Korth, Sudarshan. McGraw Hill, 2002.
Available at University Library Bozen: 13-General Collection (ST 271)

• Office hours
• Teaching assistant: Davide Lanti
• Schedule: The course is taught in the 1st semester: from 1 October 2019 to 10 January 2020.
  • Lectures (Lecture Room C3.06):
    • Tuesday 8:00-10:00
    • Thursday 8:00-10:00
  • Exercises: Friday 14:00-16:00
    • BZ Rainerum Blue Room
  See also the on-line timetable page for changes.
  
• Additional teaching material
  • Register of the past and coming lectures
  • Past exams
• Exam dates
  • Winter session: TBD
  • Summer session: TBD
  • Autumn session: TBD
• Rules for the exam
  1. The final mark will be based on:
     1. a project [30% of mark], and
     2. 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%).
  2. 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.
  3. To pass the exam, the student has to pass also the written exam (2), in addition to the project (1).
  4. 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 (s)he will have to write down some SQL queries, and possibly answer in written form some questions about the topics covered in the course.
  5. 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, (s)he keeps the project mark and only needs to retake the final exam).
• Rules for the project
  1. The project can be developed either alone, or in a group of two.
  2. When the project is developed in a group of two, the numbers given below as upper bounds should be considered as lower bounds.
  3. 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 agreed upon with the lecturer/TA. Students are strongly encouraged to first discuss their specification with the lecturer before they start working on the design of their database, and then to confirm the specification after they have produced (a first version of) the ER schema. This first version can be drawn by hand and does not need to be fancy, but should be readable and as complete as possible (with respect to the specification).
  4. Roughly speaking, the specification of the requirements should satisfy the following criteria:
     1. 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).
     2. 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.
     3. 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.
     4. The schema should contain cardinality constraints on the participation of entities to relationships that are different from the default (0,n).
     5. The schema should contain some optional and/or multi-valued attributes.
     6. There should be some external constraints, that cannot be represented in the ER model.
     7. 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).
     8. 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.
  5. 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:
     1. Conceptual design, producing the following documents:
        1. structured and organized requirements;
        2. glossary of terms (typically optional, but required for this project);
        3. diagram of the conceptual schema;
        4. data dictionary of the conceptual schema (listing the entities, relationships, and external constraints - the data dictionary for the attributes can be omitted);
        5. table of volumes and table of operations according to the foreseen application load.
     2. 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.
     3. 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.
     4. 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.
     5. 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).
  6. 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.
  7. The documentation produced for the project should include:
     1. 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);
     2. one or more SQL files containing the specification of the database (phase 5 above);
     3. 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.
  8. All documents have to be bundled in a single ZIP file and have to be uploaded to OLE within the "Database Design Project" assignment.
  9. The deadline for submitting the project is, unless announced otherwise, at 23:55 two days before the day set for the discussion of the projects (see below).
• Rules for the project discussion
  1. 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 OLE before each exam session.
  2. One or two days before the project discussion, we will post in OLE 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 OLE (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.
  3. 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.
  4. 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.