04ConceptualModeling

Conceptual Modeling

Outline

Methodology
Example 1
Example 2
Example 3
Example 4

A conceptual modelling methodology used in this subject, transforms a specification of database domain, into a conceptual schema expressed in a graphical notation of object modelling.

A transformation is performed in several steps.

In each step a specification of database domain is traversed and the new components, like classes of objects, attributes, associations, are added to a conceptual schema.

In each step, a conceptual schema is extended by only one type of component.

A transformation is consistent with a principle "one step at a time", that according to the Cambridge Dictionary means, doing things slowly and carefully, doing just a little at a time.

At the end of the process, we obtain a complete conceptual schema expressed as a simplified object class diagram.

Methodology

Input: Specification of the requirements (usually a natural language description of a database domain)
Tools: Object Modeling graphical notation
Methods: Systematic transformation of individual components of specification of requirements into the components of Object Modeling notation
Output: Conceptual schema - simplified object class diagram

A sequence of transformations of a specification of database domain, is performed in the following way.

First, we read a specification, we identify the classes of objects and we create an initial conceptual schema, that contains only the classes of objects.

Then, we read a specification again, and we add to a conceptual schema the associations and association classes.

Next, we read a specification again, and we add to a conceptual schema the attributes and link attributes describing classes of objects and association classes.

Next, we read a specification again, and we add to a conceptual schema the identifiers of classes of objects.

At this step, we also identify the classes of objects, that do not have their own identification systems.

Next, we read a specification again, and we create the qualifications, that provide the identifiers for the classes, that do not have their own identification systems.

Finally, we read a specification again, oh, maybe there is no need to read it one more time, we probably memorised a specification thus far, and we add to a conceptual schema the generalisation hierarchies.

Methodology

The transformations of specifications are performed in the following sequence of steps:
- Step 1: Creating classes of objects
- Step 2: Creating associations and association classes
- Step 3: Creating attributes and link attributes
- Step 4: Creating identifiers
- Step 5: Creating qualifications
- Step 6: Creating generalizations

Conceptual Modeling

Example 1

An objective is to create a conceptual schema of a small database that contains information about employees and projects
A detailed specification is the following:
- A group of employees works on the projects
- Some of employees supervise other employees
- An employee is described by an employee number and full name
- A project is described by a project title, completion date and budget
- Employees are identified by their employee numbers and projects are identified by titles

In the first step, we read a specification and we decide what classes of objects are described in the specification. In the other words, we create the first version of conceptual schema, that consists only of classes of objects.

When I read the specification I decided to create two classes of objects: EMPLOYEE and PROJECT. Of course it was my subjective decision and another designer may decide to create only one class of objects: EMPLOYEE and to consider the projects as a multivalued attribute describing employees. Unfortunately, such decision creates a problem how to add information like project title, completion date an budget. We cannot treat such attributes as multivalued attributes because these attributes a er semantically related. On the other hand we cannot have nested single valued or multivalued attributes. Now, we can see that decision to represent projects as multivalued attribute was not the best one. Each time we find ourselves in "no through road" situation we have have to reconsider a design decision, that lead to such situation. In this case, we should accept a fact, that the projects are the instances of objects and as a logical consequences we should create a class PROJECT.

It is possible to create more classes of objects, for example the class TITLE, DATE, and BUDGET. Then, such decision would contribute to creation of few one-to-one associations and many-to-one associations between a class PROJECT and classes described by the single attributes. Each time, we get a class described by a single attribute related by one-to-one association with another class we should ask a question: maybe it is possible to merge such classes into one. The same situation happens when a class described by a single attribute is involved in many-to-one association with another classes. Maybe such class can be "demoted" to a single valued attribute.

A design is always a process where very frequently to steps forward are followed by one step backward.

So, at the end I decided to create two new classes of objects, EMPLOYEE and PROJECT.

Example 1

Step 1: Creating classes of objects
A group of employees works on the projects. Some of employees supervise other employees. An employee is described by an employee number and full name. A project is described by a project title, completion date and budget. Employees are identified by their employee numbers and projects are identified by the titles

In the second step, we create, and we append to the conceptual schema, associations and association classes.

