How to model or query for something that does not exist in relational algebra [closed]

Question

I want to query the id of all apartments that were never rented.

I tried something like this:

    (π a_id (apartments))
-
    (π a_id
    σ from_date Exists ∧ end_date Exists 
    (rental) ⨝ rental.a_id on apartment.a_id (apartment))

But I think I cannot use Exists or null in relational algebra.

How could I do it?

I attach the schema here

Kaikaia

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. Please use text, not images/links, for text--including tables & ERDs. Use images only for what cannot be expressed as text or to augment text. Images cannot be searched for or cut & pasted. Include a legend/key & explanation with an image. Make your post self-contained. – Aside

Please see How to Ask, hits googling 'stackexchange homework' & the voting arrow mouseover texts. What parts are you able to do? Where are you stuck? What did your textbook or the documentation say about anything relevant? PS RA is a programming language--even if you are working by hand. Please in code questions give a minimal reproducible example--cut & paste & runnable code plus desired output plus 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.) PS Google 'execute relational algebra online'. – Aside

What relevant queries can you do that you think are correct? What rows do they return? Why? Then we can help you where you are stuck. It does not help for you to show invalid code because it doesn't mean anything. You need to use language to expain what you are trying to do--what parts you wish you had & how you wish you could use them. PS When giving tables say exactly what a row says about the business situation in terms of its column values when it is in the table. Also when describing a query result. PS Re relational algebra querying.. – Aside

When you mention null & exists, is it because you are thinking of SQL left join on where is not null & where not exists? Do you know what SQL except & Oracle minus are? PS See my answer. – Aside

Answer 1

How we query & reason based on what rows in tables mean

For the most straightforward relational algebra, where a relation has an attribute set as heading & tuple set as body:

Every query expression has an associated (characteristic) predicate--fill-in-the-(named)-blanks statement template parameterized by attributes. The tuples that make the predicate into a true proposition--statement--are in the relation. We cannot update or query about the business situation without being told each base relation's predicate.

We are given the predicates for expressions that are relation (value or variable) names.

Let query expression E have predicate e. Then:

• R ⨝ S has predicate r and s
• R ∪ S has predicate r or s
• R - S has predicate r and not s
• σ p (R) has predicate r and p
• π A (R) has predicate exists non-A attributes of R [r]

Given a relational algebra query, we can apply the above facts from the leaves up to get an expression for its predicate. Given a predicate expression, we can similarly apply the above facts to go the other way; if necessary, we first rearrange to an equivalent expression where every ∪ & - has 2 arguments with the same attributes. (It always works to rearrange per Codd's reduction algorithm to a certain prenex normal form.)

Re relational algebra querying.

Applying that to your query

Presumably apartment ids in Apartment are for apartments & apartment ids in Rental are for rented apartments. Then the unrented apartments are the ones in Apartment but not in Rental. Their ids are the ones in a relational difference between projections of those base relations.

Guessing at the legend/key for your ERD, there is a FK (foreign key) in Rental referencing Apartment. That confirms that an apartment in Rental is also in Apartment. So Apartment ⨝ Rental has the same apartments as Rental. That confirms that you don't need to join; you can just use Rental for rented apartments.

You mention NULL & EXISTS. Maybe you are talking about SQL NULL & EXISTS and/or you are trying to find a relational algebra version of an SQL query and/or you are reasoning in SQL. And/or maybe you are talking about logic EXISTS & whether values exist in columns or tuples.

From common sense about renting & from you not saying otherwise, Rental might be rows where occupant O rented apartment A from date F to date T. But you mention NULL. From common sense & guessing T can be NULL, Rental seems to be rows where occupant O rented apartment A from date F to date T OR occupant O rented apartment A from date F ongoing & T is null.

NULL is a value that is treated specially by SQL operators & syntax. We don't know how your algebra & language treat NULL. In mathematics EXISTS X [p] & FOR SOME X [p] say that a value exists that we can name X that satisfies condition p. SQL EXISTS (R) says whether rows exist in table R. That is whether EXISTS t [t IN R]. When R is (X,...) rows where r, that is whether EXISTS X,... [r].

When R is rows where r, π x (R) is by definition rows where EXISTS non-x attributes of R [r]. So π A (Rental) is rows where EXISTS O,F,T [occupant O rented apartment A from date F to date T OR occupant O rented apartment A from date F ongoing & T is null].

When R is rows where r, σ p (R) is by definition rows where r & p. Rows where occupant O rented apartment A from date F ongoing & T is null is rows where (occupant O rented apartment A from date F to date T OR occupant O rented apartment A from date F ongoing & T is null) & T is null. That's σ T is null (Rental).

When R is rows where r & S is rows where s, R - S is by definition rows where r & NOT s. Suppose Apartment is rows where apartment A has S square feet .... You want rows where EXISTS S,... [apartment A has S square feet ...] & NOT EXISTS O,F,T [occupant O rented apartment A from date F to date T OR occupant O rented apartment A from date F ongoing & T is null]. That's π A (Apartment) - π A (Rental). That's the relation difference at the start of this section.

Aside