SQL Server Mentalist

Hi folks ,

Continuing from my last post on same series also we have completed following topics in my previous post as follows :

• BISQL # 76 – Laymen to SQL Developer # 5 – Assignment #1 – Part #4 – What is Data Independence and All information related to Data Independence in DBMS
• BISQL # 75– Laymen to SQL Developer # 4 – Assignment #1 – Part #3 – What is Data Abstraction and All information related to Data Abstraction in DBMS
• BISQL # 72 – Laymen to SQL Developer # 3 – Assignment #1 – Part #2 – Why DBMS or What are Limitations of file processing systems « SQL Server Mentalist
• BISQL # 69–Laymen to SQL Developer # 1 – Introduction to brand New SQL Server Zero to Basics Series
• BISQL # 70 – Laymen to SQL Developer # 2 – Assignment #1 – Part #1–Introduction to Data and database Why Database exist ?

In this article we are covering

Introduction to Database Schemes and Database Instances

What is Conceptual Data Scheme

What is a database instance

How Vision of Database Developer should be ?

Some Basic Definition / Review of Definition’s

What is a DBMS ,DBA ?

Database Architectures – RDBMS, OODBMS, and ORDBMS

Advantages of having a DBMS

The Three-Schema DBMS Architecture

The DBMS Languages

Database System Applications

DBMS Interface

This is may summary article for many of you as I want all definition at one place so as consume your time in to and forth in my learning post.

Introduction to Database Schemes and Database Instances

Independent from the database model it is important to differentiate between the description of the database and the database itself.

What is database scheme Or metadata ?

The description of the database is called database scheme or also metadata . The database scheme is defined during the database design process and changes very rarely afterwards.
The actual content of the database, the data, changes often over the years.

What is a database instance
A database state at a specific time defined through the currently existing content and relationship and their attributes is called a database instance

How Vision of Database Developer should be ?

The following illustration shows that a database scheme could be looked at like a template or building plan for one or several database instances.

In the next diagram also we can distinguished how one Data Base Developer will change his/her vision with any Data base problem with comparison with normal user.

When designing a database it is differentiated between two levels of abstraction and their respective data schemes, the conceptual and the logical data scheme.

What is Conceptual Data Scheme ?

A conceptual data scheme is a system independent data description. That means that it is independent from the database or computer systems used.

What is  logical data scheme ?

A logical data scheme describes the data in a data definition language DDL of a specific database management system.

A logical data scheme describes the data in a data definition language DDL of a specific database management system.

Relation between logical and Conceptual data scheme

The conceptual data scheme orients itself exclusively by the database application and therefore by the real world. It does not consider any data technical infrastructure like DBMS or computer systems, which are eventually employed.

Entity relationship diagrams and relations are tools for the development of a conceptual scheme.
When designing a database the conceptual data scheme is derived from the logical data scheme .

This derivation results in a logical data scheme for one specific application and one specific DBMS. A DB-Development System converts then the logical scheme directly into instructions for the DBMS.

Some Basic Definition / Review of Definition’s 

What is a database?

A database stores electronic data in an organized and accessible manner. The size and scope of databases can vary wildly, from a small database used by an individual to file phone numbers, to an extremely large enterprise database that stores several terabytes of information accessed by tens of thousands of individuals. A database’s tables and rows hold data in much the same way as a file cabinet or an address book, but they allow users more flexibility in what kinds of searches they can perform on the data as well as how they want to view the data

What is a DBMS?

A Database Management System (DBMS) is a software system that is used both to create databases and manage the information stored within them. The architecture of the DBMS will frequently determine or limit the possible uses of the databases it creates. Some DBMS’s work best for creating single-user databases, while others can build databases that accommodate multiple users in larger corporate environments.

What is a DBA?

A Database Administrator (DBA) is crucial to any organization that has mission-critical information stored in its databases. DBAs are responsible for the design and administration of databases, and for ensuring that the highest level of data integrity is maintained. Database maintenance includes performance tuning, monitoring the logs for errors, performing backup and recovery procedures as well as tasks associated with the data stored in the database, which include data transfer, data replication and data cleansing.

Database Architectures – RDBMS, OODBMS, and ORDBMS

A database consists of one or more tables, each containing data stored as individual records. Different database architectures determine how the tables and records are organized or related to one another. The first database architectures – Hierarchical and Networked – have largely been superseded by the Relational, Object-Oriented and Object-Relational architectures. The Relational Data Model, developed by Todd Codd in 1969, allows multiple tables to be related to one another within a database.

For example, one customer’s information could be recorded in separate tables such as "Personal Information", "Marketing Efforts", and "Service Requests". The information stored in these tables will then relate back to the customer’s main record. A relational database management system (RDBMS) also offers flexibility in terms of how the customer’s data can be viewed.

