What exactly is a relational database?

Learned a period of time database, look back, incredibly also understand the principle of relational database is what, shame. First of all, learn the "cow" insights, and then slowly digest the understanding.

A database is an organization (or department) that is associated with a data set that is structured in a way that is determined by a data model. Database management system is a computer system software that helps users to establish, use, manage and maintain database. In other words, the database management system is the platform to develop a practical application database and support its execution. The database management system must be consistent with the data model of the database it manages.

1. Data Model

A data model is a tool for abstracting real-world data features that describe and manipulate data and information in the real world. The data model should be able to simulate the real world on the ground, which is convenient for people to understand and to be implemented on computer. The data model consists of three parts: data structure, data operation, and integrity rules. The data structure describes the basic components of the constituent database; The operation description describes the set of operations that consent to the operation of the structure; The integrity Rule description describes the constraints and storage rules for the data structure.

2. Relational Data Model

The structure of relational data model is a common two-dimensional table in daily transaction processing (such as payroll table). The relational data model sees data as a single table element in a two-dimensional table that is determined by the row and column numbers, that is, the relational data model organizes, stores, and processes data and information in a two-dimensional table. From an application perspective, the basic component of a relational database of any organization (or department) is a two-dimensional table, or the database of an organization (or department) is made up of several interrelated two-dimensional tables. Because the two-dimensional table structure is clear, simple, easy to understand, but also easy to implement the computer (storage, operation, control), coupled with a relational data model has a mathematical basis (set theory, relational algebra), so today's database management system software is based on a relational data model developed, such as SQL SERVER, ORACLE, DB2, SYBASE, ACCESS, FoxPro, and more. In other words, the accounting data base system developed by these relational database management system software for an organization must organize the data according to the relational data model. So what is the connection between a two-dimensional table in a relational data model and a data file in a database?

Here we introduce the basic concepts of the relational data model and the corresponding relationship with the data files in the database through the chart of accounts code:

(1) Relationships, two-dimensional tables, data files: Relational data models are used to describe the set of data objects to be managed in the real world that can be differentiated from one another. Each relationship has a relationship name and a set of attributes that describe its characteristics, and people are using these attribute sets to discriminate between different relationships. such as accounting vouchers, ledger accounts, General Ledger can be called relationships, they are to manage the set of data objects, have their own set of properties. A relationship is represented by a two-dimensional table with the table name corresponding to the relationship name. A two-dimensional table consists of a finite number of rows that are not repeated, and each column in the table cannot be divided. A two-dimensional table is stored in a relational database with a data file. such as the "Chart of accounts" in the accounting database with a data file storage, the file name can be used in the table name "Ledger account code", so that the file content stored in the computer and the real-world management of data objects linked.

(2) Record: Each row in a two-dimensional table is called a record, describing a detailed individual in the relationship, which is a record value in the data file. The record of the first act Cash account in table 1 describes the value (characteristics) of all the attributes of the cash account in the ledger account code file.

(3) Attributes, columns, fields: Each column in a two-dimensional table is an attribute that describes a feature of the relationship. The entire column of a two-dimensional table forms a set of properties for a relationship that can be differentiated by different two-dimensional tables (relationships). The data for each column in a two-dimensional table belongs to the same type. The column name of each column corresponds to the property name of the relationship, and the name of the field in the corresponding data file at the same time. As shown in table 1, the attribute of the ledger account code is represented by 6 columns, and the third column represents the attribute "account nature", when a record has a value of 1 o'clock, which represents an asset class account.

(4) Main code, main keyword: refers to a column (attribute) in a two-dimensional table or a number of columns (or attribute groups) whose values uniquely determine a record in a table or data file. The "account code" attribute in table 1 can be used as a master (or primary keyword) to uniquely identify each ledger account in the table.

(5) Domain: Describes the value type and range of a field in a two-dimensional table for each column property or data file. As shown in the parentheses below the column name of each column in table 1, the value type and range of the column is represented, and the fourth column, "Underlying detail flag", indicates whether an account is the lowest-level detail account (no longer has a lower account), only two values T (true) and F (false).

(6) Relational mode: A relational pattern consists of a relationship name and all its attributes, corresponding to a two-dimensional table table name and header column row (set of columns), constitutes a two-dimensional table framework, at the same time is the design of the two-dimensional table data file structure basis.

