Structure of Relational Databases

STRUCTURE OF RELATIONAL DATABASE

What is Relational Model?

Relational Model represents how data is stored in Relational Databases. A relational database stores data in the form of relations (tables). Consider a relation STUDENT with attributes ROLL_NO, NAME, ADDRESS, PHONE and AGE.

Table : 2-Dimensional structure .(rows and columns)

• Attribute: Attributes are the properties that define a relation. e.g.; ROLL_NO, NAME 

• Relation Schema: A relation schema represents name of the relation with its attributes. e.g.; STUDENT (ROLL_NO, NAME, ADDRESS, PHONE and AGE) is relation schema for STUDENT. If a schema has more than 1 relation, it is called Relational Schema. 

• Tuple: Each row in the relation is known as tuple. 

• Relation Instance: The set of tuples of a relation at a particular instance of time is called as relation instance. Table 1 shows the relation instance of STUDENT at a particular time. It can change whenever there is insertion, deletion or updation in the database. 

• Degree: The number of attributes in the relation is known as degree of the relation. The STUDENT relation defined above has degree 5. 

• Cardinality: The number of tuples in a relation is known as cardinality. The STUDENT relation defined above has cardinality 4. 

• Column: Column represents the set of values for a particular attribute. The column ROLL_NO is extracted from relation STUDENT.

Constraints in Relational Model

1. Domain Constraints 

• Every domain must contain atomic values(smallest indivisible units) which means composite and multi-valued attributes are not allowed.
• We perform a datatype check here, which means when we assign a data type to a column we limit the values that it can contain. Eg. If we assign the datatype of attribute age as int, we can’t give it values other than int datatype.  
• Example: 

EID	Name	Phone
01	Bikash Dutta	123456789 234456678

2. Key Constraints or Uniqueness Constraints

1. These are called uniqueness constraints since it ensures that every tuple in the relation should be unique.
2. A relation can have multiple keys or candidate keys(minimal superkey), out of which we choose one of the keys as the primary key, we don’t have any restriction on choosing the primary key out of candidate keys, but it is suggested to go with the candidate key with less number of attributes.
3. Null values are not allowed in the primary key, hence Not Null constraint is also part of the key constraint.

Example: 

EID	Name	Phone
01	Bikash	6000000009
02	Paul	9000090009
01	Tuhin	9234567892

3. Entity Integrity Constraints: 

1. Entity Integrity constraints say that no primary key can take a NULL value, since using the primary key we identify each tuple uniquely in a  relation.

Example: 

EID	Name	Phone
01	Bikash	9000900099
02	Paul	600000009
NULL	Sony	9234567892

4. Referential Integrity Constraints

1. The Referential integrity constraint is specified between two relations or tables and used to maintain the consistency among the tuples in two relations.
2. This constraint is enforced through a foreign key, when an attribute in the foreign key of relation R1 has the same domain(s) as the primary key of relation R2, then the foreign key of R1 is said to reference or refer to the primary key of relation R2.
3. The values of the foreign key in a tuple of relation R1 can either take the values of the primary key for some tuple in relation R2, or can take NULL values, but can’t be empty.

Example:

EID	Name	DNO
01	Divine	12
02	Dino	22
04	Vivian	14