[Notes] Database Management

04/20/2021 Miguel Menéndez

Some notes on database management.

1. Introduction to database management systems

Information qualities

1. Accurate
2. timely
3. Full
4. Significant
5. Complete
6. Safe

002 - Topic 1 - Database management systems.pdf - Page 3

What do we mean by system?

Set of things that, in an orderly relationship with each other, contribute to a certain objective.

002 - Topic 1 - Database management systems.pdf - Page 4

What do we understand by information system?

Set of elements in an orderly relationship with each other in accordance with certain rules that provides the object system with the necessary information for the fulfillment of its purposes, for which it will have to collect, process and store data, both from the same organization and from external sources, facilitating the recovery, elaboration and presentation of the same.

002 - Topic 1 - Database management systems.pdf - Page 5

Components of an electronic information system

1. Data 2.Hardware 3.software
2. Human resources

002 - Topic 1 - Database management systems.pdf - Page 6

Types of information systems

1. Process-oriented or file-oriented information systems
2. Data-oriented information systems. Databases

002 - Topic 1 - Database management systems.pdf - Page 8

Objectives and characteristics of database management systems

002 - Topic 1 - Database management systems.pdf - Page 11

Levels of abstraction of a database

ANSI/SPARC architecture:

1. External scheme (outer layer)
2. Conceptual scheme (theoretical layer)
3. Physical scheme (physical layer)

002 - Topic 1 - Database management systems.pdf - Page 12

Components of database management systems

Functions. Languages ​​of database management systems

1. Description or definition function
2. Tamper function
3. Control function

002 - Topic 1 - Database management systems.pdf - Page 14

Human resources databases

1. Database administrator
2. Database Designers
3. Application developers
4. End users

002 - Topic 1 - Database management systems.pdf - Page 15

Operation of the database management system

*002 - Topic 1 - Database management systems.pdf - Page 17

Architecture of database management systems. Standards and standardization bodies

*002 - Topic 1 - Database management systems.pdf - Page 19

Operational Structures

1. Client-server structure
2. Multi-server structure
3. Client-server structure with database user-server facilities

*002 - Topic 1 - Database management systems.pdf - Page 22

Types of database management systems

According to the logical model to which they belong

1. Hierarchical model
2. Network model
3. Relational model
4. Object-oriented model
5. Object-relational model

*002 - Topic 1 - Database management systems.pdf - Page 23

METRIC Version 3

004 - Introduction to METRIC Version 3.pdf

2. Elaboration of the conceptual design. entity-relationship model

Key elements in the model

Entity

1. Strong or regular
2. Weak

002 - Topic 2 - Elaboration of the conceptual design….pdf - Page 3

Relationship

Participation of entities in the relationship:

1. Maximum participation
2. Minimum participation

002 - Topic 2 - Elaboration of the conceptual design….pdf - Page 5

Relationship Types:

1. 1:1
2. 1:N 3.N:M

002 - Topic 2 - Elaboration of the conceptual design….pdf - Page 6

Roles

002 - Topic 2 - Elaboration of the conceptual design….pdf - Page 7

Attributes

1. Multiple
2. Optional
3. Compounds

002 - Topic 2 - Elaboration of the conceptual design….pdf - Page 7

Identifiers or keys

• Foreign or foreign identifier (foreign or foreign key)

002 - Topic 2 - Elaboration of the conceptual design….pdf - Page 9

Dependency relationships between strong and weak entities

1. Existence dependency
2. Dependency by identification
3. N-ary relations

002 - Topic 2 - Elaboration of the conceptual design….pdf - Page 10

Other restrictions on relationships

1. Exclusivity restriction
2. Exclusion Restriction
3. Inclusivity constraint
4. Inclusion Restriction
5. Redundancy

002 - Topic 2 - Elaboration of the conceptual design….pdf - Page 12

Extended entity-relationship model

Hierarchical relationships

1. Partial without overlap
2. Partial with overlap
3. Total without overlap
4. Total with overlap

