Relational Model
Relational Database Constraints
Chapter 5 – part II
Copyright By PowCoder代写 加微信 powcoder
Relational Integrity Constraints
Constraint rules are conditions on the DB that ensure integrity on our data for any state of the DB:
Single relation
Domain constraints
Entity integrity constraints
Key constraints
Two or more relations
Referential integrity constraints
Domain Constraint
Every value in a tuple must be either:
An element from the domain of its attribute
null, if allowed for that attribute
For Example,
A name may be defined in a domain of character strings of maximum length 25
A USA phone # is a set of 10 digit phone numbers valid in the U.S.
Entity Integrity
The primary key attributes of a relation schema cannot have null values in any tuple of the relation.
Primary Key values are used to identify the individual tuples.
t[PK] null for any tuple t in r(R)
If PK has several attributes, null is not allowed in any of these attributes
Note: Other attributes of a relation may be constrained to disallow null values, even though they are not members of the primary key.
Database COMPANY
Does this violate domain or entity constraints?
Key Constraint
Candidate keys:
Specified for each relation schema
Must be unique for every tuple in any relation instance of that schema
Referential Integrity
A constraint defined between two relations
Specify a relationship among tuples in both relations:
The referencing relation and the referenced relation.
Pair-wise relationships are used to build relationships across three or more relations
Foreign Key
Take two relations R1 and R2 where …
FK is a subset of attributes in R1 that “mean” the same as a corresponding Primary Key (PK) in R2.
I.e., a one-to-one mapping between these two attribute subsets
FK has the same domain(s) of PK, but not necessarily same names!
A value of FK in a tuple t1 of R1 either occurs
As a value of PK of some tuple t2 of R2 ,
i.e., t1 [FK] = t2 [PK]
Referential Integrity Constraint
A tuple in the referencing relation R1 having the FK references the PK of the referenced relation R2
t1[FK] = t2[PK]
“A tuple t1 in R1 is said to reference a tuple t2 in R2”
Referential Integrity Constraints for COMPANY database
Constraint Violations
Basic operations for changing the database:
INSERT a new tuple in a relation
DELETE an existing tuple from a relation
MODIFY an attribute of an existing tuple
Possibility of Violations
INSERT may violate any of the constraints:
Domain constraint:
If one of the attribute values provided for the new tuple is not of the specified attribute domain
Ex. An invalid phone number or zip code
Entity integrity:
If the primary key value is null in the new tuple
SSN may not be null
Key constraint:
If the value of a key attribute in the new tuple already exists in another tuple in the relation
Two employees with the same SSN
Referential integrity:
If a foreign key value in the new tuple references a primary key value that does not exist in the referenced relation
Course enrollment for a student that doesn’t exist
Possibility of Violations
UPDATE may violate
Domain constraint
Updating an ordinary attribute (neither PK nor FK):
Entity constraints
NOT NULL constraint on an attribute being modified
Key constraint:
If the new value of a key attribute in the new tuple already exists in another tuple in the relation
Referential Integrity Constraint, depending on the attribute being updated:
Updating the primary key (PK):
What about related FKs?
Updating a foreign key (FK):
May violate referential integrity
Possibility of Violations
DELETE may violate only referential integrity:
If the primary key of the tuple being deleted is referenced from other tuples in the database referential integrity is violated.
Database COMPANY
What should happen if we have a request to DELETE department 5 in the DEPARTMENT relation?
Integrity violations?
Illegal state?
Can a Foreign Key have a Null Value?
May an employee not be assigned to a department?
May an employee not have a manager?
How are constraint violations minimized/ restricted?
Can restrictions be specific?
Deleting Target of a Foreign Key Reference
Restricted – Delete is restricted to case where there are no employees in the department.
Cascades – Delete cascades to remove employees also.
Nullifies – FK is set to null
I.e., Employees are no longer in any department. Then department is deleted.
Delete department where there are still employees in the department.
Updating Target of a Foreign Key Reference.
Restricted – Update is restricted to case where there are no employees in the department.
Cascades – Update cascades to change the department numbers of all employees in department D50.
Nullifies – FK,
I.e. DNO in EMP is set to null. Department DNO is updated.
Change department number (DNO) 50 to 75.
Inserting into Relation With the Foreign Key.
Restricted – Insert is restricted to case where there are already tuples for the corresponding department.
Cascades – If done interactively, user could be asked to enter tuple for department D10.
Nullifies – Cannot be done, since user has specified the department number.
Insert a new employee into department 10, but there is no tuple in the DEPT relation for department 10.
Relational Integrity Constraints
Not included in Codd’s original definition of relations.
Not supported in initial commercial relational database systems.
Now supported in all major relational database systems.
Other Types of Constraints
Semantic Integrity Constraints:
Based on application semantics and cannot be expressed by the model per se
“The max. no. of hours per employee for all projects he or she works on is 56 hrs per week”
“An employee’s salary must be in the range $30,000 to $100,000”
Express with a constraint specification language
Triggers and store procedures must be used
/docProps/thumbnail.jpeg
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com