ISO/IEC TR 19583-21:2022
(Main)Information technology — Concepts and usage of metadata — Part 21: 11179-3 Data model in SQL
Information technology — Concepts and usage of metadata — Part 21: 11179-3 Data model in SQL
This document provides a possible instantiation of the registry metamodel specified in ISO/IEC 11179-3 using the SQL database language as specified in ISO/IEC 9075-2.
Technologies de l'information — Concepts et utilisation des métadonnées — Partie 21: Titre manque
General Information
Standards Content (Sample)
TECHNICAL ISO/IEC TR
REPORT 19583-21
First edition
2022-08
Information technology — Concepts
and usage of metadata —
Part 21:
11179-3 Data model in SQL
Reference number
ISO/IEC TR 19583-21:2022(E)
© ISO/IEC 2022
---------------------- Page: 1 ----------------------
ISO/IEC TR 19583-21:2022(E)
COPYRIGHT PROTECTED DOCUMENT
© ISO/IEC 2022
All rights reserved. Unless otherwise specified, or required in the context of its implementation, no part of this publication may
be reproduced or utilized otherwise in any form or by any means, electronic or mechanical, including photocopying, or posting on
the internet or an intranet, without prior written permission. Permission can be requested from either ISO at the address below
or ISO’s member body in the country of the requester.
ISO copyright office
CP 401 • Ch. de Blandonnet 8
CH-1214 Vernier, Geneva
Phone: +41 22 749 01 11
Email: copyright@iso.org
Website: www.iso.org
Published in Switzerland
ii
© ISO/IEC 2022 – All rights reserved
---------------------- Page: 2 ----------------------
ISO/IEC TR 19583-21:2022(E)
Contents Page
Foreword .iv
Introduction .v
1 Scope . 1
2 Normative references . 1
3 Terms and definitions . 1
4 Overview of the relationship between UML Class Diagrams and SQL .1
5 Generating the SQL for the metamodel . 2
5.1 Overview . 2
5.2 General principles for the translation of a UML Class diagram into SQL statements . 2
5.3 Specific approaches taken for the translation of the metadata registry metamodel . 3
5.3.1 Overview . 3
5.3.2 Obligations . 3
5.3.3 Translation of datatypes . 3
5.3.4 Translation of the basic classes . 4
5.3.5 Translation of the <> stereotypes . 4
5.3.6 Translation of the remaining classes . . 5
5.3.7 Translation of specialization hierarchies . 5
5.3.8 Translation of the association classes . 5
5.3.9 Translation of the attributes of the classes. 5
5.3.10 Translation of the associations . 6
5.3.11 Cross-table constraints . 6
6 Example SQL for instantiation of the metamodel . 6
Annex A (informative) Example SQL to instantiate the ISO/IEC 11179-3 metamodel .8
Bibliography .58
iii
© ISO/IEC 2022 – All rights reserved
---------------------- Page: 3 ----------------------
ISO/IEC TR 19583-21:2022(E)
Foreword
ISO (the International Organization for Standardization) and IEC (the International Electrotechnical
Commission) form the specialized system for worldwide standardization. National bodies that are
members of ISO or IEC participate in the development of International Standards through technical
committees established by the respective organization to deal with particular fields of technical
activity. ISO and IEC technical committees collaborate in fields of mutual interest. Other international
organizations, governmental and non-governmental, in liaison with ISO and IEC, also take part in the
work.
The procedures used to develop this document and those intended for its further maintenance
are described in the ISO/IEC Directives, Part 1. In particular, the different approval criteria
needed for the different types of document should be noted. This document was drafted in
accordance with the editorial rules of the ISO/IEC Directives, Part 2 (see www.iso.org/directives or
www.iec.ch/members_experts/refdocs).
Attention is drawn to the possibility that some of the elements of this document may be the subject
of patent rights. ISO and IEC shall not be held responsible for identifying any or all such patent
rights. Details of any patent rights identified during the development of the document will be in the
Introduction and/or on the ISO list of patent declarations received (see www.iso.org/patents) or the IEC
list of patent declarations received (see https://patents.iec.ch).
Any trade name used in this document is information given for the convenience of users and does not
constitute an endorsement.
For an explanation of the voluntary nature of standards, the meaning of ISO specific terms and
expressions related to conformity assessment, as well as information about ISO's adherence to
the World Trade Organization (WTO) principles in the Technical Barriers to Trade (TBT) see
www.iso.org/iso/foreword.html. In the IEC, see www.iec.ch/understanding-standards.
This document was prepared by Joint Technical Committee ISO/IEC JTC 1, Information technology,
Subcommittee SC 32, Data management and interchange.
A list of all parts in the ISO/IEC 19583 series can be found on the ISO and IEC websites.
Any feedback or questions on this document should be directed to the user’s national standards
body. A complete listing of these bodies can be found at www.iso.org/members.html and
www.iec.ch/national-committees.
iv
© ISO/IEC 2022 – All rights reserved
---------------------- Page: 4 ----------------------
ISO/IEC TR 19583-21:2022(E)
Introduction
[1]
ISO/IEC 11179-3 provides a specification for a registry in which information about metadata can be
recorded and maintained.
The metamodel to instantiate such a registry is expressed in text as a conceptual model. This conceptual
model is illustrated with a series of diagrams which use the class diagram notation from the Unified
[2][3]
Modeling Language (UML) .
Instantiaters and users of the registries described in ISO/IEC 11179-3 require further guidance to turn
the conceptual models into concrete instantiations. This document provides a possible instantiation of
the registry metamodel specified in ISO/IEC 11179-3 using the SQL database language as specified in
[4]
ISO/IEC 9075 .
This specimen instantiation is provided to increase the understanding of ISO/IEC 11179-3 and, hence,
to promote its adoption.
v
© ISO/IEC 2022 – All rights reserved
---------------------- Page: 5 ----------------------
TECHNICAL REPORT ISO/IEC TR 19583-21:2022(E)
Information technology — Concepts and usage of
metadata —
Part 21:
11179-3 Data model in SQL
1 Scope
This document provides a possible instantiation of the registry metamodel specified in ISO/IEC 11179-3
using the SQL database language as specified in ISO/IEC 9075-2.
2 Normative references
There are no normative references in this document.
3 Terms and definitions
No terms and definitions are listed in this document.
ISO and IEC maintain terminology databases for use in standardization at the following addresses:
— ISO Online browsing platform: available at https:// www .iso .org/ obp
— IEC Electropedia: available at https:// www .electropedia .org/
4 Overview of the relationship between UML Class Diagrams and SQL
The Unified Modeling Language (UML) provides a family of graphical notations that can be used in the
analysis and design of software systems. The UML is under the control of the Object Management Group
(OMG) and, as such, it is (a) a relatively ‘open’ standard, and (b) firmly rooted in the object-oriented
paradigm for software engineering. The UML is now at Version 2 and is the subject of two international
standards: ISO/IEC 19505-1 and ISO/IEC 19505-2.
Within the UML, the Class Diagram notation is used to represent information (and, hence, data)
requirements for a particular ‘universe of discourse’, a business area or the scope of a proposed
information system.
A UML Object is often defined as a:
construct within a system for which a set of attributes and operations can be specified.
Whilst this is a reasonable definition within the context of object-oriented system development, a more
appropriate definition of an Object for the purposes of this document is a:
representation of something of interest within the universe of discourse about which
information needs to be recorded.
An Object Class in both contexts can then defined as a:
definition of a set of Objects that share the same attributes, associations, and operations.
The Database Language SQL is a, largely, declarative language used to manage structured data held
in a database under the control of a Relational Database Management System (RDBMS). As such, it
1
© ISO/IEC 2022 – All rights reserved
---------------------- Page: 6 ----------------------
ISO/IEC TR 19583-21:2022(E)
[5]
was originally based on Edgar F. Codd’s relational model of data published in 1970 , but its scope has
grown over the years. SQL is the subject of the multi-part set of International Standards, ISO/IEC 9075
series. Most commercial SQL products, however, deviate from the standards to some extent, some more
than others.
5 Generating the SQL for the metamodel
5.1 Overview
The UML (and the Class Diagrams, in particular) and the SQL database language exist in two separate
programming paradigms and there is, therefore, no direct translation from one (the UML) to the other
(SQL). There are, however, approaches that can be taken to achieve a translation. This document uses
one of those approaches to generate a set of SQL statements to instantiate the metadata registry
metamodel, where the SQL statements enable easy reference back to the original UML Class Diagram
and text of the metamodel. This is achieved by using names for the SQL objects that reflect the names
of the UML artefacts and, also, by embedding comments referencing the metamodel within the SQL
statements.
5.2 General principles for the translation of a UML Class diagram into SQL statements
It is good practice to distinguish between SQL keywords and the names given to the SQL objects. One
convention is to use UPPER CASE for the keywords and lower_case (using snake case) for the object
names.
Each UML class is represented by an SQL table. To make correlation to the model easier, the name of a
table that represents a class is the same as that of the class.
Each composite datatype is also represented by an SQL table. To make correlation to the model easier,
the name of the table is the same as that of the datatype.
Each single-valued attribute of a class or a composite datatype is represented by a column in the
appropriate table. The name of this column is the same as that of the attribute in the class or datatype.
The datatype of an attribute column is intuitively selected to be similar to that of the datatype of the
attribute.
If the datatype of an attribute of a class is another class or a composite datatype, the column that
represents that attribute is additionally declared as a foreign key column referencing the relevant table
that represents the class or composite datatype.
Where an attribute is multivalued (that is, it has a multiplicity of [0.*] or [1.*] in the UML diagram)
there are two possible instantiations available. These are:
a) Use one of the collection types, MULTISET or ARRAY, available in SQL.
b) Create a new table, a characteristic table, to hold the multiple values, with each row in the table
having a foreign key referencing the kernel (prime) table and one of the values.
In object-orientation, and, hence, UML, there is no equivalent of the SQL primary key, so each table
that represents a class or a composite datatype has an additional column that is used as a surrogate
identifier. This column then becomes the primary key for the table.
UNIQUE or CHECK constraints may be added to a table where required. The latter are used, for example,
to control the valid values for a column or to control which columns should, or should not, take values in
different circumstances.
Specialization hierarchies (superclasses and their subclasses) can be instantiated in one of two ways
using SQL structures.
1) It is possible to integrate all the classes (the superclass and its subclasses) into one table, named
after the superclass, which includes a column for each attribute of the superclass, with those
2
© ISO/IEC 2022 – All rights reserved
---------------------- Page: 7 ----------------------
ISO/IEC TR 19583-21:2022(E)
columns representing mandatory attributes being declared NOT NULL, and columns for each of the
attributes of the subclasses. None of these subclass columns are declared as NOT NULL, irrespective
of whether they are mandatory or optional within their subclass. An additional column, often
called a discriminator column, with possible values representing each of the subclasses and a
CHECK constraint are provided to manage which columns are populated for each subclass. Where a
subclass is related to another class via a one-to-many association, such that there is a foreign key in
the table representing the other class, a cross-table constraint is needed to ensure that a row in the
other table will only exist if the value of the discriminator column represents the relevant subclass
in the inheritance relationship.
2) Another possibility is the creation of a separate table for the superclass and the creation of
additional tables for each of the subclasses. When the hierarchy is complete, disjoint and static (the
normal situation in most models), a fully mandatory ‘one-to-one’ relationship is provided between
the table representing the superclass and each table representing the subclasses. To achieve this,
the primary key of each subclass table is also declared as a foreign key referencing the superclass
table. Cross-table constraints are needed to ensure that for each row in the superclass table there
is a corresponding row in one of the subclass tables, and that there is no duplication of primary key
values across the tables representing the subclasses.
There are a number of different approaches that can be used when translating UML Class Diagram
associations into SQL. Since each association in the metadata registry metamodel is named, the
approach used in this document is to create a table for each association, with the table named with the
name of the association.
Some many-to-many associations are annotated with association classes. These association classes
also become tables.
5.3 Specific approaches taken for the translation of the metadata registry metamodel
5.3.1 Overview
The following subclauses provide specific detail about the translation of the metamodel artefacts,
where the information in 5.2 is either not applicable or insufficient.
5.3.2 Obligations
In the metamodel the obligation applicable to each attribute or association are described as one of
“Mandatory”, “Conditional” or “Optional”, with these obligations being enforced if, and only if, the
Registration Status of the associated metadata item is Recorded or higher, that is, if the Registration
Status of the associated item is one of “Recorded”, “Qualified”, “Standard” or “Preferred Standard”.
The obligations are not enforced if the Registration Status of the associated item is one of “Candidate”,
“Incomplete”, “Retired” and “Superseded”.
Any registry instantiation has to be able to register items with a lower Registration Status than
“Recorded”, and the obligations cannot, therefore, be simply enforced.
The example SQL instantiation allows the attributes and associations to be optional so that items with
a Registration Status lower than “Recorded” can be accommodated. The obligations applicable to items
with a Registration Status of “Recorded” or higher will, therefore, need to be enforced in the registry
application as opposed to the register (the database) itself.
5.3.3 Translation of datatypes
The datatypes used in the metamodel can be considered to be ‘primitive’ or more complex.
The primitive datatypes are translated as described in Table 1.
3
© ISO/IEC 2022 – All rights reserved
---------------------- Page: 8 ----------------------
ISO/IEC TR 19583-21:2022(E)
Table 1 — Translation of the primitive datatypes
Metadata registry
Examples or Comment SQL Datatype
metamodel datatype
Boolean This simply translates as a column of type BOOLEAN
Date This simply translates as a column of type DATE
Datetime This simply translates as a column of type TIMESTAMP
Integer This simply translates as a column of type INTEGER
XCL Common Logic, This translates as a column of type CHARACTER VARYING
Notation
OWL-DL XML (2500)
This could be a bit This translates as a column of type CHARACTER VARYING
string, but, at a mini- (2500).
Sign
mum, String must be If the SQL implementation supports the BINARY LARGE OB-
supported. JECT type, this could be used instead.
This translates as a column of type CHARACTER VARYING
String
(255)
This translates as a column of type CHARACTER VARYING
(2500).
Text
If the SQL implementation supports the CHARACTER LARGE
OBJECT type, this could be used instead.
The more complex datatypes are translated as described in Table 2.
Table 2 — Translation of the more complex datatypes
Metadata registry
Examples or Comment SQL Datatype
metamodel datatype
This is instantiated with three columns, one INTEGER column
for the lower bound, one INTEGER column for the fixed upper
Natural_Range 0, 1, 2, 1.2, 2.8, 0.*, 3.* bound, and a CHARACTER column, defaulting to ‘many’, for
the many upper bound. The columns are then managed with a
CHECK constraint.
This is instantiated using many different columns, one, or
This represents a value
more, for each of the datatypes listed and then a CHECK
Value of any of the types list-
constraint implemented to ensure that only one datatype is
ed above
represented.
A table is created (named cdt_phone_number) with columns
[6]
as specified in ISO/IEC 19773 ; the table has a surrogate pri-
Phone_Number mary key of datatype INTEGER. Tables representing classes
that have attributes specified with this datatype have foreign
keys that reference cdt_phone_number.
A table is created (named cdt_postal_address) with columns
[6]
as specified in ISO/IEC 19773 ; the table has a surrogate pri-
Postal_Address mary key of datatype INTEGER. Tables representing classes
that have attributes specified with this datatype have foreign
keys that reference cdt_postal_address.
5.3.4 Translation of the basic classes
Each basic class in the metamodel is represented by a table, with the name prefixed by cls_. Each table
representing a basic class has a surrogate primary key of type INTEGER.
5.3.5 Translation of the <> stereotypes
Some classes in the metamodel are used as stereotypes marked <>, enabling other classes to be
‘typed’; that is, to be identified, designated or classified. These classes are instantiated as tables with the
names prefixed by typ_. Each of these tables representing a superclass in a specialization hierarchy has
4
© ISO/IEC 2022 – All rights reserved
---------------------- Page: 9 ----------------------
ISO/IEC TR 19583-21:2022(E)
a surrogate primary key of type INTEGER. Those tables that are then subclasses inherit the surrogate
primary key of the superclass, with that same primary key also being declared as a foreign key.
Those classes that can be ‘typed’ are then subject to a set of ALTER TABLE statements to add columns
and foreign keys to reference the tables representing the <> classes.
5.3.6 Translation of the remaining classes
Each of the remaining classes in the metamodel is represented by a table, with the name prefixed by
cls_. Each table representing a class has a surrogate primary key of type INTEGER.
5.3.7 Translation of specialization hierarchies
Each superclass and its subclasses are represented by separate tables. Those tables that represent
subclasses inherit the surrogate primary key of the superclass, either as the primary key or as an
additional column, with that inherited primary key also being declared as a foreign key. This is normally
achieved by ALTER TABLE statements after the creation of the table.
5.3.8 Translation of the association classes
Each association class in the metamodel is represented by a table, with the name prefixed by asscls_.
Each table representing an association class has a surrogate primary key of type INTEGER, a column,
or columns representing the attributes of the association class, and two additional columns: one each
for the roles of the association class, both of which have datatype INTEGER and are specified as foreign
keys referencing the tables representing the relevant classes.
5.3.9 Translation of the attributes of the classes
Where the metamodel datatype of the attribute is listed in the first column of Table 1, the attribute is
translated as a single column of the relevant table representing the parent class, with a datatype as
specified in the third column of Table 1.
Where the metamodel datatype of the attribute is listed in the first column of Table 2, the attribute is
instantiated as specified in the third column of Table 2.
Where the attribute is a multivalued attribute, there are three options available:
— Where the metamodel datatype is specified in Table 1 and the values are unordered, a separate
characteristic table, prefixed mva_, is created with a column for the multivalued attribute specified
with the SQL datatype identified in Table 1. The second column in this table is a foreign key column
referencing the table representing the class that specifies the attribute. Both columns are declared
as the primary key. If the SQL implementation supports the MULTISET collection type, this can be
used instead of creating the characteristic table.
— Where the datatype is specified in Table 1 and the values are specified as being ordered, or it makes
sense to order them, a separate characteristic table, prefixed mva_, is created with a column for the
multivalued attribute specified with the SQL datatype identified in Table 1. The second column in
this table is a column of datatype INTEGER named ‘priority’ to indicate the order of the value. The
third column in this table is a foreign key column referencing the table representing the class that
specifies the attribute. All three columns are declared as the primary key. If the SQL implementation
supports the ARRAY collection type, this could be used instead of creating the characteristic table.
— Where the datatype is specified in Table 2, a separate characteristic table, prefixed mva_, is created
with a column for the multivalued attribute specified with the datatype INTEGER and as a foreign
key referencing the table representing the complex datatype. The second column in this table is a
foreign key column referencing the table representing the class that specifies the attribute. Both
columns are declared as the primary key.
5
© ISO/IEC 2022 – All rights reserved
---------------------- Page: 10 ----------------------
ISO/IEC TR 19583-21:2022(E)
Where the datatype of the attribute is specified as another class specified in the metamodel, the
attribute is instantiated as a column specified with the datatype INTEGER and as a foreign key
referencing the table representing the class that is specified as the datatype in the metamodel. Where
feasible, this column is included in the CREATE TABLE statement. If this is not feasible, later ALTER
TABLE statements are used to add the column and the foreign key.
5.3.10 Translation of the associations
Each association in the metamodel is represented by a table, with the name prefixed by ass_. Each
table representing an association has two columns: one each for the roles of the association, both of
which have datatype INTEGER and are specified as foreign keys referencing the tables representing the
relevant classes.
Each association is one of three basic types, as follows:
— A one-to-many association: In this case, the column referencing the class at the many end is specified
as the primary key of the table.
— A many-to-many association: In this case, both columns are declared as the primary key of the table.
— A one-to-one association: In this case, both columns are declared as the primary key of the table,
as for the many-to-many association, but, in addition, each column is declared with a UNIQUE
constraint to enforce the one-to-oneness.
5.3.11 Cross-table constraints
Because obligations specified in the metamodel are only applicable if, and only if, the Registration
Status of the associated metadata item is Recorded or higher, very few of the constraints needed to
enforce the obligations are included in the example SQL.
The only cross-table constraints included are those to ensure that, where appropriate, subclasses are
disjoint. This is done by checking that there are no duplicate values for the primary keys across all of
the subclasses in the hierarchy. There are only two disjoint hierarchies in the metamodel: the hierarchy
with Registered_Item as the superclass and the hierarchy with Concept as the superclass.
To check for ‘disjointness’, it is necessary to ensure that, for each of the subclasses, no other subclasses
within the hierarchy has a primary key value the same as a primary key value for the subclass being
checked.
In the example SQL this is achieved by creating a trigger for each subclass. If the SQL implementation
supports ASSERTIONs, they can be used instead.
6 Example SQL for instantiation of the metamodel
The complete set of SQL statements needed to provide this example SQL instantiation is at Annex A.
The statements are provided in the following order:
a) CREATE TABLE statements to create the tables to represent the two complex datatypes, the Phone_
Number class and the Postal_Address class.
b) CREATE TABLE statements to create the tables to represent the basic classes, with additional
characteristic tables to represent multi-valued attributes where needed.
c) CREATE TABLE statements to create the tables to represent the <> classes. These tables are
created in an order that allows for the instantiation of specialization of the Identified_Item class.
d) CREATE TABLE statements to create the tables to represent the remaining classes. These tables
are created in alphabetical order.
6
© ISO/IEC 2022 – All rights reserved
---------------------- Page: 11 ----------------------
ISO/IEC TR 19583-21:2022(E)
e) CREATE TABLE statements to create the tables to represent the association classes. These tables
are also created in alphabetical order. Additional characteristic tables are created to represent
multi-valued attributes where needed
f) ALTER TABLE statements to add columns and foreign keys as necessary to the already created
tables to add the attributes that were omitted when the tables were created because the datatype
of the attribute is another class.
g) ALTER TABLE statements to add columns and foreign keys as necessary to the already created
tables to instantiate subclassing where appropriate.
h) ALTER TABLE statements to add columns and foreign keys to the already created tables for those
classes in the metamodel that can be ‘typed’ so that they can be identified, designated or classified.
i) CREATE TABLE statements to create the tables to represent the metamodel associations.
j) CREATE TRIGGER stat
...
Questions, Comments and Discussion
Ask us and Technical Secretary will try to provide an answer. You can facilitate discussion about the standard in here.