ECEG-4172 - Database System Conceptual Level Data Model

2. Conceptual Level Data Model

The database design stages as seen in the previous chapter can be split in five phases as:
1. Planning and Analysis
2. Conceptual Design
3. Implementation (Logical) Design
4. Physical Design, and
5. Implementation
The planning and analysis phase in a database design is an investigation phase, during which all the
needed information are gathered and analyzed. This stage is generally done with the help of users,
and is crucial to the second phase, the conceptual design. The borders between the analysis,
conceptual, and logical designs are often blurred. You go from one stage to the other, back and
forth.
The conceptual design stage develops a conceptual data model that later on is transformed into the
implementation model. The conceptual data model mainly refers to the Entity – Relationship
(E/R) Data Model.
At full deployment the conceptual design also includes functional requirements which describe the
kinds of operations (or transactions) that will be performed on the data.

 Entity - Relationship (E/R) Data Model

The Entity-Relationship (E/R) data model was introduced by Peter Chen in 1976-77 as a way to
unify the network and relational database views. It has become very popular because it is very
simple to create and read (as it is a diagrammatic representation), and can be used directly to create
a relational model and transform its elements into database elements. The E/R model translates
analyzed information into data requirements, and is used to facilitate communications between the
database architect and the future users of the new system.
The E/R data model views the real world as a set of basic objects (entities) and relationships
among these objects. It is intended primarily for the database design process by allowing the
specification of an enterprise scheme. This represents the overall logical structure of the database.

 Elements of E/R Model

The three basic notions of the E/R model are:
 Entity: represents existing real-world objects or concepts, such as places, objects, events,
persons, orders, customers, and so on.
 Relationship: represents associations between objects, such as the fact that a customer may
place an order.
 Attribute: describes the entity, such as the invoice date or the customer first name.

EiT-M (School of Electrical and Computer Engineering) 2- 1/17

ECEG-4172 - Database System Conceptual Level Data Model

Case Study (The case in here is only for teaching purpose and it is no way
related to any company)

Consider a database system to be developed for “XYZ Software Share

Company”. The following are brief and short listed structures of the
company.

- The company runs various projects with a total of 68 full-time

employees and over 120 part-time employees.
- A project has a unique id and a name that may be designed for
a new software development or for a release of a new version
of software that had been developed by the company.
- The projects are having start date, due date, complete date and
status that describe their progress. Every project is lead by a
senior manager organized into teams of five to eight
programmers coordinated by a team leader.
- The owners of the projects are the customers of the company.
A single customer can own one or more projects. The
customers have unique id, name and address.
- The company is organized into departments that are identified
by a unique name and lead by department heads. A
department head can only lead a single department in his/her
employment by the company.
- An employee can only belong to one department. Every
employee is identified by an Id, a name, an address, and a
position. In addition full-time employees have monthly salary
and allowance rate; and part-time employees have contract
period and hourly rate. Working schedule of both full-time
employees and part-time employees is maintained on weekly
bases.

Entity Sets
Entities are the principal data objects about which information is to be collected in E/R model.
Entities are usually recognizable concepts, either concrete or abstract, such as person, places,
things, or events which have relevance to the database. An entity set is then a set consisting of the
same type of entities that share same properties.
Consider the case study; some specific examples of entities are then:
EMPLOYEES, PROJECTS, CUSTOMERS …
The candidate entities from the requirement statements are the nouns and the adjective noun phrases.
The “EMPLOYEES” entity set represents all the set of employees and the “Projects” entity set
represents all the set of projects.

EiT-M (School of Electrical and Computer Engineering) 2- 2/17

ECEG-4172 - Database System Conceptual Level Data Model

Entities are classified as independent (Strong) or dependent (Weak).

 A strong (independent) entity is one that does not rely on other entities for identification.
 A weak (dependent) entity is one that relies on other entities for identification.
An individual occurrence of an entity set is also known as an instance (object).

Attributes
Attributes are descriptive properties that are associated with an entity. A set of attributes describe
an entity.
A particular instance of an attribute is called a value.
For example, “Employee Id” and “Name” are the attributes of the “EMPLOYEES” entity set; and
“Kevin Jones” is one value of the attribute “Name”.
The domain of an attribute is the collection of all possible values an attribute can have. The domain
of “Name” is a character string.
Attributes can be classified as identifiers or descriptors.
 Identifiers: more commonly called keys, uniquely identify an instance of an entity.
