1 / 21

An Investigation of Oracle and SQL Server with respect to Integrity, and SQL Language standards

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.

brenden-kemp
Download Presentation

An Investigation of Oracle and SQL Server with respect to Integrity, and SQL Language standards

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 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

  2. 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

  3. 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.

  4. Analysis Implementation Design Approach to Study • A blend of a Research and Experimental nature • Integrity tests will be carried out using SQL scripts and broken into: • Research will be carried out on the SQL Standards

  5. Types of Data Integrity • Database structure integrity and • Semantic data integrity

  6. 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.

  7. 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.

  8. 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.

  9. 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:

  10. 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.

  11. 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)

  12. 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

  13. 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.

  14. 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

  15. 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.

  16. 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

  17. Timeline

  18. 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

  19. Work Done so far • Literature review • Getting to know SQL Server • Creating and manipulating databases • Using T-SQL • Getting to know Oracle • Creating and manipulating databases • Using PL/SQL

  20. Work Done so far (cont) • Carried out experiments to test for: • Entity integrity • Referential Integrity

  21. Questions

More Related