CSCI A114 / INFO I111
Lecture Notes Five

First semester 2001-2002

Executing queries in relational databases.

We start by defining the following operations on sets.

1. SET UNION.

The union of two sets is the set of all elements that belong to either one or the other of the two sets. If an element belongs to both sets it also belongs to the union.

2. INTERSECTION OF SETS.

The intersection of two sets is the set of all elements that belong to both sets. For an element to appear in the intersection it must belong to both the first and the second set.

3. CARTESIAN PRODUCT.

The cartesian product is the set of all possible combinations of sets from the first and the second sets.

Note that, as the previous two (union and intersection), this has been defined as a binary operation. Union, intersection and cartesian products involving more than two sets (operands) are defined in the obvious way. For example here's such a definition for the cartesian product of n sets.

We'll work out some examples.

Then we return to designing a new database for a new application.

We will apply some of these definitions before too long.

Let's design a database for a gradebook.

A GRADEBOOK PROJECT

The initial scenario here is that as a teacher, you have grade-keeping responsibilities.

Here's a set of minimal requirements:

For each quiz or exam you record the scores.
At the end of the grading period, you calculate each student's total score.

Based on the discussion of last time here's a possible design.

Fields in red and blue are keys.

Let's define the tables, and populate them with some data.

Then we will be looking at how the following queries are executed.

SELECT * FROM STUDENT;

SELECT Name FROM STUDENT

SELECT Name, Gender, [Student ID] FROM STUDENT

SELECT * FROM TAKES WHERE Score > 95

SELECT COUNT(*) AS [Number of Students] FROM STUDENT;

SELECT COUNT(*) AS [Males] FROM STUDENT WHERE Gender = 'Male';

SELECT Gender, COUNT(*) AS [How Many] FROM STUDENT GROUP BY Gender;

SELECT * FROM TAKES WHERE Date = #9/9/01#;

SELECT   [Event ID], MIN(Score) AS Lowest, MAX(Score) AS Highest 
FROM     TAKES
GROUP BY [Event ID];

SELECT [Event ID], MIN(Score)              AS Lowest, 
                   MAX(Score)              AS Highest, 
                   MAX(Score) - MIN(Score) AS Range, 
                   SUM(Score)              AS Total, 
                   AVG(Score)              AS Average,
                   COUNT(Score)            AS Count
FROM TAKES
GROUP BY [Event ID];

SELECT * FROM EVENT, TAKES;

SELECT * FROM EVENT, TAKES WHERE EVENT.[Event ID] = TAKES.[Event ID];

SELECT * 
FROM   EVENT, TAKES, STUDENT;

SELECT      *
FROM        EVENT, TAKES, STUDENT
WHERE       STUDENT.[Student ID] = TAKES.[Student ID] 
       AND  TAKES.[Event ID] = EVENT.[Event ID];

SELECT      Name, Date, Score, Type
FROM        EVENT, TAKES, STUDENT
WHERE       STUDENT.[Student ID] = TAKES.[Student ID] 
       AND  TAKES.[Event ID] = EVENT.[Event ID];

SELECT AVG(Score) AS [Exam1 Average]
FROM TAKES
WHERE [Event ID] = 'EXAM1';

SELECT STUDENT.Name, TAKES.Score, Ex1Avg.[Exam Average]
FROM   STUDENT, TAKES, Ex1Avg
WHERE  STUDENT.[Student ID] = TAKES.[Student ID] 
   AND TAKES.[Event ID] = 'EXAM1' 
   AND Ex1Avg.[Exam1 Average] <= [Score]

Last updated: September 11, 2001 by Adrian German for A114/I111

CSCI A114 / INFO I111 Lecture Notes Five

CSCI A114 / INFO I111
Lecture Notes Five