1. Character Set: A character set is a specification that determines the set of codes used to represent the characters of a particular language or set of languages. Among the oldest character sets is the ASCII (American Standard Code for Information Interchange) character set, in which the letter A is coded by the integer 65.
2. Client: A client is a program that accesses a central service. In connection with databases, a client denotes a program that accesses a central database. The database is managed by another program, the database server.
3. Client/Server Architecture: Most modern database systems are based on a server that manages the data and executes SQL commands, and an arbitrary number of clients that access the server over a network. This concept is called client/server architecture. Alternatives to this model are file-server databases such as dBase and Access, in which all clients access the database file directly through a common file system. (This is quite inefficient when many users are trying to edit data simultaneously.)
4. Cluster: In information theory a cluster usually refers to a group of networked computers that work together on a common task. A database cluster is therefore a database system in which several computers provide a common database (particularly a large or fast or secure one).
5. Cursor: In MySQL a cursor enables step-by-step processing of query results (particularly in stored procedures). In the ADO library, cursor properties determine what processing options exist for the records of a Recordset object.
6. Data Dictionary: The MySQL Data Dictionary enables one to use SELECT queries to obtain information about the structure of databases and tables as well as other administrative data about the MySQL server. The MySQL server provides, since version 5.0, virtual INFORMATION_SCHEMA tables.
7. Data Record: A data record, or simply record, is a row of a table.
8. Database: Generally speaking, a database is an ordered collection of data. In connection with MySQL, a database consists of a number of tables. In common usage, a database can also refer to an entire database system, that is, the program that manages the data. In MySQL, this program is called the database server (MySQL server).
9. Database Server: A database server is a program that permits access to databases over a network. The server is responsible for the management of the data and for the execution of SQL commands.
10. Derived Tables: These are tables that do not exist as such but are merely the result of a SELECT command. Derived tables are in fact nothing more than a variant of subSELECTs, that is, nested SELECT commands.
11. Domain Name: The domain name denotes an entire network. A local network might consist of computers with the names mars.sol, uranus.sol, and jupiter.sol, in which case mars, uranus, and jupiter are computer names (hostnames), and sol is the domain name.
12. Domain Name Server (DNS): Internally, communication within a network takes place on the basis of IP numbers (e.g., 192.168.0.27). Since mere mortals do not keep track of such numbers very well, each computer also has a computer name, composed of host name and domain name (e.g., uranus.sol).
Whenever such a name is used, the running program must determine the associated IP number. Sometimes, the converse case arises, and for a given IP number, the computer name is required. In both situations, the domain name server provides the answer. (Small networks can get by without a DNS. In that case, the association between IP numbers and computer names is determined from static tables, under Unix/Linux in the file /etc/hosts.)
13. Foreign Key: A foreign key is a unique value (usually an integer) that refers to a particular column of another table. A foreign key thus creates a link (relation) between two tables.
14. Full-Text Index: A full-text index enables the search for several words in arbitrary order within a text. To make full-text search efficient, a special index is required, known as a full-text index.
15. Global Assembly Cache (GAC): This notion is specific to Microsoft. It describes a central directory of all *.dll files for .NET programs. In contrast to the Windows system directory, in the GAC, several different versions of a library can be installed simultaneously without leading to a conflict.
16. Host Name: The host name is the name of a computer valid over a network. If the complete name of the computer is jupiter.sol, then jupiter is the host name and sol the domain name.
17. Hot Backup: What makes a hot backup hot is that during the backup process, the database server can continue to run almost completely undisturbed. (During a normal backup, on the other hand, no changes in the data may be made.) Hot backups can currently be made in MySQL only with a commercial auxiliary program that works only for InnoDB tables.
18. Include File: An include file is a part of program code. It is read by the compiler (e.g., C) or during code execution (e.g., PHP).
19. Index: In database-speak, an index, or its synonym key, is an ordered list of the content of a table. An index serves primarily to speed up access to individual records: Instead of searching through all records, it suffices to search the index. Then, the desired record can be read directly.
20. InnoDB Tables: In MySQL, tables can be stored on the hard drive in more than one format, including MyISAM and InnoDB. InnoDB tables possess some additional functionality over MyISAM tables, such as transactions and foreign key constraints. The name InnoDB is derived from the name of the company Innobase, with DB being short for database.
21. Internet Service Provider (ISP): An ISP is a company that provides technical support and hosting of web sites. ISPs are used by commercial enterprises and private individuals who wish to maintain web sites on the Internet but do not themselves possess a permanent or sufficiently high-speed Internet connection.
Be careful in your choice of ISP that it supports the programs presented in this book (Apache, PHP, MySQL, etc.). There are, of course, ISPs that offer Microsoft products instead.
22. Key: A key is an ordered index of a table. See also the entry of the synonym Index.
23. Logging: Logging refers to the recording of SQL commands in a file. The motivation for logging is usually to have available all the changes to the database made since the last backup so that the database can be re-created in the event of disaster. With MySQL, logging is also a prerequisite for replication.
24. MyISAM Tables: The MySQL server can store tables in various formats. The most important such format is called MyISAM, which stands for indexed sequential access method, referring to the method of access to data records within a file with the aid of an index. An alternative to MyISAM tables is InnoDB tables.
25. Named Pipes: Named pipes represent a mechanism whereby two programs can communicate under Windows 2000/XP. Data exchange takes place according to the principle of first in, first out (FIFO).
26. Normal Form: A database generally consists of a number of tables. A goal in the design of tables is to avoid redundancy. This goal is supported by particular rules. When the first n such rules are adhered to, the database is said to be in nth normal form.
27. Normalization: This term refers to the optimization of the database design to avoid redundancy. The goal is generally to achieve third normal form.
28. PHP: PHP is a script language for programming dynamic web sites. The program code is located in *.php files. These files are executed by the PHP interpreter at each access over the web server. The resulting HTML document is passed on to the web server.
29. Port: Network packets that are sent using the TCP/IP protocol are always addressed to a particular port. The port number is thus part of the address to which the packet is sent. Ports help in categorizing network traffic. For example, for data exchange between web browsers and web servers, port 80 is used. MySQL uses port 3306 by default.
30. Prepared Statements: These provide for a particularly efficient processing of SQL commands that differ only in the values of parameters. The entire SQL command is sent only once to the MySQL server, and thereafter only the varying parameters. This not only reduces the quantity of data that has to be transferred, but also spares the server the repeated analysis of the SQL command.
31. Primary Index (Primary Key): The primary index identifies each record of a table uniquely. Normally, the primary key field is an integer column. Whenever a new record is inserted, the database server determines a new, unique value for this column.
32. Privilege: In MySQL nomenclature, a privilege is a right to execute particular database operations (e.g., read or change data in a particular table).
33. Query: In database language, a query is generally a SELECT command. The syntax of this command is part of the language SQL (structured query language). SQL specifies how commands to the database server must be structured.
34. Query Cache: In many database applications, it happens that the same queries must be executed over and over. To speed up such queries, MySQL possesses, since version 4.0, a query cache, that is, temporary storage in which queries and their results can be stored. Queries thus stored can be answered much more rapidly than otherwise. However, this temporary storage must be deleted at each change in the underlying table.
35. Referential Integrity: Referential integrity holds if between linked tables, no reference points to nowhere. (For example, if referential integrity is satisfied for a library database, then there cannot exist a book title whose associated publisher does not exist in the database.
On the other hand, if referential integrity has been damaged, then there might well be a publisher number in the titles table that does not exist in the publisher table.) Many database systems can automatically check referential integrity when changes to a database are made. This requires the setting up of foreign key constraints, otherwise known as integrity rules. MySQL currently supports foreign key constraints only for InnoDB tables.
36. Relations: The most characteristic feature of relational databases is that a number of tables in a database can be linked based on their contents. These links are also called relations. The links are referenced in SQL commands by the JOIN operator or with WHERE conditions.
37. Replication: Through the process of replication, all changes to a database are carried out not on one, but on several database servers. Thus the same database exists on several servers. The advantages are increased security (if one server goes down) and greater speed (all servers can answer SQL queries, even if only one can make changes).
38. Schema: The schema (or database schema) describes the layout of a database, that is, the tables, their columns and data types, indexes, links between tables, etc.
39. Server: A server is a central program that provides services or data to other programs (called clients). Examples of servers are the MySQL server (a database server) and Apache (a web server).
40. Socket File: A socket file enables communication under Unix/Linux between two programs. A socket file is not a real file, in that it contains no data and has length zero.
Local connections between a MySQL client and the MySQL server generally take place under Unix/Linux over a socket file, since this type of communication is more efficient than the network protocol TCP/IP. (An exception is Java clients, which do not support socket files.) The usual file name is /var/lib/mysql/mysql.sock. This file name can be set via the configuration file /etc/my.cnf.
41. Stored Procedures: A stored procedure is program code that is executed directly by the database server. Often, stored procedures are formulated in a programming language that extends SQL through the use of control structures (loops, branching, etc.). MySQL has supported stored procedures since version 5.0.
42. SubSELECTs: The nesting of several SELECT commands to form a query is called a subSELECT. MySQL has supported such commands since version 4.1.
43. Tables: A table is a part of a database. The properties of a table are determined by its columns or fields (name and data type) and indexes. The rows of a table are also called data records. The MySQL server can store tables in several formats (e.g., MyISAM, InnoDB).
44. Timestamps: The data type TIMESTAMP is characterized by the fact that columns of this type store the current date and time each time a change is made.
45. Transactions: A transaction is a group of logically related SQL commands. Transaction-capable database systems are able to confirm all the commands of a transaction together or to abort them as a group. That is, a transaction is never only partially completed. Transactions thereby contribute to data security.
Transactions can also improve efficiency in that there is no longer the need to lock entire tables with LOCK during the execution of several SQL commands. MySQL currently supports transactions only for InnoDB tables.
46. Triggers: This term refers to SQL code (stored procedures) that is automatically executed as a result of particular actions (e.g., upon an UPDATE or INSERT command). Triggers can be used, for example, to ensure that certain rules are followed in changing data. MySQL has supported triggers since version 5.0.
47. Unicode: Unicode is a character set in which every character is represented by a 16-bit integer. Thus almost all the special characters of the world’s languages can be represented in this character set. MySQL supports the storage of Unicode character sets since version 4.1.
Within Unicode, there are various formats that specify the byte order of the 16-bit integer. Common formats are UTF8 (1 to 3 bytes, depending on the character code, avoids code 0) and UTF16 alias UCS2 (2 bytes for each character code).
48. Uniform Resource Locator (URL): This unwieldy term denotes Internet addresses (e.g., http://www.yeahhub.com/ or ftp://ftp.yeahhub.com). Additional information can be provided in a URL, such as variables or user names: http://my.yeahhub.com/post.html?var=123, ftp://yeahhub.company/directory.
49. Views: A view is an SQL query that enables a special view of several tables of a database. Views are used, for example, to enable simple access to particular parts of a table (e.g., for reasons of security if only part of a table is to be processed). MySQL has supported views since version 5.0.
50. Web Server: A web server is a program that makes HTML pages available over the Internet. The most popular web server today (current market share about 60 percent) is Apache.