3

I am trying to translate the ISA relationship in this ER diagram to database tables, and I am not sure how to enforce the total and disjoint participation constraint of Laptop and Desktop in Computer. (There are other attributes, but they are not shown here for simplicity.) Here are some options I've explored:

a) Have just one table for Computer, with the attributes of the subtypes in the parent entity. This is not an option, since some functional dependencies in the next part of the question forbid this. There isn't a "type" flag attribute in the diagram either.

b) Two tables, one each for Laptop and Desktop, and pull the attributes from Computer into the separate tables. I can't do this, since it would destroy the relationships that Computer has with other entities.

c) One table each for Computer, Desktop, and Laptop, with foreign key constraints on cid. I don't like this option, since it does not enforce the total and disjoint participation constraint.

This is a practice exercise, but there are no solutions. I would greatly appreciate any help!

                           /  \
        ------------     /      \    ---------
 (cid)--| Computer |====/Supplies\---| Store |
        -----||-----     \     /     --------- 
             ||   \        \ /
             ||    \
             ||     \     / \        ---------------  
            /d \     \--/Fixes\------| TechSupport |
(battery)   -----        \   /       ---------------
  \        /    \         \ /
--------- /      \ ----------
| Laptop |        | Desktop |--(monitor)
----------        -----------
  • The disjoint constraint is easy to implement with (your option 3) and a "type" attribute in all 3 tables (Computer, Laptop, Desktop). The "total" constraint (if I understand well what you mean), the constraint that a Computer must be in exactly one of either Laptop or Desktop is not easy to do with DRI alone. If the DBMS has implement deferrable constraints, you can do it (See option 3 here: How to create multiple one to one's. If not, it can be enforced through transactions or out of the database. – ypercubeᵀᴹ Feb 23 '13 at 10:21
  • The ER Diagram in the question doesn't have a type attribute, so I don't think I am allowed to add it to the tables. But thanks for the advice. :) – DBGuest Feb 23 '13 at 10:30
  • 1
    I think this is the best way (with a "type" attribute). The link above however does it without. Using nullable desktop_id and laptop_id in the Computer table. – ypercubeᵀᴹ Feb 23 '13 at 10:38
  • 1
    a) [...]This is not an option[...] b) [...]I can't do this[...] c) [...]I don't like this option[...] – Bogdan Sahlean Feb 23 '13 at 11:19
2

Enforcing option (c), using a type attribute:

CREATE TABLE Computer
( computerID INT NOT NULL
, computerType CHAR(1) NOT NULL
, PRIMARY KEY (computerType, computerID)
, CHECK (computerType IN ('D', 'L'))
) ;

CREATE TABLE Laptop
( computerID INT NOT NULL
, computerType CHAR(1) NOT NULL
, battery WHATEVER
, PRIMARY KEY (computerType, computerID)
, CHECK (computerType = 'L')
, FOREIGN KEY (computerType, computerID)
    REFERENCES Computer (computerType, computerID)
) ;

CREATE TABLE Desktop
( computerID INT NOT NULL
, computerType CHAR(1) NOT NULL
, monitor WHATEVER
, PRIMARY KEY (computerType, computerID)
, CHECK (computerType = 'D')
, FOREIGN KEY (computerType, computerID)
    REFERENCES Computer (computerType, computerID)
) ;

and using deferred foreign key constraints on nullable columns:

CREATE TABLE Computer
( computerID INT NOT NULL
, laptopID INT NULL                                    -- these 2 columns 
, desktopID INT NULL                                   -- are NULLable
, PRIMARY KEY (computerID)
, UNIQUE (laptopID)
, UNIQUE (desktopID)
, CHECK ( laptopID IS NOT NULL AND desktopID IS NULL   -- but only one of them 
       OR desktopID IS NOT NULL AND laptopID IS NULL   -- is NULL
        )
, CHECK (laptopID = computerID)                        -- and the non-NULL one is
, CHECK (desktopID = computerID)                       -- equal to computerID
) ;

CREATE TABLE Laptop
( laptopID INT NOT NULL
, battery VARCHAR(20) NOT NULL
, PRIMARY KEY (laptopID)
, FOREIGN KEY (laptopID)
    REFERENCES Computer (computerID)
) ;

CREATE TABLE Desktop
( desktopID INT NOT NULL
, monitor VARCHAR(20) NOT NULL
, PRIMARY KEY (desktopID)
, FOREIGN KEY (desktopID)
    REFERENCES Computer (computerID)
) ;

ALTER TABLE Computer
  ADD CONSTRAINT Laptop_Computer_FK
  FOREIGN KEY (laptopID)
    REFERENCES Laptop (laptopID)
    DEFERRABLE INITIALLY DEFERRED
, ADD CONSTRAINT Desktop_Computer_FK 
  FOREIGN KEY (desktopID)
    REFERENCES Desktop (desktopID)
    DEFERRABLE INITIALLY DEFERRED ;

Tested in Postgres, at SQL-Fiddle

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.