University of Banja Luka Faculty of Electrical Engineering

University of Banja Luka Faculty of Electrical Engineering CONCEPTUAL DATA MODELING Goran Banjac goran. banjac@etfbl. net 11/23/2020

Content Introduction Data abstraction Data models E-R model UML class diagram Comparation of E-R model and UML class diagram Conclusion Conceptual data modeling 11/23/2020 2

Introduction � Database design is one of the most important phases of development of database system (i. e. system with a database) � The goal of this phase is to obtain a conceptual database schema � Poorly organized database can be source of a bad application characteristics, and can lead to frequent, large and often expensive application changes � Therefore, a systematic approach and usage of an adequate methodology is a necessity � Such methodology should be close to man and his perception of the real world, and must have concepts that are able to represent man’s perceptions of the real world in the form of a conceptual schema on a high level of abstraction � First phase of database design is a conceptual modeling, a specification of database structure on a high level of abstraction Conceptual data modeling 11/23/2020 3

Data abstraction � Database systems support three levels of data abstraction (ANSI/SPARC database architecture, 1975. ): ◦ Physical level. The physical level describes how the data are actually stored on the physical medium, with the goal to implement more efficient data access. ◦ Logical level. Logical (conceptual) level describes the logical structure of the entire database. ◦ View level. Many users of the database system need to access only a part of the database. The view level of abstraction exists to simplify their interaction with the system. Conceptual data modeling 11/23/2020 4

Data abstraction View 1 View 2 . . . View n View level (local logical level) Logical level (global logical level) Physical level (how the data are actually stored) Conceptual data modeling 11/23/2020 5

Data models � Data model is a collection of conceptual tools for describing data, data relationships, data semantics, and consistency constraints � Models that use concepts that are close to man are called high level models or conceptual models ◦ The most often used such model is Entity-Relationship (ER) model � Models that describe how data are actually stored on secondary storage devices are called physical data models or low level models � Between conceptual and physical models there is a class of representational (or implementational) data models that use concepts that are close to man, and also represent how the data are organized in database Conceptual data modeling 11/23/2020 6

E-R model � Concept of E-R data model is simple and intuitive � E-R model is based on a perception of a real world that consists of a collection of basic objects, called entities, and of relationships among these objects � E-R model is very useful in mapping the meanings and interactions of real-world enterprises onto a conceptual schema which can be represented by a graphical notation called E-R diagram Conceptual data modeling 11/23/2020 7

Basic concepts � Entity is a “thing” or “object” in the real world that is distinguishable from all other objects � Every entity has a set of properties which are in E-R model represented by a set of attributes � Relationship is an association among several entities ◦ Most of the relationships are binary � Key is a set of one or more attributes that, taken collectively, allow us to identify uniquely an entity/relationship in the entity set/relationship set Conceptual data modeling 11/23/2020 8

E-R model symbols Е entity type / set A attribute A primary key A multivalued attribute A derived attribute R relationship type / set Е weak entity type / set A discriminating attribute of weak entity type R identifying relationship type / set R Е total participation of entity type in relationship R R m. . n rolename Е cardinality limits Е role indicator R 1: 1 relationship type R 1: M relationship type R M: 1 relationship type R M: M relationship type Conceptual data modeling 11/23/2020 9

E-R model symbols ISA superclass/subclass relationship type (specialization/ generalization) ISA total specialization/ generalization Е 1 ISA disjoint union R 1 disjoint specialization/ generalization union/category Е 2 aggregation R 2 Е 3 Conceptual data modeling 11/23/2020 10

Alternative E-R notations Entities and attributes Relationships and cardinality mappings b) a) E A 1# Е Е A 2 A 3 … A 1# A 2 A 3 … Е A 1 A 2 A 3 . . . (0, N) N 1 c) (0, 1) (0, N) (1, 1) 1 N (1, N) (0, 1) Batini / Ceri / Navathe notation Elmasri / Navathe notation Min / max notation “Crow’s foot” notation IE / IDEF 1 X notations. Oracle notation Entity type/set E and attributes A 1 (primary key), A 2, A 3 …. Notation with arrows Generalization and specialization C C d S 1 S 2 C o S 2 Elmasri / Navathe notation S 1 S 2 S 3 Batini / Ceri / Navathe notation S 1 S 2 S 3 IE (Information Engineering) notation Conceptual data modeling 11/23/2020 11

Example (conceptual schema for University database) First. Name Last. Name Address Id Birth. Date PERSON ISA Title Salary PROFESSOR GPA disjoint Grade SAddress TAKES STUDENT Semester Type Address REG. Phone FName C_D COURSE Course. No ENROLS TEACHES BEL. DEPARTMENT Duration DId ECTS DName Title FACULTY Exam. Date ECTS OF EXAM Conceptual data modeling 11/23/2020 Room 12

Example (conceptual schema for University database) Conceptual data modeling 11/23/2020 13

UML class diagrams � Unified Modeling Language (UML) provides graphical means for visualization, specification, design, and documentation of models of a software systems � UML can be used for business modeling and modeling of other non-software systems too � UML class diagram is a stucture diagram which shows structure of the system by showing classes and the relationships among the classes � Class diagram is the most widely used diagram in modeling object-oriented system � It can be used for conceptual modeling, although standard notation is not adjusted for that purpose Conceptual data modeling 11/23/2020 14

Basic concepts � Main concepts of the class diagrams are: ◦ Classes ◦ Associations � Class is a descriptor which describes a set of objects (entities) that share the same properties, behaviour and relationships with other objects (entities) � Association is a relationship between several classes Conceptual data modeling 11/23/2020 15

Basic concepts (examples) 1. . * symmetric association with specified multiplicity 1 asymmetric association aggregation composition generalization ternary association with association class Conceptual data modeling 11/23/2020 16

Example (conceptual schema for University database) Conceptual data modeling 11/23/2020 17

Comparation of E-R model and UML class diagram � Classes are equivalent to the entity types � Association concept is equivalent to the concept of relationship � Standard UML does not have symbols to specify primary key attributes. In literature, often used specification of primary key is stereotype or {PK} constraint. � Standard UML also does not have means to specify other E-R concepts like weak entity types, composite attributes, etc. � Composition can be used to represent composite attributes of the class Conceptual data modeling 11/23/2020 18

Comparation of E-R model and UML class diagram � Multiplicity of the association’s end specifies not only the cardinality mapping but also the participation cardinality � In n-ary associations, we can not draw a conclusion about partiticipation cardinalities of the classes � E-R concept of aggregation is not supported in UML class diagram � Specialization constraints on class diagrams are modeled using constraints: {disjoint}, {operlapping}, {complete}, and {incomplete} Conceptual data modeling 11/23/2020 19

Conclusion � First phase of database design is a conceptual modeling � The most often used model for conceptual data modeling is E-R model � Due to intense development and increasing use of UML in all stages of software system life cycle, class diagram is often used during the database design phase, although standard notation is not adjusted for that purpose � In practice, standard UML notation can be adjusted for conceptual data modeling by using different profiles � At our faculty, both notations are used, but with increasing use of UML class diagrams in student projects and diploma thesis Conceptual data modeling 11/23/2020 20

Conceptual data modeling 11/23/2020 21