CS457 Syllabus & Progress

More SQL features: qualifying attribute, aliasing and renaming output attributes

Ambiguous attribute names from different relations

Ambiguous attribute names:

Attributes in different relations can have the same attribute name.

Ambigious attribute name = an attribute name that can be found in multiple relations

Example:

project(essn, pno, hours); dependent(essn, name, sex, bdate, relationship);

When project and dependent relations are both used in a query :

The attribute name "essn" will be ambiguous

Example:

select essn // Ambigious attribute name from project, dependent where ...

(Because you cannot tell whether essn is an attribute from the Project relation or from the Dependent relation)

Solving the ambiguous attribute names from different relations

When different relations has a common attribute name, we can make that name un-ambiguous by:

Example:

Project.essn refers the the essn attribute in Project relation Dependent.essn refers the the essn attribute in Dependent relation

Example query that require the use of qualified attributes:

Find project numbers of projects worked on by employees who has a daughter named 'Alice'.

Solution:


  Find out who has a daughter named Alice:

      SELECT  essn
      FROM    dependent
      WHERE   name='Alice'


  Find pno worked on by these employees:

      SELECT  pno
      FROM    dependent, works_on
      WHERE   essn = essn          <----- Ambigious !!!    
        and   name='Alice'


  Solution:  Qualify with relation name to resolve ambiguity:          

      SELECT  pno
      FROM    works_on, dependent
      WHERE   works_on.essn = dependent.essn  
        and   name='Alice'

Aliasing: qualifying attribute names from the same relation

Fact:

The SELECT command can use the same relation multiple times

Example:

SELECT ... FROM employee, employee // Use employee twice

Problem when using the same relation multiple times in SELECT:

Every attribute name in that relation will be ambiguous
Furthermore:

Alias:

Alias = a mechanism in SQL to give a new name to a relation

Syntax to specify an alias:

SELECT FROM R alias1 , R alias2 , .... WHERE ...

Meaning:

The relation name alias1 is the now the name name for the first instance of relation R
The relation name alias2 is the now the name name for the second instance of relation R

How to use an alias:

Example query that require the use of aliasing:

For each employee, list his/her fname and lname, and his/her manager's fname and lname.

Solution:

Renaming the attribute name in the output

Consider the output of the previous query:

The name (title) of the attributes are not very meaningful (in fact: ambigious)

Renaming the attribute names in the output: