2.1 – ERM and EERM
PART SUPPLY
SUPPLIER N P PROJECT M PART SUPPLY SOME PART SUPPLIER N M PROJECT CAN SUPPLY N N USES M M
Fig. 2.14 – A ternary relationship is not, in general, reducible to three binary ones.
Any ERM design verification should include a thorough investigation of the necessity of any ternary (or n-ary) relationship. It is preferable to have three binary relationships rather than a ternary, implementation wise, but we have just seen that this is not a question of preference but data requirements.
The necessity of a ternary (or n-ary) relationship is a problem because some CASE tools and data models only express binary relationships. In these cases the n-ary relationship is coerced into an aggregated entity with each of its parts denoting one of the n-ary relationships. Furthermore depending on the cardinality of the ternary a set of functional dependencies (a form of integrity constraints) needs to be defined and enforced.
2.4.4 Formalisation of the EERM
In some references, for example in Ng [NGPAU81] and Vossen [VOSSE91], the structure of
entities, relationships and a number of constraints (even those found in EERM) are represented through a formal model using set, Cartesian products, and integrity constraints (i.e. first-order predicate logic denial queries).
With formalisation of ERM and including functional dependencies (a form of integrity constraints) one, for example, can easily show that some ternary relationships cannot be represented as three binary relationships. It is sufficient to show that if the combined
Conceptual Modelling - Page [ 31]
primary key sets of the participating entities determine a relationship attribute then there are no three binary relationships that can do so.
Over and above its importance to express the exact meaning of a diagram it also serves us another purpose of great relevance here. Basically these formal structures become our basis for the transformation into another database data model and carry the conceptual structures and constraints onto the database model.
2.5
An Example
A hypothetical database to represent most of the data model and query model examples discussed in this study is presented in this section. This running example describes an organisation entrusted with the categorisation and dissemination of Research and Development efforts. The organisation’s scope is in the fields of Information Systems and Telematics being undertaken by a number of research organisations. Both governmental agencies and private organisations are sponsors of these research efforts. This example is from Bertino’s exposition found in [BERTI91] and what follows is an informal listing of the
system requirements, as simple textual statements.
A number of projects are currently active and for each project, its unique name, aim and projected total cost are of interest. Also each project assignment undertakes research that has a hardware or software profile, but some projects have aspects from both areas. For hardware projects the details of interest include the devices and the functionality required by the assignment. As for software projects the main interest is in their targeted computer environments and the associated tool-set required during their development.
A number of teams participate in a project’s realisation and each team implements a part of the overall project for a predetermined fee. The team’s properties of interest include its name (for identification purposes) and its employees. Each team needs to know the total budget derived from the addition of all their participating projects’ budget allotments.
A person is an employee of only one team and an employee’s pay terms are either on a permanent or on a consultative basis (e.g. per hour basis). A team’s employees have a hierarchic placing and (therefore) some employees manage others. Each employee has a distinctive name over the whole scope of the database.
Conceptual Modelling - Page [ 32]
Teams belongs to a research institution (in most cases are part of a University) and receive funds from “outside” sources. Government and industry are the typical outside sources of funding. Each of these sponsors can finance more than one project and more than one team. In each funding instance it is pertinent to determine the exact commitment of a sponsor to each project, categorised by the team’s share.
Details of interest about government and companies are their names, main activities and their contact addresses. Some of the address locations, for a number of Government Departments, Institutes and Companies, are the same. The above requirements are drawn into the EERM given in figure 2.15.
N
Project M allocate Sponsor
Team P N Software Hardware S/W & H/W Employee Permanent Consultant Institute Government Industry Address(2) employes luanches 1 N 1 manages 1 N d4 d d Ê d
full time part time
Status Target PName PBudget (1) Target O/S Function Skills Tool Set Skills Devieces Budget Role SName SBudget (1) TName TBudget (1) EName Status IName SequenceN Details Status HourlyRate MaxRate (3) MonthlySal MaxSal (3) Ê Ê
Fig. 2.15 – An example EERM (notes are explained in the following).
Notes to EERM:
(1) The budget value for Project, Sponsor and Team instances is computed by summing all instances' budget contributions given in their respective allocation relationship. (2) Use the Address instances as part of an aggregation. Relate as independent and
shareable.
(3) These are really 'class attributes'; consequently all instances would have the same value.
(4) Should this relationship be changed to an overlap type then the multi-inheritance and entity 'S/W & H/W' are redundant.
Conceptual Modelling - Page [ 33]
2.6
Summary
The assertion that Chen’s ERM is a popular top down design language is widely supported; the main justifications being its high-level nature and independence of any database data model. A significant branching from it is found in part of UML. This chapter surveys conceptual modelling through Chen’s and other later works in the area.
Diagrams are composed of entities, relationships, and constraints. Later additions to ERM, including sub-classing, categories and aggregation, are called EERM. The introduction of these constructs not only requires malleable structures but also effective mechanisms to describe how the underlying data changes; for example changes to a part-of hierarchy required detailed data-changing procedures.
The automated conversion of an EERM into an object-oriented data model is a priority in this study. The conversion needs to have a high-degree of correctness and completeness. The effects of incorrectness are obvious; on the other hand the effects of incompleteness would imply that the ‘missing’ artefacts need to be hard wired and consequently disconnected from the DBMS control.
The following chapters deals with a definition of an object-oriented database and data model, and presents and an object-oriented database standard. In chapter nine, where our original work is presented, shows how an EERM model is translated into a standard object- oriented database language. The translation includes entities, attributes, various types of relationships, and constraints (e.g. referential, and primary key set).