Semantic Data Modeling

1. Topic 8. Semantic Data Modeling

HNEU,
Department of Information Systems,
Course Database,
V. V. Fedko

2. Contents

1.
2.
3.
4.
5.
6.
Model "entity - relationship"
Relationships between entities
Attributes and Keys
Strong and Weak Types
Selection of Primary Keys
Extended Entity-Relationship Modeling (EERM)
HNEU, Department of Information Systems, Course Database, V. V. Fedko
2

3. Test questions

En:
Ru:
1. Describe the purpose of the
concepts of entity and relationship.
2. Build an ER model using the
superclass Student and subclasses
Schoolboy, Student, Graduate
Student.
3. Write down the attributes of the
Address entity and specify the
natural and surrogate keys.
1. Опишите назначение понятий
сущность и отношение.
2. Постройте ER-модель с
использованием суперкласса
Учащийся и подклассов
Школьник, Студент, Аспирант.
3. Запишите атрибуты сущности
Адрес и укажите естественный и
суррогатный ключи.
HNEU, Department of Information Systems, Course Database, V. V. Fedko
3

4. 1. Model Entity - Relationships

The concept of semantic modeling
A semantic data model is a conceptual data model in which semantic information is
included (the meaning of this data).
Cause: One of the most difficult aspects of database design is the fact that designers,
programmers, and end-users tend to view data and its use in different ways.
For many applications, it is difficult to model the subject area on the basis of flat tables.
Unfortunately, unless we gain a common understanding that reflects how the
enterprise operates, the design we produce will fail to meet the users’ requirements.
To ensure that we get a precise understanding of the nature of the data and how it is
used by the enterprise, we need a model for communication that is nontechnical and
free of ambiguities.
As a tool for semantic modeling, various versions of entity-relationship diagrams (ER Entity-Relationship) are used.
HNEU, Department of Information Systems, Course Database, V. V. Fedko
4

5.

Entity-Relationship model
An entity-relationship model (ER model for short) describes interrelated things of
interest in a specific domain of knowledge.
A basic ER model is composed of entity types (which classify the things of interest)
and specifies relationships that can exist between entities (instances of those entity
types).
An entity–relationship model is usually the result of systematic analysis to define and
describe what is important to processes in an area of a business. It does not define
the business processes; it only presents a business data schema in graphical form. It
is usually drawn in a graphical form as boxes (entities) that are connected by lines
(relationships) which express the associations and dependencies between entities.
Entity-relationship modeling was developed for database design
by Peter Chen and published in a 1976 paper.
HNEU, Department of Information Systems, Course Database, V. V. Fedko
5

6.

Advantages of ER model
1. The notation language is simple and understandable for
database customers and developers.
2. Transition from the ER-diagram to the relational database
schema with clear rules
3. Used in CASE-tools
HNEU, Department of Information Systems, Course Database, V. V. Fedko
6

7.

Concept of Entity
Entity type is a group of objects with the same properties (class).
An entity type might be:
• an object with physical existence. E.g. a product, a student, a car
• an object with conceptual existence. E.g. a course, a job, a position
Entity occurrence is a uniquely identifiable object of an entity type (class instance).
Product: bread, long loaf, bun.
An entity type contains a logical group of attributes that represent information
important from the point of view of the modeled area.
HNEU, Department of Information Systems, Course Database, V. V. Fedko
7

8. 2. Relationships between entities

