Is it possible to transform an class diagram to an relational database diagram?
A class diagram represents a system using the object model. A relational database diagram represents a system of data using the relational model. There are significant differences between the ways these two models will present the same system. And a data model does not model behavior. It only models data.
There is, however, a modeling system that is sort of halfway between a class diagram and a relational diagram. Its called an E-R diagram, where E-R is short for Entity-Relationship. In the E-R model, the entire subject matter is analyzed into "entities", which can be persons, places, or things that have an identity. They can even be intangible things, like a bank account. Relationships involve two or more entities, and its assertions about the relationships that make up much of the data in a database. Data values are instances of attributes, and attributes describe either entities or relationships among entities.
Most of the E-R diagrams you'll see in SO are really relational diagrams masquerading as ER diagrams. In a true ER diagram, foreign keys are not present, many-to-many relationships can be diagrammed as a single line, and such things as gen-spec patterns look much the same way as they do in class diagrams. If fact, an ER diagram can be viewed as the projection of an object world onto the world of data only.
If you'll learn ER modeling as a distinct activity from relational modeling, resulting in a different model, you'll find it fairly easy to transform class diagrams into ER diagrams.
From there, transforming ER diagrams into relational diagrams is almost mechanical. Every entity gets a table, many-to-many relationships get their own table. Inheritance and association get special treatment, and so on. Relationships which were treated as abstractions in the world of ER modeling become materialized as foreign keys. The primary key of each table becomes obvious in terms of the key attributes of entities in the ER model.
And what were called "attributes" in the ER model (possibly "properties" in the class model) become "columns" in the relational model.
there are some fancy tools that manage object models, ER models, and relational models all in the same tool, and can move between these models for you. One of them, "Data Architect" was very good but very pricey a few years back.
Not sure what you mean by a "relational database diagram". If you refer to a SQL script with the DDL sentences to create the relational schema for your model, then maybe you can just see how tools do this transformation (e.g. check this online UML to SQL code generator http://modeling-languages.com/content/uml2db-full-code-generation-sql-scripts-databases)
Most of the transformations rules are straightforward (class-> table, attribute ->column, association -> foreign key,...) but you can play with UML diagrams with inheritance and association classes and see how this is translated.
Another option that I use is add Database stereotypes in my class diagram. It generates Java persistence annotation in my code. Finally I generate my code from my Java code with Hibernate.
It works really well !!