Database System

Information is emit from some raw facts which is known as Data.

A computer based system which have purpose to record and save data is called as database or record keeping system.

Any such enterprise maintains data about its operations. This is called operational data.

A database management system provides centralized control of data. A DBMS has the following advantages:

• Redundancy of data is reduced. Each application does not have to maintain its own data files. Data is integrated.
• The data can be shared. This not only implies that the existing applications can share data in the database but also means that new applications can be developed to operate using the same database.
• The data in large systems needs to be integrated and shared.
• Data integration and sharing represents a major advantage in multi user environments. Integration of data is the unification of several distinct files with little or no redundancy among them.
• Database Models
• Hierarchical Model
• This model is like a hierarchical tree structure.
• It is used to construct a hierarchy of records in the form of nodes and branches.
• Closely related information in the Parent-Child structure is stored together as a logical unit known as a node.
• A parent unit can have many child units, but a child unit can have only one parent.

In order to gain an understanding of the various database models, we take an example of a sample database consisting of suppliers, parts and shipments.

The figure shows a  possible hierarchical view for the supplier-parts database.

In this view, data is represented by a simple tree structure, with parts superior to suppliers.

The user sees four individual trees, or hierarchical occurrences, one for each part.

Each tree consists of one part record occurrence, together with a set of subordinate supplier record occurrences, one for each supplier of the part. Each supplier occurrence includes the corresponding shipment quantity.

The record type at the top of the tree - the part record type in the example given above - is usually known as the root. In general, the root may have any number of dependents, and so on, to any number of levels.

The drawbacks of this model are:

• The hierarchical structure is not flexible enough to represent all the relationships that exist in the real world.
• It cannot demonstrate the over all data model for an enterprise because of the non-availability of actual data at the time of designing the data model.
• It cannot represent many-to-many relationship
• The Hierarchical model is used only when the concerned data has a clearly hierarchical character with a single root, for example the DOS directory structure.
• Network Model
• Multiple parent-child relationships are used in the network model.
• Rapid and easy access to data is possible in this model due to multiple access paths to the data elements.

Network model

The figure given above  shows a network view for the suppliers-and-parts database. In this view, as in the hierarchical approach, records and links represent data.

A network is a more general structure than a hierarchy because a given record occurrence may have any number of immediate superiors and dependents.

The network approach thus allows us to model a many-to-many relationship.

In addition to the record types representing the suppliers and parts themselves, we introduce a third type of record, which is called the connector.

A connector occurrence, represents the association (shipment) between a supplier and a part, and contains data describing the association.

All connector occurrences for a given supplier or part are placed on a chain starting at and returning to that supplier or part.

Each connector thus has exactly two chains, one supplier chain and one part chain.

What is RDBMS?

In an RDBMS, the data is organized into tables that consist of rows and columns of data.

The rows of tables are referred as tuples and the columns of data are referred as attributes.

The data in one table may not be related to another table.

A relational database management system (RDBMS) has the following properties:

• In an RDBMS, data is represented in the form of tables.
• In an RDBMS, there are no hard-code relationships between tables.
• An RDBMS doesn't require the user to understand how data is stored.
• An RDBMS provides information online content and structure in system table
• An RDBMS supports the concept of NULL values.
• Data is organized in terms of rows and columns in a table known as relations in an RDBMS
• The position of a row in a table is of no importance in an RDBMS
• The intersection of a row and column must give a single value in an RDBMS
• All values appearing in the columns are derived from the underlying domain in an RDBMS.
• Values in a row must be unique.
• Column Names must be unique in an RDBMS

In relational database, there are no hard-coded relationships defined between tables. A relationship can be specified at any time using any column name.

Some of the main components of an RDBMS are:

Database

A database is a collection of data that is stored in the tables.

The tables are in the format of rows and columns.

The data stored in the tables are related to each other.

The data is stored in a database is related to a particular subject such as inventory system for manufacturing, customer and supplier information for an enterprise.

