Databases 2022/2023

LESSON 3 - DATA MODELLING

1 PHASES OF DATABASE DESIGN

Analysis and Requirements Collection

CONCEPTUAL SCHEMA DESIGN Entity-Relationship Model

Transformation of E-R Model into Relational Tables + Normalization

Normalized Relational Tables

LOGICAL SCHEMA DESIGN

Creation of tables in the actual commercial DBMS

PHYSICAL SCHEMA DESIGN

Database: tables, constraints, index, views,
users, privileges…….

1. Requirements collection and analysis.

During this step, the database designers interview prospective database users to
understand and document their data requirements. The result of this step is a
concisely written set of users’ requirements. These requirements should be
specified in as detailed and complete form as possible.
In parallel with specifying the data requirements, it is useful to specify the known
functional requirements of the application. These consist of the user defined
operations (or transactions) that will be applied to the database, including both
retrievals and updates.
In software design, it is common to use data flow diagrams, sequence diagrams,
scenarios, and other techniques to specify functional requirements.

2. Conceptual Schema Design.

PAG. 1
Databases 2022/2023

Once the requirements have been collected and analyzed, the next step is to
create a conceptual schema for the database, using a high-level conceptual data
model. This step is called conceptual design.
The conceptual schema is a concise description of the data requirements of the
users and includes detailed descriptions of the entity, relationships, and
constraints; these are expressed using the concepts provided by the high-level
data model. Because these concepts do not include implementation details, they
are usually easier to understand and can be used to communicate with
nontechnical users.
The high-level conceptual schema can also be used as a reference to ensure that
all users’ data requirements are met and that the requirements do not conflict.
This approach enables database designers to concentrate on specifying the
properties of the data, without being concerned with storage and
implementation details.
During or after the conceptual schema design, the basic data model operations
can be used to specify the high-level user queries and operations identified
during functional analysis. This also serves to confirm that the conceptual
schema meets all the identified functional requirements. Modifications to the
conceptual schema can be introduced if some functional requirements cannot
be specified using the initial schema.

3. Logical Schema Design or data model mapping.

The next step in database design is the actual implementation of the database,
using a commercial DBMS. Most current commercial DBMSs use an
implementation data model—such as the relational or the object-relational
database model—so the conceptual schema is transformed from the high-level
data model into the implementation data model. This step is called logical design
or data model mapping; its result is a database schema in the implementation
data model of the DBMS. Data model mapping is often automated or
semiautomated within the database design tools.

• Data Normalization ensures consistency and saves storage space.

• Data Denormalization: In practice, it is sometimes necessary to use
controlled redundancy to improve the performance of queries.

4. Physical Schema Design.

PAG. 2
Databases 2022/2023

During this last step, the internal storage structures, file organizations,
indexes, access paths, and physical design parameters for the database files
are specified. In parallel with these activities, application programs are
designed and implemented as database transactions corresponding to the
high-level transaction specifications.

PAG. 3
Databases 2022/2023

2 ENTITY-RELATIONSHIP MODEL
The Entity-Relationship model was proposed by Peter Chen in 1976. The entity-
relationship model is one of the most popular conceptual database design tools existing
today. It displays the relationship of entity sets stored in a database.
The ER Model describes data as entities, relationships, and attributes

3 Entities
An entity is a thing in the real world with an independent existence. An entity may be an
object with a physical existence (for example, a particular person, car, house, or
employee) or it may be an object with a conceptual existence (for instance, a company,
a job, or a university course).

We have two types of entities:

• Strong entities: their elements exist by themselves.

• Weak entities: the existence of a weak entity depends on the existence of a strong
entity.

Entities are represented as a rectangular box enclosing the entity name. A weak entity
is distinguished by surrounding its box with double lines. Use a general, singular noun.
Example: Person, Organization, Customer, Invoice.

3.1 Entity Occurrence

An entity occurrence is a uniquely identifiable object belonging to an entity type.
Example: “John Brenan” could be an entity occurrence of the entity “Student”.

