| DATABASES-1 LECTURE [IP-DB1L] [2017.feb] |
|
Lectures: WED 10:15-11:45 ELTE South Building 2-315 PC4 lab
Office hours: MON 12:00-13:00; WED 12:00-13:00 South B. 2-507
Lecture Course Material/weeks:
i. DATABASE QUERIES (Rel.algebra, SQL DML, Datalog, Recursion)
| #L1. Feb.15. | #L2. Feb.22. | #L3. Mar.01. | #L4. Mar.08. |
--- Mar.15. (Wed) Hungarian National Holiday
| #L5. Mar.22. | #L6. Mar.29. | #L7. Apr.05. |
--- 2017. Apr.12 (Wed) - Apr.18. (Tue) Spring Holiday
ii. DATA MODELS, SQL DDL | #L8. Apr.19. | #L9. Apr.26. |
DATABASE DESIGN | #10. May.03. | #11. May.10. | #12. May.17. |
Databases-1 Exams: WED 10:15-11:45 Room: South B. 0-826
(1) 24 May | (2) 07 June | (3) 21 June | (4) 05 July (only Re-take)
Exam prerequisites: Computer lab practice [Oracle SQL, PL/SQL]
Writing Test: Topic of the Writing Test and [A Sample Writing Test]
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)
Course Material (Lecture)
i. DATABASE QUERIES (Rel.algebra, SQL DML, Datalog, Recursion)
week#1. Feb.15.
> Relational Model, Relational Algebra
> Lect_01_RelAlgebra.pdf --pp.1-43 (Chapter 2.1-2.4)
week#2. Feb.22.
> Exercises for Relational Algebra Queries
for Practice: Exercises1/Fruits [+create table]
[help //solution// Rel.Alg.]
for Lecture: Exercises2/Products [+create products]
[help //solution// Rel.Alg. //+later: SQL]
to be continued (week#4)
week#3. Mar.01.
> SQL Query: SELECT statement [+create table]
> Lect_02_SQL_Select.pdf --pp.1-36 (Chapter 6.1-6.2)
next week to be continued
week#4. Mar.08.
> SQL Query: SELECT statement (Join expressions, Subqueries)
> Lect_02_SQL_Select.pdf --pp.37-50 (Chapter 6.3)
> Exercises for Rel.Algebra (Chapter 2.4) and SQL SELECT
Exercises2/Products [+create table products]
[help //solution// Rel.Alg., SQL SELECT]
--- Extra points exercises (non-compulsury homework)
>> Rel.alg.op. and Exercises 2.4.8, 2.4.9, 2.4.10 (Ch.2.4)
--- 2017. Mar.15. (Wed) Hungarian National Holiday ---
week#5. Mar.22.
> SQL Query: SELECT statement (Grouping, Aggregation)
> Lect_02_SQL_Select.pdf --pp.51-65 (Chapter 6.4)
> Extended Relational Algebra (Rel.op. on Bags, Grouping)
> Lect_03_SQL_ExtRelAlg.pdf --pp.1-27 (Chapter 5.1-5.2)
week#6. Mar.29.
> Extended Relational Algebra (Outerjoins)
> Lect_03_SQL_ExtRelAlg.pdf --pp.28-29 (Chapter 5.2)
> SQL Modification: SQL DML
> Lect_04_SQL_Modification.pdf --pp.1-17 (Chapter 6.5)
> Datalog and Recursion
> Lect_05_Datalog_Recursion.pdf (Chapter 5.3-5.4)
next week to be continued
week#7. Apr.05.
> Datalog and Recursion
> Lect_05_Datalog_Recursion.pdf (Chapter 11.2)
> [Flights_Excercises]
--- 2017. Apr.12 (Wed) - Apr.18. (Tue) Spring Holiday ---
ii. DATA MODELS, SQL DDL, DATABASE DESIGN THEORY
week#8. Apr.19.
> Entity Relationship Model
> Lect_06_ERmodel.pdf (Chapter 4.1-4.6.)
week#9. Apr.26.
> SQL Tables, Constraints, Views, Triggers
> Lect_07_SQL_DDL.pdf (Chapter 7, and 8.)
week#10. May.03.
> Relational Design Theory, Part-1: FD's, Rules
> Lect_08_FDs.pdf (Chapter 3.1-3.2)
next week to be continued
week#11. May.10.
> Relational Design Theory, Part-2: FD's, BCNF
> Lect_08_FDs.pdf (Chapter 3.2-3.3)
week#12. May.17.
> Relational Design Theory, Part-3: LJ Chase, DP 3NF
> Lect_09_NFs.pdf (Chapter 3.3-3.5)
Databases-1 Exams: WED 10:15-11:45 Room: South B. 0-826
(1) 24 May | (2) 07 June | (3) 21 June | (4) 05 July (only Re-take)
Exam prerequisites: Computer lab practice [Oracle SQL, PL/SQL]
Topic of the Writing Test and [A Sample Writing Test]
Databases-1 Exam (Writing Test)
--- Algebraic and Logical Query Languages (3)
#Q1.) 2.4 Relational Algebra
#Q2.) 5.1-5.2 Extended Relational Algebra
#Q3.) 5.3-5.4 A Logic for Relations: Datalog
--- The Database Manipulation Lang, Queries in SQL (5)
#Q4.) 6.1 Simple Queries in SQL
#Q5.) 6.2 Queries Involving More Than One Relation
#Q6.) 6.3 Subqueries
#Q7.) 6.4 Full-Relation Operations
#Q8.) 6.5 Database Modifications
--- The Data Definition Lang, Constraints, PL/SQL (5)
#Q9.) 7.1-7.2 Keys and Foreign Keys, Constraints
#Q10.) 8.1-8.2 Views
#Q11.) 9.3 Cursors, Queries in PSM (Oracle: PL/SQL)
#Q12.) 9.4 PSM Stored Procedures(Oracle: PL/SQL)
#Q13.) 10.2 Recursion
--- Data Models (4)
#Q14.) 1.2 The Worlds of Database Management Systems
#Q15.) 2.1-2.2 Basics of the Data Models and Relational Model
#Q16.) 4.1-4.4 The Entity/Relationship Model
#Q17.) 4.5-4.6 From E/R Diagrams to Relations
--- Relational Design Theory (4)
#Q18.) 3.1-3.2. Functional Dependencies, X+ Closure Algorithm
#Q19.) 3.3. Design Theory (Boyce-Codd Normal Form Decomp.)
#Q20.) 3.4. Decomposition (The Chase Test for Lossless Join)
#Q21.) 3.5. Third Normal Form (3NF Decomp, Depend.Preserv)
(Later) BSc Final Exam
--- (i) From this semester topic: Databases 1 - query languages
Relational model, entity-relationship model, transformation from ER to relational model.
Relational algebra, SQL, Datalog. Recursion in query languages. Procedural elements
in query languages (variables, control structures, subprograms, cursors, exceptions).
--- (ii) From the next semester topic: Databases 2 - query execution
Index structures, sparse and dense index, B+ tree, bitmap index, dynamic hashing.
One pass and two pass algorithms, sort based and hash based algorithms.
Join methods, cost of operations, query execution plans.