database - Null in Relational Algebra

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 Exist in relational algebra or null or anything.

How could I do it?

Thanks

I attach the schema here

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.