Computer Science 2018

Introduction to databases and database management systems

COMPONENTS OF AN INFORMATION SYSTEM

Application programs
S.G.B.D.
Database

The information constitutes the content of the database. In order for it to be stored and managed, it ...
information requires a structure. This structure is created and maintained by the system software
of database management (DBMS).

The database is an integral part of the so-called SiInformation systems,where the data are
collected, processed, organized and stored. The part of collection, treatment and
data processing is donefor programsspecifics such as: ERP, CRM, web service, websites,
etc. However, the storage and organization of this data are done in a database,
podendo ser ele Oracle, SQL Servidor, MySQL ou outro.

Currently, databases are called relational because their structures are formed
through tables and relationships between the tables. Both a robust database like Oracle or a
desktop like Access adopts this same standard, making it easy and possible for integration
data between banks

1
Computer Science 2018

A Database System consists of a collection of interrelated data and a

collection of programs to provide access to this data. The main objective of a system of
A database is to provide an environment that is suitable and efficient for use in retrieval and
information storage.

General purpose: To store information and allow users to search and update it.
efficient and quick information when needed
Users can:
Insert
Search
Update e
Remove data.

Advantages
Data organization: the structure of the data is too complicated to be stored in tables.
Data volume: the volume of data is too large to be stored in a table
Speed/Efficiency: The data is obtained and updated quickly in the database.
• Concurrency: different applications/users can share the same data
Controlled redundancy: shared data does not need to be replicated
Applicability: Reduction in the application development time

2
Computer Science 2018

Database management systems are programs that allow the creation and manipulation of databases.
of data, in which data are structured with independence relative to the programs
of application that manipulate them.

Characteristics of the DBMS

Concurrency Control - Limits simultaneous changes and readings of the same data by
different users;
Backup – a reconstruction mechanism that allows the database to return to
consistent state;
Security - can establish restrictions on access to each item of information;
Integrity - maintaining the consistency of the database through the validation of constraints;
Atomicity – either the transaction occurs or it does not.

There are 3 levels of architecture for any DBMS:

· Physical level:

Files are stored on computer storage media and, starting

then they are manipulated by the DBMS running on the computer;

3
Informatics2018

· Conceptual Level

Organization of information in tables and relationships;

· Viewing level

It corresponds to the way data is presented to end users, through interfaces.

graphs provided by the DBMS.

Disadvantages of a database management system

The disadvantages of DBMS and Databases are as follows:

The software of this system is complex and the concepts used are, often,
new for users. Thus, it requires specialized personnel in IT and,
sometimes, it is necessary to reduce the number of users of the DBMS;

The software causes additional overhead (resource degradation) because it is slower in execution.
It requires more disk space.

Typical operations for working with databases

· Operations for defining and altering the structure of a database

· Data manipulation operations, without altering the structure of the database

· Data control operations

Whatisadatabase?

A database is a simple repository of information related to a specific

subject or purpose, that is, it is a collection of structured data or information items
a specific way that allows for its querying, updating, and other types of operations
processed by electronic means.

4
Computer Science 2018

A database is a collection of related information.

interest, around a certain theme or domain

Database Models

The models used for the creation of databases can be classified into
two main groupings:

Object-based models
Record-based models

Object-based models have the common fact of trying to represent reality.

through objects - these are understood here and can be transposed to the field of
programming, containing relevant information about the real entities they represent

The register-based models have the common feature of trying to represent reality.
through registers. These registers correspond to the registers used in programming containing
structured information with field format.

Within this grouping of database models, there are traditionally 3.

models:

۵ Hierarchical model
۵ Network model
۵ Relational model

Advantages of a database

Little physical space occupied

Elimination of redundancy in usage
Ease and speed of handling stored information
Less deterioration of the support used (microfiche, magnetic, optical)
More truth in the combination of data, more information

5
Informatics2018

There are many tools available in the market to computerize a database:

Microsoft Access
FileMaker
Microsoft SQL
Oracle
Informix
DB2
Etc.

These are some of the products that allow you to create, use, and maintain databases.

To build a database, we need to reflect on some aspects:

. What theme to choose for our B.D ??

Our address book

Our songs, the challenges
The goals of our club
The times and standings of the F1 championship
Our recipes, etc.
What questions can we ask our database?

-How many..., which..., that..., since when, etc...

. Where to collect the data to insert into the database?

On the Internet, in files, in magazines, etc...

