One of MSSQL database system introduction and SQL Server 2008 overview

Objective

The full name of SQL is the Structured Query Language (structured Query Language), which is the most commonly used language in a relational database. Not only can SQL manage the data in the database, but it can also manage the relational database itself. In order to avoid the SQL syntax incompatibility between the database products, the SQL-92 standard is developed by ANSI (American National standards Institute, the United States Bureau of Standard), and most DBMS products currently support this standard.

The goal of this course is to enable students to master Structured Query Language (SQL), with the ability to query and manage databases.

The main reader object of this book

Beginner of sql: This book is based on a wide range of SQL Server 2008 databases, in the general Order of database operations, from shallow to deep introduction of the Transact-SQL language, and in the interpretation of the theory and supplemented by a large number of instances, It is easy for SQL novice to understand and learn SQL.

SQL Server Database Beginners: When learning SQL Server databases, users are generally accustomed to using their graphical development management interface, created to manage various objects. In fact, the operation of the graphical interface is ultimately transformed into the corresponding Transact-SQL statement to manipulate the database. Therefore, it is recommended that users, when learning SQL Server databases, try to manipulate the database through the Transact-SQL language described in this book, which facilitates the user's deep understanding and deep development of the SQL Server database.

SQL Server Database Developer: This book can be used as a reference manual for SQL Server database developers in the database development process. Covers almost all common statements of Transact-SQL and facilitates quick-check.

Chapter One Introduction to Database system Overview with SQL Server 2008

The development of database system has undergone three stages of hierarchical model and network model database system and relational model database system. The most widely used database system is still a relational database system, which represents products such as SQL Server, Oracle, DB2 and so on. The relational database system queries and accesses data in the database mainly through the SQL (Structured query Language) language. This chapter will focus on some of the basics of database systems and SQL languages, laying the groundwork for learning later chapters Focus ? Basic terminology for Database systems ? How a relational database system stores data ? SQL Server 2008 Tools Preview lessons ? What is DBS, DBMS ? What is a DBA ? The difference between DBS and DBMS ? What is SSMs ? What are relationships, tuples, attributes, primary keys

Introduction to Database Systems

The database system is actually a warehouse that the computer stores the data, the system user is us, can carry on a series of operation to these stored data.

? Basic terminology

1. Data (Date)

A computer's symbolic record describing things is called data. Data is the basic object stored in the database, there are many kinds of data, mainly including text, graphics, sound, students ' records, the transport of goods, etc., they can be digitized processing and deposited into the computer.

2. Databases (database, abbreviated DB)

Database, as the name implies, is the storage of data Warehouse, in the past people put data in the cabinet, now people use computer and database technology, scientific preservation and management of a large number of complex data, so as to facilitate the full use of these valuable information resources.

The so-called database is a long-term storage in the computer, organized, shareable data collection, the data in the database according to a certain data model organization, description and storage, with a small redundancy, high data independence and scalability, and can be shared by a variety of users.

3, Database management system (Databasemanagement system, referred to as DBMS)

Database management system refers to the software system of data management in database system, which is the core component of database system. All operations of the database system, including queries, updates, and various controls, are carried out through the DBMS.

DBMS is always based on some kind of data model, so it can be regarded as the concrete implementation of some data model on computer system.

Database management system is an important part of database system, it provides users with a convenient and effective access to database information environment.

4, database System (Databasesystem, referred to as DBS)

Database system refers to the system after the introduction of the database in the computer, which is usually composed of database, database management system (and its development tools), application system, database administrator and user. It should be noted that the establishment, use and maintenance of the database is not limited to one DBMS, but also dedicated personnel, who are known as database administrators (DB Administrator, DBA)

? Technology development

The database is primarily used for data processing. With the increasing of data processing quantity, the data management technology comes into being, its evolvement process along with the development of computer hardware and software, it has gone through several stages of file system, hierarchical model and network model database, relational model database and new generation database system.

1. File system phase

Before the database system appears, the data is separated from the computer program and has the unified management of the operating system. The user's program and data can be stored on the external storage separately, each application can share a set of data, realize the sharing in the file unit.

Because the organization of data is still program-oriented, there is a lot of data redundancy, and the logical structure of data cannot be easily modified and expanded. At the same time, because the documents are isolated from each other, they do not reflect the connection between things in the real world.

2. Hierarchical model and network model database system

Hierarchical model and mesh model database system also become the first generation database. This kind of database system is based on the graph, storing the data one at a time, using the process access method. This book does not involve much knowledge in this area.

3. Relational model Database system

The relational database system is also called the second generation database, and the relational database system is based on the set algebra, and the data is accessed at one time by a non-procedural access method. Currently widely used database software is based on relational model of relational database management system. Relational model is the most widely implemented model of modern database products, and it is the relational model that forms the foundation of SQL. The relationship model is built on the basis of strict mathematical concepts. The logical structure of data in a relational model is a chapter two-dimensional table.