Tables

A Table in a database is used to store data. The tables are made up of rows and columns.

A database can contain many tables. The characteristics of a table are mentioned below:

•     Table names are unique, and identify the entity.
•     Tables consist of rows (also called records) and columns (also called fields).

Table Restrictions

Certain table restrictions, which are mentioned below, have to be followed while the user creates or works with the table.

•   Table names must be unique within a database (enforced by SQL Server 7.0).
•   Column names must be unique within a table (enforced by SQL Server 7.0).
•   Columns

Columns are depicted as attributes in E/R diagram. Columns are of two types

•    Decomposable Columns
•    Non-Decomposable Columns
•    Decomposable Columns
•   Decomposable columns are those, which can be further, divided into smaller columns, as shown in figure given below.
•    Non-Decomposable Columns
•    Non-Decomposable columns are those, which cannot be further, divided into smaller columns, as shown in figure.3

Column Restrictions

• Not Null: If the column is restricted by the not null parameter, then the column must posses some valid value.
• No Duplicate: This restriction prevents any column from possessing the same value more than once.
• No Change: This is a column restriction that prevents the values in the column from being changed.

Codd's Rule

Information Representation

In the relational model, all the information is explicitly and logically represented by the data values in the tables.

A definition of this system is stored in the form of a catalog which is known as a data dictionary. .

Guranteed Access

Every value must be logically addressable by using a combination of table name, primary key value and column name.

Systematic Treatment of Null Values

In database management systems, null values can be used to represent missing and inapplicable information.

This support for null values must be consistent throughout the RDBMS, and independent of data types (for example, a null value in char field must mean the same as in an integer field).

Database Description Rule

A description of the database is stored and maintained in the form of tables as is done while defining the data.

A data dictionary should be present within the

It should be possible to examine the data dictionary using a high level language known as SQL.

High-Level Update, Insert, Delete

A database cannot be called relational if it uses a single-record-at-a-time procedural technique when it comes to manipulating the data.

Physical Data Independence

User access to database through application programs must remain logically consistent even when storage representation or access methods to the data are changed.

Applications must be limited to interfacing with the logical layer to enable the enforcement of this rule.

Logical Data Independence

Applications must be independent of the changes made to the base tables. This rule allows many types of database design changes to be made dynamically, without the users being aware of them.

A single table should be divisible into one or more other tables, provided it preserves all the original data, and maintains the primary key in each and every fragment/table.

Integrity Rule

Integrity constraints specific to a particular relational database must be definable in the relational data sub-languages and storable in the group, not in the application programs.

A relational system must be able to manage databases entirely through its relational capabilities.

Data Modeling

The relationships between the collection of data in a system may be graphically represented using data modeling.

If the relationships are complex, data modeling provides a simplified approach to the structured design of the system.

It helps determine the data objects in the system, the composition of each, and the relationships that exist between them.

Data Modeling is achieved in two levels - the first level builds the conceptual model of the data, using E-R Modeling. The second level organizes this model better, by removing redundancies, through a process called Normalization. The Normalized model is then converted into the Physical Database.

Entity Relationship Model

Entity Relationship Modeling is a technique for analysis and logical modeling of a system’s data requirements. It uses three basic concepts, entities, their attributes and the relationships that exist between the entities

Entity

An Entity is any object, place, person, concept, and activity about which an enterprise records data.

It is an object, which can have instances or occurrences.

Each instance should be capable of being uniquely identified.

Each entity has certain properties, or Attributes associated with it and operations applicable to it.

An entity type is a set of things which share common properties. For e.g., STUDENT, COURSE etc.,. An entity type is usually denoted in upper case.

An entity instance is a specific individual thing. For e.g., Mohit and Physics are instances of the entities students and course respectively. An entity instance is usually denoted in lower case.

Attributes

Attributes are data elements that describe an entity.

If the attribute of an entity has more attributes that describe it, then should be categorized as a separate entity in its own right.

