2

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?

philipxy
  • 14,867
  • 6
  • 39
  • 83
JadstaSeven
  • 43
  • 2
  • 8
  • wouldnt it look something like `σ(departure-station='LONDON'∧destination-station='KENT'(TRAIN))` and translates(query language dependent) to `SELECT * FROM TRAIN WHERE departure-station = 'LONDON' AND destination-station = 'KENT'` ?? – CJR Jan 29 '17 at 17:20
  • Probably that would be correct, but I'm in the UK and unfortunately we are taught this stuff in a different way which means we don't use the sigma notation or anything like that, just SELECT, PROJECT, DIVIDEBY, etc. We don't even write SELECT actually, we use RESTRICT even though it's the same but just causes more confusion for others in America for example who use SELECT and different notation. So this makes my question a bit more confusing if you're not from the UK :/ – JadstaSeven Jan 29 '17 at 17:27
  • 1. Please give a reference to or summary of what version of "relational algebra" you are supposed to use. Why don't you know whether you can use AND in RESTRICT/SELECT? 2. Is there any other table that tells you "train details"? Because this table seems to give train *trip* details. 3. See my answer. – philipxy Jan 30 '17 at 07:22

3 Answers3

1

I'm not familiar with Oracle SQL but I am familiar with MySQL and I believe the basic syntax is the same. Your use of AND looks correct but it looks like you are missing the FROM keyword in your query. If the table is called TRAIN try doing:

SELECT * FROM TRAIN WHERE departure-station = 'LONDON' AND destination-station = 'KENT'
Jack Vial
  • 2,354
  • 1
  • 28
  • 30
  • The question is asking about a relational algebra expression. They are using alphabetic names rather than symbols. – philipxy Feb 07 '17 at 04:35
1

"And" is certainly valid in relational algebra, can be written like this:

σ departure-station = 'LONDON' AND destination-station = 'KENT' (TRAIN)

this is a basic tutorial but it defines most of the RA concepts:

https://www.tutorialspoint.com/dbms/relational_algebra.htm

Jayvee
  • 10,670
  • 3
  • 29
  • 40
1

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.

Community
  • 1
  • 1
philipxy
  • 14,867
  • 6
  • 39
  • 83
  • Thank you for this well explained answer! This helps a lot, I wasn't sure about the long answer I provided and thought it was likely incorrect but didn't realise it could be done so simply as you've shown. As for the version of relational algebra, I'm really not sure what version it is as we are not told; we are just told it is relational algeba and generally use the RESTRICT function among others (not SELECT if that may help to indicate its version). Maybe it is the original version, with which you explained AND cannot be used, in which case your answer at the end is especially useful for me. – JadstaSeven Jan 30 '17 at 13:39
  • *Somehow* whoever gave you your assignment defined what they mean when they say "RESTRICT". Lecture? Slides? Notes? Textbook? References? And they gave examples, too. (I predict, Avison & Fitzgerald.) – philipxy Jan 30 '17 at 14:26