Database Management Systems


This page has hierarchy - Parent page: Teaching

Table of content

General information

 

Announcements 

  • [11-12-2017] The fourth laboratory will be held on Wednesday, December 13th, 2017 (Team A) and  on Thursday, December 14th, 2017 (Team B). The laboratory is on data warehousing. The text of the practice is available in Section “Practices”
  • [7-12-2017] Homework 3 (deadline 22/12/17) has been published in Section “Homeworks to be delivered”

Weekly class schedule

11/12/2017 – 16/12/2017

Type Day of the week Time Classroom Topics
Lecture Monday 13:00-16:00 4T Association rules (13:00-14:30); Beyond relational databases (14:30 -16:00) (2 slides per page6 slides per page)
Practice Wednesday 14:30-16:00 LABINF (map) TEAM A – Practice #4: data warehousing
Lecture Thursday 11:30-13:00 2D Association rules
Practice Thursday 13:00-14:30 LABINF (map) TEAM B – Practice #4: data warehousing
Lecture Friday 14:30-16:00 4D
Lecture Friday 16:00-17:30 4D Teachers available to answer student doubts and questions on Homework 3

LAB SCHEDULE. To fit LabInf capacity students must attend all the course labs according to the following schedule (students are sorted in alphabetic order by surname):

  • TEAM A (FROM ABSTREITER TO KRAYEM) on Wednesday from 2.30 pm to 4.00 pm
  • TEAM B (FROM LA ROCCA TO ZOU ) on Thursday from 1.00 pm to 2.30pm

The compliance to the schedule above is necessary to guarantee the feasibility of the lab.

LAB ACCOUNT. Please make sure to have an account on the LABINF PCs before the beginning of the lab practice (the accounts used to log in to the PCs of the other LAIBs are *not* valid). To register an account at LABINF, please visit the Labinf  website for further information.

 


Exams

Exam rules

  • Exam rules Academic Year 2017-2018 (pdf)

Materials

Part I

Database System Technology

  1. Introduction to DBMS (2 slides per page6 slides per page)
  2. Buffer Manager (2 slides per page,  6 slides per page)
  3. Physical access to data (2 slides per page6 slides per page)
  4. Query optimization  (2 slides per page6 slides per page)
  5. Physical design  (2 slides per page6 slides per page)
  6. Concurrency Control (2 slides per page6 slides per page)
  7. Reliability Management (2 slides per page6 slides per page)
  8. Triggers  (2 slides per page6 slides per page)
  9. Distributed databases  (2 slides per page6 slides per page)
  10. Beyond relational databases (2 slides per page6 slides per page)
  11. Introduction to Big Data (2 slides per page , 6 slides per page)

 

Oracle
  1. Oracle Optimizer
    1. Baseline version (2 slides per page6 slides per page)
    2. Extended version with examples (2 slides per page,6 slides per page)
  2. Hints (2 slides per page6 slides per page)
  3. Documentation

 

Part II

Data warehousing

Oracle
  1. Extended SQL (2 slides per page, 6 slides per page)

Data mining

Prerequisites

  • Relational model
  • Relational Algebra
  • SQL language

Material on prerequisites available at the following links

  • “Databases” course (english): link
  • “Basi di dati” course (italian): link

Exercises

Optimizer

Exercise  Text Draft solution
Exercise 1 (Fine) 2 slides per page 6 slides per page  Draft Solution
Exercise 2 (Students, Projects) 2 slides per page 6 slides per page Draft Solution
Exercise 3 (Discs) Text  
Exercise 4 (Athletes, Members) 2 slides per page 6 slides per page  

Triggers

Exercise  Text Draft solution
Exercise 1 (Athlete ranking, Boat rental) 2 slides per page 6 slides per page DraftSolAthleteRanking DraftSolBoatRental
Exercise 2 (Greenhouse) 2 slides per page 6 slides per page DraftSolutionGreenhouse
Exercise 3 (Student grant) 2 slides per page 6 slides per page DraftSolutionStudentGrant

Data warehouse

Exercise  Text Draft solution
Extended SQL (Customer )  text DraftSolutionCustomer 
Extended SQL (Rental ) text  
Data warehouse design (Italian household) exercise  
Data warehouse design (SearchingYourHouse) exercise

 


Exercises from written exams


Practices

Practices schedule

