Bài giảng Hệ cơ sở dữ liệu: Giới thiệu về cơ sở dữ liệu (Chương 1)

CHAPTER 1

Giảng viên: Trần Thị Kim Chi

What is a Database

1

2 What is Database Management System

Introduction SQL Server

3 SQL Server Services & Tools

4 Introduction about SQL Server 2008

5 SQL Server 2008 Database - Object

6 Questions

7

1 Data and Information

Data (dữ liệu) và information (thông tin)

•

• Data is raw, unorganized facts that need to be processed. Data are any facts, numbers, or text that can be processed by a computer. Information – When data is processed, organized, structured or presented in a given context so as to make it useful, it is called information

XỬ LÝ

DỮ LIỆU (DATA)

THÔNG TIN (INFORMATION)

1

Data and Information

10273 00298

CDTH7 CDTH7

20 19

Nguyễn Văn Hoà Nguyễn Minh Tâm

Dữ liệu 1 2 … 151 152

50542 50075

TCTH33 CNTH34

18 20

Hồ Xuân Phương Lê Việt Dũng

Mã sinh viên Họ và tên sinh viên 10273 00298

Lớp CDTH7 CDTH7

Tuổi 20 19

Nguyễn Văn Hoà Nguyễn Minh Tâm

Thông tin: dữ liệu trong ngữ cảnh STT 1 2 … 151 152

50542 50075

TCTH33 CNTH34

18 20

Hồ Xuân Phương Lê Việt Dũng

Data (dữ liệu) và information (thông tin)

1 Data Model

 A data model is a “description” of both a container for data and a methodology for storing and retrieving data from container.  Data model is  Not a thing  You cannot touch it  Data model are abstractions, mathematical algorithms & Concepts.

1

What is a Database

• A database a set of data that has a regular structure and that is organized in such a way that a computer can easily find the desired information.

Application Program 1

Database

Application Program 2

Application Programs

Users

1

Database System Model

1. The way it stores data :

STRUCTURE

Database management systems follow particular models (known as database models) to store and manipulate data. A database model is characterized by:

structure are manipulated: OPERATIONS

2. The way data in the

1

Characteristics of the Datatabase

– Should be able to store all kinds of data that exists in this real

• Persistent:

•

world. Interrelated: – Data and application should be isolated. – Should be able to relate the entities / tables in the database by

means of a relation. i.e.; any two tables should be related.

• Shared:

– Multiple users should be able to access the same database, without

affecting the other user.

– Database should also provide security, – Database should also support ACID property. i.e.; while performing any transactions like insert, update and delete, database makes sure that the real purpose of the data is not lost.

1 Database Design

 Objective

 Ensuring data completeness  Avoiding data redundancies  Facilitating data access

 Solution

 Normalization

• Reducing redundancies and improving data modification

performance

• Renormalizations is often done to enhance reporting

performance (at the expense of disk space and redundancy)

• Maintains the logical relationships between database objects

 Referential Integrity

1

Steps in Database Design

 Requirement analysis

What does the user want?  Conceptual database design

Defining the entities and attributes, and the relationships between these The ER model

 Physical database design

Implementation of the conceptual design using a Database Management System

1 ER model & Relational Data Model

Relational Database

Entities: Student (Id, Name, Suburb) Subject (No, Name, Dept)

Relationships:

Student

Takes

Subject

ER models view the world by entities and relationships Relational models view the world by relations

1 ER model & Relational Data Model

Entities

Tables

Attributes

Fields

Relations

1 The Relational Database

Student

Id

Name

Suburb

1108

Robert

Kew

3936

Glen

Bundoo ra

8507

Norma n

Bundoo ra

8452

Mary

Balwyn

• “A DBMS that manages data as collection of tables in which all data relationships are represented by common values in related tables.”

•“A DBMS that follows all the twelve rules of CODD is called RDBMS”

Takes SID

SNO

1108

8507

1 The Relational Database

Student

• Table: information about a

Id

Name

Suburb

1108

Robert

Kew

3936

Glen

• Primary

key:

(set

Bundoo ra

single entity

of) uniquely

8507

that

Norma n

Bundoo ra

8452

Mary

Balwyn

•

key:

(set

of) Foreign column(s) used to link table together

column(s) identifies a record.

Takes SID

SNO

1108

8507

1 TABLE Structure

