Principles of relational databases

What is the principle of relational database?

Relational databases store data in the form of rows and columns to make it easier for users to understand. This series of rows and columns is known as a table, and a set of tables makes up a database. The user uses a query (Query) to retrieve data from the database. A Query is a SELECT statement used to specify rows and columns in a database. A relational database usually contains the following components: Client application (Client) Database server (Server) Database (Database) StructuredQueryLanguage (SQL) Client and Server side of the bridge, Client with SQL to send requests to the Server side, the Server returns the Client’s request. Server returns the results requested by the Client. Now popular large-scale relational database IBMDB2, IBMUDB, Oracle, SQLServer, SyBase, Informix and so on. The basic form of storing and managing data in a relational database management system is a two-dimensional table.

Title Relational database, how relations are related to each other?

Relational databases are actually databases based on the relational model

Because a relation is actually a two-dimensional table, where each row of the table is an element, each column is an attribute, and a tuple is an element of the Cartesian product of the set of attributes involved in the relation. A relationship schema should be a 5-tuple. (A description that defines a relationship is called a relational schema). It can be formalized as R(U,D,dom,F) where R is the name of the relationship, U is the set of attribute names that make up the relationship, D is the domain from which the attributes in the attribute group U come, dom is the set of mappings of the attributes to the domain, and F is the set of dependencies of the data between the attributes.

What is a relational database What is a relational database explained

1, relational database, is a database that uses a relational model to organize data, which stores data in the form of rows and columns so that it is easy for the user to understand, relational databases this series of rows and columns is known as a table, and a set of tables make up the database.

2. Users retrieve data from the database through queries, which is an execution code used to qualify certain areas of the database. The relational model can be simply understood as a two-dimensional table model, and a relational database is a data organization consisting of two-dimensional tables and the relationships between them.

What is a relational database

A relational database, a database based on a relational model, deals with the data in the database with the help of mathematical concepts and methods such as set algebra. Various entities in the real world and various connections between them are represented by relational models. The relational model was first proposed by Edgar Codd in 1970, along with the “Codd’s Twelve Laws”. Nowadays, although there are some criticisms of this model, it is still the traditional standard for data storage. The standard data query language, SQL, is a language based on relational databases that performs retrieval and manipulation of data in relational databases. The relational model consists of three parts: a relational data structure, a collection of relational operations, and relational integrity constraints.

Simply put, a relational database is a database consisting of multiple two-dimensional row and column tables that can be linked to each other.

What are the principles of database analysis?

Basic Concepts of Database Systems

Data: actually symbolic records that describe things.

Characteristics of data: it has a certain structure, there is a distinction between type and value, such as integer, real, character, etc.. And the value of the data gives the value in line with the stereotype, such as the integer value 15.

Database: is a collection of data, with a unified structure and stored in a unified storage medium, is the integration of a variety of application data, and can be shared by various applications.

Database storage data is stored according to the data provided by the data model, has the integration and sharing characteristics.

Database management system: a system software, responsible for data organization, data manipulation, data maintenance, control and protection and data services in the database, is the core of the database.

Database Management System Functions:

(1) Data Schema Definition: i.e., constructing its data framework for the database;

(2) Physical Construction of Data Access: Providing effective access methods and means for the physical access to and construction of data schema;

(3) Data Manipulation: Facilitating the use of data in the database by the users such as querying, inserting, modifying, deleting, etc. as well as simple data services.

(4) data integrity, security definition and checking;

(5) database concurrency control and fault recovery;

(6) data services: such as copying, dumping, reorganization, performance monitoring and analysis.

In order to accomplish the above six functions, the database management system provides the following data languages:

(1) data definition language: responsible for the schema definition of the data and the construction of physical access to the data;

(2) data manipulation language: responsible for the manipulation of the data such as querying and adding, deleting, and altering, etc.

(3) data control language: responsible for the definition and security of data integrity and security. definition and checking of data integrity and security, as well as concurrency control and fault recovery.

Data language according to its use has two structural forms: interactive commands (also known as self-contained or autonomous language) host-type language (generally embedded in some host language).

Database administrator: a professional manager who plans, designs, maintains, and monitors the database.

Database system: by the database (data), database management system (software), database administrators (personnel), hardware platform (hardware), software platform (software) five parts of the operating entity.

Database application system: consists of database system, application software and application interface.

File system stage: provides simple data sharing and data management capabilities, but it can not provide a complete, unified, management and data sharing capabilities.

Hierarchical database and mesh database system stage: provides strong support for unification and sharing of data.

Relational database system stage

Basic characteristics of database systems: data integration, high data sharing and low redundancy, data independence (physical independence and logical independence), unified data management and control.

Three levels of database system schema:

(1) Conceptual schema: the description of the global data logical structure of the database system, the public data view of all users;

(2) External schema: also known as the sub-schema and user schema. It is the user’s data view, that is, the data schema seen by the user;

(3) inner schema: also known as the physical schema, which gives the database physical storage structure and physical access methods.

Two levels of mapping in a database system:

