-2

I'm given a table with two columns: id_ship and date. I need to find the ids of the ships that appear the maximum number of times (that is , the ships that have the maximum number of dates), assuming that a ship cannot have more than three dates.

It's easy to find the ids of the ships that have three dates, just selecting from the triple cartesian product of the table. However, I'm not able to check if the result obtained is empty or not. If it's not empty, I would have to choose this view, while if it is empty I would need to look for the ships that have two dates and check again if this is empty. How can I proceed? Remember I cannot use extended relational algebra, so it's not valid the use of aggregating functions or extended projection.

EDIT: Sorry, I thought the concept "classical relational algebra" was standard. The operations that. The operations supported by my RA are: select, project, rename, union, difference, intersect, cartesian product, natural join, join with condition and division

Seven
  • 151
  • 7
  • There are many RAs (relational algebras). They differ in operators & even what a relation is. Give a reference and/or definition for yours. Eg textbook name, edition & page. RA expressions form a programming language. Please in code questions give a [mre]--cut & paste & runnable code; example input (as initialization code) with desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. That includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) – philipxy Oct 12 '19 at 23:26
  • What does "check if the result obtained is empty or not" mean in terms of your being stuck? RAs don't return booleans, they return tables. Of course, you maybe just have got as far as, "I don't see how to put such checks into a query". Same for "choose this view" & "look for" except I have no idea what you have in mind. But--How far can you get by rephrasing those in terms of tables got from other tables? Eg, you can't see a query where given tables T & E, if E is not empty then return T else return restrict false (T). PS [Re relational querying.](https://stackoverflow.com/a/24425914/3404097) – philipxy Oct 13 '19 at 00:42
  • If I understood you, there's no way I can check if the relation obtained is empty . Then, I should find a way of labelling the tuples in each group, but that doesn't possible in classical RA – Seven Oct 13 '19 at 06:45
  • I said I don't know what you mean by "check" or those other things. You don't need to label if you union disjoint sets. But you can get labels from the table you were given. (--Guessing at what you mean by "label".) Please act on my other comments. In particular, if you don't define your "relational algebra" then you haven't asked an answerable question. Moreover, if you follow the querying link I gave & write your query predicate then you can map that to your algebra. – philipxy Oct 13 '19 at 09:13
  • Those are not opeator definitions, those are operator names. Please see & act on my earlier comment & define "relation" & your operators & give an example so that we don't have to define one to get specific. PS Please don't insert EDITs/UPDATEs, just make your post the best presentation as of right now. – philipxy Oct 13 '19 at 09:23
  • I edited the question adding which operations are permitted. That's all I can do yo define the RA, as I don't have a book. I just needed to check if the answer was empty because I just need to show the ships that have the maximum number of dates . If there is any ship with three dates, then I have finished, but if there isn't I need to proceed. – Seven Oct 13 '19 at 09:27
  • If you can't define your terms, you are not saying anything. If you are "permitted" then whoever gave you this assignment also told you what reference materials to use. – philipxy Oct 13 '19 at 09:28
  • That' s what I was trying , select the ships with three dates and then "check" if this set is empty. But this approach seems impossible . – Seven Oct 13 '19 at 09:30
  • Your last comment is no clearer than what you wrote before. (And now, what does "proceed" mean? You are not explaining yourself.) Also--Putting words in scare quotes does not make clear the idiosyncratic specific meaning that you didn't write out. – philipxy Oct 13 '19 at 09:32
  • Which of the operators name don't you understand? I cannot write here the whole explanation of everything, but I think is pretty standard. I don't have any reference, just the explanation in class . Sorry – Seven Oct 13 '19 at 09:37
  • "There are many RAs (relational algebras). They differ in operators & even what a relation is. Give a reference and/or definition for yours. Eg textbook name, edition & page." I'm done. – philipxy Oct 13 '19 at 09:38
  • I don't really know what a relation is, formally. So, probably my learning is not the best. I'm not making myself clear because English is not my first language, I'm sorry for that too. And thank you, I know you are trying to help . But I truly don't have any reference . – Seven Oct 13 '19 at 09:50

1 Answers1

1

The purpose of this exercise is to avoid aggregate operators or grouping. It'll be a good demonstration of why those are useful.

So you're saying that for each id_ship in the given table, there might be 3, 2, or 1 occurrences. Presumably for your "triple cartesian product" (which'll be a self-product with renaming) you're going to produce a row with three dates ascending left-to-right (so you can check the self-product doesn't duplicate dates).

Ok then for the 2-occurrence cases, you need a double cartesian product, dates ascending left-to-right. Exclude those id-ships that already appear in the triples.

For the 1-occurrence case, exclude those id-ships that appear in either of the above.

Then UNION together the three results:

SELECT id-ship, 3 AS count
FROM occ3
UNION
SELECT id-ship, 2 AS count
FROM occ2
UNION
SELECT id-ship, 1 AS count
FROM occ1 ;

Now you have a conventional group-style table with a count.

Ah, but you wanted RA not SQL? Then we bump into the difficulties of which version of RA. The versions differ in what operators are available. And for example the 'granddaddy' Codd 1972 version didn't even include rename, so you couldn't even produce that triple Cartesian Product.

All versions support UNION OK; all support projection, which is what you need to get the id-ship out of the 3 separate query results.

Supposing your RA supports relation literals: {{count 3}} is a (singleton) set of tuples, each tuple being a set of attribute name-value pairs

(pi<id-ship>(occ3) x {{count 3}})
UNION
(pi<id-ship>(occ2) x {{count 2}})
UNION
(pi<id-ship>(occ1) x {{count 1}})

If your RA doesn't support relation literals, it might support an EXTEND operation

AntC
  • 2,623
  • 1
  • 13
  • 20
  • Check the edit. I'm afraid my relational algebra doesn't have an extend operation nor relation literals. – Seven Oct 13 '19 at 06:38
  • If you think of something, please let me know – Seven Oct 13 '19 at 06:56
  • The query requested is for modal ids. Let tN(i) be "[i] occurs N or more times". We're told ~exists i t4(i). Then modal(i) is t3(i) or t2(i) & ~exists i t3(i) or t1(i) & ~exists i t2(i). – philipxy Oct 14 '19 at 09:28