Structured Query Language (SQL)

Introduction

SQL or Structured Query Language is the most common language used to retreive data from a DBMS. SQL is written in a certain form so that it can be properly understood by the DBMS in order to return the desired information. It is a skill which requires much practice, when dealing with any database. The more entities and data in a given database, the more complex the SQL needed to retreive data. SQL is not only used to retreive data, but also to manage data in a DBMS.

Examples

1-
SELECT DISTINCT FirstName FROM tblPeople;

Result: a list of people's first names with no names duplicated.

2-
SELECT TOP 2 FirstName, LastName, Sex, Salary
FROM tblPeople
ORDER BY Salary;

Result: four fields for the two lowest paid employees.

3-
SELECT Avg([Salary]) AS AvgSalary FROM tblPeople;

Result: one field (and one record) holding average Salary.

References

Jessup, Valacich & Wade. (Prentice Hall, Toronto). Information Systems Today: Why IS matters? 2006

Meta-data

Role and Importance

"Metadata is basically data about data". Data within a DBMS includes fields such as name, city, phone #, etc.. So, information or data about this data would include the size of the given fields or the format of the information that is allowed to be entered in the field such as numbers or letters. Similarly, if you were to transfer a database without transferring the metadata as well then the data would be virtually un legible. The metadata is also data on how the data should be extracted or transformed so that it can be interpreted by the end-user.Uploaded from: http://www.fao.org/aims/img/metadata_element_set.jpg

To make the importance and role of metadata more clear, it can also be applied to documents on a personal computer. For instance, a word processing document has meta-data attached to it, which is information about the document itself. The metadata for such a document could be date last modified, or file size. In terms of this blog, the metadata is the labels for the post, which enables searchers to find blogs on a subject of interest. So if someone entered metadata, meta-data, dbms metadata, etc.. there is a possibility that this blog could present itself to the user because metedata is one of the labels attached to the post.

References

Metadata definition. Accessed April 6,2009 from: http://www.techterms.com/definition/metadata

Architecture

DBMS Architecture

The basic architecture of most DBMSs is relatively similar. The three-schema architecture is the basis of DBMS and consists of an external schema, logic schema and internal schema.

• The external schema defines one view of the data as it is seen by specific applications or end-users.
• The conceptual or logic schema defines data as it is viewed from the perspective of the system designer.
  
• Finally, the internal schema of the DBMS, defines data as it is arranged and stored inside the application itself and is totally dependent on particular implementation.

Uploaded from: http://www.gitta.info/DBSysConcept/en/html/DBMSArchitec_learningObject1.html

As a user enters commands regarding retrieval or manipulation of data, it is entered into the DBMS on the external end of the three schema architecture and further through the process it is translated into machine language on the internal end, where it can then be pulled from the stored data.
The process of normalization is used to organize the data within a DBMS so that it is more easily maintained and manipulated.

Examples

Oracle- A large enterprise DBMS. Relatively expensive, but worth the expense due to it's reliability. It includes many features which are important to large companies. It is able to run queries very quickly and using less disks.

MySQL-Opensource DBMS. Free to users and the public to improve the software and make adjustments.

AccPac- Accounting based DBMS

References

Three schemes architecture. (2009) Accessed April 6, 2009 from: http://www.gitta.info/DBSysConcept/en/html/DBMSArchitec_learningObject1.html

Oracle web-site: http://www.oracle.com/index.html

DBMS Advantages for Organizations

DBMS in Business

Database Management Systems are arguably the most useful tools for managing information within almost any organization. If you imagine an organization without a DBMS, it is likely that you may visualize room after room full of papers, folders and filing cabinets. For some organizations, it would be impossible to organize or even have adequate space to hold the amount of information that the company has on customers, products, competition, etc...the list goes on and on. Now, imagine that you as an employee need to find all of your current customers who are male, between the age of 35 and 50, and are divorced. Perhaps you are searching for all of these individuals to target for a new product that the company believes will appeal to such individuals. This task is virtually impossible without a DBMS within the organization. With the use of a DBMS the corporation could pinpoint all of these individuals in a matter of seconds. Whereas, without such a system retreival of such information would take many individuals, potentially an infinite amount of time.

Uploaded from: http://pro.corbis.com/images/AX051832.jpg?size=67&uid={83C31DA5-4F6D-4722-9A2A-2438D732E98F}

The costs associated with such data management without the use of a DBMS would ultimately lead to the deterioration and destruction of any organization. Although many DBMSs for large organizations can cost very large sums of money and require many IT employees to maintain and uprade on a regular basis, they don't compare to the alternative. If you consider the number of times each day that multiple employees use company information in a number of different ways, it is impossible to justify using techniques that were used before the introduction of DBMSs.

Uploaded from: http://goselect.com.au/computerservices/images/computer.jpg

References

Encyclopedia of Business: Database Management Systems. (2nd Ed.) Acceessed April 6th, 2009 from: http://www.referenceforbusiness.com/encyclopedia/Cos-Des/Database-Management-Systems.html#BUSINESS_APPLICATIONS

A brief history of modern RDBMS IT management. (2002). Accessed April 6th, 2009 from: http://www.mountainman.com.au/software/history/intro.html

DBMS History

DBMS Brief History

The first DBMS was introduced in the 1960's. By the mid-1960's there were several basic database systems that were available for commercial use. In 1971, Charles Bachman of CODASYL intoduced the industry standard for the navigational DBMS and soon there was the emergence of DBMS programs based on the "CODASYL Approach". In 1968, IBM also introduced IMS, which was generally the same as that of the earlier navigational DBMSs.

The earliest introductions of DBMS, were classified as either network or hiereachical models. Both of these early models of DBMS fall under the category of navigational DBMS due to the method used to retreive data. Basically, in order to retreive a pursued set of data, the programmer had to go through the data one by one until the desired record was returned.

Hierarchical Model (basic navigational DBMS)

Uploaded from www.peterboyd.com/cmconf.htm

The difference between network and hierarchical models, was that the network model allowed a record to have multiple parent and child records. On the contrary, the hierarchical model allowed each record to have only one parent record and multiple child records. The only real advantage to the network model was that in allowed for more natural and simple retreival of data from a DBMS. Both forms of DBMS eventually became obsolete, as computers became more capable of performing complex functions and the relational DBMS was introduced in the early 1980s.

Uploaded from http://www.ibm.com/developerworks/xml/library/x-matters8/relat.gif

The introduction of the relational DBMS, revolutionized how data is stored and retreived to this day. Invented by E.F. Codd, the basis of the relational model was that every variable within a database is organized under a given property or relation between entities. These properties are pieces of information that the database contains. A query is used or entered into the database in order to retreive some piece of information from the database. The major difference betweeen navigational DBMS and relational DBMS is that with the introduction of the relational DBMS, data retreival became a function that could be performed in a matter of seconds or minutes rather than hours or days

References

Learning Computin History: Database History. (Dec. 5, 2004). Retreived April 6th, 2009 from http://www.comphist.org/computing_history/new_page_9.htm

A brief History of Modern RDBMS IT Management. (2002). Retreived April 6th, 2009 from http://www.mountainman.com.au/software/history/it2.html