Database Systems Lab 6: Inbuilt Functions and Relational Algebra Operations

📚 Objective

To understand the use of inbuilt functions and relational algebra operations with SQL queries using the COMPANY database schema.

🏗️ COMPANY Database Schema

EMPLOYEE Table

Column	Data Type	Constraints	Description
SSN	CHAR(9)	PRIMARY KEY	Social Security Number
FNAME	VARCHAR(15)	NOT NULL	First Name
MINIT	CHAR(1)		Middle Initial
LNAME	VARCHAR(15)	NOT NULL	Last Name
BDATE	DATE		Birth Date
ADDRESS	VARCHAR(30)		Employee Address
SEX	CHAR(1)		Gender
SALARY	DECIMAL(10,2)		Employee Salary
SUPER_SSN	CHAR(9)	FOREIGN KEY	Supervisor SSN
DNO	INT	FOREIGN KEY	Department Number

DEPARTMENT Table

Column	Data Type	Constraints	Description
DNAME	VARCHAR(15)	NOT NULL	Department Name
DNUMBER	INT	PRIMARY KEY	Department Number
MGR_SSN	CHAR(9)	FOREIGN KEY	Manager SSN
MGR_START_DATE	DATE		Manager Start Date

PROJECT Table

Column	Data Type	Constraints	Description
PNAME	VARCHAR(15)	NOT NULL	Project Name
PNUMBER	INT	PRIMARY KEY	Project Number
PLOCATION	VARCHAR(15)		Project Location
DNUM	INT	FOREIGN KEY	Department Number

WORKS_ON Table

Column	Data Type	Constraints	Description
ESSN	CHAR(9)	PRIMARY KEY, FOREIGN KEY	Employee SSN
PNO	INT	PRIMARY KEY, FOREIGN KEY	Project Number
HOURS	DECIMAL(3,1)		Hours Worked

DEPENDENT Table

Column	Data Type	Constraints	Description
ESSN	CHAR(9)	PRIMARY KEY, FOREIGN KEY	Employee SSN
DEPENDENT_NAME	VARCHAR(15)	PRIMARY KEY	Dependent Name
SEX	CHAR(1)		Gender
BDATE	DATE		Birth Date
RELATIONSHIP	VARCHAR(8)		Relationship to Employee

DEPT_LOCATIONS Table

Column	Data Type	Constraints	Description
DNUMBER	INT	PRIMARY KEY, FOREIGN KEY	Department Number
DLOCATION	VARCHAR(15)	PRIMARY KEY	Department Location

🔧 Key Concepts

Relational Algebra Operations

σ Selection: Selects rows that satisfy a condition

π Projection: Selects specific columns

⋈ Join: Combines tables based on related columns

∪ Union: Combines results from multiple queries

∩ Intersection: Common rows between two relations

− Difference: Rows in first relation but not in second

SQL Inbuilt Functions

Aggregate Functions: COUNT(), SUM(), AVG(), MAX(), MIN()

String Functions: UPPER(), LOWER(), SUBSTRING(), LENGTH()

Date Functions: YEAR(), MONTH(), DAY(), CURRENT_DATE()

Conditional Functions: CASE, COALESCE(), NULLIF()

🎯 Query Solutions

Query 1

Retrieve the names of all employees in department 5 who work more than 10 hours per week on the 'ProductX' project.

Relational Algebra:

π_FNAME,LNAME(σ_{DNO=5 ∧ HOURS>10 ∧ PNAME='ProductX'}(EMPLOYEE ⋈ WORKS_ON ⋈ PROJECT))

SELECT E.FNAME, E.LNAME
FROM EMPLOYEE E
JOIN WORKS_ON W ON E.SSN = W.ESSN
JOIN PROJECT P ON W.PNO = P.PNUMBER
WHERE E.DNO = 5 
  AND W.HOURS > 10 
  AND P.PNAME = 'ProductX';

Explanation:

