Oracle database 12c-1

What is DBMS?
Features of DBMS
Entity and Attribute
Data Models
Relational Database Management System (RDBMS)
Integrity Rules
Relational Algebra
Structured Query Language (SQL)

WHAT IS DBMS? Data is one of the most important assets of a company. It is very important to make sure data is stored and maintained accurately and quickly. DBMS (Database Management System) is a system that is used to store and manage data.

A DBMS is a set of programs that is used to store and manipulate data. Manipulations of data 

include the following:

Adding new data, for example adding details of new student. o Deleting unwanted data, for example deleting the details of students who have completed course. o Changing existing data, for example modifying the fee paid by the student.

A DBMS provides various functions like data security, data integrity, data sharing, data concurrence, data independence, data recovery etc. However, all database management systems that are now available in the market like Sybase,

Oracle, and MS-Access do not provide the same set of functions, though all are meant for data management.

Database management systems like Oracle, DB2 are more powerful and meant for bigger companies. Whereas, database management systems like MS-Access are meant for small companies. So, one has to choose the DBMS depending upon the requirement.

FEATURES OF DBMS The following are the main features offered by DBMS. Apart from these features different database management systems may offer different features. For instance, Oracle is substantially fine-tuned to be the database for Internet applications. This may not be found in other database management systems. These are the general features of database management systems. Each DBMS has its own way of implementing it. A DBMS may have more features than those discussed here and may also enhance these features.

Support for large amount of data Each DBMS is designed to support large amount of data. They provide special ways and means to store and manipulate large amount of data. Companies are trying to store more and more amount of data. Some of this data will have to be online (available every time).

In most of the cases the amount of data that can be stored is not actually constrained by DBMS and instead constrained by the availability of the hardware. For example, Oracle can store terabytes of data.

Data sharing, concurrency and locking DBMS also allows data to be shared by two or more users. The same data can be accessed by multiple users at the same time – data concurrency. However, when same data is being manipulated at the same time by multiple users certain

problems arise. To avoid these problems, DBMS locks data that is being manipulated to avoid two users from modifying the same data at the same time.

The locking mechanism is transparent and automatic. Neither we have to inform to DBMS about locking nor know how and when DBMS is locking the data. However, as a programmer, if we can know intricacies of locking mechanism used by DBMS, we will be better programmers.

Data Security While DBMS allows data to be shared, it also ensures that data is only accessed by authorized users. DBMS provides features needed to implement security at the enterprise level. By default, the data of a user cannot be accessed by other users unless the owner gives explicit permissions to other users to do so.

Data Integrity Maintaining integrity of the data is an important process. If data loses integrity, it becomes unusable and garbage. DBMS provides means to implement rules to maintain integrity of the data. Once we specify which rules are to be implemented, then DBMS can make sure that these rules are implemented always.

Three integrity rules (discussed later in this chapter) – domain, entity and referential are always supported by DBMS.

Fault tolerance and recovery DBMS provides great deal of fault tolerance. They continue to run in spite of errors, if possible, allowing users to rectify the mistake in the meantime.

DBMS also allows recovery in the event of failure. For instance, if data on the disk is completely lost due to disk failure then

also data can be recovered to the point of failure if proper back up of the data is available.

Support for Languages DBMS supports data access and manipulation language. The most widely used data access language for RDBMS (relational database management systems) is SQL. We will discuss more about RDBMS and SQL later in this chapter.

DBMS implementation of SQL will be compliant with SQL standards set by ANSI.

Apart from supporting a non-procedural language like SQL to access and manipulate data, DBMS also provides a procedural language for data processing. Oracle provides PL/SQL and Microsoft SQL Server provides T-SQL.

ENTITY AND ATTRIBUTE An entity is any object that is stored in the database. Each entity is associated with a collection of attributes. For example, if you take data of a training institute, student is an entity as we store information about each student in the database. Each student is associated with certain values such as roll number, name, course etc., which are called as attributes of the entity.

There will be relationship among entities. The relationship between entities may be one-to-one, one-to-many or many- to-many.

If you take entities like student, batch and subject, the following are the possible relationships.

There is one-to-one relationship between batch and

subject. One batch is associated with only one subject. o There is one-to-many relationship between batch and

student entities. One batch may contain many students.

There is many-to-many relationship between student and subject entities. A single student may take many subjects and multiple students may take a single subject.

DATA MODELS Data model is a way of storing and retrieving the data. There are three different data models. Data models differ in the way they allow users to view and manipulate relationships between entities. Each has its own way of storing the data. The following are the four different data models:

o Hierarchical o Network o Relational o Object-oriented

Hierarchical In this model, data is stored in the form of a tree. The data is represented by parent-child relationship. Each tree contains a single root record and one or more subordinate records. For example, each batch is root and students of the batch will be subordinates.

This model supports only one-to-many relationship between entities.

This was used in IBM’s Information Management System, IMS.

Network Data is stored along with pointers, which specify the relationship between entities. This was used in Honeywell's Integrated Data Store, IDS.

This model is complex. It is difficult to understand both the way data is stored and the way data is manipulated. It is capable

of supporting many-to-many relationship between entities, which hierarchical model doesn’t.

Relational This stores data in the form of a table. Table is a collection of rows and columns. We will discuss more about relational model in the next section. This is by far the most successful model of all models.

Object-oriented This is where Object Oriented Programming paradigm is applied to database management. Oracle provides supports for this model starting from Oracle8 with the introduction of Object Types.

RELATIONAL DATABASE MANAGEMENT SYSTEM (RDBMS) :

