Database Technology

Database Technology

Database technology is a technology for organizing, managing, manipulating, and storing data. Databases are intended to store relevant data in a consistent manner and provide quick access to it as needed.

The main elements of database technology are described below.

<Database Model>

The database model is a conceptual framework that defines how databases are designed and organized, how data are represented and related, and how databases are manipulated and queried. The main database models are described below.

• Hierarchical model: The hierarchical model represents data in a tree structure. In this model, parent-child relationships are organized hierarchically, and each parent has multiple children associated with it, where one parent can have multiple children, but no child can be associated with more than one parent.
• Networked model: A networked model is a representation of data in a graph structure. Entities (nodes) represent records, and relationships (edges) represent associations between entities. In this model, one entity can be associated with multiple entities and can also be related to multiple entities.
• Relational Model: The relational model will represent data in a tabular format. Data is represented as a table, and a table consists of rows (records) and columns (fields). Relationships between tables are expressed using primary and foreign keys. In the relational model, data manipulation and querying is performed using SQL (Structured Query Language).
• Object-oriented model: An object-oriented model represents data as objects. An object is a combination of data and associated methods (operations) that are generated from a template called a class. Object-oriented models apply the concepts of object-oriented programming to databases.

These models are chosen to suit the specific requirements of database design and operation, but the most widely used model today is the relational model. It is employed by many major database management systems (DBMS). However, other models may also be used depending on specific scenarios and requirements.

<SQL (Structured Query Language)>

SQL is a formalized language with a syntax and set of grammars for database operations and queries, and is the standard query language used in a relational database management system (RDBMS) SQL is used to perform the following key operations

• Data retrieval (SELECT statement): The SELECT statement is used to extract data from tables in a database, allowing data to be selected based on specific columns or conditions. The JOIN statement can also be used to associate multiple tables and retrieve related data.
• Add, Update, and Delete Data (INSERT, UPDATE, and DELETE statements): These statements are used to add new data to a table in the database, update existing data, and delete data. The INSERT statement adds a new row, the UPDATE statement changes the value of an existing row, and the DELETE statement deletes a row.
• Filtering and sorting data (WHERE, ORDER BY statements): The WHERE clause can be used to filter data based on specific criteria, and the ORDER BY clause can be used to sort data by specific column values.
• Aggregating Data (GROUP BY and HAVING Statements): The GROUP BY clause can be used to group data based on specific columns, and the HAVING clause can be used to specify conditions on the grouped data. This allows for aggregation of data and the use of aggregate functions (SUM, AVG, COUNT, etc.).
• Data constraints (PRIMARY KEY, FOREIGN KEY, UNIQUE constraints, etc.): These statements are used to define constraints on the tables in the database. tables, and a UNIQUE constraint ensures that the value of a particular column is unique.

SQL is used not only for database operations, but also for administrative tasks such as database creation, modification, and deletion, and is available in a variety of database management systems (DBMS).

<Index>

A database index is a data structure that improves the performance of searching data in a database and is a combination of keys and values associated with a particular column or field. The database’s use of indexes speeds up data retrieval. The following are some important points about indexes in databases.

• Purpose of Indexes: Indexes provide a means to efficiently retrieve data in a database. Indexes sort data based on specific columns and optimize the physical arrangement of data, thereby increasing the speed of data retrieval and improving database performance.
• Index Types: There are many different types of indexes in databases. The main types of indexes include B-tree indexes, hash indexes, bitmap indexes, etc. The choice of each index is based on data characteristics and access patterns.
• Index Creation: Indexes are created using the CREATE INDEX statement in the database management system (DBMS). When creating an index, the target table and columns and the type of index must be specified and maintained whenever data in the database is changed or added.
• Index Selection and Design: Indexes need to be designed appropriately. This requires analysis of the distribution, size, and frequency of updates of the data, taking into account the query and data access patterns for which the indexes will be used. Properly designed indexes will provide optimal search performance.
• Indexing caveat: While indexes improve database performance, they tend to increase database storage capacity. Creating and maintaining indexes can be costly, so an appropriate balance must be found. Also, if data is frequently updated or deleted, indexes are updated as well, and the performance impact should be considered.

Database indexes play an important role in improving database performance, and properly designed indexes increase data retrieval efficiency and improve query response times. However, the selection and design of indexes requires careful consideration and should be based on the requirements and characteristics of the database.

