Basics of RDBMS

Basics of RDBMS

We have installed Oracle and learned about Codd rules, now we should dive a little more and learn the basics of RDBMS (Relational Database Management System). These concepts are the same for all databases such as Oracle, SQL Server, Db2 etc. because they are driven by the Codd rules.

Basics of RDBMS

RDBMS is deals with data i.e. how data is stored and retrieved.

What is Data and Information

Data can be numbers, texts or images. Data is raw and has no meaning. Data is processed by humans and machines to derive information. Data is processed, organized, and structured to provide meaningful Information.

In IT, Data and Information are used interchangeably.

What is a table?

The data in a relational database is stored in database objects called tables. A table contains a group of associated data elements.  It is made up of columns and rows. These columns and rows contain fields.

A table is used for data storage in a relational database. A database can have one or more tables and these tables can be related using keys. The following is an example of an Employee table. It contains rows and columns pertaining to employees.

What is a field?

A table is made up of one or more fields. A field, also referred to as a column or attribute, stores specific information about a record in a table. In a table, a field or a column goes in the vertical direction and hold values of a particular type. For e.g. a field named First_Name will only contain the employees first names; it cannot be used to hold employees salary or phone number.

What is a Record or a Row?

A record is also called as a row of data, made up of fields. For example, there are 6 records in the above EMPLOYEES table. A record or row is always horizontal and contains a specific set of information regarding the table. The following is a single row of data or record in this table with 5 fields–

What are Constraints?

Constraints are rules specified for the data in a table. Constraints are used to limit the type of data that can be stored or inserted into a table. It ensures the accuracy and reliability of data in the table. If a database operation violates a constraint rule, an error is generated.

The following constraints are applied to a table or to a column in a table:

• Not Null: Specifies that this column cannot hold NULL values.
• Primary key:  Specifies the column that uniquely identifies a row in the table. The identified columns must be defined as NOT NULL.
• Unique: Specifies that values in the column must be unique.
• Foreign key: Specifies that the values in the column must correspond to values in a referenced primary key or unique key column or that they are NULL.
• Check: Specifies rules for values in the column.
• Index: An Index is created on a column for faster data access.

What is a Null Value?

Null values get special attention in all database systems. A field with a NULL value is a field with no or missing value. Null value is basically nothing, not even a space or an empty string.

What is Data Integrity?

Data integrity refers to the overall accuracy, completeness and consistency of data. It makes sure that data in the database is reliable. Data integrity is enforced by applying the checks and constraints on the database.

The following types of Data Integrity rules are imposed on a database:

• Entity Integrity: Each row in the table should be unique. There should not exist any duplicate rows in a table. This integrity rule is achieved by defining one or a group of columns as a Primary Key.
• Referential Integrity: Defined on a key comprising of a column or set of columns in one table. It guarantees that the values in that key match the values in a key in a related table (the referenced value). This integrity rule is imposed by defining foreign key in a related table. Two tables are in relation if they have matching values in the primary key and foreign key columns.
• Domain Integrity: This ensures that valid values are inserted into a given column. For e.g. Employee Name column cannot accept Salary values. This integrity rule is implemented by defining the appropriate data types for all the columns. Validity of entered values can also be maintained by defining proper constraints and checks.
• User defined Integrity: is enforced by defining a set of user-defined rules, which do not belong to any of the other three categories.

To understand the principles behind RDBMS or relational model, read Codd rules

To start hands-on practice on Oracle, install Oracle and start from today