Relational Model Constraints and Relational Database Schemas
Relational Model Constraints and Relational Database Schemas Constraints on databases can be categorized as follows: · inherent model-based: Example: no two tuples in a relation can be duplicates (because a relation is a set of tuples) · schema-based: can be expressed using DDL; this kind is the focus of this section. · application-based: are specific to the "business rules" of the miniworld and typically difficult or impossible to express and enforce within the data model. Hence, it is left to application programs to enforce. Elaborating upon schema-based constraints:
Domain Constraints: All the values that appear in a column of a relation must be taken from the same domain. A domain usually consists of the following components.
1. Domain Name
2. Meaning
3. Data Type
4. Size or length
5. Allowable values or Allowable range( if applicable)
6.Data Format
Key Constraints: A relation is a set of tuples, and each tuple's "identity" is given by the values of its attributes. Hence, it makes no sense for two tuples in a relation to be identical (because then the two tuples are actually one and the same tuple). That is, no two tuples may have the same combination of values in their attributes. Usually the miniworld dictates that there be (proper) subsets of attributes for which no two tuples may have the same combination of values. Such a set of attributes is called a superkey of its relation. From the fact that no two tuples can be identical, it follows that the set of all attributes of a relation constitutes a superkey of that relation. A key is a minimal superkey, i.e., a superkey such that, if we were to remove any of its attributes, the resulting set of attributes fails to be a
superkey. Example: Suppose that we stipulate that a faculty member is uniquely identified by Name and Address and also by Name and Department, but by no single one of the three attributes mentioned. Then { Name, Address, Department } is a (non-minimal) superkey and each of { Name, Address } and { Name, Department } is a key (i.e., minimal superkey).
Candidate key: any key!
Primary key: a key chosen to act as the means by which to identify tuples in a relation. Typically, one prefers a primary key to be one having as few attributes as possible. Relational Databases and Relational Database Schemas A relational database schema is a set of schemas for its relations together with a set of integrity constraints. A relational database state/instance/snapshot is a set of states of its relations such that no integrity constraint is violated. Entity Integrity, Referential Integrity, and Foreign Keys Entity Integrity Constraint: In a tuple, none of the values of the attributes forming the relation's primary key may have the (non-)value null. Referential Integrity Constraint: A foreign key of relation R is a set of its attributes intended to be used (by each tuple in R) for identifying/referring to a tuple in some relation S. (R is called the referencing relation and S the referenced relation.) For this to make sense, the set of attributes of R forming the foreign key should "correspond to" some superkey of S. Indeed, by definition we require this superkey to be the primary key of S. This constraint says that, for every tuple in R, the tuple in S to which it refers must actually be in S. Note that a foreign key may refer to a tuple in the same relation and that a foreign key may be part of a primary key . A foreign key may have value null in which case it does not refer to any tuple in the referenced relation. A set of attributes FK in relation schema R1 is a foreign key of R1 that references relation R2 if it satisfies following
two rules
1. The attributes in FK fave the same domain(s) as the primary key attributes PK of R2;the attributes FK are said to reference or refer to the relation R2
2. A value of FK in a tuple t1 of the current state r1(R1) either occurs as a value of PK for some tuple t2 in the current state r2(R2) or is null.In the former case ,we have t1[FK]=t2[PK], and we say that the tuple t1 references or refers to the tuple t2. Semantic Integrity Constraints: application-specific restrictions that are unlikely to be expressible in DDL. Examples:
· salary of a supervisee cannot be greater than that of her/his supervisor
· salary of an employee cannot be lowered
Update Operations and Dealing with Constraint Violations
For each of the update operations (Insert, Delete, and Update), we consider what kinds of constraint violations may result from applying it and how we might choose to react.
Insert: · domain constraint violation: some attribute value is not of correct domain
· entity integrity violation: key of new tuple is null
· key constraint violation: key of new tuple is same as existing one
· referential integrity violation: foreign key of new tuple refers to non-existent tuple Ways of dealing with it: reject the attempt to insert! Or give user opportunity to try again with different attribute values.
Delete:
· Referential integrity violation: a tuple referring to the deleted one exists.
Three options for dealing with it: · Reject the deletion
· Attempt to cascade (or propagate) by deleting any referencing tuples (plus those that reference them, etc., etc.)
· modify the foreign key attribute values in referencing tuples to null or to some valid value referencing a different tuple
Update:
· Key constraint violation: primary key is changed so as to become same as another tuple's
· referential integrity violation:
o foreign key is changed and new one refers to nonexistent tuple
o primary key is changed and now other tuples that had referred to this one violate the constraint
ER-to-Relational Mapping Algorithm
Step 1: Mapping of Regular Entity Types
Step 2: Mapping of Weak Entity Types
Step 3: Mapping of Binary 1:1 Relation Types
Step 4: Mapping of Binary 1:N Relationship Types.
Step 5: Mapping of Binary M:N Relationship Types.
Step 6: Mapping of Multivalued attributes.
Step 7: Mapping of N-ary Relationship Types
Step 1: Mapping of Regular Entity Types.
– For each regular (strong) entity type E in the ER schema, create a relation R that includes all the simple attributes of E.
– Choose one of the key attributes of E as the primary key for R. If the chosen key of E is composite, the set of simple attributes that form it will together form the primary key of R.
Example: We create the relations EMPLOYEE, DEPARTMENT, and PROJECT in the relational schema corresponding to the regular entities in the ER diagram. SSN, DNUMBER, and PNUMBER are the primary keys for the relations EMPLOYEE, DEPARTMENT, and PROJECT as shown.
Step 2: Mapping of Weak Entity Types
– For each weak entity type W in the ER schema with owner entity type E, create a relation R and include all simple attributes (or simple components of composite attributes) of W as attributes of R.
– In addition, include as foreign key attributes of R the primary key attribute(s) of the relation(s) that correspond to the owner entity type(s).
– The primary key of R is the combination of the primary key(s) of the owner(s) and the partial key of the weak entity type W, if any.
Example: Create the relation DEPENDENT in this step to correspond to the weak entity type DEPENDENT. Include the primary key SSN of the EMPLOYEE relation as a foreign key attribute of DEPENDENT (renamed to ESSN).
The primary key of the DEPENDENT relation is the combination {ESSN, DEPENDENT_NAME} because DEPENDENT_NAME is the partial key of DEPENDENT.