Primary Key

Supplier

Domain Domain

SCode SName Quantity City

Kamran

Lahore

S1

20

Tuples

Cardinality

S2 10 Zafar Islamabad

S3 40 Azmat Karachi

S4

34 Abdul

Lahore

S5 25 Nasir Islamabad

Attributes

1 The Relational Database

LOP

SINHVIEN

MALOP

TENLOP

SISO

TCTHA

TCTH32A

MASV

TEN

MALOP

TCTHB

TCTH32B

TCTH01

TCTHA

Sơn

TCTHC TCTH32C

TCTH02

TCTHB

Bảo

KETQUA

TCTH03

Trang

TCTHA

MASV

MAMH DIEM

MONHOC

TCTH01

THVP

MAMH

TENMH

TINCHI KHOA

TCTH01

CSDL

THVP

Nhập môn TH 4

CNTT

TCTH01

CTDL

TCTH02

THVP

CSDL

CNTT

Cấu trúc dữ liệu

TCTH02

CSDL

CTDL

Toán rời rạc

TOAN

TCTH03

THVP

16 10

1 Example: TABLE Structure

CSDL gồm các bảng sau :

Ví dụ: Để quản lý hóa đơn bán hàng ta cần xây dựng một

• SanPham: Mô tả các thông tin về sản phẩm như Masp,

• KhachHang: Mô tả các thông tin về khách hàng như

Tensp, Donvitinh, DongiaMua, Slton.

Makh, Tenkh, Diachi, DienThoai.

• Nhanvien: Mô tả các thông tin về nhân viên như Manv, Honv, Tennv, Phai, Ngaysinh, Diachi, DienThoai, Hinh. • Hoá đơn: Mô tả các thông tin về hóa đơn như Mahd, LoaiHD, Makh, Manv, NgaylapHD, NgayGiaoNhanHang, DienGiai.

• Chitiethoadon: Mô tả các thông tin chi tiết của từng hóa

đơn như Mahd, Masp, Soluong, DongiaBan.

1 Example: TABLE Structure

1 Exercise

Bài tập

Việc quản lý nhân viên của một công ty như sau: •

Công ty gồm nhiều nhân viên, mỗi nhân viên có một MANV duy nhất, mỗi MANV xác định Hoten, Phai, NgaySinh, Phongban. Mỗi nhân viên chỉ do một phòng ban quản lý.

• Mỗi phòng ban có một MAPB duy nhất, mỗi MAPB xác dịnh TenPB, DiaDiem, MaTP (Mã người phụ trách), KPHD(Kinh phí hoạt động), DT (Doanh thu của từng phòng ban).

• Nhân viên được chia làm hai loại nhân viên. Nhân viên hành chánh và nhân viên tham gia sản xuất. Nhân viên hành chánh làm việc theo giờ hành chánh. Nhân viên tham gia sản xuất thì làm nhiều công việc khác nhau.

• Mỗi công việc có một mã MACV, Macv xác định TENCV, DONGIA. Công ty có tối đa

3 công việc.

• Hàng tháng công ty chấm công cho nhân viên để tính lương cho nhân viên như

sau: – Nhân viên hành chánh chấm theo số ngày làm trong tháng. Mỗi nhân viên một tháng phải làm tối

thiểu 25 ngày công. Lương tháng được tính theo công thức: SoNC*LCB +Thuong

– Nhân viên sản xuất thì sẽ chấm công theo số lượng của mỗi công việc trong một tháng. Mỗi nhân viên phải làm ít nhất một công việc trong một tháng. Mỗi công việc số lượng nhân viên làm phải lớn hơn 10. Lương tháng được tính theo công thức: Tổng SoLuong*Dongia của mỗi công việc +Thuong 19

1

Exercise

Bài tập

Câu hỏi: • Xây dựng mô hình thực thể kết hợp gồm: Xác định các thực thể, thuộc tính, khóa, các mối kế hợp và vẽ mô hình thực thể kết hợp

• Chuyển mô hình thực thể về lược đồ cơ sở dữ liệu

quan hệ và xác định các khóa

• Xác định các mối quan hệ và các ràng buộc của lược

đồ cơ sở dữ liệu trên

1

Advantages of Database

Advantages • Reduced data redundancy • Reduced updating errors and increased consistency • Greater data integrity and independence from

•

