Relational Model in DBMS

Relational model makes the query much easier than in hierarchical or network database systems. In 1970, E.F Codd has been developed it. A relational database is defined as a group of independent tables which are linked to each other using some common fields of each related table. This model can be represented as a table with columns and rows. Each row is known as a tuple. Each table of the column has a name or attribute. It is well knows in database technology because it is usually used to represent real-world objects and the relationships between them. Some popular relational databases are used nowadays like Oracle, Sybase, DB2, MySQL Server etc.

Relational Model Terminologies:

Following are the terminologies of Relational Model:

Let's explain each term one by one in detail with the help of example:

Example: STUDENT Relation

Relation: A relation is usually represented as a table, organized into rows and columns. A relationship consists of multiple records. For example: student relation which contains tuples and attributes.

Tuple: The rows of a relation that contain the values corresponding to the attributes are called tuples. For example: in the Student relation there are 5 tuples.

The value of tuples contains (10112, Rama, 9874567891,islam ganj, F) etc.

Data Item: The smallest unit of data in the relation is the individual data item. It is stored at the intersection of rows and columns are also known as cells. For Example: 10112, "Rama" etc are data items in Student relation.

Domain: It contains a set of atomic values that an attribute can take. It could be accomplish explicitly by listing all possible values or specifying conditions that all values in that domain must be confirmed. For example: the domain of gender attributes is a set of data values "M" for male and "F" for female. No database software fully supports domains typically allowing the users to define very simple data types such as numbers, dates, characters etc.

Attribute: The smallest unit of data in relational model is an attribute. It contains the name of a column in a particular table. Each attribute Ai must have a domain, dom(Ai). For example: Stu_No, S_Name, PHONE_NO, ADDRESS, Gender are the attributes of a student relation. In relational databases a column entry in any row is a single value that contains exactly one item only.

Cardinality: The total number of rows at a time in a relation is called the cardinality of that relation. For example: In a student relation, the total number of tuples in this relation is3 so the cardinality of a relation is 3. The cardinality of a relation changes with time as more and more tuples get added or deleted.

Degree: The degree of association is called the total number of attributes in a relationship. The relation with one attribute is called unary relation, with two attributes is known a binary relation and with three attributes is known as ternary relation. For example: in the Student relation, the total number of attributes is 5, so the degree of the relations is 5. The degree of a relation does not change with time as tuples get added or deleted.

Relational instance: In the relational database system, the relational instance is represented by a finite set of tuples. Relation instances do not have duplicate tuples.

Relational schema: A relational schema contains the name of the relation and name of all columns or attributes.

Relational key: In the relational key, each row has one or more attributes. It can identify the row in the relation uniquely.

Properties of Relations

• Each attribute in a relation has only one data value corresponding to it i.e. they do not contain two or more values.

• Name of the relation is distinct from all other relations.
• Each relation cell contains exactly one atomic (single) value
• Each attribute contains a distinct name
• Attribute domain has no significance
• tuple has no duplicate value
• Order of tuple can have a different sequence
• It also provides information about metadata.

Merits of Relational Model:

Following are the various merits of relational model:

• This provides an abstract view of the data. It abstracts the physical structure from the logical structure of data.
• This model is very easy to design. Tables can use different attributes as per requirements.
• The relational model supports data independence. In a relational database the data is stored in tables so that we can modify the data without changing the physical structure.
• Relational database helps the user to use a query language to query the database.
• It offers more flexibility than other models.
• By moving sensitive attributes, we can also implement database security control and authorization in a particular table into a separate relation with its authorization controls.
• Relational database helps the user to use a query language to query the database.
• A relational model consists of simple relationships. The characteristics of a database that make it immune to certain maintenance problems have been developed in the context of relational models.
• It is useful for representing most real world objects and the relationships between them. It is very easy to implement a relationship through the use of a composite key, so this model persistence method dominates the market.

Demerits of Relational Model:

Most of the drawbacks of the relational database is not because of the shortcoming but because of the way it is being implemented, we can avoid the drawbacks of the relational model by using proper designing techniques and proper database standards are enforced. Following are the various demerits of relational model:

• The main disadvantage of relational models is that they do not support binary data for example: images, documents, spreadsheets etc.
• The relational model can easily adapt to new hardware so incurs large hardware overhead.
• Relational databases use a simple mapping of logical tables to physical structures.
• This mostly limits performance and allows non-relational systems such as object oriented management systems to perform better on specialised applications such as CAD, CAM etc.
• Enforcing data integrity in relational models is difficult because no single piece of hardware has control over the data.
• The relational model is suitable for small databases but not suitable for complex databases because the user needs to know the complex physical data storage details. So, while designing the databases they don't come to light when they may cause problems. When a database grows it will slow down the system and will result in performance degradation and data corruption.

Operations on Relational Model:

List of the following basic operations that can be performed on a relational model:

• Insertion Operation
• Deletion Operation
• Update Operation
• Retrieval Operation

Let's explain each operation one by one.

Insert operation: It is used to insert a new record in the table. Adding new records to the table is much easier than other models. Data values will not be found in a relation when the following condition occurs:

• If we try to insert a duplicate value for the field that is selected as a primary key.
• If we insert a NULL value in the attribute that contains primary key.
• If we try to enter a data value in the foreign key attribute that does not exist in corresponding primary key attribute.
• If an attribute is assigned a value that does not exist in the corresponding domain.

DELETE operation: This operation is used to delete records from the table but problems arise when the rows to be deleted have some attributes which are foreign key attributes.

Update operation: It is used to modify or change the data value of a record in a table. Updating an attribute that is neither a primary key nor a foreign key requires only checking that the new value is of the correct data type and domain. If we modify a data value of a primary key and foreign key attribute then need to check:

• The modified value does not contain the value of the corresponding foreign key value.
• The new values must not already exist in the table.

Retrieval operation: It is used to save a record from a relation. This operation is very simple and homogeneous.