Ambiguous attributes  

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

    Example: 

      project(essn, pno, hours);      

  dependent(essn, name, sex, bdate, relationship);

  • Consequence:   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)

  Resolving ambiguity: qualifying ambiguous attributes with its relation name  

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

    • qualifying (= prefixing) the attribute name with the source relation name

  • Example: 

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

  Example: qualifying ambiguous attributes with its relation name  

  • Find project numbers of projects worked on by employees with 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'

  More abiguity issues: using same relation multiple times in a query  

  • 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:

    • Qualifying an attribute name with the relation name will not make the attribute name un-ambiguous

      (Because you would use the same relation name !!!)

  Aliasing: resolving the use of same relation multiple times  

  • 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

  Example of using alias in SQL query  

  • 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.

    Sample output: 

          +--------+---------+--------+---------+
      | fname  | lname   | fname  | lname   |
      +--------+---------+--------+---------+
      | John   | Smith   | Frankl | Wong    |
      | Ramesh | Narayan | Frankl | Wong    |
      | Joyce  | English | Frankl | Wong    |
      | Alicia | Zelaya  | Jennif | Wallace |
      | Ahmad  | Jabbar  | Jennif | Wallace |
      | Frankl | Wong    | James  | Borg    |
      | Jennif | Wallace | James  | Borg    |
      +--------+---------+--------+---------+

  Example of using alias in SQL query  

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

    Solution: 

      List the employee's name and his/her supervisor SSN:           

      SELECT   fname, lname, superssn
      FROM     employee


    
  Add in supervisor's information:

      SELECT   fname, lname, fname, lname
      FROM     employee, employee
      where    superssn = ssn  (red attr belongs to supervisor !!)


    
  Solution: use alias:

      SELECT e.fname, e.lname, m.fname, m.lname    
      FROM   employee e, employee m
      WHERE  e.superssn = m.ssn;

  Giving more descriptive names to attributes  

  • There are ambiguous labels (attribute names) in the SQL query output: 

          +--------+---------+--------+---------+
      | fname  | lname   | fname  | lname   |
      +--------+---------+--------+---------+
      | John   | Smith   | Frankl | Wong    |
      | Ramesh | Narayan | Frankl | Wong    |
      | Joyce  | English | Frankl | Wong    |
      |  ...   |  ...    |  ...   |  ...    |
      | Jennif | Wallace | James  | Borg    |
      +--------+---------+--------+---------+

  • We would prefer to display unambiguous labels/attribute names: 

          +--------+---------+--------+---------+
      | EmpFN  | EmpLN   | SupFN  | SupFN   |
      +--------+---------+--------+---------+
      | John   | Smith   | Frankl | Wong    |
      | Ramesh | Narayan | Frankl | Wong    |
      | Joyce  | English | Frankl | Wong    |
      |  ...   |  ...    |  ...   |  ...    |
      | Jennif | Wallace | James  | Borg    |
      +--------+---------+--------+---------+

  How to give more descriptive labels  

  • We can rename the attribute names in the SQL output as follows: 

      SELECT e.fname EmpFN, e.lname EmpLN, m.fname SupFN, m.lname SupFN    
  FROM   employee e, employee m
  WHERE  e.superssn = m.ssn;

 Output:

      +--------+---------+--------+---------+
      | EmpFN  | EmpLN   | SupFN  | SupFN   |
      +--------+---------+--------+---------+
      | John   | Smith   | Frankl | Wong    |
      | Ramesh | Narayan | Frankl | Wong    |
      | Joyce  | English | Frankl | Wong    |
      | Alicia | Zelaya  | Jennif | Wallace |
      | Ahmad  | Jabbar  | Jennif | Wallace |
      | Frankl | Wong    | James  | Borg    |
      | Jennif | Wallace | James  | Borg    |
      +--------+---------+--------+---------+