applications programs Improved data access to users through use of host and query languages Improved data security

• • Reduced data entry, storage, and retrieval costs • Facilitated development of new applications program

1

Disadvantages of Database

Disadvantages • Database systems are complex, difficult, and time-

consuming to design

• Substantial hardware and software start-up costs • Damage to database affects virtually all applications

programs

• Extensive conversion costs in moving form a file-based

system to a database system Initial training required for all programmers and users •

2 DBMS – DataBase Management System

• DataBase Management System (DBMS)

to create,

– A database management system (DBMS) is system software for creating and managing databases. The DBMS provides users and programmers with a retrieve, update and systematic way manage data.

2 DBMS – DataBase Management System

Components of Database System

• Users : Users may be of various type such as DB administrator, System developer and End users.

may

• Database application : Database application Personal, Departmental, Enterprise and Internal • DBMS : Software that allow users to define, create and manages database access, Ex: MySql, Oracle etc.

• Database : Collection of logical data.

2 DBMS – DataBase Management System

CHARACTERISTICS OF THE DATABASE MANAGEMENT SYSTEM • Data Independence: The DBMS provides an abstract view of the

data that hides such details.

• Efficient Data Access: A DBMS utilizes a variety of sophisticated

• Data Integrity and Security: If data is always accessed through

techniques to store and retrieve data efficiently.

the DBMS, the DBMS can enforce integrity constraints.

2 DBMS – DataBase Management System

CHARACTERISTICS OF THE DATABASE MANAGEMENT SYSTEM • Data Administration: When several users share the data,

Experienced professionals who understand the nature of the data being managed, and how different groups of users use it, can be responsible for organizing the data representation to minimize redundancy and for fine tuning the storage of the data to make retrieval efficient.

• Concurrent Access and Crash Recovery: A DBMS schedules

concurrent accesses to the data in such a manner that users can think of the data as being accessed by only one user at a time. Further, the DBMS protects users from the effects of system failures.

2 DBMS – DataBase Management System

Functions of DBMS • Provides data Independence • Concurrency Control • Provides Recovery services • Provides Utility services • Provides a clear and logical view of the process that

manipulates data.

2

Architecture of DBMS

Quan hệ

2

• Database (Data) Tier:

the database resides along with its query processing languages. We also have the relations that define the data and their constraints at this level.

• Application

Architecture of DBMS

Tier:

reside

(Middle)

the database. For a user,

Quan hệ

the application server and the programs that this access application tier presents an abstracted view of the database. The application layer sits in the middle and acts as a mediator between the end-user and the database.

• User (Presentation) Tier − End-users operate on this tier and they know nothing about any existence of the database beyond this layer. At this layer, multiple views of the database can be provided by the application. All views are generated by applications that reside in the application tier.

2 DBMS – DataBase Management System

SERVICES OF DBMS

• Data Storage Management • Data Manipulation Management • Data Definition Services • Data Dictionary/System Catalog Management • Database Communication Interfaces • Authorization / Security Management • Backup and Recovery Management • Concurrency Control Service • Transaction Management • Database Access and Application Programming Interfaces

2 Types of DBMS

Ingres (cơ sở dữ liệu)

Phần mềm miễn phí hoặc nguồn mở • Cloudscape • Firebird • HSQLDB • • MaxDB • MonetDB • MySQL Quan hệ • PostgreSQL • SQLite •

tdbengine

Phần mềm có bản quyền • • • • • • • • • • • • • • • • • • • • • •

4th Dimension ANTs Data Server Dataphor Daffodil database DB2 FileMaker Pro Informix InterBase Matisse [1] Microsoft Access Microsoft SQL Server Mimer SQL NonStop SQL Oracle Sand Analytic Server (trước đây là Nucleus) SmallSQL [2] Sybase ASA (trước đây là Watcom SQL) Sybase Sybase IQ Teradata ThinkSQL [3] VistaDB [4]

2 DBMS – DataBase Management System

Advantages of a DBMS

• Segregation of applicaion program. • Minimal data duplicacy. • Easy retrieval of data. • Reduced development time and maintainance need.

2 DBMS – DataBase Management System

DisAdvantages of a DBMS

• Complexity • Costly • Large in size

2 Relational Database Management System

• RDBMS stands

for Relational Database Management System. RDBMS is the basis for SQL, and for all modern database systems like MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.

