Important Database Concepts - Microsoft Business Intelligence Tools for Excel Analysts - page 8

When working with Access, the term field is used to refer to an attribute stored in a
record. In many other database systems, including SQL Server, column is the expression
you'll hear most often in place of field. Field and column mean the same thing. The exact
terminology used relies somewhat on the context of the database system underlying
the table containing the record.

Note

Queries
Most relational database systems allow the creation of queries (sometimes called views ). Queries
extract information from the database tables. A query selects and defines a group of records that ful-
fill a certain condition. Most database outputs are based on queries that combine, filter, or sort data
before it's displayed. Queries are often called from other database objects, such as stored procedures,
macros, or code modules. In addition to extracting data from tables, queries can be used to change,
add, or delete database records.

An example of a query is when a person at the sales office tells the database, “Show me all customers,
in alphabetical order by name, who are located in Massachusetts and bought something over the
past six months.” Or “Show me all customers who bought Chevrolet car models within the past
six months and sort them by customer name and then by sale date.”

Instead of asking the question in words to query a database, you use a special syntax such as SQL
(Structured Query Language).

How Databases Are Designed
The better a database is designed or structured, the better the reporting solutions are able to lever-
age the data within it. The design process of a database is not all that mysterious. The basic design
steps described in this section provide a solid understanding of how best to think about and even
design your own databases.

Step 1: The overall design — from concept to reality
All solution developers face similar problems, the first of which is determining how to meet the
needs of the end client. It's important to understand the overall client's requirements before zeroing
in on the details.

For example, a client may ask for a database that supports the following tasks:

➤ Entering and maintaining customer information (name, address, and financial history)

➤ Entering and maintaining sales information (sales date, payment method, total amount,
customer identity, and other fields)

➤ Entering and maintaining sales line-item information (details of items purchased)

➤ Viewing information from all the tables (sales, customers, sales line items, and payments)