Introduction to SQL Server databases

Using a database can store data efficiently and clearly, allowing people to manage data more quickly and conveniently. The database has the following characteristics:

1. It can store a large amount of data information in a structured manner to facilitate effective retrieval and access by users.

2. It can effectively maintain the consistency and integrity of data information and reduce data redundancy.

3. Can meet the sharing and security requirements of applications.

Basic concepts of database:

1. Data: Symbol records that describe things become data (Data), including numbers, text, graphics, images, sounds, file records, etc., which are all data. Data is stored in a unified format in the form of "records".

2. Database and database tables: Tables are used to store specific data, and databases are collections of tables. It is a collection of mutual data stored in a certain organizational manner. For example, a relational database table consists of records, records are composed of fields, and fields are composed of characters or numbers. It can be shared by various users with minimal redundancy and high data independence. It is a collection of related data that is managed uniformly.

Database Management System (DBMS) is a system software that realizes effective organization, management and access of database resources. With the support of the operating system, it supports users' various operations on the database. DBMS mainly includes:

      1. Database establishment and maintenance function

  2. Data definition function

  3. Data operation function

  4. Database operation and management function   5. Communication function

Database System ( Database System DBS) is a person-machine system, generally consisting of hardware, operating system, database, DBMS, application software and database users (including database administrators). Users can operate the database through DBMS or through applications.

An application is an ordered collection of commands compiled using a DBMS to solve a specific management or data processing task. If the application is relatively complete, can provide a friendly human-machine interface, and is compiled into an executable file for distribution, so that ordinary users can learn to use it in a short time without having to have professional computer knowledge, then it is called database application software.

Database Administrator DBA is responsible for database updates and backups, database system maintenance and user management, etc., to ensure the normal operation of the database system. DBA is generally held by personnel with higher professional level and more seniority.

History of database development

According to the development of data models, it can be divided into three stages: the first generation of networked and hierarchical database systems; the second generation of relational database systems; the third generation of object-oriented models. Main features of the database system.

In the 1960s, the hierarchical model and network model database systems were represented by the hierarchical model database management system developed by IBM in 1969 - IMS (Imformation Management System, Information Management System) and the 1970s. The network model proposed by the Database Task Group (DBTG) of the Association for Data Systems Languages ​​(CODASYL).

In the early 1970s, the second generation database-relational database began to appear. Gradually becoming the dominant database and becoming the mainstream of the industry. Relational data systems use Structured Query Language (SQL) as Data Definition Language (DDL) and Data Manipulation Language (DML). It has become the standard language of relational databases since its birth.

Since the 1980s, various new database systems adapting to different fields have continued to emerge, such as engineering databases, multimedia databases, graph databases, intelligent databases, distributed databases, and object-oriented databases.

Classic Data Model

Data is the abstraction of “quantity” in the real world, while the data model (Data Model) is the abstraction of data characteristics. In a database system, the data model is its core and foundation. The data model represents the structure of the data, the operations and constraints defined on it. It describes the static characteristics, dynamic characteristics and constraints of the system from a conceptual level, and provides an abstract framework for the information representation and operation of the database system.

In the development process of DBMS, three classic data models have emerged: network model, hierarchical model and relational model.

The content described by the data model includes three aspects: data structure, data operations and data constraints.

Mesh model

Data records are organized into graphs, and "data structure graphs" are used for abstract analysis and representation. The network model is suitable for expressing complex data relationships and can also reduce data redundancy to a minimum. Its data structure model can intuitively reflect the relationship between data in reality.

Data Operation

The data operation of the network model is a navigational operation based on the relationship chain. The network model is based on graphs and has

data constraints

The data constraints of the network model are scattered and isolated, either scattered in various nodes, or concentrated into a relationship chain, which can easily lead to inconsistency or reduce efficiency. Usually the network model does not specifically implement data constraints, but the application itself implements data constraints.

Hierarchical model

In the hierarchical model, data records are organized in the form of a tree, using a "tree structure diagram".

Data operations

Data operations on the hierarchical model inevitably have the characteristics of the network model - navigation. Such a structure is conducive to improving data query efficiency, but there are still requirements for navigation in data access. Therefore, data operations in hierarchical model databases are still relatively complex.

Data Constraints

The data constraints of the hierarchical model are similar to the network model. Due to the simplification of the structure, the many-to-many and many-to-one relationships in the network model are removed. The data constraints of the hierarchical model can achieve appropriate system implementation. , still depends on the application itself.

The implementation technology of the hierarchical model is superior to the relational model and simpler than the network model, so it has always been the leader. Its representative is IBM's IMS system. This system was once one of the earliest and most widely used databases, and was one of the largest databases in history. Because its developers were the first to deal with issues such as concurrency, recovery, integrity, and efficient queries, Some of these technologies and ideas are naturally applied to DB2, which is the root of DB2's long-lasting prosperity.

Relational model

Data structure. The relational model is based on the theory of relational algebra. The data structure is directly represented by simple and easy-to-understand two-dimensional data tables and "entity-relationship" (E-R) diagrams. The E-R diagram contains entities (data objects), relationships and attributes. Three elements.

Entity: also called an instance, corresponds to an "event" or "thing" that can be distinguished from other objects in the real world.

Entity set: A collection of entities of the same type and sharing the same properties.