To access the information stored in relational databases, users can either build queries using the Structured Query Language (SQL), or they can utilize a user interface that translates their requests into SQL and displays the results. While the American National Standards Institute (ANSI) approved an early version of SQL as a standard, many RDBMS’s also use customized, proprietary forms of the language. The Object-Oriented database model emerged in the mid-1980s due to the dissatisfaction of some database users with the structural limitations of RDBMS’s. The Object-Oriented model defines each piece of data and its associated processes as an individual object. According to the basic tenets of this model, all information about an object is stored in one place instead of being stored across multiple tables, as is done in the relational model.

An Object-Oriented Database Management System (OODBMS) also integrates more easily with applications that have been written with an Object-Oriented programming language such as C++ or Java. Despite the advantages of the Object-Oriented approach, no standard model for the construction of an OODBMS yet exists. For this reason, at least in part, RDBMS’s still dominate the database market. One effort to combine the best parts of the RDBMS and OODBMS is the Object-Relational Database Management System (ORDBMS). This model allows developers to incorporate the best parts of an RDBMS and an ODBMS. An ORDBMS works with objects like an ODBMS, but also allows SQL-based querying like an RDBMS. Market Leaders As of June 2001, the Oracle Corporation controlled 33.8% of the database market, IBM held 30.1% and Microsoft had 14.9%.

While Oracle once dominated the market, the allegiance has been shifting as major enterprise application vendors such as SAP, PeopleSoft and Siebel Systems have pushed their primary application development efforts to IBM’s DB2 database.

IBM’s recently completed acquisition of Informix has consolidated the number of players in the database market. It remains to be seen what impact the various open source DBMS’s will have on the market, and the extent to which they will erode the market share of the current leaders. The Future of Databases Databases play an important role in both data management and data storage in today’s Information Age. The high value placed on information-gathering by companies as well as individuals requires efficient methods of storing and accessing information. Database architectures and products will need to be even more highly scalable to accommodate and support this increasing production of data in the future.

According to Michael Lesk, the huge amount of data would take several billion gigabytes or several thousand petabytes to store. Today, the importance and impact of databases is unquestioned, as government organizations, academic institutions and business entities create and maintain extensive databases containing all kinds of information ranging from natural-language text documents, statistical tables, financial data, and multimedia objects to data of a scientific and technical nature. Many databases are composed of metadata, which means the records hold data about data such as information about the size and character of another database rather than primary source content such as a person’s name and address. Database technologies, including architecture and access methods, are rapidly developing to keep pace with this demand for information management mechanisms.

Database designers and managers face many challenges that reflect the complexity of the burgeoning information environment. Database technologies must handle massive amounts of data, extract useful information from these repositories, and have the ability to reflect relationships between data maintained in different databases. In addition, the architecture and system must provide integrity, recovery, concurrency and security

To answer these challenges, the three fundamental database models, hierarchical, network and relational, have served as a foundation for developing more powerful and flexible data models, such as the extended-relational and object-relational models. Well defined architecture and data schema assure efficient, logical data storage which increases database capacity and extends the capabilities of query languages and other access methods. In addition, data mining creates useful information by identifying related data within the vast stores. Researchers now wrestle with the complexities of relational issues and interoperability. Researchers are now able to use meta data more efficiently to improved data dissemination. Researchers are also able to use federated strategies for distributed databases.

Advantages of having a DBMS

• Controlling redundancy
• Restricting unauthorized access
• Providing persistent storage for program objects and data structures.
• Permitting interfacing and actions using rules
• Permitting multiple user interfaces
• Representing complex relationships among data.
• Enforcing Integrity Constraints
• Providing backup and recovery

Data warehouses and on-line analytical processing (OLAP) systems are used in many companies to extract and analyze useful information from very large databases for decision making.

The Three-Schema DBMS Architecture

The goal of the three-schema architecture is to separate the user applications and the physical database. In this architecture, schemas can be defined at the following three levels.

• The internal level has an internal schema, which describes the physical storage structure of the database. The internal schema uses a physical data model and describes the complete details of data storage and access paths for the database.
• The conceptual level has a conceptual schema, which describes the structure of the whole database for a community of users. The conceptual schema hides the details of physical storage structures and concentrates on describing entities, data types, relationships, user operations, and constraints.
• The external or view level includes a number of external schemas or user views. Each external schema describes the part of the database that a particular user group is interested in and hides the rest of the database from that user group.

The DBMS Languages

Once the design of a database is completed and a DBMS is chosen to implement the database base, the first order of the day is to specify conceptual and internal schemas for the database and any mappings between the two. In many DBMSs where no string separation levels is maintained, one language, called the data definition language (DDL), is used by the DBA and by database designers to define both schemas. The DBMS will have a DDL compiler whose function is to process DDL statements in order to identify descriptions of the schema constructs and to store the schema description in the DBMS catalog.

In DBMS where a clear separation is maintained between the conceptual and internal levels, the Data Definition Language (DDL) is used to specify the conceptual schema only. Another language, the storage definition language (SDL), is used to specify the internal schema. The mappings between the two schemas may be specified in either one of these languages. For a true three-schema architecture, we would need a third language, the view definition language (VDL), to specify user views and their mappings to the conceptual schema, but in most DBMS the DDL is used to define both conceptual and external schemas.