We start by defining the tables, that is... the drawers of the data...

We organize the data better if we distribute it according to a criterion in different drawers...

6
Computer Science 2018

To a certain extent, we can translate almost all types of entities, situations into tables.
events, etc... For this, it is enough for us to define some of the characteristics of these
entities such as CAMPOS.

TheFIELDSbecomethecolumnsofthetableandtherowsbecomeRECORDS.

Notion of table

An integral part of a database, it is used to store information related to a subject.

specific, such as, for example, students, books, authors, countries, postal codes, etc...

The information stored in a table must be consistent with the criteria used for the
your creation - containing only the data that pertains to the subject, to which it is given

name of entity.

Notion of field

Constituent element of a table.

It is used to specify the various types of data or categories, we divide it into

information that we intend to use.

Notion of registration

Set of related fields

Arow in a table corresponds to a record

Notion of relationship

Association established between common fields (columns) of two tables, allowing for
the awareness of information be guaranteed

7
Computer Science 2018

the relationship between the common fields of the tables allows to ensure that the awareness
that the information is not called into question and also to associate the data of two or
more tables for the visualization, editing or printing of the information.

Notion of association

It represents the way two or more entities relate to each other.

There are three types of associations:

Unary - relationship between an entity and itself

Binary-existenceofanytypeofrelationshipbetweentwoentities

Complex - possibility of establishing relationships between more than two

entities.

Concept of key

The key of a table is made up of one or more fields that can be used as
identifiers of each record.

Thekeyfieldsmustallowtherecordtobeidentifieduniquely.

The field or set of fields selected for the key of a table cannot contain
repeated information.

TYPE OF FIELD...

...must be chosen according to the characteristics of the data we want it to store.

The right choice of field type facilitates its superior use:

1. If we want to compare dates, it is advisable that the field is defined as date/time.

2. If we want to perform calculations, we have to define it as numeric. There are 2 records with
the same content for the primary key.

[Link]:Therecannotbeanyprimarykeyattributethatcontainsanullfield.

8
Computing2018

Relational model

O what é bank of data relational?

The data model where the structures take the form of tables, composed of rows and
columns. These tables can be related to each other. It is the most widely used model today.

Relational Model
Data is perceived as tables (relations);
Attributes (table fields);
Keys:
Primary: an attribute that identifies the entity.
It must be unique for each record;
Foreign keys: attribute to reference entities in other tables.

Advantages
Little physical space occupied
Elimination of usage redundancy
Ease and speed of handling stored information
Less deterioration of the medium used (microfiche, magnetic, optical)
More truth in the combination of data, more information

Characteristics of a relational database

• Data organization
• Concepts of the model
• Attribute, relation, key,
• Integrity
• Basic restrictions for data and relationships
• Manipulation
• Formal languages and SQL

9
Informatics2018

PRIMARY AND FOREIGN KEYS (SECONDARY)

To introduce the foundations of the relational model, the concept of a key is very important: an attribute
The set of attributes is called a key when it allows us to uniquely identify or
unique each concrete entity or record of the table.

CONCEPT OF KEYS
A key is an attribute or a set of attributes that allows for the unique identification of
records (entities or occurrences) of a table. All possible keys of a table or
entity - simple or composite - are designated as candidate keys. Among the candidate keys
existing in a table, one of them will be the most indicated or chosen to perform the role of
key - this will be designated as the primary key. A primary key is therefore a
attribute or set of attributes that serves to uniquely identify entities
or records of a table.

PRIMARY KEY MUST RESPECT THE FOLLOWING CHARACTERISTICS

THE RULES:
Being unique or the attributes that play the role of primary key, by definition, have
to have a unique value for each concrete entity;
Not null - none of the attributes that form a primary key may contain a null value.
innorecord;
Não redundante–no caso de uma chave primária ser composta, não deve ser incluídos mais
attributes of the minimum necessary to uniquely identify the records; an attribute
a composite key cannot have attributes removed from it, because if it does, the attribute or attributes
remaining no longer become univocal.

RELATIONSHIPS AND SECONDARY KEYS

The essential characteristic of the relational model is that it allows establishing relationships between
entities or entity tables. These relationships are established precisely through
two attributes or fields that play the role of primary keys in the respective tables. A
The relationship table should include, among its fields, the keys of the tables of the entities.
that enter the relationship. When the key of one table is included as a field in another

10
Informatics2018

