1 / 16

The Relational Model 1: Introduction, QBE, and Relational Algebra

The Relational Model 1: Introduction, QBE, and Relational Algebra. Chapter 2. Instructional Objectives. Introduce Relational Databases Discuss associated terms and concepts Relational Algebra In-class exercises. Relational databases. What is a relation? What are its characteristics?.

ayasha
Download Presentation

The Relational Model 1: Introduction, QBE, and Relational Algebra

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. The Relational Model 1: Introduction, QBE, and Relational Algebra Chapter 2

  2. Instructional Objectives • Introduce Relational Databases • Discuss associated terms and concepts • Relational Algebra • In-class exercises

  3. Relational databases • What is a relation? • What are its characteristics?

  4. Relational databases • What is a relational database? • A collection of relations (or related tables) • Shorthand notation: (used to depict relational database structure) • General notation: • Table name (attribute 1, …., attribute n) • Underline primary key (or composite key) • Example: • Order (OrderNum, OrderDate, CustomerNum)

  5. Relational Data Manipulation • Four approaches: • Relational Algebra: • Operators that work on relation (+,-,etc.) • Relational calculus: • Not used in commercial database processing • Express what we want (and not how to get it) • Transform-oriented languages: • Nonprocedural • SQL • QBE: • MS Access uses it

  6. Relational Algebra • What is it? • Why learn about it?

  7. Commands • Select • Project • Join

  8. Select • Includes the word WHERE followed by a condition • Example: SELECT Customer WHERE CustomerNum=282 GIVING Answer

  9. Project • Includes the word OVER followed by a list of the columns to be included • Example: PROJECT Customer OVER (CustomerNum, CustomerName) GIVING Answer

  10. Join • Allows extraction of data from more than one table • Natural join: joins records from each original table that is common to both tables • Outer join: joins records from each original table including records not common to both tables

  11. Normal Set Operations • Union (A+B=C) • Intersection • Difference (A-B=C) • Product (A*B) • Division (p.65)

  12. Union JOIN Orders, Customer WHERE Orders.CustomerNum=Customer.CustomerNum GIVING Temp1 PROJECT Temp1 OVER CustomerNum, CustomerName GIVING Temp2 SELECT Customer WHERE RepNum='65' GIVING Temp3 PROJECT Temp3 OVER CustomerNum, CustomerName GIVING Temp4 UNION Temp2 WITH Temp4 GIVING Answer

  13. Intersection JOIN Orders, Customer WHERE Orders.CustomerNum=Customer.CustomerNum GIVING Temp1 PROJECT Temp1 OVER CustomerNum, CustomerName GIVING Temp2 SELECT Customer WHERE RepNum='65' GIVING Temp3 PROJECT Temp3 OVER CustomerNum, CustomerName GIVING Temp4 INTERSECT Temp2 WITH Temp4 GIVING Answer

  14. Difference JOIN Orders, Customer WHERE Orders.CustomerNum=Customer.CustomerNum GIVING Temp1 PROJECT Temp1 OVER CustomerNum, CustomerName GIVING Temp2 SELECT Customer WHERE RepNum='65' GIVING Temp3 PROJECT Temp3 OVER CustomerNum, CustomerName GIVING Temp4 SUBTRACT Temp4 FROM Temp2 GIVING Answer

  15. Product • Mathematically called the Cartesian product • Table obtained by concatenating every row in first table with every row in second table FIGURE 2-43: Product of two tables

  16. Division • Best illustrated by considering division of a table with two columns by a table with a single column • Result contains quotient FIGURE 2-44: Dividing one table by another

More Related