Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)
----------        -----------
share|improve this question
    
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
add comment

1 Answer

up vote 1 down vote accepted

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

share|improve this answer
    
Thanks for your thoughtful answer. :) –  DBGuest Feb 23 '13 at 11:18
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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