2

I've seen this post (How should I model an “either/or” relationship?) but it's not exactly what I'm looking for. both answers are suggesting creating a subtype instead of a relationship.

Say I have an entity MACHINE, and I want to creat a relationship to connect it with another entity OS, call it "installs" or whatsoever. And this OS has 2 subtypes: WINDOWS and MAC (Linux and Unix also work but just for demo purpose they are not included). Not considering virtual machine or double OS, I can only choose one of these 2 subtypes of OS, how should I model this in the entity-relationship model?

demo img

Should I

  • Create 1 relationship between MACHINE and OS. Or

  • Create 2 relationship between MACHINE and WINDOWS, MACHINE and MAC. Or

  • Create 1 ternary relationship between MACHINE, WINDOWS and MAC.

And should I add additional attributes to the entities or the relationship?

Thanks:D

6
  • Do you want to say that WINDOWS and MAC are considered as separate and independent entities which are stored in separate tables?
    – Akina
    Sep 10, 2020 at 4:41
  • @Akina I'm still learning DB so I'm not sure what that means. From what I know, I guess they are separated. Say a Dell Alienware installs a Win10 Home, Alienware would be an instance of the entity MACHINE, win10 home would be an instance of entity WINDOWS, and they have a relationship. But having that relationship also means this Dell machine cannot install Mac. Sep 10, 2020 at 4:57
  • I see 2 entities - MACHINE and OS. So - 2 tables. First - OS table which is self-referenced and stores everything as aa tree (parent-child), i.e. Windows and Mac are root instances, Windows 10 is an instance which have parent = Windows, Windows 10 Home is an instance which have parent = Windows 10, and so on... Second - MACHINE table which references to an instance in OS table, for example John's workstation is an instance which references to an instance Windows 10 Home in OS table.
    – Akina
    Sep 10, 2020 at 5:06
  • I'm not sure you actually have subtypes or are missing an entity to enumerate which operating systems are offered by each manufacturer (and possible versions of each operating system)?
    – user212533
    Sep 10, 2020 at 14:44
  • @bbaird I don't think manufacturer matters, think about choosing an OS to install on a computer, 2 OSs are available, but only 1 can be selected. Both OSs (WINDOWS and MAC in this case) are subclasses of the entity OS, and a relationship is needed to illustrate which OS is selected. Sep 10, 2020 at 21:45

1 Answer 1

0

The most powerful design tool is still natural language, predicates, and constraints. So, when in doubt use a plain text editor. Simply focus on logic, as opposed to jargon (this-that relationship ..). The following is one possible version of this story:

-- Machine type TYP exists.
--
mtyp {TYP}
  PK {TYP}


-- data sample
  (TYP)
-------------------
  ('dell alienware')
, ('macbook pro')
, ('thinkpad')
, ('hp spectre')
-- Operating system OS exists.
--
osys {OS}
  PK {OS}


-- data sample
  (OS)
-------------
  ('windows')
, ('mac os')
, ('linux')
, ('unix')
-- Operating system OS is available for machine type TYP.
--
mtyp_osys {TYP, OS}
       PK {TYP, OS}

FK1 {TYP} REFERENCES mtyp {TYP}
FK2 {OS}  REFERENCES osys {OS}


-- data sample
  (TYP, OS)
-------------------------------
  ('dell alienware', 'windows')
, ('macbook pro'   , 'mac os')
, ('thinkpad'      , 'linux')
, ('thinkpad'      , 'windows')
, ('hp spectre'    , 'windows')
-- Machine number MCH# is of machine type TYP.
--
machine {MCH#, TYP}
     PK {MCH#}
     SK {MCH#, TYP}

FK1 {TYP} REFERENCES mtyp {TYP}


-- data sample
  (MCH#, TYP)
-------------------------------
  (1, 'dell alienware')
, (2, 'dell alienware')
, (3, 'dell alienware')
, (4, 'macbook pro')
, (5, 'thinkpad')
, (6, 'thinkpad')
, (7, 'hp spectre')
-- Operating system OS is installed
-- on machine number MCH#, machine type TYP.
--
install {MCH#, TYP, OS}
     PK {MCH#, TYP}

FK1 {MCH#, TYP} REFERENCES machine   {MCH#, TYP}
FK2 {TYP, OS}   REFERENCES mtyp_osys {TYP, OS}


-- data sample
  (MCH#, TYP)
-------------------------------
  (1, 'dell alienware', 'windows')
, (2, 'dell alienware', 'windows')
, (3, 'dell alienware', 'windows')
, (4, 'macbook pro'   , 'mac os')
, (5, 'thinkpad'      , 'windows')
, (6, 'thinkpad'      , 'linux')

-- at this point nothing is yet installed on machine # 7

Note:

All attributes (columns) NOT NULL

PK = Primary Key
AK = Alternate Key   (Unique)
SK = Proper Superkey (Unique)
FK = Foreign Key
Using suffix # to save on screen space.
OK for SQL Server and Oracle, for others use _NO.
For example, rename MCH# to MCH_NO.

Here is another example of design using natural language, predicates, and constraints to keep a DB in high NF.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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