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_rightAssociati onEndName_Class}}}
* Association tables with association class : {{{Classname_leftAssociationEndName_AssociationCla ss_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(+createdate]---| {0..*} ->[Product]-- {0..*} {0..*} --[ProductCategory(+title:String]<>-- +parent +child {0..*}--[ProductCategory]

[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_PhysicalAdre ss"
(
"customer_FK" int8 REFERENCES "Customer" ("PK"),
"physicalAdress_FK" int8 REFERENCES "PhysicalAdress" ("PK")
);

CREATE TABLE "Product_product_productCategory_ProductCatego ry"
(
"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

karl wettin <wettin (AT) users (DOT) sourceforge.net> wrote


www.odtug.org

There is an article in a recent journal about using object views as a
layer on top of your relational database that maps to classes. Its
oracle specific, but Id assume that other vendors have similiar
implementations.

I do NOT like the idea of mapping classes to tables. Makes the
relational model too complex. A middle layer is better.

one thing to be careful of. Most vendors are using CBOs now and
heavily 'layered' views tend to have performance issues in a CBO
environment.
If you go 5-7 layers deep with views and make them complex. So mapping
needs to be cautious and dont over do it.

On 8 Oct 2003 06:00:14 -0700
rgaffuri (AT) cox (DOT) net (Ryan Gaffuri) wrote:

I suppose you mean odtug.com? It sounds really interesting, but the $125
fee is a bit too much for one article.

I'm up for that, but exactly what would that middle layer do?

Basically, I'm trying to simulate an OODBMS in an SQL-DBMS.

I've been trying to find information abount Common Business Objectss, but
can't seem to find anything concrete. Could you please give me a small
abstract description?



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

After takin a swig o' Arrakan spice grog, karl wettin <wettin (AT) users (DOT) sourceforge.net> belched out...:
There are some scripts available to transform Dia "UML class diagrams"
back and forth to/from SQL schemas.

There are numerous links to relevant tools here:
<http://www.lysator.liu.se/~alla/dia/links.html>

I'm not sure that it's an evidence of there being a perfect mapping
between "all of SQL" and "all of UML class diagrams;" instead, it
seems to be that you can kludge each one enough to be a _reasonable_
expression of the other...
--
output = ("aa454" "@" "freenet.carleton.ca")
http://cbbrowne.com/info/rdbms.html
(THASSERT (HACKER RG))
-- Example of PLANNER statement.