A relational database is a collection of data items with pre-established connections, or relationships between them. These items are organized in tables with columns and rows, to hold information about the objects to be represented in the database.
Structure of a relational database
TABLE
Any information in a relational database is presented in the form of a table (like a spreadsheet). Each table is a collection of columns (types) and rows (values).
ROW
Each row is a set of values, which describe a selected object in the table.
COLUMN
Each column contains a specific property of the object.
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 set 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).
Data types
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.
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!).
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 rows are distinct
• The order of the rows is irrelevant
• The order of the columns is irrelevant
The keys
An important element of the database structure are the keys.
We recognize three kinds of keys.
Primary 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.
Alternative keys
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).
Foreign keys
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.
Move to the next article: Data Manipulation Language (DML)
