MySQL series: Basic concepts of MySQL relational database

1. Basic Concepts

Data is a symbolic record that describes things. It refers to identifiable information recorded using physical symbols.

1. Database (DB) refers to an organized and shareable collection of data stored in a computer for a long time. Data should be organized, described and stored according to a certain data model, with low redundancy and high data independence. The system should be easy to expand and can be shared by multiple users.

Three basic characteristics of data:

• Persistent storage
• Organized
• Shareable

2. Database management system (DBMS) is a set of software specifically used to establish and manage databases, which is between application programs and operating systems. It implements various functions of the database system. It is the core of the database system.

• Data definition function
• Data manipulation functions
• Database operation management function
• Database creation and maintenance functions
• Data organization, storage and management functions
• Data security assurance
• Data backup function

3. The database system consists of:

• User (end user)
• Program (API)
• Application: refers to database-based applications
• Database Management System
• Database Administrator (DBA): Responsible for the planning, design, coordination, maintenance and management of the database
• database

4. Database system architecture:

• Standalone architecture
• Mainframe/Terminal Architecture
• Master-slave architecture c/s: In the client/server structure (Client/Server, C/S), the "client", "front-end" or "presentation layer" mainly completes the interaction tasks with database users; the "server", "back-end" or "data layer" is mainly responsible for data management.
• Distributed architecture

5. Structure of the database system

• Mode: Also called conceptual mode or logical mode, it is the logical view of data, that is, the conceptual view
• External mode: also called sub-mode or user mode data view, that is, user view
• Internal mode: Also called storage mode, it is an internal view or storage view

External mode/mode image ensures the logical independence of data and program

Internal mode/mode image ensures physical independence of data and programs

6. Model

A model is a simulation and abstract expression of real-world characteristics.

The data model is an abstraction of the characteristics of real-world data and describes the common content of the data.

2. Development of Database Management Technology

• Embryonic stage ----- file system uses disk files to store data
• Primary stage: the first generation of databases appeared in the form of network models and hierarchical models.
• Intermediate stage - second-generation database relational database and structured query language
• Advanced stage - the new generation of database "relational-object" database

1. Disadvantages of file system management

• Writing applications is inconvenient
• Data redundancy is inevitable
• Application Dependencies
• Concurrent access to files is not supported
• Weak connection between data
• Difficulty representing data by user view
• No safety control function

2. Advantages of Database Management System

• A collection of interrelated data
• Less data redundancy
• Programs and data are independent of each other
• Ensure data security and reliability
• Maximize the accuracy of data
• Data can be used concurrently while ensuring consistency

3. Relational Database (RDBMS) Concept

A relational database is a collection of all relations that constitute a relational database. A type of database that uses the relational model as the logical model of data and relations as the way to organize data. Its database operations are based on relational algebra.

• A table is a two-dimensional data structure consisting of a table name, columns, and several rows of data.
• Relation: A relation is a two-dimensional table. And satisfy the following properties: The order of rows and columns in the table is not important
• Row: Each row in the table is also called a record or a tuple. Data in a table is stored in rows.
• Column: Each column in the table is called an attribute, and a field is called a field.
• Component: An attribute value in a tuple (row) is called a component.
• Key: The values ​​of an attribute (or attribute group) can be used to uniquely identify the tuple of the relation, and these attributes (or attribute groups) are called the keys of the relation.
• Primary key: A field used to uniquely identify a record.
• Foreign Key: An attribute (or attribute group) is not the primary key or candidate key of this relation, but is the primary key of another relation.
• Referencing Relation and Referenced Relation: Referencing Relation is also called slave relationship, and referenced relationship is also called master relationship. They refer to two relationships associated with foreign keys.
• Domain: The value range of the attribute, for example, gender can only have two values: 'male' and 'female'.
• Data Type Each column has a corresponding data type, which is used to limit (or allow) the data stored in the column.

1. Transaction

Multiple operations are treated as a whole

Transactions follow ACID properties:

• AAtomicity: Indivisibility, all actions must be done, or none of them
• C consistency: maintain data consistency
• Isolation: The final data cannot be viewed until the transaction is completed
• Dirty data: data that has not been completed by the transaction
• D Durability: lasting effect, permanent

2. ER Model (Entity-Relationship)

