help plz

[munnsino1]

Relational Algebra Help SQL
I have the following table :

Patient (PatientCode, PatientSurname, PatientFirstname, PatientSex, Age, PatientOccupation, PatientHeight, PatientWeight, PatientAddress)

Doctor (DoctorCode, DoctorSurName, DoctorFirstName, DoctorPrivateAddress, MobileNo, Function)

Operation (Operation Code, PatientCode, DoctorCode, Date, Time, Result, OperationType)

Is_Seen_By (PatientCode, DoctorCode, Date, Time).


2 - i need in relational algebra form 2 find :

1. Find the surname and gender of the patients that have been operated on by doctor "DR20" and results have been successful.
2. Find the code of the operations that have been done on the 15th of December of 2008 and have been successful.
3. Find the first name, surnames and address of the patients that have had at least one operation with broken limb.
4. Find the details of all patients that have shared any doctors with the patient that has the code “P555".
5. Find the surname, gender and age of the patients that have been seen by doctor "DR55" and have been operated on by doctor “DR9”.


**** for the first one i have came up with : -

R1: select doctor where doctor code ="DR20"
R2: R1 NAT JOIN Patient
R3: Project R2 over patientsurname, patientsex

[Hack]

I do not understand what algebra has to do with any of this?

I gather you are looking for the syntax for the SQL queries that will return the results asked for in 2, 3, 4 and 5, right?

What have you tried to these that isn't working for you?

[munnsino1]

hi thanks for your reply. it is an assignment and for some reason we have to provide the relational algebra for the questions listed. i just reqyire some help and wonder if my 1st one is right?

[Hack]

To me it looks like nothing more than a SELECT query with a couple of INNER JOINS and you will have the results asked for. Not all of then even require joins.

For instance, #2

'Find the code of the operations that have been done on the 15th 
'of December of 2008 and have been successful

SELECT [operation code], [operation type] FROM operation
WHERE [date] = '12/15/2008' 
AND result = 'success'

Where is the math in that?

[munnsino1]

could you possibly help me with the other ones especuially ones that do require algebra of that type? it would be greatly appreciated

[Hack]

None of them require algebra.

In fact, none of them require 1st grade arithmetic.

They all require SQL SELECT queries.

I'm not going to write them for you, but I'll give you a hand in writing them for yourself.

How do you think the query for #3 should be written?

[munnsino1]

thanks for your help, but im still stuck. it stipulates on my worksheet write the queries listed in relational algebra only

[Hack]

Is relational algebra some kind of programming language?

[munnsino1]

no it is algebra used in sql. it used the layout like r1: r2: etc as on my 1st attempt. and there are thing such as nat join etc.

[munnsino1]

link : http://db.grussell.org/section010.html

[Hack]

In darn near 30 years of programming, much of which was spent, and continues to be spent, with database development and SQL, this is the first time I've ever heard of this.

I have no idea what practical use this would be in the real world, but I do wish you all the luck.

[munnsino1]

ok how about in nroml sql query language performing the ollwing :

find surnmae of patients that have been seen by at least one of the doctors that have seen patient "p44"

[Hack]

Probably have to play with it a little bit, but off the top of my head I'd start with something like

SELECT pat.patientsurname, pat.patientcode, seen.doctorcode 
FROM patient pat
INNER JOIN is_seen_by seen ON seen.patientcode = pat.patientcode
INNER JOIN doctor doc ON doc.doctorcode = seen.doctorcode
WHERE pat.patientcode = ‘p44’

[munnsino1]

how would i write this one in sql ?

Find the name, gender and age of the patients that had an operation on the 21st of November 2008. List the surname of all doctors that were involved.

[Hack]

Give it a stab on your own and see what you can come up with..