In general, a database is an organized repository of data stored on a permanent external storage and used for searching, reading and updating information in a multi-user environment.
There are all sort of databases like File-based databases, Object Oriented Databases (OOD) and Data Warehouse databases, but I will concentrate on RDBMS because this is the most used databases type.
File based databases are the most simple databases and they have been in use for a while. Even today there are companies that use file based databases for accounting or even banking.
Advantages of RDBMS
The advantages of a RDBMS over a File based databases:
- It has all kind of constraints, to achieve a better data quality
- You can use SQL to do reporting and make data changes
- You can write stored procedures and triggers
- It has transactions so the data is consistent all the time.
- It is encapsulated. So you can’t alter the data by manipulating data files directly.
Some of the most popular RDBMS
In this course we will present how to program for Oracle database. This is one of most popular relational databases. Other databases that implement SQL and are relational databases have much in common with Oracle. Therefore once you have learn to use Oracle you will find easy to learn other databases.
- Microsoft SQL
- IBM DB2
Features of RDBMS
Some of the most important features of relational objects:
- Implement SQL language
- Have database engines for Linux or Windows
- Support for structured data: Tables, Indexes, Views
- Support stored procedures
- Support for triggers
The content of a database
So a database contains data elements organized in a structure. The structure describe data elements and the relations between data elements. The elements of the database are also called objects. Some of the objects are: tables, indexes and views.
Tables and Indexes
Tables and indexes are database objects. The tables are used to store data while indexes are used to find data faster. A table is the only one capable of storing the data. The index is a redundant structure, it contains duplicated data that is also found in the table. However this data is sorted while in the table the data is not ordered.
An example of a table:
Let’s see how is this represented. When we use paper and pencil to organize data we usually draw a box separated by several lines and we put a header in top of the box. Then we draw some line separators and we have a tableau of values like in the picture below:
This is the picture we name a “table” and we use this term to represent a collection of data that are related to each other. We read the table like: Elucian has 50 and Doru has 54 and Dani has 42….
Rows and columns:
A table is organized in rows and columns. Every column have a name but the rows do not have a name. Sometimes a row is unique identified by data elements and sometimes the rows can contain duplicated values.
In example PERSONS table there is no relation between Elucian and Doru and Dani. Every row can exist independent and there is no relation between rows. The relation is between Name and Age columns. However a table is a collection of same kind of data and we can name this collection with a meaningful name like: “PERSONS”.
The PERSONS table contains 6 persons now but in the future we may add or remove persons from the table. This is a powerful feature of a database to be able to add, modify or remove information from a table.
The structure of the table PERSON is stable. It does not change over time. A good design is when a table do not have to change it’s structure over time. If a design has to be changed this is problematic and is subject to another future article.
For now is good to know that many tables can be designed for a database. These tables represents a part of the database structure. In a real database there are many tables and they are related to each other. This is called a “database model” and is a good design strategy to make a picture of your database model using a diagram.
We know now that inside a table data row represents a relation. However in the database world there is also a relation between data from different tables. These relations are called “references”. Most developers are using the term “relation” instead of “reference”. This is not wrong but we need to know that internal and external relations are represented different into a database structure.
The reference between 2 different entities or two different tables can be of 3 basic types:
- One to one notation is 1:1
- One to many with notation 1 : M
- Many to many with notation M : M
A person can be in one Team and sometimes can be in more Teams. This relation can be one to one or one to many. It is depending on company policy. A project can be created by one or more teams therefore the relation is one to many.For some companies this is not allowed so the relation can be also one to one. Many projects can be resolved by many persons. Therefore between Persons and Projects we have a relation many to many.
This is the database model and is represented into an ER diagram. ER = Entity Relationship diagram. And you have just learned the basics of a “database structure” and “database model”.
Views and SQL
When we search for data we can use SQL (Query Language). This language start with a keyword SELECT and specify the list of columns we like to retrieve and the table. This SQL can be created every time when we query the database or can be stored in the database permanently like a template. This template is called a View.
The View is a SQL template. It does not contain data but specify how we can visualize the data. It is a visualization description of the data. So every time we use a view we actually execute a SQL that is searching into the table and is providing the data in a specific format.
We create a view almost like a SQL but we specify a name for it. After we create a view this can be re-used in other SQL queries like a table. This is a form of inheritance if you will. It can be very productive to create SQL over a View. Also a view can be used for security purposes, to protect some data but not other data.
CREATE VIEW MyOrder AS SELECT * FROM user_table ORDER BY name;
Oh wow the procedures can be stored. Yes this is exactly what is it. We create programs for a computer but we create stored procedures for a database. These are also programs but these programs are specific to databases. They are related to data structure and therefore are compiled in correlation with the database and are stored side by side with the data in the same database.
For stored procedures in Oracle we use a language: PL/SQL that is Programming Language for SQL. This is a language with ADA like syntax. What is funny about this language is the way we create the SQL. We do not use strings to create the SQL like in other languages. Instead we use Keywords like SELECT, UPDATE, INSERT and so on. These keywords are compiled by the PL/SQL compiler.
The advantages of a store procedure is that is compiled. Therefore a stored procedure do not have compilation errors. That means is verified against the database structure. If the database structure is changed the procedure become invalid automatically and must be corrected. This is to avoid run-time errors in production.
The triggers are also small stored procedures. These procedures are related to a database object. Usually triggers start automatically when a event is happening. For example a table trigger can start when new data is added. This is an event. Some triggers can be started when data is updated or deleted.
In oracle there are 3 kind of triggers:
- Triggers for each row;
- Triggers for each statement;
- Triggers for database session.
For details how to create a trigger you can check the Oracle documentation.
See also: PL/SQL Oracle Documentation