entity integrity in database management system relational model

Entity integrity in DBMS, Define entity integrity, entity integrity examples

Entity Integrity

Entity integrity and referential integrity are the basic set of rules for the relational model. Entity integrity defines the properties that are to be satisfied by a primary key.

We all know that a primary key is UNIQUE + NOT NULL. That is, it cannot contain duplicate values and NULL values. This is what entity integrity is all about.

Entity integrity - No component of a primary key is allowed to have NULL values.

Example 1:

STUDENT (Register_Number, Name, Date_Of_Birth, Phone)

In this table STUDENT, the attribute Register_Number is the primary key. Hence, it cannot contain NULL value in it.

Example 2:

STUDENT_COURSE (Register_Number, Subject_Code, Registered_Date)

Assume that the table given above is to store the subjects that are registered by a student. A student may register one or more subjects. Hence, the key for this table is a composite key which consists of (Register_Number, Subject_Code).

In this case, neither Register_Number nor Subject_Code can contain NULL values.

**************

Go to Important Keywords/Terms in DBMS page

Go to Advanced DBMS concepts page

referential integrity in database management system relational model

Referential integrity in DBMS, Define referential integrity, referential integrity examples

Referential Integrity

Entity integrity and referential integrity are the basic set of rules for the relational model. Referential integrity defines the relationship between tables (entity sets).

This property is shown by a foreign key. Foreign key is an attribute whose values match with the primary key values of related table.

Referential integrity – If there is a foreign key value stored in a table (for example, A), then there must be a matching primary key value stored in another table (for example, B) that is linked with the foreign key table.

For each distinct non-null foreign key value in a relational database, there must exist a matching primary key value from the same domain.

Example

VENDOR (Vendor_Number, VName, VAddress, Contact_Number)

VENDOR_BILL (Bill_Number, Bill_Date, Vendor_Num)

Here, Vendor_Num of VENDOR_BILL table refers to the values of Vendor_Number of VENDOR table. So,

Vendor_Number of VENDOR is the Primary key

Vendor_Num of VENDOR_BILL is the Foreign key

The following are the permitted values for Vendor_Num of VENDOR_BILL table;

• Vendor numbers that are already stored in the VENDOR table’s Vendor_Number attribute.

• A NULL value.

 

Vendor_Number	VName	VAddress	Contact_Number
V101	AB Ltd	Chennai	8565957542
V103	SS Ltd	Bengaluru	9859621432

Table 1: VENDOR (primary key table)

Bill_Number	Bill_Date	Vendor_Num
B1	12-Jun-2017	V101
B2	10-Jul-2017	NULL
B5	12-Jul-2017	V102

Table 2: VENDOR_BILL (foreign key table)

Please observe from the above tables, that ‘V101’ is permitted value. Null is also a permitted value. But ‘V102’ is not. It is due to the reason that the value ‘V102’ is not stored in Vendor_Number of Vendor table.

*************

Go to Important Keywords/Terms in DBMS page

Go to Advanced DBMS concepts page

Reduce the ER diagram to relation table solved exercise

How to reduce an ER diagram to tables? Reduction of ERD to relation schema, Convert Entity Relationship diagram to set of tables, ERD to relation schema examples, mapping ER diagram to relational tables

Reduce (convert) the following ER diagram to relational schema

ER components	Given component	Result
Strong Entity Set Rule: Strong entity set can be directly converted into table.	(a) STUDENT (b) SUBJECT (c) CLASS	(a) STUDENT (Student_ID, Student_Name, DOB, Address)  (b) SUBJECT (Subject_ID, Subject_Name, Teacher) (C) CLASS (Class_ID, Class_Name)
Derived attribute Rule: No need to create a column in the table for derived attribute.	Age in STUDENT table	No changes
Composite attribute Rule: Replace the composite attribute with its component attributes.	Address in STUDENT table	STUDENT (Student_ID, Student_Name, DOB, Door, Street, City, Pin)
1-1, 1-n, and n-1 Relationships Rule: Include the primary key of one side entity set as the foreign key of other side entity set.	Attends (1-1 from STUDENT to CLASS) Studies (1-n from STUDENT to SUBJECT)	CLASS (Class_ID, Class_Name, Student_ID) SUBJECT (Subject_ID, Subject_Name, Teacher, Student_ID)
Descriptive attribute Rule: An attribute that is part of a relationship is descriptive. Include the descriptive attributes to 1 side as shown above.	DateOfJoin, Hours# of Attends relationship.	CLASS (Class_ID, Class_Name, Student_ID, DateOfJoin, Hours#)
Weak entity set Rule: Weak entity set is totally participated (existence dependent) on the strong entity set. Include the primary key of strong entity set into the weak entity set as foreign key.	(d) SECTION	SECTION (Section_ID, Section_Name, Class_ID)
Weak relationship Rule: No need to create as a table. If created, then the table is redundant.	Has	No changes

Final set of relation schemas: (Primary keys are underlined)

STUDENT (Student_ID, Student_Name, DOB, Door, Street, City, Pin)

CLASS (Class_ID, Class_Name, Student_ID, DateOfJoin, Hours#)

Student_ID is the foreign key refers STUDENT table

SUBJECT (Subject_ID, Subject_Name, Teacher, Student_ID)

Student_ID is the foreign key refers STUDENT table

SECTION (Section_ID, Class_ID, Section_Name)

Class_ID is the foreign key refers CLASS table

*********************

Go to Solved ER exercises page