We find two associations: EMPLOYEE Works-on PROJECT, and EMPLOYEE Supervises EMPLOYEE. We append the associations to the conceptual schema and we get a class diagram given on the present slide.

Next, we must provide the multiplicities for each association. An association Works-on is many-to-many because an employee can work on many projects, and a project can be implemented by many employees. To find we imagine to be an employee and we ask ourselves a question: how many projects I can work on ? In this domain, we are allowed to work on many projects. Then, we imagine to be a project and we ask ourselves a question: how many employees can participate in my implementation. In the present domain, a project can be implemented by many employees.

Next we can look at minimal multiplicities of the associations. Does every employee must work on at least one project ? We assume that it makes sense to have all employees working on at least one project. The second question: Do we have projects with no employees ? Well, our assumption is: no, every project must have at least one employee assigned to it.

We assume, that participation of both classes in the association is compulsory. As a consequence, an employee must work on at least one projects, and a project must be implemented by at least one employee and an association Works-on is mandatory on both sides.

An association, EMPLOYEE Supervise EMPLOYEE is zero-to-many, because an employee can supervise zero or more employees. Bottom level employees in a managerial hierarchy supervise no employees. On the other side a multiplicity of the same association expressed in a way EMPLOYEE Is-supervised-by EMPLOYEE is optional because every employee except ceo has at most one supervisor. A ceo does not have any supervisors. It is why an association Is-supervised by has multiplicity zero-or-one.

It is important to note, that a decision whether minimal multiplicity of association is zero or one strongly depends on our subjective view of the reality. For a single domain it is possible to argue that minimal multiplicity is zero and not one. If we decide that a minimal multiplicity is one then the existence of a link is strongly bound with the existence of an instance of an object. An instance of an object cannot exists with out a link and whenever a new instance of an object is added we also must add a link. In our case one may argue that when a project is created then for certain period of time no employees are assigned and because of that an association PROJECT Has EMPLOYEE is optional on PROJECT side. Luckily, minimal multiplicity does not have an important impact during a stage of logical design where a conceptual schema is converted into implementation schema of a particular database system.

Example 1

Step 2: Creating associations and association classes
A group of employees works on the projects. Some of employees supervise other employees. An employee is described by an employee number and full name. A project is described by a project title, completion date and budget. Employees are identified by their employee numbers and projects are identified by the titles

In the third step, we find and we create attributes and link attributes. After we decided what classes of objects we plan to consider in a design, we read a specification one more time and one by one we find the description of the classes.

In our case, we find, that employees are described by their employee numbers and full names. We add both attributes to a description of the class employee. It is important to decide a multiplicity of each attribute and whether an attribute is optional or mandatory. Each employee has one employee number and one full name and both values must be provided. It means that both attributes are single valued and mandatory. andWe can skip a tag [1].

We also find, that a project is described by a project title, completion date, and budget. We add three attributes, project title, completion date, and budget, to a description of the class project. Like before we find that all attributes describing a project are single valued and mandatory.

Example 1

Step 3: Creating attributes and link attributes
A group of employees works on the projects. Some of employees supervise other employees. An employee is described by an employee number and full name. A project is described by a project title, completion date and budget. Employees are identified by their employee numbers and projects are identified by the titles

Now, when we have attributes and associations we can find identifiers fro each class. A class may have its own identification system like for example a student number attribute describing a class STUDENT. A class may have a default identifier created from identifiers of the other classes, like for example a pair(student number, subject code) describing a class ENROLMENT. A class may have a derived identifier that consists of its own attribute and identifier borrowed from another class, like for example a pair of attributes (building number, room number) being an identifier of a class ROOM. Finally, a class may have an identifier inherited from a superclass in a generalisation hierarchy, like for example an attribute student number being an identifier of a class UNDERGRADUATE STUDENT and inherited from a class STUDENT.

In the fourth step, we read the specifications again and we find identifiers, and we mark identifiers in a conceptual schema, with IDn tags where n is empty if there only one identifier or it is 1, or 2, or , ... when we are able to find more identifiers.

We find, that employees are identified by their employee numbers, and we add a tag ID to an attribute enumber in a class EMPLOYEE.

Next, we find, that projects are identified by the titles, and we add a tag ID to an attribute title in a class PROJECT.