Attributes can either be listed next to the entities, or placed in circles and attached to the entities

Relationships

Relationships can be defined as an association among entities. Separate entities can have relationships with each other. Three types of relationship exist as given below:

One–to-One Relationship

One-to-Many Relationship

Many-to-Many Relationship

One-to-One Relationship

Let’s take the example of an institute, where one person can head one DEPARTMENT say Training. One person can’t head more than one DEPARTMENT.

One-To –Many and Many-To-One Relationship

Consider the following examples where:

Many authors can write one book, and a single author can write many books.

A Salesman can sell many products and one product can be sold by many salesmen.

Many to Many Relationship

Lets take an example where COURSE can be offered to many STUDENTS and many STUDENTS can enroll for a given COURSE.

Normalization is a process of removing redundant data from the database.

It is a process by which unsatisfactory relational schemas are decomposed by breaking up their attributes into smaller relational schemas that possess desirable properties.

The three normal forms i.e. INF, 2NF and 3NF are explained in the following discussion.

First Normal Form (1NF)

The 1NF requires each cell to hold atomic or non-decomposable data.

A column is split into multiple columns, one for each subset of data, if it holds composite data.

If the cell holds multiple values then the row is split into multiple rows, one for each value.

It states that the domains of attributes must be Atomically NULL value.

The 1NF disallows having a set of values, a tupple of values, or a combination of both as an attribute value for a single tupple. In this normal form due to its atomic property, there may be some redundancy of data.

Second Normal Form(2NF)

Second Normal Form is based on the concept of full functional dependency.

Functional dependency X Y ( X and Y are set of attributes) is a full functional dependency

if removal of any attribute from X means that the dependency does not hold any more.

Third Normal Form (3NF)

The 3NF states that no attribute must depend on any other key of the table except the composite primary key.

The first step towards Normalization is to convert the E-R Model into Tables or Relations.

The next step is to examine the tables for redundancy and if necessary, change them to non-redundant forms.

This non-redundant model is then converted to a database definition, which achieves the objective of the Database Design Phase.

What is SQL Server 7.0?

Today, the relational database is at the core of the information systems for many organizations, both public and private, large and small. Informix, Sybase, Oracle and SQL Server are RDBMS having worldwide acceptance.

SQL Server 7.0 is a Structured Query Language (SQL) based client server Relational Database Management System (RDBMS).

Client Server: This system is made up of a server and a group of client.

In this system, the server is at the central location and manages the resources that are used by the different clients situated at different locations.

In this system the database files and DBMS software reside on the server.

Structured Query Language (SQL): To communicate or work with the data stored in the database, there are a proper set of commands and statements defined by the DBMS software.

The SQL is the most popular language used for communicating with the relational databases. ANSI and ISO both have defined standards for SQL.

SQL Server Features

Supports Unicode data type: SQL Server 7.0 supports Unicode data type. This data type allows storage of data belonging to different languages into one database. This is an in-built feature of SQL Server 7.0.

Supports Upgrade utility:  Databases can be easily transferred from SQL Server 6.x to 7.0, by using a fully functional upgrade utility. The users can easily upgrade from SQL Server version 6.5 to SQL Server version 7.0.

Supports Full Text Search:  SQL Server 7.0 provides full-text search for character-based data that is stored in tables. This facility can be used to create special indexes of all important words in selected columns of selected tables. Support is provided for several languages.

Supports MultiSite Management:  SQL Server can manage multiple servers using one centralized server. Two or more servers can be grouped together into logical functioning units by business units or by department names

Easy to Install: SQL Server 7.0 comes up with the additional administrative tools that helps you to install and manage the database easily,

Scalability: SQL Server 7.0 easily runs on Windows 95/98, Windows NT Workstation, and Windows NT Server. This means that a single program is shared on all environments.

Data Replication: SQL Server 7.0 provides the facility of automatic replication of data. Replication means transfer of data from one geographical location to another by maintaining the copies of the original data.