• A Relational database management system (RDBMS) is a

database management system (DBMS) that is based on the relational model as introduced by E. F. Codd.

Quan hệ

Table 2 Key

Table 1 Key

Database

Data..

Data...

2 DBMS vs. RDBMS

DBMS

RDBMS It is based on the concept

Of relationships

The concepts of relationships is missing in a DBMS. If it exits it is very less.

Speed of operation is very slow

Speed of operation is very Fast

Hardware and Software requirements are minimum

Hardware and Software requirements are High

Platform used is normally DOS

Platform used can be any DOS, UNIX,VAX,VMS, etc

Uses concept of table

Uses concept of a file

RDBMS normally use a 4GL

DBMS normally use 3GL

Examples are dBase, FOXBASE, etc Examples are ORACLE, INGRESS,

SQL Server, Access, etc

3 Introduction SQL Server

• SQL stands for Structured Query Language • Microsoft SQL Server is a relational database

management system developed by Microsoft

• The primary query languages: T-SQL and ANSI SQL • SQL Server includes several components • Primary function: store and retrieve data as requested by other software applications on the same computer or on another computer across a network.

2 Introduction SQL Server

SQL Server

Client Application

1

Query

Result Set

Server Net-Libraries

5 Open Data Services

Database API (OLE DB, ODBC, DB-Library)

3

Query

Result Set

Client Net-Library

2 Relational Engine

Client

Server

4 Storage Engine

Processor

Memory

Local Database

2 Introduction SQL Server

Client/Server Architecture

Client

SQL Server

OLTP

Results

OLAP

Query

Client Application

Relational Database Management System

3 Ms SQL Server Components

2 Ms SQL Server Components

• SQL Server Integration Services (SSIS) is a platform for building high performance data integration solutions, including extraction, transformation, and load packages for data warehousing

2 Ms SQL Server Components

• SQL Server Database Engine is the core service for

storing, processing, and securing data – This includes creating tables for storing data, and database objects such as indexes, views, and stored procedures for viewing, managing, and securing data

2 Ms SQL Server Components

• SQL Server Reporting Services (SSRS) includes:

manage reports,

– A complete set of tools that you can use to create and

– An application programming interface (API)

that allows developers to integrate or extend data and report processing in custom applications

2 Ms SQL Server Components

SQL Server Analysis Services : • Analysis Services delivers Online Analytical Processing and Data Mining functionality for Business Intelligence applications.

2 Ms SQL Server Components

SQL Server Service Broker : • Service Broker, internal or external processes can send and receive

guaranteed, asynchronous messages by using extensions to Transact- SQL Data Manipulation Language (DML).

• Messages can be sent to a queue in the same database as the sender, to another database in the same SQL Server instance, or to another SQL Server instance either on the same server or on a remote server.

2 Ms SQL Server Components

• SQL Server Replication is a set of

technologies for copying and distributing data and database objects from one database to another, and then synchronizing between databases to maintain consistency

2 Ms SQL Server Components

• Full – Text Search lets users and applications run full-text queries against character-based data in SQL Server tables. • The full-text index includes one or more character-based

columns in the table.

2 Ms SQL Server Components

• Notification Services is a platform for developing and deploying applications that generate and send notifications to subscribers. The notifications generated are personalized, timely messages that can be sent to a wide range of devices, and that reflect the preferences of the subscriber.

2 Ms SQL Server Tools

• SQL Server Management Studio • SQL Server Configuration Manager • SQL Server Agent • SQL Profiler

2 SQL Server Management Studio

• SQL Server Management Studio (SSMS) is used for all administering and

managing, configuring, components within Microsoft SQL Server – This includes both script editors and graphical tools which work with objects and features of the server

2 SQL Server Configuration Manager

• SQL Server Configuration Manager is a tool provided with SQL Server 2008 for managing the services associated with SQL Server and for configuring the network protocols used by SQL Server.

• Primarily, SQL Server Configuration Manager is used to start, pause, resume, and stop SQL Server services and to view or change service properties

2 SQL Server Agent

• SQL Server Agent is a scheduling tool integrated into

SSMS that allows convenient definition and execution of scheduled scripts and maintenance jobs.

2 SQL Profiler

• The SQL Server Profiler is a GUI interface to the SQL Trace feature of SQL Server that captures the queries and results flowing to and from the database engine.

