Skip this Video
Download Presentation
Your name here

Loading in 2 Seconds...

play fullscreen
1 / 19

Your name here - PowerPoint PPT Presentation

  • Uploaded on

Your name here The lecture notes are based on using Microsoft Access interactively as part of the lecture. Manipulating Database Content with Relational Algebra and Microsoft Access. How to manipulate information in relational databases Using relational algebra and Microsoft Access

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about ' Your name here' - myra

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Your name here

The lecture notes are based on using Microsoft Access interactively as part of the lecture

manipulating database content with relational algebra and microsoft access
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
manipulating information in relational databases
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
queries and query languages
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
projection queries
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
projection in access
Projection in Access

Query wizard


Source table

Select button

(right arrow)

Selected fields

selection queries
Selection Queries
  • A selection query selects rows that match a selection criteria
    • select from Customer where lastName = ‘Doe.’
  • Relational algebra form
    • lastName=‘Doe’(Customer)
selection query in access
Selection query in Access

All fields selected

Show button unselected

Selection criteria

lastName = “Doe”

more complex criteria
More complex criteria
  • The selection criterion can be any expression
    • select from TimeCard where ssn = ‘376-77-0099’ and date < ‘01-mar-2002’
    • Shaded rows are result rows
  • Relational algebra form
    • ssn=’376-77-0099’ and date<’01-mar-2002’(TimeCard)
complex criteria in access
Complex criteria in Access

Selection criterion


Field selection list

product queries
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

join queries
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.ssn=TimeCard.ssnTimeCard
  • Some rows of result shown in this table
queries with multiple joins
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)
combining relational operations
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 accountId=113 and
  • Relational algebra form
    • videoId, title, dateDue((accountId=113(Rental) ⋈videoId Video) ⋈movieId Movie)
defining complex queries with microsoft access
Defining Complex Queries with Microsoft Access

Four source tables and three joins

Selecting by dateRented

Projecting 4 fields

applying set operators to tables
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
simple user interfaces in access
Simple User Interfaces in Access

Customer information

Videos rented by Jane Block

Customer navigation buttons

a video rental checkout form
A Video Rental Checkout Form
  • See Access database for details of forms design