Relational Model
Relational Model represents the database as a collection of relations. A relation is nothing but a table of values. Every row in the table represents a collection of related data values. These rows in the table denote a real-world entity or relationship. The table name and column names are helpful to interpret the meaning of values in each row. The data are represented as a set of relations. In the relational model, data are stored as tables. However, the physical storage of the data is independent of the way the data are logically organized.
Terms in Relational Model:
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 the entity. For example, in a database of employees, attributes might include “name,” “age,” “salary,” etc.
Tuple: Also known as a row or record, a tuple is a single instance of a relation or table in a database. It represents a complete set of data for a particular entity. Each tuple consists of a set 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 has the same set of attributes. In simpler terms, a relation is a table with rows and columns.
Domain: A domain is a set of possible values for a given attribute. It defines the type of data that an attribute can hold. For example, a domain for the attribute “age” might be integers between 0 and 150.
Degree: The degree of a relation refers to the number of attributes it contains. It represents the width or the number of columns in a table.
Cardinality: Cardinality describes the number of tuples or rows in a relation. It represents the length or the number of rows in a table.
Relational Schema: A relational schema defines the structure of a database, including the relations, attributes, and constraints. It provides a blueprint for how data is organized and represented in the database. It is a formal description of the structure of a relational database. It defines the tables (relations), their attributes (columns), and the relationships between them.
Syntax:
CREATE TABLE table_name (
column1 datatype constraints,
column2 datatype constraints, …
columnN datatype constraints
);
Relational Instance: A relational instance, also known as 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, often referred to simply as keys, are crucial elements in the design and management of relational databases. They establish relationships between tables and ensure data integrity.
Types of Relational Keys:
i) Primary Key: A primary key uniquely identifies each record in a table. It must be unique for each record and cannot contain null values. Typically, it’s a single column, but it can be a combination of columns as well.
ii) Foreign Key: A foreign key is a column or a set of columns in one table that refers to the primary key in another table. It establishes a link between two tables, enforcing referential integrity. Foreign keys ensure that data in related tables remains synchronized.
iii) Composite Key: A composite key is a combination of two or more columns used to uniquely identify records in a table. It’s essentially a primary key that consists of multiple columns.
iv) Unique Key: Similar to a primary key, a unique key ensures that all values in a column or a set of columns are unique. However, unlike a primary key, it can contain null values (but only one null per column). Unique keys can be used to enforce integrity constraints, though they’re not typically used for identifying records.
v) Alternate Key: An alternate key is a candidate key that wasn’t chosen as the primary key. It’s essentially a unique key that could have been selected as the primary key if the primary key wasn’t chosen.
Constraints in Relational Model
while designing the relational model, we define some conditions which must be hold for database present in the database, those conditions are called constraints. These constraints are checked before performing any operations (Insertion, deletion and Updation) in the database. If there is violation of constraints, the operation will fail.
There are 3 Types of constraints in Relational Database:
i) Domain constraints
These are attribute level constraints and attribute can only take the value lie inside the domain range. For Example: If the constraint is greater than zero and we apply negative value to student table, it will result in failure.
Domain constraint can be violated if an attribute value is not appending in the corresponding domain or it is not of the appropriate.
ii) Key Constraints
Every relation in the database should have at-least one set of attribute that defines tuple uniquely. Those set of attribute is called key. For Example: Roll number in the student database of same class is a key because 2 students can’t have same roll number.
The key should have 2 properties:
i) It should be unique for all tuple.
ii) It must not have null values.
iii) Referential Integrity Constraints
When an attribute of a relation can only take value from another attributes of the same or other relation, it is called referential integrity. They are based on the concept of foreign key. A foreign key is an important attribute of the relation that should be referred to another relation.
Properties of Relation:
i) Structure defines the representation of data
ii) Integrity imposes constraints on the data
iii) A relational data model describes data in the terms of relation
iv) A relational data model has 3 key components: Structure, Integrity and Language
v) Table describes the relationship among data
vi) Language provides means for accessing and manipulating data.
Mapping ER-Model to Relational Model:
When visualized into diagrams, the ER-Model provides an excellent overview of entity relationship that is simpler to understand.
ER-Diagram mainly consist of:
i) Entity and its attributes
ii) Relationship, which is association among entities
Mapping Entity
An entity is a real world object with some characteristics called attributes.
Mapping Process of Entity:
i) Create table for each entity
ii) Entity attribute should become fields of tables with their respective data types
iii) Declare primary Key
Mapping Relationship
A relationship is an association among entities. Relation shows how the entities are connected to each other and how they perform operations.
Mapping Process of Relation:
i) Create table for a relationship
ii) Add the primary key of all participating entities as fields of table with their
iii) If relationship has any attributes, add each attribute as a field of table
iv) Declare a primary key composing all the primary keys of participating entities
v) Declare all the foreign constraints
Mapping Weak Entity Sets
A weak entity is a entity which does not have any primary key associated with it.
In the example below Book is dependent upon publisher so book is weak entity.
Mapping Process of Weak Entity Sets:
i) Create table for weak entity set
ii) Add its al attribute to table as field
iii) Add the primary key to identify entity set
iv) Declare all foreign key constraints
Mapping Hierarchical Entities
Specialization or generalization comes in the form of hierarchical entity sets.
Mapping process of Hierarchical Entity sets
i) Create tables for all higher level entities
ii) Create tables for all lower level entities
iii) Add primary keys of higher level entities in the table of lower level entities
iv) In the lower level table, add all other attributes of lower level entities
v) Declare primary key of higher level table and the primary key of lower level table
vi) Declare foreign key constraints