Re: Join or Cross Product

Let's assume the following:

1. In the table Dependants the column Name is a first name. We can assume that the dependant's last name is implied to be the same as the related employee. Probably not a good contemporary assumption but our concerns here are relational and not societal :)
2. The relational algebra we are using has the following operations:
3. PROJECT: Create a new table using only specified columns from a given table. Syntax is PROJECT(table, column1, ..., columnN)
4. SELECT: Create a new table by selecting from a table those rows that meet a stated criterion. Syntax is SELECT(table, condition)
5. JOIN: Create a new table that relates the rows of two given tables that have equal values in identically named columns. In other words, an equijoin or natural join. Syntax is JOIN(table1, table2)
6. := : Refer to a table by another name or alias. Syntax is tableName := table

To solve your problem we do the following:

1. We need to join Employees with Dependants. Since they do have some columns in common and we don't wish to have them used in the join, we first project.

    EmployeeInfo := PROJECT(Employees, NINumber, Fname, Lname)     DependantInfo := PROJECT(Dependants, ENINumber, Name)     AllEmployeeDependants := JOIN(EmployeeInfo, DependantInfo)

    Since the tables EmployeeInfo and DependantInfo have no column     names in common, we get the cross product of their rows or a     cross join.

2. We now select the rows we need, i.e., those rows where an

    employee and his dependant have the same first name

    EmployeeDependantsSameName :=
    SELECT(AllEmployeeDependants,

                    NINumber = ENINumber AND Fname = Name)

3. Project the first and last names of the previously selected employees.

    Answer := PROJECT(EmployeeDependantsSameName, Fname, Lname)

    The primary key of the Dependants table ensures that an employee can't have

    more than one dependant with the same first name. The boxer George Foreman

    named all his male children George (and he has several male kids) so this wouldn't

    work for him. But note that even if you eased this restriction there cannot be

    duplicate rows in table Answer because all tables in the relational algebra are sets,

    unlike SQL tables which can be bags.

Hope this helps.

Regards,
jag

"chris.danx" <spamoff.danx_at_ntlworld.com> wrote in message news:v2w99.938$a8.21327_at_newsfep3-gui.server.ntli.net...
> Hi,
>
> Got a problem with a question on a study sheet for my exam next week,
> can someone please help clear it up? It is *not* homework, term is
> finished and our summer reading list is on java and C not relational
> algebra, just in case there's any confusion (I was off ill at the first
> sitting and missed alot of the course :( ).
>
>
> The question asks to list the names of employees who have a dependant
> with the same first name as themselves using relational algebra.
>
> The schema is given below (keys are enclosed in asterixes):
>
>
> Employee (*NINumber*, Fname, Lname, Address, Sex, Birthdate, Salary,
> Supervisor, WorksFor)
>
> Department (*Number*, Name, Manager, StartDate)
>
> Project (*Number*, Name, Location, ControllingDept)
>
> Dependant (*ENINumber*, *Name*, Sex, Relationship, Birthdate)
>
> WorksOn (*Emp*, *Proj*, Hours)
>
> Location (*Dnumber*, *Lname*)
>
>
> Relation Foreign Key Refs Relation Key
> ================================================================
> Employee Supervisor -> Employee NINumber
> Employee WorksFor -> Department Number
> Department Manager -> Employee NINumber
> Project ControllingDept -> Department Number
> Dependant ENINumber -> Employee NINumber
> WorksOn Emp -> Employee NINumber
> WorksOn Proj -> Project Number
> Location Dnumber -> Department Number
>
>
> ok, down to the main problem...
>
> I started by writing down the main steps in getting the result.
>
> 1. Get all the employees who have dependants.
> 2. Get employees who have dependants with same first name.
> 3. Get names of those employees.
>
>
> EmpDependants := select (Employee x Dept, NINumber = ENINumber)
> // Get employees of any employees with dependants along with dependant
> information.
>
>
> EmpDepSameName := select (EmpDepends, Fname = Name)
> // Get employees who have same first name as their dependant.
>
>
> EmpNames := project (EmpDepSameName, Fname, Lname)
>
> // Get columns corresponding to employee names.
>
>
> Firstly, is this correct and secondly what happens if instead of using
> the cross product a join is used? i.e.
>
> EmpDependants := join (Emp, NiNumber, Dependant, ENINumber)
>
> Is there a difference in this circumstance?
>
>
>
> Thanks,
> Chris
>
> p.s. the schema and requirements (in a separate document) are not at all
> clear on whether the dependants name is only their first name or
> includes the whole dependant name. No indication was given if we were
> allowed other functions beyond those given (I don't see why not), so I
> assumed for the purpose of the question that the dependant name was only
> their first name.
>
> --
> for personal replies change 'spamoff' to 'chris'
>
Received on Sat Aug 24 2002 - 14:19:15 CEST