0

I need to define a constraint where tuples in a booking table can only have a value in musician (foreign key attribute from musician table) or actor (foreign key attribute from actor table), and must have one of these, but not both. At first I came up with this solution -

1. select any tuple from booking, call it x;
2. project x's musician column, call it y;
3. project x's actor column, call it z;
4. count(y) + count(z) = 1;

This works but also unintentionally imposes the constraint that the 'empty' booking's musician and actor columns cannot contain an empty string. How can I fix this issue?

P.S. I'm aware that count() isn't always part of relational algebra but I am permitted to use it for this purpose.

philipxy
  • 14,867
  • 6
  • 39
  • 83
K.R.Kernspecht
  • 9
  • 1
  • I would not know how to achieve what you want. I would perhaps just have a relation called 'performers' whose one column is the type of the performer (musician, actor). The impose the unique constraint for the association between bookings and performers. – Elias Toivanen Mar 17 '21 at 19:48
  • There are many RAs (relational algebras). They differ in operators & even what a relation is. Give definitions & a reference for yours. Eg textbook name, edition & page. Nested RA calls form a programming language. So give as much of a [mre] as you can, even if you are not actually running code. But--Google 'run relational algebra online'. Please show what parts you are able to do. See [ask], other [help] links, hits googling 'stackexchange homework' & the voting arrow mouseover texts. – philipxy Mar 17 '21 at 23:04
  • "select any tuple" is not an algebra expression. You're not clear. A constraint is a condition & isn't an algebra result--What is allowed in a constraint? PS You seem to want that musicians minus people is empty & actors minus people is empty & musicians intersect actors is empty. PS When giving a business relation(ship)/association or table (base or query result), say what a row in it states about the business situation in terms of its column values. PS What does your list have to do with empty strings? Please don't say something works but doesn't work. Use enough words to be clear. – philipxy Mar 18 '21 at 00:07
  • [Re relational querying.](https://stackoverflow.com/a/24425914/3404097) – philipxy Mar 18 '21 at 00:07

1 Answers1

2

Problem

The obstacles you are facing are these:

  1. no clear separation between data analysis and problem or process analysis
  2. resorting to relational calculus or any other theoretical concept to sole a practical (eg. data modelling) problem.
  3. you are making assumptions on dependencies (or experiencing problems with) where the referred thing is not yet clearly defined

Solution

The solutions are:

  1. first, model the data, and only as data, without regard to what you need to do in any given Process
    • the Data Model should reflect reality, the real world.
  2. understand and appreciate the theory, but implement using practical methods. That is, straight Relational Data Modelling using the Standard for Relational Data Modelling, IDEF1X.
    • btw, "There are many RAs" is incorrect: there is just one Relational Calculus, by Dr E F Codd. Sure, there are many pretenders after him, but Codd's RA is the only one that is complete; resolved; universally known; and accepted. philipxy is one of those, they hate Codd.
  3. finish the Data Model properly. Define the referred thing reasonably, before attempting to define the dependent thing.
    • Before you can model a Booking for exclusively {Actor|Musician}, you need to model {Actor|Musician} ... which is a Person
    • a Person can be {Actor|Musician|Both}, ie. non-exclusive
    • but the Booking for {Actor|Musician} needs to be exclusive.

Data Model

Easily modelled in the Relational paradigm. As a consequence, the SELECT is simple and straight-forward.

The Data Model in IDEF1X/ER Level (not ERD) is:

Booking Data Model

  • Notice how it is not a RA issue, but a Data Modelling issue. In two hierarchic locations.

Note

  • The Standard for Relational Data Modelling since 1983 is IDEF1X. For those unfamiliar with the Standard, refer to the short IDEF1X Introduction.

  • For full definition and usage considerations re Subtypes, refer to Subtype Definition.

PerformanceDBA
  • 32,198
  • 10
  • 64
  • 90