Introduction to SQL Server databases

Using a database allows you to store data efficiently and coherently, enabling people to manage data more quickly and easily. The database has the following characteristics:

1, can be structured to store a large amount of data information, convenient for users to effectively search and access.

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

3, can meet the sharing and security requirements of the application.

Basic concepts of the database:

1, data: The symbol of the description of the record becomes dataincluding numbers, text, graphics, images, sounds, records, etc. are data. The data is stored in a uniform format as a "record" form.

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

Database management System ( Database Management system, DBMS,is the software that realizes the effective organization, management and access to the resources of the databases. With the support of the operating system, it supports the user's operations on the database. The DBMS mainly includes:

1, the establishment of database and maintenance functions

2. Data definition function

3. Data Operation function

4. Operation and management function of database

5. Communication function

Database System ( Database System DBSis a human-to-computer system, typically composed of hardware, operating systems, databases,DBMS, application software, and database users (including database administrators). Users can manipulate the database through the DBMS, or through the application.

applications are using Aset of ordered sets of commands that are compiled to address a specific task of management or data processing. If the application is perfect, can provide a friendly human-machine interface, and compiled into executable file distribution, so that ordinary users do not need to have computer expertise, in a relatively short time to learn to use, then called the database application software.

Database Administrator ( Database Administrator DBAis responsible for updating and backing up databases, maintaining user management of database systems, and ensuring the normal operation of the database system. A DBA is generally held by a person with a higher level of service and seniority.

History of Database

According to the development of data model, it can be divided into three stages: first generation network, hierarchical database system, second generation relational database system, third generation database system with object-oriented model as main feature.

20 century 60 age, hierarchical model and network model of the database system, the representative of the database is 1969 year ibm The company developed the hierarchical model of database management system-- ims ( imformation Management system 20 century 70 age American Data Systems Language Association ( codasyl) subordinate Database task Group ( dbtg) proposed mesh model.

at the beginning of the century, the second generation database-relational database began to appear. Gradually become the dominant database and become the mainstream of the industry. The relational data system takes a structured Query language (Structured query Language,SQL) as the data definition language (datadefinition Language,DDL ) and Data manipulation language ( Data manipulation Language,DML), which became the standard language for relational databases as soon as it was born.

Since the beginning of the century , a variety of new database systems adapted to different fields have been emerging, such as engineering database, multimedia database, graphic database, intelligent database, distributed database and object-oriented database.

Classic Data Model

data is the abstraction of "volume" in the real world, and the data model ( Data Model) is an abstraction of the characteristics of a dataset. In the database system, the data model is its core and foundation. The data model is represented by the structure of the data, the operation and constraints on which it is defined. It describes the static characteristics, dynamic characteristics and constraints of the system from the conceptual level, which provides an abstract framework for the information representation and operation of the database system.

in the In the development of DBMS , there are three kinds of classical data models, such as network model, hierarchical model and relational model.

The data model describes three aspects: data structure, operations, and data constraints.

Mesh model

data records are organized into diagrams, using "data structure diagrams" for abstract analysis and representation. The mesh model is suitable for expressing the implementation of complex data relationships, or minimizing data redundancy. Its data structure model can directly reflect the relationship between the real world.

Data manipulation

the data manipulation of the mesh model is a navigation operation based on the relationship chain. The mesh model is based on graphs,

Data constraints

The data constraints of a mesh model are fragmented or scattered across nodes, or centralized into a chain of relationships, which can lead to inconsistencies or inefficiencies. Typically, a mesh model does not implement data constraints, and the application itself implements data constraints.

Hierarchical model

in a hierarchical model, data records are organized into tree forms, using the tree chart.

Data manipulation

The data operation on the hierarchical model inevitably has the characteristic of the mesh model--navigation. This kind of structure is helpful to improve the efficiency of data query, but there is still a need to navigate the data access, so the database of hierarchical model is still more complicated in data operation.

Data constraints

the data constraint of the hierarchical model is similar to the mesh model, because of the simplification of the structure, the relationship between many-to-many and many-to-one in the mesh model is removed, and the data constraints of the hierarchical model can be implemented properly or by the application itself.

the implementation of the hierarchical model is superior to the relational model, which is more simple than the mesh model. Its representative is the IMS system of IBM Corporation . The system was once one of the oldest and most widely used databases, historically one of the largest, because its developers were the first to start dealing with issues such as concurrency, recovery, integrity, and efficient querying, some of which were naturally applied to DB2 , which was DB2 the roots of longevity.

Relational model

data structures. The relational model is based on the theory of relational algebra, the data structure is applied to the simple and understandable two-dimensional data table, the "entity-relation" (e-r) graph to direct,the E-r diagram contains the entities (data Objects), relations and attributes of three elements.

entities: Also known as instances, correspond to "events" or "things" in the real world that can be distinguished from other objects.

Entity set: A collection of entities that have the same type and share the same nature.

Property: An entity has an attribute that can have more than one attribute.

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

data manipulation. For users of the database, the relational model operates using Database manipulation language (DML) that is abstracted from relational algebra. Structured Query Language (SQL) is one of the most important. It is characterized by direct results-oriented, simplifying operational procedures, making the design of database applications very easy to understand.

for the physical structure of the database, relational database system has simple data structure, strong function, high data independence and solid theoretical Foundation. Strict relational database with two-dimensional database table as the basic data structure, the use of simple or complex index technology to implement the query algorithm, it is relatively simple to achieve, but also facilitates the pre-compilation technology to direct the SQL language into an effective data retrieval algorithm.

data constraints. The data constraints of a relational model can be for entities, for attributes of entities, and for relationships, and can be fully implemented when defining entities, entity properties, and relationships. The Data definition language (DDL) and relational model used in the relational model are theoretically required for the core implementation of the relational database, so that data constraints can be easily implemented, but his efficiency is not high.

In sum, the relational model has a more solid and complete theoretical foundation than the mesh model and the hierarchical model. The relational model is closer to the user than the hierarchical model and the mesh model, while the mesh model and the hierarchical model are more closely integrated with the underlying implementation. This feature also makes it easier to make a relational model a choice for commercial databases.

Introduction to today's mainstream databases

SQL Server is a database product for Microsoft Corporation. In design,Microsoft SQL Server leverages the underlying structure of the Microsoft Windows operating system to directly target Microsoft Windows, especially users of the Windows family server operating system.

  Oracle company was founded in 1977 year 6 month, Oracle 12c The database becomes one of the world's most widely used database systems. oracle company has been in the database field has been in a leading position, not only the database core is very good, and its related support products are quite perfect and comprehensive. oracle can adapt 70 multiple manipulation systems.

db2 1970 year , Ibm Dega Cauter of the company proposed " The concept of . He is called the "the parent of the relational database ". Db2 support from Pc to Unix, from ibm to non IBM (hp< Span style= "font-family: ' The song Body '; > and Sun unix system, etc. ) various control system platforms. db2 best operating environment on server side or ibm own operating system platform os/400

DB2 Database Core, also known as DB2 Universal Server, can be run with a variety of operating systems, it is based on the corresponding platform environment has been adjusted and optimized to achieve better performance.

MySQL is also a relational database management system that has now been acquired by Oracle . MySQL runs on top of Linux operating system,Apache and Nginx as Web server,MySQL as background database, Php/perl/python as the script interpreter. These four software are free or open source. The industry is called the "LAMP" combination.

Basic concepts of relational databases

relational database system is a database system based on relational model. Is the instantiation of the relational model applied to the database domain. Its basic concept comes from the relational model.

A relational database uses a storage structure that is a two-dimensional table that reflects the data representation of things and their connections in a flat form.

Relational databases are made up of associations between data tables, where:

A data table is usually a two-dimensional table of rows and columns, each of which describes a particular aspect or part of an object in the database and its properties.

rows in a data table are often called records or tuples, which represent one of many objects with the same properties.

a column in a data table is often called a field or property, which represents a common property that is stored in the corresponding database.

Primary key and foreign key

each row of records in a data table must be unique, not the exact same record, by defining the primary key (the primary key, Primary Key) ensures the uniqueness of the record (entity).

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

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

If a property set can uniquely represent a row of a table and does not contain more than one property, then this property set is called a candidate key. There can be multiple candidate keys in a table, but only one candidate key can be selected as the primary key for the table, and the other candidate keys are called alternate keys.

FOREIGN key

A relational database typically contains multiple tables, with foreign keys ( ForeignKey) can make these tables associated.

A foreign key is one or more columns that are used to establish and strengthen links between two table data. You can create a link between two tables by adding one or more columns of the primary key value in the table to another table. This column is called a foreign key for the second table.

The "primary table" and "from table" are always paired, and are associated with "foreign keys" to each other.

Data integrity rules

the data and update operations of a relational database must follow four complete rules:

1. Entity integrity rules

entity integrity rules require that tuples in a relationship not have null values on the primary Key's properties. If a null value appears, the primary key value does not uniquely identify the tuple.

2. Domain Integrity rules

domain integrity, also known as column integrity, specifies whether a dataset is valid for a column or determines whether null values are allowed.

3. Referential integrity rules

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

4. User-defined integrity rules

A user-defined complete rule is a constraint on a specific data that is determined by the application environment. It reflects the semantic requirements that the data involved in a particular application must meet. The system provides a mechanism for defining and verifying such integrity so that it can be processed using a unified system approach, which is no longer for the application to undertake this work.

Entity integrity rules and domain integrity rules are the relational database standards that are implemented at the core level of the database system.

This article from the "11637266" blog, reproduced please contact the author!

Introduction to SQL Server databases