Concept of relationship
A relationship type is a set of associations between one or more entity types (class).
Each relationship type is given a name that describes its function:
teacher works_at department,
student has photo.
A relationship occurrence is a uniquely identifiable association that includes one
occurrence from each participating entity type (class instance).
Rudenko works_at Department of Information Systems,
Fedko works_at Department of Information Systems,
Denisova works_at Department of Math,
___________________
Smith has Smith`s image
HNEU, Department of Information Systems, Course Database, V. V. Fedko
8

9.

Degree of Relationship Type
Degree of a relationship type is the number of participating entity types in a relationship.
A relationship of degree two is called binary, three – ternary, four - quaternary.
Binary
Teacher
Works_at
Department
Quaternary
Ternary
Teacher
Registers
Department
Teacher
Student
Discipline
Registers
Department
Student
HNEU, Department of Information Systems, Course Database, V. V. Fedko
9

10.

Binary Relationship
Binary relationship is the most common type of relationship in ER-models.
1:1 - one-to-one (Department – Manager, Employee - Photo)
1:М - one-to-many (Department – Employee, Blog – Post)
М:N - many-to-many (Manufacturer – Product, Boy - Girl)
If there is a 1: 1 relationship in the scheme, then related entities can be combined into
one entity.
Relationship M: N can be converted to an intermediate entity and
two relationships 1: M.
Manufacturer
Manufacturer
1
Produce
M
Make
M Manufacturer`s
_Product
N
Product
N
HNEU, Department of Information Systems, Course Database, V. V. Fedko
Is
1
Product
10

11.

Recursive Relationship
Recursive relationship is a relationship type in which the same entity type
participates more than once in different roles.
Example: employee is managed by another employee
Employee
is managed
by
• a team plays a game against another
team
• a person is a child of a person
• an organizational unit (e.g. department,
division, branch, ...) comprises other units
• a course is a prerequisite for another
course
HNEU, Department of Information Systems, Course Database, V. V. Fedko
11

12. 3. Attributes and Keys

Concept of Attribute
Attribute is a property of an entity or a relationship type.
Attribute may be mandatory or optional (NULL).
Each attribute belongs to a certain domain (type).
Domain - the set of possible attribute values.
Examples:
Name: string,
Price: decimal > 0,
Age: integer >= 0, <= 120
HNEU, Department of Information Systems, Course Database, V. V. Fedko
12

13.

Simple and Composite Attributes
Simple attribute is an attribute composed of a single component with an
independent existence.
Simple attributes cannot be further subdivided into smaller components (atomic).
Examples: Price, Quantity, Name.
Composite attribute is an attribute composed of multiple components, each with
an independent existence.
Some composite attributes can be further divided to yield smaller components
with an independent existence of their own.
Example: Address = City + Street + Number
HNEU, Department of Information Systems, Course Database, V. V. Fedko
13

14.

Single-valued and Multi-valued Attributes
Single-valued attribute is an attribute that holds a single value for each
occurrence of an entity type.
Examples: Price, Sale_date, Name.
Multi-valued attribute is an attribute that holds multiple values for each
occurrence of an entity type.
Examples: Phones=(0571112223, 0951112223, 0671112223)
A multi-valued attribute may have a set of numbers with upper and lower limits.
Examples:
Phones for Manufacturer it may be between 1 and 3.
Colours for Car_model.
HNEU, Department of Information Systems, Course Database, V. V. Fedko
14

15.

Derived Attributes
Derived attribute is an attribute that represents a value that is derivable from
the value of a related attribute or set of attributes, not necessarily in the same
entity type.
Examples:
Cost = Price * Quantity
Sale_year = YEAR(Sale_date)
Avg_price = AVG(Price) -- AVG – aggregate function
HNEU, Department of Information Systems, Course Database, V. V. Fedko
15

16.

Keys
Primary Key is an attribute or group of attributes whose values ​uniquely identify
an entity occurrence.
For one entity, there may be several attributes or sets of attributes that claim to be
the primary key. Such applicants are called candidate key.
Composite key is candidate key that consists of two or more attributes
Key attributes must not contain null values.
Alternate key is a candidate key that has not become primary.
Secondary key - an attribute or group of attributes whose values ​define a set of
entity instances with some common properties.
Examples:
(SaleId, Sale_date, Manufacturer_name, Product_name, Quantity)
(SaleId, Sale_date, Manufacturer_name, Product_name, Quantity)
Secondary key: Quantity, Sale_date
HNEU, Department of Information Systems, Course Database, V. V. Fedko
16