<Transaction Processing>

Database transaction processing is a mechanism for executing a series of database operations in a consistent and reliable manner. A transaction consists of multiple database operations and terminates either when all operations are successfully completed or none are executed. The database system controls the processing of transactions and ensures data integrity. The following are some important points regarding transaction processing in databases.

• ACID Characteristics: Transaction processing has characteristics called ACID (Atomicity, Consistency, Isolation, and Durability).
  • Atomicity: The transaction as a whole is treated as one indivisible operation, and either all operations complete successfully or none of them are executed. If an error occurs during the process, the transaction is rolled back and the data returns to its original state.
  • Consistency: A transaction keeps the data in the database in a consistent state. This is achieved by ensuring that database consistency constraints (constraints, relationships, etc.) are maintained before and after the transaction.
  • Isolation: Multiple transactions running simultaneously are processed in such a way that they do not affect each other. One transaction does not see the results of another transaction in progress. This ensures data consistency and reliability.
  • Durability: Once a transaction completes successfully, its changes are permanently reflected in the database. Changes to successfully completed transactions are not lost in the event of database system failure or malfunction.
• Transaction control: The database management system (DBMS) is responsible for controlling and managing transactions. Transactions can be explicitly BEGIN, COMMIT, or ROLLBACK. A commit confirms the changes made to the transaction and stores them permanently in the database, while a rollback undoes the changes made to the transaction and returns the database to the state it was in before the transaction was initiated.
• Transaction isolation levels: Transaction isolation levels control the extent to which transactions affect each other. There are different isolation levels, such as READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE. By properly setting transaction isolation levels, data integrity and performance among multiple concurrently executed transactions can be balanced.

Database transaction processing is important to ensure data consistency and reliability, and proper control of transactions and meeting ACID characteristics will help maintain database integrity and ensure data accuracy and reliability.

<Database Security>

Database security is a generic term for measures and techniques to protect data in databases from malicious access and attacks. Since databases contain important business, personal, and confidential information, appropriate security measures are essential. The following describes the main elements and measures related to database security.

• Access Control: Controlling access to the database is fundamental to security. This includes user authentication and authorization, where users are authenticated with a unique identifier (user name) and password, and given access rights to the database (read, write, delete, etc.). Access control must be properly implemented at the database management system (DBMS) and application levels.
• Encryption: Data protection and confidentiality can be ensured by encrypting the data in the database. It is recommended that critical data and personal information be encrypted before the data is stored within the database, and it is also important to use encryption during database backup and data transfer.
• Parameterization and configuration management: Proper parameterization and configuration management is needed to enhance database security. This includes changing default settings, applying security patches, monitoring logs, and setting alerts for security events. Database administrators should follow the latest security guidelines to properly manage database settings.
• Audit Logs and Audit Trails: Audit logs will be the mechanism for recording the history of access and operations in the database. Audit logs are useful for detecting and troubleshooting security incidents and require proper storage and regular audit log reviews.
• Vulnerability Management and Penetration Testing: Database vulnerability management involves applying security patches to the database system and performing periodic vulnerability scans. It is also important to conduct penetration testing to identify and correct database security weaknesses.
• Backup and Disaster Recovery: Database backups help protect data and recover from disasters. It is important to implement an appropriate backup strategy and disaster recovery plan to minimize data loss or loss.

Database security is an ongoing effort and it is important to follow the latest security guidelines and best practices. Database administrators need to develop a comprehensive security strategy that includes assessing security risks, implementing appropriate countermeasures, and raising security awareness.

<Database Management System (DBMS)>

Database Management System (DBMS) is a generic term for software systems used to create, manage, operate, and protect databases to ensure efficient database management and data consistency. The main roles and general functions of a DBMS are described below.

• Database Creation and Management: The DBMS supports database creation and management. It creates data structures such as database schemas, tables, and indexes, manages the physical storage of the database, and is also responsible for database backup and recovery, performance tuning, and security management.
• Data Manipulation and Query Processing: A DBMS provides an interface for performing operations such as adding, updating, and deleting data, as well as performing searches and aggregations against the database for query processing. Typically, the database is accessed using SQL (Structured Query Language).
• Data Integrity and Consistency Management: The DBMS ensures the integrity and consistency of the database and applies consistency constraints (constraint conditions) to maintain data integrity. It also controls transaction processing to prevent incorrect data modification and race conditions.
• Access Control and Security: The DBMS provides access control and security management for the database. It will provide user authentication and privilege management, ensure data confidentiality and protection, and enforce audit logs and security policies.
• Performance Optimization: The DBMS provides features to optimize database performance. This includes indexing, query optimization, caching, data compression, and distributed database management.
• Multi-user support: DBMSs allow multiple users to access the database simultaneously, managing transaction isolation levels and concurrency controls to ensure data integrity and performance.

