BD Browser

Relations
Schema
Simple SQL Queries
Join Two Relations
Join Two Relations- Alternative Way
Join Two Relations- Alternative Way 2
Join Three Relations
Join Three Relations - Alternative Way
Relations
EMPLOYEE FNAME MINIT LNAME SSN BDATE ADDRESS SEX SUPPERSSN DNO
DEPARTMENT DNAME DNUMBER MGRSSN MGRSTARTDATE
DEPT_LOCATION DNUMBER DLOCATION
PROJECT PNAME PNUMBER PLOCATION DNUM
WORKS_ON ESSN PNO HOURS
DEPENDENT ESSN DEPENDENT_NAME HOURS SEX BDATE RELATIONSHIP

Schema
Simple SQL Queries

⊕ Example Query 0:

Retrieve the birthdate and address of the employee whose name is `John B. Smith'. 
SELECT BDATE,ADDRESS
FROM EMPLOYEE
WHERE FNAME='John' AND MINIT='B' AND LNAME='Smith'
Testing Database
Result

♦ Browser
EMPLOYEE FNAME MINIT LNAME SSN BDATE ADDRESS SEX SUPPERSSN DNO
John N Smith            

Join Two Relations
COMPANY database Schema

⊕ Query 1:

Retrieve the name and address of all employees who work for the 'Research' department. 
SELECT FNAME,LNAME,ADDRESS
FROM EMPLOYEE, DEPARTMENT
WHERE DNAME='Research' AND DNUMBER=DNO
Testing Database
Result

⊕ Step 1: View Entire COMPANY database

EMPLOYEE FNAME MINIT LNAME SSN BDATE ADDRESS SEX SUPPERSSN DNO
DEPARTMENT DNAME DNUMBER MGRSSN MGRSTARTDATE
DEPT_LOCATION DNUMBER DLOCATION
PROJECT PNAME PNUMBER PLOCATION DNUM
WORKS_ON ESSN PNO HOURS
DEPENDENT ESSN DEPENDENT_NAME HOURS SEX BDATE RELATIONSHIP

⊕ Step 2: Select Related Relations

EMPLOYEE FNAME MINIT LNAME SSN BDATE ADDRESS SEX SUPPERSSN DNO
                 
DEPARTMENT DNAME DNUMBER MGRSSN MGRSTARTDATE
       

⊕ Step 3: Join Relations

EMPLOYEE.
FNAME 		EMPLOYEE.
MINIT 		EMPLOYEE.
LNAME 		EMPLOYEE.
SSN 		EMPLOYEE.
BDATE 		EMPLOYEE.
ADDRESS 		EMPLOYEE.
SEX 		EMPLOYEE.
SALARY 		EMPLOYEE.
SUPPERSSN 		EMPLOYEE.DNO=
DEPARTMENT.DNUMBER 		DEPARTMENT.
DNAME 		DEPARTMENT.
MGRSSN 		DEPARTMENT.
MGRSTARTDATE
                         

⊕ Step 4: Enter Selection Conditions

EMPLOYEE.
FNAME 		EMPLOYEE.
MINIT 		EMPLOYEE.
LNAME 		EMPLOYEE.
SSN 		EMPLOYEE.
BDATE 		EMPLOYEE.
ADDRESS 		EMPLOYEE.
SEX 		EMPLOYEE.
SALARY 		EMPLOYEE.
SUPPERSSN 		EMPLOYEE.DNO=
DEPARTMENT.DNUMBER 		DEPARTMENT.
DNAME 		DEPARTMENT.
MGRSSN 		DEPARTMENT.
MGRSTARTDATE
                    Research    

⊕ Step 5: Select Needed Attributes

EMPLOYEE.
FNAME 		EMPLOYEE.
MINIT 		EMPLOYEE.
LNAME 		EMPLOYEE.
SSN 		EMPLOYEE.
BDATE 		EMPLOYEE.
ADDRESS 		EMPLOYEE.
SEX 		EMPLOYEE.
SALARY 		EMPLOYEE.
SUPPERSSN 		EMPLOYEE.DNO=
DEPARTMENT.DNUMBER 		DEPARTMENT.
DNAME 		DEPARTMENT.
MGRSSN 		DEPARTMENT.
MGRSTARTDATE
                    Research    

⊕ Step 6: Clean Up Query (Remove Non-Concerned Attributes)

EMPLOYEE.
FNAME 		EMPLOYEE.
LNAME 		EMPLOYEE.
ADDRESS 		EMPLOYEE.DNO=
DEPARTMENT.DNUMBER 		DEPARTMENT.
DNAME
        Research

⊕ Step 7: Submit Query

Join Two Relations- Alternative Way
COMPANY database Schema

⊕ Query 1:

Retrieve the name and address of all employees who work for the 'Research' department. 
SELECT FNAME,LNAME,ADDRESS
FROM EMPLOYEE, DEPARTMENT
WHERE DNAME='Research' AND DNUMBER=DNO
Testing Database
Result

⊕ Step 1: View Entire COMPANY database

EMPLOYEE FNAME MINIT LNAME SSN BDATE ADDRESS SEX SUPPERSSN DNO
DEPARTMENT DNAME DNUMBER MGRSSN MGRSTARTDATE
DEPT_LOCATION DNUMBER DLOCATION
PROJECT PNAME PNUMBER PLOCATION DNUM
WORKS_ON ESSN PNO HOURS
DEPENDENT ESSN DEPENDENT_NAME HOURS SEX BDATE RELATIONSHIP

⊕ Step 2: Select Major Interested Relations

EMPLOYEE FNAME MINIT LNAME SSN BDATE ADDRESS SEX SUPPERSSN DNO
                 

⊕ Step 2: Select Interested Attributes (Remove Other Attributes)

EMPLOYEE FNAME LNAME ADDRESS DNO
       

⊕ Step 3: Select Joinging Attributes

EMPLOYEE FNAME LNAME ADDRESS DNO
       

⊕ Step 4: Show Up Referenced Relations

EMPLOYEE FNAME LNAME ADDRESS DNO
       
DEPARTMENT DNAME DNUMBER MGRSSN MGRSTARTDATE
       

⊕ Step 5: Expand and Select Interested Attributes Again (Remove Other Attributes)

EMPLOYEE FNAME LNAME ADDRESS EMPLOYEE.DNO=
DEPARTMENT.DNUMBER 		DNAME
         

♦ OR

EMPLOYEE FNAME LNAME ADDRESS DNAME
       

⊕ Step 6: Enter Selection Conditions

EMPLOYEE.
FNAME 		EMPLOYEE.
LNAME 		EMPLOYEE.
ADDRESS 		EMPLOYEE.DNO=
DEPARTMENT.DNUMBER 		DEPARTMENT.
DNAME
        Research

♦ OR

EMPLOYEE.
FNAME 		EMPLOYEE.
LNAME 		EMPLOYEE.
ADDRESS 		DEPARTMENT.
DNAME
      Research

⊕ Step 7: Submit Query

Join Two Relations- Alternative Way 2
COMPANY database Schema

⊕ Query 1:

Retrieve the name and address of all employees who work for the 'Research' department. 
SELECT FNAME,LNAME,ADDRESS
FROM EMPLOYEE, DEPARTMENT
WHERE DNAME='Research' AND DNUMBER=DNO
Testing Database
Result

⊕ Step 1: View Entire COMPANY database

EMPLOYEE FNAME MINIT LNAME SSN BDATE ADDRESS SEX SUPPERSSN DNO
DEPARTMENT DNAME DNUMBER MGRSSN MGRSTARTDATE
DEPT_LOCATION DNUMBER DLOCATION
PROJECT PNAME PNUMBER PLOCATION DNUM
WORKS_ON ESSN PNO HOURS
DEPENDENT ESSN DEPENDENT_NAME HOURS SEX BDATE RELATIONSHIP

⊕ Step 2: Select Major Interested Relations

EMPLOYEE FNAME MINIT LNAME SSN BDATE ADDRESS SEX SUPPERSSN DNO
                 

⊕ Step 2: Select Interested Attributes (Remove Other Attributes)

EMPLOYEE FNAME LNAME ADDRESS DNO
       

⊕ Step 3: Select Joinging Attributes

EMPLOYEE FNAME LNAME ADDRESS DNO
       

⊕ Step 4: Enter Selection Conditions and Needed Attributes

EMPLOYEE FNAME LNAME ADDRESS DNO
      ->DNAME = Research

⊕ Step 5: Submit Query

Join Three Relations
COMPANY database Schema

⊕ Query 2:

For every project located in 'Taipei', list the project number, the controlling department number, and the department manager's last name, address, and birthdate. 
    SELECT PNUMBER, DNUM, LNAME, BDATE, ADDRESS
    FROM PROJECT, DEPARTMENT, EMPLOYEE
    WHERE DNUM=DNUMBER AND MGRSSN=SSN AND
    PLOCATION='Taipei'
Testing Database  
Result2

♦ In Q2, there are two join conditions
The join condition DNUM=DNUMBER relates a project to its controlling department The join condition MGRSSN=SSN relates the controlling department to the employee who manages that department

⊕ Step 1: View Entire COMPANY database

EMPLOYEE FNAME MINIT LNAME SSN BDATE ADDRESS SEX SUPPERSSN DNO
DEPARTMENT DNAME DNUMBER MGRSSN MGRSTARTDATE
DEPT_LOCATION DNUMBER DLOCATION
PROJECT PNAME PNUMBER PLOCATION DNUM
WORKS_ON ESSN PNO HOURS
DEPENDENT ESSN DEPENDENT_NAME HOURS SEX BDATE RELATIONSHIP

⊕ Step 2: Select Related Relations

PROJECT PNAME PNUMBER PLOCATION DNUM
       
DEPARTMENT DNAME DNUMBER MGRSSN MGRSTARTDATE
       
EMPLOYEE FNAME MINIT LNAME SSN BDATE ADDRESS SEX SUPPERSSN DNO
                 

⊕ Step 3: Join Relations

PROJECT.
PNAME 		PROJECT.
PNUMBER 		PROJECT.
PLOCATION 		PROJECT.DNUM =
DEPARTMENT.DNUMBER 		DEPARTMENT.
DNAME 		DEPARTMENT.
MGRSTARTDATE 		DEPARTMENT.MGRSSN=
EMPLOYEE.SSN 		EMPLOYEE.
FNAME 		EMPLOYEE.
MINIT 		EMPLOYEE.
LNAME 		EMPLOYEE.
BDATE TD>EMPLOYEE.
ADDRESS 		EMPLOYEE.
SEX 		EMPLOYEE.
SALARY 		EMPLOYEE.
SUPPERSSN
                           

⊕ Step 4: Enter Selection Conditions

PROJECT.
PNAME 		PROJECT.
PNUMBER 		PROJECT.
PLOCATION 		PROJECT.DNUM =
DEPARTMENT.DNUMBER 		DEPARTMENT.
DNAME 		DEPARTMENT.
MGRSTARTDATE 		DEPARTMENT.MGRSSN=
EMPLOYEE.SSN 		EMPLOYEE.
FNAME 		EMPLOYEE.
MINIT 		EMPLOYEE.
LNAME 		EMPLOYEE.
BDATE TD>EMPLOYEE.
ADDRESS 		EMPLOYEE.
SEX 		EMPLOYEE.
SALARY 		EMPLOYEE.
SUPPERSSN
    Taipei                      

⊕ Step 5: Select Needed Attributes

PROJECT.
PNAME 		PROJECT.
PNUMBER 		PROJECT.
PLOCATION 		PROJECT.DNUM =
DEPARTMENT.DNUMBER 		DEPARTMENT.
DNAME 		DEPARTMENT.
MGRSTARTDATE 		DEPARTMENT.MGRSSN=
EMPLOYEE.SSN 		EMPLOYEE.
FNAME 		EMPLOYEE.
MINIT 		EMPLOYEE.
LNAME 		EMPLOYEE.
BDATE 		EMPLOYEE.
ADDRESS 		EMPLOYEE.
SEX 		EMPLOYEE.
SALARY 		EMPLOYEE.
SUPPERSSN
    Taipei                        

⊕ Step 6: Clean Up Query (Remove Non-Concerned Attributes)

PROJECT.
PNUMBER 		PROJECT.
PLOCATION 		PROJECT.DNUM =
DEPARTMENT.DNUMBER 		DEPARTMENT.MGRSSN=
EMPLOYEE.SSN 		EMPLOYEE.
LNAME 		EMPLOYEE.
BDATE 		EMPLOYEE.
ADDRESS
  Taipei          

⊕ Step 7: Submit Query

Join Three Relations - Alternative Way
COMPANY database Schema

⊕ Query 2:

For every project located in 'Taipei', list the project number, the controlling department number, and the department manager's last name, address, and birthdate. 
    SELECT PNUMBER, DNUM, LNAME, BDATE, ADDRESS
    FROM PROJECT, DEPARTMENT, EMPLOYEE
    WHERE DNUM=DNUMBER AND MGRSSN=SSN AND
    PLOCATION='Taipei'
Testing Database  
Result

⊕ Step 1: View Entire COMPANY database

EMPLOYEE FNAME MINIT LNAME SSN BDATE ADDRESS SEX SUPPERSSN DNO
DEPARTMENT DNAME DNUMBER MGRSSN MGRSTARTDATE
DEPT_LOCATION DNUMBER DLOCATION
PROJECT PNAME PNUMBER PLOCATION DNUM
WORKS_ON ESSN PNO HOURS
DEPENDENT ESSN DEPENDENT_NAME HOURS SEX BDATE RELATIONSHIP

⊕ Step 2: Select Major Relation

PROJECT PNAME PNUMBER PLOCATION DNUM
       

⊕ Step 3: Select Interested Attributes (Remove Other Attributes)

PROJECT PNUMBER PLOCATION DNUM
     

⊕ Step 4: Enter Selection Condition

PROJECT PNUMBER PLOCATION DNUM
  Taipei  

⊕ Step 5: Select Joining Attribute

PROJECT PNUMBER PLOCATION DNUM
  Taipei  

⊕ Step 6: Show Up Referenced Relations

PROJECT PNUMBER PLOCATION DNUM
  Taipei  
DEPARTMENT DNAME DNUMBER MGRSSN MGRSTARTDATE
       

⊕ Step 7: Join Relations

PROJECT.
PNUMBER 		PROJECT.
PLOCATION 		PROJECT.DNUM =
DEPARTMENT.DNUMBER 		DEPARTMENT.
DNAME 		DEPARTMENT.
MGRSSN 		DEPARTMENT.
MGRSTARTDATE
  Taipei        

⊕ Step 8: Select Interested Attributes Again

PROJECT.
PNUMBER 		PROJECT.
PLOCATION 		PROJECT.DNUM =
DEPARTMENT.DNUMBER 		DEPARTMENT.
MGRSSN
  Taipei    

⊕ Step 9: Select Second Joining Attribute

PROJECT.
PNUMBER 		PROJECT.
PLOCATION 		PROJECT.DNUM =
DEPARTMENT.DNUMBER 		DEPARTMENT.
MGRSSN
  Taipei    

⊕ Step 10: Show Up the Third Relation

PROJECT.
PNUMBER 		PROJECT.
PLOCATION 		PROJECT.DNUM =
DEPARTMENT.DNUMBER 		DEPARTMENT.
MGRSSN
  Taipei    
EMPLOYEE FNAME MINIT LNAME SSN BDATE ADDRESS SEX SUPPERSSN DNO
                 

⊕ Step 11: Join the Third Relation

PROJECT.
PNUMBER 		PROJECT.
PLOCATION 		PROJECT.DNUM =
DEPARTMENT.DNUMBER 		DEPARTMENT.MGRSSN=
EMPLOYEE.SSN 		EMPLOYEE.
FNAME 		EMPLOYEE.
MINIT 		EMPLOYEE.
LNAME 		EMPLOYEE.
BDATE TD>EMPLOYEE.
ADDRESS 		EMPLOYEE.
SEX 		EMPLOYEE.
SALARY 		EMPLOYEE.
SUPPERSSN
  Taipei                  

⊕ Step 12: Select Needed Attributes and Clean Up

PROJECT.
PNUMBER 		PROJECT.
PLOCATION 		PROJECT.DNUM =
DEPARTMENT.DNUMBER 		DEPARTMENT.MGRSSN=
EMPLOYEE.SSN 		EMPLOYEE.
LNAME 		EMPLOYEE.
BDATE 		EMPLOYEE.
ADDRESS
  Taipei          

⊕ Step 13: Submit Query