Introduction & History of DBMSs

The first posting will introduce DBMSs and discuss their history, including a
comparison with the ways data was stored and used before the introduction of
DBMSs.

Where do you store data? How do you retrieve data easily? How do you restrict access to data? Managing data effectively is a big challenge. From adding to manipulating to deleting data, the introduction of database management systems (DBMSs) has truly changed the way we store and manage information. They have not only improved efficiency but also have enabled us share data easily with others. A DBMS allows a user to create and manage databases all with a graphical interface.

Life before DBMSs
In the old days, data was being store in data files called file management system where all information was stored in a single file. The disadvantage was the increased delay in retrieving data and the ability to update information easily. This method has posed great problems and the need for an improved system was long overdue.

History of DBMSs
Early 1960s, Charles Bachman designed the first general-purpose DBMS at General Electric. He called it Integrated Data Store. It was based on the network data model of which data is structured in a tree like environment where each record has one parent and many children.


Almost decade later, in the late 1960s, IBM developed the Information Management Syswtem (IMS) database management system. This system is still being used im major establishments. The introduction of this sytem also introduced a new framework called hierarchical data model. During that same time, IBM and American Airlines had developed a DBMS called SABRE which is primarily used in the web-based travel companies such as Travelocity.


The relational data model was introduced by Edgar Codd in 1970, while working at IBM. This framework had a great impact on database systems. In fact, it has changed the commercial landscape and has made DBMSs a foundation for managing corporate data.


The 1980s and 1990s are the years where great advances have been made in the field of DBMSs. With the creation of Enterprise Resource Planning and Management Resource Planning layers on top of DBMSs, corporations are now able to manage inventory, HR departments, financial analysis and much more. Data is stored in relational databases and DBMS application such as Baan, Oracle, PeopleSoft, SAP and Siebel enabled companies to interact with store information.


References
Database Management Systems by Raghu Ramakrishnan
http://books.google.com/books?hl=en&lr=&id=JSVhe-WLGZ0C&oi=fnd&pg=PR24&dq=Database+management+systems&ots=Lleh7NsjN4&sig=pRxgh7H0DWTKycpeVNsju6B2qFo#PPA6,M1

Advantages of DBMSs in the Business World

Databases are integral components of a business infrastructure. Business rely on databases in order to operate and conduct transactions. No matter how big a company is, the need for a database management system is needed in order for a company to be effective. Employees may use databases in order to retrieve customer information, create an invoice or simply view financial information.

The advantages of using a DBMS in a business are so many that it will be very hard to cover all of them. For the purpose of this project, we will simply analyze 3 main advantages of using a DBMS in a business environment:

1. Reduced operative costs
2. Reporting capabilities
3. Improved client management
   

1) Reduced Operative Costs
For every type of business, one main objective is to reduce its operational costs. A company may take necessary cost reducing decisions in the short term but in the long run, a business must consider implementing a database management solution. DBMSs may have high start up costs, but in the long run these costs will be recuperated. As a business starts growing, so does it's data. With increased transactions and customer acquisitions, data management costs will also increase. A database management solution will reduce costs by allowing company employees add, modify, or simply manipulate data with ease. What might take hours to accomplish a certain task such as gathering sales data of certain customers, it will take a lot less time to retrieve this information using a DBMS.

2) Reporting Capabilities
Businesses rely on reports in order to conduct business. Management needs fast report generating solutions in order to take immediate decisions in regards to day to day operations. Generating reports in a DBMS is one great advantage. Reports can be executed with a click of a button where data will be displayed in any format the user wishes to obtain. Simply query the database for data you wish to retrieve and a report will be generated. Whether it's reports for HR, finance, or management, DBMSs are essential for gathering data.

3) Improved client management
One way for a business to differentiate itself from the rest is by simply offering great customer service. Customers love being taken care of and serviced quick. A DBMS enables a business manage its customer information and keep track of events such as phone calls, service requests and much more. A DBMS will simply make a business aware of what's going on with its customers.

References:
1) http://en.wikipedia.org/wiki/Database_management_system
2) http://www.wandelcoach.biz/

DBMS Software & Architecture

DBMS Architecture
Database management software are sophisticated applications that manage small number of data to enterprise level data. The structure of a DBMS may be analyzed in 2 separate architectures

1. Logical DBMS Architecture
2. Physical DBMS Architecture

Logical DBMS Architecture
In this type of architecture, the user is concerned primarily with the look and feel of the data he or she will manipulate. There are no concerns related to how the data is stored or how it should be handled. That aspect is hidden from the user and the DBMS shields it. For example, in SAP ERP System, you can bring up a customer profile and you may be able to update the information or simply add more data. From that point, you will not be concerned on how and where the data will be stored. You simply click on the save button.

