ER Diagram translation to tables

database-design

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)
----------        -----------

Best Answer

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

Related Question