| DATABASES-1 LECTURE [IP-12fAB1E] [2016.feb] |
|
Lecture: Tuesday 10:15-11:45 ELTE South Building 0-221 (class)
Practice: [here: link] Wednesday 10:15-11:45 2-315 PC4 (comp.lab)
Lecture Course Material/weeks:
i. DATABASE QUERIES (SQL, Relational algebra and Datalog)
| #L1. Feb.16. | #L2. Feb.23. | #L3. Mar.01. | #L4. Mar.08. |
-- 2016. Mar.14-15. Hungarian National Holiday --
| #L5. Mar.22. |
-- 2016. Mar.23-29. Spring vacation (Easter Holiday) --
ii. DATABASE DESIGN THEORY
| #L6. Apr.05. | #L7. Apr.12. | #L8. Apr.19. | #L9. Apr.26. |
-- 2016. May.03. Neumann Day (Faculty Holiday) --
iii. DATABASE PROGRAMMING (SQL/PSM, Oracle PL/SQL)
see in the Practice [link]
| #L10. May.10. |
Exam: Topic of the Writing Test [of Databases-1 Lecture]
Time: Wednesday 10:00-12:00 Room: South B. 0-821 Bolyai
- Please, in the Neptun choose one of this Wednesdays 10h:
| (1) 25th May | (2) 01st June | (3) 08th June | (4) 15th June |
| +Re-take Exam: 22nd June, but not 10h -> from Wed 12h |
Exam prerequisites: Computer lab practice [see DB1P.html]
- Oracle SQL Query and Modification (DML)
- Oracle SQL DDL
- Oracle PL/SQL
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. QUERIES (SQL, Relational algebra and Datalog)
week#1. Feb.16.
> Relational Model, Relational Algebra
> Lect_01_RelAlgebra.pdf (Chapter 2.1-2.4)
week#2. Feb.23.
> Queries: Relational Algebra and SQL SELECT
>> Exercises1/Fruits [+create table] [+//solution//]
>> Exercises2/Products [+create products] [+//solution//]
> SQL Query: SELECT statement
> Lect_02_SQL_Select.pdf (Chapter 6.1-6.2)
week#3. Mar.01.
> SQL Query: SELECT statement
> Lect_02_SQL_Select.pdf (Chapter 6.3-6.4)
week#4. Mar.08.
> Extended Relational Algebra
> Lect_03_SQL_ExtRelAlg.pdf (Chapter 5.1-5.2)
> SQL Modification: SQL DML
> Lect_04_SQL_Modification.pdf(Chapter 6.5)
week#5. Mar.22.
> Datalog
> Lect_05_Datalog.pdf (Chapter 5.3-5.4)
- ii. DATABASE DESIGN THEORY
week#6. Apr.05.
> Entity Relationship Model
> Lect_06_ERmodel.pdf (Chapter 4.1-4.6.)
week#7. Apr.12.
> SQL Tables, Constraints, Views, Triggers
> Lect_07_SQL_DDL.pdf (Chapter 7, and 8.)
week#8. Apr.19.
> Relational Design Theory, Part-1
> Lect_08_FDs.pdf(Chapter 3.1-3.2)
next week to be continued
week#9. Apr.26.
> Relational Design Theory, , Part-2
> Lect_09_NFs.pdf(Chapter 3.3-3.5)
- i. (back to) QUERIES (Summary) and Recursion
week#10. May.10.
> Recursion
> Lect_10_Recursion.pdf (Chapter 11.2)
> [Flights_Excercises]
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.