Semantic Data Modeling презентация

Contents
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

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

1. Model Entity - Relationships
HNEU, Department of Information Systems, Course Database, V. V. Fedko
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.
The concept of semantic modeling

HNEU, Department of Information Systems, Course Database, V. V. Fedko
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
Advantages of ER model
The notation language is simple and understandable for database customers and developers.
Transition from the ER-diagram to the relational database schema with clear rules
Used in CASE-tools

HNEU, Department of Information Systems, Course Database, V. V. Fedko
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.

2. Relationships between entities
HNEU, Department of Information Systems, Course Database, V. V. Fedko
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
Concept of relationship

HNEU, Department of Information Systems, Course Database, V. V. Fedko
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
Ternary
Quaternary

HNEU, Department of Information Systems, Course Database, V. V. Fedko
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.

HNEU, Department of Information Systems, Course Database, V. V. Fedko
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
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

3. Attributes and Keys
HNEU, Department of Information Systems, Course Database, V. V. Fedko
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
Concept of Attribute

HNEU, Department of Information Systems, Course Database, V. V. Fedko
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
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
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
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

4. Strong and Weak Types
HNEU, Department of Information Systems, Course Database, V. V. Fedko
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.
Entity types

HNEU, Department of Information Systems, Course Database, V. V. Fedko
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)

5. Extended Entity-Relationship Modeling (EERM)
HNEU, Department of Information Systems, Course Database, V. V. Fedko
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)
Features in Entity Occurrence

HNEU, Department of Information Systems, Course Database, V. V. Fedko
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.
Superclasses and Subclasses

HNEU, Department of Information Systems, Course Database, V. V. Fedko
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.
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
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 Person ⇒
University consist of Teacher
University consist of Student

HNEU, Department of Information Systems, Course Database, V. V. Fedko
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
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.
“T”
“S”

HNEU, Department of Information Systems, Course Database, V. V. Fedko
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.

6. Selection of Primary Keys
HNEU, Department of Information Systems, Course Database, V. V. Fedko
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)
Natural 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)
Surrogate Keys

HNEU, Department of Information Systems, Course Database, V. V. Fedko
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
Natural Key vs Surrogate Key1

HNEU, Department of Information Systems, Course Database, V. V. Fedko
Natural Key vs Surrogate Key2