SQL Server Relational Database Management System RDBMS Computer Science Essay

Published:

This essay has been submitted by a student. This is not an example of the work written by our professional essay writers.

SQL Server 2005 : SQL Server is a relational database management system (RDBMS) developed by Microsoft thats designed specifically for the enterprise environment. SQL Server runs on T-SQL (Transact -SQL), a set of programming extensions from Sybase and Microsoft with some additional features to standard SQL, including transaction control, exception and error handling, row processing, and declared variables.

Oracle 9i :

Oracle is a relational database management system (RDBMS) from Oracle Corporation, which runs on more than 80 platforms. Oracle was ntroduced in the late 1970s, it was one of the first database product to run on a variety of platforms from micro to mainframe. The Oracle database is Oracle's flagship product, and version 11g was introduced in 2007.

The "i" and "g" Versions :

The Oracle version with "i" reflects the database support for Internet with its built-in java Virtual machine. And the version with "g" emphasis for grid computing which enables clusters of low-cost, industry standard servers to be treated as a single unit.

Comparision SQL Server and Oracle

This section contains information comparing both the Database technologies used in MIGSORA project to migrate data, Microsoft SQL Server and Oracle database. It includes the following sections:

Schema Migration

Data Types

Data Storage Concepts

Data Manipulation Language

Schema Migration:

The schema contains the definition of database -specific objects like tables, views, indexes, users, stored procedures etc.

The schema migration topics discussed here include the following:

Object Similarities

Both RDBMS Database SQL Server and Oracle have many similarities and dis-similarities.

Comparision of both database in reference to Schema is given below in table chart.

Table Schema Objects in Oracle and Microsoft SQL Server

Oracle

Microsoft SQL Server

Database

Database

Schema

Database and database owner (DBO)

Tablespace

Database

User

User

Role

Group/Role

Table

Table

Temporary tables

Temporary tables

Cluster

Cluster

Primary Key

Primary Key

Foreign Key

Foreign Key

PL/SQL Procedure

Transact-SQL (T-SQL) stored procedure

Packages

NA

Synonyms

NA

Snapshot

NA

View

View

â-  Schema Object Names

Object Names

Every RDBMS have reserved word which a user can't use while assigning a name to database objects.

Same both SQL Server and Oracle have reserved words but they differ in both the databases. Many

Oracle reserved words are valid in Microsoft SQL Server. For Example Date is the reserved word in Oracle

But it can be use as a column name in SQL Server as it was not a reserved word in case of Microsoft SQL Server.

Hence no column was allowed to have DATE as a column name but in SQL a column can be named as a DATE.

Using of reserved word as a schema name makes it impossible to use the same name across the database.

One should choose a schema object name that is unique by at least one characteristics, and need to make it sure that

The name used for object was not a reserved word for that database.

â-  Table Design Considerations

This particular section discusses many table creation issues which one should think about while

Converting SQL Server database to Oracle 9i database. Issues discussed are as follows :

â-  Data Types

DATETIME Data Types

Precision of Datetime datatype in SQL Server is 1/300th of a second whereas Oracle don't have any Datetime datatype rather Oracle have Timestamp which has a precision of 1/100000000th of a second. Apart from Timestamp datatype Oracle also have Date data type that stores date and time values accurate to one second.

â-  Entity Integrity Constraints

Entity Integrity Constraints

In case of Microsoft SQL Server you can define a Primary key through CREATE Table statement

or a ALTER TABLE statement. Same you can create a Primary key in Oracle with CREATE Table or ALTER TABLE statement, but Oracle provides a declarative referential integrity by automatically creating a Unique index to enforce the integrity.

Data Types

This section provides a table showing the base data types used by Microsoft SQL Server and Oracle database available and how they are mapped to each other.

Table Datatypes in Oracle and Microsoft SQL Server.

SQL Server

Oracle

INTEGER

NUMBER(10)

SMALLINT

NUMBER(6)

TINYINT

NUMBER(3)

REAL

FLOAT

FLOAT