Example: “Employee Id” uniquely identifies an employee entity from the entity set.
 Descriptor: describes a non-unique characteristic of an entity instance.
Example: “Name” is a descriptor for the “EMPLOYEES” entity set.
Other way of categorizing Attributes is as Simple and Composite attributes.
 Simple Attributes: are attributes also known as Atomic Attributes that can not be divided
into subparts mainly of primitive types.
Example: “Age” and “Gender” of the “EMPLOYEES” entity set.
 Composite Attributes: are attributes that are composed of smaller subparts that can be
subdivided into the subparts (Attributes).
Example: “Address” of the “EMPLOYEES” entity set that can be divided into “City”,
“Home Address”, “Phone”, and “P.O. Box”
Hierarchical Composite Attributes
Address

City Home Address Phone P.O. Box

SubCity Kebele H. No.

Fig 1. Hierarchical Composite Attribute

Another classification of attributes is based on the values that they can hold as: Single-valued and
Multi-valued attributes.
 Single-valued Attributes: are attributes having only one possible value at any time.
Example: “Name” and “Gender” of the “EMPLOYEES” entity set.
 Multi-valued Attributes: are attributes that are having possibly more than one value.
Example: “Address” of the “EMPLOYEES” entity set.

EiT-M (School of Electrical and Computer Engineering) 2- 3/17

ECEG-4172 - Database System Conceptual Level Data Model

Attributes can also be categorized Stored and Derived attributes.

 Derived Attributes: are attributes that can be calculated from the related stored attributes,
entities or general states.
 Stored Attributes: on the other hand are attributes that can not be calculated in any way
from the stored attributes.
Example: “Birth Date” of the “EMPLOYEES” entity set is a stored attribute, where
as “Age” is a derived attribute that can be calculated from the “Birth Date” and
“Current Date”.

Relationship Sets
A Relationship represents an association between two or more entities. An example of a
relationship would be:
- “EMPLOYEES” are Assigned to “TEAMS”
- “CUSTOMERS” Owns “PROJECTS”
- “TEAMS” works on “PROJECTS”
A Relationship Set is then a set consisting same types of relationships. The entities involved in the
relationship are known as participating entities and the function the entity plays in a relationship
is called the entity’s role.
Example: In the Assigned relationship “EMPLOYEES” and “TEAMS” entity sets are the
participating entity sets; and the “EMPLOYEES” entity has a role as a “Programmer” or
“Team Leader” in the relationship.
Relationships are classified in terms of degree, connectivity, cardinality, and existence.
Degree: The degree of a relationship is the number of entities associated with the relationship. The
n-ary (multi-way) relationship is the general form for degree n. Special cases are the binary, and
ternary, where the degree is 2, and 3, respectively
Connectivity: The connectivity of a relationship describes the mapping of associated entity
instances in the relationship. The values of connectivity are “one” or “many”.
Cardinality: The cardinality of a relationship is the actual number of related occurrences for each of
the two entities. The basic types of connectivity for relations are: one-to-one, one-to-many, and many-
to-many.
A B A B A B

a1 b1 b1 a1 b1
a2
a2 b2 b2 a2 b2

a3 b3 b3 a3 b3
a4
a4 b4 b4 a4 b4

(a) (b) (c)

Fig 2. Relationship credentials
(a) one-to-one (b) one-to-many (c) many-to-many

EiT-M (School of Electrical and Computer Engineering) 2- 4/17

ECEG-4172 - Database System Conceptual Level Data Model

Existence: denotes whether the existence of an entity instance is dependent upon the existence of
another, related, entity instance. The existence of an entity in a relationship is defined as either
mandatory or optional.

 Design Principles

E/R Diagram
The Entity Relationship (E/R) data model is a diagrammatical data model. The elements of the
E/R model are represented by:
- Rectangles - for the Entity sets,
- Ellipses - for the Attributes,
- Diamonds - for the Relationships, and
- Lines - for the links between the attributes and the entity sets and between the entity sets
and the relationships.
- Double border Rectangles - for the weak entity sets.
- Double border Ellipses - for the multi-valued attributes.
- Dashed border Ellipses - for the derived attributes.
- Arrow Head Line - for the link between an entity set and a one-to-one or many-to-one
relationship. The arrow is headed to the one side entity set.
Example
- “EMPLOYEES” are Assigned to “TEAMS”
- “CUSTOMERS” Owns “PROJECTS”
- “TEAMS” works on “PROJECTS”
Name Name
Descr EmpId BDate

