Vertical Partitioning, Replication, MS SQL Server

<< Physical Record and De-normalization, Partitioning

Rules of SQL Format, Data Types in SQL Server >>

Database Management System (CS403)

Lecture No. 24

Reading Material

"Database Systems Principles, Design and Implementation" written by Catherine Ricardo,

Maxwell Macmillan.

"Database Management Systems", 2nd edition, Raghu Ramakrishnan, Johannes Gehrke,

McGraw-Hill

Overview of Lecture

o Vertical Partitioning

o Replication

o Structured Query Language (SQL)

In the previous lecture we were discussing physical data base design, in which we

studied denormalization and its different aspects. We also studied the horizontal

partitioning. In this lecture we will study vertical partitioning.

Vertical Partitioning

Vertical partitioning is done on the basis of attributes. Same table is split into different

physical records depending on the nature of accesses. Primary key is repeated in all

vertical partitions of a table to get the original table. In contrast to horizontal

partitioning, vertical partitioning lets you restrict which columns you send to other

destinations, so you can replicate a limited subset of a table's columns to other

machines. We will now see it with the example of a student relation as under: -

STD (stId, sName, sAdr, sPhone, cgpa, prName, school, mtMrks, mtSubs, clgName,

ntMarks, intSubs, dClg, bMarks, bSubs)

Now in this relation the student relation has number of attributes. It is in 3NF . But the

nature of accesses in this relation is different. So now we will partition this relation

vertically as under.

STD (stId, sName, sAdr, sPhone, cgpa, prName)

191

Database Management System (CS403)

STDACD (sId, school, mtMrks, mtSubs, clgName, intMarks, intSubs, dClg, bMarks,

bSubs)

Replication

The process of copying a portion of the database from one environment to another and

keeping subsequent copies of the data in synchronization with the original source

Changes made to the original source are propagated to the copies of the data in other

environments. It is the final form of denormalization. It increases the access speed and

failure damage of the database. In replication entire table or part of table can be

replicated. Replication is normally adopted in those applications, where updation is

not very frequent, because if updation is frequent so then it will have problems of

updation in all the copies of database relations. This will also slow down the speed of

database.

Clustering Files

Clustering is a process, which means to place records from different tables to place in

adjacent physical locations, called clusters. It increases the efficiency since related

records are placed close to each other. Clustering is also suitable to relatively static

situations. The advantage of clustering is that while accessing the records it is easy to

access. Define cluster, define the key of the cluster, and include the tables into the

cluster while creating associating the key with it.

Summary of Physical Database Design

Database design is the process of transforming a logical data model into an actual

physical database. A logical data model is required before you can even begin to

design a physical database. The first step is to create an initial physical data model by

transforming the logical data model into a physical implementation based on an

understanding of the DBMS to be used for deployment. To successfully create a

physical database design you will need to have a good working knowledge of the

features of the DBMS including:

In-depth knowledge of the database objects supported by the DBMS and the

physical structures and files required to support those objects.

Details regarding the manner in which the DBMS supports indexing,

referential integrity, constraints, data types, and other features that augment

the functionality of database objects.

192

Database Management System (CS403)

Detailed knowledge of new and obsolete features for particular versions or

releases of the DBMS to be used.

Knowledge of the DBMS configuration parameters that are in place.

Data definition language (DDL) skills to translate the physical design into

actual database objects.

Armed with the correct information, you can create an effective and efficient database

from a logical data model. The first step in transforming a logical data model into a

physical model is to perform a simple translation from logical terms to physical

objects. Of course, this simple transformation will not result in a complete and correct

physical database design it is simply the first step. The transformation consists of

the following things:

Transforming entities into tables

Transforming attributes into columns

Transforming domains into data types and constraints

There are many decisions that must be made during the transition from logical to

physical. For example, each of the following must be addressed:

The nullability of each column in each table

For character columns, should fixed length or variable length be used

Should the DBMS be used to assign values to sequences or identity columns?

Implementing logical relationships by assigning referential constraints

Building indexes on columns to improve query performance

Choosing the type of index to create: b-tree, bit map, reverse key, hash,

partitioning, etc.

Deciding on the clustering sequence for the data

Other physical aspects such as column ordering, buffer pool specification, data

files, denormalization, and so on.

Structured Query Language

SQL is an ANSI standard computer language for accessing and manipulating

databases. SQL is standardized, and the current version is referred to as SQL-92. Any

SQL-compliant database should conform to the standards of SQL at the time. If not,

193

Database Management System (CS403)

they should state which flavor of SQL (SQL-89 for example) so that you can quickly

figure out what features are and are not available. The standardization of SQL makes

it an excellent tool for use in Web site design. Most Web application development

toolkits, most notably Allaire's Cold Fusion and Microsoft's Visual InterDev, rely on

SQL or SQL-like statements to connect to and extract information from databases. A

solid foundation in SQL makes hooking databases to Web sites all the simpler. SQL is

used to communicate with a database. According to ANSI (American National

Standards Institute), it is the standard language for relational database management

systems. SQL statements are used to perform tasks such as update data on a database,

or retrieve data from a database. Some common relational database management

systems that use SQL are: Oracle, Sybase, Microsoft SQL Server, Access, Ingres, etc.

Although most database systems use SQL, most of them also have their own

additional proprietary extensions that are usually only used on their system. However,

the standard SQL commands such as "Select", "Insert", "Update", "Delete", "Create",

and "Drop" can be used to accomplish almost everything that one needs to do with a

database. This tutorial will provide you with the instruction on the basics of each of

these commands as well as allow you to put them to practice using the SQL

Interpreter.

Benefits of Standard SQL:

Following are the major benefits of SQL:-

Reduced training cost

· Application portability

Application longevity

Reduced dependence on a single vendor

· Cross-system communicationSQL is used for any type of interaction with

the database through DBMS. It can be used for creating tables, insertion in the

table and deletion as we well and other operations also.

MS SQL Server

The DBMS for our course is Microsoft's SQL Server 2000 desktop edition. There are

two main tools Query Analyzer and Enterprise Manager; both can be used. For SQL

practice we will use Query Analyzer. So you must use this software for the SQL

queries. So we will be using this software for our SQL queries.

Summary:

194

Database Management System (CS403)

In this lecture we have studied the vertical partitioning, its importance and methods of

applying. We have also studied replication and clustering issues. We then started with

the basics of SQL and in the next lectures we will use SQL Server for the queries.

Exercise:

Critically examine the tables drawn for Examination system and see if there is a

requirement of vertical partitioning and then carry out the process. Also install

the SQL Server and acquaint yourself with this software.

195

Table of Contents: