Fundamental Database Concepts
We will now look at some key database concepts and data objects. Any database administrator worth his salt must be absolutely familiar with these. And they aren’t just theoretical; virtually all DBAs will be intimately involved with these concepts and objects on a near-daily basis. They are to database administration what knowledge of the human body is to the field of medicine.
We will only define each term briefly here. To better grasp the concepts, together with real-world examples, follow the term links and visit the "Related Terms" section to understand how each concept relates to and works with others in the realm of database administration.
The table is the basic data-storage unit in a relational database. Tables consist of columns and rows. The columns are the attributes or qualities that we want to express, while the rows hold the actual data, with one (or no) items per row. Think of the layout of a spreadsheet; this is very similar to the logical organization of a relational table.
A simple example of the tables in the database of a commercial bank can be found below.
Relationships are THE reason why relational databases work so well. If you only learn one concept about databases, this is the one to learn. As the name implies, relationships are the very core of relational databases.
In relational databases, a relationship exists between two tables when one of them has a foreign key that references the primary key of the other table. (More on foreign and primary keys in a bit).
In the diagram below, you can see examples of relationships. For instance, the AccountTypeID field (column) in the AccountTypes table references the AccountTypeID column of the Customer table.
A row, also called a record, represents a set of data about a specific item. Every record in a table has exactly the same structure, but of course different data. Think of the rows in an Excel spreadsheet - the concept of rows in a database is very similar. Each row in a table consists of distinct data items, with one (or zero) items for each column of the table. Rows are also called tuples, although this term is not very common.
See an example of a record or row below:
A column is a specific set of values in a table of the same type. It defines a specific attribute of the table or data. For example, we can create a column called CUSTOMER_SURNAME in a table. This is a self-explanatory column whose purpose is to store customer surnames, one value for each row. Again, think of the rows in an Excel spreadsheet and you’ll have a pretty good idea of how columns in a relational table work.
A primary key is a special column or combination of columns that uniquely identifies each record (row) in the table. The primary key column must be unique for each row, and must not contain any nulls (non-values). So, for example, to identify yourself in various databases belonging to various U.S. government departments, a unique identifier, the Social Security number, is assigned and used.
The primary key, together with the closely related foreign key concept, are the main way in which relationships are defined. Primary keys may also be a combination of columns. For instance, for many companies, a calendar month is a short-term financial period. Therefore, to uniquely identify any period, you can combine the month column and the year column, such as May 2011, to form a primary key to uniquely identify each and every financial period.
We cannot talk about the yin of primary keys without the yang of foreign keys. The two go hand-in-hand. A primary key uniquely defines a record, while a foreign key is used to reference the same record from another table.
In a commercial bank’s database, assume that you have a CUSTOMER_ID column as the primary key in the CUSTOMER_MASTER table. This uniquely identifies each customer; let us also assume the same table also contains other relevant customer information in other columns, such as CUSTOMER_SURNAME, CUSTOMER_FIRSTNAME, CUSTOMER_SOCIAL_SEC_NUMBER, CUSTOMER, CUSTOMER_GENDER, and so on.
We also have another table called LOANS_MASTER to keep track of loans given to the same bank’s customers. We now only need a single column in this table to identify which customer has received a particular loan. We can call this column CUSTOMERID, and it will reference the CUSTOMER_ID column of the CUSTOMER_MASTER table. We don’t need to store all the other customer information (name, gender, Social Security number, etc.) in the loans table. This is the elegance of the relational model.
SQLStructured Query Language (SQL) is the de facto language used for the management and manipulation of data in relational databases. SQL can be used to query, insert, update and modify data. All major relational databases support SQL, which makes life much easier for database administrators (DBAs), who have to support databases on several different platforms. Proficiency in SQL is usually one of the very first things any DBA must learn early in his or her career. Note that some people pronounce SQL as one word, "sequel".
Note that the SQL language is different from SQL Server, a relational database platform from Microsoft. It can be confusing for beginners because of the use of the generic term SQL.
Most commercial RDBMS platforms have their own customized SQL implementations, but these tend to be fully compatible with the standard SQL.