Essay About Using Tables And Column N

Datamining And Security
Essay Preview: Datamining And Security
Report this essay
SQL — Structured Query Language
Tables
In relational database systems (DBS) data are represented using
tables
(relations ). A query
issued against the DBS also results in a table. A table has the following structure:
Column 1
Column 2
. . .
Column n
в†Ñв? Tuple (or Record)
. . .
. . .
. . .
. . .
A table is uniquely identiп¬Ðƒed by its name and consists of rows that contain the stored informa-
tion, each row containing exactly one tuple
(or record ). A table can have one or more columns.
A column
is made up of a column name and a data type, and it describes an attribute of the
tuples. The structure of a table, also called relation schema, thus is deп¬Ðƒned by its attributes.
The type of information to be stored in a table is deп¬Ðƒned by the data types of the attributes
at table creation time.
SQL uses the terms
table, row, and column
relation, tuple, and
attribute, respectively. In
this tutorial we will use the terms interchangeably.
A table can have up to 254 columns which may have diп¬Ð‚erent or same data types and sets of
values (domains), respectively. Possible domains are alphanumeric data (strings), numbers and
date formats.
Oracle oп¬Ð‚ers the following basic data types:
•
char(n): Fixed-length character data (string), n
characters long. The maximum size for
n is 255 bytes (2000 in Oracle8). Note that a string of type char is always padded on
right with blanks to full length of n. (
can be memory consuming).
Example:
char(40)
•
varchar2(n): Variable-length character string. The maximum size for n is 2000 (4000 in
Oracle8). Only the bytes used for a string require storage. Example:
varchar2(80)
•
number(o, d): Numeric data type for integers and reals. o = overall number of digits, d
= number of digits to the right of the decimal point.
Maximum values:
o =38, d= в?84 to +127. Examples:
number(8), number(5,2)
Note that, e.g., number(5,2) cannot contain anything larger than 999.99 without result-
ing in an error. Data types derived from number
are int[eger],
dec[imal], smallint
and real.
•
date: Date data type for storing date and time.
The default format for a date is: DD-MMM-YY. Examples: ’13-OCT-94’, ’07-JAN-98’
•
long: Character data up to a length of 2GB. Only one long column is allowed per table.
Note:
In Oracle-SQL there is no data type boolean. It can, however, be simulated by using
either char(1) or number(1).
As long as no constraint restricts the possible values of an attribute, it may have the special
value
(for unknown). This value is diп¬Ð‚erent from the number 0, and it is also diп¬Ð‚erent
from the empty string вЂ™Ð²Ð‚™.
Further properties of tables are:
•
the order in which tuples appear in a table is not relevant (unless a query requires an
explicit sorting).
•
a table has no duplicate tuples (depending on the query, however, duplicate tuples can
appear in the query result).
A database schema is a set of relation schemas. The extension of a database schema at database
run-time is called a database instance or database, for short.
1.1.1
Example Database
In the following discussions and examples we use an example database to manage information
about employees, departments and salary scales. The corresponding tables can be created
under the UNIX shell using the command
demobld. The tables can be dropped by issuing
the command demodrop under the UNIX shell.
The table EMP is used to store information about employees:
EMPNO
ENAME
SMITH
CLERK
HIREDATE
DEPTNO
17-DEC-80
ALLEN
SALESMAN
20-FEB-81
SALESMAN
22-FEB-81