An apparent equivalence of the category of affine schemes over $S$ and the category of quasi-coherent $\mathcal{O}_S$-algebras

No, it's not true in general (EGA 2, (1.2.3)).

The following example is taken from EGA 2, (1.3.3). Over a field $K$, let $S$ be the affine plane with a doubled origin. Then $S$ is the union of two affine open subsets $Y_1$ and $Y_2$, each of them is isomorphic to the affine plane, glued along the complementary subset of their origin. In particular, $Y_1$ is affine, but the open immersion $j\colon Y_1\to S$ is not an affine morphism because the inverse image of $Y_2$ is $Y_1\cap Y_2$, the plane minus the origin, which is not an affine scheme.

On the other hand, if $S$ is separated, then the intersection of two open affine subschemes is affine, and this will imply that your desired result holds true (see EGA 1 (5.5.10)).

Finally, the other direction does not work either: every scheme, be it affine or not, is affine over itself.


What is true is that there is an antiequivalence between the category of schemes affine over $S$ (that is $S$-schemes for which the preimage of an open affine of $S$ is an open affine) and quasi-coherent $\mathcal{O}_S$-algebras.

The anti-equivalence is realized by the pushforward of the structure sheaf and the relative spectrum (see Exercise 5.17 in Hartshorne's "Algebraic Geometry"). This is of course not the result you hoped for, but maybe it is still interesting.