No more identifiers can be discovered from the specification. Sometimes, we can use our own knowledge of the reality to discover an identifier. For example, a class CAR has an identifier registration number.

Example 1

Step 4: Creating identifiers
A group of employees works on the projects. Some of employees supervise other employees. An employee is described by an employee number and full name. A project is described by a project title, completion date and budget. Employees are identified by their employee numbers and projects are identified by the titles

Example 1

Step 5: Creating qualifications
A group of employees works on the projects. Some of employees supervise other employees. An employee is described by an employee number and full name. A project is described by a project title, completion date and budget. Employees are identified by their employee numbers and projects are identified by the titles

Finally, in the last step, we look for generalisation hierarchies. Information about generalisation hierarchies is usually hidden in the statements like "... there are two types of X: a type Y and a type Z...", for example "... there are two types of students: postgraduate students and undergraduate students. Such information does not contribute to an attribute "type" in a description of a class STUDENT. We already created such attribute then we must replace it with a generalisation hierarchy UNDERGRADUATE STUDENT ISA STUDENT and POSTGRADUATE STUDENT ISA STUDENT. It is not a mistake to make a design better. We always apply a rule saying that "better is an enemy of good".

Our sample database domain does not contain any information, about subsets or supersets of objects. Therefore, we conclude, that there are no generalisation hierarchies in the conceptual schema. The final conceptual schema is visible at the bottom of a slide.

Example 1

Step 6: Creating generalizations
A group of employees works on the projects. Some of employees supervise other employees. An employee is described by an employee number and full name. A project is described by a project title, completion date and budget. Employees are identified by their employee numbers and projects are identified by the titles

Conceptual Modeling

Outline

Methodology
Example 1
Example 2
Example 3
Example 4

Example 2

An objective is to create a conceptual schema of a small database that contains information about publications such as journals and conference proceedings, published research papers and authors
A detailed specification is the following:
- A publishing company publishes the journals and conference proceedings
- The research papers are included in the journals and proceedings
- The company employs the general editors who take responsibility for editing the research papers
- A research paper is written by one or more authors

Example 2

Step 1: Creating classes of objects
A publishing company publishes the journals and conference proceedings. The research papers are included in the journals and proceedings. The company employs the general editors who take responsibility for editing the research papers. A research paper is written by one or more authors

In the next step, we add to a conceptual schema, associations and association classes.

A statement: research papers are included in journals and proceedings contributes to the following associations: PAPER Is-included-in JOURNAL, and PAPER Is-included-in PROCEEDINGS. Due to the research integrity rules, a paper can be published only one time, and because of that both associations are one to many. Of course, journals, proceedings publish many research papers.

A statement: the company employs the general editors, who take responsibility for the editing research papers, contributes to an association: PAPER Is-edited-by EDITOR. An editor can edit many papers, and a paper can be edited by many editors. Hence, the association has a multiplicity many to many.

A statement: a research paper is written by one or more authors, contributes to an association: PAPER Is-written-by AUTHOR. Such association has a multiplicity many to many, because a paper can be written by many authors, and an author can write many papers. An updated conceptual schema is given at the bottom of a slide.

Example 2

Step 2: Creating associations and association classes
A publishing company publishes the journals and conference proceedings. The research papers are included in the journals and proceedings. The company employs the general editors who take responsibility for editing the research papers. A research paper is written by one or more authors

Example 2

Step 3: Creating attributes and link attributes
A publishing company publishes the journals and conference proceedings. The research papers are included in the journals and proceedings. The company employs the general editors who take responsibility for editing the research papers. A research paper is written by one or more authors

Example 2

Step 4: Creating identifiers
A publishing company publishes the journals and conference proceedings. The research papers are included in the journals and proceedings. The company employs the general editors who take responsibility for editing the research papers. A research paper is written by one or more authors

Example 2

Step 5: Creating qualifications
A publishing company publishes the journals and conference proceedings. The research papers are included in the journals and proceedings. The company employs the general editors who take responsibility for editing the research papers. A research paper is written by one or more authors

Finally, we are looking for the generalisation hierarchies.

To create a generalisation, we introduce a new concept of publication. Both, proceedings, and journals are publications.