Age
TEAMS Assigned EMPLOYEES
Address

City H Addrs Phone

WorksOn SubCity Kebele H No

Name
ProjId CustId Address

Name PROJECTS Owns CUSTOMERS

SDate DDate
Fig 3. Sample Partial E/R Model

Composite attributes are represented by linked ellipses as depicted in the above figure with the
attributes “Address” and “H Addrs”.
EiT-M (School of Electrical and Computer Engineering) 2- 5/17
ECEG-4172 - Database System Conceptual Level Data Model

Relationship Attributes: Attribute(s) may be used in some relationships to describe the

relationship further. Consider the relationship “WorksOn” between the “TEAMS” and “PROJECTS”
entity sets. The relationship can be further described if an attribute “Task” is added to it as follows.
Task

PROJECTS WorksOn TEAMS

Fig 4. Relationship Attributes

Multi-way Relationship: Consider the three way relationship between the “PROJECTS”,
“TEAMS”, and “SOFTWARE” entity sets.
Cardinality Limits of a Relationship: The credential limit of a relationship is labeled as:
- 0..* or 0..∞ indicating zero or more participation of the entity in the relationship.
- 1..* or 1..∞ indicating one or more participation of the entity in the relationship.
- 0..1 indicating zero or one participation of the entity in the relationship.
- 1..1 indicating exactly one participation of the entity in the relationship.
Task

1..1 WorksOn 1..*

SOFTWARE TEAMS
0..*
1..1
PROJECTS Assigned

5..8
EMPLOYEES
Fig 5. Multi-way Relationships and credential limits

The multi-way (ternary) relationship shown in figure 5 above can be reduced to a binary
relationship with the use of an entity set in place of the relationship and having three new
relationships for the links in between the participating entity sets and the relationship.
Task

SOFTWARE Produce ASSIGNMENT Formed TEAMS

For

PROJECTS
Fig 6. Multi-way Relationships to Binary Relationship

EiT-M (School of Electrical and Computer Engineering) 2- 6/17

ECEG-4172 - Database System Conceptual Level Data Model

If the multi-way relationship set that is transformed into the binary relationship had any attributes,
these are assigned to the entity set that replaces the relationship.
Entity Set Roles in a Relationship: In some relationships a single entity set may participate more
than once in such case a label is on the link line from the entity set is used to differentiate the
Assigned By
TEAMS Assigned EMPLOYEES
Assigned
For
Fig 7. Role of an entity set in a Relationship.
participation of the entity set.
Total Participation in a Relationship: The participation of the entity set in a relationship is said
to be in total if every element of the entity set is at least related to one element in the other
participating entities through the relationship; otherwise the participation is said to partial. A total
participation in E/R model is represented by a double line from the entity set to the relationship.
Consider the relationship between the “SOFTWARE” and “ASSIGNMENT” entity sets above.
Every Software is produced in an Assignment, hence the relationship from the “SOFTWARE” to
the “ASSIGNMENT” is total.

Task

SOFTWARE Produce ASSIGNMENT

Fig 8. Total Participation of the SOFTWARE entity set.

Design Issues
The following are some useful principles to be followed in designing databases.
1. Faithfulness - first and for most, the design should be faithful to the specifications. That is
classes or entity sets and their attributes should reflect reality.
2. Avoiding Redundancy - be careful to say everything only once.
3. Simplicity - avoid introducing more elements into your design than are absolutely
necessary.
4. Picking the Right kind of Element - Sometimes we have options regarding the type of
design element used to represent a real-world concept.
Use of Entity Set versus Attributes: Generally, of something has more information associated
with it than just its name, it probably needs to be an entity set. However, if it has only its
name to contribute to the design, then it is probably better to make it an attribute.
Example: A “SOFTWARE” entity set may have a “Version” attribute, or “VERSION”
can be argued to be an entity set.
Entity versus Relationship Sets: Since relationships represent events there will always be
confusion between the entity sets and relations.
Binary versus n-ary Relationship Sets: Generally, of something has more information associated
with it than just its name, it probably needs to be an entity set. However, if it has only its
name to contribute to the design, then it is probably better to make it an attribute.

