Database_Management_for

Database Management, if1c1i05, 2013/2014 (Hajas, Csilla)

Lecture in English: Thursday 10:00-13:00 Room: 2-503
Consultation hours: Wednesday 12:00-14:00 Room: 2-503
Website of the course: http://people.inf.elte.hu/sila/edu13sep/PDB.html
>> Book
>> Course Material
>> Tools
>> Doc
>> Homework
>> Exams

Book
Database Systems: The Complete Book (2nd ed)
by Garcia-Molina, Jeff Ullman and Jennifer Widom

- Table of Contents
- Sample chapters in PDF: Chapter1 and Chapter2
- Jeff Ullman's Slides (Stanford University CS145)
- Hector Garcia-Molina' s Slides (Stanford CS245)

Course Material -- with password (the password is sent by email)

week	Lecture Slides (the Complete Book)	Oracle Lectures, Examples
#1. Sep.12	C2.2. Introduction Database Models C2.4. Relational Algebra, Chapter2	E1.Rel.Alg, create like table E2.Rel.Alg.(homework)
#2. Sep.19	--- from Rel.Algebra to SQL Select C6.1-Ch6.2. SQL: Select	E2.RA>SQL, create products E3.SQL (hwork), create dept/c
#3. Sep.26	C6.3. SQL: Subqueries	L1select, L2where, L3functions DocSQL: Func's, createHRsyn
#4. Oct.03	C5.1-5.2. Extended Relational Algebra C6.4. SQL: Grouping	L4aggr(+ExtAlg), L5from, L7set createHRsyn, createHRtables
#5. Oct.10	C6.5. SQL DML: Modifications C7.1-7.4. DDL: Constr., 7.5.Triggers	L6subq, S6csubq, L8dml, L9ddl E5.SQL(hwork), create dept/no
#6. Oct.17	C9.3-9.4. SQL/PSM Oracle: PL/SQL	OracleDoc: PLSQL: 4.statments 6.ref.types,cursors, 8.subprogr.
#7. Oct.24	C5.3-5.4. Logical Query Lang: Datalog C10.2 Recursion, C10.6-10.7.OLAP	S7: Oracle Hierarchical Retrieval OracleDoc: SH (a star schema)
---	--- Autumn Holiday, no classes	--- Autumn Holiday
#8. Nov.07	C.1 Overview, C6.6. Transactions, C8. Views, Indexes	L10view&index, L11dbaViews Archit.ppt, E7. Objects (hwork)
#9. Nov.14	C14. Index Structures, B+Trees C14. Index Structures, Hashing	Ex8. Index_Structures (forExam) OracleDoc: Concepts
#10. Nov.21	C15. Query Execution C16. Query Optimization	Tuning.ppt OracleDoc: Tuning Guide
#11. Nov.28	C3.1-3.2. Functional Dependencies, C3.3-3.5. Rel.Design, C3.6. MVD's	- >> Homework Assignments
#12. Dec.05	C4.1-4.6. Entity/Relationship Model, C4.7-4.8. UML	- >> Homework Assignments
#13. Dec.12	C11.1-11.4. XML, C12.1.-12.3. XPath, XQuery	- >> Homework Assignments
Exams	Dec.16. Monday 13:00-14:00, 2-520	-- >> Exams

Tools

Oracle sqldeveloper Oracle Technology Network: otn.oracle.com
> Windows Installation Notes -->> Download(windows32/64.zip)
> RPM for Linux Installation Notes -->> Download(linux.rpm)
> Connections
ABLINUX
Hostname: ablinux.inf.elte.hu
Port: 1521
Service name: ablinux.inf.elte.hu
TOMX
Hostname: tomx.inf.elte.hu
Port: 1521
Service name: ora11g

Oracle Documentation

Orace11gR2 Doc.Library
Example Tables: HR Schemas, createHRsynonyms, createHRtables

# Week 1-5 (Oracle SQL SELECT)
> Sample Schemas - Schema Diagram: HR
> SQL Language Reference >> 5 Functions >> see Examples:
-- Single-Row Functions
Character Functions e.g. SUBSTR, RPAD, ..., INSTR, ...
Datetime Functions e.g. MONTHS_BETWEEN, ...
Conversion Functions e.g. TO_DATE, ...
   Null-related Functions e.g. NVL, ...
   -- Aggregate Functions e.g. COUNT, MAX, MIN, SUM, ...

