UML class diagram -> SQL-DBMS tables
Date: Mon, 6 Oct 2003 02:57:02 +0200
Message-Id: <20031006025702.353c70ec.wettin_at_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.
- Each class is represented as a table.
- 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.comReceived on Mon Oct 06 2003 - 02:57:02 CEST