Advanced Server Oracle Compatibility Developer's Guide
2.1 Getting Started
Postgres Plus Advanced Server is a relational database management system (RDBMS). That means it is a system for managing data stored in relations. A relation is essentially a mathematical term for a table. The notion of storing data in tables is so commonplace today that it might seem inherently obvious, but there are a number of other ways of organizing databases. Files and directories on Unix-like operating systems form an example of a hierarchical database. A more modern development is the object-oriented database.
Each table is a named collection of rows. Each row of a given table has the same set of named columns, and each column is of a specific data type. Whereas columns have a fixed order in each row, it is important to remember that SQL does not guarantee the order of the rows within the table in any way (although they can be explicitly sorted for display).
Tables are grouped into databases, and a collection of databases managed by a single Postgres Plus Advanced Server server instance constitutes a database cluster.
2.1.1 Sample Database
Throughout this documentation we will be working with a sample database to help explain some basic to advanced level database concepts.
18.104.22.168 Sample Database Installation
When Postgres Plus Advanced Server is installed a sample database named, edb, is automatically created. This sample database contains the tables and programs used throughout this document.
The tables and programs in the sample database can be re-created at any time by executing the script, edb-sample.sql, located in the samples subdirectory of the Postgres Plus Advanced Server home directory.
This script does the following:
● Creates the sample tables and programs in the currently connected database
● Grants all permissions on the tables to the PUBLIC group
The tables and programs will be created in the first schema of the search path in which the current user has permission to create tables and procedures. You can display the search path by issuing the command:
Altering the search path can be done using commands in PSQL.
22.214.171.124 Sample Database Description
The sample database represents employees in an organization.
It contains three types of records: employees, departments, and historical records of employees.
Each employee has an identification number, name, hire date, salary, and manager. Some employees earn a commission in addition to their salary. All employee-related information is stored in the emp table.
The sample company is regionally diverse, so the database keeps track of the location of the departments. Each company employee is assigned to a department. Each department is identified by a unique department number and a short name. Each department is associated with one location. All department-related information is stored in the dept table.
The company also tracks information about jobs held by the employees. Some employees have been with the company for a long time and have held different positions, received raises, switched departments, etc. When a change in employee status occurs, the company records the end date of the former position. A new job record is added with the start date and the new job title, department, salary, and the reason for the status change. All employee history is maintained in the jobhist table.