FLOAT

BIT

NUMBER(1)

CHAR(n)

CHAR(n)

VARCHAR(n)

VARCHAR2(n)

TEXT

CLOB

IMAGE

BLOB

BINARY(n)

RAW(n)/BLOB

VARBINARY(n)

RAW(n)/BLOB

DATETIME

DATE

MONEY

NUMBER(19,4)

SMALLMONEY

NUMBER(10,4)

TIMESTAMP

NUMBER

In addition to the data types provided in above table chart users can also define their own data type as per the

there requirement by using predefined base datatypes. You can map datatypes from Microsoft SQL Server to Oracle with equivalent data types listed in above table.

Data Storage Concepts

This section defines the detailed description of Datastorage concepts in both SQL Server and Oracle Database.

Microsoft SQL Server

Oracle

Database Devices:

A database device is mapped to the specified

physical disk files.

Datafiles:

All physical data of all logical structures in a tablespace are stored by creating a datafiles in Oracle. The size of all datafiles is the size of tablespace. The size of tablespace is the total storage capacity of database. Once created a size of datafile cannot be changed.

Page:

Many pages make up a database device. Each

page contains a certain number of bytes.

Data Block:

One data block corresponds to a definite

number of bytes, of database

space, on the disk. The size of the data

block can be specified when creating the

database

Extent:

Each extent consists of eight pages. Space is

allocated to all the databases in increments of

one extent at a time.

Extent:

An extent is a specific number of

contiguous data blocks, obtained in a single

allocation.

Log Devices:

Log devices are the logical area assigned to store the log

of database. The database object to store the log can be specified while creating the database.

Redo Log Files:

Each database in oracle have a set of atleast two or more redo log files. The number of redo log files to be created can be specified while creating database. All changes made to database were recorded in redo log files.

NA

Control Files:

Each database has a control file. It is one of the important file of oracle database. This file

records the physical structure of the

database. It contains the following

information:

â-  database name

â-  names and locations of a database's

datafiles

â-  names and locations of redo log files

â-  time stamp of database creation

The conceptual differences in the storage structures do not affect the conversion

process directly. However, the physical storage structures need to be in place before

conversion of the database begins.

Methods:

Objective for migration process can be achieved through four steps. The first step is to capture a snapshot of the SQL Server 2005 database. In this step meta-data of SQL Server 2005 would be gathered and stored in repository for migration. The captured repository model would be then converted to an Oracle model. This model represents what the Oracle database would look like. The converted model is then fired as a SQL script, which when run against an Oracle database, will generate all the migrated users, tables, views and other relevant objects. The last step is to migrate or move the data, which can be done using scripts by utilizing the data move tools like Bulk Copy Program (BCP) and Oracle's SQL*Loader.

Writing Services

Essay Writing
Service

Find out how the very best essay writing service can help you accomplish more and achieve higher marks today.

Assignment Writing Service

From complicated assignments to tricky tasks, our experts can tackle virtually any question thrown at them.

Dissertation Writing Service

A dissertation (also known as a thesis or research project) is probably the most important piece of work for any student! From full dissertations to individual chapters, we’re on hand to support you.

Coursework Writing Service

Our expert qualified writers can help you get your coursework right first time, every time.

Dissertation Proposal Service

The first step to completing a dissertation is to create a proposal that talks about what you wish to do. Our experts can design suitable methodologies - perfect to help you get started with a dissertation.

Report Writing
Service

Reports for any audience. Perfectly structured, professionally written, and tailored to suit your exact requirements.

Essay Skeleton Answer Service

If you’re just looking for some help to get started on an essay, our outline service provides you with a perfect essay plan.

Marking & Proofreading Service

Not sure if your work is hitting the mark? Struggling to get feedback from your lecturer? Our premium marking service was created just for you - get the feedback you deserve now.

Exam Revision
Service

Exams can be one of the most stressful experiences you’ll ever have! Revision is key, and we’re here to help. With custom created revision notes and exam answers, you’ll never feel underprepared again.