comp3 - TypesofConstraints KeyConstraints(EntityIntegrity (...

Info iconThis preview shows pages 1–7. Sign up to view the full content.

View Full Document Right Arrow Icon
Integrity and Security
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Constraints and Triggers Integrity constraints  ensure that the changes made  to the database by authorized users do not result in a  loss of data consistency. Types of Constraints  Key Constraints (Entity Integrity)  Foreign Key Constraints (Referential Integrity)  Domain Constraints (Domain Integrity)  General Constraints (User Defined Integrity)
Background image of page 2
Domain Constraints A domain constraint is a predicate on an attribute A  of each tuple of a relation to be atomic value from a  domain set domain(A). CREATE DOMAIN  <domain_name>  <data_type> CONSTRAINT  <constraint_name>  CHECK  <constraint> Example: Salary of an employee is a two decimal  point numeric field in a range 150 to 6000
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Domain Constraints CREATE DOMAIN  BasicSalary  NUMERIC (7, 2)  CONSTRAINT  SalaryRange  CHECK   (VALUE>=150.00  AND  VALUE<=6000.00) CHECK  constraint can be used in a column definition  as follows: Salary NUMERIC(9, 2)  CHECK  (Salary>=150.00  AND  Salary<=6000.00)
Background image of page 4
General Constraints The syntax for general assertion is: CREATE ASSERTION  <assertion_name>  CHECK   <predicate> The <predicate> is a valid conditional expression  similar to the <condition> in the WHERE clause of  the SELECT-FROM-WHERE statement.
Background image of page 5

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Example: Constraint on the number of employees in a team: CREATE ASSERTION  NumberOfTeamMembers  CHECK (8 >=  ALL  ( SELECT  EmpId  FROM  EmpTeams  GROUP  BY TeamId)
Background image of page 6
Image of page 7
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

Page1 / 21

comp3 - TypesofConstraints KeyConstraints(EntityIntegrity (...

This preview shows document pages 1 - 7. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online