The relevant table is:
TRAIN (train-code, train-type, departure-station, destination-station)
List details of trains which depart from LONDON and are destined for KENT.
The question is very simple, and I've looked everywhere for a basic answer but can't find one.
This is what I have come up with so far, but I don't know if it is correct or if AND can even be used in relational algebra:
SELECT TRAIN WHERE departure-station = 'LONDON' AND destination-station = 'KENT'
Is that correct? I can't find any information online about whether or not you can use AND for this. If this didn't work, I'm thinking my answer should be something like this:
SELECT TRAIN WHERE departure-station = 'LONDON' GIVING TABLE1
SELECT TRAIN WHERE destination-station = 'KENT' GIVING TABLE2
PROJECT TABLE1 OVER train-code GIVING TABLE3
PROJECT TABLE2 OVER train-code GIVING TABLE4
TABLE1 INTERSECT TABLE2 GIVING TABLE3
PROJECT TABLE3 OVER train-code, train-type, departure-station, destination-station GIVING RESULT
This seems like it would be too long to me, and may be incorrect anyway. Maybe I need to use a JOIN command instead? I really don't know, even though it's such a simple question. Can anyone help me?
There is no single version of relational algebra, even when we ignore trivial syntax differences like π vs PROJECT OVER. They don't even all have the same notion of relation. What is the definition you were given for SELECT/RESTRICT? We can't tell you, you must tell us.
Your AND is valid if the version of relational algebra to be used allows an arbitrarily nested condition in SELECT. The original relational algebra allowed only a single equality or inequality. You need to find out what "relational algebra" & "SELECT" mean for your assignment. But either way you could write:
SELECT (SELECT TRAIN WHERE departure-station = 'LONDON')
WHERE destination-station = 'KENT'
Your long form pointlessly sets TABLE3, doesn't use it, then sets it again. Also your long form is invalid, because INTERSECT needs two relations with the same attribute sets. But you are right that INTERSECT parallels AND. So does NATURAL JOIN. Every table (given or query) has a predicate, ie a statement template parameterized by attributes, giving the tuple membership condition. Eg TRAIN is tuples where (something like) train train-code of type train-type goes from departure-station to destination-station. INTERSECT & NATURAL JOIN return tuples that are in one relation AND in the other, ie that satisfy one relation's predicate ANDed with the other's. Similarly UNION calculates for OR, MINUS calculates for AND NOT, and ... WHEREcondition calculates ... AND condition. Eg your SELECT with AND is tuples where train train-code of type train-type goes from departure-station to destination-station AND departure-station = 'LONDON' AND destination-station = 'KENT'. PROJECTrelationOVERattributes kept calculates for FOR SOME (values for) attributes dropped, predicate. Eg TABLE4 is tuples where FOR SOME train-type, departure-station & destination-station, train-code of type train-type goes from departure-station to destination-station AND destination-station = 'KENT'.
So another answer that doesn't use AND is TABLE1 INTERSECT TABLE2. Why are you doing all the other stuff? (Eg you took some projections then joined them with the original; what does that always give?)
See this answer.