Therefore, we can create a generalisation: PROCEEDINGS ISA PUBLICATION, and JOURNAL ISA PUBLICATION, see visualisation at the bottom of a conceptual schema.

Generalisation allows us to replace the associations, PAPER Is-included-in PROCEEDINGS, and PAPER Is-included-in Journal, with one association: PAPER Is-included-in PUBLICATION. It is a commonly used solution to a problem, when many associations with the same names, link a class of objects with many other classes of objects, that can be generalised into a super class. In such a case, generalisation to a super class, allows for elimination of the repetitions of the same associations.

In our case, it eliminates of the repetitions of an association: Is-included-in.

Example 2

Step 6: Creating generalizations
A publishing company publishes the journals and conference proceedings. The research papers are included in the journals and proceedings. The company employs the general editors who take responsibility for editing the research papers. A research paper is written by one or more authors
Journal, proceedings IS-A publication

Conceptual Modeling

Outline

Methodology
Example 1
Example 2
Example 3
Example 4

An objective of the next example, is to create a conceptual schema of a small database, that contains information about the construction companies, and about the buildings erected by the construction companies.

A detailed specification is the following: A group of construction companies is involved in construction of industrial buildings. A building is located at a different address, it has a unique name, and completion date. A number of construction stages apply to each building. A construction stage is described by its name, the names of construction companies involved together with the start date, completion date, and the total costs. A construction company is usually involved in many construction stages of the same building. Many construction companies are involved in one construction stage.

In this example, we shall mainly concentrate on minimal set of associations and on identification of object classes with the derived identifiers.

Example 3

An objective is to create a conceptual schema of a small database that contains information about the construction companies and buildings built by the companies
A detailed specification is the following:
- A group of construction companies is involved in the construction of industrial buildings
- A building is located at a different address, it has a unique name and completion date
- A number of construction stages apply to each building
- A construction stage is described by its name, the names of the construction companies involved together with the start date, completion date and the total costs
- A construction company is usually involved in many construction stages of the same building
- Many construction companies are involved in one construction stage

Example 3

Step 1: Creating classes of objects
A group of construction companies is involved in the construction of industrial buildings. A building is located at a different address, it has a unique name and completion date. A number of construction stages apply to each building. A construction stage is described by its name, the names of the construction companies involved together with the start date, completion date and the total costs. A construction company is usually involved in many construction stages of the same building. Many construction companies are involved in one construction stage

In the next step, we read a specification again, and we try to find the associations, and association classes.

The facts like, construction companies involved in construction of industrial buildings, construction stages applied to buildings, and construction companies involved in many construction stages, contribute to the following association: COMPANY Involved-in BUILDING. The multiplicities on both sides of such association are many to many because a company is involved in construction of many buildings, and a building is constructed by many companies.

The next association is: STAGE Applies-to BUILDING. A stage applies to one building and a building requires many construction stages. Hence, the multiplicities are one to many from a building side.

The third association is: COMPANY Involved-in STAGE. Such association is many to many, because a company is involved in many construction stages and a construction stage my need many companies.

Note, that the associations create a cycle in a conceptual schema. Is it a problem ? Sometimes yes, sometimes not. it is a problem when one of the associations involved in a cycle can be logically derived from the other associations involved in a cycle. Then, storing derived information always means certain level or redundancies in a database. Redundancies are useful when allow fro processing of frequent queries faster. On the other hand, redundancies delay processing of data manipulation operations where modifications of data must be replicated to keep derived data up to date. If there is no improvements in performance data redundancies are always considered as harmful.

Therefore, whenever we get a cycle in a conceptual schema, we always try to eliminate it. Redundancies can be always added to a database at the later stages when database applications are tested agains the performance constraints. In our case, a conclusion, that if a company is involved in the construction stage, then such company is involved in construction of a building allows for elimination of a cycle. We shall discuss it more thoroughly in the next slide.

Example 3

Step 2: Creating associations and association classes
A group of construction companies is involved in the construction of industrial buildings. A building is located at a different address, it has a unique name and completion date. A number of construction stages apply to each building. A construction stage is described by its name, the names of the construction companies involved together with the start date, completion date and the total costs. A construction company is usually involved in many construction stages of the same building. Many construction companies are involved in one construction stage