This query uses multiple JOIN operations to combine three tables. We filter employees by department number 5, hours worked greater than 10, and project name 'ProductX'. The projection operation selects only the first and last names.

Query 2

List the names of all employees who have a dependent with the same first name as themselves.

Relational Algebra:

π_FNAME,LNAME(σ_{FNAME=DEPENDENT_NAME}(EMPLOYEE ⋈_SSN=ESSN DEPENDENT))

SELECT E.FNAME, E.LNAME
FROM EMPLOYEE E
JOIN DEPENDENT D ON E.SSN = D.ESSN
WHERE E.FNAME = D.DEPENDENT_NAME;

Explanation:

This query joins the EMPLOYEE and DEPENDENT tables on the SSN/ESSN relationship, then filters where the employee's first name matches their dependent's name. This demonstrates self-referential comparison within joined tables.

Query 3

Find the names of employees who are directly supervised by 'Franklin Wong'.

Relational Algebra:

π_{E1.FNAME,E1.LNAME}(σ_{E2.FNAME='Franklin' ∧ E2.LNAME='Wong'}(EMPLOYEE E1 ⋈_{E1.SUPER_SSN=E2.SSN} EMPLOYEE E2))

SELECT E1.FNAME, E1.LNAME
FROM EMPLOYEE E1
JOIN EMPLOYEE E2 ON E1.SUPER_SSN = E2.SSN
WHERE E2.FNAME = 'Franklin' 
  AND E2.LNAME = 'Wong';

Explanation:

This is a self-join query where we alias the EMPLOYEE table twice. E1 represents subordinates and E2 represents supervisors. We join on the supervisor relationship and filter for Franklin Wong as the supervisor.

Query 4

Retrieve the names of employees who work on every project.

Relational Algebra:

π_FNAME,LNAME(EMPLOYEE ⋈ (π_ESSN(WORKS_ON) ÷ π_PNUMBER(PROJECT)))

SELECT E.FNAME, E.LNAME
FROM EMPLOYEE E
WHERE NOT EXISTS (
    SELECT P.PNUMBER
    FROM PROJECT P
    WHERE NOT EXISTS (
        SELECT W.PNO
        FROM WORKS_ON W
        WHERE W.ESSN = E.SSN 
          AND W.PNO = P.PNUMBER
    )
);

Explanation:

This query uses the division operation concept implemented with double negation (NOT EXISTS). It finds employees for whom there doesn't exist any project that they don't work on - meaning they work on all projects.

Query 5

Retrieve the names of employees who do not work on any project.

Relational Algebra:

π_FNAME,LNAME(EMPLOYEE − π_FNAME,LNAME(EMPLOYEE ⋈_SSN=ESSN WORKS_ON))

SELECT E.FNAME, E.LNAME
FROM EMPLOYEE E
WHERE E.SSN NOT IN (
    SELECT DISTINCT W.ESSN
    FROM WORKS_ON W
    WHERE W.ESSN IS NOT NULL
);

Explanation:

This query uses the difference operation to find employees who are not in the WORKS_ON table. The NOT IN clause excludes employees who have any project assignments.

Query 6

Retrieve the names and addresses of all employees who work on at least one project located in Houston but whose department has no location in Houston.

Relational Algebra:

π_{FNAME,LNAME,ADDRESS}((EMPLOYEE ⋈ WORKS_ON ⋈ σ_{PLOCATION='Houston'}(PROJECT)) − (EMPLOYEE ⋈ σ_{DLOCATION='Houston'}(DEPT_LOCATIONS)))

SELECT DISTINCT E.FNAME, E.LNAME, E.ADDRESS
FROM EMPLOYEE E
JOIN WORKS_ON W ON E.SSN = W.ESSN
JOIN PROJECT P ON W.PNO = P.PNUMBER
WHERE P.PLOCATION = 'Houston'
  AND E.DNO NOT IN (
      SELECT DL.DNUMBER
      FROM DEPT_LOCATIONS DL
      WHERE DL.DLOCATION = 'Houston'
  );

