The Universe - A Simplified Database Model
This is a play with a simplified model of The Universe, just to illustrate the power of ER Modeling. Let us start with the above model of The Universe:
It looks rather impossible to fit in The Universe here. And, well, although we are allowed to use many-to-many relationships in an early stage of the Analysis Phase, we must at some point resolve them:
They are a violation of the First Normal Form. The method for doing this is to create a new entity and two one-to-many relationships towards that new entity: We call this an "Intersection Entity" (sorry about the language). We also find that some renaming would do well:
Look at the left relationship. We can read this as:
:A given Universe element may have one or more Element Parts; while a given element part must belong to one and only one Universe element.
We can read the right relationship as:
A given Universe Element may be a part of one or more sets of Element Parts, but only once for a given Universe Element set.
This is not easy reading, I admit, so we have to visualize it a little. Let us first create the tables:
A little explaining first: The tables were generated from the ER model,
using an internal naming standard. Let us keep it. What happened was
that the ELEMENT_PARTS table (plural name as table) got two columns,
one for each relationship. In this case, they also form the primary key
I go on to concentrate on Europe, inserting:
Enough, now (it's just an example, but you get the picture).
Let us connect the continents together to form The Earth by inserting all continents and The Earth ids into ELEMENT_PARTS. Let us look at the contents of ELEMENT_PARTS:
Now, let us do a SELECT here:
Similarly, we can find countries in Europe:
This little structure of the Universe represented by two
tables can store elements and how they relate to each other, no matter
what volume we are talking about. We navigate and make our selections
based on the ID of the owning (or master) Universe element.
We need to classify our Universe elements:
Now we can ask the database:
In the real world, a system for organizing the Universe would
contain more entities than these three (you think so too?), but this is
how you may start your Entity Relationship modeling of the Universe in
the Analysis Phase.
Free eBookSubscribe to my newsletter and get my ebook on Entity Relationship Modeling Principles as a free gift:
What visitors say...
"I just stumbled accross your site looking for some normalization theory and I have to say it is fantastic.Read more Testimonials
I have been in the database field for 10+ years and I have never before come across such a useful site. Thank you for taking the time to put this site together."
Theory & Practice
SQL Server DBA
Install SQL Server
Database Normalization eBook:
Copyright © www.databasedesign-resource.com / Alf A. Pedersen
All rights reserved.
All information contained on this website is for informational purposes only.
Disclaimer: www.databasedesign-resource.com does not warrant any company, product, service or any content contained herein.
Return to top
The name Oracle is a trademark of Oracle Corporation.