EiT-M (School of Electrical and Computer Engineering) 2- 7/17

ECEG-4172 - Database System Conceptual Level Data Model

Remarks on Designing
- Choose meaningful naming for the entities, attributes and relationships.
- Use short links.
- Cluster diagram if it has too many entities and relationships.

 Keys and Constraints

Constraints in a database design are assertions that need to be conformed to maintain the actual
aspect of the real world system under construction. Some of the commonly used classifications of
database constraints are:
1. Keys – are attributes or set of attributes that can be used to uniquely identify an entity
within the entity set. A key in E/R model is represented by underlining the attributes
belonging to the key
2. Single-value constraints – are requirements that specify a given attribute or set of
attributes are unique in certain context.
3. Referential integrity constraints – are requirements for an existence of an entity in the
database so as it can be referenced by a certain relationship. Referential integrity in E/R
model is represented by a rounded arrow head pointing to the entity set required for
existence.
4. Domain constraints – are requirements on an attribute value to be in a specified range of
values. No specific notation, but side marks may be used to represent domain constraints.
5. Participation constraints – are assertions whether the participation of an entity in a
relationship is total or partial.

Keys
As described above keys are attributes or set of attributes that suffice to distinguish entities from
each other.
A super key also know as super set is then a set of one or more attributes that in group
(collectively) can identify an entity uniquely from the entity set.
Example: Consider the “EMPLOYEES” entity set, then
- “EmpId”, “EmpId, Name”, “NationalId”, “NationalId, BDate”, … are super keys
- “Name”, “BDate” are NOT super keys
REMARK
 If K is a super set (super key) then a set consisting of K is also a super set.
The more interesting super set is the minimal super set that is referred to as the candidate key.
The candidate key is the sufficient and the necessary set of attributes to distinguish an entity set.
Example: In the “EMPLOYEES” entity set
- “EmpId”, “NationalId”, “Name, BDate” (assuming that there is no coincidence that
employees with the same name may born on the same day) … are candidate keys

EiT-M (School of Electrical and Computer Engineering) 2- 8/17

ECEG-4172 - Database System Conceptual Level Data Model

The designer of the database is the one that makes the choice of the candidate keys for
implementation, but the choice has to be made carefully. Primary key is a term used to refer to the
candidate key that is selected by the designer for implementation.

Name
ProjId CustId Address

Name PROJECTS Owns CUSTOMERS

SDate DDate

Fig 9. Primary key representation in E/R model

 Enhanced E/R Modeling

E/R modeling can be further extended to include concepts such as inheritance (specialization) and
aggregation.

Week Entity Set

From the previous discussion it is to be recalled that a weak entity can be uniquely identified with
the help of other entity. That is, for the entity to be uniquely distinguished some of its attributes in
conjunction with the primary key of another entity has to be used.
The entity which contributes its primary key is called the identifying or owner entity set. The
identifying entity set is then said to own the weak entity set.
For an entity set to be called a weak entity set the following restrictions has to hold:
 The owner entity set and the weak entity set must participate in a one-to-many relationship
set (one owner entity is associated with one or more weak entities, but each weak entity has
a single owner). This relationship set is called the identifying relationship or supporting
relationship of the weak entity set.
 The weak entity set must have total participation in the identifying relationship.
To distinguish weak entities that depend on one particular identifying entity (strong entity) an
attribute or set attributes in the weak entity set is used. Such an attribute or set of attributes is
referred to as discriminator.
The key (primary key) of a weak entity set consists of:
1. Zero or more of its own attribute; the discriminator, and
2. Key attributes of the owner (identifying) entity set.
Notation for Weak Entity Set
- Weak Entity sets are represented by Double boarder Rectangles.
- The identifying many-to-one relationship is represented by Double border Diamonds.
- If the entity set has a discriminator then it is represented by Underlining the attribute(s).

EiT-M (School of Electrical and Computer Engineering) 2- 9/17

ECEG-4172 - Database System Conceptual Level Data Model

Example:
- Consider “TEAMS” entity set Teams with the same name can be formed to work on
different projects. Thus neither “Name” nor “Description” can uniquely identify a
“TEAMS” entity. Rather an entity will be distinguished when it is related to a
“PROJECT” entity. Note that the relationship in between is a many-to-one relationship

