Lecture #20 (28 March 2002)

Relational Databases

Overall Reading
Brookshear: pp. 409-417

Outline:

  • Relational Operations (pp. 409-415 [Br])
  • SQL (Structured Query Language) (pp. 416-417 [Br])
  • "A Gentle Introduction to SQL"

    • Relational Operations (pp. 409-415 [Br])

    We would like to see how we can get information from a relational database as a user/programmer.
  • SELECT
    Sometimes we want to select only some of the tuples.

    EMPLOYEE relation
    EmplIDNameAddressSS Num
    25X15 Joe E. Baker 33 Nowhere St. 111223333
    34Y70 Cheryl H. Clark 563 Downtown Ave. 999009999
    23Y34 G. Jerry Smith 1555 Circle Dr. 111005555
    . . . .
    . . . .
    . . . .

    SELECT from EMPLOYEE where EmplID="34Y70"

    EmplIDNameAddressSS Num
    34Y70 Cheryl H. Clark 563 Downtown Ave. 999009999

  • PROJECT
    Sometimes you only want a view with some columns.

    EMPLOYEE relation
    EmplIDNameAddressSS Num
    25X15 Joe E. Baker 33 Nowhere St. 111223333
    34Y70 Cheryl H. Clark 563 Downtown Ave. 999009999
    23Y34 G. Jerry Smith 1555 Circle Dr. 111005555
    . . . .
    . . . .
    . . . .

    PROJECT Name,Address from EMPLOYEE

    NameAddress
    Joe E. Baker 33 Nowhere St.
    Cheryl H. Clark 563 Downtown Ave.
    G. Jerry Smith 1555 Circle Dr.
    . .
    . .
    . .

  • JOIN
    This operation combines the attributes of both relations into a single relation, as follows. It takes each tuple from the first table and combines it with each tuple from the second table. As a simple example:

    Relation A
    VW
    r2
    t4
    p6

    Relation B

    XYZ
    5gp
    4de
    2mq
    4tf

    JOIN A and B

    A.VA.WB.XB.YB.Z
    r25gp
    r24de
    r22mq
    r24tf
    t45gp
    t44de
    t42mq
    t44tf
    p65gp
    p64de
    p62mq
    p64tf

    The attribute names from each of the original relations do not necessarily need to have the same names, and if they do, it may be a coincidence. However, we often do not want to join all possible pairs of tuples, but only those in which some particular attributes match.

    Relation A
    VW
    r2
    t4
    p6

    Relation B

    XYZ
    5gp
    4de
    2mq
    4tf

    JOIN A and B where A.W = B.X

    A.VA.WB.XB.YB.Z
    r22mq
    t44de
    t44tf

  • Combining operators:
    In general, you can combining these operators together to do more non-trivial queries, essentially creating new relations and then operating on those.

    Example,
    NEW1 <- JOIN ASSIGNMENT and JOB where ASSIGNMENT.JobID=JOB.JobID
    NEW2 <- SELECT form NEW1 where ASSIGNMENT.TermDate="present"
    LIST <- PROJECT ASSIGNMENT.EmplID,JOB.Dept from NEW2

    Also, it should be noted that the user interface for many databases will present these same concepts, however using different syntax or graphical interaction.

    • SQL (Structured Query Language) (pp. 416-417 [Br])

    This is a language for specifying queries of a relational database. It was originally created by IBM but has since become standardized for use in the industry.

    Though its terminology differs somewhat from what we have introduced above, a single SQL statement may be used to express a combination of SELECT, PROJECT and JOIN operations.

    Format: 

      select <list of attributes>
  from   <list of tables>
  where  <list of conditions>
    Let's revisit some sample queries:

  • PROJECT Name, Address from EMPLOYEE

    In SQL: 

      select Name, Address
  from EMPLOYEE
    [Note well: the terminology 'select' in SQL is not the same as the operation SELECT we have discussed]

  • SELECT from EMPLOYEE where EmplID="34Y70" 

      select EmplID, Name, Address, SSN
  from EMPLOYEE
  where EmplID = '34Y70'

  • JOIN A and B where A.W = B.X 

      select *
  from A, B
  where A.W = B.X

  • NEW1 <- JOIN ASSIGNMENT and JOB where ASSIGNMENT.JobID=JOB.JobID
    NEW2 <- SELECT form NEW1 where ASSIGNMENT.TermDate="present"
    LIST <- PROJECT ASSIGNMENT.EmplID,JOB.Dept from NEW2 

      select EmplID, Dept
  from ASSIGNMENT, JOB
  where ASSIGNMENT.JobID = JOB.JobID
    and ASSIGNMENT.TermDate = "present"

    • SQL statements can use additional features not mentioned in the text. Just a few such examples are:

  • attribute list following select keyword
  • the character "*" can be used as a wildcard to display all attributes.
  • for numeric attributes, you can create new columns which combine information of several attributes. For example:
    select Weight/Density
  • condition list following where keyword
  • condition can be arbitrarily complex logical expression, based on operators (AND, OR, NOT).
  • for numeric attributes, you can say things such as:
    where Value >= 25
    Or using operators such as (>, >=, =, <, <=)
  • for text attributes, the inequality operators will be evaluated based on alphabetical order.
  • There are even way to do partial matches for text, such as
    FirstName LIKE 'Mich*'

    • "A Gentle Introduction to SQL"

    Next time, we will play with a very nice website titled, A Gentle Introduction to SQL, provided by Andrew Cumming of the School of Computing of Napier University in the UK. It provides several nice tutorials allowing you to form your own SQL queries on existing databases (and it goes into far more depth than we will do in this course).

    comp150 Class Page
    mhg@cs.luc.edu
    Last modified: Mon Mar 18 21:16:07 CST 2002