Databases

Database Fundamentals
Goals
• Understanding the fundamental concepts about databases:
• data
• databases
• database management systems (DBMS)
• Understanding the common models used for designing
databases
• Understanding the basic elements that constitute the database
management systems and their main functionalities
• Understanding the steps to designing a database system
Data
• Data is a physical representation of real-world relevant
information.
• Database modeling is the transformation of this information
into a computer system.
• We consider three “worlds” regarding data:
• The real world, which contains the information to be represented
• The conceptual/logical world, which models the real-world
information
• The physical world, which contains the model with computer/digital
elements
Data

Real world

Four sheep

Farm Database
Item Type Amount

Sheep Animal 4
Conceptual/logical world
… … …

Physical world
Database definition (I)
• A database is a computerized system designed to store an organized
collection of data and facilitate its access, search, classification,
and organization by a group of users and applications
• A database provides a physical representation for relevant information
in a real-world problem (anything important to a person or an
organization) to support processes that need the information.
• The data stored in a database is grouped according to a series of
relevant concepts, facilitating their combination to generate new
information
Database definition (II)
• Designing a database consists in understanding a reality
and describing it in a way that the elements relevant to the
context and their relationships are representable.
• The three main axes of a database system are:
• The data, which represent the information,
• The operations, that allow for the users to work with the database,
and
• The computerized system, which allows for storing the data.
Database Management Systems (DBMS)
• A software that enables users and other applications to
interact with (define, create, maintain and control access to)
a database through an interface.
• The main goal of a DBMS is to provide mechanisms with
high system performance, while guaranteeing the availability
and the validity of the data.
• Elements:
• File manager, which assigns the physical space (digital storage)
• Database engine, which manages access to the data
Database Management Systems (II)
Functionalities of DBMS:
• Enabling the storage of different types of data
• Enabling the manipulation of the data in an efficient and secure
way.
• Allowing concurrent access to data.
• Offering mechanisms that allow the recovery of the information in
case of
• system failure.
• data corruption.
Database Management Systems (III)
Query Processor
Query
(DML)

Schema Query Optimizer

Schema Processor
Real-Time Manager
(DDL)

Database Result
Data + Metadata

DDL: Database Design Language

DML: Data Manipulation Language
Database Management Systems (IV)

Schema: Every farm has items. Every item has an acquisition

date, amount and type. Valid item types include animals, tools,
fungibles and installations.

I want to know what new animals were acquired last month

Query: Show me the items of type animal that were acquired

over the last 30 days.
Item Type Amount Acquisition date

Pig Animal 1 10/09/2021

Result
Sheep Animal 3 04/09/2021

Possum Animal 2 21/09/2021

Data Models
• Hierarchical model
• Relates the elements in the form of a tree.
• Only allows one-to-many relationships.
• Network model
• Relates the elements in the form of a network (generic graph).
• Allows many-to-many relationships but not between more than two elements
• Relational model
• Allows for representing many-to-many relationships and between more than two
elements
• Introduces the concept of referential integrity, which guarantees coherence of data
• Document model
• Information is structured in the form of documents
• There are no relationships between data
Hierarchic Model Example
• Tree structure
• Library Example: we are interested in C. J. Date
maintaining information on books,
publishers, and authors.
• Hierarchic model doesn’t allow for
representing books with more than one An Introduction to A Guide to
author (>1 parents not allowed). Database Systems SQL Standard

• It forces data redundancy by not allowing

the association of publishers with more than
one book.
Pearson O’Reilly O’Reilly
Education Media Media
Network Model Example
• Generic graph structure (not restricted to
any particular structure) with entities as nodes
and relationships as links C. J. Date H. Darwen

• In our library example, network model allows

for representing books with more than one
author.
An Introduction to A Guide to
• Does not allow for establishing a 3-way Database Systems SQL Standard
relationship between publishers, authors,
and books.
• The paths are fixed, not allowing access
flexibility. Pearson O’Reilly
Education Media
Relational Model Example
• Consists of a set of tables (one per entity)
with a unique key (one or more attributes)
identifying each row of each table.
First Name
• In relational model, relationship between Author Last Name
City of Birth
more than two entities can be modeled

• Relational model allows for linking