Name
ProjId Descr

Name PROJECTS WorksOn TEAMS

SDate DDate

Fig 10. Weak entity set representation in E/R model

when it is seen from the “TEAMS” side.
The two most common sources of weak entity sets are:
- Inheritance or specialization and generalization, and
- Multi-way relationships that are transformed into binary relationships through entity sets.

Specialization and Generalization

Sometimes grouping of entity set in hierarchical structure can be helpful to show the association
between the entities in the real world.
Specialization is a top-down process in grouping of entities that are similar in some way and
distinct in some other ways in which distinctions are made explicitly.
Generalization is a design process of bottom-up approach in which multiple entity sets are
synthesized into a higher level entity set based on their common features.
Specialization and generalization in E/R model are represented by a triangle labeled “ISA” between
the entities. The vertex of the triangle is towards the generalized (super class) entity set.
Example:
- “FULL-TIME EMPLOYEES” and “PART-TIME EMPLOYEES” can be generalized to
for an entity set “EMPLOYEES”
- “PROJECTS” entity set may be further specialized into “WEB-BASED PROJECTS” and
“WIN32 PROJECTS”

PROJECTS EMPLOYEES

ISA ISA

WEB-BASED WIN32 FULL-TIME PART-TIME

PROJECTS PROJECTS EMPLOYEES EMPLOYEES

Fig 11. Specialization and generalization in E/R model.

EiT-M (School of Electrical and Computer Engineering) 2- 10/17

ECEG-4172 - Database System Conceptual Level Data Model

Constraints on Specialization and Generalization

Condition-defined vs. User-defined Lower-level Entity Sets: - Members of condition-defined
specialized (lower-level) entity sets are those satisfying an explicit condition or predicate from the
higher-level entity set; whereas, User-defined members are determined upon the entry of the entities
from the user irrespective of any constraint.
Example:
- Consider the “EMPLOYEES” entity set specialization. If there is field named “Emp Type”,
in the higher-level entity set that determines for a given entity to belong to either of the
two lower-level entity sets “FULL-TIME EMPLOYEES” and “PART-TIME
EMPLOYEES”. Then the lower-level entities are said to be condition-defined.
Disjoint vs. Overlapping Specialization: - Specialization of a higher-level entity set to lower
entities may be either Disjoint or Overlapping based on whether the entities may belong to one or
more lower-level entity sets. If the specialization allows for a higher-level entity to belong to more
than one entity then the lower-level entities are said to be Overlapping otherwise Disjoint.
Example:
- Consider the “PROJECTS” entity set specialization. If a given project is allowed to have
the two flavors “web-based” and “win32” applications then the lower-level entity sets are
said to be Overlapping. However, the “EMPLOYEES” specialization entities are disjoint,
since an employee can be either full-time or part-time but not both.
Total vs. Partial Specialization or Generalization: - If each higher-level entity set in
specialization or generalization must belong to one of the lower-level entity sets then the
specialization or generalization is said to be total, however if it is not a must for the entity to belong
any of the lower-level entity sets it is said to be partial.

Aggregation
One deficiency in E/R modeling is the fact that a relationship is allowed only between entity sets.
But in some cases it may be advantageous to have a relationship between a relationship and an
entity set or a collection of entity sets.
Example:
- Consider the “WorksOn” relationship between “TEAMS” and “PROJECTS”. From the case
study it is to be noted that every project is to be lead by a senior manager. Hence the
manager is responsible for managing the teams, projects and the outcome of the project,
the software. Therefore the resulting E/R model would be as follows.

PROJECTS WorksOn TEAMS

Manages

EMPLOYEES
Fig 12. Redundancy in E/R model

As can be seen from the diagram above redundancy loop is introduced as the E/R model doesn’t
allow a direct association of a relationship to a relationship.

EiT-M (School of Electrical and Computer Engineering) 2- 11/17

ECEG-4172 - Database System Conceptual Level Data Model

An alternative way to avoid the redundancy is with the use of the aggregation. Aggregation is an
abstraction through which collection of related entity sets and relationships are treated as high-
level entities. It allows indicating for a relationship set (identified through a box) to participate in
another relationship set.
Example:
- The previous example can be alternatively represented as follows

PROJECTS WorksOn TEAMS

