Wednesday 23 February

The objective of the lecture is to briefly introduce the notion of a database system and then to give a practical overview of the process of designing a database schema.
The aim is to show how to end up with a database model starting from the row data. In this process the participants will learn what is a conceptual design of a database (entity-relationship model), how to transfer the conceptual design to a logical design (relational model), get acquainted with the Data Definition Language as part of SQL, look at some common pitfalls when designing a database schema.

1.      Introducing database concepts

2.      Conceptual Design – Entity-Relationship Model

3.      Logical Design

4.      Relational Database Model

5.      Introducing SQL (Structured Query Language)

6.      Implementing the relation model through the DDL part of SQL

7.     Effective design best practices and common pitfalls

Wednesday 23 February

At the end of this lecture it is expected that the participants have heard about the main features available for interacting with a database. The base of the session is to look in detail at all the possibilities of database queries, with particular attention to advanced SELECT forms. Most of the session will be based on SQL92 standard and a small part on Oracle features.

1. DML basics: insert/update/delete

2. SELECT basics

• '||', column pseudonyms, NVL

• union, union all, intersect, minus

• restricting: where, in, like, distinct, and/or, not, is [not] null, any, all

• sorting: order by, asc/desc

• aggregation: count, sum, max, avg, group by, having

• joins: equijoins, outerjoins

• charater manipulation functions: contat, length, lower, upper, ltrim, substr, ...

• numeric functions: abs, ceil, floor, mod, power, round, sign, sqrt, trunc, ...

• date functions: to_date, last_day, next_day, NLS_DATE_FORMAT, round, sysdate, trunc

• convertion functions: to_char, to_date, to_number

• other functions: decode, greatest, least, nvl, uid, user, vsize

3. Advanced SELECT

• self joins

• subqueries, inline views, rownum

• correlated subqueries

4. Indexes b-tree

5. Transactions

6. Multi-dimensional aggregation
 

Wednesday 23 February

This lecture will give an overview of what a database offers to improve the performance of very big databases (index-organized tables, partitioning, etc.) and certain features for protecting the data when working in a multi-user environment in a database. It will also show how to put more logic into the database layer and make the database ‘smarter’ by capturing database events through triggers or adding programming logic to the execution of SQL commands (PL/SQL functions). The lecture is heavily based on the Oracle implementation of all these features.
 

1. Creating a table from a table

2. Creating an index-organized tables

3. Other indexes (bitmap, function based, reverse, multi-column)

4. Using partitioned tables

• range, hash, composite partitioning

• global, local indexes

5. By what authority – users and privileges

6. Views

7. Materialized views

8. Accessing Remote Data - synonyms, db links

9. Introduction to PL/SQL

10. Triggers

11. PL/SQL procedures, functions and packages
     

Wednesday 23 February

The aim of this lecture is to give you an idea of what database performance tuning is from the point of view of an application developer and not that of a DataBase Administrator (DBA ). Why do we need to tune at all? How can we make tuning experts unnecessary? Application tuning is the main topic of the lecture and its substantial part is devoted to SQL statement tuning. But the larger picture is also there! Common pitfalls are listed and you will see real life examples and problems.

Come to this lecture if you want to learn:

• what tuning is, why it's perceived as magic and how to tame it,

• when to start tuning a database application,

• what techniques and tools to use,

• what is an SQL optimizer and how to make it work better,

• how to read an execution plan,

• what types of indexes to use and why,

• why timing and logging is so important,

• why avoid using optimizer hints

All these issues are presented based on an Oracle database. But they are also relevant to other database systems!

Wednesday 23 February

A hidden knowledge can be stored in databases. How to discover it? How can we search for an answer, if we do not know a question? Data mining can help. The objective of the lecture is to introduce basic methods of knowledge discovery in structured data, and also in an unstructured text.

1. What and why

• Data mining, knowledge discovery, data exploration

• Machine learning

• Statistics

2. Data mining as a process

•  CRISP-DM method

• Predictive and descriptive tasks

•  Concepts, instances, attributes

3. Models and algorithms

• Decision trees

• Classification rules

• Association rules

• k-nearest neighbors

• Cluster analysis

4. Text mining: How does Google News work

• Converting unstructured text to structured data

• Cluster analysis