In the same step we eliminate redundant association asn association classes.

In the previous slide, we realised, that if a company is involved in a construction stage applied to a building, then such company must be involved in the construction of a building. It means, that association: COMPANY Involved-in BUILDING is not really needed, because it can be logically derived from an association: COMPANY Involved-in STAGE. Then, it is possible to eliminate a cycle of associations through elimination of one of the associations involved in the cycle.

Elimination of an association is possible, only if, such association can be logically derived from the other associations. Sometimes, it is impossible to eliminate a cycle without loosing information. For example, if we look at a database domain where sporting teams play their games, then the associations: team plays a home game, and, team plays an away game form a cycle, that involves both associations. In such a case, it is impossible to break cycle, because we would loose information, who played a home game, or who played away game.

It means, that sometimes it is impossible to derive an association from the other association, in order to break a cycle.

Example 3

Step 2 (improved): Creating associations and association classes
A group of construction companies is involved in the construction of industrial buildings. A building is located at a different address, it has a unique name and completion date. A number of construction stages apply to each building. A construction stage is described by its name, the names of the construction companies involved together with the start date, completion date and the total costs. A construction company is usually involved in many construction stages of the same building. Many construction companies are involved in one construction stage
If a company is involved in a construction stage that applies to a building then such company is involved in construction of the building

Example 3

Step 2 (improved): Creating associations and association classes
It means that we do not need an association Involved-in bettwen the classes COMPANY and BUILDING because such association can be derived from the other two associations

In the next step, we find the attributes describing classes of objects, and link attributes, describing associations.

To do so, we read a description of a database domain again, and we find that a statement "A building is located at a different address, it has a unique name and completion date." indicates, that a building is described by an address, name and the completion date.

We also find from a statement: "A construction stage is described by its name, the names of construction companies involved together with the start date, completion date and the total costs", that a construction stage is described by a name, start date, completion date and cost.

An information about construction stage being described by the names of construction companies, contributes to an attribute name describing a construction company.

We find no link attributes describing associations.

A conceptual schema with the attributes describing the classes of objects is given at the bottom of the slide.

Example 3

Step 3: Creating attributes and link attributes
A group of construction companies is involved in the construction of industrial buildings. A building is located at a different address, it has a unique name and completion date. A number of construction stages apply to each building. A construction stage is described by its name, the names of the construction companies involved together with the start date, completion date and the total costs. A construction company is usually involved in many construction stages of the same building. Many construction companies are involved in one construction stage

Step 3

Example 3

Step 4: Creating identifiers
A group of construction companies is involved in the construction of industrial buildings. A building is located at a different address, it has a unique name and completion date. A number of construction stages apply to each building. A construction stage is described by its name, the names of the construction companies involved together with the start date, completion date and the total costs. A construction company is usually involved in many construction stages of the same building. Many construction companies are involved in one construction stage
What we know about the reality is that construction companies have unique names

Step 4

Example 3

Step 5: Creating qualifications
A group of construction companies is involved in the construction of industrial buildings. A building is located at a different address, it has a unique name and completion date. A number of construction stages apply to each building. A construction stage is described by its name, the names of the construction companies involved together with the start date, completion date and the total costs. A construction company is usually involved in many construction stages of the same building. Many construction companies are involved in one construction stage
What we also know about the reality is that a construction stage of a building starts once at a given moment in time

Step 5

Example 3

Step 6: Creating generalizations
A group of construction companies is involved in the construction of industrial buildings. A building is located at a different address, it has a unique name and completion date. A number of construction stages apply to each building. A construction stage is described by its name, the names of the construction companies involved together with the start date, completion date and the total costs. A construction company is usually involved in many construction stages of the same building. Many construction companies are involved in one construction stage

Step 6

Conceptual Modeling

Outline

Methodology
Example 1
Example 2
Example 3
Example 4

In the last example, show, that it is possible to create a conceptual schema practically for everything what can be categorised as a fragment of the reality. Now, we shall look at a domain of caterpillars, birds, toads, and butterflies described as a short poem for kids. Such specification is a bit harder to interpret because it does not say a lot about the groups of objects and links. It mainly speaks about individual instances of objects and links between the objects. It is a task of a designer to "upgrade" the instances of objects to classes of objects and links to associations.

