Database Types and Design: Relational, Hierarchical, Network

Posted on Dec 11, 2024 in Computers

Database Types

There are three major models of databases: hierarchical, network, and relational.

Hierarchical Database

A database hierarchy, or hierarchical tree, is used for the logical representation of data. Files are organized into hierarchies, and normally each corresponds to one of the entities of the database. Hierarchical trees are represented upside down, with the root up and the leaves down.

Characteristics of hierarchical databases:

  • File segments are arranged in a hierarchical tree.
  • Segments are linked by one or more relationships.
  • Each node consists of one or more fields.
  • Each occurrence of a parent record can have different numbers of occurrences of child records.
  • When you delete a parent record, you should delete all child records (data integrity).
  • Each child record must have a single parent, except the root record.

Network Database

The network model attempts to overcome the shortcomings of the hierarchical approach, allowing many-to-many relationships through the network or graph structure. A network data structure, or plex structure, is very similar to a hierarchical structure; in fact, it is just a superset of it. As in the hierarchical structure, each node may have several children, but unlike the latter, it can also have several parents.

The basic concept in the network approach is the set. A set consists of two types of records that have a many-to-many relationship. To represent this type of relationship, it is necessary for both types of records to be interconnected by a record called a connective record.

Sets have the following characteristics:

  • The parent is named the owner of the joint record, while the child record is called a member.
  • A set consists of a single owner record and one or more member records.
  • A set occurrence is a collection of records, one of them is the owner and the others are members.
  • All owner records of occurrences of the same type of set should be the same type of record.
  • The type of record owner of a set type must be different from the types of member records.
  • Only one member record is allowed to appear once in the occurrences of sets of the same type.
  • A member record can be associated with more than one owner, i.e., it can belong simultaneously to two or more types of different sets.
  • This situation can be represented by a multiple-ring structure.
  • You can define multiple levels of hierarchy where one type of record can be a member of a set while another is the owner of a different set.

Relational Database

Relational databases are the most known and used, both for their simple operation and because they are available to all users and are widespread. In a relational database, the aim is that the user sees the database as a logical structure of relations (tables), simple and uniform.

Relational systems are important because they offer many types of data processing, such as simplicity and generality, ease of end-user use, short periods of learning, and consultation of information specified in a simple way. Relational databases are comprised of one or more tables that contain information in an organized manner. Tables are a means of representing information in a more compact way, and you can access the information in two or more tables.

They meet the following basic laws:

  • Generally, they contain multiple tables.
  • A table contains only a fixed number of fields.
  • The name of the table fields is different.
  • Each table row is unique.
  • The order of the records and fields is not certain.
  • For each field, there is a set of possible values.
  • In the tables, rows represent records (collections of data about separate items) and columns represent fields (particular attributes of a record).
  • In conducting searches, a relational database matches the information in a field in a table with information in the field of another table and thus leads to a third table that combines requested data from both tables.
  • In these databases, the permitted operations are basically updates, i.e., insertion, deletion, and modification, and also queries.
  • The basis of all operations is in relational algebra.
  • They are used for all relational languages like SQL, and treatment of the bases must satisfy integrity rules. These integrity rules are to prevent the model from plausibly representing reality, avoiding inconsistencies.
  • They also collect the primary key uniqueness, its organization, domain integrity, and referential integrity.

The benefits of a relational database are:

  • Compatibility and standardization.
  • Reliability.
  • Guarantee of data independence.
  • Presence of numerous trading systems to choose from and subsequent technical support.
  • Guaranteed connectivity to standard programming languages.
  • A relational database is a stable platform and compatible with its capabilities and limitations in expressive power.

ER Model

The Entity-Relationship Model (ER) is a representation model that is used for the representation of relational databases. It was proposed by Chen in the years 1976-1977. The ER model describes data as entities, relationships (links), and attributes and allows to represent the conceptual schema of a database graphically by ER diagrams. The model considers the relational database as a collection of relations.

Put simply, a relation represents a table, where each row represents a collection of values that describe a real-world entity. Each row is called a tuple or record, and each column is a field.

Some Important Features

  • Each table contains only one record type.
  • Records have no specific order, from top to bottom.
  • The fields do not have a specific order from left to right.
  • Each field has a value and is represented only one way.
  • The logical connections within a relationship between relationships are represented by fields considered by primary keys and secondary keys.
  • It represents a logical relational model of databases at a sufficient level of abstraction to be understood regardless of the system or machine where it will operate.

