Basic Theory of SQL Server

  1 . SQL syntax

  1.1 database tables

  A database usually contains one or more tables. Each table is identified by a name, that is, the table name. A table contains records (rows) with data. The first row indicates that the header of each column is generally the field name, which is the Content Overview of each column. As shown in the preceding figure, the table name is DBO. gdbz. It has 6 records and contains the following fields: Guodian standard document No., Wanfang data Standard No., version nature, and Chinese name.

  1.2 SQL statements

 Most of the work performed on the database is completed by SQL statements, which are not case sensitive.

 A semicolon is a standard method used to separate each SQL statement in the database system. Some programs must strictly abide by it, but it is unnecessary because I use SQL Server 2005.

 

SQL 1.3DML and DDL

SQL is divided into two parts: data operation language (DML) and Data Definition Language (DDL ).

SQL (Structured Query Language) is the syntax used to execute queries. However, the SQL language also contains the syntax used to update, insert, and delete records. The query and update commands constitute the DML part of SQL, as shown below:

 Select-obtain data from a database table

 Update-update data in the database table

 Delete-delete data from a database table

 Insert into-insert data to a database table

 

The Data Definition Language (DDL) Section of SQL enables us to create or Delete tables. We can also define indexes (KEYS), define links between tables, and apply constraints between tables. The most important DDL statements in SQL:

 

Create Database-create a new database

Alter database-modify Database

Create Table-create a new table

Alter table-Change Database Table

Drop table-delete a table

Create index-create index (search key)

Drop index-delete an index

3. Relational Database Modeling

 3.1 simulate reality

Data Modeling Process:

(1) Mobile Phone observation results and requirements

(2) logical representation of the real world

(3) identification and design of visible entities

(4) Architecture Design (supporting and supporting entities)

(5) Application Structure Design

 

3.2 visible entities

Visible entities are usually the objects that most people can recognize.

(1) Each tuple (ROW) is independent.

(2) primary key

(3) tables, rows, and columns

 

3.3 recognize multiple entities

(1) Multiple objects(2) Relationship between objects (3) organization objects(4) Consistent search Value(5) complex objects

 

3.4 link mode

Link uses the key to associate objects, including the following two main attributes:

Base Number: number of objects that may exist at each end of the link

Optional: The link is optional and mandatory.

(1) auxiliary entities and foreign keys

When two objects are associated with each other, one entity is usually the primary entity, and the other entity is the secondary entity. An object in the primary object is associated with multiple objects or tuples in the Secondary object.

(2) relational Base

The number of tuples at each end of the link. The primary key of a link can have only one tuples, while the foreign key can have multiple tuples.

(3) Availability of links: optional and mandatory relationships are very important to the integrity of the database.

(4) Draw a data model Diagram

The method for drawing the Chen ER diagram is very simple, just use sisio.

(5) link types

One-to-multipleOne-to-oneSuperclass/subclassMany-to-many

(6) category entity

It is also called searching a table,

(7) Self-inverse relationship

Establish relationships between two entities of the same type, such as the parent-child relationship between people and the relationship between family members.

 

3.5 Standardization

Normalization is a tool to reduce update exceptions.

(1) Design Principles of entities/attributes:

Each database entity must describe a "transaction"

There must be a one-to-one relationship between properties and transactions.

Each attribute must describe its entity rather than its related entity.

(2) paradigm

Normalization is usually defined in the form of a paradigm. Each paradigm describes possible errors when designing entities and attributes, and provides rules to correct such errors. Therefore, the paradigm is similar to the syntax rules of the written language.

The following table lists the advantages of a standardized database over a flat database:

Eliminate duplicate Storage

Reduce lock contention

Zoom out files

Data modelers should first design the logical architecture according to the third paradigm.

 (3) first paradigm

Qualified

A each data unit is represented by a scalar attribute. A Scalar Value refers to a value that can be represented by a point on a ruler.

B. All data must be represented by unique attributes. Each attribute must have a unique name and purpose.

C. All data must be represented by unique tuples.

(4) Second paradigm

Make sure that each attribute is an object attribute. Each attribute must have a primary key. If other attributes only depend on one of the primary key attributes and do not depend on the other attributes of the primary key, this is called partial dependency, which violates the second paradigm.

(5) third paradigm

Check transfer dependencies. The similarities between passing dependencies and partial dependencies are that they are worth being that attributes are not completely dependent on primary keys. When a depends on B and B depends on the primary key, the transfer dependency occurs.

Like the second paradigm, the solution is to move non-dependent attributes to new entities.

* If the entity has a good primary key, and each attribute is a scalar and fully dependent on the primary key, the logical design conforms to the third method, and most database designs satisfy the third paradigm.

(6) bcnf paradigm

It is located between three and four paradigms and is used to deal with the problem that the entity has two groups of primary keys. Bcnf stipulates that in this case, the entity should be split into two entities, each containing a primary key.

(7) fourth paradigm

It is used to deal with complex problems caused by primary keys. If two independent attributes are combined into a primary key, there must be a third attribute. If there is no third attribute, if only two attributes cannot uniquely identify an object, the fourth paradigm is violated.

(8) Fifth paradigm

It is used to design complex relationships involving multiple (more than three) entities. If the design is correct, a ternary relationship can be designed, where the base of each link can be one or more.

 

3.6 relational algebra

Relational algebra consists of eight Relational operators.

(1) restriction: Return rows that meet the specified conditions.

(2) Projection: return a specified column from the dataset

(3) Product: relational multiplication, which returns all possible data sets of two datasets.

(4) Union: link addition and subtraction: stack one table vertically to another and merge the two tables.

(5) intersection: returns the rows in both tables.

(6) difference set: return the special rows in a dataset.

(7) join: return the horizontal merge results of the two tables. The merge results are matched based on all rows.

(8) Division: returns the exact matching content of the two datasets.

In addition, SQL Server also develops the following features:

Subquery

In relational algebra, the following terms are used:

A table or dataset is called a link or entity.

A row is called a tuples.

A column is called an attribute.

From http://blog.sina.com.cn/s/blog_5f0493880100woe9.html