Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles

SQL etc

Posted Jun 28, 2001

Beginning SQL Programming: Pt. 1 - Page 6

By DatabaseJournal.com Staff


Structured Query Language and the science of databases is no different from other highly-evolved schools of thought, in that there are many words which are specific to the discipline. In the case of SQL some of these words are similar but actually refer to entirely different concepts. For example we use several words and phrases that contain the term "data." There are some shades of meaning to these terms that involve what layer of information or processes we are discussing.

  • Data: A set of information with some aspect in common For example data for employees might include social security numbers: "123-45-6789 Abe Adams, 234- 56-7890 Beth Barrett, 345-67-8901 Chris Cao"

  • Metadata: Information about the structure and organization of data in a database In database terms (which we will cover in Chapter 3), metadata typically contains descriptions of the tables and their constraints, the fields and their sizes and rules, and the relationships between the tables. Some people have phrased metadata as "data about the data". An example of metadata for an employee database would be something like:

    Table #1 name= Employees field count = 3
    Field #1 name= EmployeeID type=integer size= 9 digits (exactly)
    Field #2 name= NameFirst type= string size <=15 characters
    Field #3 name= NameLast type=string size <=30 characters

    This metadata describes that we have a set of data about employees that is organized into a table. Within that table we hold three kinds of information about each employee, their ID number, and their first and last names. The metadata goes on to describe how each type of information is maintained. By the end of Chapter 7, you will have a lot of experience with these terms, but for now note that the metadata does not include the data, only a description of how the data is organized.

  • Database: A database is the data and the metadata

    In other words a database is a set of related information as well as a description of how that information is organized. A database for employees would be the metadata we just looked at, plus the information described above under Data.

    Employee IDNameFirstNameLast

    Note that we frequently display data as being in a table. The table is only a logical structure, not a representation of exactly how the data sits on the disk. Different DBMS will physically store the data in different configurations, usually associated with the order that the records were added. As you will see later, the storage methodology is of no concern to us.

  • Datastore: A source of data

    (for our purposes, a source of data that has a way of responding to SQL statements)

    Since about 1985, the dominant form of database has been of a type called relational. We will study this term later in the book, but for now it is enough to understand that relational databases follow the rules for data organization established by Dr. E.F. "Ted" Codd. However, in the past few years more and more data has accumulated in non-relational forms, primarily hierarchical forms. Think, for example, of the amount of information in word-processing documents stored on your company's desktop hard drives. Clearly your company has data in those documents, but it is organized into drives, folders, subfolders and files; a hierarchical system with no rules about the data contained at each level. In an effort to emphasize data techniques that work with any source of data (relational or not), the term Datastore can be used instead of Database. The term datastore also includes older databases (generally called "flat file") that predate the relational rules of Dr. Codd. In summary, whereas the term database today generally means a modern relational database, the term datastore is much broader, including information contained in non-relational databases. Software companies have created many tools to allow SQL to talk with all types of datastores as well as proper relational databases.

  • Data Server: A Data Server manages data and is typically one or more of many servers in an environment

    Modern business computing relies on one or more central servers to hold data and handle centralized processing. In the past a single machine might perform data storage, security, print services and all other functions of the office. But as the number of computing jobs has increased (e-mail, Web Site Support, etc.), IT departments have split the job across multiple servers, each optimized to perform a very specific job. There may also be servers handling the business rules, web or network services and other functions. Any of the other servers can pass a SQL statement to the Data Server, which will execute the statement and then pass the results back to the requesting server. In simpler cases Data Servers pass information directly to clients such as desktop PCs.

    The other point to note here is that a Data Server differs from a File Server. The latter holds files organized into folders and subfolders, with the intention of keeping employee's documents on one central server. A Data Server generally abandons the folders in favor of a disk organization scheme more efficient for relational data. In a small shop a single machine may function as both a file and data server by using different organization schemes on different disk areas.

    Note that the terms "server" and "client" are relative in multiple server environments. When a web server sends a SQL statement to a data server the web server is considered to be the "client." However the web server is then considered the "server" in relation to the browser. In other words, we refer to the client as the machine or software that sent the SQL statement to the DBMS and we refer to the server as the machine (or group of machines) that receives the statement and processes it.

  • Database Management System (DBMS): Software which handles most aspects of data management including physical storage, reading and writing data, security, replication, error correction and other functions

    Common DBMS include IBM DB2, Microsoft SQL Server, Oracle, Sybase and  Informix. Although it is not a true DBMS and isn't designed for large numbers of users, Microsoft Access can also accept SQL statements. Each DBMS has a way of receiving a SQL statement and forwarding the statement to its Database Engine. The DBMS will then have a way to take a result from the engine and send it back out to the requester. Larger and more complex DBMS will be closely integrated with a specific Operating System and some actually contain an OS optimized for data management.

SQL etc Archives

Latest Forum Threads
SQL etc Forum
Topic By Replies Updated
MySQL rollback UAL225 0 August 21st, 09:56 PM
Complex Search Query Galway 0 May 20th, 10:04 PM
change collation at once supercain 2 May 15th, 06:18 AM
SQL Features, tools and utilities question Neomite 1 April 10th, 09:13 AM