elements between different entities through
key attributes. Book Publisher
• This model guarantees the coherence
when referencing data Title
ISBN
Name
Editor in chief
Publication date
Database Design Steps
• Gathering and analysis of requirements
• Identifying the world that we want to represent in a database
• Conceptual model design
• Graphical representation of the elements of the world we want to represent
• We will use the entity-relationship model
• Logical model design
• Logical representation of the elements of the conceptual model
• Easily representable in a computer system
• We will use the relational model
• Physical model design
• Adaptation of the logical model to the requirements of a DBMS
• We will use MySQL as our DBMS
The Conceptual Model
• Represents real-world information in a visual form.
• Does not have any connection to or dependence on any particular
technology
• We use Entity-Relationship (ER) Diagrams
• Formed by three types of elements:

Element Shape in ER Diagram

Entities author book

Attributes title name

Relationships wrote
The Conceptual Model – The ER Diagram
• We want to keep a list of
establishments and their types.
ID
• Each establishment will have a name
series of attributes that describe phone
Business

it. owner
N

• A subset of these attributes description belong

should uniquely identify it (ID in address

1
this example). location
N
Business
• Each establishment may have a Category
type and subtype from more
1
be
generic to more specific.
ID
Name
Amount
Relational Model
• Consists in one or more tables each representing an entity we want to
model.
• Currently, the most commonly used logical model.
• Based on relational algebra, which itself is based on set theory.
• The relationships between different elements follow some integrity rules.
• Formed by different elements:
• Relationships
• Tuples
• Attributes
• Data type domain
Relational Model – Example
Student Subject Professor

Evaluation Responsible
Relational Model – Integrity Rules
• Referential Integrity Rule
For each tuple (table row) that is part of a database element, and that refers to
another element of the database, the referenced data exists and has a valid value.
• Entity Integrity Rule
Each instance (row) of the table should be uniquely identified by a set of attributes
(columns).
• Domain Integrity Rule
Each value taken by an attribute of a tuple shall be from a declared type.
Relational Model – SQL
• The relational model facilitates the implementation of a
formal language, based on relational algebra, for accessing
information.
• SQL (Structured Query Language) is the most common.
• SQL has three sub-languages:
• Data Definition Language (DDL)
• Used to define the database structure
• Data Manipulation Language (DML)
• Used to insert, modify, access, or delete data from a database
• Data Control Language (DCL)
• Manages access to data by different users (authorization)
Physical Design
• The physical design of a database links the structure and the
data to a DBMS (database management system).
• A DBMS facilitates an implementation of SQL.
• It specifies the mechanisms for accessing the data and how the
SQL instructions are executed through a query engine
• It allows for the creation of indexes that optimize access to the data
• It maintains the concurrence, coherence and integrity of the data
after transactions
Summary
We saw:
• an introduction to fundamental concepts of databases and
the DBMS
• different database models for representing the real world
• database design steps
• the main characteristics of the relational model
• Within the relational model, we learned about:
• the goals of a DBMS
• SQL and its sublanguages
Questionnaire
In AG you will find a questionnaire to answer that will count
for your conceptual mark (5%).

To complete before Sunday October, 1st.

References
• Main references
• H. García-Molina, J. D. Ullman, J. Widom, 2008. Database Systems –
The Complete Book. Prentice Hall, Second Edition
• M. Takahashi, S. Azuma, 2009. The Manga Guide to Databases. No
Starch Press

•Complimentary references
• R. Ramakrishnan and J. Gehrke, 2003. Database Management Systems,
McGraw-Hill
• J. Sistac, 2000. Bases de Dades, EdiUOC
• C. J. Date, 1995. An introduction to Data Base systems, Addison-Wesley
Real-world Examples of Database Use (I)
Real-world Examples of Database Use (II)
Real-world Examples of Database Use (III)
Database Management Systems (III)

Schema Query Processor Query

(DML)

Schema Processor
Query
(DDL)
Optimizer

Real-Time
Manager

Database Result
Data + Metadata
Mapping Natural Language to ER

English grammar
ER structure
structure
Common noun Entity type
Proper noun Entity
Transitive verb Relationship type
Intransitive verb Attribute type
Adjective Attribute for entity
Adverb Attribute for relationship