Once the database schemas are compiled and the database is populated with data, users must have some means to manipulate the database. Typical manipulations include retrieval, insertion , deletion and modification of the data. The DBMS provides a data manipulation language (DML) for these purposes.

In current DBMSs, the preceding types of languages are usually not considered distinct languages; rather, a comprehensive integrated language is used that includes constructs for conceptual schema definition, view definition, and data manipulation. Storage definition is typically kept separate, since it is used for defining physical storage structures to fine-tune the performance of a database system. A typical example of a comprehensive database language is the SQL relational database language which represents a combination of DDL, VDL, and DML, as well as statements of constraint specification and schema evolution.

Many DBMSs have forms specification language for form-based interfaces.

SQL uses a combination of relational-algebra and relational-calculus constructs. The SQL language has several parts:

• Data-Definition Language (DDL) : The SQL DDL provides commands for defining relation schemas, deleting relations, and modifying relation schemas.
• Interactive Data-Manipulation Language (DML) : The SQL DML includes a query language based on both the relational algebra and the tuple relational calculus. It includes also commands to insert tuples into, delete tuples from, and modify tuples in the database.
• View Definition : The SQL DDL includes commands for defining views.
• Transaction Control : SQL includes commands for specifying the beginning and ending of transactions.
• Embedded SQL and Dynamic SQL : Embedded and dynamic SQL define how SQL statements can be embedded within general-purpose programming languages, such as C, C++, Java, PL/I, Cobol, Pascal, and Fortran.
• Integrity : The SQL DDL includes commands for specifying integrity constraints that the data stored in the database must satisfy. Updates that violate integrity constraints are disallowed.
• Authorization : The SQL DDL includes commands for specifying access rights to relations and views.

Database System Applications :

• Banking : For customer information, accounts, and loans, and banking transactions.
• Airlines : For reservations and schedule information. Airlines were among the first to use databases in a geographically distributed manner—terminals situated around the world accessed the central database system through phone lines and other data networks.
• Universities : For student information, course registrations, and grades.
• Credit Card Transactions : For purchases on credit cards and generation of monthly statements.
• Telecommunication : For keeping records of calls made, generating monthly bills, maintaining balances on prepaid calling cards, and storing information about the communication networks.
• Finance : For storing information about holdings, sales, and purchases of financial instruments such as stocks and bonds.
• Sales : For customer, product, and purchase information.
• Manufacturing : For management of supply chain and for tracking production of items in factories, inventories of items in warehouses/stores, and orders for items.
• Human Resources : For information about employees, salaries, payroll taxes and benefits, and for generation of paychecks.

DBMS Interface

1. Menu-Based Interfaces for Web Clients or Browsing : These interfaces present the user with lists of options, called menus, that lead the user through the formulation of a request. Menus do away with the need to memorize the specific commands and syntax of a query language; rather, the query is composed step by step by picking options from a menu that is displayed by the system. Pull-down menus are a very popular technique in Web-based user interfaces. They are also often used in browsing interfaces, which allow a user to look through the contents of a database in an exploratory and unstructured manner.
2. Forms-Based Interfaces : A forms-based interface displays a form to each user. Users can fill out all of the form entries to insert new data, or they fill out only certain entries, in which case the DBMS will retrieve matching data for the remaining entries. Forms are usually designed and programmed for naive users as interfaces to canned transactions. Many DBMSs have forms specification languages, which are special languages that help programmers specify such forms. Some systems have utilities that define a form by letting the end user interactively construct a sample form on the screen.
3. Graphical User Interfaces : A graphical interface (CUI) typically displays a schema to the user in diagrammatic form. The user can then specify a query by manipulating the diagram. In many cases, CUIs utilize both menus and forms. Most CUIs use a pointing device, such as a mouse, to pick certain parts of the displayed schema diagram.
4. Natural Language Interfaces : These interfaces accept requests written in English or some other language and attempt to "understand" them. A natural language interface usually has its own "schema," which is similar to the database conceptual schema, as well as a dictionary of important words. The natural language interface refers to the words in its schema, as well as to the set of standard words in its dictionary, to interpret the request. If the interpretation is successful, the interface generates a high-level query corresponding to the natural language request and submits it to the DBMS for processing; otherwise, a dialogue is started with the user to clarify the request.
5. Interfaces for Parametric Users : Parametric users, such as bank tellers, often have a small set of operations that they must perform repeatedly. Systems analysts and programmers design and implement a special interface for each known class of naive users. Usually, a small set of abbreviated commands is included, with the goal of minimizing the number of keystrokes required for each request.
6. Interfaces for the DBA : Most database systems contain privileged commands that can be used only by the DBA’s staff. These include commands for creating accounts, setting system parameters, granting account authorization, changing a schema, and reorganizing the storage structures of a database.

   Hope this Helps !!!!

   Thanks for visiting my blog !!

   Hope you will like this post on Database Schemes ,Database Instances and Some Basic Definitions.

   If you really like reading my blog and understood at lest few thing then please don’t forget to subscribe my blog .

   If you wan daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog

   Where todays links are

   Link Resource Website