An objective of the last example is to create a conceptual schema, for a database domain, described by the following poem for kids.

"Caterpillar".

by Christina Rossetti

Brown and furry

Caterpillar in a hurry,

Take your walk

To the shady leaf, or stalk,

Or what not,

Which may be the chosen spot.

No toad spy you,

Hovering bird of prey pass by you;

Spin and die,

To live again a butterfly.

Example 4

An objective is to create a conceptual schema for a database domain described by the following poem for kids
"Caterpillar"
by Christina Rossetti

Brown and furry
Caterpillar in a hurry,
Take your walk
To the shady leaf, or stalk,
Or what not,
Which may be the chosen spot.
No toad spy you,
Hovering bird of prey pass by you;
Spin and die,
To live again a butterfly.

Example 4

Step 1: Creating classes of objects
Brown and furry Caterpillar in a hurry Take your walk To the shady leaf, or stalk, Or what not, Which may be the chosen spot. No toad spy you, Hovering bird of prey pass by you; Spin and die, To live again a butterfly.

Step 2

In the next step, we read a specification again, to find the associations and association classes.

Pretty large number of classes of objects are linked an instance of CATERPILLAR class. We can find the following associstions:

TOAD Spies CATERPILLAR.

BIRD Passes CATERPILLAR.

CATERPILLAR Walks to LEAF.

CATERPILLAR Walks to SPOT.

CATERPILLAR Walks to STALK.

CATERPILLAR Spins and dies to live as BUTTERFLY.

The multiplicities of all associations, except one, are many to many.

An association: CATERPILLAR Spins and dies to live as BUTTERFLY, has multiplicities one-to-one.

It is, because in a process of metamorphosis, one caterpillar is transformed into one butterfly, and one butterfly arises from one caterpillar.

Three repetitions of an association: Walks, suggest generalisation of the classes LEAF, SPOT, and STALK.

We shall do it later on.

Example 4

Step 2: Creating associations and association classes
Brown and furry Caterpillar in a hurry Take your walk To the shady leaf, or stalk, Or what not, Which may be the chosen spot. No toad spy you, Hovering bird of prey pass by you; Spin and die, To live again a butterfly.

Step 2

Example 4

Step 3: Creating attributes and link attributes
Brown and furry Caterpillar in a hurry Take your walk To the shady leaf, or stalk, Or what not, Which may be the chosen spot. No toad spy you, Hovering bird of prey pass by you; Spin and die, To live again a butterfly.

Step 3

Example 4

Step 4: Creating identifiers
Brown and furry Caterpillar in a hurry Take your walk To the shady leaf, or stalk, Or what not, Which may be the chosen spot. No toad spy you, Hovering bird of prey pass by you; Spin and die, To live again a butterfly.

Step 4

Example 4

Step 5: Creating qualifications
Brown and furry Caterpillar in a hurry Take your walk To the shady leaf, or stalk, Or what not, Which may be the chosen spot. No toad spy you, Hovering bird of prey pass by you; Spin and die, To live again a butterfly.

Step 5

Example 4

Step 6: Creating generalizations
Brown and furry Caterpillar in a hurry Take your walk To the shady leaf, or stalk, Or what not, Which may be the chosen spot. No toad spy you, Hovering bird of prey pass by you; Spin and die, To live again a butterfly.

Step 6

References

C. Coronel, S. Morris, A. Basta, M. Zgola, Data Management and Security, Chapters 3 and 4, Cengage Compose eBook, 2018, eBook: Data Management and Security, 1st Edition
T. Connoly, C. Begg, Database Systems, A Practical Approach to Design, Implementation, and Management, Chapter 16 Methodology - Conceptual Database Design, Pearson Education Ltd, 2015
How to ... ? Cookbook, How to use UMLetlet for conceptual modeling ? Recipe 2.1 How to create very simple conceptual schemas with UMLetlet ?
How to ... ? Cookbook, How to use UMLetlet for conceptual modeling ? Recipes 2.2, 2.3, and 2.4 How to do design a conceptual schema with UMLetlet (Design 1, 2, and 3) ?