Oracle - Introduction

Main Page >> Oracle and SQL >> Workbook >> Introduction

Introduction

The purpose of this module is to teach you about databases, in particular databases based on the relational model. The relational model is widely used in industry and many vendors provide relational products. Oracle, Microsoft Access and Visual FoxPro are three such products. SQL (Structure Query Language) is regarded as being the de facto query language for relational databases and this module uses Oracle as a vehicle for teaching SQL.

SQL is subject to a number of standards: ANSI X3.135 - 1989; SQL2 (or SQL92) and SQL3 (SQL99) and recently SQL4 (2003). The current version of Oracle (version 12c) supports most of the SQL92 standard. It does also offer some support for objects, which is in the SQL99

As you get more familiar with the product, you will come to realise that a lot of the commands detailed in this booklet could have been more easily entered/executed using the power tools. The purpose of the module is to teach you SQL which is standard across many relational products, e.g., Sysbase, Ingres, DB2. The Oracle Developer tools however are proprietary and every product has different tools. The commands in this booklet however can be executed on most relational platforms with little amendment; this would not be true if the toolset were used.

The Sample Relational Database

A relational database is a named collection of tables and other associated database objects (e.g., indexes, screens, menus, etc.). Typically a database management system (DBMS) can support more than one database simultaneously (or in ANSI-1989 terms, more than one environment). This allows the user to organise their tables into different databases if required. For example, you could have a different database for each module.

The following relational headings show the tables that are used for illustrative purposes throughout the handout:

EMP(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT(DEPTNO, DNAME, LOC)

SALGRADE(GRADE, LOSAL, HISAL)

CUSTOMER(CNO, ORDERDATE, NAME, CITY, COUNTRY, PNO, DESCRIPTION, QTY)

Where:

• EMP contains information about the employees of a sample company
• DEPT contains information about the departments in the company
• SALGRADE groups salary ranges into grades
• CUSTOMER details information about the company's customers (and is an example of a poorly designed table!)

A table is a two-dimensional, fixed length structure that holds information about something you wish to store data about, e.g., employees or customers. Each table will contain a number of records (also called rows or tuples), e.g., each employee will be represented as a single record in the EMP table and each record will contain a number of fields (also called columns or attributes), e.g., DEPT has the fields DEPTNO, DNAME and LOC. The relational model can be described in more formal terms, but for the moment let's keep things simple!

More specific constraints have been added to each table, constraints are a way of enforcing integrity rules on the data and include:

• The department name for the DEPT table (DNAME) must be entered
• The manager number attribute (MGR) refers to another employee’s number. You will see in the sample data that employee 7566 (JONES) is managed by employee 7839 (KING)
• An employee's salary must be entered and be greater than 0
• An employee's commission may or may not be entered, but if it is must be greater than or equal to 0
• An employee's department may or may not be entered, but if it is it must also exist in the department table DEPT
• The low point of a salary grade (LOSAL) must be greater than 0
• The high point of a salary grade (HISAL) must be greater than or equal to the low point

All of this has been specified with NO traditional 3rd generation language programming. The database management system (DBMS) will enforce these constraints at all times.