Chapter 1

DB2

DB2 is typically used for large, mission-critical systems that run on legacy IBM mainframe systems using the z/OS or OS/390 operating system

null values

Each column definition also indicates whether or not it can contain null values. A null value indicates that the value of the column is unknown

one-to-many relationship

Each row in the Vendors table is related to one or more rows in the Invoices table.

Client/Server System

In a client/server system, the processing done by an application is typically divided between the client and the server.

File-Handling System

In a file-handling system, all of the processing is done on the clients. Although the clients may access data that's stored in files on the server, none of the processing is done by the server. As a result, a file-handling system isn't a client/server system.

Unique Key

In addition to primary keys, some database management systems let you define one or more non-primary keys. In MySQL, these keys are called unique keys. Like a primary key, a non-primary key uniquely identifies each row in the table

Microsoft SQL

Microsoft (MS) SQL Server is typically used for small-to medium-sized systems that run on one or more Windows servers

Primary Key

Most tables have a primary key that uniquely identifies each row in the table. The primary key is usually a single column, but it can also consist of two or more columns. If a primary key uses two or more columns, it's called a composite primary key

programmers and database admins

MySQL programmers typically work with the DML statements, while database administrators (DBAs) use the DDL statements

MySQL

MySQL runs on all major operating systems and is widely used for web applications. MySQL is an open-source database, which means that any developer can view and improve its source code. In addition, the MySQL Community Server is free for most users, though Oracle also sells an Enterprise Edition of MySQL that has advanced features.

How does the client communicate with the server?

Once the software for both client and server is installed, the client communicates with the server via SQL queries (or just queries) that are passed to the DBMS through the API. After the client sends a query to the DBMS, the DBMS interprets the query and sends the results back to the client.

Oracle

Oracle is typically used for large, mission-critical systems that run on one or more Unix servers

A SELECT statement is commonly referred to as a _____

Query

database driver

Some programming languages include a piece of software known as a database driver for the API that it uses to access MySQL. For example, PHP includes a MySQL driver for both the mysqli and PDO APIs. As a result, you typically don't need to install a database driver when you use PHP

SQL Statements

The SQL statements can be divided into two categories: the data manipulation language (DML) that lets you work with the data in the database and the data definition language (DDL) that lets you work with the objects in the database.

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.

data type

The data type that's assigned to a column determines the type of information that can be stored in the column

Foreign Key

The foreign key is simply one or more columns in a table that refer to a primary key in another table

Cell

The intersection of a row and a column is sometimes called a cell. A cell stores a single value.

standard SQL

The most basic SQL statements are the same for all SQL dialects. Once you have learned one SQL dialect, you can easily learn other dialects. Any non-trivial application will require modification when moved from one SQL database to another

Inner join syntax

The most common type of join is an inner join. This type of join returns rows from both tables only if their related columns match

back-end processing

The processing that's done by the DBMS is typically referred to as back-end processing, and the database server is referred to as the back end.

DELETE Statement

You use the DELETE statement to delete one or more rows from a table based on the condition you specify.

INSERT statement

You use the INSERT statement to add rows to a table.

SELECT statement

You use the SELECT statement to retrieve selected columns and rows from a base table. The result of a SELECT statement is a result table, or result set

UPDATE Statement

You use the UPDATE statement to change the values in one or more rows of a table based on the condition you specify

DBMS (Database Management System)

a product used for the storage and organization of data that typically has defined formats and structures

data access API (application programming interface)

an API depends on the programming language, and API you're using

SQL (Structured Query Language)

an international standard language for processing a database

auto increment column

A column can also be defined as an auto increment column. An auto increment column is a numeric column whose value is generated automatically when a row is added to the table

Default Value

A column can also be defined with a default value. Then, that value is used if another value isn't provided when a row is added to the table.

column

A column represents some attribute of the entity, such as the amount of an invoice or a vendor's address

JOIN statement

A join lets you combine data from two or more tables into a single result set

relational database

A relational database consists of tables. Tables consist of rows and columns, which can also be referred to as records and fields

result set

A result set can include calculated values that are calculated from columns in the table

Rows

A row contains a set of values for a single instance of the entity, such as one invoice or one vendor

Indexes

A table can also be defined with one or more indexes. An index provides an efficient way to access data from a table based on the values in specific columns. An index is automatically created for a table's primary and non-primary keys.

table

A table is typically modeled after a real-world entity, such as an invoice or a vendor.

SQL language

Although SQL is a standard language, each vendor has its own SQL dialect, or variant, that may include extensions to the standard

Enhanced entity-relationship (EER) model or (ER) model

An entity-relationship (ER) diagram or enhanced entity-relationship (EER) diagram can be used to show how the tables in a database are defined and relate

outer join syntax

An outer join returns rows from one table in the join even if the other table doesn't contain a matching row.