Geek asks and answers

How to convert SQL to relational algebra in case of SQL joins?

Nowadays, I'm working on SQL and Relational Algebra. And I'm stuck on the following questions. I can do SQL for the following questions, but somehow my relational algebra I created does not look like that.

Below are my tables -

Employee ( EmployeeId , EmployeeName, EmployeeCountry)
 Training ( TrainingCode , TrainingName, TrainingType, TrainingInstructor)
Outcome ( EmployeeId, TrainingCode , Grade)

All keys are indicated by an asterisk *.

Below is the question and its SQL query that works fine -

Find the identifier of the employee who completed all the workouts.

SQL Qyery:

SELECT X.EmployeeID
FROM   (SELECT EmployeeID, COUNT(*) AS NumClassesTaken 
        FROM OutCome GROUP BY EmployeeID ) 
        AS X
  JOIN (SELECT COUNT(*) AS ClassesAvailable 
        FROM Training) 
        AS Y
  ON X.NumClassesTaken = Y.ClassesAvailable

I can’t understand what will be the relational algebra for the above query? Can anyone help me with this?

+3
source share
1 answer

Relational algebra for:

Find Id Employeewhich one has taken training.

% :

r Γ· s , "all":

:

  • ALL ?
  • , ALL , Jon Smith?

:

% : ", ".

:

Training ( TrainingCode , TrainingName, TrainingType, TrainingInstructor)

: TrainingCode:

TC = TrainingCode ()

employeeID trainingCode: .

ET = EmployeeId, TrainingCode ()

% Division, TrainingCode, , .

Result = EmployeeId (ET% TC)

" " - , .

6.3.4 DIVISION

DIVISION , universal quantification ALL. SQL . SQL EXISTS, CONTAINS NOT EXISTS .

DIVISION T(Y) = R(Z) % S(X), X βŠ† Z Y = Z - X (, , Z = X βˆͺ Y); Y - R, S, . X = {A}, Z = {A, B} then Y = {B}, B S.

T(Y) DIVISION , t, tuple tR R tR[Y] = t, tR[X] = tS every S. , . t, t DIVISION, t R S.

, { Οƒ , ∏ , β‹ˆ , Ξ§ , - } Selection, Projection, Join, Cartesian Cross Minus ; . % ∏, β‹ˆ -:

T1 <-- ∏Y(R)
T2 <-- ∏Y((S Χ T1) - R)
T3 <-- T1 - T2

, , R , S Y EmployeeId.

, .

+4

More articles:


All Articles