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 handouts (pdf) Reading material (two choices):
Elmasri & Navathe, 4th ed.: Chapters 1-2, pages 3-47
Ramakrishnan & Gehrke: Chapter 1 (pages 3-24)
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 handouts (pdf) Review questions (questions will open a new window, close the window when you are ready with the question)
Reading material:
Davor Cormic: Entity Relationship Modeling with UML
Two choises:
Elmasri & Navathe, 4th ed.: Chapters 3-4 (pages 49-115) and 5 (pages 125-144),
Ramakrishnan & Gehrke: Chapter 2,3.1-3.2 (pages 25-56)
This module introduces how to work with relations.
Lecture handouts (pdf)
Revision handouts (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 (two choises):
Elmasri & Navathe 4th. ed: Chapters 6.1-6.4 (pages 149-172)
Ramakrishnan & Gehrke: Chapter 4.1-4.2 (pages 100-115)
This module presents the principles of how to design databases.
Lecture handouts Review questions
Reading material (two choises):
Elmasri&Navathe 4th ed.: Chapters 7, 10 (pages 191-205, 293-328)
Ramakrishnan & Gehrke: Chapter 3.5, 19.1-19.2, 19.4 (pages 74-85,605-611,614-617) .
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)
Reading material (two choises):
Elmasri & Navathe 4th ed.: Chapters 8.1-8.2 (pages 207-216)
Ramakrishnan & Gehrke: Chapter 3.1 (pages 57-66)
This module introduces the SQL query language. It explains how to make queries to retrieve detailed data stored in the database.
E-learning materialSQL-Trainer tasks will be published here. Reading material (two choises):
Elmasri & Navathe 4th ed.: Chapter 8.4-8.5 (pages 218-244)
Ramakrishnan & Gehrke: Chapter 5.1-5.4,5.6 (pages 130-150, 162-165)
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 handouts 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 tasks will be published here. Reading material: Elmasri & Navathe 4th ed.: Chapter 8.5 and 8.7 (pages 229-244)
Ramakrishnan & Gehrke: Chapter 5.5 (pages 151-161)
This 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 handouts
E-learning materialSQL-Trainer tasks will be published here. Reading material :
Elmasri&Navathe 4th. ed: Chapter 8.6 (pages 245-248)
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 4th ed.: Chapter 9.2 (pages 257-260)
Ramakrishnan & Gehrke: Chapter 3.6 (pages 86-90)
XML-material will be added here soon.
Harri Laine 14.09.2006/font>