The Database Essentials
Before you start learning the technicalities of SQL as a computer language, this episode will discuss first what a database is and its fundamental characteristics. You will also be informed why the business world is now driven to gather and manipulate data to bring forth more profitable income.
There are many ways on how to define or describe what a database is. In simple terms, it can be defined as a collection of items that can exist over a long period of time. Think of a calling card holder as a database that contains business cards with different information of people that you know (e.g. person’s name, job title, company name, contact number). Another one is a printed telephone directory (more popularly known as the yellow pages) that contains the name, phone number and address of the registered residents living in a particular area.
Some define database more professionally, not just a collection of data. It is described as an organized tool capable of keeping data or information that you can retrieve in an effective and efficient way when the need arises. It can also be more strictly defined as a self-describing collection of objects that are integrated to one another. When you create representations of these physical or conceptual objects then they will be called records. From the previous example of your calling card holder, if you wish to keep track of your business contacts then you have to assign each business card a specific record. Every record contains multiple information or data, such as individual name, job title, company name and address, phone number and more that you will now call the record’s attributes.
A database does not only contain the data that you need, but also what you call its metadata. This is the information that defines or describes the data’s structure within the given database (that is why it was defined earlier as a self- describing entity), stored in a region called data dictionary. Thus, data retrieval will be faster if you know how information is arranged and stored. Furthermore, relationships exist among the data items since they are integrated to one another. Check the following figure for a sample illustration of what a database is.
Whether a database contains a simple collection of a few records or a massive system composed of millions of records, it can be categorized into three types: personal, workgroup or departmental, and enterprise. Each category is characterized by the database size, the machinery size into which the database runs and how big the organization that manages it.
- Personal Database – This is conceptualized and designed by a single person on a stand-alone computer. Its database structure is rather simple and the size is relatively small. For example, your personal electronic address book.
- Workgroup/Departmental Database – This is designed and created by individuals of a single workgroup or department within a certain organization. The database structure is larger and more complex, as compared to the personal category, which is also accessed by multiple users at the same time.
- Enterprise Database – Among the three categories, this type is conceptualized and created to handle the entire flow of information of very large organizations. Thus, the database design involves far more complex structures.
- Relational Database Fundamentals :-
Taking the discussion further into a more technical aspect, a relational database is an entity consisting of logical units known as tables. This relational database model was first formulated by Dr. E. F. Codd in 1970. How the tables are related to each other defines their relationships. In this scenario, data is simplified into smaller yet more logical and manageable units that optimize the database performance. The following figure shows an illustration on how the various components of a relationaldatabase are connected to each other.
A table consists of rows and columns that store data. In a relational database, these tables are related to one another improving the data retrieval process when a query is submitted by the user. For you to clearly picture out this
idea, convert the information found on the calling card holder into a spreadsheet like a Microsoft Excel file. Assume that these are your contacts from companies that have ordered products and services from your business. You will have at least a CUSTOMER TABLE (containing all important information about your contacts) similar to the following:
| CUSTOMER ID |
NAME |
POSITION |
COMPANY |
STATE |
CONTACT No. |
| 1 |
Kathy Ale |
President |
Tile Industrial |
TX |
234564327 |
| 2 |
Kevin Lord |
VP |
Best Tooling |
NY |
865336976 |
| 3 |
Kim Ash |
Director |
Car World |
CA |
673146976 |
| 4 |
Abby Karr |
Manager |
West Mart |
NV |
247336900 |
You will also have an ORDER TABLE that will store information such as order ID, date, quantity and more. Check the following table:
| ORDER ID |
ORDER_DATE |
CUSTOMER ID |
PRODUCT ID |
ORDER QTY |
| 1 |
2016-05-23 |
1 |
4 |
300 |
| 2 |
2016-09-09 |
1 |
5 |
100 |
| 3 |
2016-02-17 |
3 |
2 |
150 |
| 4 |
2016-05-12 |
2 |
2 |
500 |
As you can see, each table looks like an array of rows and columns.
Referring to the CUSTOMER TABLE, a row is also called a record or a tuple that holds information for a single customer. On the other hand, a column holds a single attribute of the customer (i.e., name, job title or position, company name and address, contact number). It is also self-consistent, meaning it contains the same type of data in every row. So if a column contains the name of your customer in the first row, then the succeeding rows will have to show the names of your other customers. There is also no significance which row or column will appear first and which will be next, since there is no particular organization that is followed. Looking at both tables, you will notice that each one of them has a
column that contains the same data value – CUSTOMER ID. This is now called the common key, which links the tables to one another in a relational database. The existence of the common keys makes it possible to merge data from multiple tables in forming a larger set of data entity.
The relation between the two tables consists of a two-dimensional array of data stored in rows and columns. The intersection of a row and a column is called a cell. Each cell contains singe-valued entries and each row is unique. Thus, each cell has only one value and no duplicate rows. Going back to the
CUSTOMER and ORDER TABLES, will be able to create the following relation by adding the CUSTOMER NAME that corresponds to the given CUSTOMER ID:
| ORDER ID |
CUSTOMER ID |
CUSTOMER NAME |
|---|
| 1 |
1 |
Kathy Ale |
| 2 |
1 |
Kathy Ale |
| 3 |
3 |
Kim Ash |
| 4 |
2 |
Kevin Lord |
You will see that there is no empty cell. One particular order referenced by the ORDER ID is associated to a particular customer, indicated by the CUSTOMER ID and CUSTOMER NAME. There are no two customers having the same ORDER ID. That is why, there will be no two rows that are exactly identical.
- Database Management Systems :-
A database management system, or simply DBMS, is an important programming tool that consists of a set of programs that define, manage and process databases and all applications associated to them.
Through this, you are able to build a structure and operate on the valuable data that the database holds in a very efficient way. There are two main types of users that work on DBMS – the conventional user who retrieves or modifies data and the administrator who is responsible for maintaining the structure of the database.
The following are the key features of a DBMS:
- Allows the creation of new databases and their data structures.
- Allows data query and modification using an appropriate programming language.
- Allows the storage of vast amounts of data over a long period of time.
- Enables database recovery in times of failure, error or intentional misuse.
- Controls data access from many users at once.
It was during the late 1960s when the first commercial DBMS appeared. It evolved from file systems that basically provided data storage for over a certain period of time. Though such systems were capable of storing tremendous
amount of data, computer professionals still have to face problems of data loss and an inefficient information retrieval system. There were also issues on control access where errors occur when two users modify the same file at the same time. Some examples of these applications are airline reservation systems, banking
systems and corporate record keeping systems, among others.
With the advancement of technology in the market today, you can find numerous programs that will be suitable for your DBMS requirements. There are applications that run on a small scale level like your personal computer or tablet. Some are built to run on a large and powerful equipment, like those being used
by multinational companies. Nevertheless, the on-going trend is for DBMS to be executed on multiple platforms or machines (whether large or small) that are
interconnected to one another, forming an immense scalable network. IT experts have also found ways on how store data using Internet technology in powerful
data centers or more popularly known as clouds. This cloud can be public entity (managed by a large company like Microsoft or Google) or a private one (maintained and stored via the intranet within an organization).
In this episode you have learnt the essential features of a database and how it transforms to become a relational database. Also, you had an overview of the history and characteristics of database management systems. In the next episode you will learn the fundamental structure of the SQL language and its components.
Share This Post
No comments:
Post a Comment
If you have any doubts. Please let me know.