Topic Team A Team B Lab Assistance
Practice #1: Oracle optimizer  25/10/2017  26/10/2017 assistant lecturer + scholarship holder
Additional optional lab for students who have not completed Practice #1 27/10/2017 3/11/2017 scholarship holder
Practice #2: Oracle triggers 8/11/2017 9/11/2017 assistant lecturer + scholarship holder
Lab for Homework #2 on Oracle triggers 15/11/2017 16/11/2017 scholarship holder
Additional optional lab for students who have not completed Homework #2 22/11/2017 23/11/2017 unassisted lab
Practice #3: Extended SQL in Oracle 29/11/2017 30/11/2017 assistant lecturer + scholarship holder
Additional optional lab for students who have not completed Practice #3 6/12/2017 7/12/2017 unassisted lab
Practice #4: Data warehousing 13/12/2017 14/12/2017 assistant lecturer + scholarship holder
Additional optional lab for students who have not completed Practice #4 20/12/2017 21/12/2017 scholarship holder
Practice #5: Data mining with Rapidminer 10/1/2018 11/1/2018 assistant lecturer + scholarship holder
Lab for Homework #4 on data mining with Rapidminer 17/1/2018 18/1/2018 scholarship holder

 

Lab 1: The Oracle Optimizer

  • Text (pdf)
  • Useful scripts
  • Description of the execution plan operations
  • Installing Oracle 11g Express Edition at home
    • To download and install Oracle Express Edition
    • To import the database in Oracle  on your personal Computer
      • Download the following archive empdb2013.zip
      • Extract the database file empdb.dump
      • Download the batch file Oracle-DB-import.bat (for Windows) or the shell script Oracle-DB-import.sh (for Linux) and save it in the same directory in which the empdb.dump file is stored
      • Modify the batch file or the shell script by replacing the keyword password by the password string defined during the Oracle XE installation and (only for the shell script) check the Oracle directory path
      • Please check that tables  EMP e DEPT are not already present. Otherwise, please remove then with the DROP command  (during user creation / workspace Application Express, Oracle automatically creates EMP and DEPT example tables)
      • Execute the batch file or the shell script updated with the correct password

 

Lab 2: Triggers

 

Lab 3:Extended SQL

  • Text (pdf)
  • Data warehouse tables in csv format (zip)
  • FOR STUDENTS WHO WANT TO PRACTICE AT HOME WITH EXTENDED SQL: to import tables from the Oracle XE Web interface select Home>Utilities>Data Load/Unload>Load>Load Text Data->)
  • Draft solution of queries 1-5 and materialized view (pdf)

 

Lab 4: Data-warehouse analytics and reporting with Google Data Studio

 


Homework to be delivered

To obtain the points associated with the homeworks, students have to observe the following terms:

  • Complete all the points of the exercises in the homework text.
  • Prepare one file in PDF, DOC or ODT format with the solution of the homework.
  • Name the file as: HomeworkN_Surname_Name_StudentId.XXXwhere
    • StudentId, Surname and Name should be substituted with student information
    • the N character following Homework should be substituted with the number of the submitted homework
    • the filename extension XXX depends on the file type chosen for the submission (PDF, DOC or ODT).
    • DOCX format is not supported.
    • Since uploaded files are automatically processed, naming the file with a wrong name implies the cancellation of the related homework submission.
  • Load the file on the didactic 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.
    • The upload date show on the didactic 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 requested. Insert the same name of the file according to the rules described above.
  • Only the students without the access to the course page on the didactic portal can submit the homework before the deadline by sending an email to the assistant lecturer.
  • Discuss the homework with a positive evaluation on the fixed date (announcement will be published).

Homework discussion: Students attending the written exam must bring the following items:

  • for Homeworks #1 – #4:
    • a hard-copy of the submitted reports

Homework text

Homework Material Deadline Homework deliveries
Homework #1: Query Optimization HW text (pdf) to be delivered by Monday, November 6th, 2017 at 11.59 PM (UTC/GMT+1)
Homework #2: Triggers  HW text (pdf) – Useful scripts.  to be delivered by Monday, November 27th, 2017 at 11.59 PM (UTC/GMT+1)
Homework #3: Data warehouse  HW text (pdf)   to be delivered by Friday, December 22, 2017 at 11.59 PM (UTC/GMT+1)
Homework #4: Data Mining