Relational Model in DBMS

The relational model is very simple and elegant; a database is a collection of one or more relations, where each relation is a table with rows and columns. This simple tabular representation enables even novice users to understand the contents of a database, and it permits the use of simple, high-level languages to query the data.

The main construct for representing data in the relational model is a relation. A relation consists of a relation schema and a relation instance. The relation instance is a table, and the relation schema describes the column heads for the table. We first describe the relation schema and then the relation instance. The schema specifies the relation's name, the name of each field (or column, or attribute), and the domain of each field. A domain is referred to in a relation schema by the domain name and has a set of associated values.

Students(sid: string, name: string, login: string, age: integer, gpa: real)

This says, for instance, that the field named sid has a domain named string. The set of values associated with domain string is the set of all character strings.

Integrity Constraints over Relations

A database management system (DBMS) is crucial in preventing incorrect information entry and maintaining the quality of the stored data. An integrity constraint (IC) is a condition specified on a database schema that restricts the data that can be stored in an instance of the database. A DBMS enforces these constraints, allowing only legal instances to be stored in the database.

Integrity constraints are specified and enforced at different times:

• When the DBA or end user defines a database schema, he or she specifies the ICs that must hold on any instance of this database. 
• When a database application is run, the DBMS checks for violations and disallows changes to the data that violate the specified ICs.

Key Constraints

The Students relation is an example of a key constraint, where no two students have the same student ID. This constraint states that a minimal subset of the fields of a relation is a unique identifier for a tuple. A candidate key, often abbreviated to just key, uniquely identifies a tuple according to this constraint. In the Students relation, the set of fields containing the student ID field is a candidate key.

Let us take a closer look at the above definition of a (candidate) key. There are two parts to the definition:

• Two distinct tuples in a legal instance cannot have identical values in all the fields of a key. 
• No subset of the set of fields in a key is a unique identifier for a tuple.

CREATE TABLE Students (sid VARCHAR(20),
		name VARCHAR(30),
		login VARCHAR(20),
		age INTEGER,
		gpa REAL,
		UNIQUE (name, age),
		CONSTRAINT StudentsKey PRIMARY KEY (sid) )

This definition states that sid is the primary key and that the combination of name and age is an additional key. The specification of the primary key also demonstrates how to designate a constraint by prefixing it with CONSTRAINT constraint-name. Upon violation of the constraint, the name of the constraint is returned, facilitating the identification of the error.

Foreign Key Constraints

Occasionally, the data contained in one connection is associated with the data in another relation. If one relation is altered, the other must be verified and perhaps adjusted to maintain data consistency. An integrity constraint covering both relations must be specified for a DBMS to do such verifications. The predominant integrity constraint involving two relations is a foreign key constraint.

To ensure only genuine students can enroll in courses, any value in the Enrolled field should also appear in the Students field. This foreign key refers to Students and must match the primary key of the referenced relation. The foreign key must have the same number of columns and compatible data types, although column names can vary.

CREATE TABLE Enrolled ( sid VARCHAR(20),
		cid VARCHAR(20),
		grade VARCHAR(10),
		PRIMARY KEY (sid, cid),
		FOREIGN KEY (sid) REFERENCES Students )

General Constraints

Domain, primary key, and foreign key constraints are essential components of relational data models, particularly in commercial systems. However, sometimes more general constraints are necessary, such as requiring student ages within a certain range. This helps prevent data entry errors by rejecting inserts and updates that violate the constraint. For example, if all students must be at least 16 years old, the instance of two underage students is illegal. However, if these constraints are disallowed, a legal instance is obtained.

---

Happy Exploring!