# Week 6 (Oracle PL/SQL)
> OracleDoc: PLSQL >> see PL/SQL examples, e.g. 6-43
-- before:
   drop table employees;
   create table employees as select * from hr.employees;
   select employee_id, job_id, salary from employees
   where job_id = 'SA_REP';
   set serveroutput on;
-- copy example 6-43 (select for update and update cursors)
-- after:
   select employee_id, job_id, salary from employees
   where job_id = 'SA_REP';

# Week 7 (Oracle SQL)
> SQL Lang.Ref. >> 9 SQL Queries >> Hierarchical Queries

# Week 8-10 (Oracle Indexes, Query Optimization)
> Sample Schemas - Schema Diagram: SH
> Concepts
> Reference
> Performance Tuning Guide >> 11 The Query Optimizer - 19 Using Optimizer Hints
    EXPLAIN PLAN FOR SELECT /*+ hints */ ...
SELECT plan_table_output FROM table(dbms_xplan.display);

# Week 11-13 (no Oracle practice /only Lectures)

Homework Assignments

1.) Relational algebra
Out: week 1 (Sep.12) E2.Rel.Alg.
Deadline: part-i. week 2 (Sep.18), part_ii. week 5 (Oct.10)

2.) SQL SELECT
Out: week 2 (Sep.18) E3.SQL
Example Tables: create dept, emp tables (constraints)
Deadline: week 8 (Nov.07)

3.) SQL DML, PL/SQL
Out: week 5 (Oct.10) E5.SQL
Example Tables: create dept, emp tables (no constr)
Deadline: week 8 (Nov.07)

4.) Indexes, Query Optimization
Out: week 8 (Nov.07) E7.Objects
Deadline: week 13 (Dec.12)

Exams

Final Exam: Dec.16 Monday 13:00-14:30 Room: 2-520 MI /AI/ lab.
40% Homework - >> Homework Assignments (Deadline: Dec.12)
30% Part I. Week 1-7. Query Languages (15 topics -> 5 -> 1)
30% Part II. Week 8-13. Optimization + Design (15 -> 5 -> 1)
From these 15+15 topics choose any 5+5 topics to prepare for the exam.

Part I.	Week 1-7. Query Languages (15 topics -> 5 -> 1)
Q1. Q2. Q3. Q4. Q5.	Algebraic and Logical Query Languages (5) 2.4 Relational Algebra 5.1 Relational Operations on Bags 5.2 Extended Operators of Relational Algebra 5.3 A Logic for Relations: Datalog 5.4 Relational Algebra and Datalog
Q6. Q7. Q8. Q9. Q10.	The Database Language: Queries in SQL (5) 6.1 Simple Queries in SQL 6.2 Queries Involving More Than One Relation 6.3 Subqueries 6.4 Full-Relation Operations 6.5 Database Modifications
Q11. Q12. Q13. Q14. Q15.	Constraints, Procedural Languages/SQL (5) 7.1-7.2 Keys and Foreign Keys, Constraints 8.1-8.2 Views 9.3-9.4 Cursors, PSM (Oracle: PL/SQL) 10.2 Recursion in SQL (Oracle: Hierarchical) 10.6-10.7 On-Line Analytic Processing (OLAP)

Part II.	Week 8-13. Optimization + Design (15 topics -> 5 -> 1)
T1. T2. T3. T4.	Index Structures (4) 8.3-8.4 Indexes in SQL 14.1 Index-Structures, Sparse and Dense Index 14.2 B+ Trees 14.3 Hash Tables, Dynamic Hashing
T5. T6. T7. T8.	Query Optimization (4) 15.1-15.2 Physical-Query-Plan Operators 15.3 Join Methods, Nested-Loop Joins 15.4 Sort-Based Two-Pass Algorithms 15.5 Hashed-Based Two-Pass Algorithms
T9. T10. T11. T12. T13. T14. T15.	Data Models and Design Theory (7) 1.2 The Worlds of Database Management Systems 2.1-2.2 Basics of the Data Models and Relational Model 3.1-3.2. Functional Dependencies, X+ Closure Algorithm 3.3-3.4. Design Theory (BCNF Decomp., Lossless Join) 3.5. Third Normal Form (3NF Decomp, Depend.Preserv) 4.1-4.4 The Entity/Relationship Model 4.5-4.6 From E/R Diagrams to Relations