Advantages of the Relational Model

  • Guarantees tools to avoid duplication of records, through key fields or keys (reduced redundancy).
  • Guarantees referential integrity: when deleting a record, it removes all related dependent records.
  • Favors normalization to be more understandable and applicable.
  • Data can be shared by multiple users and/or applications.
  • The data does not depend on the program that manages it (DBMS). So any application can make use of the data (data independence).
  • Relational systems are important because they offer many types of data processing, such as simplicity and generality, ease of end-user use, short periods of learning, and information queries easily specified.
  • One of the great advantages of the relational model is that it also defines an algebra, called relational algebra.
  • All operations possible on the table are obtained through a combination of only five basic operators: RESTRICT, PROJECT, TIMES, UNION, and MINUS.
  • For ease of use, three additional operators have also been defined: JOIN, INTERSECT, and DIVIDE, although these can be obtained by applying the above.
  • However, the DBMS usually does not allow the user to use relational algebra.
  • Generally, it interacts with the database through a data language, a specially developed language for it, called SQL Language.
  • This is a declarative language based on SQL (queries) which are decomposed by the DBMS in a series of relational operations.

SQL queries offer a wide flexibility to manage information. Through them, the information in a relational database can be retrieved or stored easily by the user. For this reason, the language SQL (Structured Query Language) has become a standard implemented by the main engine management systems or relational databases.

Under the logic of a SQL query, the rows are ordered only if the database is told to do so, through the corresponding SQL command. The columns selected and the order in which they are selected are determined by each query and syntax.

SQL Query Simple Example: It shows the fields Id, Name, and Year for each of the records that match the Brand FIORINO.

Stages of Designing a Relational Database

Conceptual Design

The framework is a source of information for the logical design of the database. At this stage, a conceptual framework must be built to describe the situation. This scheme is called the conceptual schema. In constructing the scheme, the semantics (meaning) of data that characterize a situation are discovered. They are entities, attributes, and relationships.

Aiming to understand:

  • The prospect that each user has data.
  • The nature of the data, regardless of their physical representation.
  • The use of data across application areas.

Logical Design

The logical design process involves building the outline of information that characterizes the given situation, regardless of the specific DBMS used. The logical schema is a source of information for physical design. Here is the stage where the conceptual schema is transformed into tables, data structures specific to the relational model.

At this stage, a key aspect is the standardization process, a technique that is used to pre-established logic schemes, ensuring that tables and relationships between the tables are the best to ensure both efficiency and quality in the data management. If the conceptual design and logical design are not an accurate representation of the situation, the benefits of a relational database will be limited, insecure, and unable to meet the needs of users in the present, let alone in the future.

Physical Design

In a broad sense, the purpose of physical design is to describe how the logical schema obtained in the previous phase will be physically implemented. This includes:

  • Getting a set of relations (tables) and the restrictions to be enforced on them.
  • Determining the storage structures and access methods that will be used to achieve optimal performance.
  • Designing the system security model.

Conceptual Design: The E/R Model

In the Model Entity/Relationship, the section of reality is represented by a very small number of basic semantic concepts (graphic and linguistic representations).

Entity means any object or concept distinguishable and relevant in the context of the situation to intervene (thing, person, abstract concept or event: cars, houses, employees, customers, businesses, organizations, processes, functions, courses, concerts, etc.). In practical terms, an entity corresponds to a table and is graphically represented by a rectangle.

Attribute: Each entity has a number of properties or attributes: bits of information that describe or characterize entities. In addition, each entity has an identity: a property that makes them uniquely recognizable, which is lodged in its primary key, and clearly identifies each tuple of the entity. In practical terms, the attributes correspond to fields in the table, where the order of attributes does not matter, and no two attributes should be named the same.

Identifier: An identifier of an entity is an attribute or set of attributes that uniquely identifies each tuple; there cannot be two occurrences with the same identifier value.

Relation (relationship): Each entity is associated with other entities; groups of related entities also maintain relationships with other groups of entities. In the E/R model, a ratio is plotted with a diamond, and each relationship has a unique name that describes its function.

Design Process in the ER Model

  • Identify the entities that must file the database.
  • Determine the relationships and cardinalities established between the various entities.
  • Sort these interrelationships between the following types:
    • One by one (e.g., a parcel has only one direction).
    • One to many (e.g., in a plot, several fires occur).
    • Many to many (e.g., sale of plots: a single piece of land can sell several owners, and each owner can sell several plots).
  • Determine the properties (attributes) of each entity.
  • Define the primary key (unique) for each entity, its identity.
  • Determine secondary keys, as appropriate.
  • Determine generalization hierarchies (if any).
  • Draw the diagram Entity/Relationship.

Normalization of a Database

In simple terms, the process of standardization is based on describing the information using tables. These tables are structured to attempt to comply with certain formats called Normal Forms. The higher the normal form, the stricter the criteria met by the board, and the easier it is to treat it.

  • First Normal Form: No multiple fields. All rows should have the same number of columns.
  • Second Normal Form: All non-key fields must depend entirely on the whole key.
  • Third Normal Form: No transitive dependencies. A field must depend on the key and not on another field.
  • Fourth Normal Form: A line must not contain two or more multi-valued fields (those that can contain more than one value simultaneously) on an entity.
  • Fifth Normal Form: A table can store key attributes dependent only by marriage.

Informatics Engineering

End Database