Title Unit V - Database Constraints and Relational Database Design – Database Management System.pdf ✅

www.ckundan.com.np 1 Unit V: Database Constraints and Relational Database Design – Database Management System Introduction of Database Constraints: Constraints are a very important feature in a relational model. In fact, the relational model supports the well-defined theory of constraints on attributes or tables. Constraints are useful because they allow a designer to specify the semantics of data in the database. Constraints are the rules that force DBMSs to check that data satisfies the semantics. Constraints in the databases can be categorized into 3 main categories: 1. Constraints that are applied in the data model is called implicit constraints. 2. Constraints that are directly applied in the schemas of the data model, by specifying them in the DDL (Data Definition Language). These are called as schema-based constraints or explicit constraints. 3. Constraints that cannot be directly applied in the schemas of the data model. We call these Application based or semantic constraints. Types of Integrity Constraints: 1. Domain Constraints: Every domain must contain atomic values (smallest indivisible units) it means composite and multi-valued attributes are not allowed.
www.ckundan.com.np 2 We perform datatype check here, which means when we assign a data type to a column we limit the values that it can contain. Example: 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 Ramesh 9807654097 9868754382 In the above relation, Name is a composite attribute and Phone is a multi-values attribute, so it is violating domain constraint. 2. Entity Integrity Constraints: Entity Integrity constraints says that no primary key can take NULL value, since using primary key we identify each tuple uniquely in a relation. Example: EID NAME PHONE 01 Bikash 9807654321 02 Paul 9865408765 NULL Sony 9826475890 In the above relation, EID is made primary key, and the primary key cant take NULL values but in the third tuple, the primary key is null, so it is a violating Entity Integrity constraints. 3. Referential Integrity Constraints: The Referential integrity constraints is specified between two relations or tables and used to maintain the consistency among the tuples in two relations. This constraint is enforced through foreign key, when an attribute in the foreign key of relation R1 have 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. 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.
www.ckundan.com.np 3 Example: EID NAME DNO 01 Divine 12 02 Dino 22 04 Vivian 14 DNO PLACE 12 Pokhara 13 Kathmandu 14 Biratnagar In the above, DNO of the first relation is the foreign key, and DNO in the second relation is the primary key. DNO = 22 in the foreign key of the first table is not allowed since DNO = 22 is not defined in the primary key of the second relation. Therefore Referential integrity constraints is violated here. 4. Key Constraints or Uniqueness Constraints: These are called uniqueness constraints since it ensures that every tuple in the relation should be unique. A relation can have multiple keys or candidate keys(minimal superkey), out of which we choose one of the keys as 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. Null values are not allowed in the primary key, hence Not Null constraint is also a part of key constraint. Example: EID NAME PHONE 01 Bikash 9870987765 02 Paul 9846740876 01 Tuhin 9870986634 In the above table, EID is the primary key, and first and the last tuple has the same value in EID i.e. 01, so it is violating the key constraint.