Fall Semester 2002

Let's approach the relational data model as a basis for data definition and data manipulation.

The Relational Data Model

In this model data is represented as tables and only as tables.

We use the entity-relationship model to design a relational database. This model does an adequate, albeit imperfect, job of modeling real-world situations, such as business enterprises or the records kept by schools, hospitals, governments, and so on, where database systems are likely to be used. However, if we view the structures defined in the entity-relationship model as conceptual schemes, we shall not be grossly deceived so this is a good starting point for your design.

We will have the following recipe:

1. first list the entities, with attributes
2. then list the relationships

Make a table for each relationship, in which

• the columns are keys in the tables for the entities that the relationship is connecting.

Here's an example.

Suppose we have a list of tennis tournaments:

• Indianapolis RCA (August) for $US115,000
• Cincinnati Masters (September) for $US400,000
• French Open (May) for $US588,664
• Wimbledon (July) for $US600,000
• Australian Open (January) for $US462,887

• Patrick Rafter (Australia)
• Pete Sampras (USA)
• Gustavo Kuerten (Brazil)
• Goran Ivanisevic (Croatia)
• Andre Agassi (USA)
• Tim Henman (England)

• Kuerten won the Cincinnati Masters
• Rafter won the French Open
• Kuerten won the Australian Open
• Ivanisevic won the Wimbledon
• Rafter won Indianapolis

Each table has two columns.

If a column is in color it is a key, or part of a key.

Now create the tables and populate the database.

Then create three queries:

• list the average prize
• list the average of prizes won, grouped by players
• list the players that have their averages above the average prize

Design a database in which you could store information about the following information.

• Ali defeats Foreman in Kinshasa, Zaire
• Frazier defeats Ali in New York, NY
• Ali defeats Frazier in Manilla, the Philippines.

Next, let's talk about: Executing queries in relational databases.

We start (as we did) by defining the following operations on sets.

1. SET UNION.

2. INTERSECTION OF SETS.

3. CARTESIAN PRODUCT.

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.

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.

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]