Your name here
This presentation is the property of its rightful owner.
Sponsored Links
1 / 19

Your name here PowerPoint PPT Presentation


  • 70 Views
  • Uploaded on
  • Presentation posted in: General

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

Download Presentation

Your name here

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

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

dialog

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

date<01-mar-2002

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


Join query in access

Join query in Access

Join line


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


  • Login