Common DBMSs include Oracle Database, MySQL, Microsoft SQL Server, PostgreSQL, MongoDB, etc. Each DBMS offers different features and functions to meet specific requirements and purposes. A database management system is an important component of the database design, operation, and security foundation.

Types of databases

Databases can be classified based on various formats and models. The following describes some common types of databases.

• Hierarchical databases: Hierarchical databases represent data in a tree structure. Parent-child relationships are organized hierarchically, multiple children are associated with one parent, and data relationships are hierarchically restricted because of the parent-child relationship. Hierarchical databases were used as an early database model, but are less common due to the emergence of relational models.
• Networked database: A networked database represents data in a graph structure. It consists of entities (nodes) and relationships (edges), and expresses the relationships among entities. An entity can be associated with multiple entities and can also be related to multiple entities. Network-type databases were developed to overcome the limitations of hierarchical databases, but their use is limited due to the spread of relational databases.
• Relational databases: Relational databases represent data as tabular tables. Tables consist of rows (records) and columns (fields), and data is stored in these tables. Relationships are represented by primary keys and foreign keys, and relational databases use SQL (Structured Query Language) to manipulate and query data. Relational databases are the most widely used database model in companies and organizations.
• Object-Oriented Database: An object-oriented database applies the concepts of object-oriented programming to databases. Data is represented as objects, and object-oriented characteristics such as classes, inheritance, and polymorphism are supported. Object-oriented databases are used to represent complex data structures and business logic.
• NoSQL Databases: NoSQL (Not only SQL) databases will refer to models other than relational databases, which are designed to meet requirements such as scalability, high performance, and flexibility. Typical NoSQL databases include document, key-value, column-family, and graph databases.

Open Source Database Systems

An open source database system is a database system whose source code is publicly available and can be freely used, researched, modified, and distributed by anyone, offering a high degree of flexibility and customization and often supported by the community. The following is a list of popular open source database systems.

• MySQL: MySQL is a very popular open source relational database management system. It is widely used in web applications and enterprise environments because of its simple and easy-to-use interface, high performance, and scalability.
• PostgreSQL: PostgreSQL is an open source relational database management system with advanced features and scalability. It is widely used as a robust database solution with excellent features such as transaction processing, query optimization, and data integrity.
• MongoDB: MongoDB is an open source NoSQL database. It is a document-oriented database, featuring flexible schema design and scalability, storing documents in JSON format for fast read and write operations.
• SQLite: SQLite is a lightweight open source relational database engine. It is a serverless database and is often used as a file-based database. Such features allow it to be used in resource-constrained environments such as embedded systems and mobile applications.
• Redis: Redis is an open source database known as a fast key-value store. It uses in-memory data structures to provide fast data access and caching capabilities, and is used for a variety of applications including caching, session stores, and messaging.
• RDF store: RDF store is a NoSQL database that handles RDF data and is one of the graph-based databases such as Noo4J and Datomic, and is used as a database for knowledge data such as DBPedia.

Reference Information and Reference Books

As for specific database implementations, “Overview of Database Technology and Examples of Implementation in Various Languages” describes relational databases, “Redis: Overview and Basic Use of K-V Type DB (Redis), a NoSQL DB” describes Redis, and “RDF store and About Graph DB, a SPARQL NoSQL DB” describes graph databases. Also, see “Integrating Web Server and DB with Clojure” and “Clojure and Redis” for information on integration with web technologies. Database technology as a whole is also discussed in “Database Technology.

For reference books, see “Seven Databases, Seven Worlds”.

“RDBMS In-Depth: Mastering SQL and PL/SQL Concepts, Database Design, ACID Transactions, and Practice Real Implementation of RDBM”

“Mastering Redis“

“NoSQL For Dummies“

“Hands-On Graph Analytics with Neo4j: Perform graph processing and visualization techniques using connected data across your enterprise”