Data Integrity TNT

IF41204 - IF31204
Data
Integrity
Pengenalan Basisdata
Taken from Togu Turnip slide- Institut Teknologi Del
Minggu 05 Sesi 01
AGENDA
1
Data Integrity
2
Type of Data Integrity
3
Enforcing Data Integrity
Data Integrity
1
When is Data Integrity Lost?
•
•
•
•
Invalid data added to the database
Existing data modified to incorrect value
Changes to database lost
Changes partially applied
Why Data Integrity Important
•
•
•
•
•
Undermines the safety and efficiency and/or assurance
of quality of the data
Example: drugs that consumers will take.
Data integrity problems break trust.
We rely largely on trusting the firm to do the right thing
when no one is seeing.
Integrity of data can be maintained by specifying certain
checks at the time of creating and modifying tables
and then applying those checks when handling data.
Data Integrity
•
•
•
In its broadest use, “data integrity” refers to the
accuracy and consistency of data stored in a
relational database.
The term – Data Integrity - can be used to describe a
state, a process or a function – and is often used as
a proxy for “data quality”.
All characteristics of the data must be correct –
including business rules, relations, dates,
definitions and lineage – for data to be complete.
Rules of Data Integrity
•
•
•
•
Uniqueness
Validity
Consistency
Business Rules
2
Type of Data Integrity
Types of Data Integrity
1
2
Requirement Stages
Requirement Stages
3
Requirement Stages
Illustration of Data Integrity
Entity Integrity
•
•
•
•
•
Each entity is unique and table’s PRIMARY KEY must have
unique values for each row
DBMS automatically checks uniqueness of primary key
value for each insert or update
Insert or update to include duplicate value in primary key
column results in error
If required for non-primary key column, UNIQUE
constraint or CREATE INDEX
Uniqueness also enforces NOT NULL for primary key
Domain Integrity
A domain defines the possible values of an attribute.
Domain Integrity rules govern these values.
In a database system, the domain integrity is defined by:
•
•
•
•
•
•
•
•
The datatype and the length
The NULL value acceptance
The allowable values, through techniques like constraints or
rules
The default value
For example, if you define the attribute of Age, of an
Employee entity, is an integer, the value of every instance
of that attribute must always be numeric and an integer.
Domain Information
Each attribute in the model should be assigned domain information which
includes:
•
•
•
•
•
•
•
•
Data Type - Basic data types are integer, decimal, or character. Most data bases
support variants of these plus special data types for date and time.
Length - This is the number of digits or characters in the value. For example, a
value of 5 digits or 40 characters.
Date Format - The format for date values such as dd/mm/yy or mm/dd/yyyy.
Range - The range specifies the lower and upper boundaries of the values the
attribute may legally have.
Constraints - Are special restrictions on allowable values. For example, the
LeavingDate for an Employee must always be greater than the HireDate for that
Employee.
Null support - Indicates whether the attribute can have null values.
Default value (if any) - The value an attribute instance will have if a value is not
entered.
Referential Integrity
•
•
•
•
A Rule that states that any foreign key value (on the
relation of the many sides) MUST match a primary key
value in another table.
A constraint that enforce integrity between related tables.
Foreign key is a set of attributes that are a primary key for
some other relations
Foreign key is identifier that enable a dependent relation
to refer to its parent relation
3
Enforcing Data
Integrity
Mechanisms to Enforcing
Data Integrity
Determining Which Type of Constraint to Use
Requirement Stages
Domain
A domain defines a logical set of values that make up the
valid values in a column.
Domain integrity is maintained using the following:
•
•
•
•
•
FOREIGN KEY constraint: a FOREIGN KEY column can either have a
value that exists in the UNIQUE or PRIMARY KEY columns of the
referenced table or it can have a null value.
CHECK constraint: specifies the range of valid data values that can be
entered into a column.
DEFAULT definitions: specify default values for columns that do not
accept null values.
Domain
•
•
•
FOREIGN KEY constraint: a FOREIGN KEY column can
either have a value that exists in the UNIQUE or PRIMARY
KEY columns of the referenced table or it can have a null
value.
CHECK constraint: CHECK constraint can be specified on a
column within the CREATE TABLE or ALTER TABLE
statement.
DEFAULT definitions: specify default values for columns that
do not accept null values.
Example : Domain Constraints
Requirement Stages
Entity
A table is said to comply with entity integrity when no two
rows in the table have the exact same values in all the
columns.
Entity integrity is ensured using:
•
•
•
•
•
•
PRIMARY KEY constraint: does not allow duplicate or null values to be
inserted.
UNIQUE constraint: does not allow duplicate but allows null values to
be inserted. However, this column allows a null value to be inserted
once.
Indexes: prevents duplicate values from being entered in a column.
IDENTITY property: defines an identifier column that contains systemgenerated sequential values for every record inserted.
Example : Entity Constraints
Requirement Stages
Example : Key Constraints
Referential
Referential integrity is implemented using the concept of
FOREIGN KEYS. FOREIGN KEYS columns reference
UNIQUE or PRIMARY KEY columns in other tables.
Referential integrity is ensured using:
•
•
•
•
•
Values can be inserted in a FOREIGN KEY column only if similar values
exist in the referenced UNIQUE or PRIMARY KEY column.
If a value in the UNIQUE or PRIMARY KEY column is modified, similar
modifications are carried out in the referring FOREIGN KEY columns.
If a value in the UNIQUE or PRIMARY KEY column is deleted, deletion
should be carried out in the referring FOREIGN KEY columns.
The Cascading Options
•
•
•
•
When records in the referenced PRIMARY KEY table are
modified, changes can be cascaded to corresponding records
in the referring FOREIGN KEY table.
This way is used to maintain the referential integrity.
The cascading options are defined in the REFERENCES
clause of the CREATE TABLE or ALTER TABLE statement.
DBMS provides the following two cascading options:
• Cascading Update.
• Cascading Delete.
Example : Referential Constraints
Requirement Stages
Summary
•
•
•
•
•
•
Data Integrity ensures accurate and up-to-date information at
any point in time.
In DBMS, data integrity is enforced using Constraints, Default
Values, Rules and Triggers
Types of Data Integrity
To maintain accuracy and consistency of data in a relational
database.
To ensure validity and consistency of data in a database.
DBMS supports UNIQUE, CHECK, PRIMARY and FOREIGN
KEY constraints on columns in a table.
References
• DAVID M. KROENKE’S DATABASE CONCEPTS, 2nd Edition, 2005
Pearson Prentice Hall
• Slide was adapted from VES (2016) for Introduction to Database
course
•Database Operations and Constraint Violations in DBMS, accessed
from: http://www.edugrabs.com/database-operations-and-constraintviolations/
THANK YOU!
DON’T FORGET TO BE AWESOME