CSCI A114 / INFO I111 Lecture Notes Four First semester 2001-2002

Today in class we will be going over labs

• 3 (due tomorrow) and
• 4 (to be started tomorrow).

• adding, changing, deleting records in a table
• searching and filtering
• adding new fields, change existing fields properties
• use update and delete queries
• create validation rules
• specify referential integrity, and use subdatasheets
• creating indexes

• The first report contains calculated fields, is based on a query.
• The second report contains grouping, with a total and sub-totals.
• The form is custom-designed and contains combo boxes.

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 entity, identify the key attributes.

   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
   Suppose we also have a list of tennis players:
   • Patrick Rafter (Australia)
   • Pete Sampras (USA)
   • Gustavo Kuerten (Brazil)
   • Goran Ivanisevic (Croatia)
   • Andre Agassi (USA)
   • Tim Henman (England)
   Let's design a database in which we can store the information that
   • Kuerten won the Cincinnati Masters
   • Rafter won the French Open
   • Kuerten won the Australian Open
   • Ivanisevic won the Wimbledon
   • Rafter won Indianapolis
   Here's the entity-relationship diagram.

   

   We will have three tables.

   Each table has two columns.

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

   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
   Now solve a similar problem, as follows.

   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.
   Identify the entities (and their attributes), the relationships (and their attributes) draw the entity relationship diagram, then create their tables and then populate the database.

   You're now ready for the next homework assignment, due Wednesday in lab.

   ---

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