SQL

Introduction



The standard in the industry for interacting with RDBMS (Relational Database Management System) is through Structured Query Language (SQL). It is not a programming language like COBOL or Java. SQL is a sublanguage, and it can process sets of data as groups unlike programming language.(1)

Using SQL statements, users can instruct the DBMS to create, modify tables, enter and maintain data, and retrieve data for a variety of situations. These commands or statements let users perform interactive searches and set environmental variables.(1)

Two industry-accepted committees set the industry standards for SQL: the American National Standards Institute (ANSI) and the International Standards Organization (ISO).(1)




Examples

• An example code used in SQL to create a table is as follows:

CREATE TABLE STATION
(ID INTEGER PRIMARY KEY,
CITY CHAR(20),
STATE CHAR(2),
LAT_N REAL,
LONG_W REAL); (2)

This will create a table called: STATION with columns: ID, City, State with Latitude and Longitude coordinates.

• Another example of SQL code is:

SELECT ID, CITY, STATE FROM STATION
WHERE LAT_N > 39.7;

This will display the following columns: ID, CITY, STATE from the STATION table where the Latitude Coordinate is higher than 39.7 (2)





(1) http://www.itl.nist.gov/div897/ctg/dm/sql_examples.htm#create%20table
(2) Oracle 9i: SQL Lannes L. Morris-Murphy

Metadata

What is Metadata and what's its purpose?



Metadata is by definition "data about other data" of any type and sort in any media. It is used to facilitate the understanding, characteristics and management usage of data. For effective data management, the Metadata should include data that is coherent with the context of use.(1)




Role



This Metadata is used for better access to the enormous amounts of data stored and managed by different companies. Metadata provides context for data. In data processing, for example, Metadata is definitional, it gives documentation of other data in the application or environment. The term "Metadata" should be used carefully since all data is about something and hence is "Metadata".(1)



Importance



In Databases, Metadata defines data elements and attributes (Name, data type, size, etc), data could be registered about structures and records as well (Length, columns and fields). This is extremely helpful for the reliability of databases and their efficiency. In a library, for example, the data is the content of the titles, and the Metadata is about the title, the author,a description of the content, the physical location and the date of publication. In addition, it protects investment in data, helps the user in understanding data,enables discovery options, and limits Liability. All of these reasons, make Metadata the backbone in understanding a DBMS.(2)





Examples





Examples of Metadata include; Photograph:

• Date and time
• Camera Settings Like:
  
• Focal Length, aperture, exposure

Web Pages:

• Descriptive Text
• Dates
• Keywords(1)

An example of the Metadata used on this website could be mentioned here and it is as follows: blogger.com, blogspot.com, free blog, personal blog, weblog, create blog, new blog"> The information being registered as Metada is all about and relevant to the content of the blogger site. Arguably,search engines like YAHOO and GOOGLE still use website Metadata as part of the search criteria. Metadata information was widely used previously for Search engines until more advanced techniques were implemented, still to this day most websites include Metadata or "Data about other data" in the website.(*)





(3)





(1) http://en.wikipedia.org/wiki/Metadata
(2) http://www.sdvc.uwyo.edu/metadata/why.html
(3) http://www.fao.org/aims/img/metadata_element_set.jpg
(*) This website, and others, mentioned above.

DBMS Architectures

Types



There are three schemes, by which a DBMS is formed



1. The internal scheme; in this level the stored data could be accessed from data warehouses. Let's say I want to drive a car, the first thing i have to do is to get inside, that is in simple terms what the internal scheme is about. It is the level that is closest to the computer.


2. The logical scheme; this is the level where all the work is done... data is manipulated, edited, add-ons are applied, and non usable data is deleted. In addition, it is on this level that good business sense is made out of the raw data entered, or the input that the DBMS has.


3. The external scheme; here is where the information, or data transformed to valuable business output is displayed and reaches the concerned member of the business organization. It is the closest level to the user, or users of a DBMS.(1)


(1)





Examples

• Microsoft access; it was widely used in companies for internal communication, and ease of daily tasks. Use of mass e-mails, pre-generated forms embedded in each of the companies' stores. However; newly developed products much more powerful, and faster, have taken its place. Since time is money in business, saving time using old DBMSs is a problem that is swiftly solved nowadays, thanks to the evolving market of IT. (2)
  

• Oracle; The largest market share in 2006, was for this DBMS software, skyrocketing at 47.1%(3). Companies use oracle for higher needs, it is a very powerful tool. Oracle has the RDBMS storing data logically in the form of tablespaces, it also stores data physically in the form of data files. Tablespaces are able to contain many kinds of memory segments. Most big companies rely on the effectiveness of Oracle to be time efficient, and save operation costs. (4)
  

• DB2; IBM has DB2 actively present in the market and according to "gartner.com"(3) constitutes 21.1 % of the total market share of RDBMSs. It can function on UNIX, LINUX, and WINDOWS, therefore; it makes using it very user friendly, and easily downloadable. This limits problems, and invites more companies to buy and use DB2 in their operations.(5)
  

(1) http://www.gitta.info/DBSysConcept/en/html/DBMSArchitec_learningObject1.html
(2) http://en.wikipedia.org/wiki/Microsoft_Access
(3) http://www.gartner.com/it/page.jsp?id=507466
(4) http://en.wikipedia.org/wiki/Oracle_Database#Oracle_processes
(5) http://en.wikipedia.org/wiki/IBM_DB2

DBMS Use in Business