4 Relationships
A relationship is defined as an association among two or more entities.
Relationships are displayed as diamond-shaped boxes, which are connected by straight
lines to the rectangular boxes representing the participating entities. The relationship
name is written inside the diamond-shaped box. You use a verb that connects two (or
more) nouns (the entities). You should always use meaningful names.

PAG. 4
Databases 2022/2023

The degree of a relationship is the number of entity types that participate in a

relationship.

• Binary (degree 2)

• Ternary (degree 3)

• Unary (degree 1) or reflexive: Here, the same entity type participates more than
once in a relationship type with a different role for each instance.

• N-ary (>3 entities involved in the relationship): Rarely. Generally, it can be

decomposed if semantic doesn't change.

4.1 PARTICIPATION
Relationships have certain constraints that limit the possible combinations of entity
instances that may participate in the corresponding relationship set. These constraints
are determined from the mini world situation that the relationships represent.

PAG. 5
Databases 2022/2023

The ER model introduces the (min, max) notation to specify an interval of possible
participations in a relationship:

An entity of type E1 may be related to at least m1 and at most n1 entities of type E2.
Likewise, m2 is the minimum number and n2 is the maximum number of E1 entities to
which an E2 entity is related.
Types of participation

• (0, 1)
• (1, 1)
• (0, n)
• (1, n)
Minimum participation tells whether the participation is partial or total.
• If minimum cardinality = 0, then it signifies partial participation.
• If minimum cardinality = 1, then it signifies total participation.
-
4.2 CARDINALITY
Cardinality constraint defines the maximum number of relationship instances in which
an entity can participate.

PAG. 6
Databases 2022/2023

The possible cardinality ratios for binary relationship types are:

• 1: 1 (one to one)
• 1: N (one to many)
• M: N (many to many)

The cardinality is written just above the relation.

• Each project has at least one employee working on it.

• An employee is assigned to 0 or more projects.

• An employee is supervised by 0 or 1 employee (their manager)

• A employee (manager) supervises 0 or n employees

• For the Tasks Entity: given a department and an employee (an employee in a
department), how many tasks do they perform? (1, N)
• For the Employee Entity: given a department and a task (a task of a department),
how many employees does it perform? (1, N)
• For the Department Entity: given an employee and a task in how many
departments is it performed? (1, 1)

PAG. 7
Databases 2022/2023

5 Attributes
Each entity is described by a set of attributes (e.g., Employee = (Name, Address,
Birthdate (Age), Salary). An attribute is a property or characteristic of an entity.
The names of the attributes are enclosed in ovals and are attached to their entity type
by straight lines. The Key attribute is underlined.

The key attribute is the set of attributes whose values can be used to identify each entity
element uniquely.
A primary key that is made up of more than one attribute is known as a composite key.
An attribute can be allocated to an entity or to a relationship.

We can also represent an entity and its attributes as follows:

Types of attributes
Several types of attributes occur in the ER model:
PAG. 8
Databases 2022/2023

• simple versus composite: salary / address (street, post code, city)

• single-valued versus multivalued: gender / phone numbers
• stored versus derived: A derived attribute is an attribute whose values are
calculated from other attributes: birthdate / age
• required versus optional: A required attribute is an attribute that must have a
value in it, while an optional attribute may not have a value in it and can be left
blank.
There is no unanimity for its notations, but one of the representations more widespread
is the following:

5.1 Domains
In ER model a domain is a set of values for an attribute.
Attribute Domain
DNI String of 10 characters
Name String of 10 characters
Birthdate Date
Address String of 100 characters
Salary Real numbers
Age Integer numbers
Department Human resources, Marketing, Information Technology,
Administration, Sales

6 Weak Entities
The relationship between a strong entity and a weak entity is also weak. This relationship
has a dependency that can be of two types:

• Existence dependency: The occurrence of a weak entity has no sense without the
presence of the strong entity.

PAG. 9
Databases 2022/2023

• Identification dependency: Occurs when in addition to the dependency of

existence, the weak entity needs the strong entity in order to create a key.

PAG. 10