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.
- Get all the employees who have dependants.
- Get employees who have dependants with same first name.
- 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.