Introduction and advantages



The introduction of DBMS into businesses and organizations was vital for their future growth. It offered a simple, efficient and in most cases a reliable way of storing, managing and accessing data. Businesses and organizations alike need to have an efficient and organized way to store their data and to be able to access it without delays or problems.(1) Database Management Systems offer in general:

• Query ability
• Backup and replication
• Rule enforcement
• Security
• Computation
• Change and access logging
• Automated optimization

With all these advantages, companies cannot afford not to have such a system setup anymore. It is vital to their existence and more importantly, it is the only way they can make sure to stay in Business, except that DBMS has to co-exist with a good business plan, and profits have to expected in the long run, not immediately.(2)




Examples



We could easily mention two areas, where DBMS's helped majorly in the growth of the organizations involved:


The retail industry and companies like Best Buy and even online businesses like ebay, rely heavily on database management systems to store data related to their sales, to track purchases and client information updates as well as to analyze automatically the growth charts. This could not have been possible if they didn't rely on database management systems, If they were to keep customer information in physical documents and file them in cabinets, companies like Best Buy could not have been able to open 1800 stores for business internationally. (2) (*)


Likewise, All financial institutions rely heavily on Database management systems to be able to record customer transactions, customer information, credit/debit information. The ability to manage all that data in such an accurate way to handle millions of customers and to be able to access your balance in a branch in Montreal when you actually opened your Bank account in Vancouver, for example, is thanks to the existence of an elaborate DBMS system in place. If a company's DBMS system is non-reliable compared to another competitive company, it risks slower growth and revenue, hence it risks losing market share and profit.(*)




(3)

(4)







(1)http://en.wikipedia.org/wiki/Database_management_system
(2)http://www.allbusiness.com/technology/databases/267657-1.html
(3) (pic ref)http://www.swc.scipy.org/lec/img/db/using_dbms.png
(4)(pic ref)http://docs.sun.com/source/819-3669/images/bank-database.gif
(*) I Was a previous employee, working with these systems.

History of DBMS

Introduction


A DBMS (Database management system) is used to create and maintain the structure of a database, and then to enter, manipulate and retrieve the data it stores. Creating an efficient database design is the key to effectively using a database to support an organization's business operations(1). A database management system (DBMS) is a computer software that manages databases, it may use any of a variety of database models, such as the hierarchical DBMS, network DBMS and relational DBMS . In large systems, a DBMS allows users and other software to store and retrieve data in a structured way.(2)


History


1960's-1970's: The emergence of the first type of DBMS, the hierarchical DBMS. IBM had the first model, developed on IBM 360 and their (DBMS) was called IMS, originally it was written for the Apollo program. This type of DBMS was based on binary trees, where the shape was like a tree and relations were only limited between parent and child records. The benefits were numerous; less redundant data, data independence, security and integrity, which all lead to efficient searches. Nonetheless; there were some disadvantages such as; complex implementation, was hard to manage because of the absence of standards, which made it harder to handle many relationships.( see image below) (1)(3)

1960's-1970's: The emergence of the network DBMS. Charles Bachmann developed first DBMS at Honeywell, Integrated Data Store ( IDS) then a group called CODASYL who is responsible for the creation of COBOL, had that system standardized. However; the CODASYL group invented what they call the "CODASYL APPROACH. Based on that approach many systems using network DBMS were developed for business use(2). In this model, each record can have multiple parents in comparison with one in the hierarchical DBMS. It is made of sets of relationships where a set represents a one to many relationship between the owner and the member. The main and unfortunate disadvantage was that the System was complex and there was difficulty in design and maintenance, it is believed that the Lack of structural independence was the main cause. (2)(3)

1970's- 1990's: The emergence of the relational DBMS on the hands of Edgar Codd. He worked at IBM, and he was unhappy with the navigational model of the CODASYL APPROACH. To him, a tool for searching, such as a search facility was very useful, and it was absent . In 1970, he proposed a new approach to database construction, which made the creation of a Relational DBMS intended for Large Shared Data Banks, possible and easy to grab (3). Moreover; This was a new system for entering data and working with big databases, where the idea was to use a table of records. All tables will be then linked by either one to one relationships, one to many, or many to many(2). when elements took space and were not useful, it was easy to remove them from the original table, and all the other "entries" in other tables linked to this record were removed. Worth mentioning, is that two initial projects were launched, the R program at IBM, and INGRES program at the university of California. In 1985, the object oriented DBMS was developed, but it did not have any booming commercial profit because of the high unjustified costs to change systems, and format. In 1990, the DBMS took on a new object oriented approach joint with relational DBMS . In this approach, text, multimedia, internet and web use in conjunction with DBMS were available and possible.(3)(1)

Past and present

In the early years of computing, a punch card was used in unit record machines for input, data storage and processing this data. Data was entered offline and for both data, and computer programs input. This input method is similar to voting machines nowadays (2). This was the only method, where it was fast to enter data, and retrieve it, but not to manipulate or edit it.
After that era, there was the introduction of the file type entries for data, then the DBMS as hierarchical, network, and relational.(3)

(4)



(1) ORACLE 9I SQL ( LANNES L. MORRIS-MURPHY)
(2) WIKIPEDIA : DBMS
(3) http://www.comphist.org/computing_history/new_page_9.htm
(4)(pic ref): http://ww2.cis.temple.edu/cis109friedman/CIS%20109%20-%20Lecture%20Set%20I%20-%20Introduction%20and%20Overview/history.gif