Relational Database Management Systems In Logical Database Design

Relational DBMS:

The term relational database was originally defined by Edgar Cod at IBM Research Center in 1970. Relational database as implemented in relation database management system have become a predominant choice for the storage of information in new databases used for financial records manufacturing and logistical information personnel data and much more.

In the relational database the user only needs to understand the logical structure of data not how it is physically stored. In this database data is represented in simple two-dimensional tables which consists of rows and columns. A relational database is simply a collection of tables.

Relational database theory uses a set of mathematical terms which are roughly equivalent to SQL database terminology.

Relational Database Basic Concepts:

Relations Or Tables:

A relation is defined as a set of tuples that have the same attributes. A tuple usually represents an object and information about that objects are typically physical object or concepts. A relation is usually described as a table which is organized into rows and columns. All the data referenced by an attribute are in the same domain and conform to the same constraints.

Applications access data by specifying queries which use operations such as select to identify tuples project to identify attributes and join to combine relation. Relations can be modified using the insert, delete and update operators. New tuples can supply explicit values or be derived from a query. Similarly queries identify tuples for updating or deleting. It is necessary for each tuple of a relation to be uniquely identifiable by some combination of its attribute values. This combination is referred to as the primary key.

Base and derived relations:

In a relational database all data is stored and accessed via relations. Relations that store data are called base relations and in implementations are called tables. Other relations do not store data but are computed by applying relational operations to other relations. These relations are sometimes called derived relations. In implementations these are called views or queries. Derived relations are convenient in that though they may grab information from several relations they act as a single relation. Also derived relations can be used as an abstraction layer.

Tuple | Record | Row :

 A record holds all the information about one item or subject. Conceptually if you collected business cards from 50 people all 50 cards would represent a table and the information on any one business card would represent one record. In a database table each row is a record or tuple.

Field/Column:

A field holds one piece of information about an item or subject. Since a field is a column in a database table some database experts prefer to just use the word column. In the student table there are fields for Roll_No, Name, Address etc. In a relational database the relationships are maintained by having matching fields in two tables. For instance the student table and the result table share the Roll_No field, thus allowing the two tables to be linked and data to be pulled from the two tables together. 

Domain:

 A domain describes the set of possible values for a given attribute. Because a domain constrains the attributes values and name it can be considered constraints. Mathematically attaching a domain to an attribute means that all values for this attribute must be an element of the specified set.

The character data value ABC for instance is not in the integer domain. The integer value 123 satisfies the domain constraint.

Constraints:

Constraints allow you to further restrict the domain of an attribute. For instance a constraint can restrict a given integer attribute to values between 1 and 10. Constraints provide one method of implementing business rules in the database. SQL implements constraint functionality in the form of check constraints.

Constraints restrict the data that can be stored in relations. These are usually defined using expressions that result in a value indicating whether or not the data satisfies the constraint. Constraints can apply to single attributes to a tuple or to an entire relation.

Every attribute has an associated domain. It is called as domain constraints. The two principal rules for the relational model are known as entity integrity and referential integrity.

Data type:

Every field in a database table is assigned a data type which describes the kind of data that can be stored in the field. Data types vary depending on the database system you are working in.

Primary Key:

A primary key is a field that uniquely identifies each record in the table from every other record in the table. In our student table it is Roll_no. Each student has an Roll_no that is different from every other student in the table. Primary keys are essential in a relational database.

Foreign Key:

A foreign key is a reference to a key in another relation meaning that the referencing tuple has as one of its attributes the values of a key in the referenced tuple. Foreign key effectively use the values of attributes in the referenced relation to restrict the domain of one or more attributes in the referencing relation.

A foreign key could be described formally as for all tuples in the referencing relation projected over the referencing attributes there must exist a tuple in the referenced relation projected over those same attributes such that the values in the referenced attributes. For example the subject_id, primary key of subject table is used as foreign key in Time_Table table.

Stored Procedure:

A stored procedure is a high end database tool that adds programming power into the database. A stored procedure is executable code that is associated with and generally stored in the database. Database administrators will often create stored procedures to handle inserts, edits and updates of records. The front-end programmer then only needs to call the stored procedure to accomplish these functions. It makes the programming code simpler and helps protect the database from problems caused by program bugs.

Indices:

An index is one way of providing quicker access to data. Indices can be created on any combination of attributes on a relation. Queries that filter using those attributes can find matching tuples randomly using the index without having to check each tuple in turn. Relational databases typically supply multiple indexing techniques each of which is optimal for some combination of data distribution, relation size and typical access pattern. Different indexing techniques are B-tress, B+ trees.

Normalization:

Normalization was first proposed by cod as an integral part of the relational model. It is used to eliminate the duplication of data which in turn prevents data manipulation anomalies and loss of data integrity. The most common forms of normalization applied to databases are called the normal forms.

Characteristics of Relational DBMS:

The relational Model eliminated all parent child relationships and instead represented all data in the database as simple row/column tables of data values.

A relation is similar to a table rows/column of data values. Each table is an independent entity and there is no physical relationship between tables.

Most data management systems based on the relational model have a built-in support for query languages like ANSI SQL. These queries are simple English constructs that allow data manipulation from a table.

Relational model of data management is based on set theory. The user interface used with relational models is non-procedural because only what needs to be done is specified and not how it has to be done.