Database Examples

A sample university DBMS application has been created to support the sample program applications and code referenced throughout this paper. This application was specifically created to illustrate many of the points, issues, and tricks involved in converting an Oracle 7 application to a SQL Server application.

Sample University Application

This sample application uses four tables to track all university activity. The DEPT table is used to track department offerings within the university. The CLASS table is used to track class offerings within each department. The STUDENT table is used to track each student within the university. The GRADE table is used to track the enrollments of each student within each class.

In this sample application, social security number (SSN) is used as the primary key for the STUDENT table. The DEPT table uses department code (DEPT) as the primary key, and course code (CCODE) is used as the primary key for the CLASS table. The combination of social security number (SSN) and course code (CCODE) together makes up the primary key for the GRADE table.

The column major is defined as a foreign key in the STUDENT table. When selecting a major, the student must choose a valid department code (DEPT) from the DEPT table. The department (DEPT) column in the CLASS table is also defined as a foreign key. When a course is inserted in this table, it must also be associated with a valid department (DEPT) from the DEPT table.

The GRADE table has two foreign keys. When enrolling a student in a class, the social security number (SSN) must exist in the STUDENT table, and the course code (CCODE) must exist in the CLASS table. This ensures that students are not enrolled in nonexistent classes, and that classes are not filled with nonexistent students.

Sample Application and Code References

The following sample application code is referenced throughout this paper.

Orademo.cpp

Oracle ODBC application that accesses the sample university tables on an Oracle 7 database. This program is the starting point for the conversion process. It allows users to perform data entry and produce reports against the sample university application.

Ssdemo.cpp

SQL Server ODBC application that accesses SQL Server. This program is the ending point for the conversion process. All of the Oracle SQL commands, procedures, packages, and functions have been converted to SQL Server SQL statements and procedures. Many of the advantages associated with SQL Server are demonstrated in this program.

Common.cpp

ODBC application that works with both the Oracle and SQL Server DBMS's. In order to connect to Oracle or SQL Server, the user simply provides the ODBC Data Source Name (DSN). The program then logs on to the requested database. The program contains excellent examples of programming techniques that can be used when developing a multidatabase program.

Orauser.sql

Creates the Oracle user accounts and database roles required for the sample Oracle program.

Oratable.sql

Creates the Oracle tables and views required for the sample Oracle program.

Oraproc.sql

Creates the Oracle stored procedure, functions, and packages required for the sample Oracle program.

Oracommn.sql

Creates all of the additional Oracle database objects that are required to support the Common.cpp program.

Oradata.sql

Loads sample application data into the tables required for the sample Oracle program.

Ssuser.sql

Creates the SQL Server user accounts and database groups required for the sample SQL Server program.

Sstable.sql

Creates the SQL Server tables and views required for the sample SQL Server program.

Ssproc.sql

Creates the stored procedures required for the sample SQL Server program.

Sscommon.sql

Creates all of the additional SQL Server database objects that are required to support the Common.cpp application.

Ssdata.sql

Loads sample application data into the tables required for the sample SQL Server program.

Running the Supplied Scripts

The sample scripts must be run in the following sequence to create the sample applications on the target DBMS platforms.

Prior to running these scripts for the SQL Server 6.5 database, you must create an application database (named USER_DB) for these scripts and the sample SQL Server program to work. This database can be created using SQL Enterprise Manager. After the database has been created, log on to ISQL/w using the SA account and run these scripts in the indicated order.

Prior to running these scripts for the Oracle 7.3 database, the sample scripts assume that the USER_DATA and TEMPORARY_DATA tablespaces exist. They are usually created during a default Oracle 7.3 database installation. If these tablespaces do not exist, you must either add them or modify the supplied sample scripts to use other tablespaces.

After verifying that these tablespaces exist, log on to SQL*Plus using the SYSTEM account. If the password is not the default value of MANAGER, you must change the password in the Oracle SQL scripts.

DBMS User Accounts

Three user accounts are created for this application:

This account is the administrative owner of the STUDENT and GRADE tables.

This account is the administrative owner of the DEPT and CLASS tables.

This account is a query only account that can access the STUDENT, GRADE, DEPT, and CLASS tables.