This module explains what is the difference between files and databases. It lists advantages of using databases and discusses about the components of a database system.
Lecture notes (pdf) Reading material: Elmasri & Navathe: Chapter 1, pages 3-21
This module discussed about how to define data. Various concept frameworks have been developed for this purpose. Most important is the relational model of data.
Lecture notes (pdf) Review questions (questions will open a new window, close the window when you are ready with the question)
Deliverable task (send your answer by e-mail to Tiina.Pelkonen@cs.helsinki.fi ) by 28th of January at 17.00
SolutionReading material: Elmasri & Navathe: Chapters 7.1 and 7.2 (pages 196-208)
This module introduces how to work with relations.
Lecture notes (pdf) E-Lerning material:
Review questions (there will be more)
- Union 1
- Union 2
- Union 3
- Union 4
- Difference 1
- Difference 2
- Difference 3
- Difference 4
- Product 1
- Product 2
- Product 3
- Product 4
- Intersect 1
- Intersect 2
- Intersect 3
- Intersect 4
- Projection 1
- Projection 2
- Projection 3
- Selection 1
- Selection 2
- Selection 3
- Join 1
- Join 2
- Join 3
- Join 4
- Join 5
- Building queries 1
- Building queries 2
Reading material: Elmasri & Navathe: Chapters 7.4.1- 7.4.6, 7.5.3 (pages 211-224, 228-234)
This module introduces the SQL database language. It outlines in detail how a database is defined in SQL.
Review questions(questions will open a new window, close the window when you are ready with the question)
Deliverable task (send your answer by e-mail to Tiina.Pelkonen@cs.helsinki.fi by the 5th of February at 8.00
SolutionReading material: Elmasri & Navathe: Chapters 7.4.1- 7.4.6, 7.5.3 (pages 211-224, 228-234)
This module introduces the SQL query language. It explains how to make queries to retrieve detailed data stored in the database. You may use SQL-Trainer to practice queries and to execute the examples of the course material. You get credits by doing the exercises before the deadline.
E-learning materialSQL-Trainer practicing
Tasks K1-K4 (Solution), K5-K9 (Solution), K10-16 (Solution), deadline on 14.2.2003 at 12.00
Reading material: Elmasri & Navathe: Chapter 8.2 and 8.3.1-8.3.4 (pages 251-268)
This module explain how to make summary queries in SQL. You may use SQL-Trainer to practice queries and to execute the examples of the material. You get credits by doing the exercises before the deadline.
Lecture notes Note: Some examples of the e-learning material use right and left outer joins as defined in the SQL standard. This syntax does not however work in Oracle. Thus, you cannot execute these queries in SQL-Trainer. Oracle has a syntax of its own for outer joins. In this syntax the symbol (+) after the column name in the where condition indicates that this column may have a null value. Thus the example:
select Name, CustomerId, count(orderID)
from Ordered right outer join Customer on
Ordered.Customer=Customer.CustomerID
group by Name, CustomerID
would be expressed in Oracle as
select Name, CustomerId, count(orderID)
from Ordered, Customer where
Ordered.Customer (+) =Customer.CustomerID
group by Name, CustomerID
If you execute this query in Trainer, you may also see that group by does not always sort the records in the same way as order by would
SQL-Trainer practicing
Tasks: Y1-Y7 (Solution), Y8-Y16 (Solution) deadline on 21.2.2003 at 12.00
Reading material: Elmasri & Navathe: Chapter 8.3.5 (pages 269-274)
This local pre-TUeLIP module explains how to make modifications on the contents of the database in SQL. You may use SQL-Trainer to practice operations. You get credits by doing the exercises before the deadline.
Lecture notes
E-learning materialSQL-Trainer practicing
Reading material: Elmasri&Navathe: Chapter 8.4 (pages 273-278)
This module discussed about SQL-views, a means to define derived tables. There are no exercises in this module.
E-learning material Reading material: Elmasri&Navathe: Chapter 8.5 (pages 278-282)
This module outlines how to use a relational database in applications and how to program database applications..Elmasri&Navathe: luku 27.1 (873-880)
Lecture notes
E-learning materialReading material: Elmasri&Navathe: Chapter 727.1 (pages 873-880)
This module presents the principles of how to design databases.
Lecture notes Review questions
Deliverable task (send your answer by e-mail to Tiina.Pelkonencs.helsinki.fi ) by 3rd of March at 17.00 (solution) Reading material: Elmasri&Navathe: Chapters 14.1, 14.2.1,14.2.2, 14.3.1, 14.5 (pages 465-473, 476-479, 483-485,493-495).
This module outlines how to use a relational database in web applications and how to program database applications..Elmasri&Navathe: luku 27.1 (873-880)
Slide copies Reading material: Elmasri&Navathe: Chapter 727.1 (pages 873-880)
Harri Laine 25.02.2003
.