3 SQL Command

Transa

Implementation of Entry-Level ANSI ISO Standard

• • Composing of 3 categories

– Data Definition Language Statements (DDL)

– Data Control Language Statements (DCL)

– Data Manipulation Language Statements (DML)

3 SQL Command

DDL - Data Definition Language

Command Description

CREATE

Creates a new table, a view of a table, or other object in database

ALTER

DROP

Deletes an entire table, a view of a table or other object in the database.

Modifies an existing database object, such as a table.

3 SQL Command

DML - Data Manipulation Language

Command Description

SELECT Retrieves certain records from one or more tables

Creates a record INSERT

Modifies records UPDATE

Deletes records DELETE

3 SQL Command

DCL - Data Control Language

Command

Description

GRANT Gives a privilege to user

REVOKE Takes back privileges granted from user

3 SQL Server Editions

SQL Server 6.5

SQL Server 7.0

SQL Server 2000

SQL Server 2005

SQL Server 2008

• Secure trusted

• Enterprise-class

• Re-architecture

• Data

• Reliability and

platform for data

scalability

warehousing

• Productive

of relational server

scalability advancements

• Programmability advancements

• First to include

• Internet support

• Deep XML

support

• Data

• Differentiation from Sybase SQL Server

OLAP in database • Auto tuning • Ease-of-use

policy-based management • Optimized and predictable system performance

• End-to-end business intelligence • Manageability • Support for

warehousing • SQL Server CE • 64 bit support

• Dynamic

development

multiple types of data

• Beyond

relational data

Evolution of SQL Server

• Pervasive

Business Insight

Realibility & Security Integrated Business Intelligence

Cross-release objective

Performance and Scalability Automatic Tuning

3 SQL SERVER 2008 R2 EDITIONS.

SQL Server 2008 Standard Edition • This is the version intended for the masses to medium-sized systems who don’t require the performance, scalability, and availability provided by Enterprise Edition.

SQL Server 2008 Enterprise Edition • Provides the most scalability and availability of all editions and is intended for systems that require high performance and availability, such as large-volume websites, data warehouses, and high-throughput online transaction processing (OLTP) systems.

intended for server-based deployment of applications

Other SQL Server 2008 Editions • The Standard and Enterprise Editions of SQL Server 2008 are

4 Introduction SQL Server 2008

Your Data Any Place, Any Time

4 Install SQL Server 2008

Bước 1: Chạy file setup.exe để cài đặt, chọn Installation -> New SQL Server stand-alone ...

4 Install SQL Server 2008

Bước 2: Chọn OkClick Next

4 Install SQL Server 2008

Bước 3: Chọn kiểu cài đặt

4 Install SQL Server 2008

Bước 4: Nhập product key

4 Install SQL Server 2008

Bước 5: Sau khi đồng ý License Terms, chọn các thành phần cài đặt

4 Install SQL Server 2008

Bước 6: Thiết lập cài đặt chọn Default instance

4 Install SQL Server 2008

Bước 7: Cấu hình server

4 Install SQL Server 2008

Bước 8: Cấu hình dữ liệu như sau chọn Window Authentication và Add current User

4 Install SQL Server 2008

Bước 8: Cấu hình analysis services Add Current User Bước 10: Cấu hình report chọn option như hình nhấn Next, Next … Cho đến khi hoàn tất

5

SQL Server 2008 Tools

• Getting help with books online • Sql server management studio • Object explorer • Query editor • Log file viewer • Sql server profiler • Sql server configuration manager • Report server configuration manager • Command line tools

5 GETTING HELP WITH BOOKS ONLINE

• Books Online is simply one of the most important tools

you’re going to find in SQL Server.

• SQL Server Books Online is a great quick reference that follows you to whatever machine you’re working on at the time

5 GETTING HELP WITH BOOKS ONLINE

5

SQL Server Management Studio

• SSMS presents a graphical

interface for configuring, monitoring, and administering instances of SQL Server, including creating and modifying components of a database, and dealing with security issues.

• SSMS offers many different tool windows that facilitate the development and modification of database objects.

5

SQL Server Management Studio

SQL Server Management Studio in 10

It's easier to find errors

It (sort of) plays well with previous versions

