UML class diagram -> SQL-DBMS tables

From: karl wettin <wettin_at_users.sourceforge.net>
Date: Mon, 6 Oct 2003 02:57:02 +0200
Message-Id: <20031006025702.353c70ec.wettin@users.sourceforge.net>

        

Group,

I'd like to hear your opinions on my way of representing an UML class diagram as SQL-DBMS tables.

<http://snigel.dnsalias.net/snigelwiki/translating_20class_20diagram_20to_20SQL_20tables>

This is the content of above Wiki article:

[[TableOfContents]]

• Abstract =
  • Each class is represented as a table.
    • Non-multiple attributes are represented as a column in the class table
    • Fixed size multiplicity attributes are represented in their own table
    • Generalized classes share ["PK"] with their super class using an ["SK"].
  • Association relations are represented in their own table, no matter of the multiplicity size.
    • Qualifiers are represented in their own table, since each qualifier is a class.
• SQL table naming convention =
  • Class table representation : {{{Classname}}}
  • Multiple attribute tables : {{{Class name_attributeName}}}
  • Association tables : {{{Classname_leftAssociationEndName_rightAssociationEndName_Class}}}
  • Association tables with association class : {{{Classname_leftAssociationEndName_AssociationClass_rightAssociationEndName_Class}}}
  • Qualifier tables : {{{Classname_rightAssociationEndNameQualifier}}}
• Example =

This is supposed to represent a simple webshop, something I hope everybody can relate to. It contains all associations but realizing super relations, and they are not covered in this draft. Realization usually only means ["polymorphic"] methods, and methods aren't persistent.

• UML class diagram ==

The class diagram follows the ["Silvertejp"] ["ASCII"] ["UML"] notation.

{{{
[WebShop](email:String)--- {0..*} -[Customer(+password:String;+name:String{2..5};)]----[PhysicalAdress(+streetAdress:String{1..5};+zipCode:String;+countryCode:int;)]

[Customer]--- {0..*} -<<>>[ShoppingCart(+created:Date;)]---| {0..*} ->[Product]-- {0..*} {0..*} --[ProductCategory(+title:String;)]<>-- +parent +child {0..*}--[ProductCategory]

                                                           |
                                                           \--[ShoppingCartProductSize(+size:int;)]

[abstract Product(+title:String;+price:float;)]<|-----[Book(+ISBN:String;)] }}}

• SQL-DBMS tables ==

This is ["PostgreSQL"]-specific ["SQL"].

{{{
CREATE TABLE "WebShop"
(

    "PK" int8 PRIMARY KEY
);

CREATE TABLE "WebShop_CustomerQualifier" (

    "PK" int8 PRIMARY KEY,
    "email" varchar(50)
);

CREATE TABLE "Customer"
(

    "PK" int8 PRIMARY KEY,
    "password" varchar(50)
);

CREATE TABLE "Customer_name"
(

    "customer_FK" int8 REFERENCES "Customer" ("PK"),
    "_order" int2,
    "name" varchar(50)

);

CREATE TABLE "PhysicalAdress"
(

    "PK" int8 PRIMARY KEY,
    "zipCode" varchar(12),
    "countryCode" int2

);

CREATE TABLE "PhysicalAdress_streetAdress" (

    "PysicalAdress_FK" int8 REFERENCES "PhysicalAdress" ("PK"),
    "_order" int2,
    "streetAdress" varchar(50)

);

CREATE TABLE "ShoppingCart"
(

    "PK" int8 PRIMARY KEY,
    "created" date
);

CREATE TABLE "ShoppingCartProductSize"
(

    "PK" int8 PRIMARY KEY,
    "size" int4
);

CREATE TABLE "Product"
(

    "PK" int8 PRIMARY KEY,
    "title" varchar(50),
    "price" float4

);

CREATE TABLE "ProductCategory"
(

    "PK" int8 PRIMARY KEY,
    "title" varchar(50)
);

CREATE TABLE "Book"
(

    "SK" int8 PRIMARY KEY REFERENCES "Product" ("PK"),     "ISBN" varchar(50)
);

CREATE TABLE "WebShop_webShop_customer_Customer" (

    "webShop_FK" int8 REFERENCES "WebShop" ("PK"),
    "webShop_CustomerQualifier_FK" int8 REFERENCES "WebShop_CustomerQualifier" ("PK"),
    "customer_FK" int8 REFERENCES "Customer" ("PK")

);

CREATE TABLE "Customer_customer_physicalAdress_PhysicalAdress" (

    "customer_FK" int8 REFERENCES "Customer" ("PK"),     "physicalAdress_FK" int8 REFERENCES "PhysicalAdress" ("PK") );

CREATE TABLE "Product_product_productCategory_ProductCategory" (

    "product_FK" int8 REFERENCES "Product" ("PK"),     "productCategory_FK" int8 REFERENCES "ProductCategory" ("PK") );

CREATE TABLE "ProductCategory_parent_child_ProductCategory" (

    "parent_FK" int8 REFERENCES "ProductCategory" ("PK"),     "child_FK" int8 REFERENCES "ProductCategory" ("PK") );

CREATE TABLE "ShoppingCart_shoppingCart_ShoppingCartProductSize_product_Product" (

    "shoppingCart_FK" int8 REFERENCES "ShoppingCart" ("PK"),
    "ShoppingCartProductSize_FK" int8 REFERENCES "ShoppingCartProductSize" ("PK"),
    "product_FK" int8 REFERENCES "Product" ("PK")

);
}}}

karl

-- 
http://sf.net/projects/silvertejp/ 

[abstract Human]<|--+--[Woman]<>-- +mother +child {0..*} --[Human]
                    \--[Man]<>-- +father +child {0..*} --[Human]

"arghhh .. it's all in geek" - objectmonkey.com 

Received on Sun Oct 05 2003 - 19:57:02 CDT