Attribute: A certain characteristic of an entity. An entity can have multiple attributes.

Contact: The correspondence between entity sets becomes a contact, also called a relationship.

Data operations. For database users, the relational model operates using a Database Manipulation Language (DML) abstracted from relational algebra. Structured Query Language (SQL) is one of the most important. Its feature is that it is directly oriented to results and simplifies the operation steps, making the design of database applications very simple and easy to understand.

In terms of the physical structure of the database, the relational database system has a simple data structure, strong functions, high data independence, and a solid theoretical foundation. Strict relational databases use two-dimensional database tables as the basic data structure and use simple or complex indexing technology to implement query algorithms. It is relatively simple to implement and facilitates pre-compilation technology to directly convert SQL language into effective data retrieval algorithms.

Data constraints. The data constraints of the relational model can be targeted at entities, attributes of entities, or relationships, and can be fully implemented when defining entities, entity attributes, and relationships. The data definition language (DDL) used by the relational model and the theoretical requirements for the core implementation of the relational database make data constraints easy to implement, but its efficiency is not high.

In general, the relational model has a more solid and complete theoretical foundation than the network model and the hierarchical model. Compared with the hierarchical model and the network model, the relational model is closer to the user, while the network model and the hierarchical model are more closely integrated with the underlying implementation. Such features also make it easier for the relational model to become the choice of commercial database.

Introduction to today’s mainstream databases

SQL Server is a database product of Microsoft. In terms of design, Microsoft SQL Server makes extensive use of the underlying structure of the Microsoft Windows operating system and is directly oriented to users of Microsoft Windows, especially Windows series server operating systems.

Oracle was founded in 1977. In June 2013, Oracle 12C was officially released, enabling enterprises to quickly implement private clouds. Oracle database has become one of the most widely used database systems in the world. Oracle has always been in a leading position in the database field. Not only is the database core excellent, but its related support products are also quite complete and comprehensive. Oracle can adapt to more than 70 operating systems.

DB2, in 1970, Degas Court of IBM proposed the concept of "relational model". He is called the "father of relational database". DB2 supports everything from PC to UNIX, from small and medium-sized computers to mainframes, from IBM to non- IBM (HP and Sun UNIX systems, etc.) various operating system platforms. The best operating environment for the DB2 server is IBM's own operating system platform OS/400.

DB2 database core, also known as DB2 universal server, can run on a variety of operating systems. It has been adjusted and optimized according to the corresponding platform environment in order to achieve better performance.

MySQL is also a relational database management system and has been acquired by Oracle. MySQL runs on the Linux operating system, with Apache and Nginx as web servers, MySQL as the backend database, and PHP/Perl/Python as the script interpreter. All four software are free or open source. Known in the industry as the "LAMP" portfolio.

Basic concepts of relational database

Relational database system is a database system based on the relational model. It is an instantiation of the relational model applied to the database field. Its basic concepts come from the relational model.

The storage structure used by relational databases is multiple two-dimensional tables, and the data descriptions reflecting things and their relationships are embodied in the form of flat tables.

A relational database is composed of associations between data tables, among which:

A data table is usually a two-dimensional table composed of rows and columns. Each data table describes a specific aspect or part of the database. Objects and their properties.

A row in a data table is usually called a record or tuple, which represents one of many objects with the same attributes.

The columns in the data table are usually called fields or attributes, which represent the common attributes stored in the corresponding database.

Primary Key and Foreign Key

Each row of records in the data table must be unique, and identical records are not allowed to appear. By defining the primary key (Primary Key), the uniqueness of the record (entity) can be guaranteed .

Key, that is, keyword, is a very important element in the relational model.

The primary key uniquely identifies the row data in the table, and one primary key value corresponds to one row of data. The primary key consists of one or more fields, the value of which is unique and does not allow null values ​​(NULL). A table can only have one primary key.

If an attribute set can uniquely represent a row of the table without more than one attribute, then this attribute set is called a candidate key. There can be multiple candidate keys in the table, but only one candidate key can be selected as the primary key of the table. The other candidate keys are called alternate keys.

Foreign Key

A relational database usually contains multiple tables, and these tables can be related through foreign keys (ForeignKey).

A foreign key is one or more columns used to establish and strengthen a link between data in two tables. You create a link between two tables by adding one or more columns of primary key values ​​from one table to another table. This column is called the foreign key to the second table.

"Master table" and "Slave table" always appear in pairs, and are related to each other by "foreign keys".

Data integrity rules

Data and update operations in relational databases must comply with four types of integrity rules:

1. Entity integrity rules

Entity integrity rules require that tuples in a relationship must not have null attributes in the primary key value. If a null value occurs, the primary key value cannot uniquely identify the tuple.

2. Domain integrity rules

Domain integrity is also called column integrity, which specifies whether a data set is valid for a certain column or determines whether null values ​​are allowed.

3. Referential integrity rules

If two tables are related to each other, then referential integrity rules require that references to non-existent tuples are not allowed.

4. User-defined integrity rules

User-defined integrity rules are constraints for a specific data and are determined by the application environment. It reflects the semantic requirements that the data involved in a specific application must meet. The system provides mechanisms to define and verify this type of integrity so that it can be handled in a unified system approach, without this work being left to the application.

Entity integrity rules and domain integrity rules are required by relational database standards to be implemented at the core layer of the database system.