Multidimensional Entity Relationship Diagrams

Entity relationship diagrams show how data in a relational database is related.

e.g http://www.databaseanswers.org/data_models/index.htm

e.g

A car has four wheels so have the entity relationship.

 
           has  
  car ---1-----4--- wheels


           has  
  car ---1-----Many--- windows

 

There are loads of references on the web and a number of notation formats.

A project I am working of relates items with a criteria that has a list of 4 components.

There is a item called ControlGroup which has sibblings called Controllers.

so we have the relationship:

 
                    has   
  ControlGroup ---1-----Many--- Controllers

 

Now what makes the 1 to many relationship?

In the car example, it was an attribute of the wheels.

The database tables may be:

 
--
-- SQL example
--  Created into car.db
--  sqlite3 car.db
--
--
--



 CREATE TABLE "Car" (
   carID text ,    -- PrimaryKey  
   unique( carID )

 );

 CREATE TABLE "Wheels" (
   wheelID text ,   -- PrimaryKey   
   carID text ,     -- ForeignKey 

   position text ,
   pressure number ,

   unique( wheelID )
 );

 CREATE TABLE "Windows" (
   windowID text ,   -- PrimaryKey    
   carID text ,      -- ForeignKey

   position text ,

   unique( windowID )
 );


 

 

--
--  Inserts into car.db
--  sqlite3 car.db
--  .read carIns.sql
--

INSERT INTO "Car"     VALUES(   'car1' );
INSERT INTO "Wheels"  VALUES(   'car1_wheel1',  'car1',  'fl',  31.0 );
INSERT INTO "Wheels"  VALUES(   'car1_wheel2',  'car1',  'fr',  31.0 );
INSERT INTO "Wheels"  VALUES(   'car1_wheel3',  'car1',  'fl',  31.5 );
INSERT INTO "Wheels"  VALUES(   'car1_wheel4',  'car1',  'br',  31.3 );

INSERT INTO "Windows" VALUES(   'car1_window1',  'car1',  'windscreen');
INSERT INTO "Windows" VALUES(   'car1_window2',  'car1',  'rearscreen');
INSERT INTO "Windows" VALUES(   'car1_window3',  'car1',  'drivers');
INSERT INTO "Windows" VALUES(   'car1_window4',  'car1',  'passengers');



-- ==================================


INSERT INTO "Car"     VALUES(   'car2' );
INSERT INTO "Wheels"  VALUES(   'car2_wheel1',  'car2',  'fl',  31.0 );
INSERT INTO "Wheels"  VALUES(   'car2_wheel2',  'car2',  'fr',  31.0 );
INSERT INTO "Wheels"  VALUES(   'car2_wheel3',  'car2',  'fl',  31.5 );
INSERT INTO "Wheels"  VALUES(   'car2_wheel4',  'car2',  'br',  31.3 );

INSERT INTO "Windows" VALUES(   'car2_window1',  'car2',  'windscreen');
INSERT INTO "Windows" VALUES(   'car2_window2',  'car2',  'rearscreen');
INSERT INTO "Windows" VALUES(   'car2_window3',  'car2',  'drivers');
INSERT INTO "Windows" VALUES(   'car2_window4',  'car2',  'passengers');


select * from Car,Windows where Car.CarId == Windows.CarID ;


select * from wheels,windows where wheels.carID='car1' AND wheels.carID = windows.carID;