Relationship (Relation): A relationship corresponds to a table that is usually said.

L tuples (): a single row in a table is a tuple.

L Property (Attribute): A column in a table is an attribute, giving each property a name that is the property name.

An attribute group in the primary key (key) table that uniquely identifies a tuple.

L Domain: The value range of the attribute

L Relationship Pattern: a description of the relationship such as: Relationship name (attribute 1, attribute 2 、..., attribute N)

The relationship model requires that the relationship must be normalized, that is, the requirement relationship must meet certain normative conditions, the most basic of which is that each component of the relationship must be an irreducible data item, that is, the table is not allowed.

4. New Generation Database System

With the advent and development of CAD, CAM, GIS, Knowledge base system and real-time system in new field, higher requirements for database system are put forward. such as the storage and processing of complex objects, support complex data types, the management of resident memory objects and support the access and computation of a large number of objects, the implementation of programming language and database language seamless integration, support long transaction processing and so on. The traditional database system because of its own limitations is difficult to achieve the above functions, therefore, a new generation of database technology, that is, the concept of a third-generation database system.

The third generation database system has a new development to the data model. Database technology and other related technologies, such as the combination of distributed processing technology, the emergence of a distributed database, and object-oriented technology, the emergence of object-oriented database technology. In addition, the database technology is applied to the specific domain, there are many new domain-oriented database technologies, such as engineering database, statistic database and spatial database, which are applied in the fields of CAD, CAM and CIM.

In a word, the third generation database system number is in the development stage, and its research goal is to propose a unified database system supporting various application fields.

SQL Server 2008 Overview

In this section you will focus on creating a database, a table for the integrated development environment provided by SQL Server 2008.

? version of SQL Server 2008:

SQL Server 2008 introduces many versions, and the higher the version, the more features are available. This is based on the order from the high-end to the low-end:

1. SQL Server 2008 Enterprise Edition ()

2. SQL Server 2008 Developer Edition ()

3. SQL Server 2008 Standard Edition ()

4. SQL Server 2008 Workgroup Edition ()

5. SQL Server 2008 Lite ()

? SQL Server Management Studio

SQL Server Management Studio, called SSMs, is an integrated development environment that is provided to us by Server 2008, and we can easily do many things with ssms, such as creating a database, creating a table in a database, and so on. How to open this development environment, very simple: click Start | Program "|" Microsoft SQL Server "|" SQL Server Management Studio option launches the Managementstudio tool.

? Transac-sql Language Introduction

1. Data Definition language

Data definition Language (DDL) refers to statements that define and manage databases and the various objects in the database, including create, alter, and drop. In SQL Server 2008, database objects include tables, views, triggers, stored procedures, rules, user-defined data types, and so on. The creation, modification, and deletion of these objects can be accomplished by using statements such as Create,alter,drop.

2. Data Manipulation language

Data manipulation Language (DML) refers to statements that query, add, modify, and delete data in a database, including Select, INSERT, UPDATE, delete, and so on

3. Data Control Language

The Data Control Language (DCL) is a statement that sets or changes the permissions of a database user or role, including statements such as Grant,deny,revoke. By default, only people such as Sysadmin,dbcreator,db_owner or db_securityadmin have the power to enforce the data Control language.

4. System stored Procedure

A system stored procedure is a stored procedure created by a SQL Server system that is designed to easily query information from a system table, or to complete administrative tasks or other system administration tasks related to updating a database table. System stored procedures can be executed in any database. The system stored procedure is created and stored in the system database master, and the name begins with sp_ or xp_. Some system procedures can only be used by system administrators, and some system procedures can be used by other users through authorization.

Practical issues

1. What is a DBMS?

2. Describe the characteristics of relational model database system

3. A brief overview of the "tuple" concept

4. Create a database with SQL Server Management Studio Tools

5. Use SQL Server Management Studio to create a data table in the database you just created, and fill in the table with more than three data

6. What is DML language

Summary

In this chapter, you have learned:

1, the basic terminology in the database. such as: Database system of database management system of data database

2. Basic knowledge of relational model database

3. SQL Server 2008 Operator Interface

SQL Servermanagement Studio (SQL Server Management Studio)

SQL Serverbusiness Intelligence Management Studio (SQL Server Business Intelligence Management Studio)

Databaseengine Tuning Advisor (Database Engine tuning recommendation)

SQL Serverconfiguration Manager (SQL Server Configuration Manager)

4. Transac-sql language

Data Definition language

Data Manipulation language

Data Control Language

System stored Procedures

5. Create a database with SQL Server Management Studio

6. Create data tables, modify tables, view tables using SQL Server Management Studio

One of MSSQL database system introduction and SQL Server 2008 overview