Database design
Database design
➢Entity-Relationship Model
➢Conceptual design
➢Logical design
➢Normalization
1
Entity-Relationship Model
Database design
2
Entity-Relationship Model
➢Life cycle of an information system
➢Database design
➢Entities and Relationships
➢Attributes
➢Identifiers
➢Generalization
➢Documenting E-R Schematics
➢UML and E-R
3
Life cycle of an information system
Database design
4
Database design
• The design of a database is one of the activities of the process of
developing an information system
• must be seen in the broader context of the life cycle of an information system
5
Life cycle of an information system
Determination of the costs of the
different alternatives and the Feasibility study
priorities for the implementation
of each system component
6
Life cycle of an information system
• Definition of the properties and
functionalities of the Feasibility study
information system
• Requires user interaction Collection and analysis
• Produces a comprehensive, but of the requirements
informal, description of the
system to be implemented
7
Life cycle of an information system
• Divided into data and Feasibility study
application design
• Produces formal descriptions
Collection and analysis
of the requirements
Design
8
Life cycle of an information system
• Implementation of the Feasibility study
information system
according to the
characteristics defined in the Collection and analysis
design phase of the requirements
Design
Implementation
9
Life cycle of an information system
• Verification of the correct Feasibility study
functioning and quality of the
information system
• It can lead to changes in Collection and analysis
of the requirements
requirements or design
revision
Design
Implementation
Validation and testing
10
Life cycle of an information system
• System operation
Feasibility study
• Requires maintenance and
managing operations
Collection and analysis
of the requirements
Design
Implementation
Validation and testing
Operation 11
Life cycle of an information system
• Quickly create a simplified version of
Feasibility study
the system to evaluate its characteristics
• It can lead to changes in requirements
or design revision Collection and analysis
of the requirements
Design
Prototyping Implementation
Validation and testing
Operation 12
Life cycle of an information system
Feasibility study
Collection and analysis
of the requirements
Design
Prototyping Implementation
Validation and testing
Operation 13
Database design
Database design
14
Database design
• The database is an important component of the entire
system
• Data-driven design methodology
• the design of the database precedes that of the applications that
use it
• greater attention to the design phase than to the other phases
15
Design Methodology
• A design methodology consists of
• decomposition of the project activity into successive and
independent phases
• strategies to be followed in the various phases and criteria for
choosing the best strategy
• reference models to describe the input and output data of the
various phases
16
Properties of the methodology
Generality
• can be used regardless of the problem and the tools available
Quality of the result
• in terms of correctness, completeness and efficiency with respect to the
resources used
Ease of use
• of both strategies and reference models
17
Data-driven design
• For databases, methodology based on separating two key
decisions
• what to represent in the database
• conceptual design
• how to represent it
• logical and physical design
18
Stages of database design
Informal specification of the
Application reality of interest
requirements • Application properties
• Application functionalities
19
Stages of database design
Representation of informal
Application specifications in the form of a
requirements Conceptual conceptual diagram
design • formal and complete description,
referring to a conceptual model
Conceptual schema • Independent from implementation
aspects (data model)
• the aim is to represent the
information content of the
database
20
Stages of database design
Translating the conceptual
Application schema into the logical schema
requirements Conceptual • depends on the chosen data
design logic model
• takes into account the
Conceptual schema optimization of data processing
operation
• schema quality verified by
Logical design formal techniques
(normalization)
Logical schema
21
Stages of database design
Specification of the physical
Application
data storage parameters (File
requirements Conceptual and index organization)
design • produces a physical model,
which depends on the chosen
Conceptual schema DBMS
Logical design
Logical schema Physical design
Physical
schema
22
Entity-Relationship Model
Database design
23
The E-R (Entity-Relationship) model
• It is the most widely used conceptual model
• Provides constructs to describe specifications about data structure
• in a simple and understandable way
• with a graphic formalism
• independent of the data model, which can be chosen later
• Several variants are available
24
Main elements of the E-R model
➢Entity
➢Relations
➢Attributes
➢Identifiers
➢Generalizations and subsets
25
Entity name
• It represents classes of real-world objects (people,
things, events, ...), which have
Entity • common Properties
• autonomous existence
• Examples: Employee, Student, Article
• An occurrence of an entity is an object of the class that
the entity represents
26
Example of entities
STUDENT COURSE
Student
s1 c1
c2 cc5 Course
s4 s2 3
s3
c4
27
RELATIONSHIP
NAME
• Represents a logical link between two or more entities
Relationship • Examples: exam between student and course,
residence between person and municipality
• Not to be confused with the relation of the relational
model
• sometimes referred to as association
28
Relationship examples
Students Exam Course
Person Lives in Municipality
Born in
29
Occurrences of a relationship
• An occurrence of a relationship is an n-tuple (pair in the case of a
binary relationship) consisting of occurrences of entities, one for each
of the entities involved
• There can be no identical n-tuples
Student
s1 c1 Course
c5
s 4 s2 c2 c3
s3
c4
30
Cardinality of binary relationships
• They are specified for each entity that participates in a relationship
• Describe the minimum and maximum number of occurrences of a
relationship in which an occurrence of an entity can participate
• minimum can be either
• 0 (optional participation)
• 1 (participation required)
• maximum varies between
• 1 (at most one occurrence)
• N (arbitrary number of occurrences)
31
Cardinality of binary relationships
• 1-to-1 relationship
r1
Professor
p1 u1 University
r3
p 4 p2 u2
p3
u3
r2
32
Cardinality of binary relationships
• 1-to-N (many) relationship
r1
Person
p1 c1 City
r2
p 4 p2
p3 r4
c2
r3
33
Cardinality of binary relationships
• N-to-N (Many to Many) relationship
e1
Student
s1 c1 Course
e2 c5
s 4 s2 e3 c2 c3
s3
c4
e4
34
Limitations of binary relationships
Student
s1 c1 Course
c5
s4 s2 c2 c3
s3
c4
• It is not possible for a student to take the same exam more than once
35
Ternary relationship
• A student may take the same exam more than once at different times.
s1 c1 t1
• Example of an instance of the EXAM report s c t
1 1 2
...
36
Occurrences of a ternary relationship
Student
s1 c1 Course
c5
s4 s2 c2 c3
s3
c4
t1 Time
t2 t3
37
Occurrences of a ternary relationship
s1 e1 c1
Student Course
c5
s4 s2 c2 c3
s3
c4
t1 Time
t2 t3
38
Occurrences of a ternary relationship
s1 e1
Student c1 Course
c5
s4 s2 e2 c2 c3
s3
c4
t1 Time
t3
t2
39
Occurrences of a ternary relationship
s1 e1 e2
Student c1 Course
c5
s4 s2 c2 c3
s3
c4
e3
e4
t1 Time
t3
t2
40
Cardinality of ternary relationships
• Minimum cardinalities are rarely 1 for all entities involved in a
relationship
• The maximum cardinalities of an n-ary relationship are (practically)
always N
• if the participation of an entity E has a maximum cardinality of 1, it is possible
to eliminate the n-ary relationship and associate the entity E with the others
by binary relations
41
Recursive relationship
Under Supervises CEO
Employee
(0,1) (0,N) Purchases/Sales
Director
Marketing
Manager R&D
Director
Director
Northern Italy
Director
Southern Italy Graphic
Director Manager
Promotions
Manager
Confectionery Pasta
Sector
Manager Production
Manager
• Relationship between an entity and itself
• If the relationship is not symmetrical, the two roles of the entity
must be defined 42
Recursive relationship
CEO
Purchases/Sales
Director
Marketing
Director
R&D Director
Southern Italy Northern Italy
Director Director
Promotions
Graphic Manager
Manager
Confectionery Pasta Production
Sector Manager
Manager 43
Recursive relationship
CEO
Under Supervises
Employee
Purchases/Sales
Director
(0,N) (0,N)
Marketing
Director
Manager R&D
Director
Northern Italy
Director
Southern Italy Graphic
Director Manager
Promotions
Manager
Confectionery Pasta
Sector Production
Manager Manager
• An employee might have several managers
44
• Describes an elementary property of an entity
or relationship
• Examples
Attribute • surname, first name, student ID are attributes that
describe the student entity
• grade is an attribute that describes the exam
relationship
• Each attribute is characterized by the domain,
the set of admissible values for the attribute
45
Examples of attributes
Fiscal code
Residency
Name (1,1) (1,N) Name
Surname Person Municipality
Province
Transfer date
(1,1) (0,N)
Birth Birth date
Student Id
Name
Exam Code
(0,N) (0,N)
Surname Student Course Name
Grade
(1,N)
Date Professor’s
Time name
46
Composite attribute
Name of attr. 1
Name of attr. 2
Name of the ...
composite attr. Name of attr. i
• Group of attributes that have closely connected meanings or uses.
• Example:
Street
Number
Address
Postal Code
47
Cardinality of an attribute
• Can be specified for entity or relationship attributes
• Describes the minimum and maximum number of attribute values associated
with an occurrence of an entity or relationship
• if it is omitted it corresponds to (1,1)
• minimum 0 corresponds to an attribute that admits a null value
• maximum N corresponds to an attribute that can have more than one value
for the same occurrence (multivalued attribute)
Fiscal code
Name Educational
Surname Person qualification
(0,N)
Profession
(0,1)
48
• It is specified for each entity
• Describes the concepts (attributes and/or
entities) of the schema that allow you to
uniquely identify the occurrences of the
entities
• each entity must have at least one identifier
Identifier • there can be more than one appropriate identifier
for an entity
• The identifier can be
• internal or external
• simple or composite
49
Internal identifier
Simple Composite
• consisting of a single attribute • consisting of multiple attributes
50
External identifier
• An entity that does not have internal attributes sufficient to define
an identifier is called a weak entity
• The weak entity must participate with cardinality (1,1) in each of
the relationships that provide part of the identifier
(0,N)
(0,N)
51
Remarks
• An external identifier can involve an entity that is itself externally
identified
• No identification cycles should be generated
52
Remarks
• Relationships do not have identifiers
Student ID Course code Name
Exam
Surname
Name Student Course
(0,N) (0,N)
Date
53
It describes a logical link between an entity E and
one or more entities E1, E2,…, En, that are particular
cases of E.
• E is called parent entity, and is a generalization of
E1, E2,…, En
• E1, E2,…, En are called child entities, and are
specializations of E
Generalization
54
Generalization: example
p1 Person
p5
p2 p3
p4
55
Generalization: example
p1 Person
p5
p2 p3
p4
56
Generalization: example
p1 Person
p5
(t,e) p2 p3
p4
57
Generalization: example
s1 Athlete
s5
s2 s3
s4
58
Generalization: example
s1 Athlete
s5
s2 s3
s4
59
Generalization: example
soccer player
nor skier
nor soccer player
s1 Athlete
s5
s2 s3
s4
both skier
and soccer player
skier
60
Generalization: properties
• Each occurrence of a child entity is also an occurrence of the parent
entity
• Each property of the parent entity (attributes, identifiers,
relationships, other generalizations) is also a property of each child
entity
• property known as inheritance
• An entity can be involved in multiple different generalizations
61
Generalization: properties
• Orthogonal characteristics
• total generalization if each instance of the parent entity is an instance of at
least one of the child entities, partial otherwise.
• exclusive if each instance of the parent entity is at most one instance of one of
the child entities, overlapping otherwise.
62
Generalization: incorrect example
ID
Name
GPA (0,1) Person Department (0,1)
Surname Student Professor Surname
63
Generalization: incorrect example
ID
Name
GPA (0,1) Person Department (0,1)
Surname Student Professor Surname
64
Generalization: incorrect example
Name Fiscal code
Surname Person
Department (0,1)
GPA (0,1)
Student Professor
65
Generalization: incorrect example
Name Fiscal code
Surname Person
Department (0,1)
GPA (0,1)
Student Professor
66
Generalization: correct example
ID
Name
Surname Person
GPA Student Professor Department
67
Subset
• Particular case of generalization with only one child entity
• the generalization is always partial and exclusive
68
ER Model Documentation
Database design
69
Documenting E-R models
70
Documenting E-R models
Data Dictionary
Enrich the E-R schema with natural language
descriptions of entities, relationships, and
attributes
71
Data dictionary: example
Entity Description Attributes Identifier
Student University student Student ID, Surname, Student ID
Name, CFU acquired,
Grades average
Professor University professor Professor ID, Department, Professor ID
Surname, Name
Course Courses offered by the Course code, Name, CFU Course code
university
Time Dates on which exams Date Date
were taken
72
Data dictionary: example
Relationship Description Entities involved Attributes
Exam It associates a student Student (0,N), Grade
to the exams taken Course (0,N),
and memorizes the Time (1,N)
mark obtained
Holder It associates each Course (1,1),
course to the Professor (0,N)
professor who
teachers the course.
73
Documenting E-R models
Data Dictionary Data Integrity Constraints
Enrich the E-R schema with natural language They cannot always be explicitly stated in an E-R
descriptions of entities, relationships, and scheme
attributes They can be described in natural language
74
Data integrity constraints: examples
Integrity constraints
RV1 The grade of an exam can only take values between 0 and 30
RV2 Each student cannot pass the same exam twice
RV3 A student may not take more than three exams for the same course during the same
academic year
75
Documenting E-R models
Data Dictionary Data Integrity Constraints Data Derivation Rules
Enrich the E-R schema with natural They cannot always be explicitly Explicitly define that a concept of
language descriptions of entities, stated in an E-R scheme the schema can be obtained (by
relationships, and attributes They can be described in natural inference or arithmetic calculation)
language from other concepts of the schema
76
Derivation rules: examples
Derivation rules
RD1 The number of credits acquired by a student is obtained by adding the number of
credits of the courses for which the student has passed the exam
RD2 The average mark is obtained by calculating the average of the marks of the exams
passed by a student
77
UML vs ER
Database design
78
UML and ER
UML (Unified Modeling Language)
• Modeling a software application
• structural and behavioural aspects (data, operations, processes and
architectures)
• Rich formalism
• class diagram, actor diagram, sequence diagram, communication diagram,
state diagram,...
ER
• Modeling a database
• structural aspects of an application
• elements tailored to the modelling of a database
79
UML vs ER
• Different formalisms
• The class diagram of an application is different
from the E-R schema of the database
• The class diagram, even if designed for
different uses, can be adapted for the
description of the conceptual design of a
database
• Main Differences of UML vs ER
• no standard notation to define identifiers
• ability to add notes to comment on
diagrams
• possibility to indicate the direction of
navigation of an association (not relevant
in the design of a database) 80