Fall Semester 2003

Problem One: Premiere Relational Algebra.

1. Data is coming from only one table (Rep). We want to project two columns. No selection is performed. (Remember that selection acts like a filter, eliminating some of the rows. Projection is dropping columns).
2. Data is coming from one table, a specific row is selected, all columns are listed so no projection is performed. (Pp. 53-54 contains the theory relevant to these questions).
3. Data is coming from three tables now (Orders, Customer, Rep). These tables need to be joined. Then you need to use a projection so only the necessary columns are listed.
4. After that you select only those rows where the name of the rep is Kaiser.
5. A union is needed here (either-or). Data is coming from two tables. Join them and produce the two sets, then take the sets' union (one set is defined by the credit limit, the other by the date the order was placed on).
6. Same as above but the condition specifies that both the credit limit and the date be simultaneously satisfy the requirement. So an intersection of two sets (same sets) is needed.
7. Take the difference of the same sets we discussed above.

Use the data provided and QBE to practice.

Problem Two: Henry Books Case

1. Data comes from one table, a projection of two columns is needed.
2. One table, no selection, no projection, everything is extracted.
3. One table, needs a selection for New York, and a projection for the name.
4. Condition is the opposite of that in the previous query.
5. Project name from selection on number of employees.
6. Selection by type, project code and title.
7. Selection now uses a composite condition (a conjunction).
8. Same as above but the condition uses a disjunction (either-or vs "and").
9. Selection on price ("and") projection on three columns.
10. Essentially same as above.
11. A calculated column (see p. 39) is needed. No selection. Project two columns, and add a new, calculated one.
12. Project code and title of a selection by a set of types.
13. Sorting is the new aspect here (see p. 43).
14. Count them (aggregate functions, see p. 42).
15. Same as above (essentially).
16. Data comes from two tables, so join first, then project (using SELECT).
17. Join the publisher and book tables, then select by publisher, and project the title and price of the book.
18. Same as above but add to your selection the price requirement.
19. See make-table queries on page 51.
20. Page 49.
21. Page 50. In these last three questions you first have to think the query in normal terms, then use that basic idea in a specific type of query.

Hope this helps. We'll see you in lab. All of next week is dedicated to a review of Chapters 2 and 3 with exercises in class (lecture). For the labs you should start Project Four (on Wed) and Project Five (on Fri).