Question

Relational Data Model and Language: Relational data model concepts, integrity constraints, Keys domain constraints, referential integrity, assertions, triggers, foreign key relational algebra, relational calculus, domain and tuple calculus, SQL data definition queries and updates in SQL.

Answer 1

Relational Data Model and Language

Relational Model : Relational Model represents how data is stored in Relational Databases.  A relational database stores data in the form of relations (tables). Relational data model is the primary data model, which is used widely around the world for data storage and processing. This model is simple and it has all the properties and capabilities required to process data with storage efficiency.

Relational Model Concepts

1. Attribute : Each column in a Table. Attributes are the properties which define a relation. e.g., Student_Rollno, NAME,etc.

2. Tables :  In the Relational model the, relations are saved in the table format. It is stored along with its entities. A table has two properties rows and columns. Rows represent records and columns represent attributes.

3. Tuple :  It is nothing but a single row of a table, which contains a single record.

4. Relation Schema : A relation schema represents the name of the relation with its attributes.

5. Degree : The total number of attributes which in the relation is called the degree of the relation.

6. Cardinality : Total number of rows present in the Table.

7. Column : The column represents the set of values for a specific attribute.

8. Relation instance : Relation instance is a finite set of tuples in the RDBMS system. Relation instances never have duplicate tuples.

9. Relation key :  Every row has one, two or multiple attributes, which is called relation key.

10. Attribute domain :  Every attribute has some pre-defined value and scope which is known as attribute domain

Integrity Constraints

Integrity constraints are a set of rules. It is used to maintain the quality of information.Integrity constraints ensure that the data insertion, updating, and other processes have to be performed in such a way that data integrity is not affected.Thus, integrity constraint is used to guard against accidental damage to the database.There are four types of integrity Constraints

1. Domain Constraint
2. Entity Integrity Constraint
3. Key Constraint
4. Referential Integrity Constraint

Domain Constraints : Domain constraints can be violated if an attribute value is not appearing in the corresponding domain or it is not of the appropriate data type.Domain constraints specify that within each tuple, and the value of each attribute must be unique. This is specified as data types which include standard data types integers, real numbers, characters, Booleans, variable length strings, etc.

Example: Create DOMAIN CustomerName CHECK (value not NULL)

The example shown demonstrates creating a domain constraint such that CustomerName is not NULL

Key constraints : An attribute that can uniquely identify a tuple in a relation is called the key of the table. The value of the attribute for different tuples in the relation has to be unique.

Example: In the given table, CustomerID is a key attribute of Customer Table. It is most likely to have a single key for one customer, CustomerID =1 is only for the CustomerName =" Google".

CustomerID	CustomerName	Status
1	G  oogle	Active
2	Amazon	Active
3	Apple	Inactive

Referential integrity constraints :  Referential integrity constraints is base on the concept of Foreign Keys. A foreign key is an important attribute of a relation which should be referred to in other relationships. Referential integrity constraint state happens where relation refers to a key attribute of a different or same relation. However, that key element must exist in the table. 

Example : suppose we have two tables EMPLOYEE(emp_id,emp_name,phone,address) and another table be DEPARTMENT(emp_id,dept_id,dept_name,subjects) then emp_id is the primary key of table EMPLOYEE whereas in table DEPARTMENT emp_id is a Foreign key . 

Entity integrity constraints : The entity integrity constraint states that primary key value can't be null.This is because the primary key value is used to identify individual rows in relation and if the primary key has a null value, then we can't identify those rows.A table can contain a null value other than the primary key field.

Example : suppose we have a table EMPLOYEE(emp_id,emp_name,phone,address)  here emp_id is a primary key so values in emp_id cannot be null because primary key cant be null.

Triggers 

A SQL trigger is a database object just like a stored procedure, or we can say it is a special kind of stored procedure which fires when an event occurs in a database. We can execute a SQL query that will "do something" in a database when an event is fired.

To know more about triggers Click Here 

---

For Relational algebra Click Here

---

For more Delhi Technical University(DTU) CSE-IV Database Management System Lab Experiments Click Here