Search code examples
databaseoracle-databasedatabase-designoracle10gpowerdesigner

Mandatory relationship m:n

I have a conceptual scheme (Power Designer) for school homework and between the tables torrent and server I have a mandatory relationship (a torrent must have at least one server). I generate the physical model, then the code (Oracle 10g) and then when I insert new row to torrent and there's nothing yet in the table connecting torrents with servers, so the torrent doesn't actually have any server as it should. The generated code doesn't change when I change if it's mandatory.

  1. What is it there for then in the conceptual model, when it's for nothing?
  2. What can I do so a torrent must have at least one server?

Conceptual model

Solution

  • You would need to understand the reasons for producing a conceptual, logical and physical model.

    A Conceptual Model is a representation of the area of interest in a form that is understood by users. It will consist of classes of entities with attributes and the business rules regarding these. As well as diagrams, natural language descriptions will be needed to fully explain these points. If all the interested parties agree that the conceptual model completely documents the area of interest then it has fulfilled its purpose.

    A Logical Model formalizes the Conceptual Model into data structures and integrity constraints. The Logical Model may be presented as a Relational Data Model (RDM). In which case all the data structures and integrity constraints will be formally represented only using mathematical relations. It is database management system agnostic.

    For example, you could define an integrity constraint such that:

    { t(ID_TORRENTU) | t∈v(TORRENT) } = { o(ID_TORRENTU) | o∈v(OBSAHUJE) }

    i.e. the set of ID_TORRENTU values in TORRENT equals the set of ID_TORRENTU values in OBSAHUJE.

    The Physical Model is a representation of the Logical Model on a specific database management system. You could implement this integrity constraint following the SQL standard with a CREATE ASSERTION statement:

    CREATE ASSERTION torrent_obsahuje AS CHECK ( NOT EXISTS ( SELECT t.id_torrentu FROM torrent t WHERE NOT EXISTS (SELECT NULL FROM obsahuje o WHERE o.id_torrentu = t.id_torrentu) ) )

    However, SQL DBMSs - including Oracle - do not support the CREATE ASSERTION statement. In order to implement this integrity constraint on Oracle you would need to code it yourself. There are various methods by which this could be achieved including writing custom triggers or creating a materialized view. There are various resources on the internet which explain in detail how you may implement complex integrity constraints in Oracle.

    The methodologies are quite complex and the automated tools you are using would not be able to produce the code required to implement this complex integrity constraint.