1

I am trying to study object-relational databases, and am having a really hard time to find information on it and understand the concept. I found only some examples. Probably because English is not my first language.

I want to be able to make an object-relational database design model in the form of a UML class diagram and then create the object-relational (SQL-99) tables in Oracle. And I don't know how to do that.

This is a navigational model example (I think) : enter image description here

Gerd Wagner
  • 5,481
  • 1
  • 22
  • 41
MAR1
  • 29
  • 5
  • 2
    The term _naviagtional model_ has no ISO definition. So what are you after? And what type of diagram is the one you show? – qwerty_so Jan 05 '22 at 20:36
  • @qwerty_so i really don't know the name in english. But i thing the name is Navigational model. I wan't to convert from uml to it, so i was wondering how to do that. – MAR1 Jan 06 '22 at 03:40
  • 1
    I've renamed the term "navigational" in your question to "object-relational" model (corresponding to SQL-99) because that's what your question really is about. – Gerd Wagner Jan 08 '22 at 19:59

1 Answers1

1

Navigational models is a legacy of obsolete databases tachnologies that have been supplanted by the relational model:

  • The prehistoric hierarchical model allowed to group records in a tree. So every record has pointer to the related records. Navigation goes top down and was as far as I know unidirectional.
  • The less prehistoric network model was an alternative where navigation between records was more powerful, and not necessarily limited to tree structures. It gained some popularity as it could relate records very efficiently in a time where relational databases were not very performant on smaller computers (remember Oracle 5 had a table locking mecanism!)

Both where based on fixed, structured records, and the navigation needed to be fixed up-front. THis is the past. Don't go-there if you want to learn modern ORM or NoSQL. The translation from an UML class diagram to a database model is straightforward. If you need to subdivide large classes into smaller one (like Employee and Address), it means that your classes are too large. Fix it in the original UML model: decompose the classes in smaller classes.

UML allow to make associations navigable (but it's not useful if you target a relational database in the end). THere are then a coupe of techniques that allow you to easily build the tables from that model, using for example identity fields, foreign key mapping (for one-to-one or one-to-many) or association table mapping (for many-to-many). But there are full articles or books on these techniques and it would be too long to develop this here.

P.S: you need to improve your English. French literature on the topic is too poor. I can tell from my own experience. ;-)

Christophe
  • 68,716
  • 7
  • 72
  • 138
  • While the Hierarchical and Network DB models are historical (and, in this sense, obsolete), the paradigms of Object DBs and Object-Relational (SQL99) DBs are not. – Gerd Wagner Jan 08 '22 at 20:01
  • Notice that navigation in a UML class model is not necessarily related to UML's "navigability" arrows, but can be provided by association ownership dots and the reference properties they stand for. E.g., in an OOP language, expressions like person1.father.sister are navigational. In an RDB, you navigate via foreign keys. – Gerd Wagner Jan 08 '22 at 20:12
  • @GerdWagner Thank you for the clarification. Indeed, my point about obsolete DB models. Of course, navigating in an object db or in an ORM is still relevant. My point is more that modeling should be done in UML and not reintroducing some old-school representations. And while an ORM doesn't need explicit navigation in UML (I think bidirectional FK navigation is explained in one of the links, or at least the book), other technologies, such as graph db or a JSON (or better a BSON) document DB may require more thoughts about it ;-) – Christophe Jan 08 '22 at 21:23
  • Please can you show me how can i learn this (books or sites) ? should i look up how to convert from UML class diagram to Object-relational model (sql-99) ? – MAR1 Jan 09 '22 at 10:05
  • 1
    @MAR1 Here a very [quick intro in another answer](https://stackoverflow.com/a/70046328/3723423). Here a more comprehensive [site about UML and databases](https://web.csulb.edu/colleges/coe/cecs/dbdesign/dbdesign.php?page=intro.html) and here [some tips](http://www.agiledata.org/essays/umlDataModelingProfile.html#TablesEntitiesViews) for db modeling (i.e. an intermediate model, if useful). – Christophe Jan 09 '22 at 10:35
  • For books, I like the [PEAA](https://martinfowler.com/books/eaa.html) but it's not its core topic and doesn't go in full depth. Maybe @GerdWagner could recommend some ;-) – Christophe Jan 09 '22 at 10:36