Constraints in Relational Model
While designing the Relational Model, we define some conditions which must hold for data present in the database are called Constraints. These constraints are checked before performing any operation (insertion, deletion, and updation ) in the database. If there is a violation of any of the constraints, the operation will fail.
Domain Constraints
These are attribute-level constraints. An attribute can only take values that lie inside the domain range. e.g.; If a constraint AGE>0 is applied to STUDENT relation, inserting a negative value of AGE will result in failure.
Key Integrity
Every relation in the database should have at least one set of attributes that defines a tuple uniquely. Those set of attributes is called keys. e.g.; ROLL_NO in STUDENT is key. No two students can have the same roll number. So a key has two properties:
- It should be unique for all tuples.
- It can’t have NULL values.
Referential Integrity
When one attribute of a relation can only take values from another attribute of the same relation or any other relation, it is called referential integrity. Let us suppose we have 2 relations
Table Student
ROLL_NO | NAME | ADDRESS | PHONE | AGE | BRANCH_CODE |
---|---|---|---|---|---|
1 | RAM | DELHI | 9455123451 | 18 | CS |
2 | RAMESH | GURGAON | 9652431543 | 18 | CS |
3 | SUJIT | ROHTAK | 9156253131 | 20 | ECE |
4 | SURESH | DELHI | 18 | IT |
Table Branch
BRANCH_CODE | BRANCH_NAME |
---|---|
CS | COMPUTER SCIENCE |
IT | INFORMATION TECHNOLOGY |
ECE | ELECTRONICS AND COMMUNICATION ENGINEERING |
CV | CIVIL ENGINEERING |
BRANCH_CODE of STUDENT can only take the values which are present in BRANCH_CODE of BRANCH which is called referential integrity constraint. The relation which is referencing another relation is called REFERENCING RELATION (STUDENT in this case) and the relation to which other relations refer is called REFERENCED RELATION (BRANCH in this case).
Relational Model in DBMS
E.F. Codd proposed the relational Model to model data in the form of relations or tables. After designing the conceptual model of the Database using ER diagram, we need to convert the conceptual model into a relational model which can be implemented using any RDBMS language like Oracle SQL, MySQL, etc. So we will see what the Relational Model is.
The relational model uses a collection of tables to represent both data and the relationships among those data. Each table has multiple columns, and each column has a unique name. Tables are also known as relations. The relational model is an example of a record-based model. Record-based models are so named because the database is structured in fixed-format records of several types. Each table contains records of a particular type. Each record type defines a fixed number of fields, or attributes. The columns of the table correspond to the attributes of the record type. The relational data model is the most widely used data model, and a vast majority of current database systems are based on the relational model.