CODEWITHSUNDEEP
databaserelational-databaserelational-algebra
K.R.Kernspecht
K.R.Kernspecht

Reputation: 9

Either or relational algebra enterprise constraint

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.

Upvotes: 0

Views: 253

Answers (1)

PerformanceDBA
PerformanceDBA

Reputation: 33808

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.

Upvotes: 2

Related Questions