------------------------------------------------------------------------

typische Multiplizitäten in UML-Klassendiagrammen: 0..1, 1, 0..*, 1..*

------------------------------------------------------------------------

+-----+        LiegtIn       +----+
|Stadt|----------------------|Land|
+-----+ * stadt       1 land +----+


create table Land(
  name text primary key,
  ...);

create table Stadt(
  name text primary key,
  ...
  land text not null references Land,
  ...);

------------------------------------------------------------------------

+-------------------------+                        +-------------------+
|          Auto           |     FahrzeugHalter     |       Person      |
+-------------------------+------------------------+-------------------+
|fahrgestnr:String <<key>>|* fahrzeug   0..1 halter|name:String <<key>>|
|marke:String             |                        |gebdat:Int <<key>> |
+-------------------------+                        |adr:String         |
                                                   +-------------------+

create table Auto (
  fahrgestnr text primary key,
  marke text,
  name text,      -- halter_name
  gebdat integer, -- halter_gebdat
  foreign key (name, gebdat)
    references Person (name,gebdat) );

create table Person (
  name   text,
  gebdat integer,
  adr    text,
  primary key (name,gebdat));

------------------------------------------------------------------------

-UML- Beispiel 0..1 Multiplizitaet

class Stud
attributes
  name String <<key>>
  adr  String
end

class Account
attributes
  loginId       String <<key>>
  dateLastLogin String
end

association Stud_Account
  Stud    [0..1]
  Account [0..1]
end

-SQL-Option-1-

create table Stud (
  name    text primary key,
  adr     text,
  loginId text references Account)

create table Account (
  loginId       text primary key,
  dateLastLogin text)

-SQL-Option-2-

create table Stud (
  name text primary key,
  adr  text)

create table Account (
  loginId       text primary key,
  dateLastLogin text,
  name          text references Stud)

-SQL-Option-3-

create table Stud (
  name    text primary key,
  adr     text,
  loginId text references Account)

create table Account (
  loginId       text primary key,
  dateLastLogin text,
  name          text references Stud)

Benötigt würde ferner ein Constraint: Das Constraint muss
sicherstellen, dass, wenn man von einem Stud-Tupel zum referenzierten
Account-Tupel geht und von dort wieder zum Stud-Tupel, man wieder bei
Start-Stud-Tupel landet. Analog auch die andere Richtung bei Start mit
Account-Tupel.

------------------------------------------------------------------------

create table products (
  prod_no text primary key,
  price   integer);

create table orders (
  ord_no   integer primary key,
  ship_adr text);

create table order_item (
  prod_no  text    references products on delete restrict,
  ord_no   integer references orders on delete cascade,
  quantity integer,
  primary key (prod_no,ord_no));

+----------------------+                  +------------------+
|       products       |                  |      orders      |
+----------------------+---------+--------+------------------+
|prod_no:String <<key>>|* prod   |   * ord|ord_no:Int <<key>>|
|price:Int             |         |        |ship_adr:String   |
+----------------------+         |        +------------------+
                          +------+-----+
                          | order_item |
                          +------------+
                          |quantity:Int|
                          +------------+

------------------------------------------------------------------------