17. 4. Strong and Weak Types

Entity types
Strong entity type is an entity type that is not existence-dependent on
some other entity type.
Weak entity type is an entity type that is existence-dependent on some
other entity type.
Weak entity types are sometimes referred to as child, dependent, or
subordinate entities and strong entity types as parent, owner, or dominant
entities
Examples:
Strong entity type: Product, Manufacturer.
Weak entity type: Sale.
HNEU, Department of Information Systems, Course Database, V. V. Fedko
17

18.

Relationship types
A weak relationship, also known as a non-identifying relationship, exists if the PK of
the related entity does not contain a PK component of the parent entity.
By default, relationships are established by having the PK of the parent entity appear
as an FK on the related entity.
Example:
Product = (Product_name, Price, Purchase_price)
Sale = (SaleId, Sale_date, Manufacturer_name, Product_name, Quantity)
_______________________________
A strong relationship, also known as an identifying relationship, exists when the PK
of the related entity contains a PK component of the parent entity.
Example:
Product = (Product_name, Price, Purchase_price)
Sale = (Sale_date, Manufacturer_name, Product_name, Quantity)
HNEU, Department of Information Systems, Course Database, V. V. Fedko
18

19. 5. Extended Entity-Relationship Modeling (EERM)

Features in Entity Occurrence
An entity set may include subgroupings of
entities that are distinct in some way from
other entities in the set.
For instance, a subset of entities within an
entity set may have attributes that are not
shared by all the entities in the entity set.
Example:
Person = (Name, Address)
Teacher=(Department, Discipline),
Student=(Faculty, Course, Group)
Person
Name
Address
Teacher
Department
Discipline
HNEU, Department of Information Systems, Course Database, V. V. Fedko
Student
Faculty
Course
Group
19

20.

Superclasses and Subclasses
Superclass is an entity type that includes one or more distinct
subgroupings of its occurrences, which must be represented in a data
model.
Subclass is a distinct subgrouping of occurrences of an entity type,
which must be represented in a data model.
Example:
Superclass: Person, Teacher, Student
Subclasses: Teacher, Student
Entity types that have distinct subclasses are called superclasses.
The relationship between a superclass and any one of its subclasses is
called a superclass/subclass relationship.
Example: Person/Teacher has a superclass/subclass relationship.
HNEU, Department of Information Systems, Course Database, V. V. Fedko
20

21.

Attribute Inheritance
An entity in a subclass represents the same “real world” object as in the superclass,
and may possess subclass-specific attributes, as well as those associated with the
superclass.
Person
Name
Address
Teacher
Department
Discipline
Student
Faculty
Course
Group
Entity Teacher = Superclass Person +
Subclass Teacher =
(Name, Address, Department, Discipline)
A member of the Teacher subclass
inherits all the attributes of the Person
superclass, together with those
specifically associated with the Teacher
subclass
HNEU, Department of Information Systems, Course Database, V. V. Fedko
21

22.

Inheritance of Primary Key & Relationship
All entity subtypes inherit their primary key attribute from their supertype:
Entity Teacher = (Name, Address, Department, Discipline)
Entity subtypes inherit all relationships in which the supertype entity
participates:
University consist of Teacher
University consist of Person ⇒ University consist of Student
HNEU, Department of Information Systems, Course Database, V. V. Fedko
22

23.

Type Hierarchy
A subclass is an entity in its own right and so it may also have
one or more subclasses. An entity and its subclasses and their
subclasses, and so on, is called a type hierarchy.
The following type hierarchies exist:
• specialization hierarchy (a Teacher is a Person specialization),
• generalization hierarchy (Person is a generalization of the
Teacher)
• IS-A hierarchy (Teacher IS-A (member of) Person ).
HNEU, Department of Information Systems, Course Database, V. V. Fedko
23

