Database Concepts - Data Warehouse Systems: Design and Implementation - page 46

2.14 Describe the different operations of the relational algebra. Elaborate
on the difference between the several types of joins. How can a join be
expressed in terms of other operations of the relational algebra?
2.15 What is SQL? What are the sublanguages of SQL?
2.16 What is the general structure of SQL queries? How can the semantics
of an SQL query be expressed with the relational algebra?
2.17 Discuss the differences between the relational algebra and SQL. Why is
relational algebra an operational language, whereas SQL is a declarative
language?
2.18 Explain what duplicates are in SQL and how they are handled.
2.19 Describe the general structure of SQL queries with aggregation and
sorting. State the basic aggregation operations provided by SQL.
2.20 What are subqueries in SQL? Give an example of a correlated subquery.
2.21 What are CTEs in SQL? What are they needed for?
2.22 What is the objective of physical database design? Explain some factors
that can be used to measure the performance of database applications
and the trade-offs that have to be resolved.
2.23 Explain different types of file organization. Discuss their respective
advantages and disadvantages.
2.24 What is an index? Why are indexes needed? Explain the various types
of indexes.
2.25 What is clustering? What is it used for?

2.9 Exercises

2.1 A French horse race fan wants to set up a database to analyze the
performance of the horses as well as the betting payoffs.
A racetrack is described by a name (e.g., Hippodrome de Chantilly),
a location (e.g., Chantilly, Oise, France), an owner, a manager, a date
opened, and a description. A racetrack hosts a series of horse races.
A horse race has a name (e.g., Prix Jean Prat), a category (i.e., Group
1, 2, or 3), a race type (e.g., thoroughbred flat racing), a distance (in
meters), a track type (e.g., turf right-handed), qualification conditions
(e.g., 3-year-old excluding geldings), and the first year it took place.
A meeting is held on a certain date and a racetrack and is composed
of one or several races. For a meeting, the following information is kept:
weather (e.g., sunny, stormy), temperature, wind speed (in km per hour),
and wind direction (N, S, E, W, NE, etc.).
Each race of a meeting is given a number and a departure time and
has a number of horses participating in it. The application must keep
track of the purse distribution, that is, how the amount of prize money
is distributed among the top places (e.g., first place:

e 228,000; second

place: e 88,000, etc.), and the time of the fastest horse.