1. Combines the best features of many tools 2. Work with projects and solutions 3. The tool is a data analyst's best friend 4. You can display line numbers 5. 6. Get started faster with an expanded Template Explorer 7. 8. Name that registered server 9. Manage your SQL Server Integration Services pages 10. Keep tabs on your reports

5

SQL Server Management Studio

1. Open SQL Server Management Studio: Click start  select program  select Microsoft SQL Server 2008 select SQL Server Management Studio

5

SQL Server Management Studio

• Server Type: the various subsystems of SQL Server

Integration Services.

that user logging into: • Database engine • The normal database server. • Analysis Services. • Report Server. •

5

SQL Server Management Studio

• Server Name: the SQL Server in to which you’re asking

•

to be logged. •

“.” log in to the default instance of SQL Server that is on this same machine. “.” (local): automatically identifies server and how you’re going to get there.

5

SQL Server Management Studio

• Authentication:

• SQL Server Authentication: the user provides a

• Windows Authentication: Those Windows users are mapped into SQL Server logins in their Windows user profile.

SQL Server specific login and password.

5

Object Explorer

• Object Explorer is a multifunctional window available in that will be used to initiate most

is the tool it

SSMS, database management tasks.

• Use Object Explorer to connect to multiple instances of SQL Integration Services, Analysis Services, and

Server, Reporting Services instances .

5

Object Explorer

• Databases: Holds the system and user databases

within the SQL Server.

connect to SQL Server.

• Security: Details the list of SQL Server logins that can

• Server Objects: Details objects such as backup devices and provides a list of linked servers, where one server is connected to another remote server.

5

Object Explorer

• Replication: the details involving data replication from a database on this server to another database or vice versa.

• Management: Details maintenance plans, policy

management, data collection, and Database Mail setup.

• SQL Server Agent: Builds and runs tasks within SQL Server at certain times, with details of successes or failures.

5

Query Editor

• Query Editor provides the ability to open, edit, or create

new queries.

• Database Engine Queries • Analysis Services MDX Queries • Analysis Services DMX Queries • Analysis Services XMLA Queries • SQL Server Compact

• The types of queries supported by the Editor are:

5

Command-Line Tools

Query Editor

Demo

Result

5

Log File View

• Log File Viewer in SQL Server Management Studio is used to access information about errors and events that are captured in log files.

• Right-click on a log, and select View SQL Server Log.

• Expand the Management node in Object Explorer. • Expand SQL Server Logs.

5

Log File View

5

Log File View

• Can view SQL Server log files from a local or remote instance of SQL Server when the target instance is offline or cannot start.

or programmatically.

• Can access the offline log files from Registered Servers,

5

Log File View

Log File Viewer task

• Describes how to open Log File Viewer depending on

the information that you want to view.

• Describes how to view offline log files through registered servers and how to verify WMI (Windows Management Infrastructure) permissions.

• Provides Log File Viewer F1 Help.

5

SQL Server Profiler

• SQL server Profiler provides the ability to monitor and

record virtually every facet of SQL Server activity.

5

SQL Server Trace

5

SQL Server Trace

• The Profiler can be used to create and view SQL Server

Traces

• The Trace Properties

trace template, saving options, and trace stop time

• The basic structure of the trace: the trace name,

• SQL Server events are to be traced

5 SQL SERVER CONFIGURATION MANAGER

• The SQL Server Configuration Manager is a Microsoft Management Console (MMC) snap-in and is used to manage all the services and protocols employed by an instance of SQL Server.

• The SQL Server Configuration Manager is used to configure computers for database access. Include: • Service Management • Network Configuration

5

Service Management

• The services include:

Integration Services

• • Analysis Services • Reporting Services • SQL Server Agent • SQL Server • SQL Server Browser

5

Network Configuration

• SQL Server provides several Net-Libraries (NetLibs) is used to communicate with certain network protocols.

• The NetLibs supplied with SQL Server include:

• Named Pipes • TCP/IP (the default) • Shared Memory • VIA (Virtual Interface Adapter)

5

Network Configuration

5 Report services Configuration manager

• SQL Server 2008 includes an updated Reporting Services Configuration Manager use to configure a Reporting Services Native Mode installation.

5 Report services Configuration manager

5

Reporting SCM

• Report Server Status: monitor the status and stop and

start the Reporting Services service

the Reporting Service runs

• Service Account: configure the account under which

• Web Service URL: the viewing or changing of

