1 / 19

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. Overview. 1. Motivation. 2. Problem Statement. 3. SQL Language Standards. 4. Integrity. 5. Plan of action.

brent-vinson
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

  2. Overview 1. Motivation 2. Problem Statement 3. SQL Language Standards 4. Integrity 5. Plan of action 6. Deliverables / Expected results 7. Timeline 8. Possible Extensions 9. Questions

  3. Motivation • Long standing debate between DBAs on Oracle and SQL Server • Critical to choose the right DBMS from the start • The criteria to be met to fulfil the needs of the organisation should be clarified • There are many criteria and benchmarks to aid the choice of a DBMS • For example platform support, ease of use, recoverability etc

  4. Problem Statement • In this project I will carry out a comparative investigation and evaluation of • Oracle 9i - (9.2.0.5.0) and • Ms SQL Server 2000 - (8.00.818) • With respect to Integrity and SQL Language standards (SQL: 2003) • Both installed on Windows Server 2003

  5. SQL Language Standards • Specify the syntax and semantics of SQL language facilities for defining and accessing SQL databases. e.g. • Schema definition • Data manipulation • Transaction management • These standards were setup to protect customers from vendors making overly aggressive marketing strategies

  6. SQL Language Standards (cont) • There is an international committee working on the SQL standards • (ISO/IEC JTC 1/SC 32/WG 3) as well as an American committee (ANSI TC NCITS H2) • SQL-92 • SQL: 1999 the previous standard: • Core features • Non-core features. • SQL: 2003 the current standard

  7. Conformance to Standards • Oracle, IBM and Microsoft make formal and clever statements about their conformance to the standards. • For example: • "The Core SQL: 1999 features that Oracle fully supports are listed in Table B-1 ..."-- Oracle Corporation, Oracle9i SQL Reference

  8. Conformance (cont) • "The Microsoft SQL Server 2000 Transact-SQL version complies with the Entry level of the SQL-92 standard ..."-- Microsoft Corporation, SQL Server 2000 Books Online (Updated)

  9. Conformance testing models • The “Underwriters Laboratory (UL)” model – vendors pay independent organisation • Liability insurance companies demand it, • Consumers buy only tested products, • Wholesalers will buy only tested products. But that is no longer happening

  10. Conformance testing models (cont) • The “Consumers Union (CU)” model- individual organisation is funded by consumers • They will in turn use these results to make informed buying decisions.

  11. Integrity • The quality, accuracy, consistency or completeness of the data stored in a database. • In terms of GIGO integrity is all about keeping the Garbage out of the system • There are four main types of data integrity:

  12. Integrity (cont) • Entity integrity • Domain integrity • Referential Integrity • Synchronises relationships between tables • UPDATE threat: changing PK or FK • INSERT threat: inserting in child table (dangling tuples) • DELETE threat: deleting from parent table • User Defined integrity • Specific business rules

  13. Implementing data Integrity • Declarative data integrity • Enforces integrity through constraints (data types) • Limitations on columns e.g. PK, FK, NOT NULL etc • Procedural data integrity • Enforces integrity through • Triggers • Stored procedures.

  14. Plan of action • Carry out a literature search: mainly to obtain background knowledge and understanding of databases. • Get to know SQL Server • Creating and manipulating databases • Get to know Oracle • Creating and manipulating databases

  15. Plan of action (cont) • Carry out experiments to test Integrity • Using scripts • DML INSERT, UPDATE, DELETE, and SELECT • DDL CREATE, ALTER, and DROP • DCL GRANT and REVOKE • Transaction Control ROLLBACK and COMMIT • Investigate the language structures for conformity with SQL:2003 • Are the commands written according to specifications.

  16. Expected Results • To produce a report detailing weaknesses and strengths in SQL Server and Oracle database systems, • making recommendations about the optimal use of these products for different types of users.

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

More Related