Data Marts & Data Warehousing: Data marts & data warehouses copy the data from the decision-support database for querying on the data easily, without affecting data manipulation.

English Query: This feature is used for helping those users who don’t know SQL statements to query on the data. This feature allows those users to use simple English in place of SQL statements.

Data Transferring Services: This feature makes the movement of data.This feature also allows direct transferring of data between SQL Server 7.0 and other databases like MS Access or Oracle.

Distributed Transaction: SQL Server 7.0 uses a tool, namely, Microsoft Distributed Transaction Coordinator (MSDTC), which helps the client to make changes in all the connected servers. It also makes sure that changes made by the client get reflected either on all the connected servers or on none of the connected servers.

Visual Administration Tools & Wizards: SQL Server 7.0 provides a GUI interface SQL Enterprise Manager which takes advantage of using wizards for performing administrative tasks.

Components of SQL Server Architecture

The SQL Server architecture can be understood by considering it as a onglomerate of several different functional levels of architecture. These are:

Client-Server Architecture

Server Architecture

Client-Server Architecture: This system is made up of a server and a group of client.

The database in this system is located at a central computer, known as the Server.

In a client/server system, the server is responsible for creating and manipulating database objects such as tables and indexes

The Server responds back whenever a request is made from the client side.

The client application performs all user interactions including retrieval and manipulation of data through the graphical user interface.

The relational database server reduces the network traffic by sending only the requested data to the user/client.

The Client/Server architecture, also referred to as distributed computing implies that the processing of data can be done on different computers across a network, i.e., different parts of the SQL Server application run on two or more computers at the same time.

Server Architecture: The server is responsible for taking actions required to respond back to the client, when any query is made by the client.

The server involves components like Net Libraries, Open Data Services (ODS), and Database Server.

The Server uses a set of functions called Net libraries to communicate with the network.

An interface that resides between net libraries and Database server is known as an ODS

It is an Application Programming Interface, which holds the functions and statements to call T-SQL functions.

The Database Server receives requests from the ODS and processes them and builds the result sets that are returned to the client.

Main Components of SQL Server 7.0

The Distributed Management Framework (DMF) is a collection of administrator applications.

It is an enterprise wide administrative framework used to manage the server.

The DMF includes object services and components that are used to manage SQL Server 7.0, as shown in figure given below. DMF includes:

Distributed Management Object is a collection of objects that are used for administering SQL Server 7.0 and its services.

SQL Server 7.0 Services and SQL Server 7.0 Agent form the back-end for the database and provide the core management services managed by the SQL Agent.

They provide direct access to the SQL Server 7.0.

SQL Enterprise Manager relies on SQL Server 7.0 Executive and SQL Distributed Management Objects.

The Distributed Management Framework is divided into three parts:,

SQL Enterprise Manager and application available at the client end

The SQL Object Library which is the interface between the server and the client

The server/backend which includes SQL Server 7.0 Executive Service (Windows NT Services) and SQL Server 7.0 Service.

Transact-SQL: Transact-SQL (T-SQL) is an enhanced version of Structured Query Language (SQL). It a high level language which is used for retrieving and manipulating the data in the database, create database and database objects such as tables, indexes for relational database management system

SQL Enterprise Manager is a graphical user interface based tool used to administer a single or multiple servers.

This can be used by administrators can manipulate and view servers from a central location.

The Enterprise Manager has the feature of scheduling tasks through which the user can schedule tasks to run automatically on the SQL Server at a specified time.

The SQL Enterprise Manager can be used to do the following:

Start, Stop and Configure servers
Manage user-accounts and server logins
Back up and restore databases and transaction logs
Create and manage databases and devices
Create and schedule tasks
Execute and analyze queries
Generate SQL scripts
Manage database objects
Manage object and statement permissions
Create and control user accounts and groups
Display Server statistics
Set up Alerts
Check Database consistency
Set up and manage database replication
View object dependencies
View the SQL Server 7.0 error log