Manages

EMPLOYEES
Fig 13. Aggregation in E/R model

 Object Definition Language (ODL) Data Model

Object Definition Language (ODL) is an object-oriented approach in a database design that is
standardized by the ODGM (Object Data Management Group). It is an extension of IDL (Interface
Data Language) a component in the distributed object-oriented computing standard CORBA
(Common Object Request Broker Architecture).

 Overview of Object Oriented Concept

Some of the basic ideas in object oriented programming are:
 Complex Types – Object oriented programming supports rich collection of complex data
types. Typical types that can be constructed in OOP are: Structures (Records), Reference Types
(Pointers) and Collection Types:
 Classes and Objects – Classes are types (molds or templates) for similar set of objects. And
objects are instances of classes. Every object has (current) state and behavior.
Example: For a Vehicle class: - Color, Make, Model, Gas are states and Driving,
Parking are behaviors
- Variables represent the state of the object or the class.
- Methods/functions represent the behavior of the object or the class.
- Objects interact with each other through messages that initiate tasks.
 Objects Identity – Every object has a unique identity that helps to identify the object
independent of its values.
 Inheritance – Objects can inherit properties, both state and behavior, from other objects. A
class hierarchy (super-class and sub-classes) can be formed with the help of inheritance.

EiT-M (School of Electrical and Computer Engineering) 2- 12/17

ECEG-4172 - Database System Conceptual Level Data Model

 ODL Design and Syntax

Object Definition Language (ODL) is an object oriented approach that models the real world as a
composition of objects. Objects in ODL model are some how similar to the entities in E/R model.
Real world concepts that are represented by objects of similar description and behavior are grouped
into classes.
ODL is used to define persistent classes, those whose objects may be stored permanently in the
database. The ODL classes are described by three properties (elements):
 Attributes – which are values associated with the objects whose types are from the built in
primitive types or a structure of the primitive types.
 Relationships – are connections between the object and another object of some class.
 Methods – are functions that may be applied to objects of the class.
NOTE: ODL classes look like Entity sets with binary relationships, plus methods.
An ODL class declaration includes:
1. A name for the class.
2. Optional key declaration(s).
3. Extent declaration: - name for the set of currently existing objects of the class.
4. Element declarations: - An element is either an attribute, a relationship, or a method.
The simplest form of the class declaration is:

class <name> {
<list of element declarations, separated by semicolons>
};

Attributes in ODL
Attributes are (usually) elements with a type that does not involve classes. They can be of simple,
enumerated or structured type. The syntaxes for the three types are as follows, respectively:

attribute <type> <name>;

attribute enum <typename>
{<enumlist1>, <enumlist2>,…}<name>;
attribute struct <typename>
{<type> <name1>, <type> <name2>,…}<name>;
Example:
- Consider the “EMPLOEES” class partial declaration.
class Employee {
attribute string empid;
attribute string name;
attribute integer age;
attribute enum Gender {Male, Female} gender;
attribute struct Address {string city, string hAddr, string phone} address;
};

EiT-M (School of Electrical and Computer Engineering) 2- 13/17

ECEG-4172 - Database System Conceptual Level Data Model

REMARK: The names for the enumerated and structured data types are not necessary for the
declaration but giving the name helps to refer to the type outside the class declaration
using the scoped name such as, “Employees::Gender” and “Employees::Address”.

Relationships in ODL
Syntax for a relationship in ODL is as follows

relationship <type> <name>

inverse <relationship>;
Relationship Types in ODL
The word <type> in the relationship declaration of ODL design refers to the type of relationship
and the related class in the statement.
Example:
- Consider the “EMPLOYEES” class and its relationship to “TEAMS” class.
class Employee {
attribute string empid;
attribute string name;
:
relationship Set <Teams> assigned;
:
};
The basic collection types of attributes and relationships in ODL model are:
1. Set:- Set<T> denotes a relationship to class T with finite number of association between the
objects in the class. It is an unordered set of association which doesn’t allow repetition.
2. Bag (Multiset):- It is similar to the Set operator which allows repetition of association to one
object.
3. List:- It is an association in which order is material.
4. Array:- Array<T, i> denotes a fixed number of association to objects in the class T which are
indexed.
Inverse Relationships
Unlike E/R design the relationships in ODL model are only binary. Hence for every relationship in
class C there is an inverse relationship in the related class D. Suppose class C has a relationship R to
class D, then class D must have some relationship S to class C. R and S must then be true inverses.
That is; if object d is related to object c by R, then c must be related to d by S.
Example:
- Consider the “EMPLOYEES” class and its relationship to “TEAMS” class.
class Employee {
relationship Set <Team> assigned;
};
class Team {
relationship Set <Employee> formed;
};