Explanation:

This complex query finds employees who work on Houston projects but their department isn't located in Houston. It combines positive selection (works on Houston projects) with negative selection (department not in Houston).

Query 7

Retrieve the last names of all department managers who have no dependents.

Relational Algebra:

π_LNAME(σ_{SSN=MGR_SSN}(EMPLOYEE ⋈ DEPARTMENT) − π_LNAME(σ_SSN=ESSN(EMPLOYEE ⋈ DEPENDENT)))

SELECT E.LNAME
FROM EMPLOYEE E
JOIN DEPARTMENT D ON E.SSN = D.MGR_SSN
WHERE E.SSN NOT IN (
    SELECT DISTINCT DEP.ESSN
    FROM DEPENDENT DEP
    WHERE DEP.ESSN IS NOT NULL
);

Explanation:

This query identifies department managers (by joining EMPLOYEE and DEPARTMENT on manager SSN) who are not in the DEPENDENT table as employees with dependents. It combines role-based selection with exclusion logic.

📊 Sample Data and Expected Results

Note: The following sample data demonstrates the structure and relationships in the COMPANY database. Actual results will depend on the specific data in your database instance.

Sample EMPLOYEE Data

SSN	FNAME	LNAME	ADDRESS	DNO	SUPER_SSN
123456789	John	Smith	731 Fondren, Houston TX	5	333445555
333445555	Franklin	Wong	638 Voss, Houston TX	5	888665555
999887777	Alicia	Zelaya	3321 Castle, Spring TX	4	987654321
987654321	Jennifer	Wallace	291 Berry, Bellaire TX	4	888665555

💡 Advanced Topics

Query Optimization Tips:

Use appropriate indexes on frequently joined columns
Consider query execution plans for complex operations
Use EXIST instead of IN for better performance with NULLs
Avoid unnecessary DISTINCT operations

Common Pitfalls:

NULL values in NOT IN clauses can produce unexpected results
Self-joins require careful aliasing to avoid confusion
Division queries (universal quantification) are complex and require double negation
Always consider cardinality when using JOINs

📝 Practice Exercises

Additional Practice Problems:

Find employees who work on more than 3 projects
List departments with more than 5 employees
Retrieve projects with no assigned employees
Find the highest paid employee in each department
List employees who have the same supervisor as 'John Smith'

🎯 Learning Outcomes

After completing this lab, students should be able to:

Understand and apply fundamental relational algebra operations
Write complex SQL queries involving multiple tables and conditions
Use various types of JOIN operations effectively
Implement universal quantification using double negation
Handle NULL values appropriately in queries
Optimize query performance through proper indexing and query structure

EXPERIMENT-6

📚 Objective

🏗️ COMPANY Database Schema

EMPLOYEE Table

DEPARTMENT Table

PROJECT Table

WORKS_ON Table

DEPENDENT Table

DEPT_LOCATIONS Table

🔧 Key Concepts

Relational Algebra Operations

SQL Inbuilt Functions

🎯 Query Solutions

Retrieve the names of all employees in department 5 who work more than 10 hours per week on the 'ProductX' project.

Relational Algebra:

Explanation:

List the names of all employees who have a dependent with the same first name as themselves.

Relational Algebra:

Explanation:

Find the names of employees who are directly supervised by 'Franklin Wong'.

Relational Algebra:

Explanation:

Retrieve the names of employees who work on every project.

Relational Algebra:

Explanation:

Retrieve the names of employees who do not work on any project.

Relational Algebra:

Explanation:

Retrieve the names and addresses of all employees who work on at least one project located in Houston but whose department has no location in Houston.

Relational Algebra:

Explanation:

Retrieve the last names of all department managers who have no dependents.

Relational Algebra:

Explanation:

📊 Sample Data and Expected Results

Sample EMPLOYEE Data

💡 Advanced Topics

Query Optimization Tips:

Common Pitfalls:

📝 Practice Exercises

Additional Practice Problems:

🎯 Learning Outcomes