Relational Model

The Relational Model represents a database as a collection of relations.

A relation is simply a table of values.

Every row in a table represents a collection of related data values. These rows may represent a real-world entity or relationship.

The table name and column names help us understand the meaning of the values stored in each row.

In the relational model:

• Data is represented as a set of relations.
• Data is stored in the form of tables.
• The physical storage of data is independent of how the data is logically organized.

Terms in Relational Model

Important terms used in the relational model include:

1. Attribute
2. Tuple
3. Relation
4. Domain
5. Degree
6. Cardinality
7. Relational Schema
8. Relational Instance

Attribute

An Attribute is a characteristic or property of an entity or object in a database.

It represents a particular piece of data that describes an entity.

Example:

In an employee database, attributes may include:

• Name
• Age
• Salary

Tuple

A Tuple, also known as a row or record, is a single instance of a relation or table.

It represents a complete set of data for a particular entity.

Each tuple consists of attribute values corresponding to the attributes defined for that relation.

Relation

A Relation is a mathematical concept that represents a table in a relational database.

It consists of a set of tuples where each tuple contains the same set of attributes.

In simple terms, a relation is a table containing:

• Rows
• Columns

Domain

A Domain is a set of possible values that can be assigned to a particular attribute.

It defines the type or range of data that an attribute can hold.

Example:

For an attribute called Age, the domain may contain integer values between:

0 and 150

Degree

The Degree of a Relation refers to the number of attributes in the relation.

In other words, it represents the number of columns in a table.

Cardinality

Cardinality refers to the number of tuples or rows present in a relation.

In other words, it represents the number of rows in a table.

Relational Schema

A Relational Schema defines the structure of a database.

It includes:

• Relations
• Attributes
• Constraints
• Relationships

It provides a blueprint for how data is organized and represented in a relational database.

A relational schema formally defines:

• Tables or relations
• Columns or attributes
• Relationships between tables

Syntax

CREATE TABLE table_name (

column1 datatype constraints,

column2 datatype constraints,

…

columnN datatype constraints

);

Relational Instance

A Relational Instance, also called a Relation Instance or Table Instance, refers to the actual set of tuples or rows that populate a relation at a particular point in time.

It represents the current state of the database.

Relational Keys

Relational Keys, commonly called keys, are important elements in the design and management of relational databases.

They are used to:

• Identify records
• Establish relationships between tables
• Maintain data integrity

Types of Relational Keys

The major types of relational keys are:

1. Primary Key
2. Foreign Key
3. Composite Key
4. Unique Key
5. Alternate Key

1. Primary Key

A Primary Key uniquely identifies each record in a table.

A primary key:

• Must be unique for each record.
• Cannot contain NULL values.
• May consist of one column or a combination of columns.

2. Foreign Key

A Foreign Key is a column or set of columns in one table that refers to the primary key of another table.

It creates a link between two tables.

Foreign keys help enforce referential integrity and ensure that data in related tables remains synchronized.

3. Composite Key

A Composite Key is a combination of two or more columns used to uniquely identify a record in a table.

It is essentially a primary key consisting of multiple columns.

4. Unique Key

A Unique Key ensures that all values in a column or set of columns are unique.

According to the provided notes:

• It can contain NULL values.
• Only one NULL value is allowed per column.
• It can be used to enforce integrity constraints.

Unlike a primary key, a unique key is not normally used as the main identifier for records.

5. Alternate Key

An Alternate Key is a candidate key that was not selected as the primary key.

It is a unique key that could have been selected as the primary key.

Constraints in Relational Model

While designing a relational model, certain conditions are defined that must be satisfied by the data stored in the database.

These conditions are called Constraints.

Constraints are checked before performing operations such as:

• Insertion
• Deletion
• Updation

If a constraint is violated, the database operation will fail.

Types of Constraints in Relational Database

There are three main types of constraints:

1. Domain Constraints
2. Key Constraints
3. Referential Integrity Constraints

1. Domain Constraints

Domain Constraints are attribute-level constraints.

An attribute can only contain values that lie within its defined domain or range.

Example:

Suppose the value of an attribute must be greater than zero.

If a negative value is entered into the student table, the operation will fail.

A domain constraint can be violated when:

• An attribute value does not belong to its corresponding domain.
• The value is not of the appropriate type.

2. Key Constraints

Every relation in a database should have at least one set of attributes that can uniquely identify each tuple.

Such a set of attributes is called a Key.

Example:

The roll number in a student database can be used as a key because two students in the same class cannot have the same roll number.

Properties of a Key

A key should have two important properties:

1. It should be unique for all tuples.
2. It must not contain NULL values.

3. Referential Integrity Constraints

When an attribute of one relation can only take values from another attribute of the same or another relation, it is called Referential Integrity.

Referential integrity is based on the concept of a Foreign Key.

A foreign key is an important attribute of a relation that refers to another relation.

Properties of Relation

Important properties of the relational model include:

1. Structure defines the representation of data.
2. Integrity imposes constraints on the data.
3. A relational data model describes data in terms of relations.
4. A relational data model has three key components:
   • Structure
   • Integrity
   • Language
5. Tables describe relationships among data.
6. Language provides a means for accessing and manipulating data.

Mapping ER Model to Relational Model

When represented through diagrams, the ER Model provides a clear overview of entity relationships that is easier to understand.

An ER Diagram mainly consists of:

1. Entities and their attributes
2. Relationships or associations among entities

The ER Model can then be mapped into the Relational Model.

Mapping Entity

An Entity is a real-world object having certain characteristics called attributes.

Mapping Process of Entity

The process of mapping an entity into the relational model includes:

1. Create a table for each entity.
2. Entity attributes should become fields of the table with their respective data types.
3. Declare the primary key.

Mapping Relationship

A Relationship is an association among entities.

A relationship shows:

• How entities are connected.
• How entities interact with each other.

Mapping Process of Relationship

The mapping process of a relationship includes:

1. Create a table for the relationship.
2. Add the primary keys of all participating entities as fields of the table.
3. If the relationship has any attributes, add each attribute as a field of the table.
4. Declare a primary key composed of the primary keys of participating entities.
5. Declare all foreign key constraints.

Mapping Weak Entity Sets

A Weak Entity is an entity that does not have a primary key of its own.

According to the provided example, Book is dependent upon Publisher, so Book is considered a weak entity.

Mapping Process of Weak Entity Sets

The mapping process includes:

1. Create a table for the weak entity set.
2. Add all its attributes to the table as fields.
3. Add the primary key of the identifying entity set.
4. Declare all foreign key constraints.

Mapping Hierarchical Entities

Specialization or Generalization may appear in the form of hierarchical entity sets.

Mapping Process of Hierarchical Entity Sets

The process includes:

1. Create tables for all higher-level entities.
2. Create tables for all lower-level entities.
3. Add the primary keys of higher-level entities to the tables of lower-level entities.
4. In the lower-level table, add all other attributes of the lower-level entity.
5. Declare the primary key of the higher-level table and the primary key of the lower-level table.
6. Declare foreign key constraints.