Transcript Semantic data integrity

An Investigation of Oracle and
SQL Server with respect to
Integrity, and SQL Language
standards
Presented by: Paul Tarwireyi
Supervisor: John Ebden
Date: 27/07/2005
Overview
1. Project Objectives
2. Approach to Study
3. Data Integrity
4. SQL Language Standards
5. Timeline
6. Possible Extensions
7. Work Done so far
8. Questions
Project Objectives


To produce a report detailing weaknesses
and strengths in SQL Server and Oracle
database systems,
To make recommendations about the
optimal use of these products for different
types of users.
Approach to Study
•A blend of a Research and Experimental nature
•Integrity tests will be carried out using SQL scripts
and broken into:
Analysis
Implementation
Design
• Research will be carried out on the SQL
Standards
Types of Data Integrity


Database structure integrity and
Semantic data integrity
Database structure integrity:


Keeping track of database objects and
ensuring that each object is created,
formatted and maintained properly.
Each DBMS uses its own internal format
and structure to support the databases,
table spaces, tables, and indexes under
its control.
Database structure integrity (cont)




Many DBMSs use pointers to store large
objects.
System and application errors at times can
cause faults within these internal structures.
And they become corrupted.
DBAs need to identify and correct such faults
before insurmountable problems occur.
Semantic data integrity


Refers to the meaning of data and
relationships that need to be maintained
between different types of data.
The DBMS provides options, controls
and procedures to define and assure the
semantic integrity of the data stored
within its databases.
Semantic data integrity (cont)


These are DBMS features and
processes that a DBA can use to ensure
data Integrity.
This is further categorised into four
types:
Semantic data integrity (cont)

Entity integrity
• PRIMARY KEY and UNIQUE
• The SQL-92 standard requires that all values
in a primary key be unique and that the
column should not allow null values.
Semantic data integrity (cont)

Domain integrity
• It is all about the permissible entries that a
•
field can have.
Enforced by restricting
• the type (through data types),
• the format (through CHECK constraints and rules),
or
• the range of possible values (through FOREIGN
KEY constraints, CHECK constraints, DEFAULT
definitions, NOT NULL definitions, and rules)
Semantic data integrity (cont)

Referential Integrity
• About Building and maintaining logical
•
•
•
relationships between tables
RI guarantees that an acceptable value is
always in the foreign key column.
The ANSI SQL-92 standard defines four
possible referential actions that apply to
deletes from or updates to the referenced table.
NO ACTION, CASCADE, SET DEFAULT, and
SET NULL
Semantic data integrity (cont)

User Defined integrity
• This refers the to specific business rules not
covered by the types of integrity. In both
DBMSs this is usually implemented by making
use of triggers and procedures.
Transaction management and
concurrency control



To ensure data integrity the database
should not violate the ACID properties.
Concurrently reading and or writing
records in a database without
compromising the consistency of the
data.
Locking methods
SQL Language Standards



Due to the rapid increase in the number
of vendors in the market, SQL dialects
proliferated.
(ISO/IEC JTC 1/SC 32/WG 3) as well as
an American committee (ANSI TC
NCITS H2)
Which released standards for SQL in
1986, 1989, 1992, 1999, and 2003.
Conformance to Standards




Most major database vendors support the
SQL-92 standard.
Oracle (PL/SQL) and Microsoft (T-SQL) use
extended forms of SQL which adds declared
variables, error and exception handling etc.
DECLARE @NUM1 INT … T-SQL
WHEN exception-name1 THEN
PL/SQL-statements; …PL/SQL
Timeline
Reading Literature and tutorials
First term
Knowing Oracle and Ms SQL Server
First and second
term
Literature Survey
End of second term
Search for a Database
End of second Term
Investigating them for integrity problems
Second and third
term
Investigating them for conformity to SQL:2003
Third term
Prepare project Poster
End of third term
What (if anything) can be done to fix the problems
Fourth term
Final write up
Fourth term
Possible extensions




Developing software which will test
DBMSs for integrity.
Evaluating the recoverability, backup,
maintainability, security and so on.
Evaluating the latest versions: SQL
Server 2005 (Yukon) and Oracle 10g
Performing similar tests with Open
Source databases
Work Done so far

Literature review

Getting to know SQL Server

Getting to know Oracle
• Creating and manipulating databases
• Using T-SQL
• Creating and manipulating databases
• Using PL/SQL
Work Done so far (cont)

Carried out experiments to test for:
• Entity integrity
• Referential Integrity
Questions