SQLServerCh01

Chapter 1

An introduction
to relational databases
and SQL

Murach's SQL Server 2012, C1

© 2012, Mike Murach & Associates, Inc.

Slide 1

Objectives
Knowledge
 Identify the three main hardware components of a client/server
system.
 Describe the way a client accesses the database on a server using
these terms: application software, data access API, database
management system, SQL query, and query results.
 Describe the way a database is organized using these terms: tables,
columns, rows, and cells.
 Describe how the tables in a relational database are related using
these terms: primary key and foreign key.
 Identify the three types of relationships that can exist between two
tables.
 Describe the way the columns in a table are defined using these
terms: data type, null value, default value, and identity column.

Murach's SQL Server 2012, C1

© 2012, Mike Murach & Associates, Inc.

Slide 2

Objectives (cont.)
 Describe the relationship between standard SQL and Microsoft SQL
Server’s Transact-SQL.
 Describe the difference between DML statements and DDL
statements.
 Describe the difference between an action query and a SELECT query.
 List three coding techniques that can make your SQL code easier to
read and maintain.
 Explain how views and stored procedures differ from SQL statements
that are issued from an application program.
 Describe the use of command, connection, and data reader objects
when .NET applications access a SQL Server database.

Murach's SQL Server 2012, C1

© 2012, Mike Murach & Associates, Inc.

Slide 3

A simple client/server system

Database server

`

Network

Client

`
Client

`
Client

Murach's SQL Server 2012, C1

© 2012, Mike Murach & Associates, Inc.

Slide 4

The three hardware components
of a client/server system
 Clients
 Server
 Network

Terms to know
 Local area network (LAN)
 Wide area network (WAN)
 Enterprise system

Murach's SQL Server 2012, C1

© 2012, Mike Murach & Associates, Inc.

Slide 5

Client software, server software,
and the SQL interface

SQL queries

`
Results

Client

Database server

Application software
Data access API

Database management system
Database

Murach's SQL Server 2012, C1

© 2012, Mike Murach & Associates, Inc.

Slide 6

Server software
 Database management system (DBMS)
 The DBMS does the back-end processing

Client software
 Application software
 Data access API (application programming interface)
 The client software does the front-end processing

Murach's SQL Server 2012, C1

© 2012, Mike Murach & Associates, Inc.

Slide 7

The SQL interface
 The application software communicates with the DBMS by
sending SQL queries through the data access API.
 When the DBMS receives a query, it provides a service like
returning the requested data (the query results) to the client.
 SQL stands for Structured Query Language, which is the standard
language for working with a relational database.

Murach's SQL Server 2012, C1

© 2012, Mike Murach & Associates, Inc.

Slide 8

Client/server system
 Processing is divided between client and server

File-handling system
 All processing is done on the clients

Murach's SQL Server 2012, C1

© 2012, Mike Murach & Associates, Inc.

Slide 9

A Windows-based system
that uses an application server
User request

SQL queries

`
Response

Client
User interface

Murach's SQL Server 2012, C1

Results

Application Server

Database Server

Business components

DBMS
Database

© 2012, Mike Murach & Associates, Inc.

Slide 10

A simple web-based system
User request

`

User request

SQL queries

Internet
Response

Response

Results

Client

Web Server

Database Server

Web browser

Web applications
Web services

DBMS
Database

Murach's SQL Server 2012, C1

© 2012, Mike Murach & Associates, Inc.

Slide 11

Other client/server system architectures
 Application servers store business components
 Web servers store web applications and web services

Murach's SQL Server 2012, C1

© 2012, Mike Murach & Associates, Inc.

Slide 12

How web applications work
 A web browser on a client sends a request to a web server.
 The web server processes the request.
 The web server passes any requests for data to the database server.
 The database server returns the results to the web server.
 The web server returns a response to the browser.

Murach's SQL Server 2012, C1

© 2012, Mike Murach & Associates, Inc.

Slide 13

The Vendors table in an Accounts Payable (AP)
database
Primary key

Columns

Rows

Murach's SQL Server 2012, C1

© 2012, Mike Murach & Associates, Inc.

Slide 14

Terms
 Relational database
 Table
 Column
 Row
 Cell
 Primary key
 Composite primary key
 Non-primary key (unique key)
 Index

Murach's SQL Server 2012, C1

© 2012, Mike Murach & Associates, Inc.

Slide 15

The relationship between two tables
Primary key

Foreign key

Murach's SQL Server 2012, C1

© 2012, Mike Murach & Associates, Inc.

Slide 16

Terms
 Foreign key
 One-to-many relationship
 One-to-one relationship
 Many-to-many relationship

Murach's SQL Server 2012, C1

© 2012, Mike Murach & Associates, Inc.

Slide 17

The columns of the Invoices table

Murach's SQL Server 2012, C1

© 2012, Mike Murach & Associates, Inc.

Slide 18

Common SQL Server data types
 bit
 int, bigint, smallint, tinyint
 money, smallmoney
 decimal, numeric
 float, real
 datetime, smalldatetime
 char, varchar
 nchar, nvarchar

Murach's SQL Server 2012, C1

© 2012, Mike Murach & Associates, Inc.

Slide 19

Terms
 Data type
 Null value
 Default value
 Identity column

Murach's SQL Server 2012, C1

© 2012, Mike Murach & Associates, Inc.

Slide 20