3. MARCO TEÓRICO
3.3. RAS 2000 REGLAMENTO TÉCNICO
PostgreSQL is a free alternative to commercial DBMS (such as Oracle, MsSQL Server, IBM DB2, Informix and DBMS manufactured by Sybase), like other free DBMS (such as MySQL and Firebird).
PostgreSQL is based on the SQL language and supports many of the capabilities of the SQL:2003 standard (ISO/IEC 9075).
At this moment in time, PostgreSQL (version 8.4.0) has the following limitations: A maximum database size no limitations
A maximum table size 32 Tb A maximum entry size 1,6 Tb A maximum field size 1 Gb
A maximum number of entries in a table no limitations
A maximum number of fields in a table 250—1600, relating to field types A maximum number of indexes in a table no limitations
The strong points of PostgreSQL are considered to be:
support for databases of practically unlimited size;
powerful and reliable transaction and replication mechanisms;
inheritance;
simple expandability.
Configuring PostgreSQL
Installation of PostgreSQL is relatively straightforward – simply download the distribution kit from the official website: http://www.postgresql.org/download/windows/ and launch the installation process. During the installation process, you will be required to enter the password of the database administrator (the user with login «postgres»).
In cases where the database will be accessed from a remote machine, you will need to enable the remote connections in the DBMS settings.
Step-by-step instructions are presented below:
you will need to edit the file C:\Program Files\PostgreSQL\8.3\data\postgresql.conf, as shown in Figure 1, specifying as the value for the listen_addresses parameter the IP address from which the DBMS will listen and expect an incoming connection (By default, «*» is entered, meaning «All addresses». You may leave this value and not change anything if you wish).
86
Figure 1.
Once you have edited the postgresql.conf file, save it under the same name.
You will need to configure access permission from specific remote computers. For this, you will need to open the file C:\Program Files\PGSQL\8.3\data\pg_hba.conf in any text editor and find in it the following line:
# Ipv4 local connections:
In the list located below this line, add an entry corresponding with the range of IP addresses of the computers from which connections will be initialized. For example:
host all all 192.168.0.7/32 md5
where,
host – signifies authorization at host level
all – signifies that access will be enabled to all databases for all users
192.168.0.7/32 – range of IP addresses, from which a connection will be made (in the format IP/Mask)
md5 – defines the type of encryption for the information being transmitted
87
Figure 2.
Once you have edited and saved files postgresql.conf and pg_hba.conf you will need to restart the database server. To do this, simply restart the PostgreSQL Server service. Changes will be applied only once PostgreSQL is restarted.
The next step is creating the database.
To create a database, launch the PostgreSQL console (script C:\Program Files\PostgreSQL\8.3\scripts\runpsql.bat) and run the following command:
CREATE DATABASE "Activity" WITH OWNER = postgres ENCODING = 'UTF8'; where,
Activity - name of database being created postgres - name of database manager
utf8 – text encoding used to store data in the database. The result of the executed command is shown in Figure 3.
88
Figure 3.
Data Center Configuration
Once the new database is created, Data Center should be set up in order to work with it. For this, you must open the program’s management console, go to the «Configuration» page shown in Figure 4, and select «Data Storage Settings».
89
Figure 4.
90
Figure 5.
On the second tab, enter the connection parameters as shown in Figure 6, where,
192.168.0.166 -IP address of PostgreSQL
5432 - port through which the connection will be made (5432 used by default)
User name - name of user with privileges to connect to the database (all necessary privileges assigned to user «postgres» earlier)
91
Figure 6.
To test whether the connection parameters have been entered correctly, click «Test Connection». After you press «Test Connection», the program will attempt to connect to the database, and if the connection settings were specified correctly, the message shown in Figure 7 will appear.
Figure 7.
Next, click «OK».
A message will appear notifying you that Data Center must be restarted for the new parameters to be applied (Figure 8).
92
Figure 8.
Click «Yes», and Data Center will restart automatically. Once Data Center is restarted, all changes will take effect and the program will begin using the new database to store information.
The status of the database can be viewed on the «Statistics» tab of the data storage management dialog (Figure 9).
Figure 9.
Conclusion:
PostgreSQL - is a freely-distributed object-relational database management system (ORDBMS), the most developed open-source DBMS in the world, and is a real alternative to commercial databases.
93
support for databases of practically unlimited size;
powerful and reliable transaction and replication mechanisms;
inheritance;
94
Oracle
Oracle is a DBMS designed to be incorporated into corporate distributed data processing networks (Enterprise Grid). It allows you to reduce IT expenses thanks to automated management, the use of inexpensive modular components and clustered servers, with the aim of using resources as efficiently as possible. The Oracle architecture is designed to work with large volumes of data and huge teams of users; it demonstrates unrivalled capabilities to provide high reliability, efficiency, scalability, security and self-management. This DBMS can be implemented effectively on any platform, from small blade servers to large symmetrical multiprocessor servers and multisite clusters of any size.
The Oracle DBMS is supplied in four different versions, designed for different working scenarios and deployment of applications (and also differing in price).
Edition Rastriction OS
Enterprise Edition
Standard Edition cannot be installed on systems with more than four processor sockets
Standard Edition One
cannot be installed on systems with more than two processor sockets
Personal Edition one user
Lite for mobile and built-in devices
Express Edition free version; physical working memory —1 Gb, maximum volume of user data — 4 Gb
Windows 32-bit Linux x86
Configuring Oracle
Installation and configuration of the Oracle server should be carried out by the administrator themselves.
All described operations were carried out in Oracle Database 10g Express Edition.
Oracle Database 10g Express Edition can be downloaded from the official Oracle site: http://www.oracle.com/technology/software/products/database/xe/index.html
During installation, you must enter the system administrator password (the user with login «system»). Immediately after installation, the program will launch the database homepage.
On the authorization page, you must enter login «system» and the password chosen during installation, as shown in Figure 1.
95
Figure 1.
96
Figure 2.
97
Figure 3.
On the «Database Users» page, shown in Figure 4, a list of all existing users is displayed. To find a user by name, simply enter the name in the «Search Username» field and click «Go». You can adjust the view of the user list by using the View, Show and Display settings.
View – users displayed in icon or list view
Show – show all users, only database users, only external users Display – maximum number of users displayed in the list To create a new user, click «Create>».
98
Figure 4.
On the create new user page that opens, you must complete all fields, as specified in Figure 5. You will need to enter a name for the new user, a password, and confirm the password.
Besides this, you will need to assign the user all the required privileges. To assign the user the required privileges, simply click «Check All» in the select privileges section.
If the «Expire Password» option is set up, this user’s password will have a time limit. Once the time limit has expired, the user will need to change their password.
The «Account Status» option allows you to set the status of an account (blocked/unblocked). By using this option, you can temporarily disable unused accounts.
Once you have entered all the required information, click «Create» to begin the user creation procedure.
99
Figure 5.
On completion of the user creation procedure, the page shown in Figure 6 will open. If everything has been done correctly, and the user was created successfully, their name will appear in the list.
100
Figure 6.
On the computer with Data Center, you will need to set up Oracle Database Instant Client. Oracle Database Instant Client can be downloaded here:
http://www.oracle.com/technology/software/tech/oci/instantclient/htdocs/winsoft.html
To provide access to the database, simply download the Instant Client Package - Basic Lite package. Once it has downloaded, unzip the contents of the package to any folder, for example
C:\Oracle\InstantClient.
Next, copy the file tnsnames.ora from the c:\oraclexe\app\oracle\product\10.2.0\server\NETWORK\ADMIN\ folder of the computer where the Oracle database server is installed to the C:\Oracle\InstantClient folder of the client’s computer.
101
Data Center Configuration
Once the new database is created, Data Center should be set up in order to work with it. For this, you must open the program’s management console, go to the «Configuration» page shown in Figure 7, and select «Data Storage Settings».
Figure 7.
102
Figure 8.
On the second tab, you must specify the parameters for connecting to the database (Figure 9), where,
192.168.0.166 -IP address of Oracle server
User name - name of user with privileges to connect to the database (all necessary privileges assigned to user «Activity» earlier)
Password - password for user «Activity»
103
Figure 9.
Once you have pressed «Test Connection» the program will attempt to connect to the database, and if the connection settings have been specified correctly, the message shown in Figure 10.
Figure 10.
Next, click «OK».
A message will appear notifying you that Data Center must be restarted for the new parameters to be applied (Figure 11).
104
Figure 11.
Click «Yes», and Data Center will restart automatically. Once Data Center is restarted, all changes will take effect and the program will begin using the new database to store information.
The status of the database can be viewed on the «Statistics» tab of the data storage management dialog (Figure 12).
Figure 12.
Conclusion:
Oracle is the largest professional DBMS with a multitude of options and settings. This DBMS is designed to be incorporated into corporate distributed data processing networks.
The Oracle architecture is designed to work with large volumes of data. Due to its complex setup and high price, this DBMS is not recommended for use on smaller networks.