SQL Foundations  «Prev 

Databases and Column Types

Different database engines have different names for column types.
One will have to determine the general use of a given column, then figure out which data type most closely resembles what you are looking for. In the examples here, we have used the SQL Server (Microsoft) data types, but you can easily substitute those used for Oracle, Sybase, Access, or mSQL.

A database engine is the underlying software component that a database management system (DBMS) uses to create, read, update and delete data from a database.
Most database management systems include their own (API) application programming interface that allows the user to interact with their underlying engine without going through the user interface of the DBMS.
The term database engine is frequently used interchangeably with database server or database management system.
A database instance refers to the processes and memory structures of the running database engine.
Many of the modern DBMS support multiple storage engines within the same database. For example, MySQL supports InnoDB and MyISAM.

Tables, Columns, and Rows

If you are familiar with databases already, you have heard alternative terms for similar concepts. Figure 4-4.1 shows how these terms are related. Codd's relational-model terms are in the first column; SQL-standard and DBMS-documentation terms are in the second column; and the third-column terms are holdovers from traditional (nonrelational) file processing. I use SQL terms in this course (though in formal texts the SQL and Model never are used interchangeably).

Figure 4-4.1: Model, SQL, Files

Database Tables

From a user's point of view, a database is a collection of one or more tables (and nothing but tables). A table:
  1. Is the database structure that holds data.
  2. Contains data about a specific entity type. An entity type is a class of distinguishable real-world objects, events, or concepts with common properties [patients, movies, genes, weather conditions, invoices, projects, or appointments] (Patients and appointments are different entities, so you would store information about them in different tables.)
  3. Is a two-dimensional grid characterized by rows and columns (refer to Figures 4-4.2 and 4-4.3).
  4. Holds a data item called a value at each row's column intersection.

Figure 4-4.2: This grid is an abstract representation of a table, which is the fundamental storage unit in a database.

Figure 4-4.3:This grid represents an actual (not abstract) table, shown as it usually appears in database software and books. This table has 3 columns, 4 rows, and 3 X 4 = 12 values. The top row is not a row but a header that displays column names.