table, then, from the point of view of this last table, said that it is about an external key
(secondary). Therefore, a foreign key (secondary) is an attribute that is a primary key of
a table and it will appear as an attribute of another table. It is fundamentally through the
inclusion of attributes that are primary keys (in their source tables) in other tables (where
are secondary keys) that establish the relationships between the different entities of
a relational database.

With the adventfrom the Entity-Relationship Model a confusion was caused among the
termsrelationship and relationship

The Relational Model,when described mathematically, it is defined as a model formed

for relations (in the mathematical sense) between the domains. Each tuple is an element of the set
relationship.

In other words, the relationship is the table.

Arelationship ofEntity-Relationship Model it is an association between entities

different. There is no direct relationship between the term relationship and the name relation.

However, a relationship, from theEntity-Relationship Model istranslated for creation

of attributes with foreign keysof the Relational [Link] translation is made by connecting a field
from a table X with a field from a table Y, through the inclusion of the key field from the table
And as a field (known as a foreign key) of table X.

By means of foreign keys, it is possible to implement constraints in the RDBMS.

There are some types of possible relationships in the MER:

One to one(1 for 1) - indicates that the tables have a unique relationship with each other. You choose
which table will receive the foreign key;
One for many1 for N)the primary key of the table that has side 1 goes to the table
on side N. On side N it is called a foreign key;
Many to manyN for N)when tables have a n.n relationship with each other, it is necessary to create
a new table with the primary keys of the involved tables, resulting in a key

11
Informatics2018

composed, that is, formed by several key fields from other tables. The relation then
it reduces to a 1.n relationship, where the n side will have the new table created.

One-to-one and one-to-many relationships can be mapped directly in foreign keys.

in the original tables. The N to N relationship requires the use of an auxiliary table.

Normal forms and Normalization

Normalization is a process through which rules are applied to all tables in the database.
data with the aim of avoiding project failures, such as data redundancy and mixing of
different subjects in the same table.

When designing a database, if we have a model of entities and relationships and from there
let's build the relational model following thetransformation rulescorrectly, the
the resulting relational model will probably be normalized. But not always the models
what we encounter is implemented this way and, when that happens, the support for the bank
data is difficult.

In both cases, it is necessary to apply normalization techniques, or to normalize

(second cited case), or just to validate the created scheme (first cited case). Applying
the rules described below can ensure a more integrity database, without redundancies
the inconsistencies.

There are 3 more well-known normal forms:

1NF - 1st Normal Form: all attributes of a table must be atomic, that is, the
the table should not contain repeated groups or attributes with more than one value. To leave
In this normal form, it is necessary to identify the primary key of the table, to identify the(s)

column(s) that have repeated data and remove it/them, create a new table with the key
primary to store the repeated data and, finally, create a relationship between the table
main and the secondary table. For example, consider the following People table.

PESSOAS = {ID+ NOME + ENDERECO + TELEFONES}

12
Computer Science 2018

It contains the primary key ID and the TELEFONES attribute is a multivalued attribute and,
Therefore, the table is not in 1NF. To put it in 1NF, we will create a new table.
table TELEPHONES that will contain PERSON_ID as a foreign key from PEOPLE and
TELEPHONE as the multivalued attribute that will be stored.

PESSOAS = { ID + NOME + ENDERECO }

TELEFONES = {PESSOA_ID + TELEFONE}

2NF - 2nd Normal Form: first of all, to be in 2NF it is necessary to be in 1NF.

Moreover, all non-key attributes of the table must solely depend on the key.
primary (not being able to depend only on part of it). To leave in the second form
Normally, it is necessary to identify the columns that are not functionally dependent on the key.
primary from the table and then remove that column from the main table and create a new one
table with these data. For example, consider the following ALUNOS_CURSOS table.

ALUNOS_CURSOS = { ID_ALUNO + ID_CURSO + NOTA + DESCRICAO_CURSO

}

In this table, the attribute DESCRICAO_CURSO depends solely on the primary key.
COURSE_ID. Thus, the table is not in 2NF. To achieve this, a new table is created.
chamada CURSOS que tem como chave primária ID_CURSO e atributo DESCRICAO
thus removing the attribute DESCRICAO_CURSO from the ALUNOS_CURSOS table.

ALUNOS_CURSOS = {ID_ALUNO + ID_CURSO + NOTA}

CURSOS = {ID_CURSO + DESCRICAO}