24.

Person
Name
Subtype Discriminator
A subtype discriminator is the
attribute in the supertype entity
that determines to which subtype
the supertype occurrence is
related.
It is common practice to show the
subtype discriminator and its
value for each subtype.
Address
Person_type
Person_type
“T”
Teacher
“S”
Student
Name (FK)
Name (FK)
Discipline
Department
Course
Group
Faculty
HNEU, Department of Information Systems, Course Database, V. V. Fedko
24

25.

Completeness of Subtypes
An incomplete subtype indicates that the modeler feels there may be other subtype
entities that have not yet been discovered.
A complete subtype indicates that the modeler is certain that all possible subtype
entities are included in the subtype structure.
Person
Person
Name
Name
Address
Person_type
Address
Person_type
Person_type
Teacher
Student
Person_type
Teacher
Student
Name (FK)
Name (FK)
Name (FK)
Name (FK)
Discipline
Department
Course
Group
Faculty
Discipline
Department
Course
Group
Faculty
HNEU, Department of Information Systems, Course Database, V. V. Fedko
Engineer
Name (FK)
25

26. 6. Selection of Primary Keys

Natural Keys
A natural key is one or more existing data attributes that are unique to the
business concept.
Product = (Product_name, Price, Purchase_price)
Sale = (Sale_date, Manufacturer_name, Product_name, Quantity)
Surrogate Keys
A surrogate key is a new attribute that has no business meaning.
Product = (ProductId, Product_name, Price, Purchase_price)
Sale = (SaleId, Sale_date, Manufacturer_name, Product_name, Quantity)
HNEU, Department of Information Systems, Course Database, V. V. Fedko
26

27.

Rules when choosing a primary key
• The primary key must uniquely identify each record.
• A record’s primary-key value can’t be null.
• The primary key-value must exist when the record is created.
• The primary key must remain stable — you can’t change the primary-key
field(s).
• The primary key must be compact and contain the fewest possible
attributes.
• The primary-key value can’t be changed.
HNEU, Department of Information Systems, Course Database, V. V. Fedko
27

28.

Natural Key vs Surrogate Key1
Rule
Natural Key
The primary key must uniquely ••••
identify each record.
But subject to input and other
human errors
The primary key can’t be null. ••
Can’t enter record until value is
known
The primary key must exist
••
when the record is created.
Can’t enter record until value is
known
Surrogate Key
••••
System-generated value is always
unique
••••
Generated by system when
record is created
••••
Generated by system when
record is created
HNEU, Department of Information Systems, Course Database, V. V. Fedko
28

29.

Natural Key vs Surrogate Key2
Rule
The primary key must remain
stable — you can’t change the
primary-key field(s).
The primary key must be compact
and contain the fewest possible
attributes.
The primary-key value can’t be
changed.
Natural Key
Surrogate Key
Natural keys are subject to
business rules and other outside
influences.
••
A natural key can consist of many
fields.
Natural data often changes.
••••
Surrogate keys are neutral to
the application’s function and
the data.
••••
Surrogate key is always just
one field
••••
No reason to change a
meaningless value
HNEU, Department of Information Systems, Course Database, V. V. Fedko
29

30. Test questions

En:
Ru:
1. Describe the purpose of the
concepts of entity and relationship.
2. Build an ER model using the
superclass Student and subclasses
Schoolboy, Student, Graduate
Student.
3. Write down the attributes of the
Address entity and specify the
natural and surrogate keys.
1. Опишите назначение понятий
сущность и отношение.
2. Постройте ER-модель с
использованием суперкласса
Учащийся и подклассов
Школьник, Студент, Аспирант.
3. Запишите атрибуты сущности
Адрес и укажите естественный и
суррогатный ключи.
HNEU, Department of Information Systems, Course Database, V. V. Fedko
30