Database Management Systems

Table of content

General information

 

Announcements 


Weekly class schedule


 

Exams

 

Exam rules


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) – updated 02-12-2016 at 13:06
  11. Introduction to Big Data (2 slides per page , 6 slides per page) – updated 02-12-2016 at 13:07
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

Material on prerequisites available at the following links


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  Draft Solution
Exercise 4 (Athletes, Members) 2 slides per page 6 slides per page  Draft Solution
Exercise 5 (Actors) Actors  Draft solution

 

Triggers

Exercise  Text Draft solution
Exercise 1 (Athlete ranking, Boat rental) 2 slides per page 6 slides per page DraftSolAthleteRankingDraftSolBoatRental
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  Draft solution
Data warehouse design (Italian household) exercise  Remark: Write all queries from (a) to (f) DraftSolutionItalianHousehold
Data warehouse design (SearchingYourHouse) exercise DraftSolutioSearchingYourHouse

Exercises from written exams

AA 2015-2016

Exam Draft solution
Exam (23-02-2016)
Exam (2016-01-27)

AA 2011-2012

Exam Draft solution
Exam (2012-02-06)  optimizer and dwtrigger
Exam (2012-02-28) optimizer and dw
Exam (2012-06-21) optimizer and dwtrigger
Exam (2012-09-07) optimizer and dw

AA 2010-2011

Exam Draft solution
Exam (2011-02-07)  optimizer, trigger, dw
Exam (2011-02-22)
Exam (2011-07-08)  optimizer, trigger
Exam (2011-09-21)  optimizer, trigger

 


Other exercises


Practices

Lab 1: The Oracle Optimizer

Lab 2: Triggers

 

Lab 3:Extended SQL

 

Lab 4: Pentaho: cubes, reports, and dashboard

Lab 5: Data mining – Rapid Miner  

 


Homework to be delivered

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

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

 

Homework Material Deadline Deliveries of Homework 
Homework #1: Query Optimization Homework 1 (pdf) to be delivered by Monday, November 7th, 2016 at 11.59 PM (UTC/GMT+1) DeliveriesHW#1
Homework #2: Triggers Homework 2 (pdf) Useful scripts. to be delivered by Monday, November 28rd, 2016 at 11.59 PM (UTC/GMT+1) DeliveriesHW#2
Homework #3: Data warehouse Homework 3 (pdf) to be delivered by Friday, December 23, 2016 at 11.59 PM (UTC/GMT+1)  DeliveriesHW#3
Homework #4: Data Mining Homework 4 (pdf) Dataset (breast.xls) to be delivered by Thursday February 2, 2017 at 11.59 PM (UTC/GMT+1) DeliveriesHW#4

 

 


Installing Oracle Express Edition

To download and install Oracle Express Edition

To import the database in Oracle  on your personal Computer:

  1. Download the following archive empdb2013.zip
  2. Extract the database file empdb.dump
  3. 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
  4. 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
  5. *NEW* 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)
  6. Execute the batch file or the shell script updated with the correct password



 Parent page 

 Menu 

 © 2017 - DataBase and Data Mining Group