3NF - Third Normal Form: to be in 3NF, it is necessary to be in 2NF. Furthermore, the

Non-key attributes of a table should be mutually independent and dependent.
only and exclusively from the primary key (an attribute B is functionally
A is dependent on B if and only if, for each value of A, there is only one value of B.
to reach this normal form, it is necessary to identify the columns that are functionally

13
IT2018

dependencies from the other non-key columns and extract them to another table. Consider, as
example, the table EMPLOYEES below.

FUNCIONARIOS = { ID + NOME + ID_CARGO + DESCRICAO_CARGO }

The attribute JOB_DESCRIPTION depends exclusively on JOB_ID (attribute not

key) and therefore, a new table must be created with these attributes. This way,
we have the following tables:

FUNCIONARIOS = { ID + NOME + ID_CARGO }

CARGOS = { ID_CARGO + DESCRICAO }

Example 2

1stNormalForm

A relation is in 1st Normal Form if and only if each row contains exactly one value for
each attribute Example (data related to a book order - non-normalized structure):

Pedido_Livro ( Nome-cliente, ISBN, Data-pedido, Título, Autor(es), Quantidade, Preço, Valor-

total)

In 1st Normal Form:

Pedido_Livro ( Nome-cliente, ISBN, Data-pedido, Título, Quantidade, Preço, Valor-total)

Authorship (ISBN, Author)

SecondNormalForm

A relation is in 2nd Normal Form if it is in the first and if all the descriptor attributes
depend on the totality of the candidate keys.

Example (in the 1st FN):

14
Computer Science 2018

Pedido_Livro ( Nome-cliente, ISBN, Data-pedido, Título, Quantidade, Preço, Valor-total) Autoria

(ISBN, Author)

Applying the 2nd Normal Form:

Pedido_Livro ( Nome-cliente, ISBN, Data-pedido, Quantidade, Valor-total)

Authorship (ISBN, Author)

Livro (ISBN, Título, Preço)

3rdNormalForm

A relation is in the 3rd Normal Form if it is in the 2nd Normal Form and if there are no attributes
descriptors depending functionally on other descriptor attributes (that do not belong to
no candidate key).

Example (in the 2nd FN):

Pedido_Livro (Nome-cliente, ISBN, Data-pedido, Quantidade, Valor-total)

Authorship (ISBN, Author)

Livro (ISBN, Título, Preço)

This relational schema is in the 3rd Normal Form because there are no relationships.
transitive functional dependencies are in BCNF because each relation has a unique key
candidate

Example 3

First Normal Form - 1NF

According to 1NF, all occurrences of a record must contain the same number of fields.
1NF does not allow repetitions or fields that contain more than one value. Note the example:

15
Computer Science 2018

In this case, to achieve complete normalization, following the 1NF, we would have the following tables:

In this example, the phones were separated because there was a possibility of one of the records
having more than one phone, which, if registered in the same table, would cause the
"denormalization" of the data. As for the ZIP code, it is in a separate field from the Address.
as well as the Neighborhood and the records are, thus, in a nested form.

2nd Normal Form - 2NF

We can say that a table is in 2NF if it is in 1NF and all non-key attributes
keys were completely dependent on the primary key (dependent on the entire key and not just
on her part–in case of composite keys).

Understand: if the client's name already exists in the Clients table, then it is not necessary in the
tableAppointments. This is what the 2NF deals with, these anomalies, redundancies in the database.

Here is an example, with a product sales scenario:

16
Computer Science 2018

In fact, this is wrong, since the products are registered in another table (the table of
products) where, at the time of registration, we have already informed your name. The correct way would be like this:

Note that it is only necessary to reference the primary key (the code) of the product, and nothing more!

Third Normal Form - 3NF

We know that a table is in 3NF if it is in 2NF and if no non-

key depends on another non-key column.
Basically, the goal is to eliminate the fields that can be obtained by the equation (calculation
mathematician or logical) from other fields of the same table.
The primary key of the new entity will be the attribute from which the removed attributes are.
functionally dependent.

Let's consider the following table:

17
Computer Science 2018

Note that the value of the "Subtotal" column can (and should) be easily obtained through the
calculation of the value of the column Q
' uant' multiplied by the value of the column V
' alor_unit.' This would eliminate

from our table (from the table above) the column 'Subtotal', leaving it like this:

Exercises on normalization
1) Transform into 1NF, 2NF, and 3NF

18