Idea of a Relational Database

A database is a collection of data organized in some fashion. We are dealing with relational databases.

Data that is stored in a relational database is stored in tables. Each table is made up of rows and columns, with each column storing some kind of information. We may have various kinds of data: names, numbers, dates, etc. All the rows in a table have the same column structure.

In each table, one column (or several columns taken as a whole) is the key for that table. The value in the key column is unique; that is, no two rows have the same key value.

There are various requirements for designing the tables, such as:

• Each entry in the table (a specific row and column) may contain just one value, not an embedded list of values.

• The data in the row should depend on all of the key value, not on just part of it.

There are other rules as well. We often begin with a single table of many columns and end up with several tables with fewer columns each.

We may have several tables with different keys. The primary key for table B may be listed as a column in table A. In table A, it would be considered a "foreign key" while the actual key for Table A would be the "primary key". This ties together the different tables in the overall database.

A principle is that we store each piece of information once, unless it is needed as a foreign key in another table. It is the foreign keys that tie the tables together.

Each table has a name (unique).

The overall collection of metadata (names of table, the organization of each table, what tables we have, which fields are key fields, what kind of data is in each field) is called a schema.

Let us assume someone else has designed and created the database and we want to work with it. This is done using SQL ("structured query language").

The tables themselves are probably being stored as one disk file per table, but when we use the database, they may be sitting in memory. This is not our concern; someone else is managing the database. The database itself may exist on some computer elsewhere, and we connect to it through a network.

There are a number of commercial RDB products such as Oracle, MySQL (also from Oracle), DB2, MS SQL Server, MS Access, etc. We will be using MS SQL Server.

Data Types in SQL

SQL has its own names for various data types, presumably to provide a way to talk about the subject without commitment to a specific language.

Here are a few of them:

• char[n] -- fixed-length, non-Unicode string data; n = length, 1 to 8000; the default value of n in a declaration is 1

• varchar -- variable-length, non-Unicode string data; maximum size 2 GB

• nchar[n] -- fixed-length, Unicode string data; n = length, 1 to 4000; the default value of n in a declaration is 1

• nvarchar -- variable length, Unicode string data; maximum size 2GB

• bigint -- 8-byte signed binary integer

• int -- 4-byte signed binary integer

• smallint -- 2-byte signed binary integer

• tinyint -- 1-byte unsigned binary integer

• date -- 10 characters, YYYY-MM-DD, Gregorian calendar, stored as 3 bytes

The value in a column may be "null" to indicate that the information is not known or does not apply. Key values may not be "null".