SQL SERVER -2005

SQL SERVER -2005

A database management, or DBMS, gives the user access to their data and helps them transform the data into information. Such database management systems include dBase, paradox, IMS, SQL Server and SQL Server.  These systems allow users to create, update and extract information from their database.

A database is a structured collection of data.  Data refers to the characteristics of people, things and events.  SQL Server stores each data item in its own fields.  In SQL Server, the fields relating to a particular person, thing or event are bundled together to form a single complete unit of data, called a record (it can also be referred to as raw or an occurrence).  Each record is made up of a number of fields.  No two fields in a record can have the same field name.

During an SQL Server Database design project, the analysis of your business needs identifies all the fields or attributes of interest.  If your business needs change over time, you define any additional fields or change the definition of existing fields.

SQL SERVER TABLES

SQL Server stores records relating to each other in a table.  Different tables are created for the various groups of information. Related tables are grouped together to form a database.

PRIMARY KEY

Every table in SQL Server has a field or a combination of fields that uniquely identifies each record in the table.  The Unique identifier is called the Primary Key, or simply the Key.  The primary key provides the means to distinguish one record from all other in a table.  It allows the user and the database system to identify, locate and refer to one particular record in the database.

RELATIONAL DATABASE

Sometimes all the information of interest to a business operation can be stored in one table.  SQL Server makes it very easy to link the data in multiple tables. Matching an employee to the department in which they work is one example.  This is what makes SQL Server a relational database management system, or RDBMS.  It stores data in two or more tables and enables you to define relationships between the table and enables you to define relationships between the tables.

FOREIGN KEY

When a field is one table matches the primary key of another field is referred to as a foreign key.  A foreign key is a field or a group of fields in one table whose values match those of the primary key of another table.

REFERENTIAL INTEGRITY

Not only does SQL Server allow you to link multiple tables, it also maintains consistency between them.  Ensuring that the data among related tables is correctly matched is referred to as maintaining referential integrity.

DATA ABSTRACTION

A major purpose of a database system is to provide users with an abstract view of the data.  This system hides certain details of how the data is stored and maintained. Data abstraction is divided into three levels.

Physical level:  This is the lowest level of abstraction at which one describes how the data are actually stored.

Conceptual Level:  At this level of database abstraction all the attributed and what data are actually stored is described and entries and relationship among them.

View level:  This is the highest level of abstraction at which one describes only part of the database.

ADVANTAGES OF RDBMS

• Redundancy can be avoided
• Inconsistency can be eliminated
• Data can be Shared
• Standards can be enforced
• Security restrictions ca be applied
• Integrity can be maintained
• Conflicting requirements can be balanced
• Data independence can be achieved.

DISADVANTAGES OF DBMS

A significant disadvantage of the DBMS system is cost.  In addition to the cost of purchasing of developing the software, the hardware has to be upgraded to allow for the extensive programs and the workspace required for their execution and storage.  While centralization reduces duplication, the lack of duplication requires that the database be adequately backed up so that in case of failure the data can be recovered.