At this point, we have a visual introduction to the relationship between the relational database, the two-dimensional table, data files, the corresponding relationship between the various concepts. Because the rows in the two-dimensional table and the data files, the columns (attributes) of the two-dimensional table and the fields of the data files correspond to each other, the auditors only have to master the two-dimensional table structure of the accounting database and the association between the tables to analyze the structure of the electronic accounts.

For example, convert the chart of Accounts code (table 1) into a data file structure in a relational database:

3. Data manipulation of relational data models

From the point of view of mathematics, the data operation of relational data model is based on set operation, and the Operation object and operation result are all collections. From the point of view of data processing, the object and result of the operation are two-dimensional tables. The operation of the two-dimensional table mainly includes:

(1) Operation of rows (records) in a table: refers to a conditional operation on a record of a specified range in a table, and the result of the operation is composed of a new table. For example, the asset class account is filtered from the chart of accounts to form the new asset Class account code table, which is scoped to the entire Ledger account code table, provided that the account nature equals 1. When you manipulate rows in a table, the resulting table has the same structure as the original table, and the number of records is less than or equal to the original table.

(2) Manipulating columns (attributes) in a table: refers to a conditional operation on a specified column in a table, and the result of the operation consists of a new table. For example, the "Account code" and "account name" columns are selected from the "Chart of Accounts" to form the corresponding table for the new account code, and the new table has only two columns: "Account code" and "account name". Obviously, the structure of the result table after the column operation differs from the original table, and the resulting table is less than or equal to the original table.

(3) Connection: A conditional join operation on two or more tables to generate a new table. The result table after the join operation is greater than or equal to the table before the operation.

From the point of view of application, the data manipulation function in the two-dimensional table mainly includes updating (adding, altering, deleting) data and retrieving (querying) data, i.e. filling and altering data in two-dimensional table, and retrieving data from the table for processing application.

4. Data integrity rules for relational data models

Data integrity means that the data stored in the database is meaningful or correct. Data integrity rules in a relational data model refer to the definition of a two-dimensional table and some constraints to be followed during operation. Mainly includes:

(1) Entity integrity: Refers to each table must have a master code, and the table does not agree with the existence of no master code value of the record and the same record of the main code value. Each record in table 1 must have an account code and cannot have a record of the same account code and no account code.

(2) Participation completeness: The value of a column in a table is constrained by the value range of a column in a table, and describes the association between multiple tables. For example, the value of the "Account Code" column in the Accounting voucher table is limited by the range of account code values in the chart of account codes.

(3) User defined integrity. The database constraints defined by the pointer on a detailed application reflect that the data involved in a detailed application must meet the application semantics requirements. That is, restricting the value type and scope of the attribute, and preventing the value of the attribute from applying semantic contradiction. As shown in table 1, the "nature of the account" can only be valued at 1 (assets), 2 (liabilities), 3 (equity), 4 (cost), and 5 (profit and loss).

5. Inspiration from the relational data model

(1) The accounting database based on the relational data model is constructed with two-dimensional table as the basic part, and the correlation between data files can be represented by the correlation between the two-dimensional table, and the data integrity constraints must be satisfied for the definition of the two-dimensional table and the operation of the database. Building an accounting database first of all, the object of accounting management, such as ledger accounts, accounting vouchers, journals, ledgers, General Ledger and their relationship between the abstract into a two-dimensional table Form, to clarify their two-dimensional table structure is also clear their data file structure, that is, the electronic account structure. Therefore, the structure design of accounting database can be transformed into the relationship design between two-dimensional table and two-dimensional table of accounting data, and a two-dimensional table header column (attribute set) reflects the structure characteristic of the table and is the basis of design data file structure.

(2) The relational database management system developed according to the relational data model is the tool software to develop and manage the accounting data base system, and also the platform to support the implementation of the accounting data base system, no matter what one accounting database must be executed under the online management of a relational database management system. Because the different database software companies provide the relational database management system software version number of the functional strength, the implementation of the computer system to adapt to the environment (single-machine, network, etc.), the table is provided by the operation of commands, etc., so the auditor to review the electronic accounts, The first thing to know is the name of the database management system software (such as SQL Server or Oracle), the version number (stand-alone, the network, the first edition), the Open Database (table) and the command format and commands for the table operation. Although the various relational database management system software version number is different, but through the above to the relational data model operation can understand: Table main operation type and function basically consistent. Auditors only need to master the most important open, search, Summary database (table) and other operational orders to be able to review the accounting database of the basic work, not deep unpredictable.

This article from Csdn Blog, reproduced please indicate the source: http://blog.csdn.net/hdhai9451/archive/2008/12/06/3459700.aspx

What exactly is a relational database?