Introduction to databases

What is data?

• conventional representation of information so that it can be treated in a computer process or an automatic system in general.
• it is a symbolic representation (numerical, alphabetical, etc.) of an entity. Data has no semantic value (meaning) in itself, but conveniently treated (processed) it can be used in performing calculations or decision making. It is very common in the computer field.

In programming, data is the general expression that describes the characteristics of the entities on which an algorithm operates.

• are computer representations of available information, relating to real-world objects that interest us.

What is data and what use can we get from it?

Data becomes useful information when we give it meaning—that is, when we process it so that it provides knowledge. A datum (singular of data) is a symbolic representation of an object or concept and by itself has no meaning.

To give meaning to data, it must be treated and organized when stored. If we achieve a tool to store a collection of data, organize it, perform quick queries, retrieve it, and process it, then we will be able to make use of it.

A bit of history

The management of computer data has evolved significantly. Currently, we find systems known as DBMS (Database Management Systems). Before their existence, however, what was used was the system based on files.

Files

One way to maintain information on a computer is to store it permanently in files. This system, however, has a series of drawbacks.

Drawbacks:

• Data redundancy: this problem appears when a piece of data must be modified from several places and carries implicit problems such as:
  • Inconsistency in the data format.
  • Duplicity of information: present in several files, it takes up extra space and duplicates efforts.
  • Data inconsistency: when copies of the same data conflict.
• Data isolation: appears when there are concurrent accesses to a piece of data. It is a property that determines when and how changes made by one operation are visible to other concurrent users and systems.
• Integrity problems: integrity is the guarantee that the data is correct and consistent.
• Security problems: access to data will be related to access privileges for different files. New requirements can make the application of restrictions difficult.
• Concurrent access: the ability to allow multiple users to access data at the same time. In file-based systems, access to them is blocked and therefore there is no concurrency.

Databases (DBs)

The difficulties that arise when working with file systems led to the emergence of a new system to manage large amounts of information.

What is a DB?

• A a shared collection of related data used to support the activities of a particular organization.
• It is a set of data according to a coherent structure that is accessible from one or more programs or applications, so that any of this data can be extracted from the set and updated without affecting the structure of the set or the other data.

Properties of a DB

• It is a representation of some aspects of the real world or a collection of data representing real-world information.
• It is logical, coherent, and internally consistent.
• It has been designed, built, and populated with data for a specific purpose.

Properties added to relational DBs:

• A piece of data is a field.
• A combination of fields forms a table.

Database Management Systems (DBMS)

A DBMS (in English DBMS, database management system) is a collection of programs that allow users to create and maintain DBs by controlling all access to them.

Properties of a DBMS

• Information abstraction: how the data is stored should be transparent to the user.
• Independence: it must be guaranteed that a change in schema, physical or logical, does not imply a change in the applications that use it.
• Consistency: it must be guaranteed that the data is consistent, meaning it is updated when queried.
• Security: it must be guaranteed that information is queried, updated, inserted, or deleted by authorized users.
• Integrity: in case of an incident, it must be possible to recover information that may have been lost.
• Recovery: backup copies must be able to be made efficiently to avoid data loss.
• Concurrency control: simultaneous access to the DBMS must be guaranteed.
• Information about data or metadata: the system must have information about the system itself.

Characteristics of DBs

DBs must allow data (information) to be managed so that it is useful for the tasks to be performed. With DBMS, information becomes more accessible and integrated with the rest of the system we use.

Advantages:

• Self-descriptive nature of a database system: it not only contains the DB itself but also includes metadata that defines and describes the data and the relationships between the tables of the DB.
• Isolation between program and data: in a file-based system, the structure of the files is defined in the applications. In a DB-based system, the structure is stored in the system catalog and not in the programs.
• Support for multiple data views: a DB supports multiple views of the data (subsets of the DB intended for specific system users).
• Data sharing and multi-user systems: DB systems are designed for concurrent access (ensuring that data accessed simultaneously is always correct and maintains its integrity).
• Control of data redundancy: efforts are made to avoid data duplication, although it is sometimes allowed for efficiency reasons.
• Enforcement of integrity constraints: they have the ability to define and enforce certain constraints to ensure that users enter valid information and maintain data integrity. Constraints are rules that state what can be entered into a field.
• Restriction of unauthorized access: DBMS must provide a security subsystem to create and control various types of users and restrict unauthorized access. File-based systems can restrict access to a file, but not to a specific part of it.
• Data independence: descriptions of the system data or the data describing the data are separate from the applications.
• Transaction processing: DBMS must also include concurrency subsystems, which allow data to remain consistent and valid during transaction processing if several users update the same information.
• Backup and recovery capability: backups and recoveries are the methods that protect the system against accidental data loss.

The ACID properties of transactions

Transactions in a DBMS must comply with ACID properties to ensure data integrity and consistency.

• Atomicity: guarantees that all operations within a transaction are completed correctly, or none of them are performed.
• Consistency: ensures that the transaction takes the database from one valid state to another.
• Isolation: guarantees that operations within a transaction do not interfere with other simultaneous transactions.
• Durability: ensures that once a transaction has been confirmed, the changes remain permanent, even in the event of system failure.

