No, you couldn't use er ist über mich or er ist über mir to imply "He is over me = He is not attached any more to the relationship we used to have". (That could be Er ist über mich hinweg., though.)
So über mir could mean physically higher up (e.g. top bunk in bunk beds) or metaphorically higher up (e.g. your boss is over you in an organisation), but not in the sense of "be over someone" (after a relationship) or "be all over something" (that you are strongly interested in). Nor can it mean "done, finished" as in "the play is over".
In the hotel/restaurant example, I would use über in German, yes.
But for "he is on top of me", I would say er ist auf mir (drauf) if he is physically lying on me -- similarly with das Buch ist auf dem Tisch for "the book is on top of the table".
But with storeys of a building, I guess we don't view them as "sitting on top of" each other but merely as being located above one another.
Same question, my first thought was "over" as well. My propositions cheat sheet says über can mean over, above, and about. I'm curious to know if this is a context thing (in which case either should be a correct answer) or if there is some way to know from the wording that it is above vs over.
The preposition über can be used with either accusative case (corresponding to mich) or dative case (corresponding to mir). When referring to a direction it requires accusative case, and when referring to a location it requires dative case.
A bird is flying toward me from some distance away, passes over my head and keeps going into the distance. I would say Der Vogel fliegt über mich = "The bird flies over me" (accusative, direction).
A bird is flying in circles above my head, remaining above me for some time. I would say Der Vogel fliegt über mir = "The bird flies above me" (dative, location).
Hey, fun tidbit - Duo accepts "Er isst über mir" ("He eats over me") with the listening exercise, but then lists the translation as "He is above me."
I tried it as a test and have reported the error. Yes, I know that someone's hand could slip and put "s" twice, but that error should be pointed out in the answer section. :)<h1>mytwocents</h1>