CIS4365: Database Applications
Fall, 2017

What are the basic RDBMS Operators?                                

The basic relational database management systems operators include:  SELECT, PROJECT, & JOIN.

                               

Brief review:

         

A database is a large, integrated collection of data and metadata. This data and metadata need some kind of                                                                                                     

organization so that the user(s) can access and manipulate it. A set of programs is set up to perform these functions called a database management system (DBMS).

 

So then what is a relational database management system (RDBMS)?

                

It is a DBMS that manages data logically as a collection of tables where data and data relationships are represented by common values in related tables.

Key word here:  tables.

                        

How does it work?

 

The RDBMS manages data (logically) as a collection of two-dimensional tables like spreadsheets.                                                                                                                                                                                                                     

Relationships between tables are maintained by having common values in each of the related tables and joining them together using relational algebra principles.

Then how does one access and manipulate the data and metadata in these tables?

 

§       Relational algebra determines table manipulations

§       And the key operators are…you guessed it…

     the basic RDBMS operators:

                           

SELECT, PROJECT, & JOIN

                      

What is the use of the SELECT operator?

                                 

The SELECT operator chooses All or Specified criterion from a table as the latter is illustrated in the following table.

                                                               

 

What is the use of the PROJECT operator?

 

The PROJECT operator produces a list of all values for a selected attribute.

 

                                                               

 

What is the use of the JOIN operator?

 

The JOIN operator links two different tables together (creating a different table).

 

                                                               

 

 

 

More information on JOIN:

 

Natural Join Process

 

 

§       Links tables by selecting rows with common values in common attribute(s)

§       Three-stage process

1.   Product creates one table

2.   Select yields appropriate rows

3.   Project yields single copy of each attribute to eliminate duplicate columns

§       Eliminates duplicates

§       Does not include rows that are unmatched

 

Other Joins

 

§        EquiJOIN

      - Links tables based on equality condition that compares            specified columns of tables

      - Does not eliminate duplicate columns

      - Join criteria must be explicitly defined

 

§         Theta JOIN

         - EquiJOIN that compares specified columns of                     

        each table using operator other than equality one

 

§         Outer JOIN

      - Matched pairs are retained

         - Unmatched values in other tables left null

      - Right and left

            

                                                                                                

 

 

 

Short Answer Questions:

1.    What are the three basic RDBMS operators?

Answer:  SELECT, PROJECT, and JOIN

2.    What are the principles called that determine table manipulation?

Answer:  Algebra relational principles

3.    Name three other JOINs.

Answer:  EquiJOIN, Theta JOIN, and Outer JOIN.

 

T/F and Multiple-Choice Questions:

1.   The SELECT operator produces a list of all values for a selected attribute.

Answer:  False. The JOIN operator produces a list of all values for a selected attribute.

2.   What two functions do the RDBMS operators satisfy?

a.      Create and delete

b.       Update and access

c.        Access and manipulate

d.       Manipulate and maintain

    Answer:  c. Access and manipulate

 

Other related topics http://www2.bus.orst.edu/faculty/reitsmar/ba370/lectures/dbch3.html

References:        http://www.cs.aue.auc.dk/~nasrullah/notes/3.pdf