Types of data models

High-level conceptual model

These models provide concepts to present data in ways that are close to how people perceive data. An example of these models is the ER model (Entity-Relationship).

Record-based logical model

These models provide concepts that users can understand but are far removed from how the data is stored. Three well-known models of this type are the relational model, the network model, and the hierarchical model.

• The relational model: represents data as relationships or tables.
• The network model: represents data as record types. This model also represents a limited type of 1 to N relationship, called a set type.
• The hierarchical model: represents data with a hierarchical tree structure. Each branch of the hierarchy represents a number of related records.

The first to appear was the hierarchical model in the early 60s. Its structures are interrelated records in the form of trees. The classic DBMS for this model is IBM's IMS/DL1.

In the early 70s, DBMS based on the network model appeared, which were less limited than the previous ones. A standard defined by CODASYL_DBTG was proposed.

During the 80s, a large number of DBMS appeared based on the relational model proposed in 1969 by E.F. Codd of IBM, almost all using the SQL language.

Data modeling

The first step of the DB design process is data modeling.

Objective:

• Determine the data contained in the DB.
• Determine the relationships between data types.
• Determine the constraints of the data.

The second step of the process is the representation of entities, relationships, and constraints using a high-level data model. These concepts do not include implementation details; therefore, the result of the modeling process is a fairly formal representation of the structure of the DB.

The third step is the design of the database. In this step, the DB is defined in a data model of a specific DBMS and the internal storage structure of the DB, file organization, or indexing techniques are defined.

Data abstraction levels

Designs always move from a high level and descend to levels with more detail.

The DB design begins with users identifying business rules, then designers and analysts create the DB design, and finally, the DB administrator implements the design using a DBMS.

External models

The external model is the view of the data from the end user's perspective. Each user (or group of users) will have a different view of the data.

Conceptual models

The conceptual model provides a global view of the entire database as seen by the organization. It integrates all external views into a single, global description of the data.

Internal models

The internal model is the representation of the database as seen by the DBMS. It adapts the conceptual model to a specific database software (logical design).

Physical models

The physical model is the lowest level of abstraction, describing how data is actually stored on storage media (disks, magnetic tapes, etc.).

Organization of data in memory

Methods used to store and organize data in memory include:

• Sequential: elements are stored one after another.
• Indexed: uses an index to locate elements quickly.
• Hash: calculated from a key, where an element of the table can be accessed through a hash function. Example: Assignment of lockers in a dressing room.

Data abstraction layer

The external model is the end user's view of the data. One user's view will be different from another's.

The external model requires the designer to subdivide a set of requirements and constraints into functional modules.

The conceptual model is independent of software and hardware.

Once a DBMS is selected, it can be implemented. This is the internal model. At this time, all tables, constraints, keys, rules, etc., are created (logical design).

The physical model is simply the way data is saved on the disk. Each DBMS has its own way of storing data.

Schemas

A schema is a general description of the DB and is usually represented with an entity-relationship diagram (ERD).

Schemas representing external models:

• External schemas: there are multiple.
• Multiple sub-schemas: these show multiple external views of the data.
• Conceptual schema: there is only one. This schema includes the data, relationships, and constraints, represented in an ERD.
• Physical schema: there is only one.

Physical and logical independence

It refers to the immunity an application must have to changes made in the definition and organization of the data.

Data abstraction exposes only those elements that are important to the user. Complexity will be hidden from the DB user.

Logical data independence

A logical schema is a conceptual design of the DB done by hand. It is the ability to modify the logical schema without having to change the external schema or the user's view.

Changes to the logical schema, such as alterations to the DB structure (adding a column, a new table, etc.), should not affect the operation of an application that uses it.

Physical data independence

It refers to the immunity of the internal model with respect to changes in the physical model.

The logical schema will remain the same despite changes in the organization of files, storage structures, etc. Therefore, the details of the storage structure are hidden or made transparent to user applications.

Classification of DBMS

DBMS are classified according to the data model, the number of users, and the distribution of the database.

Classification based on the data model

The main classification is based on the data model they use:

• Relational (most common)
• Object-oriented
• NoSQL (Document, Key-Value, Graph, Column-family)

Classification based on DB distribution

Centralized systems

The database is stored in a single location (a single computer or server).

Distributed database systems

The database and the DBMS software are distributed over several sites connected by a network.

Types of DB users

End users

Occasional users who access the database from time to time, but may need different information each time.

Application users

Users who interact with the database through pre-established programs or applications (e.g., bank tellers, airline reservation agents).

Sophisticated users

Users who are familiar with the capabilities of the DBMS and use it to meet their complex requirements (engineers, scientists, business analysts).

Application developers

Computer professionals who write software programs to interact with the database.

Database Administrators (DBA)

The person or group responsible for authorizing access to the database, coordinating and monitoring its use, and acquiring software and hardware resources as needed.

Author: jbosom@xtec.cat