ZeePedia

Vertical Partitioning, Replication, MS SQL Server

<< Physical Record and De-normalization, Partitioning
Rules of SQL Format, Data Types in SQL Server >>
img
Database Management System (CS403)
VU
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,
i
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
img
Database Management System (CS403)
VU
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
img
Database Management System (CS403)
VU
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
img
Database Management System (CS403)
VU
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
img
Database Management System (CS403)
VU
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:
  1. Introduction to Databases and Traditional File Processing Systems
  2. Advantages, Cost, Importance, Levels, Users of Database Systems
  3. Database Architecture: Level, Schema, Model, Conceptual or Logical View:
  4. Internal or Physical View of Schema, Data Independence, Funct ions of DBMS
  5. Database Development Process, Tools, Data Flow Diagrams, Types of DFD
  6. Data Flow Diagram, Data Dictionary, Database Design, Data Model
  7. Entity-Relationship Data Model, Classification of entity types, Attributes
  8. Attributes, The Keys
  9. Relationships:Types of Relationships in databases
  10. Dependencies, Enhancements in E-R Data Model. Super-type and Subtypes
  11. Inheritance Is, Super types and Subtypes, Constraints, Completeness Constraint, Disjointness Constraint, Subtype Discriminator
  12. Steps in the Study of system
  13. Conceptual, Logical Database Design, Relationships and Cardinalities in between Entities
  14. Relational Data Model, Mathematical Relations, Database Relations
  15. Database and Math Relations, Degree of a Relation
  16. Mapping Relationships, Binary, Unary Relationship, Data Manipulation Languages, Relational Algebra
  17. The Project Operator
  18. Types of Joins: Theta Join, Equi–Join, Natural Join, Outer Join, Semi Join
  19. Functional Dependency, Inference Rules, Normal Forms
  20. Second, Third Normal Form, Boyce - Codd Normal Form, Higher Normal Forms
  21. Normalization Summary, Example, Physical Database Design
  22. Physical Database Design: DESIGNING FIELDS, CODING AND COMPRESSION TECHNIQUES
  23. Physical Record and De-normalization, Partitioning
  24. Vertical Partitioning, Replication, MS SQL Server
  25. Rules of SQL Format, Data Types in SQL Server
  26. Categories of SQL Commands,
  27. Alter Table Statement
  28. Select Statement, Attribute Allias
  29. Data Manipulation Language
  30. ORDER BY Clause, Functions in SQL, GROUP BY Clause, HAVING Clause, Cartesian Product
  31. Inner Join, Outer Join, Semi Join, Self Join, Subquery,
  32. Application Programs, User Interface, Forms, Tips for User Friendly Interface
  33. Designing Input Form, Arranging Form, Adding Command Buttons
  34. Data Storage Concepts, Physical Storage Media, Memory Hierarchy
  35. File Organizations: Hashing Algorithm, Collision Handling
  36. Hashing, Hash Functions, Hashed Access Characteristics, Mapping functions, Open addressing
  37. Index Classification
  38. Ordered, Dense, Sparse, Multi-Level Indices, Clustered, Non-clustered Indexes
  39. Views, Data Independence, Security, Vertical and Horizontal Subset of a Table
  40. Materialized View, Simple Views, Complex View, Dynamic Views
  41. Updating Multiple Tables, Transaction Management
  42. Transactions and Schedules, Concurrent Execution, Serializability, Lock-Based Concurrency Control, Deadlocks
  43. Incremental Log with Deferred, Immediate Updates, Concurrency Control
  44. Serial Execution, Serializability, Locking, Inconsistent Analysis
  45. Locking Idea, DeadLock Handling, Deadlock Resolution, Timestamping rules