A DBMS that is based on relational model is called as RDBMS. Relational model is most successful mode of all four models. Designed by E.F. Codd, relational model is based on the theory of sets and relations of mathematics.

Relational model represents data in the form of a table. A table is a two dimensional array containing rows and columns. Each row contains data related to an entity such as a student. Each column contains the data related to a single attribute of the entity such as student name.

One of the reasons behind the success of relational model is its simplicity. It is easy to understand the data and easy to manipulate.

Another important advantage with relational model compared with remaining models) is it doesn’t bind data with relationship between data items. Instead it allows you to have dynamic relationship between entities using the values of the columns.

Almost all Database systems that are sold in the market, now- a-days, have either complete or partial implementation of relational model.

Tuple / Row A single row in the table is called as tuple. Each row represents the data of a single entity.

Attribute / Column A column stores an attribute of the entity. For example, if details of students are stored then student name is an attribute; course is another attribute and so on.

Column Name Each column in the table is given a name. This name is used to refer to value in the column.

Table Name Each table is given a name. This is used to refer to the table. The name depicts the content of the table.

The following are two other terms, primary key and foreign key, which are very important in relational model.

Primary Key A table contains data related to entities. If you take COURSES table, it contains data related to courses. For each course there will be one row in the table. Each course’s data in the table must be uniquely identified. In order to identify each entity uniquely in the table, we use a column in the table. That column, which is used to uniquely identify entities (Courses) in the table, is called as primary key.

In case of COURSES table (Figure 1) we can use CODE as the primary key as it is not duplicated.

So a primary key can be defined as a set of columns used to uniquely identify rows of a table.

Some other examples for primary keys are account number in bank, product code of products, and employee number of an employee.

Composite Primary Key In some tables a single column cannot be used to uniquely identify entities (rows). In that case we have to use two or more columns to uniquely identify rows of the table. When a primary key contains two or more columns it is called as composite primary key.

FACULTY_CODE and COURSE_CODE to uniquely identify rows in the table. As primary key is consisting of two columns it is called as composite primary key.

Foreign Key In relational model, we often store data in different tables and put them together to get complete information. For example, table BATCHES stores only course code. To get remaining information about the course, we have to use COURSES table.

The relationship between entities, course and batch, is one-to- many. One course may have many batches. As we already have course code in BATCHES table, it is possible to join with COURSES table and get information about parent entity (course).

COURSE_CODE column in BATCHES table is called as foreign key as it is used to join BATCHES with COURSES table. So, foreign key is the column on the many side of the relationship.

When a child table contains a row that doesn’t refer to a corresponding parent key, it is called as orphan record. We must not have orphan records, as they are result of lack of data integrity.

INTEGRITY RULES Data integrity is to be maintained at any cost. If data loses integrity it becomes garbage. So every effort is to be made to ensure data integrity is maintained. The following are the main integrity rules that are to be followed.

Domain integrity Data is said to have domain integrity when the value of a column is derived from the domain. Domain is the collection of potential values. For example, column DJ (date of joining) must be a valid date. All valid dates form one domain. If the value of date of joining is an invalid date, then it is said to violate domain integrity.

Entity integrity This specifies that all values in primary key must be not null and unique. Each entity that is stored in the table must be uniquely identified. Every table must contain a primary key and primary key must be not null and unique.

Referential Integrity This specifies that a foreign key must be either null or must have a value that is derived from corresponding parent key. For example, COURSE_CODE column of BATCHES table is referencing COURSE_CODE column of COURSES table. All the values of COURSE_CODE column of BATCHES table must be derived from COURSE_CODE column of COURSES table.

RELATIONAL ALGEBRA A set of operators used to perform operations on tables is called as relational algebra. Operators in relational algebra take one or more tables as parameters and produce one table as the result.

The following are operators in relational algebra:

Union o Intersection o Difference or minus o Projection o Selection o Joining

Union This takes two tables and returns all rows that are belonging to either first or second table (or both).

Intersection This takes two tables and returns all rows that are belonging to first and second table.

Difference or Minus This takes two tables and returns all rows that exist in the first table and not in the second table.

Projection Takes a single table and returns the vertical subset of the table.

Selection Takes a single table and returns a horizontal subset of the table. That means it returns only those rows that satisfy the condition.

Joining Rows of two table are combined based on the given column(s) values. The tables being joined must have a common column.

Note: See chapter 3, for SELECT and PROJECT, chapter 9 for JOIN, UNION, INTERSECT and MINUS.

STRUCTURED QUERY LANGUAGE (SQL) Almost all relational database management systems use SQL (Structured Query Language) for data manipulation and retrieval. SQL is the standard language for relational database systems. SQL is a non-procedural language, where you need to concentrate on what you want, not on how you get it. Put it in other way, you need not be concerned with procedural details.

SQL Commands are divided into four categories, depending upon what they do.

DDL (Data Definition Language) o DML (Data Manipulation Language) o DCL (Data Control Language) o Query (Retrieving data)

DDL commands are used to define the data. For example, CREATE TABLE.

DML commands such as, INSERT and DELETE are used to manipulate data.

DCL commands are used to control access to data. For example, GRANT.

Query is used to retrieve data using SELECT.

DML and Query are also collectively called as DML. And DDL and DCL are called as DDL.

SUMMARY A DBMS is used to store and manipulate data. A DBMS based on relational model is RDBMS. Primary key is used for unique identification of rows and foreign key to join tables. Relational algebra is a collection of operators used to operate on tables. We will see how to practically use these operators in later chapters.

SQL is a language commonly used in RDBMS to store and retrieve data. In my opinion, SQL is one of the most important languages if you are dealing with an RDBMS because total data access is done using SQL.