Physical DBMS Architecture
This aspect is concerned with the architecture related to manipulating the data being collected from the user and processing it. The physical architecture can be divided as follows: front-end and back-end divisions.

The front-end module is a simple interface that sits on top of the database and is used primarily for the interaction between the user and the database. The back-end part is concerned with the physical part of the database.
DBMS Software
When discussing DBMS software, I have decided to go over 3 specific ones. Microsoft SQL, Microsoft Access and MySQL. Even though Access is not really being used on large scale platform, it would be interesting to learn more about it since it's a very popular database management system which many students tend to deal with in early university years.

Access is available through Microsoft's productivity suite Office. It's a very powerful application that doesn't cost a lot of money. It allows you to create and manage a database with ease and through it's user friendly graphical interface, you may design tables, generate reports, or simply maintain the data.

MySQL is available for free through the Open Source Agreement. It is being use primarily in conjunction to websites such as ecommerce or newsfeeds.

Widely used by many small or medium sized businesses, SQL server makes the task of managing a database more efficient. Businesses rely on it to manage enterprise level databases.




References:
http://www.cns-service.com/microsoft/images/sql-diagram.gif
http://www.dbmaker.com.tw/reference/manuals/tutorial/tutorial_03.html

Metadata and its Importance

Simply put metadata is data about data. As simple as it sounds, metadata is vital for database management. In order for a database to be designed and created, metadata must be defined.

When you create a database, you may create fields such as first name, address, telephone, customerid. As for metadata, it will describe in details the type of field is Telephone by setting it as a number with X amount of characters or First Name may not contain integers or a max of 50 characters may be set.

Metadata may also set security measures in place by defining who's allowed to access which data or which data may be changed. In order for a database to be managed, there must a clear understanding of its metadata. Without this knowledge, data may have no meaning or integrity may not exist.

For example, in a relational database, metadata will define tables including their name number of records or rows within each one. In addition, it may also define columns in each of these tables and define the type of data to be used in each field. It's similar as to creating a catalog summary of a full database.



References:
1) http://en.wikipedia.org/wiki/Metadata
2) http://www.ukoln.ac.uk/metadata/presentations/2006/rsc-northwest/discovery.pdf
3) http://linktionary.com/m/metadata.html

4) http://www.fao.org/aims/img/metadata_element_set.jpg

Introduction to SQL

What is SQL?
SQL stands for "Standard Query Language". It's a powerful language which is being used in many Database Management Systems nowadays. It's primary purpose is to enable users to manipulate and interact with databases.


SQL and DBMSs
SQL comes in many versions such as PL/SQL or Transact-SQL and many others. In general, all of these version are based upon the ANSI SQL Standard (American National Standards Institute). All DBMSs allow users to interact with a database using a graphical user interface. In the end, all those commands issued by the user are converted into SQL by the DBMS application.


Using SQL
When you are faced with SQL syntax, you might feel overwhelmed due to the amount of tags available for you to use. An SQL statement can certainly be written in many ways but in all, it's very straightforward.

SQL is divided into 2 sublanguages:
1) Data Definition Language
This set includes all commands that enable you to create and structure databases and database objects.

2) Data Manipulation Language
This set includes all commands that enable you to insert, retrieve and alter data within the databases created.


SQL commands examples
In this section will cover the most important commands used in SQL

You may also visit the following website for more SQL tutorials:
http://www.w3schools.com/sql/default.asp

We will display examples of the following commands:
insert, select, update, delete

INSERT
This command allows use to insert records into a table.
INSERT INTO orders
VALUES('12342', 'john', 'smith', '$545.55')
Explanation: orders is the table name and the values reflect the following fields (orderid, first name, last name, order total)

UPDATE
This command allows you to update certain fields within a recordset in a table
UPDATE orders
SET ordertotal = '$99.88'
WHERE orderid = '12342'
Explanation: we are updating the order total value for orderid 12342 to $99.88 in orders table

SELECT
This command allows you to retrieve defined fields within a table based on a condition. YOu may also simply retrieve all records in a table with no condition specified.
SELECT *
FROM orders
WHERE orderid = '12342'
Explanation: We are asking for all "*" fields within this recordset for orderid "12342".


References:
1) http://www.w3schools.com/SQL/sql_intro.asp
2) http://databases.about.com/od/sql/a/sqlbasics.htm
3) http://databases.about.com/od/sql/a/sqlfundamentals.htm