SQL Guide - Basic RDBMS Concepts.

RBDMS (Relational Database Management System) is a database management system based on the relational model. Below are key components of the Relational Database Management System or RDBMS

1. Table
2. Row, Record or Tuple
3. Column, Field or Attribute
4. Domain
5. Instance
6. Schema
7. Constraints & Keys

  

Table

 In RDBMS, data is stored in objects called tables. A table is a set of related data, consisting of columns and rows.

The table is the most common and simplest way to store data in a relational database (hereinafter referred to as RDB).

The following is an example of the Employee table which contains columns for EmployeeID, Firstname, Lastname and Title.

 

Below is an example of Employee table

COLUMN (Field)

Each table consists of entities called fields. The Employee table includes the following fields:

 EmployeeID

 Firstname

 Lastname

 Title

 

A field is a column of a table whose purpose is to store certain information about each record in a table.

ROW (Record)

A record is every single element that is stored in a table. For example, in our Employee table there are 9 entries so in other words there are 9 rows in the table. In simple explanation we can say that a record is an entity that is stored horizontally and a column is a vertical entity in a table that contains all the information associated with a specific field of the table. 

In the above example, in our “Employee ” table, the 'title' column represents the title of each employee.

Domain

A domain is a set of permitted values for a column in table. For 
example, a column of months in a year can accept January, 
February,…December as values, a column or domain of dates can accept all possible 
valid dates in a month etc. We can specify domain of a column(attribute) while creating a table. 

An attribute cannot accept values that are outside of their domains. For example, In the above table “Employee ”, the EmplyeeID field has integer domain so that field cannot accept values that are not integers for example, EmplyeeID cannot has values like, “First”, 10.11 etc.

Instance 

Database consists of columns and rows (records) and these records change all the time. There will be always continuous changes to the database in form of increasing records or decreasing records or changes to the existing records. However at any particular time, there would be only one particular set of records exist in each of the object in a database; satisfying all the conditions of a database and that would be called an instance of database. 

In short; an instance of the database is the point in time status of the database at any given time.  An instance of the database in the morning might not be the same as another instance of the same database in the evening due to changes through the day.

Schema

Schema is the design part of the database. There are three common types of the schema

Physical Schema (How the tables physically join to each other to structure a database)

Logical Schema (How data will be stored in those tables to achieve the desired output)

View Schema (What users will see when access these database objects)

SQL constraints

SQL constraints are a set of rules applied to columns in a table. They are used to limit the types of data that can be stored in a table. It also ensures the accuracy and readability of the data in the database.

Constrains can apply to both the column and the entire table. The column constraints apply only to the column, while the table constrains apply to the entire table.

The following is a list of the most common constrains used in SQL:

 Some additional topics which we can cover in the RDBMS concepts 

Null

A null value in a table is a field that we see as empty. This means that this table field does not matter.

It is extremely important to understand that the NULL value is not 0 and not a field filled with spaces. 

A field with a NULL value is a field that remains empty when a record is created.

 

Data integrity

Each RDBMS has the following integrity categories:

Entity Integrity: 

There is no duplicate data in the table. This is achieved by making sure each row is unique entity in the table by using constrains like Primary Key, Unique Key and Not Null.

  

Domain Integrity: 

Uses valid entries for the specified column by limiting the type, format, or range of values defined beforehand. This is achieved by enforcing these rules using Check and Default constraints.

Referential Integrity:

Records that are used by other records cannot be deleted. This is achieved by using Foreign Key constraint.

User-defined Integrity:

These are integrity checks defined by users and these rules do not fit into the three categories described above.


This concludes our study of the basic concepts of RDBMS. In the next article, we will take a closer look at the basic syntax of SQL.

Below is a list of my favorite SQL books which I would recommend for you if you looking to enhance your knowledge on the topic.
SQL in 10 Minutes, Sams Teach YourselfSQL: The Ultimate Beginner's Guide to Learn SQL Programming Step by Step 
Learning SQL
SQL Cookbook (Cookbooks (O'Reilly))
SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL
T-SQL Fundamentals
Data Analysis Using SQL and Excel, 2nd Edition 


Below is my favourite Forums for SQL   
SQL Authority with Pinal Dave 
SQL Server Central Articles