The chapter covers database systems and provides the big picture of their purpose and function.
By the end of the chapter, you will be able to:
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 and rows
Describe how the tables in a relational database are related
using these terms: primary key and foreign key.
What is the
difference between DML SQL statements and DDL SQL statements
A database is part of a larger network or system. It stores the data used by client's on the system as shown in the simple client/server example below:
When working with a client/server system, terminology you should know includes:
1. Client - the PCs, macs, workstations etc of that system that make requests
2. Server - stores the databases and files of the system and provides services to the clients. Servers dedicated to storing database files are called database servers. (Systems with database servers would have separate servers to handle network services.) Servers can be PCs, mainframes, midrange systems etc.
3. Network - how clients and servers are connected to allow communication
Types of networks include:
a) LAN (local area network) - connects devices over a short distance such as an office building, school or home. They are normally owned, controlled and managed by a single person or organization.
b) WAN (wide area network) - connects geographically dispersed LANs using a router to connect the LAN to the WAN. The Internet is the largest WAN. WANs are not owned by an individual or organization and they use a variety of telecommunication devices/channels to transmit messages.
c) Enterprise WAN - corporate network that connects geographically dispersed LANs . The enterprise owns and manages the networking equipment within the LAN and the LANs are connect to the Internet using an Internet Service Provider (ISP). Remote users would connect to the Enterprise WAN using a virtual private network (VPN)
Much of what you do online involves interacting with a database. When you "google" something, that something is an online database Google checks for keywords you have entered. When you look at previous orders on shopping sites, you are retrieving order data from a database or data storage system. When you register for classes, you are interacting with a database. Literally, almost everything you do online involves some type of data storage system.
A simple web based system consists of a client that connects to the Internet and makes a request. The request is processed by a web server. If the request requires stored data, the web server queries the database server for the requested data. The database server sends the results to the web server which then sends a response containing the results back to the client. The example below illustrates this process.
Concepts involved in this process include:
Client software (application software for front-end processing)
Database Server software
How the SQL interface works
An intranet is a private, internal network contained within a company. It may include multiple, linked LANs and it could also use leased lines in a WAN. The primary purpose is to share information and resources among employees.
In the example below, a client makes a request to an application server. If the request requires data stored in a database, a query is passed to the database server. The database server processes the request and sends results back to the application server, which then passes a response back to the client (the software on the client would format the response and display it to the user).
It is not unusual for a company to have several servers - each one is dedicated to a particular type of processing
We will be working with relational databases all semester, the terminology and concepts presented below will be expanded upon in upcoming weeks. For now, you should try to get an understanding of the big picture regarding what relational dataabases are and how relational databases work.
A relational database is a collection of tables (also called relations).
A table is modeled after a real-world entity such as students, classes, customers, products or orders.
Tables consist of a collection of rows or records.
Each row in a table contains a set of columns (also called fields or attributes).
Each column has a data type that specifies what kind of data can be stored.
Examples of data types include:
Data types are important because they:
All tables must have a unique identifier that can be used to access data. This unique identify is called a primary key.
In the customer table below, Customer# is the primary key and can be used to retrieve a single row or record
| Customer# | FirstName | LastName | Address | City | State | Zipcode |
| 1 | Andrew | Chin | 123 Old Oak Rd | Traverse City | Mi | 49684 |
| 2 | Laura | Walton | 555 Windemere Lane | Traverse City | Mi | 49696 |
| 3 | Harold | Herman | 55 Hwy 1 | Traverse City | Mi | 49684 |
| 4 | Cindy | Lopez | 25 W Houghton Lk Rd | Traverse City | Mi | 49696 |
In the order table below, OrderId is the primary key and can be
used to retrieve a single record or row
| Order Id | Item# | Description | Price | Qty |
| 1 | 100 | Prudenville Sweatshirt | 20.00 | 4 |
| 2 | 50 | Varsity jacket | 75.00 | 1 |
| 3 | 65 | HS Ring | 100.00 | 1 |
The advantage of using a relational database is the ability to
connect tables within the database to share information. The
sharing of information eliminates repeating data among different tables.
In a relational database the ONLY column that should be repeated among tables is the column you want to use to connect the tables. Most often, you will relate tables by placing the primary key from table #1 into table #2. That column in table #2 is called a foreign key. So, a foreign key is a special column used to relate the row to another table.
In our example, we would need to add customer# to the order table in order to connect the two tables and share data. By adding Customer# to the order table we are adding a foreign key to the order table.
| Order Id | Item# | Description | Price | Qty | Customer# |
| 1 | 100 | Prudenville Sweatshirt | 20.00 | 4 | 4 |
| 2 | 50 | Varsity jacket | 75.00 | 1 | 1 |
| 3 | 65 | HS Ring | 100.00 | 1 | 4 |
A database schema is a blueprint of how the database is constructed. It includes the structure for each table and how the tables are related. The table structure is made up of the columns used to store the data along with the primary key and constraints (rules) to ensure that the data entered will be accurate.
Structured Query Language or SQL (pronounced sequel ) is a language for creating and manipulating relational databases. It was initially created at IBM as part of System-R. It was implemented with modifications in numerous products: Oracle, Sybase, DB-2, SQL Server and MySQL.
While SQL is an ANSI (American National Standards Institute) standard, there are different vendor-based versions of the SQL language. Each version must support the major commands (such as SELECT, UPDATE, DELETE, INSERT, WHERE) in a similar fashion to remain compliant. So, even though different vendor versions are not identical, they are very similar. For example, MySQL and SQL Server use the same basic commands, but the data types are a little different.
SQL statements fall under two primary categories:
1. DML SQL statements - work with data and are used by programmers. DML statements use commands like SELECT, INSERT, UPDATE and DELETE
2. DDL SQL statements - work with database objects and are used by database administrators to manage the database or table structure. DDL statements use commands like CREATE DATABASE, CREATE TABLE, ALTER TABLE, DROP DATABASE, DROP TABLE etc.
In this class we are using SQL Server whose commands are based on Transact SQL (T-SQL). Throughout the semester, you will be learning and using T-SQL commands.
SQL commands that are part of the ANSI standard include:
SELECT - extracts data from a database
UPDATE - updates data
in a database
DELETE - deletes data from a database
INSERT
INTO - inserts new data into a database
CREATE DATABASE - creates a
new database
ALTER DATABASE - modifies a database
CREATE TABLE
- creates a new table
ALTER TABLE - modifies a table
DROP
TABLE - deletes a table
CREATE INDEX - creates an index (search
key)
DROP INDEX - deletes an index
We will be learning more about the SELECT command in the next couple of weeks.
Commands and options that are part of the SQL language are known as keywords. Keywords are not case sensitive, but using all capital letters is a standard way of entering them (we will be following the standards in this course)
Another standard is placing semicolons at the end of SQL statements.
SQL Server doesnt require the semicolon, BUT OTHER DB systems do
require it. We will be following the standard to make our
code portable to other systems (such as MySQL).