Notation Semantics - ERD - Entities - Domains - SQL


show ERD Notation
       
E_Person . . SQL

          .pk  D_Identifikationsnummer    a_PNr
          .    D_Geschlecht               a_Geschlecht
          .nn  D_Name                     a_Name
          .nn  D_Name                     a_Vorname
       ref.    E_Person                   a_Vater
       ref.nn  E_Person                   a_Mutter
          .nn  S_Adresse                  a_Hauptwohnsitz
          .    S_Adresse                  a_Zweitwohnsitz

          <--  is target of:  E_Person.Mutter
          <--  is target of:  E_Person.Vater

....   E_Student . SQL
               |    E_Person                
            ref.nn  E_Organisation             a_Schule

....   .   E_Studentenheim_Bewohner . SQL
                    |    E_Person                
                    |    E_Student               
                 ref.nn  E_Organisation             a_Studentenheim
                    .nn  D_RaumNr                   a_RaumNr


E_Organisation . . SQL

          .pk  D_Identifikationsnummer    a_ONr
          .nn  D_Name                     a_Name
          .nn  S_Adresse                  a_Zentrale

          <--  is target of:  E_Zweigstelle.Org
          <--  is target of:  E_Studentenheim_Bewohner.Studentenheim
          <--  is target of:  E_Student.Schule


E_Zweigstelle . . SQL

       ref.pk  E_Organisation             a_Org
          .nn  S_Adresse                  a_


S_Adresse
 

          .    D_Name                     a_Ort
          .    D_Name                     a_Strasse
          .    D_Hausnummer               a_Hausnummer
          .    D_Postleitzahl             a_Postleitzahl


end of o.student.er1 ERD




How to read the ERD above:

Prefixes E_, R_, S_, A_, e_ in front of a name show what kind of concept the name x in question is denoting: E_x is an entity class. Note however: We write e_x instead of E_x where we want to say that E_x is occurring in the role of a superclass. If a class E_x1 is a specialization of another class E_x, i.e. if each instance of E_x1 can be also be seen as an instance of type E_x, then e_x is meant to be the group of all attributes of E_x1 defined in E_x already (the set of all inherited attributes). R_x is entity class E_x representing entity associations that are not of cardinality 1:n or n:1 (they need not even be binary relations). A_x is an attribute of an entity class, same as a_x() in C++ or Java. S_x is a complex attribute (i.e. a set of attributes), same as a_x() in C++ or Java. We write a_x where we do not care to see whether attribute x is complex or not. D_x is a domain, i.e. an attribute type. pk says that this attribute is part of the entity's primary key. nn says that this is a NOT NULL attribute. c: is to mark core entity types (types that do not specialize any other type). Types not marked as core entity types inherit structure from their - always unique - direct supertype). A Core Type is an entity type that is not a specialization of another entity type in the model shown. Specialisation hierarchies are shown in this form: ------------------------------------------------- | E_x3 | e_x1 | e_x2 . E_x3 structure not inherited means: E_x3 specializes E_x2 (so that all structure of E_x2 is also structure of E_x3). E_x3 is a direct subtype of E_x2, and E_x2 is a direct subtype of E_x1. Entity Relationship Structure is shown as follows: ------------------------------------------------- E_x1 ->> set of E_x2 a_Name means: Given any object x1 of type E_x1, x1.a_Name() is an instance of a List h_Name defined in the ERD (the Name can be seen as describing the role of the list members in relation to x1). E_x1 . E_x2 x3 (implies: E_x2 <-- E_x1.x3 ) means: Each value E_x1.A_x3 is primary key of an entity of type E_x2, so that ( E_x1, E_x2 ) is a relation of cardinality (m:1). An application may force it to be a relation of cardinality (1:1). E_x1 . D_x2 x3 . S_x2 x3 means: Each value E_x1.A_x3 is an attribute of type D_x2 (resp. S_x2, S_x2 a named set of attributes also specified in this model). R_x1 is a type E_x1 that can be interpreted as a relationship type of a specific dimension n, 2 <= n. Note: Classes E_x without any relation structure --> or <<- tend to be superfluous. They form a self-contained ERD model which quite often does not make sense in isolation. Here is an example: Classes meant to be enumerations such as e.g. E_Category . A_Value D_ValueName . A_Semantics D_Comment . A_ObsoleteSince D_Date need usually not exist in form of a separate table. To model it in form of a domain D_Category together with an attribute A_ObsoleteSince in an E_DomainValues table would be better: E_DomainValues . A_Domain D_DomainName . A_Value D_ValueName . A_Semantics D_Comment . A_ObsoleteSince D_Date To have such a table documenting the semantics of all the values of enumeration domains is recommended and definitely helpful (the fact that most developers do not create this table is simply bad practice).

Notation Semantics - ERD - Entities - Domains - Domain Usage - SQL

       

All Entity Types
( grouped - goto sorted by Name )

       E_Person
       .   E_Student
       .   .   E_Studentenheim_Bewohner
       E_Organisation
       E_Zweigstelle
       S_Adresse

Notation Semantics - ERD - Entities - Domains - Domain Usage - SQL

       

All primary Entity Types
( sorted by Name - goto grouped )


A O P Z

       E_Organisation
       |
       E_Person
       |
       E_Zweigstelle


These are 3 primary Entity Types
( not counting specializations )

       |
       S_Adresse



Notation Semantics - ERD - Entities - Domains - Domain Usage - SQL


Our Physical Domain Implementation
       
Domain          
 
        Datatype
 
D_GeschlechtCHAR(1)
D_HausnummerVCHAR(5)
D_IdentifikationsnummerCHAR(11)
D_NameVCHAR(100)
D_PostleitzahlCHAR(5)
D_RaumNrVCHAR(5)
D_UpdateNrINT


          Enumeration Domain Values are:

          D_Geschlecht:   m
          D_Geschlecht:   w


Notation Semantics - ERD - Entities - Domains - Domain Usage - SQL


Where Domains are actually used


 
        Where Domain Types are used
 

Here you can see whether there are Domain Types no longer in use.


Geschlecht :
.   E_Person
.   E_Student
.   E_Studentenheim_Bewohner
|
Hausnummer :
.   E_Adresse
.   E_Organisation
.   E_Person
.   E_Student
.   E_Studentenheim_Bewohner
.   E_Zweigstelle
|
Identifikationsnummer :
.   E_Organisation
.   E_Person
.   E_Student
.   E_Studentenheim_Bewohner
.   E_Zweigstelle
|
Name :
.   E_Adresse
.   E_Organisation
.   E_Person
.   E_Student
.   E_Studentenheim_Bewohner
.   E_Zweigstelle
|
Postleitzahl :
.   E_Adresse
.   E_Organisation
.   E_Person
.   E_Student
.   E_Studentenheim_Bewohner
.   E_Zweigstelle
|
RaumNr :
.   E_Studentenheim_Bewohner


Notation Semantics - ERD - Entities - Domains - Domain Usage - SQL

, 3 Tables