1.1 Structure of a relational database
Any information in a relational database is in the form of a table (like a spreadsheet). This one is a collection of columns (its structure) and lines (its content). Each of the tables represents a type of object or specific concept. For example, the table of products.
The rows represent each of the occurrences (the objects described) of a table. For example, a specific line represents the product # 2743.
Columns represent the characteristics that describe the objects. For example, color is one of the characteristics of products; the product # 2743 is black in color.
1.2 Definition of columns
The definition of a table goes through the definition of its columns. Each column is defined by two characteristics:
• Its name
• Its field of values
In relational theory, a domain is neither more nor less than a set of values.
The domain of a column is therefore the set of possible values that can take a column. The bare minimum required representing this area, and therefore to create a column in a table is the data type (and its length when the type chosen requires the definition of a maximum size).
A specific type of data must therefore characterize each column. It exists mainly four major families of data types: alphanumeric, digital, date and binary.
• Alphanumeric types are used to retain text and characters. The main data types in this group are CHAR (x) and VARCHAR (x), where x is the maximum number of characters that the column allows.
• Numeric types are used to store numeric data, i.e. intended to be used for calculations. Numeric is a very large family, itself divided into sub-families. We can use integer numbers, such as SMALLINT and INTEGER, or decimal numbers with NUMERIC (x, y), where x is the total number of numeric positions and y is the number of decimals. The big difference between these various types is the physical space actually occupied by the value in the table.
In the specific case of SQL Server, the data type INTEGER is named in fact INT.
• Date types are used to keep dates with or without hours, all by allowing their use for calculations. The main types of data of this group are DATE and DATETIME.
In the specific case of SQL Server, the DATE type does not exist.
• Binary types are used to hold data whose form is not known to the DBMS (Database Management System), and which then require other applications for its interpretation(such as images or sound, for example). We often do reference to the term “BLOB” to refer to these types of data.
Although the SQL standard recognizes the types of data listed above, their exact application may vary depending on the DBMS chosen. Consult the online documentation of SQL Server to know all the types of data that it can offer.
1.3 The term NULL
All columns in a row do not necessarily have a value. They are then called “NULL”. By definition, the term NULL is not a value. It is not 0 (zero), it’s not ” (white space) and neither “” (empty string). A null column does not have value and can never be compared with an operator (=, <,>, etc.).
A condition with a NULL value is always evaluated as FALSE (i.e. a NULL is never equal to a NULL, and a NULL is never different from NULL!).
1.4 Table Properties
For tables to be defined respecting the relational theory, they must respect five properties:
• All the data within the same column are homogeneous
• Each column represents a single data element
• All lines are distinct
• The order of the lines is irrelevant
• The order of the columns is irrelevant
1.5 The keys
An important element of the database structure are the keys. We recognize three kinds of keys.
The primary key is the value that uniquely identifies each of the rows contained in a table. The primary key is usually composed of a single column, but may, on occasion, be composed of several columns.
The alternative key concept is very similar to that of the primary key. An alternative key is a value that uniquely identifies each row in a table, but that was not chosen as the primary key of the table. For example, the social insurance number in a student file is an alternative key; as long as the primary key of the file is another element, (the personnel number, for example).
A column, or a combination of columns, is named foreign key in a table when it is primary key in another table. The relationship that we can usually establish between two tables goes through a primary key and a remote (foreign) key.