Oracle Tutorials Structured Query Language Data, Databases, RDBMS
| 
| 
 |
Tutorials introducing some basic concepts:
|
 |
|
What is data ?
Data is a raw fact that you wish to store for business requirements and future use. Individuals maintain data in the form of address books containing the contact details of their friends and acquaintances. Companies store information about their employees, their products they sell, the product sales etc. Hospitals store patient data, staff data, payment details etc. The amount of data to be stored can vary. Depending on the amount of data to be stored you might choose to use an address book or a spreadsheet or a database.
What is a database?
Databases allow you to store large quantities of data. By definition, a database is a centralized repository of related data. The word centralized implies that it is shared between many users and data is stored only in one place. The word related implies that the data is not random and is always stored based on the needs of a business environment.
What is a database management system?
To store data in a database, you would need a type of software that will store and manage the data in the database. This type of software is called a database management system. Database management systems are of various kinds, and the most popular type is called the Relational Database Management System (RDBMS). An RDBMS allow you to conceptualize all the data in the form of tables.
What are tables?
Tables are two-dimensional structures consisting of rows and columns.
If you wish to store the details of products that you sell, you would create a table with a name, say PRODUCT. You would identify attributes for the product table, depending on what you want to details you want to store for each product. These include, the PRODUCT_ID, PRODUCT_NAME, PRICE, CURRENT_STOCK, and SUPPLIER_ID. These attributes of the table, will become the columns of the table. In an RDBMS all the data is stored in tables. Tables may have relationships with each other. These relationships can be created and maintained by the RDBMS.
The actual products that are sold would form the rows of the table. For each product you sell, you will add a row to the table. Hence the PRODUCT table typically looks like:
| PRODUCT_ID | PRODUCT_NAME | PRICE | CURRENT_STOCK | SUPPLIER_ID |
| 101 | COMPUTER MONITOR | 145 | 6 | S1 |
| 102 | LABEL MAKER | 3 | S2 | |
| 103 | BROTHER PRINTER | 175 | 8 | S3 |
To further understand this article, presented is another table called the SUPPLIER table that contains details of suppliers.
| SUPPLIER_ID | SUPPLIER_NAME | PHONE_NUMBER |
| S1 | GOODS TO GO | 888-000-0001 |
| S2 | WAREHOUSE DEALS | 444-900-0001 |
Important concepts about tables
It is important to become familiar with the following terms, when discussing tables:
| Term | Description |
| Primary Key |
The primary key is a column(s) of a table that can be used to uniquely identify the rows of the table. In the example above the Product ID is a column that can the primary key because its values are unique and each product has a value for that column. |
| Null | An unavailable or unknown value. The price of the label maker in the PRODUCT table is null, as there is no value. The price for this product might be unavailable. |
| Foreign Key | is a column(s) whose values must be existing values in a primary key column of the same table or another table. The SUPPLIER_ID column in the product table is a foreign key columns. The values in that column are related to the values in the SUPPLIER_ID column of the SUPPLIER table. Since, products are purchased only by known suppliers, the values taken by the SUPPLIER_ID column of the PRODUCT tables, must be existing values in the SUPPLIER_ID column of the SUPPLIER table. |
Understanding the structure of of a table
When a table is created in Oracle, the table and its columns must be given names. The columns would have datatypes, which identify the type of value that can be stored in the column. The basic datatypes can be numeric, character, date. Numeric data is defined as NUMBER, character data can either be CHAR or VARCHAR2. CHAR is fixed length character data. VARCHAR2 is variable length character data. DATE is date type values such as 2-FEB-72. You can also specify a size for the columns, indicating the maximum width in the case of character data, or the number of digits in the case of numeric data. A numeric value can also take decimal places. In the price column below, the (10,2) indicates, a total of 10 digits, and two decimal places.
In the PRODUCT table, the datatypes for the columns are:
PRODUCT_ID -> NUMBER
PRODUCT_NAME -> VARCHAR2(20)
PRICE -> NUMBER(10,2)
SUPPLIER_ID -> NUMBER(3)
In Oracle you can see the structure of a table using a DESCRIBE command. At this time, it would be a good idea to take a look at the article Sample Tables that describe the tables that will be used for explaining other articles. Take a few minutes to review the tables, their description and their data. Review the relationships between the tables and draw an ER diagram.
