A Comparison of Database Software

1
A Comparison of Database Software

• CS 616
• April 8, 2004
• Team 7
• Mandar Patankar
• Jonathan Cohen
• B. Timothy Walsh

2
Todays Presentation

• Review and compare features of three robust
  database engines
• SQL Server 2000
• Oracle 9i
• MySQL 4.1

3
Hardware Requirements
SQL Server MySQL Oracle
Processor Pentium 166 Mz or higher Less power needed Pentium 166 Mz or higher
Memory(RAM) 64 MB 32 MB 128 MB
Disk Space 270 MB 60 MB 128 MB (system drive)4.5 GB Oracle Home Drive
4
SQL Implementation
SQL Server MySQL Oracle
Name Transact SQL MySQL Dialect PL/SQL
Views General Views,Indexed Views,Distributed Partitioned Views Not Supported DBMS_XMLSCHEMA PackageCatalog views
Views are mostly useful for letting users access
a set of relations as one table (in read-only
mode). Many SQL databases don't allow one to
update any rows in a view, but you have to do the
updates in the separate tables.  MySQL doesn't
yet support views, but they plan to implement
these to about 5.0 (development has been delayed,
it was planned for 4.1)
5
SQL Implementation
SQL Server MySQL Oracle
Triggers AFTER triggers,INSTEAD OF triggers Not Supported BEFORE triggers,AFTER triggers,INSTEAD OF triggers
A trigger is a special kind of stored procedure
that runs when you modify data in a specified
table using one or more of the data modification
operations UPDATE, INSERT, or DELETE. Special
triggers known as INSTEAD OF triggers can specify
modifications to a view's underlying base
tables. Triggers can query other tables and can
include complex SQL statements. They are
primarily useful for enforcing complex business
rules or requirements. For example, you can
control whether to allow a new order to be
inserted based on a customer's current account
status.
6
SQL Implementation
SQL Server MySQL Oracle
Stored Procedures T-SQL statements Not Supported PL/SQL statements,Java methods,third-generation language(3GL) routines
Stored procedures and user-defined functions are
collections of SQL statements and optional
control-of-flow statements stored under a name
and processed by the database server as a unit.
Both stored procedures and user-defined functions
are precompiled and ready for later use. Using
Visual Database Tools, you can easily create,
view, modify, and test stored procedures and
user-defined functions.
7
SQL Implementation
SQL Server MySQL Oracle
User-defined functions Scalar functions,Inline table-valued functions,Multistatement table-valued functions C, C external libraries Support for object-oriented programming
8
SQL Implementation
SQL Server MySQL Oracle
Foreign Keys Supported Supported only for InnoDB tables Supporte
The foreign key is a field reference into another
table. The foreign key points back to the primary
key of another table. Foreign keys prevent
dependent tables from adding which might
jeopardize the integrity of the data
9
SQL Implementation
SQL Server MySQL Oracle
Cursors Supported Not Supported Supported
Arrays Not supported Supported Supported
Indexes B-Tree indexes B-tree indexes B-Tree indexes,Bitmap indexes,Partitioned indexes,Function-based indexes,Domain indexes
10
Table Implementation
SQL Server MySQL Oracle
Tables Relational tables,Temporary tables Transaction-safe tables (InnoDB) non-transaction-safe tables (ISAM) Relational tables,Object tables,Temporary tables
11
Cost

• Oracle
• 4,000 per processor
• 150 per user (minimum 5 users)
• MS SQLServer
• 5,000 per procesor

12
Performance - 1
13
Performance - 2
14
Performance - 3