EiT-M (School of Electrical and Computer Engineering) 2- 14/17

ECEG-4172 - Database System Conceptual Level Data Model

Inverse relationship in ODL design is represented by using the keyword inverse.

Example:
- Consider the “EMPLOYEES” class and its relationship to “TEAMS” class.
class Employee {
relationship Set <Team> assigned
inverse Team::formed;
};
class Team {
relationship Set <Employee> formed;
inverse Employee::assigned;
};
Multiplicity of Relationships
Multiplicity of relationships in ODL design is indicated by the type operators in the relationship
and the inverse relationship.
 Many-to-many relationships: - are indicated by Set<…> for the type of the relationship
and its inverse.
 One-to-many relationships: - have Set<…> in the relationship of the “one” and just the
class for the relationship of the “many.”
 One-to-one relationships: - have classes as the type in both directions.
REMARK: Note that the Set operator in the relationship type declaration is optional and can be
omitted for one-to-many and one-to-one relationships.
Example:
- In the previous relationship declaration between “EMPLOYEES” class and the “TEAMS”
class there is a many-to-many relationship.

class Employee {
relationship Set <Team> assigned
inverse Team::formed;
};
class Team {
relationship Set <Employee> formed;
inverse Employee::assigned;
};
- Consider the one-to-many relationship between “PROJECTS” and “CUSTOMERS”

class Project {
relationship <Customer> ownedBy
inverse Customers::owns;
};
class Customer {
relationship Set <Project> owns;
inverse Projects::ownedBy;
};

EiT-M (School of Electrical and Computer Engineering) 2- 15/17

ECEG-4172 - Database System Conceptual Level Data Model

- Consider the one-to-one relationship between “PROJECTS” and “SOFTWARE”

class Project {
relationship <Software> produce
inverse Software::producedIn;
};
class Software {
relationship <Project> producedIn;
inverse Projects:: produce;
};
NOTE: Recall that ODL does not support 3-way or higher relationships. Multiway relationships in
ODL may be simulated by a “connecting” class, whose objects represent tuples of objects
that will be connected by the multiway relationship.

Inheritance in ODL
Inheritance in ODL is similar to the usual object-oriented inheritance principle. It indicates a
relationship between superclass and subclasses. Subclass lists only the properties unique to it and it
inherits its superclass’ properties.
Inheritance in ODL design is indicated by the colon operator as follows

class <Subclass>:<Superclass> {
<list of element declarations>
};
Example:
- Consider the “PART-TIME EMPLOYEES” class that is inherited from “EMPLOYEES”
class.
class PartTimeEmployee:Employee {
attribute real hourlPay;
attribute integer contractPeriod;
relationship Set <TimeSchedule> works
inverse TimeSchedule::working;
};

Declaring Keys in ODL

Recall that every object has an object Identifier and keys are attributes that can identify an object
uniquely for the set of objects.
In ODL any number of keys for a class can be declared by adding the following code after the class
name.

(key <list of keys>)

A key consisting of more than one attribute needs additional parentheses around those attributes.

EiT-M (School of Electrical and Computer Engineering) 2- 16/17

ECEG-4172 - Database System Conceptual Level Data Model

Example:

class Employee (key EmpId, NationalId, (Name, BDate)) {

attribute string empid;
attribute string name;
:
relationship Set <Teams> assigned;
inverse Teams::formed;
:
};
For each class in ODL there is an extent, the set of existing objects of that class. Extent is the
relation with that class as its schema (definition). It is indicated after the class name, along with
keys, as:
(extent <extent name> key <list of keys>)
Conventionally, singular nouns are used for class names and plural for the corresponding extent.
Example:

class Employee (extent Employees key EmpId, NationalId, (Name, BDate)) {

attribute string empid;
attribute string name;
:
relationship Set <Teams> assigned;
inverse Teams::formed;
:
};

EiT-M (School of Electrical and Computer Engineering) 2- 17/17