the that hosts the

virtual directory on the SQL Server Reporting Services Web Service.

Report Server databases

• Database: create or configure SQL Server 2008

5

Reporting SCM

• E-mail Settings: The SMTP Server settings are very

• Report Manager URL: where the virtual directory for the administrative interface, Report Manager, is viewed or configured.

straightforward and simple.

• Execution Account: is used when a report needs

resources that are not locally available

5

Command-Line Tools

• The SQLCMD is the utility used to execute Transact- SQL statements, Stored Procedures, and SQL script files from the command prompt.

• The SQLCMD includes the ability to use variables, server connect information, and pass error information back to the calling environment

dynamically, servers query to

5 SQL Server 2008 Database Objects

Database

• The database scope defines all the objects within a

database catalog.

• An instance of SQL Server 2008 can contain many

100

databases.

5 SQL Server 2008 Database Objects

Schema • Each database can contain one or more schemas. A

schema is a namespace for database objects.

• Adatabase schema is a defined namespace in which

101

database objects exist.

5 SQL Server 2008 Database Objects

Object Names

• Every object in a SQL Server 2008 database is

identifiedby a four-part, fully qualified name: server.database.schema.object

102

• Can be omitted the server name and database name. • New users are assigned the default schema of dbo.

5 SQL Server 2008 Database Objects

Objects

103

– Tables – Views – Stored Procedures – Functions – Rules – Defaults – Triggers

5 SQL Server 2008 Databases

System Databases

objects in SQL Server, accounts, Linked Server definitions, and EndPoints.

• The master Database: is used to record all server-level this includes Server Logon

104

• The model Database: a template database.

5 SQL Server 2008 Databases

System Databases

msdb database extensively for the storage of automated job definitions, job schedules, operator definitions, and alert definitions.

• The tempdb Database: is used extensively during SQL

• The msdb Database: the SQL Server Agent uses the

105

Server operations

5 SQL Server 2008 Databases

System Databases

• The resource Database: is a Read Only

database that contains all system objects used by an instance of SQL Server. – It contains the structure and description of all system

106

objects

5 SQL Server 2008 Databases

User Databases

• Databases created by users. They are created to store data used by data applications and are the primary purpose of having a database server.

107

5 SQL Server 2008 Database Storage

• Default locations for system database files

associated with the first instance of SQL Server

108

5 SQL Server 2008 Database Storage

Data Files and Filegroups

• When a user database is created, it must contain at least one data file. This first data file is known as the primary data file.

• The primary data file is a member of the default Primary

filegroup.

• Every database has one Primary filegroup when created,

109

which consists of at least the primary data file.

5 SQL Server 2008 Database Storage

Data Files and Filegroups

110

5 SQL Server 2008 Database Storage

Log Files

must be defined.

• Upon initial creation of a database, one transaction log

111

• The transaction log is used to record all modifications to the database to guarantee transactional consistency and recoverability.

5 SQL Server Security

• SQL Server can be configured to work in either the

112

Windows Authentication Mode or the SQL Server and Windows Authentication Mode, which is frequently called Mixed Mode.

5 SQL Server Security

Windows Authentication Mode

• Windows Authentication Mode: only logins for valid Windows users are allowed to connect to SQL Server. • SQL Server “trusts” the Windows or Active Directory security subsystem to have validated the account credentials.

113

5

Windows Authentication Mode

•

SQL Server and Windows Authentication Mode (Mixed Mode)

In SQL Server Mode and Windows Authentication Mode, or Mixed Mode, valid Windows accounts and standard SQL Server logins are permitted to connect to the server.

• SQL Server logins are validated by supplying a

114

username and password. Windows accounts are trusted by SQL Server.

6 Questions

1. Định nghĩa các thuật ngữ: cơ sở dữ liệu, hệ quản trị cơ sở dữ liệu, hệ cơ sở dữ liệu, từ điển cơ sở dữ liệu, mô hình cơ sở dữ liệu.

115

2. Nêu các tính chất của một cơ sở dữ liệu 3. Nêu các chức năng của một hệ quản trị cơ sở dữ liệu 4. Giải thích các đặc trưng của giải pháp cơ sở dữ liệu 5. Định nghĩa mô hình cơ sở dữ liệu và phân loại 6. Liệt kê các người có liên quan đến hệ cơ sở dữ liệu.

116