Binbert Technology | Computer | Security | Networking | Mobile
In my last article we were discussing about a few points related to the origin of the computer era. In this lesson, we will talk about database management systems, query language and the procedure based programming.
Any application software picks data from its database. A database can be defined as a collection of processed data arranged in two-dimensional tables ( ie . in rows and columns). To manage the stored data, we need specialized software which can be called as Database Management Systems (DBMS).
DBMS arranges and stores data in a logical fashion so that it can be easily maintained. IBM was the first company to introduce a formal database management system called IMS. In 1970, the concept of Relational Structure of a database came into effect which is proposed by Dr. Edgar F Codd.
Earlier, list-model database was used, where data is stored in the form of lists. The maintenance of these list based databases was quite difficult.
Codd’s idea of storing data in a tabular manner started a revolution in the database management systems. Instead of storing the entire data in a list, Codd’s way of tabular storage used a method of splitting the data in a logical fashion and stored in multiple tables and linking the tables together by relating the tables. DBMS which store data in this model is known as RDBMS (Relational Database Management System).
In this modern world we have many RDBMS software ex: Oracle, MS SQL Server, IBM DB2 etc..
In our session we are learning a little about Oracle Database and programming using Oracle. Oracle is the world’s leading RDBMS found by Dr. Larry Ellison based on the relational database structure designed by Dr. E F Codd. Oracle stores data in the form of tables and views. We are not going deep into how oracle stores data, oracle architecture, memory etc… We will go through the details of tables, views etc…
A table in an Oracle database is arranged in a two dimensional way represented by Rows and Columns. It is very much similar to an excel worksheet. The picture below displays the structure of a table.
Employees
| EMPID | EMPNAME | DESIGNATION | SALARY | HIREDATE | DEPTID |
| 101 | Scott | Manager | 12500 | 12-Jul-2010 | 200 |
| 102 | Peter | Sales Executive | 10000 | 08-Jul-2009 | 300 |
| 103 | Matt | Secretary | 5000 | 08-Jul-2009 | 200 |
| 104 | Andrea | Receptionist | 5000 | 08-Jul-2009 | 400 |
| 105 | Roma | Receptionist | 6500 | 25-Feb-2010 | 400 |
The above displays the employee details of an organization. The table has a name, Employees. (It is recommended that while creating the table names, the name should be plural). Each column in the table has a column name such as EMPID, EMPNAME etc. Under each column, similar type of data is stored. Each row contains multiple details of a single entity.
Let us see another table called Departments below. Instead of including the department details of each employee in the same employee table, another table is created to store those. We can see that there is a relation exists between each table in the form of EMPID and DEPTID by which each row of these tables corresponds to each other. And this is how a relational database management systems records data. (The table structure here only for illustration purpose, more about creating a table in correct way will be discussed later).
Department
| DEPTID | DEPT | LOCATION | EMPID |
| 200 | Administration | Texas | 101 |
| 300 | Sales | Texas | 102 |
| 200 | Administration | Texas | 103 |
| 400 | Reception | Seattle | 104 |
| 400 | Reception | Seattle | 105 |
The data in the above tables can anytime be queried, manipulated by a database query language. Before we start with the database query language, let us analyze some aspects and foundation stones of database. We shall talk about the data types, character sets, operators, and oracle database specific items.
In a typical database, we store different kinds of data in the form of numbers, alphabets, pictures, movie files etc. When you create a table or cluster, you must specify a data type for the columns accordingly.
Oracle Database provides a number of built-in data types as well as several categories of user-defined types that can be used as data types. A datatype can be either scalar or nonscalar. A scalar type contains an atomic value, whereas a non-scalar (sometimes called a “collection”) contains a set of values. A large object (LOB) is a special form of scalar datatype representing a large scalar value of binary or character data. LOBs are subject to some restrictions that do not affect other scalar types because of their size. The below table lists the most common datatypes used in oracle database.
| Datatype | Description |
| VARCHAR2(size [BYTE | CHAR]) | Variable-length character string having maximum length size bytes or characters. Maximum size is 4000 bytes or characters, and minimum is 1 byte or 1 character. You must specify size for VARCHAR2.
BYTE indicates that the column will have byte length semantics; CHAR indicates that the column will have character semantics |
| NVARCHAR2(size) | Variable-length Unicode character string having maximum length size characters. The number of bytes can be up to two times size for AL16UTF16 encoding and three times size for UTF8 encoding. Maximum size is determined by the national character set definition, with an upper limit of 4000 bytes. You must specify size for NVARCHAR2 |
| NUMBER[(precision [, scale]]) | Number having precision p and scale s. The precision p can range from 1 to 38. The scale s can range from -84 to 127. |
| LONG | Character data of variable length up to 2 gigabytes, or 231 -1 bytes. Provided for backward compatibility |
| DATE | Valid date range from January 1, 4712 BC to December 31, 9999 AD. The default format is determined explicitly by the NLS_ DATE_FORMAT parameter or implicitly by the NLS_TERRITORY
Parameter. The size is fixed at 7 bytes. This datatype contains the date time fields YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. It does not have fractional seconds or a time zone. |
| TIMESTAMP [(fractional_
seconds)] |
Year, month, and day values of date, as well as hour, minute, and second values of time, where fractional_seconds_precision is the number of digits in the fractional part of the SECOND datetime field. Accepted values of fractional_seconds_precision are 0 to 9. The default is 6. The default format is determined explicitly by the NLS_DATE_FORMAT parameter or implicitly by the NLS_TERRITORY parameter. The
Sizes vary from 7 to 11 bytes, depending on the precision. This datatype contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. It contains fractional seconds but does not have a time zone |
| CHAR [(size [BYTE | CHAR])] | Fixed-length character data of length size bytes. Maximum size is 2000 bytes or characters. Default and minimum size is 1 byte. BYTE and CHAR have the same semantics as for VARCHAR2 |
| NCHAR[(size)] | Fixed-length character data of length size characters. The number of bytes can be up to two times size for AL16UTF16 encoding and three times size for UTF8 encoding. Maximum size is determined by the national character set definition, with an upper limit of 2000 bytes. Default and minimum size is 1 character |
Oracle database also has supplied datatypes for advanced uses. Some of them are CLOB, NCLOB, BFILE, BLOB, RAW, ROWID, LONG RAW, BOOLEAN, EXCEPTION, BINARY FLOAT, BINARY DOUBLE, INTERVAL etc… We will go through each of them in later sessions.
The data types are classified into two:
Character datatype which is used to store character based data
Non-character datatypes which is used to stored number form of data.
In the above tables, we see that each column has its own specific data types. The first column EMPID stores only numbers and therefore NUMBER datatype is used and it allows only numbers to be stored. The second column EMPNAME stores characters, therefore VARCHAR2 datatype is stored which allows alphanumeric characters to be stored. HIREDATE uses DATE datatype and only date format is allowed. Each column has a specification in the form of how many characters can be accommodated maximum.
Next lesion, we will discuss about the database query language which is known as Structured Query Language (SQL).
[…] the last session , we have seen a brief about relational database model and a table structure. Let us see the […]
How to create and execute stored procedures in Oracle. i am in the middle of my academic project. thanks for any advice.