Your name here

1

• Your name here
• The lecture notes are based on using Microsoft
  Access interactively as part of the lecture

2
Manipulating Database Content with Relational
Algebra and Microsoft Access

• How to manipulate information in relational
  databases
• Using relational algebra and Microsoft Access
• How to define simple one-table queries
• Projection queries
• Selection queries
• How to define queries that combine multiple
  tables
• Product queries
• Join queries
• Queries with multiple joins
• How to combine relational operations to create
  complex queries
• Defining complex queries with Microsoft Access
• How to define queries with set operators
• Union, intersection, difference
• How to create user interfaces in Access
• A video rental checkout form

3
Manipulating Information in Relational Databases

• A relational database table
• Consists of a set of rows (also called records)
• each row in a table has the same number and types
  of attributes.
• When you send a query to the database, it
• Finds the appropriate rows of information in the
  stored tables
• Performs the requested operations on the data
• Represents the results in a new temporary table
• Delivers the results table to the user
• Destroys the table when the user no longer needs
  it

4
Queries and Query Languages

• Queries can be represented in many ways
• Relational algebra (explained in Chapter 8)
• Query by example diagrams (as in Microsoft
  Access)
• SQL, the standard query language (explained in
  Chapter 9)
• 4 basic types of queries
• A projection operation produces a result table
  with
• Only some of the columns of its input table.
• A selection operation produces a result table
  with
• All of the columns of the input table
• Only those rows of its input table that satisfy
  some criteria.
• A join or product operation produces a result
  table by
• Combining the columns of two input tables.
• A set operation produces a result table by
• Combining rows from one or the other of its input
  tables
• This chapter focuses on manipulating relational
  tables with relational algebra and Microsoft
  Access

5
Projection Queries

• A projection query selects some of the columns of
  the input table
• project Customer onto (firstName, lastName)
• Relational algebra form
• ?firstName,lastName(Customer)
• Notice that the result table has fewer rows
• Duplicate rows have been removed

6
Projection in Access
Query wizard dialog
Source table
Select button (right arrow)
Selected fields
7
Selection Queries

• A selection query selects rows that match a
  selection criteria
• select from Customer where lastName Doe.
• Relational algebra form
• ?lastNameDoe(Customer)

8
Selection query in Access
All fields selected
Show button unselected
Selection criteria lastName Doe
9
More complex criteria

• The selection criterion can be any expression
• select from TimeCard where ssn 376-77-0099
  and date lt 01-mar-2002
• Shaded rows are result rows
• Relational algebra form
• ?ssn376-77-0099 and datelt01-mar-2002(TimeCard
  )

10
Complex criteria in Access
Selection criterion datelt01-mar-2002
Field selection list
11
Product Queries

• A product query produces a result table from 2
  inputs
• Combines the attributes from two different tables
  
• Produces a new table with more attributes than
  either of the original ones
• is product of Employee and TimeCard
• Relational algebra form
• Employee ? TimeCard
• Combines every row of one table with every row of
  other table
• We really want to combine an employee and a
  timecard for a single employee

Rows from first time card
Rows from second time card
12
Join queries

• A join query is a product with a restriction on
  the result rows
• The join condition determines which rows match
• Only matching rows are in the result table
• Typical join condition is equality of attributes
• join Employee and TimeCard where Employee.ssn
  TimeCard.ssn
• Relational algebra form
• Employee ?Employee.ssnTimeCard.ssn TimeCard
• Some rows of result shown in this table

13
Join query in Access
Join line
14
Queries with Multiple Joins

• This query has 2 joins
• Join Rental and Video
• Join that table with Movie
• Relational algebra form
• ? accountId, videoId, dateRented, dateDue, title,
  cost ((Rental ?videoId Video) ?movieId Movie)

15
Combining Relational Operations

• Combine selection, projection, and join in one
  query
• project videoId, title, and dateDue from Rental
  join Video on videoId join Movie on movieId where
  accountId113 and
• Relational algebra form
• ? videoId, title, dateDue((? accountId113(Rental)
  ?videoId Video) ?movieId Movie)

16
Defining Complex Queries with Microsoft Access
Four source tables and three joins
Selecting by dateRented
Projecting 4 fields
17
Applying Set Operators to Tables

• Set operations include
• Union, intersection, and difference
• Set operations can be applied to any tables with
  the same shape
• The same order and type of attributes
• Attribute names do not have to agree
• For example
• Rental (accountId number, videoId number,
  dateRented date, dateDue date, cost currency)
• PreviousRental (accountId number, videoId number,
  dateRented date, dateReturned date, cost
  currency)
• Agree in order and type, but not in name
• Rental.dateRented vs. PreviousRental.dateReturned

18
Simple User Interfaces in Access
Customer information
Videos rented by Jane Block
Customer navigation buttons
19
A Video Rental Checkout Form

• See Access database for details of forms design