Entity: Objective things or abstract events that exist objectively and can be distinguished from each other are called entities. In the ER diagram, entities are represented by rectangular boxes, and the entity names are written inside the boxes.

​Attribute: A characteristic or property possessed by an entity

​Relationship: A relationship is a collection of associations between data and is an objectively existing application semantic chain. There are connections within entities and connections between entities. The connections between entities are represented by diamond-shaped boxes.

Type of contact:

• One-to-one contact (1:1)
• One-to-many relationship (1:n)
• Many-to-many relationship (m:n)

3. History of relational databases

• In 1970, E.F. Codd of IBM proposed the relational model, which laid the theoretical foundation for relational databases.
• In the late 1970s, a major breakthrough was made in the theoretical research of relational methods and the development of software systems.
• In 1981, relatively mature relational database management technology emerged, confirming the advantages of relational databases: advanced non-procedural language interface and better data independence.
• After the 1980s, the network model and hierarchical model were closely integrated with the underlying implementation, and the relational model had a solid theoretical foundation and became the mainstream data model.

4. Elements of the relational data model

Elements of the data model include:

• Relational Data Structures
• Relational Operation Set
• Relational integrity constraints

5. Three elements of data

• Data structure: It includes two categories. One category is objects related to data type, content, and properties, such as domains, attributes, and relationships in the relational model; the other category is objects related to the connection between data, which express the structure of data records and fields from the data organization layer.
• Data operations:

Data extraction, extracting interesting content from a data set.
Data update: Change the data in the database.

• Data constraints: a set of integrity rules

Entity integrity
Domain Integrity
Referential Integrity

6. Constraint

• Primary key constraint: A combination of one or more fields. The data filled in must be able to uniquely identify the row in this table. Data must be provided, that is, NOT NULL. A table can only have one.
• Unique key constraint: A combination of one or more fields. The data entered must be able to uniquely identify the row in this table. NULL is allowed. Multiple keys can exist in a table.
• Foreign key constraint: The data that can be filled in a field in one table depends on the data already in the primary key or unique key of another table.
• Check constraint: Field value is within a certain range.

7. Index

​Copy the data in one or more fields in a table and save them separately, and these need to be sorted and stored in a specific order.

8. Relational operations

• Select: Select the rows that meet the conditions
• Projection: Select the required fields
• Join: Association between fields in tables

9. Data Model

• Data Abstraction:

Physical layer: data storage format, that is, how RDBMS organizes files on disk Logical layer: from the DBA's perspective, describes what data is stored and what kind of relationship exists between data View layer: from the user's perspective, describes some data in the DB

• Classification of relational models:

Object-based relational model Semi-structured relational model

4. RDBMS Design Paradigm

When designing a relational database, we must follow different specifications and design a reasonable relational database. These different specifications are called different paradigms. Various paradigms present sub-specifications. The higher the paradigm, the less redundancy the database has.

Currently, there are six paradigms for relational databases: the first paradigm (1NF), the second paradigm (2NF), the third paradigm (3NF), the Badesco paradigm (BCNF), the fourth paradigm (4NF) and the fifth paradigm (5NF, also known as the perfect paradigm).

The minimum required normal form is the first normal form (1NF). The first normal form that further meets more specification requirements is called the second normal form (2NF), and the remaining normal forms are similar.

Generally speaking, a database only needs to meet the third normal form (3NF).

• 1NF: There are no repeated columns. Each column is an indivisible basic data item. There cannot be multiple values ​​in the same column. That is, an attribute in the entity cannot have multiple values ​​or repeated attributes. Excluding fields of the same type, there are no duplicate columns.

Note: The first normal form (1NF) is the basic requirement for the relational model. A database that does not meet the first normal form (1NF) is not a relational database.

• 2NF: The attributes are completely dependent on the primary key. The second normal form must first satisfy the first normal form, requiring that each row in the table must be uniquely distinguishable. Usually a column is added to the table to store the unique identifier PK of each instance. Non-PK fields need to be directly related to the entire PK.
• 3NF: Attributes do not depend on other non-primary attributes. To satisfy the third normal form, the second normal form must be satisfied first. The third normal form requires that a database table does not contain non-primary keyword information that is already contained in other tables, and there cannot be a subordinate relationship between non-PK fields.

This concludes this article on the basic concepts of MySQL relational database, the opening chapter of the MySQL series. For more information about MySQL relational database, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!