002 - Topic 2 - Elaboration of the conceptual design….pdf - Page 15

3. Elaboration of the logical design. relational model

Relational databases

The relational model

Goals

1. Physical independence
2. logical independence
3. Flexibility
4. Uniformity
5. Simplicity

002 - Topic 3 - Elaboration of the logical design. Relational model.pdf - Page 3

Codd’s 12 Rules

1. Information
2. Guaranteed access
3. Systematic treatment of null values
4. Online catalog
5. Data sublanguage
6. Update views
7. Insertions, modifications and deletions of level data
8. Physical independence
9. Logical Independence
10. Independence of integrity
11. Distribution Independence
12. Not subversion

002 - Topic 3 - Elaboration of the logical design. Relational model.pdf - Page 4

Structure of relational databases

Relationship or table

1. Attributes
2. Tuples

002 - Topic 3 - Elaboration of the logical design. Relational model.pdf - Page 8

tuple

002 - Topic 3 - Elaboration of the logical design. Relational model.pdf - Page 9

Domain

002 - Topic 3 - Elaboration of the logical design. Relational model.pdf - Page 9

Grade

002 - Topic 3 - Elaboration of the logical design. Relational model.pdf - Page 10

Cardinality

002 - Topic 3 - Elaboration of the logical design. Relational model.pdf - Page 10

Formal definition of relationship

1. Name
2. Relationship header
3. Scheme of the relationship
4. Body of the relationship
5. Relationship Status

002 - Topic 3 - Elaboration of the logical design. Relational model.pdf - Page 11

Table properties (or relationships)

002 - Topic 3 - Elaboration of the logical design. Relational model.pdf - Page 12

Table Types

1. Foundation
2. Views
3. Snapshots
4. Temporary

002 - Topic 3 - Elaboration of the logical design. Relational model.pdf - Page 12

Keys

1. Candidate key
2. Primary key
3. Alternative key
4. External, alien or foreign key

002 - Topic 3 - Elaboration of the logical design. Relational model.pdf - Page 13

Restrictions

null value (null)

Restrictions inherent to the relational model

Semantic or user restrictions

1. Primary key constraint
2. Unique constraint (unique)
3. Mandatory restriction (not null)
4. Referential integrity or foreign key constraint
5. Verification or validation restriction (check)
6. Triggers, triggers or triggers

*002 - Topic 3 - Elaboration of the logical design. Relational model.pdf - Page 17

4. Transformation from the entity-relationship model to the relational model

*002 - Topic 4 - Transformation of the entity-relationship model to the relational model.pdf

5. Normalization of relationships

Functional dependencies

002 - Topic 5 - Normalization of relationships.pdf - Page 3

Full functional dependency

002 - Topic 5 - Normalization of relationships.pdf - Page 4

Transitive functional dependency

002 - Topic 5 - Normalization of relationships.pdf - Page 5

Multi-valued functional dependency

002 - Topic 5 - Normalization of relationships.pdf - Page 6

Join Dependency (JOIN)

002 - Topic 5 - Normalization of relationships.pdf - Page 7

Normal Forms

002 - Topic 5 - Normalization of relationships.pdf - Page 9

First Normal Form (1NF)

002 - Topic 5 - Normalization of relationships.pdf - Page 10

Second Normal Form (2FN)

002 - Topic 5 - Normalization of relationships.pdf - Page 11

Third Normal Form (3NF)

002 - Topic 5 - Normalization of relationships.pdf - Page 13

Boyce-Codd Normal Form (BCNF)

002 - Topic 5 - Normalization of relationships.pdf - Page 14

Fourth Normal Form (4NF)

002 - Topic 5 - Normalization of relationships.pdf - Page 15

Fifth Normal Form (5FN)

002 - Topic 5 - Normalization of relationships.pdf - Page 16

domain-key normal form (DKNF)

002 - Topic 5 - Normalization of relationships.pdf - Page 17

Normal form of denormalization

002 - Topic 5 - Normalization of relationships.pdf - Page 18

M04 GBD Databases