(1) conceptual schema to inner schema;

(2) outer schema to conceptual schema.

4.2 Data Model

The concept of data model: it is an abstraction of data characteristics, describes the static characteristics, dynamic behavior and constraints of the system at the abstraction level, and provides an abstract framework for the information tables and operations of the database system. Describes data structures, data operations, and data constraints.

Basic Concepts of the E-R Model

(1) Entity: things in the real world;

(2) Attribute: characteristics of things;

(3) Connection: relationships between things in the real world. There are one-to-one, one-to-many, and many-to-many connections for sets of entities.

The linkage between the three basic concepts of the E-R model: entities are the basic units in the conceptual world, attributes have attribute domains, and each entity can take the values in the attribute domains. All attribute values of an entity are called tuples.

The graphical methods of the E-R model: (1) entity set representation; (2) attribute table method; (3) linkage representation.

The basic structure of the hierarchical model is a tree structure with the following characteristics:

(1) Each tree has one and only one node without a biparent, called the root;

(2) All nodes in the tree except the root have one and only one biparent.

Graph theoretically, the mesh model is an undirected graph without any conditional restrictions.

The relational model is represented using two-dimensional tables, or tables for short, consisting of a table frame and a tuple of tables. A two-dimensional table is a relation.

The smallest attribute in a two-dimensional table that uniquely identifies a tuple is called a key or code. The key that is selected from all the marquee keys to be used by the user is called the primary key. If an attribute in table A is the key of a table B, the set of attributes is said to be the foreign key or foreign code of A.

Data Constraints in Relationships:

(1) Entity Integrity Constraints: Constraints that the values of attributes in the primary key of a relationship cannot be null values;

(2) Referential Completeness Constraints: Basic constraints between relationships;

(3) User-Defined Integrity Constraints: It reflects the semantic requirements of the data in a specific application.

4.3 Relational Algebra

One of the characteristics of a relational database system is that it is based on data theory, and there are many data theories that can represent the data operations of a relational model, the most famous of which are relational algebra and relational algorithms.

Basic operations of the relational model:

(1) insertion (2) deletion (3) modification (4) querying (including projection, selection, and Cartesian product operations)

4.4 Database Design and Management

Database design is the core of data applications.

Two approaches to database design:

(1) data-oriented: information needs-oriented, taking into account processing needs;

(2) process-oriented: processing needs-oriented, taking into account information needs.

Life cycle of a database: requirements analysis phase, conceptual design phase, logical design phase, physical design phase, coding phase, testing phase, operation phase, further modification phase.

Requirements analysis commonly used structural analysis methods and object-oriented methods. The structured analysis (SA for short) approach analyzes a system in a top-down, layer-by-layer decomposition. Data flow diagrams are used to express the relationship between data and processing. For database design, the data dictionary is the main result of detailed data collection and data analysis.

The data dictionary is a collection of data descriptions, including five parts: data items, data structures, data flow (either data items or data structures), data storage, and processing.

The purpose of database conceptual design is to analyze the intrinsic semantic relationships of data. There are two approaches to design

(1) the centralized schema design approach (for small or uncomplicated units or departments);

(2) the view integration design approach.

Design method: E-R model and view integration.

There are three general design sequences for view design: top-down, bottom-up, and inside-out.

Several types of conflicts for view integration: naming conflicts, conceptual conflicts, domain conflicts, and constraint conflicts.

Relational view design: the design of relational views is also known as outer schema design.

The main role of the relational view:

(1) to provide logical independence of data;

(2) to adapt to the different needs of the user’s data;

(3) there is a certain degree of data confidentiality.

The main goal of the physical design of the database is to adjust the internal physical structure of the data and choose a reasonable access path to improve database access speed and effective use of storage space. In general, RDBMS leaves the user to participate in the physical design of the contents of the index design, integrated cluster design and partition design.

Database management:

(1) database establishment;

(2) database adjustment;

(3) database reorganization;

(4) database security and integrity control;

(5) database failure recovery;

(6) database monitoring.

What is a relational database?

The term relational database refers to a database that employs a relational model to organize data. The relational model was first proposed by Dr. E.F. Codd, a researcher at IBM, in 1970. In the following decades, the concept of relational model has been fully developed and has gradually become the dominant model of database architecture. Simply put, the relational model refers to a two-dimensional table model, and a relational database is a data organization composed of two-dimensional tables and the links between them. The following is a list of commonly used concepts in the relational model. Relationship: can be understood as a two-dimensional table, each relationship has a relationship name, which is commonly referred to as the table name. Tuple: can be understood as a row in a two-dimensional table, often referred to as a record in databases. Attribute: can be understood as a column in a two-dimensional table, often referred to as a field in the database. Field: the range of values of an attribute, that is, the limit of values of a column in the database. Keyword: a set of attributes that uniquely identifies a tuple. Often referred to as a primary key in databases, it consists of one or more columns. Relationship Schema: A description of a relationship in the format: relationship name (attribute 1, attribute 2, …, attribute N). It is often referred to as a table structure in databases.