Tt
Click this widget to change the font size.
CC
Click this widget to change contrast.

Home Page CH 1 | CH 2 | CH 3 | CH 4 | CH 5 | CH 6 | CH 7 | CH 8 | CH 9 | CH 10 | CH 11 | CH 12 | CH 13 | Links | Search | Bio |

Guide to Oracle SQL (University at Buffalo Version)


Introduction to Databases

Wikipedia defines a database as "an organized collection of data. It is the collection of schemas, tables, queries, reports, views and other objects. The data are typically organized to model aspects of reality in a way that supports processes requiring information, such as modelling the availability of rooms in hotels in a way that supports finding a hotel with vacancies."

A schema is "its structure described in a formal language supported by the database management system (DBMS). The term "schema" refers to the organization of data as a blueprint of how the database is constructed (divided into database tables in the case of relational databases).

A table is a collection of related data held in a structured format within a database. It consists of fields (columns), and rows. In relational databases and flat file databases, a table is a set of data elements (values) using a model of vertical columns (identifiable by name) and horizontal rows (a record), the cell (field) being the unit where a row and column intersect.

Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
SID                                       NOT NULL VARCHAR2(9)
S_LNAME                                   NOT NULL VARCHAR2(10)
S_FNAME                                   NOT NULL VARCHAR2(10)
GENDER                                             VARCHAR2(1)
MAJOR                                              VARCHAR2(3)
GPA                                                NUMBER(3,2)
SAT                                                NUMBER(5)
DOB                                                DATE
Figure 1-1: Database Table Structure

Data Types

Each record contains fields that hold the data for that record. A field has a data type, such as Integer (NUMBER), string (VARCHAR/LONG), date (DATE), large objects (BLOB/CLOB)).

CHAR
Used to store fixed length character data. For example, a state field would be CHAR(2).
VARCHAR2
Used to store variable length character data. For example, a firstName field would be VARCHAR2(25). This allows the field to be any number of characters between 0 and 25.
NUMBER
Used to store integer data. For example, the age field would be NUMBER(2).
NUMBER can also be used to store floating point (decimal) data. For example, a currency field would be NUMBER(9,2) which allows 7 digits to the left of the decimal point and 2 digits to the right of the decdimal point.
DATE
Used to store the year (including the century), the month, the day, the hours, the minutes, and the seconds (after midnight).
LOB (Large OBject)
BLOB - Used to store unstructured binary data.
CLOB - Used to store database character set data.
NCLOB - Used to store Unicode national character set data.
BFILE - used to store a pointer to a binary file in the operating system outside the database.
Figure 1-2: Oracle Data Types

Normalization

Under construction. normalization.

Using SQL Developer

SQL Developer is a graphical interface to an Oracle database. You can download SQL Developer from the Oracle download site. Installation instructions are also available on that site.

Setting Up a Connection

SQL Developer New Connection

Figure 1-3: The SQL Developer New Connection Window
  1. In the Name field, type: ubunix.buffalo.edu
  2. In the Username field, type: your-localhost-username
  3. In the Password field, type:
  4. Check the Save Password box.
  5. In the Hostname field, type: ubunix.buffalo.edu
  6. In the SID field, type:
  7. Click: Test
  8. If the test is successful then click: Connect

You should now see your connection in the right side Connections navigation window.

SQL Developer Interface

Figure 1-4: The SQL Developer New Connection Settings

Help contribute to my OER Resources. Donate with PayPal button via my PayPal account.
Creative Commons License This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License. Copyright © 2016-2022 Jim Gerland